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

来源：互联网发布：java创建一个学生类编辑：程序博客网时间：2024/04/30 18:54

基于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_tbcheck 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$_sqlstatsys@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(*)----------         0sys@SYBO2SZ> select count(*) from dba_hist_sqltext;  COUNT(*)----------         0

2、生成历史SQL及其执行计划

sys@SYBO2SZ> conn scott/tigerscott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';  COUNT(*)----------     43560scott@SYBO2SZ> @my_last_sqlADDRESS          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_awrEnter value for input_sqlid: 4hqyjwh7861tpno rows selected--创建快照scott@SYBO2SZ> exec dbms_workload_repository.create_snapshot();PL/SQL procedure successfully completed.--查看SQL的历史执行计划scott@SYBO2SZ> @sql_plan_disp_awrEnter value for input_sqlid: 4hqyjwh7861tpPLAN_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_infoEnter value for owner: scottEnter value for table_name: big_tableTABLE_NAME                INDEX_NAME          CL_NAM               CL_POS STATUS   IDX_TYP         DSCD------------------------- ------------------- -------------------- ------ -------- --------------- ----BIG_TABLE                 BIG_TABLE_PK        ID                        1 UNUSABLE NORMAL          ASCBIG_TABLE                 I_BIG_TB_OWNER      OWNER                     1 UNUSABLE NORMAL          ASC--再次执行与之前相同的SQL语句scott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';  COUNT(*)----------     43560scott@SYBO2SZ> @my_last_sqlADDRESS          HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT---------------- ---------- ------------- ------------ ---------- ----------------------------------------------------------000000007B9BB7D0  243468085 4hqyjwh7861tp            3          0 select count(*) from big_table where owner='GOEX_ADMIN'--创建一个新的快照，使之成为历史SQLscott@SYBO2SZ> exec dbms_workload_repository.create_snapshot();--查看SQL的执行计划scott@SYBO2SZ> @sql_plan_disp_awrEnter value for input_sqlid: 4hqyjwh7861tpPLAN_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,idEnter 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:166 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 stateORA-06512: at "SYS.DBMS_STATS", line 13182ORA-06512: at "SYS.DBMS_STATS", line 13202ORA-06512: at line 1--上面再收集统计信息时，提示索引不可用，需要先rebulidscott@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语句执行环境

更多参考

有关Oracle RAC请参考
     使用crs_setperm修改RAC资源的所有者及权限
     使用crs_profile管理RAC资源配置文件
     RAC 数据库的启动与关闭
     再说 Oracle RAC services
     Services in Oracle Database 10g
     Migrate datbase from single instance to Oracle RAC
     Oracle RAC 连接到指定实例
     Oracle RAC 负载均衡测试(结合服务器端与客户端)
     Oracle RAC 服务器端连接负载均衡(Load Balance)
     Oracle RAC 客户端连接负载均衡(Load Balance)
     ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)
     ORACLE RAC 监听配置 (listener.ora tnsnames.ora)
    配置 RAC 负载均衡与故障转移
     CRS-1006 , CRS-0215 故障一例
     基于Linux (RHEL 5.5) 安装Oracle 10g RAC
     使用 runcluvfy 校验Oracle RAC安装环境

有关Oracle 网络配置相关基础以及概念性的问题请参考：
     配置非默认端口的动态服务注册
     配置sqlnet.ora限制IP访问Oracle
     Oracle 监听器日志配置与管理
     设置 Oracle 监听器密码(LISTENER)
     配置ORACLE 客户端连接到数据库

有关基于用户管理的备份和备份恢复的概念请参考
     Oracle 冷备份
     Oracle 热备份
     Oracle 备份恢复概念
     Oracle 实例恢复
     Oracle 基于用户管理恢复的处理
     SYSTEM 表空间管理及备份恢复
     SYSAUX表空间管理及恢复
     Oracle 基于备份控制文件的恢复(unsing backup controlfile)

有关RMAN的备份恢复与管理请参考
     RMAN 概述及其体系结构
     RMAN 配置、监控与管理
     RMAN 备份详解
     RMAN 还原与恢复
     RMAN catalog 的创建和使用
     基于catalog 创建RMAN存储脚本
     基于catalog 的RMAN 备份与恢复
     RMAN 备份路径困惑
     使用RMAN实现异机备份恢复(WIN平台)
     使用RMAN迁移文件系统数据库到ASM
     linux 下RMAN备份shell脚本
     使用RMAN迁移数据库到异机

有关ORACLE体系结构请参考
     Oracle 表空间与数据文件
     Oracle 密码文件
     Oracle 参数文件
     Oracle 联机重做日志文件(ONLINE LOG FILE)
     Oracle 控制文件(CONTROLFILE)
     Oracle 归档日志
     Oracle 回滚(ROLLBACK)和撤销(UNDO)
     Oracle 数据库实例启动关闭过程
     Oracle 10g SGA 的自动化管理
     Oracle 实例和Oracle数据库(Oracle体系结构)