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 以及使用了不同的执行计划
--最早的一个是走索引范围扫描，一个是全表扫描

--下面直接从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_TABLEb、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体系结构) 

时间： 2024-11-03 09:31:02

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

使用 EXPLAIN PLAN 获取SQL语句执行计划

     SQL查询语句的性能从一定程度上影响整个数据库的性能.很多情况下,数据库性能的低下差不多都是不良SQL语句所引起.而SQL语句的执行 计划则决定了SQL语句将会采用何种方式从数据库提取数据并返回给客户端,本文描述的将是如何通过EXPLAIN PLAN 获取SQL语句执行计划来获 取SQL语句的执行计划. 一.获取SQL语句执行计划的方式     1. 使用explain plan 将执行计划加载到表plan_table,然后查询该表来获取预估的执行计划      2. 查询动态性能视图

跟踪oracle中sql语句执行过程及相关知识拓展

select * from v$sqlarea; select * from v$sqlarea where first_load_time>'2010-11-27/09:30:00';         这个方法查询结果每条记录显示一条查询语句,且只能查询sql_text小于1000字符的,多余的会被截断.         改进一下: select * from v$sqlarea where first_load_time>'2010-11-27/09:30:00' and sql_text

Oracle的SQL语句执行效率问题查找与解决方法

一.识别占用资源较多的语句的方法(4种方法) 1.测试组和最终用户反馈的与反应缓慢有关的问题. 2.利用V_$SQLAREA视图提供了执行的细节.(执行.读取磁盘和读取缓冲区的次数) •数据列 EXECUTIONS:执行次数 DISK_READS:读盘次数 COMMAND_TYPE:命令类型(3:select,2:insert;6:update;7delete;47:pl/sql程序单元) OPTIMIZER_MODE:优化方式 SQL_TEXT:Sql语句 SHARABLE_MEM:占用sha

MyBatis MapperProvider MessageFormat拼接批量SQL语句执行报错的原因分析及解决办法_MsSql

最近在项目中有这么一段代码:下载服务器基础业务数据进行本地批量插入操作,因项目中使用mybatis进行持久化操作,故直接考虑使用mybatis的批量插入功能. 1.以下是Mapper接口的部分代码 public interface PrintMapper { @InsertProvider(type = PrintMapperProvider.class,method = "insertAllLotWithVehicleCode4H2") void insertAllLotWithVe

使用优化器性能视图获取SQL语句执行环境

    Oracle SQL语句的运行环境分为多个不同的层次,主要包括实例级别,会话级别,语句级别,其优先级依次递增.即语句级别的执行环境具有最高的优先权,会话级别次之,实例级别最低.反过来,实例级别的环境设置影响全局,而会话级别的则影响当前会话,语句级别的设置当然也就只影响当前语句.由此可知,运行环境中每一个环节的参数都对最终的数据库性能或所执行的SQL语句有直接的影响.因此在对数据库优化或调试SQL时,获得当前SQL语句运行环境显得尤为重要.为此,Oracle提供了三个重要的视图来获取不同级

【OUTLINE】使用Oracle Outline技术暂时锁定SQL的执行计划

  Oracle的Outline技术可以在特殊情况下保证执行计划的稳定性.在极端情况下可以使用此项技术实现暂时锁定执行计划的目的.  主要使用场景如下:  ①短时间内无法完成SQL的优化任务,此时可以使用outline暂时锁定SQL执行计划:  ②在CBO优化模式下,当统计信息出现问题时,会导致执行计划出现异常变化,此时可以使用outline暂时调整SQL执行计划:  ③由于数据库的bug导致SQL的执行计划出现异常,使用outline锁定执行计划.   记录一下关于outline的使用方法,供

怎样看oracle查询语句执行计划?

oracle|语句|执行 SQLPLUS的AutoTrace是分析SQL的执行计划,执行效率的一个非常简单方便的工具,在绝大多数情况下,也是非常有用的工具. 1.如何设置和使用AUTOTRACE SQL> connect / as sysdba SQL> @?/rdbms/admin/utlxplan.sql Table created. SQL> create public synonym plan_table for plan_table; Synonym created. SQL&

Oracle数据库如何搜集指定SQL的执行计划和解决过程中的ORA-00904错误

  Oracle 数据库如何搜集指定SQL的执行计划和解决过程中的ORA-00904错误 (版权声明,本人原创或者翻译的文章如需转载,如转载用于个人学习,请注明出处;否则请与本人联系,违者必究) 如何收集指定SQL的执行计划对开发人员来说非常重要的,这里记录下基础的收集方式,以便查阅和其他人参考. 1. 链接到sqlplus,如下图 2. 执行下面两个的命令之一 set autotrace on; (说明:打开自动分析统计,并显示SQL语句的运行结果) 3. 输入并执行要搜集执行计划的SQL语句

ORACLE从共享池删除指定SQL的执行计划

Oracle 11g在DBMS_SHARED_POOL包中引入了一个名为PURGE的新存储过程,用于从对象库缓存中刷新特定对象,例如游标,包,序列,触发器等.也就是说可以删除.清理特定SQL的执行计划,这样在特殊情况下,就避免你要将整个SHARED POOL清空的危险情况.例如某个SQL语句由于优化器产生了错误的执行计划,我们希望优化器重新解析,生成新的执行计划,必须先将SQL的执行计划从共享池中刷出或将其置为无效,那么优化器才能将后续SQL进行硬解析.生成新的执行计划.这在以前只能使用清空共享