Statspack report
SQL ordered by Elapsed time for DB: ORCL Instance: orcl Snaps: 19094 -19114
-> Total DB Time (s): 19,794
-> Captured SQL accounts for 28.6% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
———- ———— ———- —— ———- ————— ———-
467.77 296 1.58 2.4 24.79 5,932,508 3741116312
Module: dsi_ts_tcpip.exe
Select IMITM,IMLITM,KMITM,KMKBQT,KMKID1,KMLITM,KMLOCN,KMMCU,KMMM
CU,KMTLOC,KMUOM FROM PRODDTA.V55KBDC1 WHERE KMKBST = ‚2‘ and KMM
CU = ‚ SN002‘ Order By V55KBDC1.KMMCU
A jak získat celý SQL statement a jeho exekuční plán na základě hash value?
Pomocí scriptu @\rdbms\admin\sprepsql.sql
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 19094
Begin Snapshot Id specified: 19094
Enter value for end_snap: 19114
End Snapshot Id specified: 19114
Specify the old (i.e. pre-10g) Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 3741116312
Hash Value specified is: 3741116312
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_19094_19114_3741116312. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name sp_19094_19114_3741116312
STATSPACK SQL report for Old Hash Value: 3741116312 Module: dsi_ts_tcpip.exe
DB Name DB Id Instance Inst Num Release RAC Host
———— ———– ———— ——– ———– — —————-
ORCL 4263223448 orcl 1 11.2.0.4.0 NO DBASolar
Start Id Start Time End Id End Time Duration(mins)
——— ——————- ——— ——————- ————–
19094 11-May-16 04:00:01 19114 11-May-16 06:00:03 120.03
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,314,773 24,712.1 1.45
Disk Reads: 5,932,508 20,042.3 24.95
Rows processed: 1,150 3.9
CPU Time(s/ms): 25 83.7
Elapsed Time(s/ms): 468 1,580.3
Sorts: 0 .0
Parse Calls: 296 1.0
Invalidations: 0
Version count: 1
Sharable Mem(K): 27
Executions: 296
SQL Text
~~~~~~~~
Select IMITM,IMLITM,KMITM,KMKBQT,KMKID1,KMLITM,KMLOCN,KMMCU,KMMM
CU,KMTLOC,KMUOM FROM PRODDTA.V55KBDC1 WHERE KMKBST = ‚2‘ and KMM
CU = ‚ SN002‘ Order By V55KBDC1.KMMCU
Known Optimizer Plan(s) for this Old 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 Last Plan
Snap Id Snap Time Active Time Hash Value Cost
——— ————— ————— ———— ———-
16214 26-Apr-16 23:00 27-Apr-16 21:49 1419033047 6022
16284 27-Apr-16 06:00 09-May-16 21:48 1419033047
16454 27-Apr-16 23:00 29-Apr-16 21:00 1092194495 6016
16474 28-Apr-16 01:00 29-Apr-16 21:45 1092194495
16944 30-Apr-16 00:00 03-May-16 22:59 1419033047 5798
17664 04-May-16 00:00 11-May-16 06:57 3864953904 6812
17704 04-May-16 04:00 10-May-16 23:54 3864953904
18134 05-May-16 23:00 09-May-16 20:58 1419033047 6290
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 |—– 3864953904 —-| | | 6397 |
|HASH JOIN | | 2K| 516K| 6397 |
| TABLE ACCESS FULL |F3016 | 2K| 405K| 5099 |
| TABLE ACCESS FULL |F4101 | 14K| 782K| 1298 |
——————————————————————————–
End of Report