用 Statspack 收集 SQL 执行计划历史信息

小技巧一个:在一个开发环境中,可以考虑把用户的SQL执行计划历史也收集进来.很简单,但是对开发 DBA 会很有帮助, 根据 HASH_VALUE 查询相关过去某个时间段内的 SQL 执行计划.一共两步:
1 Statspack 的 level 6 收集所有的统计和执行计划. 用 level 6 收集快照(Snap)信息:

SQL> execute statspack.snap (i_snap_level=>6);

2 运行 $ORACLE_HOME/rdbms/admin/sprepsql.sql 脚本抽取特定 HASH_VALUE 的 SQL 信息:

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 16
Begin Snapshot Id specified: 16


Enter value for end_snap: 21
End   Snapshot Id specified: 21


Specify the Hash Value
~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 479732399
Hash Value specified is: 479732399


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_16_21_479732399.  To use this name,
press  to continue, otherwise enter an alternative.
Enter value for report_name: 


Using the report name sp_16_21_479732399


STATSPACK SQL report for Hash Value: 479732399  Module: java@Localhost(TNS V1-V3)

DB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
DEMO          1702625429 DEMO                 1 9.2.0.6.0   NO      Localhost

Start Id     Start Time         End Id      End Time       Duration(mins)
--------- ------------------- --------- ------------------- --------------
16 17-Feb-06 09:54:05         21 17-Feb-06 16:24:05          390.00

SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total      Per Execute   Total
---------------  ---------------  ------
Buffer Gets:           7,931          7,931.0     .00
Disk Reads:           6,688          6,688.0    1.35
Rows processed:               0              0.0
CPU Time(s/ms):               0            450.0
Elapsed Time(s/ms):               2          2,022.0
Sorts:               0               .0
Parse Calls:               1              1.0
Invalidations:               0
Version count:               1
Sharable Mem(K):             145
Executions:               1
SQL Text
~~~~~~~~
select * from view_return where ((return_status = 'SUCCESS') AND (Seller_id = :1))

Known Optimizer Plan(s) for this Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows all known Optimizer Plans for this database instance, and the Snap Id's
they were first found in the shared pool.  A Plan Hash Value will appear
multiple times if the cost has changed
-> ordered by Snap Id

First        First          Plan
Snap Id     Snap Time     Hash Value        Cost
-------- --------------- ------------ ----------
18 17 Feb 06 11:54   3233489309       319

Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified.  The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value

--------------------------------------------------------------------------------
| Operation                      | PHV/Object Name     |  Rows | Bytes|   Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT                |----- 3233489309 ----|       |      |    319 |
|HASH JOIN                       |                     |   396 |  198K|    319 |
| HASH JOIN                      |                     |   396 |   93K|    311 |
|  HASH JOIN OUTER               |                     |   396 |   80K|    258 |
|   HASH JOIN                    |                     |   309 |   48K|    251 |
|    TABLE ACCESS FULL           |ORACLE_RETURN        |   309 |   31K|      3 |
|    TABLE ACCESS FULL           |ORACLE_RETURN_GOODS  |   201K|   10M|    239 |
|   TABLE ACCESS FULL            |ORACLE_TIMEOUT       |   678 |   32K|      6 |
|  TABLE ACCESS FULL             |ORACLE_GOODS         |    42K|    1M|     49 |
| TABLE ACCESS BY GLOBAL INDEX RO|ORACLE_GOODS         |     1K|  279K|      6 |
|  INDEX RANGE SCAN              |IND_ORACLE_GOODS_NAME|     1 |      |      1 |
--------------------------------------------------------------------------------

End of Report


@>

多么糟糕的一条语句阿 :)

Google+