加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 (https://www.lidatong.com.cn/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 百科 > 正文

[oracle性能优化]sql调优基础

发布时间:2020-12-12 13:43:40 所属栏目:百科 来源:网络整理
导读:不会根据执行计划进行sql调优的dba是不合格的,SQL tuning是基本技能 0. 优化器的基本概念 ???? 为SQL语句找到最好的,执行成本最低的执行计划 ???? 制定执行计划是以SQL语句中涉及到的对象的统计信息为基础的。 1. 统计信息的介绍 ??? --表的统计信息(user

不会根据执行计划进行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;

(编辑:李大同)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章
      热点阅读