不会根据执行计划进行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,--每条记录的平均长度 ??? 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,--直方图的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‘,‘(PROD_ID,CUST_ID)‘); ???? END;
??? select sys.dbms_stats.show_extended_stats_name(‘SH‘,CUST_ID)‘) col_group_name ???? from dual;
??? EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SH‘,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?,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,sysdate-1,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,--直方图的BUCKET个数 ???? HISTOGRAM --直方图的类型 ???? FROM USER_TAB_COLUMNS ???? where table_name=‘SALES_ONLINE‘
?? SELECT NUM_ROWS,--每条记录的平均长度 ??? 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,sysdate,AMOUNT_SOLD from new_sales;
commit;
--再次查询数据 select c.cust_city,‘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,‘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;
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|