ITPub博客

首页 > 数据库 > Oracle > 一条SQL的优化

一条SQL的优化

原创 Oracle 作者:lucyne 时间:2015-08-12 15:26:40 0 删除 编辑
开发同学反映,一条SQL查询特别慢,并提供了SQL如下。


SQL> select *
  2    from BB.rc_log
  3   where cn = 'd577s780887'
  4     and charge_date >= to_date('2013-09-01', 'YYYY-MM-DD')
  5     and charge_date <= to_date('2013-09-08', 'YYYY-MM-DD') + 1
  6     and item_id = 13
  7   order by charge_date desc;
Elapsed: 00:00:03.01
Execution Plan
----------------------------------------------------------
Plan hash value: 4099628218
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |   100 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | rc_log        |     1 |   100 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_CHARGE_LOG_DATE |     4 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("CN"='d577s780887' AND "ITEM_ID"=3)
   2 - access("CHARGE_DATE">=TO_DATE(' 2013-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "CHARGE_DATE"<=TO_DATE(' 2013-09-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      19409  consistent gets
          0  physical reads
          0  redo size
       1575  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


按道理应该走cn的索引,用最笨的办法可以验证一下。


查询条件cn,只查出来3条记录
SQL> select count(*)
  2    from BB.rc_log
  3   where cn = 'd577s780887';
  COUNT(*)
----------
         3

根据时间条件去查询结果为763811条记录。
SQL> select count(*)
  2    from BB.rc_log
  3   where charge_date >= to_date('2013-09-01', 'YYYY-MM-DD')
  4     and charge_date <= to_date('2013-09-08', 'YYYY-MM-DD') + 1
  5     and item_id = 13;
  COUNT(*)
----------
    763811

而之前的执行计划按时间查询,再过滤cn,这样显然是不正确的。

SQL的优化第一点,查看表结构,索引结构及表,索引统计信息。
SQL>  alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS' ;

Session altered.

SQL> select table_name,last_analyzed from dba_tables where table_name ='rc_log' and owner='BB';

TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------------
rc_log                   2013-05-22 12:37:25

经检查统计信息不正确,由于GATHER_STATS_JOB未启动,导致DB里很多表,很长时间都没有收集统计信息,显然SQL选择的执行计划不准。

SQL>  exec sys.dbms_scheduler.enable( '"SYS"."GATHER_STATS_JOB"' ); 
PL/SQL procedure successfully completed.


手工收集一下此表的统计信息。

SQL> execute dbms_stats.gather_table_stats(ownname=> 'BB',tabname=> 'rc_log',cascade=> true);

PL/SQL procedure successfully completed.

再次查看SQL的执行计划。
SQL> select *
  2    from BB.rc_log
  3   where cn = 'd577s780887'
  4     and charge_date >= to_date('2013-09-01', 'YYYY-MM-DD')
  5     and charge_date <= to_date('2013-09-08', 'YYYY-MM-DD') + 1
  6     and item_id = 13
  7   order by charge_date desc;
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 3333539068
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    99 |     8  (13)| 00:00:01 |
|   1 |  SORT ORDER BY               |                     |     1 |    99 |     8  (13)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| rc_log        |     1 |    99 |     7   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_rc_log_CN |    24 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("CHARGE_DATE">=TO_DATE(' 2013-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ITEM_ID"=3 AND "CHARGE_DATE"<=TO_DATE(' 2013-09-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   3 - access("CN"='d577s780887')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          1  physical reads
          0  redo size
       1575  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

ok,以上SQL执行计划已经走IDX_rc_log_CN索引RANGE SACN,执行时间下降到毫秒,逻辑读下降了19402。

事情有那么简单吗?
没过多久,我们通过监控去了解db的性能,又发现此SQL执行时间较长,而此时开发同学也来找我了,说这条SQL又开始慢了。

查看SQL执行计划。
SQL> select count(*)
  2    from BB.rc_log
  3   where cn = 'd78088ss73'
  4     and CHARGE_DATE >= to_date('2013-10-18', 'YYYY-MM-DD')
  5     and CHARGE_DATE <= to_date('2013-10-25', 'YYYY-MM-DD') + 1
  6     and ITEM_ID = 15;

  COUNT(*)
----------
         0

Elapsed: 00:00:02.22
Execution Plan
----------------------------------------------------------
Plan hash value: 3721384603
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    35 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                     |     1 |    35 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| rc_log        |     1 |    35 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_CHARGE_LOG_DATE |     5 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("CN"='d78088ss73' AND "ITEM_ID"=5)
   3 - access("CHARGE_DATE">=TO_DATE(' 2013-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "CHARGE_DATE"<=TO_DATE(' 2013-10-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      10404  consistent gets
          0  physical reads
          0  redo size
        514  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

以上SQL执行计划中,我们发现此SQL又走IDX_CHARGE_LOG_DATE索引。

使用hint 使其走cn索引,执行时间就会降低。
SQL> select  /*+ INDEX(rc_log IDX_rc_log_CN) */ count(*)
  2    from BB.rc_log
  3   where cn = 'd78088ss73'
  4     and CHARGE_DATE >= to_date('2013-10-18', 'YYYY-MM-DD')
  5     and CHARGE_DATE <= to_date('2013-10-25', 'YYYY-MM-DD') + 1
  6     and ITEM_ID = 15;

  COUNT(*)
----------
         0
Elapsed: 00:00:00.24
Execution Plan
----------------------------------------------------------
Plan hash value: 4051850550
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    35 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                     |     1 |    35 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| rc_log        |     1 |    35 |     7   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_rc_log_CN |    24 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("CHARGE_DATE">=TO_DATE(' 2013-10-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ITEM_ID"=5 AND "CHARGE_DATE"<=TO_DATE(' 2013-10-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   3 - access("CN"='d78088ss73')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         56  consistent gets
         45  physical reads
          0  redo size
        514  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

同样,我们查看统计信息是否正确,发现统计信息最后分析时间是1个月前。
SQL> l
  1* select owner,table_name,num_rows,last_analyzed from dba_tables where table_name='rc_log'
SQL> /

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZE
------------------------------ ------------------------------ ---------- ------------
BB                           rc_log                    362229571 12-SEP-13

查看GATHER_STATS_JOB的执行历史,发现每次都成功执行。
SQL> select LOG_DATE, job_name, status, run_duration
  2    from dba_scheduler_job_run_details
  3   where job_name = 'GATHER_STATS_JOB'
  4  --and status <> 'SUCCEEDED'
  5   order by 1 desc;
LOG_DATE                                        JOB_NAME          STATUS            RUN_DURATION
-----------------------------------------------------------------------------------------------------
25-10月-13 02.05.55.545590 上午 +08:00        GATHER_STATS_JOB       SUCCEEDED    +000 00:05:54
24-10月-13 02.07.02.504118 上午 +08:00        GATHER_STATS_JOB       SUCCEEDED    +000 00:07:00
23-10月-13 02.03.28.883934 上午 +08:00        GATHER_STATS_JOB       SUCCEEDED    +000 00:03:27
22-10月-13 02.05.25.676865 上午 +08:00        GATHER_STATS_JOB       SUCCEEDED    +000 00:05:22
21-10月-13 02.14.29.999187 上午 +08:00        GATHER_STATS_JOB       SUCCEEDED    +000 00:14:29
19-10月-13 12.05.05.076881 上午 +08:00        GATHER_STATS_JOB       SUCCEEDED    +000 00:05:03
18-10月-13 02.03.24.322206 上午 +08:00        GATHER_STATS_JOB       SUCCEEDED    +000 00:03:23
17-10月-13 02.02.14.114860 上午 +08:00        GATHER_STATS_JOB       SUCCEEDED    +000 00:02:11
16-10月-13 02.06.29.104459 上午 +08:00        GATHER_STATS_JOB       SUCCEEDED    +000 00:06:26
15-10月-13 02.09.51.079176 上午 +08:00        GATHER_STATS_JOB       SUCCEEDED    +000 00:09:50
14-10月-13 02.06.38.523192 上午 +08:00        GATHER_STATS_JOB       SUCCEEDED    +000 00:06:36
12-10月-13 12.02.58.584856 上午 +08:00        GATHER_STATS_JOB       SUCCEEDED    +000 00:02:57
11-10月-13 02.06.15.622545 上午 +08:00        GATHER_STATS_JOB       SUCCEEDED    +000 00:06:15
10-10月-13 02.10.57.021596 上午 +08:00        GATHER_STATS_JOB       SUCCEEDED    +000 00:10:54


为什么GATHER_STATS_JOB成功执行,但rc_log表的统计信息又不准呢?
原因就是GATHER_STATS_JOB自动统计信息收集JOB,每次执行时是有针对性地收集统计信息,而不是对数据库中所有schema下的对象都分析/收集一遍;


  分析/收集的条件:
     1. 对象之前从未收集过统计信息,或由于某些原因没有统计信息。
     2. 对象的统计信息相对陈旧,是否陈旧的评判标准是由上次收集信息到此次收集期间被修改过的行数需要超过10%。


怎么能确定是否陈旧,怎么能确定修改过的行数超过10%?


SQL> show parameter statistics_level
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

SQL> select table_name,inserts,updates,deletes,timestamp from dba_tab_modifications where table_name='rc_log';
TABLE_NAME                        INSERTS    UPDATES    DELETES TIMESTAMP
------------------------------ ---------- ---------- ---------- ------------
rc_log                      3829422         12          1 25-OCT-13

dba_tab_modifications 表中的数据可能不是最新的,可以执行 DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO收集最新的DML数据;


  Oracle是怎么知道表是否有过DML操作,而DML操作又涉及到了多少行数据呢?这都是通过表监控特性(table monitoring facility)来实现的,当初始化参数STATISTICS_LEVEL设置为TYPICAL或ALL时默认启用这个特性。Oracle会默认监控表上的INSERT,UPDATE,DELETE以及表是否有过TRUNCATE操作,并记录这些操作数量的数据到数据字典。我们可以通过访问dba_tab_modifications视图来查看表的信息,同时由于table monitoring facility的存在,不管过去多久,只要某个表数据不改变,gather_stats_job就不会对此表进行统计信息收集,这样就节省了系统资源。若statistics_level=basic,则table monitoring facility被禁止,automatic statistics gathering将无法知道stale statistics,此时需要人工收集;

经过查询发现现在修改过的行数才是表行业的 1%
SQL> select (3829422+12+1)/362229571 from dual;
(3829422+12+1)/362229571
------------------------
              .010571845

定义一个单表收集的job,每周二凌晨1点执行。
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SYS"."GATHER_RECHARGE"',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
dbms_stats.gather_table_stats(ownname=>''BB'',tabname=>''rc_log'',cascade=>true);
END;',
repeat_interval => 'FREQ=WEEKLY;BYDAY=TUE;BYHOUR=1;BYMINUTE=0;BYSECOND=0',
start_date => to_timestamp_tz('2013-10-26 Asia/Shanghai', 'YYYY-MM-DD TZR'),
job_class => '"DEFAULT_JOB_CLASS"',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"SYS"."GATHER_RECHARGE"', attribute => 'job_weight', value => 1);
sys.dbms_scheduler.enable( '"SYS"."GATHER_RECHARGE"' );
END;

第二天查询,GATHER_RECHARGE收集正常。
SQL> select owner,table_name,num_rows,last_analyzed from dba_tables where table_name='rc_log';
OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZE
------------------------------ ------------------------------ ---------- ------------
BB                           rc_log                    366220581 26-OCT-13
查询dba_tab_modifications
SQL> select table_name,inserts,updates,deletes,timestamp from dba_tab_modifications where table_name='rc_log';

TABLE_NAME                        INSERTS    UPDATES    DELETES TIMESTAMP
------------------------------ ---------- ---------- ---------- ------------
rc_log                            2          0          0 26-OCT-13

再次查看执行计划,已经正常。
SQL> select count(*)
  2    from BB.rc_log
  3   where cn = 'yaowen56ll011@changyou.com'
  4     and CHARGE_DATE >= to_date('2013-10-18', 'YYYY-MM-DD')
  5     and CHARGE_DATE <= to_date('2013-10-25', 'YYYY-MM-DD') + 1
  6     and ITEM_ID = 15;

  COUNT(*)
----------
         0
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 4051850550
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    34 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                     |     1 |    34 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| rc_log        |     1 |    34 |     7   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_rc_log_CN |    24 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ITEM_ID"=5 AND "CHARGE_DATE">=TO_DATE(' 2013-10-18 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "CHARGE_DATE"<=TO_DATE(' 2013-10-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   3 - access("CN"='yaowen56ll011@changyou.com')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        514  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


事情有那么简单吗?


没那么简单。。。。


------------优化要深入业务----------------
最终我们查询rc_log表上所有的SQL,发现所有SQL的谓词都有cn和date两个条件,
如果此时是一个cn+date的联合索引,此SQL就不会出现由于统计信息不正确而导致了走错执行计划。
但是统计信息正确是CBO评估正确执行计划关键信息,我们应该保证统计信息正确,特别是对于数据量大的表,
我们应该自定义统计信息的收集,以免出现由于统计信息导致执行计划不准的问题。



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26390465/viewspace-1769360/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
. . 一个 DBA. . .

注册时间:2012-01-06

  • 博文量
    84
  • 访问量
    741503