小技巧一个:在一个开发环境中,可以考虑把用户的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, pressto 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
@>
多么糟糕的一条语句阿 :)