Oracle 历史SQL语句执行计划的对比与分析

ㄣ负二代

-

基于CBO优化器的环境中，SQL执行计划的生成依赖于统计信息的真实与完整。如列的离散度，列上的直方图，索引的可用性，索引上的聚簇因子。当这 些信息是真实完整的情况下，CBO优化器通常都可以制定最优的执行计划。也正因此CBO优化器也灵活，难以控制，任一信息的不真实或缺失都可能导致执行计 划发生变化而产生多个版本。经常碰到的情形是之前的某个SQL语句前阵子还不是TOP SQL，而最近变成了TOP SQL。或者说之前尽管是TOP SQL但，但最近尽然成了TOP 1。对于此情形，我们可以比对SQL语句的历史执行计划进行分析是何种原因导致SQL变慢或执行计划发生变化。下面通过例子来模拟SQL执行计划变异的情 形。　　1、创建演示环境
　　--演示环境
　　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及其执行计划
　　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并对比执行计划
　　--对表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
　　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'
　　--创建一个新的快照，使之成为历史SQL
　　scott@SYBO2SZ> exec dbms_workload_repository.create_snapshot();
　　--查看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 |
　　------------------------------------------------------------------------------------
　　SQL_ID 4hqyjwh7861tp
　　--------------------
　　select count(*) from big_table where owner='GOEX_ADMIN'
　　Plan hash value: 599409829
　　--------------------------------------------------------------------------------
　　| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
　　--------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT   |           |       |       |  3221 (100)|          |
　　|   1 |  SORT AGGREGATE    |           |     1 |    17 |            |          |
　　|   2 |   TABLE ACCESS FULL| BIG_TABLE | 10073 |   167K|  3221   (1)| 00:00:39 |
　　--------------------------------------------------------------------------------
　　28 rows selected.
　　--从上面的查询结果可以看到，同一条历史SQL语句有不同的plan_hash_value 以及使用了不同的执行计划
　　--最早的一个是走索引范围扫描，一个是全表扫描

宇智波·佐助

完全看不懂啊