基于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 以及使用了不同的执行计划
--最早的一个是走索引范围扫描,一个是全表扫描
--下面直接从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脚本,请参考:Oracle 测试常用表BIG_TABLE b、alter table move 方式用于实现段收缩,移动高水位,但不会释放申请的空间,以及导致索引失效 c、对于历史SQL语句,需要执行snapshot之后,才会被填充到DBA_HIST_SQL_PLAN、DBA_HIST_SQLSTAT、DBA_HIST_SNAPSHOT数据字典中 d、如果你的测试无法获得历史SQL语句及其执行计划,通常是由于awr阀值设置所致,可参考:Oracle AWR 阙值影响历史执行计划 e、历史SQL语句的执行计划也可以通过$ORACLE_HOME/rdbms/admin/awrsqrpt.sql来生成txt或html文件 f、引起同一SQL执行计划发生变化的情形很多,如统计信息的缺失,索引失效,不同级别的参数发生变化等 h、对于实例,会话,语句级别环境变化导致同一SQL执行计划发变异,也可以对此跟踪。参考:使用优化器性能视图获取SQL语句执行环境