failed parse elapsed time过大分析案例

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:failed parse elapsed time过大分析案例

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

朋友公司做压力测试,awr比较异常,让给看看
1-1


这里显示数据库db time较大,数据库应该比较繁忙,主要等待事件为:library cache: mutex X
1-4

但是Load Profile显示Parses (SQL)和Hard parses (SQL)均不大
1-2

但是发现failed parse elapsed time特别大,也就是说这个库出现该问题,主要可能是由于sql语句执行解析失败导致,而解析失败最大的可能性就是sql语句语法/权限错误.对于这类问题可以通过设置event 10035进行跟踪
1-3

演示设置event 10035进行跟踪的效果

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> ALTER SYSTEM SET EVENTS '10035 trace name context forever, level 1';

System altered.

SQL> ALTER SESSION SET EVENTS '10035 trace name context forever, level 1';

Session altered.

SQL> select 1;
select 1
       *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> select * from xifenfei_t;
select * from xifenfei_t
              *
ERROR at line 1:
ORA-00942: table or view does not exist

查看alert日志,观察错误语句
1-5


sql plan baseline简单介绍

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:sql plan baseline简单介绍

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

Oracle 11g开始,提供了一种新的固定执行计划的方法,即SQL plan baseline,中文名SQL执行计划基线(简称基线),可以认为是OUTLINE(大纲)或者SQL PROFILE的改进版本,基本上它的主要作用可以归纳为如下两个:
1、稳定给定SQL语句的执行计划,防止执行环境或对象统计信息等因子的改变对SQL语句的执行计划产生影响
2、减少数据库中出现SQL语句性能退化的概率,理论上不允许一条语句切换到一个比已经执行过的执行计划慢很多的新的执行计划上(可以通过OPTIMIZER_USE_SQL_PLAN_BASELINE实现)
3、sql baseline对于sql 大小写,sql空格可以生效,但是对于非绑定变量sql,如果使用不同变量无法生效(无force_matching功能)
确认当前无sql baseline启用

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

no rows selected

创建场景
模拟一个表有index,如果再不强制的情况下,查询直接使用index,但是我这边要通过sql baseline模拟使用走全表扫描,实现不修改sql的情况下直接修改执行计划

SQL> create table t_xifenfei tablespace users as select * from dba_objects;

Table created.

SQL> create index i_xifenfei on t_xifenfei(object_id) tablespace users;

Index created.

SQL> execute dbms_stats.gather_table_stats('SYS','T_XIFENFEI',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  b9hj14ntjgmtr, child number 0
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 1926396081

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=100)


19 rows selected.


SQL> select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  aqgv7stwu6w5t, child number 0
-------------------------------------
select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where
OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)


19 rows selected.

从shared pool->library cache中直接加载sql plan baseline

SQL> set serveroutput on
SQL>  DECLARE
  2      ret PLS_INTEGER;
  3    BEGIN
  4      ret := dbms_spm.load_plans_from_cursor_cache(sql_id          => 'b9hj14ntjgmtr',
  5                                                   plan_hash_value => null);
  6      dbms_output.put_line(ret || ' SQL plan baseline(s) created');
  7    END;
  8    /
1 SQL plan baseline(s) created

PL/SQL procedure successfully completed.

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
ACC
---
SQL_ed6b78bdb7b643ad           SQL_PLAN_fuuvsrqvvchxd04acd9ab
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES

SQL> select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  b9hj14ntjgmtr, child number 1
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 1926396081

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=100)

Note
-----
   - SQL plan baseline SQL_PLAN_fuuvsrqvvchxd04acd9ab used for this statement


23 rows selected.

利用第一个baseline的sql_handle创建新执行计划的baseline

SQL> set serveroutput on
SQL> DECLARE
  2    ret pls_integer;
  3   begin
  4   ret := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
  5   sql_id=>'aqgv7stwu6w5t',
  6   plan_hash_value=>548923532,sql_handle=>'SQL_ed6b78bdb7b643ad'
  7   );
  8  dbms_output.put_line(ret || ' SQL plan baseline(s) created');
  9   end;
 10   /
1 SQL plan baseline(s) created

PL/SQL procedure successfully completed.

SQL>  select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
ACC
---
SQL_ed6b78bdb7b643ad           SQL_PLAN_fuuvsrqvvchxd04acd9ab
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES

SQL_ed6b78bdb7b643ad           SQL_PLAN_fuuvsrqvvchxdf0c521d1
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES

删除第一个baseline

SQL> set serveroutput on
SQL> DECLARE
  2    ret pls_integer;
  3   begin
  4   ret := DBMS_SPM.drop_sql_plan_baseline (  sql_handle=>'SQL_ed6b78bdb7b643ad'
     ,plan_name=>'SQL_PLAN_fuuvsrqvvchxd04acd9ab');
  5  dbms_output.put_line(ret || ' SQL plan baseline(s) created');
  6   end;
  7   /
1 SQL plan baseline(s) created

PL/SQL procedure successfully completed.


SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
ACC
---
SQL_ed6b78bdb7b643ad           SQL_PLAN_fuuvsrqvvchxdf0c521d1
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES

验证baseline生效,实现sql语句执行计划的改变

SQL> alter system flush shared_pool;

System altered.

SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL>  select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  b9hj14ntjgmtr, child number 1
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

Note
-----
   - SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement


22 rows selected.

利用coe脚本利用baseline快速绑定sql执行计划

SQL> set serveroutput on
SQL> DECLARE
  2    ret pls_integer;
  3   begin
  4   ret := DBMS_SPM.drop_sql_plan_baseline (  sql_handle=>'SQL_ed6b78bdb7b643ad',
      plan_name=>'SQL_PLAN_fuuvsrqvvchxdf0c521d1');
  5  dbms_output.put_line(ret || ' SQL plan baseline(s) created');
  6   end;
  7   /
1 SQL plan baseline(s) created

PL/SQL procedure successfully completed.

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

no rows selected


SQL> alter system flush shared_pool;

System altered.

SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL> select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL> select sql_id,sql_text from v$sql where sql_text like '%from T_XIFENFEI where OBJECT_ID=100%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
aqgv7stwu6w5t
select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

7jdqvvnpxb9z5
select sql_id,sql_text from v$sql where sql_text like '%from T_XIFENFEI where OBJECT_ID=100%'

b9hj14ntjgmtr
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100


SQL> select sql_id,PLAN_HASH_VALUE from v$sql where sql_id in('b9hj14ntjgmtr','aqgv7stwu6w5t');

SQL_ID        PLAN_HASH_VALUE
------------- ---------------
aqgv7stwu6w5t       548923532
b9hj14ntjgmtr      1926396081

SQL>  select * from table(dbms_xplan.display_cursor('aqgv7stwu6w5t','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  aqgv7stwu6w5t, child number 0
-------------------------------------
select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where
OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)


19 rows selected.

SQL>  select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  b9hj14ntjgmtr, child number 0
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 1926396081

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=100)


19 rows selected.




SQL> @coe_load_sql_baseline.sql

Parameter 1:
ORIGINAL_SQL_ID (required)

Enter value for 1: b9hj14ntjgmtr

Parameter 2:
MODIFIED_SQL_ID (required)

Enter value for 2: aqgv7stwu6w5t


     PLAN_HASH_VALUE          AVG_ET_SECS
-------------------- --------------------
           548923532                 .003

Parameter 3:
PLAN_HASH_VALUE (required)

Enter value for 3: 548923532



SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
ACC
---
SQL_ed6b78bdb7b643ad           SQL_PLAN_fuuvsrqvvchxdf0c521d1
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100
YES


SQL> alter system flush shared_pool ;

System altered.

SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------
ORA$BASE

SQL>  select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  b9hj14ntjgmtr, child number 1
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

Note
-----
   - SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement


22 rows selected.

sql空格和大小写改变不影响baseline效果

SQL>  select * from table(dbms_xplan.display_cursor('dwfxd7x6kwx6u','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dwfxd7x6kwx6u, child number 1
-------------------------------------
select     OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

Note
-----
   - SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement


22 rows selected.

SQL> select     OBJECT_NAME from T_xifenfei where OBJECT_ID=100;

OBJECT_NAME
--------------------------------------------------------------------------------
ORA$BASE

SQL> select sql_id,sql_text from v$sql where sql_text like '%from T_xifenfei where OBJECT_ID=100%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
5spn2x6ac44af
select sql_id,sql_text from v$sql where sql_text like '%from T_xifenfei where OB
JECT_ID=100%'

8tytmh8r6w80n
select     OBJECT_NAME from T_xifenfei where OBJECT_ID=100


SQL>  select * from table(dbms_xplan.display_cursor('8tytmh8r6w80n','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  8tytmh8r6w80n, child number 1
-------------------------------------
select     OBJECT_NAME from T_xifenfei where OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   349 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   349   (1)| 00:00:05 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

Note
-----
   - SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement


22 rows selected.

但是sql变量不一样导致baseline失效

SQL>  select * from table(dbms_xplan.display_cursor('fp9u8wkp5cuw1','',''));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fp9u8wkp5cuw1, child number 0
-------------------------------------
select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=101

Plan hash value: 1926396081

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|         |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    30 |     2   (0)|00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
00:00:01 |

------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=101)


19 rows selected.

执行计划改变导致数据库负载过高

核心生产库突然负载飙升,我的压力来了,通过分析是一条sql的执行计划改变引起该故障,最终通过sql profile固定执行计划解决该问题
数据库主机负载
这里明显表现系统load 偏高,而且还在上升中;top的进程中,占用cpu都计划100%

top - 16:25:39 up 123 days,  1:42,  4 users,  load average: 46.19, 45.08, 43.93
Tasks: 1469 total,  28 running, 1439 sleeping,   0 stopped,   2 zombie
Cpu(s): 45.9%us,  1.1%sy,  0.0%ni, 47.1%id,  5.2%wa,  0.1%hi,  0.6%si,  0.0%st
Mem:  264253752k total, 262605260k used,  1648492k free,   413408k buffers
Swap: 33554424k total,   458684k used, 33095740k free, 67110504k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                    
 2622 oracle    18   0  150g  34m  28m R 100.0  0.0  11:58.21 oracleq9db1 (LOCAL=NO)    
15881 oracle    21   0  150g  35m  28m R 100.0  0.0  72:14.20 oracleq9db1 (LOCAL=NO)    
17214 oracle    19   0  150g  38m  32m R 100.0  0.0  20:47.44 oracleq9db1 (LOCAL=NO)    
17705 oracle    21   0  150g  34m  28m R 100.0  0.0  27:33.00 oracleq9db1 (LOCAL=NO)    
 6110 oracle    19   0  150g  34m  28m R 99.8  0.0  12:33.34 oracleq9db1 (LOCAL=NO)     
 6876 oracle    19   0  150g  34m  27m R 99.5  0.0  12:43.90 oracleq9db1 (LOCAL=NO)     
17205 oracle    23   0  150g  34m  27m R 99.5  0.0  21:37.18 oracleq9db1 (LOCAL=NO)     
24629 oracle    20   0  150g  35m  29m R 99.5  0.0  28:10.62 oracleq9db1 (LOCAL=NO)     
26959 oracle    19   0  150g  34m  27m R 99.5  0.0  47:17.87 oracleq9db1 (LOCAL=NO)     
 7655 oracle    18   0  150g  30m  25m R 98.5  0.0   2:28.45 oracleq9db1 (LOCAL=NO)     
16377 oracle    18   0  150g  34m  28m R 98.5  0.0  36:07.11 oracleq9db1 (LOCAL=NO)     
24637 oracle    18   0  150g  37m  30m R 98.2  0.0  26:39.15 oracleq9db1 (LOCAL=NO)     
 6106 oracle    21   0  150g  40m  33m R 97.2  0.0  11:37.75 oracleq9db1 (LOCAL=NO)     
28785 oracle    18   0  150g  34m  28m R 96.9  0.0  24:03.29 oracleq9db1 (LOCAL=NO)     
24278 oracle    17   0  150g  31m  26m S 96.5  0.0   3:15.51 oracleq9db1 (LOCAL=NO)     
24283 oracle    17   0  150g  33m  28m S 96.5  0.0   6:25.26 oracleq9db1 (LOCAL=NO)     
 7098 oracle    18   0  150g  32m  27m R 94.6  0.0   2:20.22 oracleq9db1 (LOCAL=NO)     
 6874 oracle    17   0  150g  34m  28m R 87.0  0.0  12:02.92 oracleq9db1 (LOCAL=NO)     
18206 oracle    16   0  150g  34m  27m R 86.1  0.0  16:11.28 oracleq9db1 (LOCAL=NO)     
 7096 oracle    17   0  150g  29m  24m R 85.4  0.0   3:01.72 oracleq9db1 (LOCAL=NO)     

数据库等待事件

       SID EVENT
---------- ----------------------------------------------------------------
       183 gc cr request
       185 latch: cache buffers chains
       239 db file sequential read
       292 gc cr request
       406 gc cr request
       410 db file sequential read
       463 gc current request
       572 gc buffer busy acquire
       575 gc buffer busy acquire
       577 latch: cache buffers chains
       629 db file sequential read
       747 gc cr request
       919 latch: cache buffers chains
       974 gc cr request
      1033 log file sync
      1141 db file parallel write
      1153 gc cr request
      1199 db file sequential read
      1378 db file sequential read
      1495 gc cr request
      1540 db file parallel write
      1547 gc buffer busy acquire
      1662 gc cr request
      1715 gc buffer busy acquire
      1770 SQL*Net message to client
      1830 latch: cache buffers chains
      1884 gc cr request
      2113 db file sequential read
      2173 db file sequential read
      2229 rdbms ipc reply
      2292 db file sequential read
      2341 db file sequential read
      2348 gc cr request
      2460 gc cr request
      2632 gc cr request
      2684 gc cr request
      2687 db file sequential read
      2749 db file sequential read
      2913 gc cr request
      2967 db file sequential read
      3038 gc cr request
      3087 SQL*Net message to client
      3089 gc cr request
      3194 db file sequential read
      3195 db file sequential read
      3309 latch: cache buffers chains
      3371 gc cr request
      3485 gc cr request
      3535 gc cr request

可以这里有很多gc cr request等待和cache buffers chains等待,第一反应就是很可能系统某条sql执行计划不正确导致逻辑读剧增.

分析awr报告
故障时候的逻辑读 top sql可以看出来cdwjdd67x27mh的sql逻辑读异常大
awr1
分析正常时间点awr报告中cdwjdd67x27mh逻辑读情况
awr2
通过对比可以发现两个awr中,cdwjdd67x27mh 语句执行次数差不多,但是单次逻辑读从800多突变为34000多,增加了40多倍

cdwjdd67x27mh 语句分析
执行计划

17:36:08 sys@Q9DB>select * from table(dbms_xplan.display_awr('cdwjdd67x27mh'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cdwjdd67x27mh
--------------------
select * from ( select          *                               from                            GE_TAOBAO_BILL           o 
                 WHERE 1=1                       and
o.CREATED_TIME >= :1            and o.CREATED_TIME < :2                    
                                and o.REC_SITE_ID = :3                                
             and o.STATUS_ID = :4             
             and o.SERVICE_TYPE = :5 ) where rownum
<= 36000

Plan hash value: 647855111

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |       |       |     5 (100)|          |        |
|   1 |  COUNT STOPKEY                       |                           |       |       |            |          |        |
|   2 |   FILTER                             |                           |       |       |            |          |        |
|   3 |    PARTITION RANGE ITERATOR          |                           |     1 |   455 |     5   (0)| 00:00:01 |   KEY |   KEY |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| GE_TAOBAO_BILL            |     1 |   455 |     5   (0)| 00:00:01 |   KEY |   KEY |
|   5 |      INDEX RANGE SCAN                | IDX_TAOBAO_BILL_CR_ISDP_S |     1 |       |     4   (0)| 00:00:01 |   KEY |   KEY |
----------------------------------------------------------------------------------------------------------------------------------

SQL_ID cdwjdd67x27mh
--------------------
select * from ( select          *                               from                            GE_TAOBAO_BILL           o 
                 WHERE 1=1                       and
o.CREATED_TIME >= :1            and o.CREATED_TIME < :2                    
                                and o.REC_SITE_ID = :3                                
             and o.STATUS_ID = :4   
             and o.SERVICE_TYPE = :5 ) where rownum
<= 36000

Plan hash value: 2979024279

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                          |       |       |    13 (100)|          |       ||
|   1 |  COUNT STOPKEY                       |                          |       |       |            |          |       ||
|   2 |   FILTER                             |                          |       |       |            |          |       ||
|   3 |    PARTITION RANGE ITERATOR          |                          |     1 |   455 |    13   (0)| 00:00:01 |   KEY |   KEY |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| GE_TAOBAO_BILL           |     1 |   455 |    13   (0)| 00:00:01 |   KEY |   KEY |
|   5 |      INDEX RANGE SCAN                | IDX_TAOBAO_BILL_CR_REC_S |     4 |       |     9   (0)| 00:00:01 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------------------

这里可以发现cdwjdd67x27mh在数据库中有两个执行计划

通过awr数据,看执行计划的变化情况

10:50:29 sys@Q9DB>select a.INSTANCE_NUMBER,a.snap_id,a.sql_id,a.plan_hash_value,b.begin_interval_time
10:50:30   2  from dba_hist_sqlstat a, dba_hist_snapshot b 
10:50:30   3  where sql_id ='cdwjdd67x27mh' 
10:50:30   4  and a.snap_id = b.snap_id 
10:50:30   5  order by instance_number,begin_interval_time  desc;

INSTANCE_NUMBER    SNAP_ID SQL_ID        PLAN_HASH_VALUE BEGIN_INTERVAL_TIME
--------------- ---------- ------------- --------------- ----------------------------------------
              1      20719 cdwjdd67x27mh      2979024279 12-MAY-14 05.00.12.753 PM
              1      20719 cdwjdd67x27mh       647855111 12-MAY-14 05.00.12.753 PM
              1      20719 cdwjdd67x27mh      2979024279 12-MAY-14 05.00.12.702 PM
              1      20719 cdwjdd67x27mh       647855111 12-MAY-14 05.00.12.702 PM
              1      20718 cdwjdd67x27mh       647855111 12-MAY-14 04.00.24.197 PM
              1      20718 cdwjdd67x27mh      2979024279 12-MAY-14 04.00.24.197 PM
              1      20718 cdwjdd67x27mh       647855111 12-MAY-14 04.00.24.172 PM
              1      20718 cdwjdd67x27mh      2979024279 12-MAY-14 04.00.24.172 PM
              1      20717 cdwjdd67x27mh       647855111 12-MAY-14 03.11.22.251 PM
              1      20717 cdwjdd67x27mh      2979024279 12-MAY-14 03.11.22.251 PM
              1      20717 cdwjdd67x27mh       647855111 12-MAY-14 03.11.22.188 PM
              1      20717 cdwjdd67x27mh      2979024279 12-MAY-14 03.11.22.188 PM
              ………………
              1      20696 cdwjdd67x27mh      2979024279 11-MAY-14 07.00.07.142 PM
              1      20696 cdwjdd67x27mh      2979024279 11-MAY-14 07.00.07.105 PM
              1      20695 cdwjdd67x27mh      2979024279 11-MAY-14 06.00.12.771 PM
              1      20695 cdwjdd67x27mh      2979024279 11-MAY-14 06.00.12.707 PM
              1      20694 cdwjdd67x27mh      2979024279 11-MAY-14 05.00.48.249 PM
              1      20694 cdwjdd67x27mh      2979024279 11-MAY-14 05.00.48.170 PM
              1      20693 cdwjdd67x27mh      2979024279 11-MAY-14 04.00.37.841 PM
              …………
              2      20719 cdwjdd67x27mh      2979024279 12-MAY-14 05.00.12.753 PM
              2      20719 cdwjdd67x27mh      2979024279 12-MAY-14 05.00.12.702 PM
              2      20718 cdwjdd67x27mh      2979024279 12-MAY-14 04.00.24.197 PM
              2      20718 cdwjdd67x27mh      2979024279 12-MAY-14 04.00.24.172 PM
              2      20717 cdwjdd67x27mh      2979024279 12-MAY-14 03.11.22.251 PM
              2      20717 cdwjdd67x27mh      2979024279 12-MAY-14 03.11.22.188 PM

这里可以清晰看到,执行计划在1节点中发生震荡(两个执行计划都有选择),仔细看两个执行计划,会发现就是查询的时候所使用的index不同而已,继续分析两个index

IDX_TAOBAO_BILL_CR_ISDP_S      CREATED_TIME                            1
IDX_TAOBAO_BILL_CR_ISDP_S      IS_DISPART                              2
IDX_TAOBAO_BILL_CR_ISDP_S      STATUS_ID                               3

IDX_TAOBAO_BILL_CR_REC_S       REC_SITE_ID                             1
IDX_TAOBAO_BILL_CR_REC_S       CREATED_TIME                            2
IDX_TAOBAO_BILL_CR_REC_S       STATUS_ID                               3

分析数据分布情况(其他列省略)

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT LAST_ANAL
------------------------------ ------------------------------ ------------ ---------
GE_TAOBAO_BILL                 CREATED_TIME                      287080448 05-MAY-14
GE_TAOBAO_BILL                 REC_SITE_ID                           13176 05-MAY-14
GE_TAOBAO_BILL                 IS_DISPART                                2 05-MAY-14
GE_TAOBAO_BILL                 STATUS_ID                                 7 05-MAY-14

这里可以看到数据库在正常的时候使用IDX_TAOBAO_BILL_CR_REC_S没有问题,但是在某些情况下选择使用IDX_TAOBAO_BILL_CR_ISDP_S这个就有问题,导致逻辑读过高,其实对于该sql语句,是因为IDX_TAOBAO_BILL_CR_REC_S 索引不合理导致,如果创建CREATED_TIME,REC_SITE_ID,STATUS_ID,就不会因为传输的值范围不同而使用IDX_TAOBAO_BILL_CR_ISDP_S的情况。针对该情况,因为表非常大,短时间内无法修改index,只能考虑使用 sql profile固定执行计划

sql profile固定计划

SQL>@coe_xfr_sql_profile.sql cdwjdd67x27mh

Parameter 1:
SQL_ID (required)



PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     2979024279        .011
      647855111       5.164

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 2979024279

Values passed:
~~~~~~~~~~~~~
SQL_ID         : "cdwjdd67x27mh"
PLAN_HASH_VALUE: "2979024279"


Execute coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql
on TARGET system in order to create a custom SQL Profile
with plan 2979024279 linked to adjusted sql_text.


COE_XFR_SQL_PROFILE completed.
SQL>@coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql
SQL>DECLARE
  2  sql_txt CLOB;
  3  h       SYS.SQLPROF_ATTR;
  4  BEGIN
  5  sql_txt := q'[
  6  select * from ( select
  7                  *
  8  
  9                  from
 10  
 11                  GE_TAOBAO_BILL
 12  
 13                  o
 14  
 15                   WHERE 1=1
 16  
 17  
 18  
 19  
 20  
 21  
 22  
 23  
 24  
 25  
 26  
 27                                  and o.CREATED_TIME >= :1
 28  
 29  
 30                                  and o.CREATED_TIME < :2
 31  
 32  
 33                                  and o.REC_SITE_ID = :3
 34  
 35  
 36  
 37  
 38  
 39  
 40  
 41  
 42  
 43  
 44  
 45  
 46  
 47  
 48  
 49                                  and o.STATUS_ID = :4
 50  
 51  
 52  
 53  
 54  
 55  
 56  
 57  
 58  
 59  
 60  
 61  
 62  
 63  
 64                                  and o.SERVICE_TYPE = :5 ) where rownum <= 36000
 65  ]';
 66  h := SYS.SQLPROF_ATTR(
 67  q'[BEGIN_OUTLINE_DATA]',
 68  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 69  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
 70  q'[DB_VERSION('11.2.0.3')]',
 71  q'[FIRST_ROWS]',
 72  q'[OUTLINE_LEAF(@"SEL$F5BB74E1")]',
 73  q'[MERGE(@"SEL$2")]',
 74  q'[OUTLINE(@"SEL$1")]',
 75  q'[OUTLINE(@"SEL$2")]',
 76  q'[INDEX_RS_ASC(@"SEL$F5BB74E1" "O"@"SEL$2" ("GE_TAOBAO_BILL"."REC_SITE_ID" "GE_TAOBAO_BILL"."CREATED_TIME" "GE_TAOBAO_BILL"."STATUS_ID"))]',
 77  q'[END_OUTLINE_DATA]');
 78  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 79  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 80  sql_text    => sql_txt,
 81  profile     => h,
 82  name        => 'coe_cdwjdd67x27mh_2979024279',
 83  description => 'coe cdwjdd67x27mh 2979024279 '||:signature||'',
 84  category    => 'DEFAULT',
 85  validate    => TRUE,
 86  replace     => TRUE,
 87  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
 88  END;
 89  /

PL/SQL procedure successfully completed.

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

            SIGNATURE
---------------------
 18414135509058398362


... manual custom SQL Profile has been created


COE_XFR_SQL_PROFILE_cdwjdd67x27mh_2979024279 completed

固定执行计划后,系统负载恢复正常
load恢复正常,单个进程占用cpu 也正常

top - 18:25:29 up 123 days,  4:25,  3 users,  load average: 17.59, 16.72, 16.10
Tasks: 1559 total,   6 running, 1551 sleeping,   0 stopped,   2 zombie
Cpu(s):  6.2%us,  1.2%sy,  0.0%ni, 84.3%id,  7.4%wa,  0.1%hi,  0.9%si,  0.0%st
Mem:  264253752k total, 262395300k used,  1858452k free,   305928k buffers
Swap: 33554424k total,   467420k used, 33087004k free, 66811412k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                  
 3010 oracle    15   0  150g  26m  21m S 23.6  0.0   0:08.60 oracleq9db2 (LOCAL=NO)   
26936 oracle    15   0  150g  33m  28m S 22.9  0.0   0:12.53 oracleq9db2 (LOCAL=NO)   
 9691 oracle    15   0  150g  34m  28m D 21.0  0.0   0:48.73 oracleq9db2 (LOCAL=NO)   
 9694 oracle    15   0  150g  34m  28m S 17.4  0.0   0:45.74 oracleq9db2 (LOCAL=NO)   
14366 oracle    15   0  150g  33m  28m R 17.4  0.0   0:25.11 oracleq9db2 (LOCAL=NO)   
 2471 oracle    -2   0  150g 180m  37m S 16.7  0.1  10795:55 ora_lms3_q9db2           
 2463 oracle    -2   0  150g 180m  37m S 15.7  0.1  10648:38 ora_lms1_q9db2           
 2459 oracle    -2   0  150g 180m  37m S 14.8  0.1  10726:42 ora_lms0_q9db2           
 2467 oracle    -2   0  150g 180m  36m S 14.8  0.1  10980:33 ora_lms2_q9db2           
13324 oracle    15   0  150g  31m  26m S 14.1  0.0   0:22.75 oracleq9db2 (LOCAL=NO)   
16740 oracle    15   0  150g  34m  28m R 13.4  0.0   0:43.85 oracleq9db2 (LOCAL=NO)   
 1908 oracle    15   0  150g  27m  22m S 11.8  0.0   0:03.95 oracleq9db2 (LOCAL=NO)   
 9689 oracle    15   0  150g  33m  27m S 11.8  0.0   0:46.01 oracleq9db2 (LOCAL=NO)   
19410 oracle    15   0  150g  31m  26m S 11.8  0.0   0:43.18 oracleq9db2 (LOCAL=NO)   
14102 oracle    15   0  150g  31m  25m S 11.5  0.0   2:17.23 oracleq9db2 (LOCAL=NO)   
 1914 oracle    15   0  150g  29m  24m S 11.1  0.0   0:04.77 oracleq9db2 (LOCAL=NO)   
31106 oracle    15   0  150g  34m  28m S  9.8  0.0   1:24.85 oracleq9db2 (LOCAL=NO)   
31139 oracle    15   0  150g  30m  24m S  9.8  0.0   1:21.75 oracleq9db2 (LOCAL=NO)   
 2498 oracle    15   0  150g  42m  35m S  7.9  0.0   3838:43 ora_lgwr_q9db2           
28108 oracle    15   0  150g  36m  29m S  7.9  0.0   0:18.19 oracleq9db2 (LOCAL=NO)   
 2392 oracle    15   0  150g  35m  17m S  7.5  0.0   5304:57 ora_lmd0_q9db2           

含is null sql语句优化

原sql语句与执行计划

SQL> set autot trace
SQL> WITH AL AS (SELECT * FROM XIFENFEI_LOG WHERE CLEAR_TIME IS NULL) 
   2 SELECT SWP.ID SWP_ID, AL.* FROM AL FULL OUTER JOIN XIFENFEI_LOG_SWAP SWP ON SWP.ID = AL.ID;

54 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 888046630

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |    24 | 11064 | 24658   (2)| 00:04:56 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |     |
|   2 |   LOAD AS SELECT           |                             |       |       |            |     |
|*  3 |    TABLE ACCESS FULL       | XIFENFEI_LOG                |    23 |  2576 | 24652   (2)| 00:04:56 |
|   4 |   VIEW                     |                             |    24 | 11064 |     6  (17)| 00:00:01 |
|   5 |    UNION-ALL               |                             |       |       |            |     |
|   6 |     NESTED LOOPS OUTER     |                             |    23 | 10465 |     2   (0)| 00:00:01 |
|   7 |      VIEW                  |                             |    23 | 10304 |     2   (0)| 00:00:01 |
|   8 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6605_51B4E691 |    23 |  2576 |     2   (0)| 00:00:01 |
|*  9 |      INDEX UNIQUE SCAN     | XIFENFEI_LOG_SWP_PK         |     1 |     7 |     0   (0)| 00:00:01 |
|* 10 |     HASH JOIN ANTI         |                             |     1 |    20 |     4  (25)| 00:00:01 |
|  11 |      INDEX FULL SCAN       | XIFENFEI_LOG_SWP_PK         |    20 |   140 |     1   (0)| 00:00:01 |
|  12 |      VIEW                  |                             |    23 |   299 |     2   (0)| 00:00:01 |
|  13 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6605_51B4E691 |    23 |  2576 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("CLEAR_TIME" IS NULL)
   9 - access("SWP"."ID"(+)="AL"."ID")
  10 - access("SWP"."ID"="AL"."ID")


Statistics
----------------------------------------------------------
          2  recursive calls
          8  db block gets
     111504  consistent gets
          1  physical reads
        692  redo size
       8075  bytes sent via SQL*Net to client
        502  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         54  rows processed

这里很明显占用资源多,执行时间长的都在XIFENFEI_LOG表的全表扫描上,而该表的where 条件是CLEAR_TIME is null.


分析CLEAR_TIME 列null值的分布

SQL> SELECT count(*) FROM XIFENFEI_LOG WHERE CLEAR_TIME IS NULL;

  COUNT(*)
----------
        48

SQL> SELECT count(*) FROM XIFENFEI_LOG WHERE CLEAR_TIME IS not NULL;

  COUNT(*)
----------
   6899211

通过这里分析可以知道,CLEAR_TIME is null的值非常少,如果能够创建一个index,取到CLEAR_TIME 列null的值,那效率将非常搞.但是有oracle index知识的人都知道,B树index是不包含null列,因此一般性index无法满足该需求.这里思考创建含常数的复合index,而且把CLEAR_TIME放在前面,因为后面的常数一定存在,因此CLEAR_TIME中含有null的记录也就包含在该复合index中.

创建含常数复合index

SQL> create index ind_XIFENFEI_LOG_null on XIFENFEI_LOG (CLEAR_TIME,0) online;

Index created.

再次查看执行计划

SQL> WITH AL AS (SELECT * FROM XIFENFEI_LOG WHERE CLEAR_TIME IS NULL) 
  2  SELECT SWP.ID SWP_ID, AL.* FROM AL FULL OUTER JOIN XIFENFEI_LOG_SWAP SWP ON SWP.ID = AL.ID;

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2359331571

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                             |    24 | 11064 |    25   (4)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION    |                             |       |       |            |          |
|   2 |   LOAD AS SELECT              |                             |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| XIFENFEI_LOG                |    23 |  2576 |    19   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IND_XIFENFEI_LOG_NULL       |    23 |       |     3   (0)| 00:00:01 |
|   5 |   VIEW                        |                             |    24 | 11064 |     6  (17)| 00:00:01 |
|   6 |    UNION-ALL                  |                             |       |       |            |          |
|   7 |     NESTED LOOPS OUTER        |                             |    23 | 10465 |     2   (0)| 00:00:01 |
|   8 |      VIEW                     |                             |    23 | 10304 |     2   (0)| 00:00:01 |
|   9 |       TABLE ACCESS FULL       | SYS_TEMP_0FD9D660D_51B4E691 |    23 |  2576 |     2   (0)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN        | XIFENFEI_LOG_SWP_PK         |     1 |     7 |     0   (0)| 00:00:01 |
|* 11 |     HASH JOIN ANTI            |                             |     1 |    20 |     4  (25)| 00:00:01 |
|  12 |      INDEX FULL SCAN          | XIFENFEI_LOG_SWP_PK         |    20 |   140 |     1   (0)| 00:00:01 |
|  13 |      VIEW                     |                             |    23 |   299 |     2   (0)| 00:00:01 |
|  14 |       TABLE ACCESS FULL       | SYS_TEMP_0FD9D660D_51B4E691 |    23 |  2576 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("CLEAR_TIME" IS NULL)
  10 - access("SWP"."ID"(+)="AL"."ID")
  11 - access("SWP"."ID"="AL"."ID")


Statistics
----------------------------------------------------------
          2  recursive calls
          8  db block gets
         33  consistent gets
          1  physical reads
        648  redo size
       7688  bytes sent via SQL*Net to client
        502  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

这里可以发现,该sql使用了创建的含常数的复合index,sql执行时间从4分56秒,提高到现在的1秒钟,逻辑读从当初的111504减小到现在的33,巧用含常数的复合索引使得sql执行效率极大提高.

一次数据库优化全过程分析

最近对客户的一个数据库进行了优化,在本次优化过程中,主要涉及以下方面:
1. 确保系统有足够的内存,处理方法配置Hugepage,减小SGA
2. 优化因为主键表频繁插入引起的user$,con$,cdef$递归查询sql

SQL> select c.name, u.name from con$ c, cdef$ cd, user$ u where 
   2 c.con# = cd.con# and cd.enabled = :1 and c.owner# = u.user#;


Execution Plan
----------------------------------------------------------
Plan hash value: 2409458995

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |  3820 |   164K|    38   (6)| 00:00:01 |
|*  1 |  HASH JOIN          |       |  3820 |   164K|    38   (6)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | USER$ |    64 |   896 |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |       |  3820 |   111K|    34   (3)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| CDEF$ |  3820 | 34380 |    25   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| CON$  |  6368 |   130K|     8   (0)| 00:00:01 |
-----------------------------------------------------------------------------

具体见:一次数据库优化全过程分析