`

10G中查看历史执行计划信息[转]

历史执行计划DBA_HIST_SQLTEXTDBA_HIST_SQL_PLANDBA_HIST_SQLSTATDBA_HIST_SNAPSHOT 
阅读更多

现在总结下10G的，使用的是AWR报告中的信息，主要是查询以下四个视图：
DBA_HIST_SQLTEXT、DBA_HIST_SQL_PLAN、DBA_HIST_SQLSTAT、DBA_HIST_SNAPSHOT。

Sometimes you will receive complains from users that something that has been running ok for ages on a production server, suddenly is running very very slowly. In 10g, AWR gives you the ability to go and check and possibly compare the execution path of a query at different times in the past.

select DBID, SQL_ID, SQL_TEXT from dba_hist_sqltext where sql_text like ‘%your query%’;

DBID      | SQL_ID      | SQL_TEXT |

———-+————-+—————————+-

1234567890|12345xxxxxxxx|UPDATE STAGING SET BLAH    |

We know that yesterday it was running fine, but today it’s not, and users have confirmed that data volumes are identical between the two dates

select s.begin_interval_time, s.end_interval_time , q.snap_id, q.dbid, q.sql_id, q.plan_hash_value, q.optimizer_cost, q.optimizer_mode

from dba_hist_sqlstat q, dba_hist_snapshot s

where q.dbid = 1234567890 and q.sql_id = ‘12345xxxxxxxx’

and q.snap_id = s.snap_id

and s.begin_interval_time between sysdate-2 and sysdate

order by s.snap_id desc;

When you find this, take note of the PLAN_HASH_VALUE from the 2 different times that you want to compare

Actually the 3 parameters that you need are:

·         DBID

·         SQL_ID

·         PLAN_HASH_VALUE

 

And to check the execution plan, run the following query:

 

select id, operation, options, object_name, cost

from dba_hist_sql_plan

where dbid = 1234567890 and sql_id = ‘12345xxxxxxxx’

and plan_hash_value = <plan_hash_value1>

 

compare that with the second one

select id, operation, options, object_name, cost

from dba_hist_sql_plan

where dbid = 1234567890 and sql_id = ‘12345xxxxxxxx’

and plan_hash_value = <plan_hash_value2>


下面来看看实验：
Session 1：

SQL> select max(sid) from v$mystat;
 
  MAX(SID)
----------
       542
 
SQL> create table test_plan as
  2  select object_id,object_name from dba_objects;
 
Table created.
 
SQL> exec dbms_workload_repository.create_snapshot();
 
PL/SQL procedure successfully completed.
 
SQL> select * from test_plan where object_id=10 and object_name='C_USER#';
 
 OBJECT_ID OBJECT_NAME
---------- ------------------------------
        10 C_USER#
 
SQL> exec dbms_workload_repository.create_snapshot();
 
PL/SQL procedure successfully completed.
 
SQL> select * from test_plan where object_id=10 and object_name='C_USER#';
 
 OBJECT_ID OBJECT_NAME
---------- ------------------------------
        10 C_USER#

Session 2：

SQL> select sid,SQL_HASH_VALUE,SQL_ID,PREV_HASH_VALUE,PREV_SQL_ID from v$session
  2  where sid=542;
 
       SID SQL_HASH_VALUE SQL_ID        PREV_HASH_VALUE PREV_SQL_ID
---------- -------------- ------------- --------------- -------------
       542              0                    2397140960 36qmvdf7f2xz0
 
SQL> select sql_id,
  2         plan_hash_value,
  3         id,
  4         operation,
  5         options,
  6         object_owner,
  7         object_name,
  8         depth,
  9         cost,
 10         timestamp
 11    from DBA_HIST_SQL_PLAN
 12   where sql_id = '36qmvdf7f2xz0';
 
SQL_ID        PLAN_HASH_VALUE  ID OPERATION         OPTIONS  OBJECT_OWN OBJECT_NAME  DEPTH   COST TIMESTAMP
------------- --------------- --- ----------------- -------- ---------- ------------ ----- ------ -------------------
36qmvdf7f2xz0      2068845065   0 SELECT STATEMENT                                       0     24 2009-04-08 11:27:42
36qmvdf7f2xz0      2068845065   1 TABLE ACCESS      FULL     SYS        TEST_PLAN        1     24 2009-04-08 11:27:42

可以看到DBA_HIST_SQL_PLAN已经记录了执行计划的信息。此时，如果在object_id列上建索引，使执行计划发生变化：
Session 1：

SQL> create index ind_id on test_plan(object_id);
 
Index created.
 
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYS',tabname=>'TEST_PLAN',method_opt=>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
 
PL/SQL procedure successfully completed.
 
SQL> set autotrace on
SQL> select * from test_plan where object_id=10 and object_name='C_USER#';
 
 OBJECT_ID OBJECT_NAME
---------- ------------------------------
        10 C_USER#
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2839240733
 
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    28 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST_PLAN |     1 |    28 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_ID    |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("OBJECT_NAME"='C_USER#')
   2 - access("OBJECT_ID"=10)
 
 
Statistics
----------------------------------------------------------
        252  recursive calls
          0  db block gets
         68  consistent gets
          0  physical reads
          0  redo size
        593  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         15  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL> set autotrace off
SQL> select * from test_plan where object_id=10 and object_name='C_USER#';
 
 OBJECT_ID OBJECT_NAME
---------- ------------------------------
        10 C_USER#
 
SQL> exec dbms_workload_repository.create_snapshot();
 
PL/SQL procedure successfully completed.

Session 2:

SQL> select sql_id,
  2         plan_hash_value,
  3         id,
  4         operation,
  5         options,
  6         object_owner,
  7         object_name,
  8         depth,
  9         cost,
 10         timestamp
 11    from DBA_HIST_SQL_PLAN
 12   where sql_id = '36qmvdf7f2xz0'
 13   order by 2, 3;
 
SQL_ID        PLAN_HASH_VALUE  ID OPERATION            OPTIONS    OBJECT_OWN OBJECT_NAME     DEPTH   COST TIMESTAMP
------------- --------------- --- -------------------- ---------- ---------- --------------- ----- ------ -------------------
36qmvdf7f2xz0      2068845065   0 SELECT STATEMENT                                               0     24 2009-04-08 11:27:42
36qmvdf7f2xz0      2068845065   1 TABLE ACCESS         FULL       SYS        TEST_PLAN           1     24 2009-04-08 11:27:42
36qmvdf7f2xz0      2839240733   0 SELECT STATEMENT                                               0      2 2009-04-08 11:30:59
36qmvdf7f2xz0      2839240733   1 TABLE ACCESS         BY INDEX R SYS        TEST_PLAN           1      2 2009-04-08 11:30:59
36qmvdf7f2xz0      2839240733   2 INDEX                RANGE SCAN SYS        IND_ID              2      1 2009-04-08 11:30:59
 
SQL> select a.INSTANCE_NUMBER,
  2         a.snap_id,
  3         a.sql_id,
  4         a.plan_hash_value,
  5         b.begin_interval_time
  6    from dba_hist_sqlstat a, dba_hist_snapshot b
  7   where sql_id = '36qmvdf7f2xz0'
  8     and a.snap_id = b.snap_id
  9   order by instance_number, snap_id;
 
INSTANCE_NUMBER    SNAP_ID SQL_ID        PLAN_HASH_VALUE BEGIN_INTERVAL_TIME
--------------- ---------- ------------- --------------- ------------------------
              1       3997 36qmvdf7f2xz0      2068845065 08-APR-09 11.27.28.891
              1       3998 36qmvdf7f2xz0      2839240733 08-APR-09 11.27.50.131

通过查询dba_hist_sqlstat、dba_hist_snapshot就可以获得执行计划的历史信息，可以看出何时发生了变化。
如果我们再在object_id,object_name列上建组合索引。使执行计划再发生变化，这时再来观察执行计划信息：

SQL> select sql_id,
  2         plan_hash_value,
  3         id,
  4         operation,
  5         options,
  6         object_owner,
  7         object_name,
  8         depth,
  9         cost,
 10         timestamp
 11    from DBA_HIST_SQL_PLAN
 12   where sql_id = '36qmvdf7f2xz0'
 13   order by 2, 3;
 
SQL_ID        PLAN_HASH_VALUE  ID OPERATION            OPTIONS    OBJECT_OWN OBJECT_NAME     DEPTH   COST TIMESTAMP
------------- --------------- --- -------------------- ---------- ---------- --------------- ----- ------ -------------------
36qmvdf7f2xz0      2068845065   0 SELECT STATEMENT                                               0     24 2009-04-08 11:27:42
36qmvdf7f2xz0      2068845065   1 TABLE ACCESS         FULL       SYS        TEST_PLAN           1     24 2009-04-08 11:27:42
36qmvdf7f2xz0      2829165872   0 SELECT STATEMENT                                               0      1 2009-04-08 11:36:01
36qmvdf7f2xz0      2829165872   1 INDEX                RANGE SCAN SYS        IND_ID_NAME         1      1 2009-04-08 11:36:01
36qmvdf7f2xz0      2839240733   0 SELECT STATEMENT                                               0      2 2009-04-08 11:30:59
36qmvdf7f2xz0      2839240733   1 TABLE ACCESS         BY INDEX R SYS        TEST_PLAN           1      2 2009-04-08 11:30:59
36qmvdf7f2xz0      2839240733   2 INDEX                RANGE SCAN SYS        IND_ID              2      1 2009-04-08 11:30:59
 
7 rows selected.
 
SQL> select a.INSTANCE_NUMBER,
  2         a.snap_id,
  3         a.sql_id,
  4         a.plan_hash_value,
  5         b.begin_interval_time
  6    from dba_hist_sqlstat a, dba_hist_snapshot b
  7   where sql_id = '36qmvdf7f2xz0'
  8     and a.snap_id = b.snap_id
  9   order by instance_number, snap_id;
 
INSTANCE_NUMBER    SNAP_ID SQL_ID        PLAN_HASH_VALUE BEGIN_INTERVAL_TIME
--------------- ---------- ------------- --------------- ------------------------
              1       3997 36qmvdf7f2xz0      2068845065 08-APR-09 11.27.28.891
              1       3998 36qmvdf7f2xz0      2839240733 08-APR-09 11.27.50.131
              1       4003 36qmvdf7f2xz0      2829165872 08-APR-09 11.39.46.599

同样，也能查到相关的执行计划信息。所以，当出现异常语句时，我们可以先查查dba_hist_sqlstat，dba_hist_snapshot，获得历史执行计划信息，看看何时发生了变化。再通过查询DBA_HIST_SQL_PLAN来获得具体的历史执行执行计划，与当前的执行计划进行对比，发现问题。

注：这里有几个snapshot点，3999至4002间，都没有记录我执行这个SQL的信息,不知是执行次数少了，还是刷快了的原因。最后多执行了几次这条语句，再手工创建一个快照，才产生相关信息。

SQL> select INSTANCE_NUMBER,
  2         snap_id,
  3         sql_id,
  4         plan_hash_value
  5    from dba_hist_sqlstat   
  6   where sql_id = '36qmvdf7f2xz0';
 
INSTANCE_NUMBER    SNAP_ID SQL_ID        PLAN_HASH_VALUE
--------------- ---------- ------------- ---------------
              1       3997 36qmvdf7f2xz0      2068845065
              1       3998 36qmvdf7f2xz0      2839240733
              1       4003 36qmvdf7f2xz0      2829165872

— The End —

 
分享到：
| Oracle 表连接 [转]
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    oracle11g执行计划管理-(讲解如何固定sql的执行计划)

    1. **计划历史记录**（Plan History）：对于执行超过一次的SQL语句，优化器会记录其每一次的执行计划及其相关信息，如SQL文本、绑定变量等，形成执行计划的历史记录。 2. **执行计划基准线**（Plan Baseline）：这是...

    oracle10g 10.5.0.4 补丁包

    同时，补丁也可能包含性能优化，比如改进查询执行计划，提高数据处理速度，或者优化内存管理，减少不必要的内存消耗。 此外，补丁包可能还涉及到数据保护和备份恢复的改进，如增强的 flashback technology，允许...

    xilinx 10G mac

    10G以太网子系统包含了10G以太网MAC（媒体访问控制）和PCS/PMA（物理编码子层/物理媒体附加层），支持10GBASE-R/KR标准以提供一个10G以太网端口。本文将详细介绍10G以太网子系统的功能、性能、资源利用率、延迟、...

    Oracle10g培训PPT

    1. 优化器（Optimizer）：Oracle 10g的优化器采用了新的成本估算方法和统计信息，提高了查询执行计划的选择准确性。 2. SQL Trace 和 TKPROF：这些工具用于诊断SQL语句的性能问题，生成分析报告，帮助改进查询效率。...

    Oracle10g-10.1.0.2客户端的exp.exe文件

    Oracle 10g是一款历史悠久但仍然广泛使用的数据库管理系统，它提供了强大的数据管理和处理能力。在本文中，我们将深入探讨Oracle10g客户端与服务端之间的兼容性问题，以及如何解决因版本不一致导致的导入导出问题。 ...

    oracle 10g performance tuning 教材

    - **ASH（Active Session History）**：活动会话历史，提供更细粒度的性能分析，可查看会话在特定时间点的行为。 2. **性能优化策略** - **索引优化**：通过创建、选择合适的索引提高查询速度，包括B树索引、位图...

    ORACLE 10G 经典PPT

    Oracle 10g是一款历史悠久但仍然受到广泛关注的关系型数据库管理系统，尤其对于初学者和准备Oracle Certified Professional (OCP)认证的人来说，它提供了丰富的学习资源。本篇内容将深入解析Oracle 10g的关键知识点...

    toad for oracle 10g

    Toad for Oracle 10g是一款专为Oracle数据库管理设计的强大工具，由Quest Software公司开发。它提供了全面的数据库开发、管理和性能优化功能，深受DBA（数据库管理员）和开发人员的喜爱。以下是对Toad for Oracle 10...

    oracle database 10g基础教程.rar

    同时，监控工具如AWR（自动工作区管理报告）和ASH（活动会话历史）提供了宝贵的性能诊断信息。 最后，故障排查和问题诊断是每个DBA必备的技能。学习查看数据库警告日志、跟踪文件，理解ORA错误代码，以及使用DBA...

    oracle10g administration

    Oracle 10g Administration 是一个全面的数据库管理系统，主要用于企业级的数据存储、管理和处理。作为IT行业的专业人士，了解Oracle 10g的管理是至关重要的，因为它涉及到许多关键的数据库管理概念和技术。以下是对...

    startOracle10g stopOracle10g

    在给定的信息中，`startOracle10g.bat` 和 `stopOracle10g.bat` 就是这样的批处理文件，分别用于启动和停止Oracle 10g数据库服务。 **启动Oracle 10g (startOracle10g.bat)** `startOracle10g.bat` 是一个批处理...

    Oracle10g-win32.rar

    Oracle 10g是一款历史悠久且深受企业信赖的关系型数据库管理系统，尤其在Windows 32位平台上，它提供了高效、稳定的数据存储与管理解决方案。Oracle 10g-win32.rar这个压缩包文件正是针对这样的环境设计，包含了安装...

    oracle 10G 实验手册

    Oracle 10G的闪回功能极大地增强了数据库的灵活性和安全性，使得数据恢复和历史数据分析变得更加简单高效。通过理解闪回功能的原理和掌握其使用方法，DBA可以更加自信地应对数据库管理中的各种挑战。然而，正确使用...

    Oracle10g数据库心得

    2. **SQL优化**：Oracle 10g引入了自动SQL优化器，能够根据执行计划统计信息自动选择最佳的执行路径。DBA可以通过绑定变量、索引优化、物化视图和分区等手段进一步提升SQL性能。 3. **RAC（Real Application ...

    Db10g SQL Tuning Vol-I

    SQL Access Advisor 是 Db10g 提供的一个工具，它可以根据历史数据和统计信息推荐索引创建策略以及其他优化建议，以改进 SQL 语句的执行效率。 #### 3. SQL Performance Analyzer (SPA) SPA 是一个用于比较不同...

    jdbc oracle 10g

    Oracle 10g是一款历史悠久但仍然广泛使用的数据库管理系统，它为开发者提供了强大的数据存储和管理功能。JDBC（Java Database Connectivity）是Java平台中的标准接口，用于与各种类型的数据库进行通信，包括Oracle ...

    Oracle10G官方文档CHM合集 Database.rar

    通过深入学习和理解"Oracle10G官方文档CHM合集 Database.rar"中的内容，无论是数据库管理员、开发人员还是运维人员，都能更好地掌握Oracle 10G的使用和维护技巧，从而提升工作效率和系统稳定性。在实际工作中，根据...

Magicbox
Global site tag (gtag.js) - Google Analytics