[oracle性能优化]sql调优基础
不会根据执行计划进行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;