基于CBO优化器的环境中，SQL执行计划的生成依赖于统计信息的真实与完整。如列的离散度，列上的直方图，索引的可用性，索引上的聚簇因子。当这些信息是真实完整的情况下，CBO优化器通常都可以制定最优的执行计划。也正因此CBO优化器也灵活，难以控制，任一信息的不真实或缺失都可能导致执行计划发生变化而产生多个版本。经常碰到的情形是之前的某个SQL语句前阵子还不是TOP SQL，而最近变成了TOP SQL。或者说之前尽管是TOP SQL但，但最近尽然成了TOP 1。对于此情形，我们可以比对SQL语句的历史执行计划进行分析是何种原因导致SQL变慢或执行计划发生变化。下面通过例子来模拟SQL执行计划变异的情形。
1、创建演示环境
--演示环境 BANNER --创建1000000万记录的表 check total rows for big_table --为表创建索引 sys@SYBO2SZ> conn / as sysdba; sys@SYBO2SZ> select snap_id from dba_hist_snapshot order by snap_id; SNAP_ID --清除awr的历史记录,shared pool及buffer cache 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 --清除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(*) sys@SYBO2SZ> select count(*) from dba_hist_sqltext; COUNT(*) |
2、生成历史SQL及其执行计划
sys@SYBO2SZ> conn scott/tiger scott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN'; COUNT(*) scott@SYBO2SZ> @my_last_sql ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE SQL_TEXT --从awr中查询sql的执行计划，由于没有生成快照，所以无其执行计划 no rows selected --创建快照 PL/SQL procedure successfully completed. --查看SQL的历史执行计划 PLAN_TABLE_OUTPUT Plan hash value: 334839806 ------------------------------------------------------------------------------------ |
