Oracle历史SQL语句执行计划的对比与分析
基于CBO优化器的环境中，SQL执行计划的生成依赖于统计信息的真实与完整。如列的离散度，列上的直方图，索引的可用性，索引上的聚簇因子。当这些信息是真实完整的情况下，CBO优化器通常都可以制定最优的执行计划。也正因此CBO优化器也灵活，难以控制，任一信息的不真实或缺失都可能导致执行计划发生变化而产生多个版本。经常碰到的情形是之前的某个SQL语句前阵子还不是TOP SQL，而最近变成了TOP SQL。或者说之前尽管是TOP SQL但，但最近尽然成了TOP 1。对于此情形，我们可以比对SQL语句的历史执行计划进行分析是何种原因导致SQL变慢或执行计划发生变化。下面通过例子来模拟SQL执行计划变异的情形。
1、创建演示环境
[sql]
--演示环境
scott@SYBO2SZ> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
--创建1000000万记录的表
scott@SYBO2SZ> @cr_big_tb
check total rows for big_table
====================================
COUNT(*)
----------
1000000
--为表创建索引
scott@SYBO2SZ> create index i_big_tb_owner on big_table(owner);
sys@SYBO2SZ> conn / as sysdba;
sys@SYBO2SZ> select snap_id from dba_hist_snapshot order by snap_id;
SNAP_ID
----------
30
31
--清除awr的历史记录,shared pool及buffer cache
sys@SYBO2SZ> exec dbms_workload_repository.drop_snapshot_range(30,31);
sys@SYBO2SZ> alter system flush shared_pool;
sys@SYBO2SZ> alter system flush buffer_cache;
--清除dba_hist_sql_plan视图,实际上清除wrh$_sql_plan,wrh$_sqltext,wrh$_sqlstat
sys@SYBO2SZ> truncate table wrh$_sql_plan;
--清除dba_hist_sql_sqltext以及dba_hist_sqlstat视图
sys@SYBO2SZ> truncate table wrh$_sqltext;
sys@SYBO2SZ> truncate table wrh$_sqlstat;
sys@SYBO2SZ> select count(*) from dba_hist_sql_plan;
COUNT(*)
----------
0
sys@SYBO2SZ> select count(*) from dba_hist_sqltext;
COUNT(*)
----------
0
2、生成历史SQL及其执行计划
[sql]
sys@SYBO2SZ> conn scott/tiger
scott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';
COUNT(*)
----------
43560
scott@SYBO2SZ> @my_last_sql
ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE SQL_TEXT
---------------- ---------- ------------- ------------ ---------- ---------------------------------------------------------
000000007B9BB7D0 243468085 4hqyjwh7861tp 3 0 select count(*) from big_table where owner='GOEX_ADMIN'
--从awr中查询sql的执行计划，由于没有生成快照，所以无其执行计划
scott@SYBO2SZ> @sql_plan_disp_awr
Enter value for input_sqlid: 4hqyjwh7861tp
no rows selected
--创建快照
scott@SYBO2SZ> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
--查看SQL的历史执行计划
scott@SYBO2SZ> @sql_plan_disp_awr
Enter value for input_sqlid: 4hqyjwh7861tp
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 4hqyjwh7861tp
--------------------
select count(*) from big_table where owner='GOEX_ADMIN'
Plan hash value: 334839806
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 139 (100)| |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
| 2 | INDEX RANGE SCAN| I_BIG_TB_OWNER | 10073 | 167K| 139 (0)| 00:00:02 |
------------------------------------------------------------------------------------
3、生成不同的历史SQL并对比执行计划
[sql]
--对表big_table进行move操作
scott@SYBO2SZ> alter table big_table move;
--检查其表上的索引，如下，索引已经失效
scott@SYBO2SZ> @idx_info
Enter value for owner: scott
Enter value for table_name: big_table
TABLE_NAME INDEX_NAME CL_NAM CL_POS STATUS IDX_TYP DSCD
------------------------- ------------------- -------------------- ------ -------- --------------- ----
BIG_TABLE BIG_TABLE_PK ID 1 UNUSABLE NORMAL ASC
BIG_TABLE I_BIG_TB_OWNER OWNER 1 UNUSABLE NORMAL ASC
--再次执行与之前相同的SQL语句
scott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';
COUNT(*)
----------
43560
