欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-27 14:30:33
不会根据执行计划进行sql调优的dba是不合格的,SQL tuning是基本技能 ......

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