oracle 历史sql 参数,Oracle 历史SQL语句执行计划的对比与分析(2)

--下面直接从dba_hist_sql_plan查看sql语句的执行计划 --该视图记录了所有被awr快照捕获的所有历史sql的执行计划以及执行计划的生成时间 scott@SYBO2SZ run sql_plan_his 1 SELECT id, 2 operation, 3 options, 4 o

--下面直接从dba_hist_sql_plan查看sql语句的执行计划　　--该视图记录了所有被awr快照捕获的所有历史sql的执行计划以及执行计划的生成时间

scott@SYBO2SZ> run sql_plan_his

1  SELECT id,

2           operation,

3           options,

4           object_name,

5           bytes,

6           cpu_cost,                -----> Author : Robinson

7           io_cost,                 -----> Blog   : http://blog.csdn.net/robinson_0612

8           timestamp

9      FROM dba_hist_sql_plan

10     WHERE sql_id = '&input_sql_id'

11* ORDER BY timestamp,id

Enter value for input_sql_id: 4hqyjwh7861tp

ID OPERATION                 OPTIONS       OBJECT_NAME            BYTES   CPU_COST    IO_COST TIMESTAMP

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

0 SELECT STATEMENT                                                                           20130517 11:23:20

1 SORT                      AGGREGATE                               17                       20130517 11:23:20

2 INDEX                     RANGE SCAN    I_BIG_TB_OWNER        171241    1789880        139 20130517 11:23:20

0 SELECT STATEMENT                                                                           20130517 11:27:16

1 SORT                      AGGREGATE                               17                       20130517 11:27:16

2 TABLE ACCESS              FULL          BIG_TABLE             171241  325825194       3203 20130517 11:27:16

6 rows selected.

4、修正SQL执行计划

--如前面可知，由于索引不可用导致了SQL语句执行了全表扫描。

--事实上导致全表扫描的问题很多，若使用谓词列函数，谓词列数据类型转换，使用不等于，以及谓词列参与计算等，不一一列出

--针对上面的情形，我们应当收集统计信息以及重建索引

scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);

BEGIN dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true); END;

*

ERROR at line 1:

ORA-20000: index "SCOTT"."BIG_TABLE_PK"  or partition of such index is in unusable state

ORA-06512: at "SYS.DBMS_STATS", line 13182

ORA-06512: at "SYS.DBMS_STATS", line 13202

ORA-06512: at line 1

--上面再收集统计信息时，提示索引不可用，需要先rebulid

scott@SYBO2SZ> alter index i_big_tb_owner rebuild nologging;

scott@SYBO2SZ> alter index big_table_pk rebuild nologging;

scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);

--下面我们再次执行原SQL以及，由下可知，SQL已经使用了最优的执行计划

scott@SYBO2SZ> set autot trace exp;

scott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';

Execution Plan

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

Plan hash value: 334839806

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

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

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

|   0 | SELECT STATEMENT  |                |     1 |     6 |   108   (1)| 00:00:02 |

|   1 |  SORT AGGREGATE   |                |     1 |     6 |            |          |

|*  2 |   INDEX RANGE SCAN| I_BIG_TB_OWNER | 44750 |   262K|   108   (1)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

2 - access("OWNER"='GOEX_ADMIN')

5、后记

a、示例中创建的big_table脚本，

b、alter table move 方式用于实现段收缩，移动高水位，但不会释放申请的空间，以及导致索引失效

c、对于历史SQL语句，需要执行snapshot之后，才会被填充到DBA_HIST_SQL_PLAN、DBA_HIST_SQLSTAT、DBA_HIST_SNAPSHOT数据字典中

d、如果你的测试无法获得历史SQL语句及其执行计划，通常是由于awr阀值设置所致

e、历史SQL语句的执行计划也可以通过$ORACLE_HOME/rdbms/admin/awrsqrpt.sql来生成txt或html文件

f、引起同一SQL执行计划发生变化的情形很多，如统计信息的缺失，索引失效，不同级别的参数发生变化等

h、对于实例，会话，语句级别环境变化导致同一SQL执行计划发变异，也可以对此跟踪。