查看内容

oracle质量优化

  • 2020-01-18 10:29
  • 数据库知识
  • Views

不会根据执行计划进行sql调优的dba是不合格的,SQL tuning是基本技能

0. 优化器的基本概念

     为SQL语句找到最好的,执行成本最低的执行计划
     制定执行计划是以SQL语句中涉及到的对象的统计信息为基础的。

1. 统计信息的介绍

    --表的统计信息(user_tables, user_TAB_STATISTICS)
   
    SELECT NUM_ROWS, --表中的记录数
    BLOCKS, --表中数据所占的数据块数
    EMPTY_BLOCKS, --表中的空块数
    AVG_SPACE, --数据块中平均的使用空间
    CHAIN_CNT, --表中行连接和行迁移的数量
    AVG_ROW_LEN, --每条记录的平均长度
    LAST_ANALYZED -- 最近一次搜集统计信息的时间
    FROM USER_TABLES where table_name='NEW_SALES';
   
    --搜集表的统计信息
    exec dbms_stats.gather_table_stats('SH','NEW_SALES');
   
    --再来执行一次
    SELECT NUM_ROWS, --表中的记录数
    BLOCKS, --表中数据所占的数据块数
    EMPTY_BLOCKS, --表中的空块数
    AVG_SPACE, --数据块中平均的使用空间
    CHAIN_CNT, --表中行连接和行迁移的数量
    AVG_ROW_LEN, --每条记录的平均长度
    LAST_ANALYZED -- 最近一次搜集统计信息的时间
    FROM USER_TABLES where table_name='NEW_SALES';
   
   
     --列的统计信息 (user_tab_columns, user_TAB_COL_STATISTICS,user_TAB_HISTOGRAMS)
     SELECT COLUMN_NAME,
     NUM_DISTINCT, --唯一值的个数
     LOW_VALUE, --列上的最小值
     HIGH_VALUE, --列上的最大值
     DENSITY, --选择率因子(密度) = 1/(NDV),如果不存在柱状图的话
     NUM_NULLS, --空值的个数
     NUM_BUCKETS, --直方图的BUCKET个数
     HISTOGRAM --直方图的类型
     FROM USER_TAB_COLUMNS
     where table_name='NEW_SALES'
   
     --搜集柱状图
     exec dbms_stats.Gather_table_stats('SH', 'NEW_SALES', method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 CUST_ID');
    
    
     --再来看看统计信息
     SELECT COLUMN_NAME,
     NUM_DISTINCT, --唯一值的个数
     LOW_VALUE, --列上的最小值
     HIGH_VALUE, --列上的最大值
     DENSITY, --选择率因子(密度) = 1/(NDV),如果不存在柱状图的话
     NUM_NULLS, --空值的个数
     NUM_BUCKETS, --直方图的BUCKET个数
     HISTOGRAM --直方图的类型
     FROM USER_TAB_COLUMNS
     where table_name='NEW_SALES'
    
     select
     column_name,
     ENDPOINT_NUMBER,
     ENDPOINT_VALUE,
     from user_TAB_HISTOGRAMS
     where table_name='NEW_SALES' and COLUMN_NAME='CUST_ID'
    
   
    扩展统计信息 (user_stat_extensions)
     select e.extension col_group, t.num_distinct, t.histogram
     from user_stat_extensions e, user_tab_col_statistics t
     where e.extension_name=t.column_name
     and t.table_name='NEW_SALES';
    
     --搜集扩展统计信息
     DECLARE
        cg_name varchar2(30);
     BEGIN
        cg_name := dbms_stats.create_extended_stats('SH','NEW_SALES','(PROD_ID,CUST_ID)');
     END;

    select sys.dbms_stats.show_extended_stats_name('SH','NEW_SALES', '(PROD_ID,CUST_ID)') col_group_name
     from dual;

    EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','NEW_SALES', METHOD_OPT => -
     'FOR COLUMNS (PROD_ID,CUST_ID) SIZE SKEWONLY');     

2.统计信息不准确容易导致的问题

表统计信息不准确
    导致了表的访问方式出现了问题(全表扫描和使用索引)
    导致了表和表的链接方式出现问题(应该使用hash join,却是用了nest loop)
   
列统计信息不准确
    导致了访问表的方式不同(错误的索引)
    导致了表的连接方式不同(应该使用hash join , 但是使用了nest loop)

索引的统计信息不准确
    导致了访问表的方式不同(应该使用索引,但是使用了全表扫描)

+++++++++++++++++++++++++++++++++++++++++++
--当天线上表
create table sales_online
(
  PROD_ID      NUMBER  NOT NULL  ,      
  CUST_ID       NUMBER  NOT NULL,      
time_id      DATE  NOT NULL,        
CHANNEL_ID     NUMBER  NOT NULL,      
PROMO_ID       NUMBER  NOT NULL,      
QUANTITY_SOLD  NUMBER(10,2) NOT NULL,
AMOUNT_SOLD    NUMBER(10,2) NOT NULL)

--历史归档表

create table sales_part (
  PROD_ID      NUMBER  NOT NULL  ,      
  CUST_ID       NUMBER  NOT NULL,      
time_id      DATE  NOT NULL,        
CHANNEL_ID     NUMBER  NOT NULL,      
PROMO_ID       NUMBER  NOT NULL,      
QUANTITY_SOLD  NUMBER(10,2) NOT NULL,
AMOUNT_SOLD    NUMBER(10,2) NOT NULL)
partition by range (time_id)
(
partition part_20171218 values less than (to_date('19-12-2017','dd-mm-yyyy')),
partition part_20171219 values less than (to_date('20-12-2017','dd-mm-yyyy'))
);

insert into sales_part
select PROD_ID,CUST_ID, sysdate-2,CHANNEL_ID,PROMO_ID,QUANTITY_SOLD,AMOUNT_SOLD
from new_sales;

insert into sales_part
select PROD_ID,CUST_ID, sysdate-1,CHANNEL_ID,PROMO_ID,QUANTITY_SOLD,AMOUNT_SOLD
from new_sales;

commit;

create index sales_cust_idx on sales_online(cust_id);
create index sales_part_cust_idx on sales_part(cust_id);

--每天晚上把当天数据归档之后,再删除
declare
    v_sql varchar2(3000);
begin

   v_sql := 'alter table SALES_PART drop partition PART_20171219';
    execute immediate v_sql;
   
    v_sql := 'alter table sales_part add partition part_'||to_char(sysdate+1,'yyyymmdd')||
    ' values less than (to_date('||''''||to_char(sysdate+1,'dd-mm-yyyy')||''''||','||''''||'dd-mm-yyyy'||
    ''''||'))';
    dbms_output.put_line(v_sql);
    execute immediate v_sql;
   
    v_sql := 'alter table sales_part exchange partition part_'||to_char(sysdate+1,'yyyymmdd')||
    ' with table sales_online';
    execute immediate v_sql;
    dbms_output.put_line(v_sql);
   
    v_sql := 'truncate table sales_online';
    execute immediate v_sql;
    dbms_output.put_line(v_sql);
   
    v_sql := 'alter index sales_part_cust_idx rebuild online';
    execute immediate v_sql;
    dbms_output.put_line(v_sql);
   
     v_sql := 'alter index sales_cust_idx rebuild online';
    execute immediate v_sql;
    dbms_output.put_line(v_sql);
  
    dbms_stats.Gather_table_stats('SH', 'SALES_PART');
    dbms_stats.Gather_table_stats('SH', 'SALES_ONLINE');
   
end;

-- 检查统计信息

    SELECT COLUMN_NAME,
     NUM_DISTINCT, --唯一值的个数
     LOW_VALUE, --列上的最小值
     HIGH_VALUE, --列上的最大值
     DENSITY, --选择率因子(密度) = 1/(NDV),如果不存在柱状图的话
     NUM_NULLS, --空值的个数
     NUM_BUCKETS, --直方图的BUCKET个数
     HISTOGRAM --直方图的类型
     FROM USER_TAB_COLUMNS
     where table_name='SALES_ONLINE'

   SELECT NUM_ROWS, --表中的记录数
    BLOCKS, --表中数据所占的数据块数
    EMPTY_BLOCKS, --表中的空块数
    AVG_SPACE, --数据块中平均的使用空间
    CHAIN_CNT, --表中行连接和行迁移的数量
    AVG_ROW_LEN, --每条记录的平均长度
    LAST_ANALYZED -- 最近一次搜集统计信息的时间
    FROM USER_TABLES where table_name='SALES_ONLINE';

   

====实例1

--进行查询
select c.cust_city, sum(AMOUNT_SOLD) from sales_part s, new_customers c
where s.cust_id = c.cust_id
and s.cust_id > 100
and time_id between to_date('2017-12-18 00:00:00' ,'yyyy-mm-dd hh24:mi:ss') and 
to_date('2017-12-18 01:00:00', 'yyyy-mm-dd hh24:mi:ss')
group by c.cust_city


|   0 | SELECT STATEMENT         |               |     1 |    48 |  1779   (1)| 00:00:22 | |        |
|   1 |  HASH GROUP BY           |               |     1 |    48 |  1779   (1)| 00:00:22 | |        |
|*  2 |   HASH JOIN              |               |     1 |    48 |  1778   (1)| 00:00:22 | |        |
|   3 |    PARTITION RANGE SINGLE|               |     1 |    18 |  1373   (1)| 00:00:17 |     2 |     2 |
|*  4 |     TABLE ACCESS FULL    | SALES_PART    |     1 |    18 |  1373   (1)| 00:00:17 |     2 |     2 |
|*  5 |    TABLE ACCESS FULL     | NEW_CUSTOMERS | 54144 |  1586K|  

405   (1)| 00:00:05 | |        |

select c.cust_city, sum(AMOUNT_SOLD) from sales_online s, new_customers c
where s.cust_id = c.cust_id
and s.cust_id > 100
and time_id between to_date('2017-12-20 00:00:00' ,'yyyy-mm-dd hh24:mi:ss') and 
to_date('2017-12-20 01:00:00', 'yyyy-mm-dd hh24:mi:ss')
group by c.cust_city


| Id  | Operation                     | Name           | Rows  | Bytes |

Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT              |                |     1 |    65 |   406   (1)| 00:00:05 |
|   1 |  HASH GROUP BY                |                |     1 |    65 |   406   (1)| 00:00:05 |
|*  2 |   HASH JOIN                   |                |     1 |    65 |   405   (1)| 00:00:05 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| SALES_ONLINE   |     1 |    35 |     0   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SALES_CUST_IDX |     1 |       |     0   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL          | NEW_CUSTOMERS  | 54144 | 

1586K|   405   (1)| 00:00:05 |

--向表sales_online 中插入一些数据

insert into sales_online
select PROD_ID,CUST_ID, sysdate,CHANNEL_ID,PROMO_ID,QUANTITY_SOLD,AMOUNT_SOLD
from new_sales;

commit;

--再次查询数据
select c.cust_city, sum(AMOUNT_SOLD) from sales_online s, new_customers c
where s.cust_id = c.cust_id
and s.cust_id > 100
and time_id between to_date('2017-12-20 00:00:00' ,'yyyy-mm-dd hh24:mi:ss') and 
to_date('2017-12-20 01:00:00', 'yyyy-mm-dd hh24:mi:ss')
group by c.cust_city


|   0 | SELECT STATEMENT              |                |     1 |    65 |   406   (1)| 00:00:05 |
|   1 |  HASH GROUP BY                |                |     1 |    65 |   406   (1)| 00:00:05 |
|*  2 |   HASH JOIN                   |                |     1 |    65 |   405   (1)| 00:00:05 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| SALES_ONLINE   |     1 |    35 |     0   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SALES_CUST_IDX |     1 |       |     0   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL          | NEW_CUSTOMERS  | 54144 | 

1586K|   405   (1)| 00:00:05 |

       
--手动搜集统计信息或许是一个办法

exec dbms_stats.Gather_table_stats('SH', 'SALES_ONLINE', cascade => true);


| Id  | Operation           | Name          | Rows  | Bytes | Cost

(%CPU)| Time     |

|   0 | SELECT STATEMENT    |               |     1 |    48 |  1641   (1)| 00:00:20 |
|   1 |  HASH GROUP BY      |               |     1 |    48 |  1641   (1)| 00:00:20 |
|*  2 |   HASH JOIN         |               |     1 |    48 |  1640   (1)| 00:00:20 |
|*  3 |    TABLE ACCESS FULL| SALES_ONLINE  |     1 |    18 |  1235   (1)| 00:00:15 |
|*  4 |    TABLE ACCESS FULL| NEW_CUSTOMERS | 54144 |  1586K|   405  

(1)| 00:00:05 |

执行计划变了过来。但是这不是一个好的办法,因为在生产时间搜集统计信息比较危险。

—-可以这样做

declare
    v_sql varchar2(3000);
begin

   v_sql := 'alter table SALES_PART drop partition PART_20171219';
    execute immediate v_sql;
   
    --导出统计信息
    dbms_stats.export_table_stats(ownname =>'SH',tabname=>'SALES_ONLINE',stattab=>'SALES_ONLINE_ST',statid => 'A2');
   
    v_sql := 'alter table sales_part add partition part_'||to_char(sysdate+1,'yyyymmdd')||
    ' values less than (to_date('||''''||to_char(sysdate+1,'dd-mm-yyyy')||''''||','||''''||'dd-mm-yyyy'||
    ''''||'))';
    dbms_output.put_line(v_sql);
    execute immediate v_sql;
   
    v_sql := 'alter table sales_part exchange partition part_'||to_char(sysdate+1,'yyyymmdd')||
    ' with table sales_online';
    execute immediate v_sql;
    dbms_output.put_line(v_sql);
   
    v_sql := 'truncate table sales_online';
    execute immediate v_sql;
    dbms_output.put_line(v_sql);
   
    v_sql := 'alter index sales_part_cust_idx rebuild online';
    execute immediate v_sql;
    dbms_output.put_line(v_sql);
   
     v_sql := 'alter index sales_cust_idx rebuild online';
    execute immediate v_sql;
    dbms_output.put_line(v_sql);
  
    dbms_stats.Gather_table_stats('SH', 'SALES_PART');
    --dbms_stats.Gather_table_stats('SH', 'SALES_ONLINE');

   --导入统计信息
    dbms_stats.import_table_stats(ownname => 'SH', tabname => 'SALES_ONLINE', stattab => 'SALES_ONLINE_ST', statid => 'A2', no_invalidate => true);

end;

上一篇:九个方面告诉你 下一篇:表的优化