WORKLOAD REPOSITORY SQL Report Snapshot Period Summary DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ TALKBACK 346516274 TALKBACK 1 10.2.0.1.0 NO talkback-sd0 Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 14662 09-Sep-09 08:00:29 191 15.9 End Snap: 14663 09-Sep-09 09:00:47 258 11.5 Elapsed: 60.30 (mins) DB Time: 1,698.57 (mins) SQL Summary DB/Inst: TALKBACK/TALKBACK Snaps: 14662-14663 Elapsed SQL Id Time (ms) ------------- ---------- 0qsa92jqw24yg ########## Module: Digesterservice.exe select a1.bbid from fc__KeyTable_1098 u1, fc__KeyTable_1040 d1, fc__KeyTable_101 6 a1 where a1.bbid = u1.bbid and d1.bbid = u1.bbid and u1.keyValue = :1 and d1.k eyValue = :2 and a1.keyValue = (select /*+ INDEX(u1) USE_NL(d1) USE_NL(a1) */ m ax(a1.keyValue) from fc__KeyTable_1098 u1, fc__KeyTable_1040 d1, fc__KeyTable_10 ------------------------------------------------------------- SQL ID: 0qsa92jqw24yg DB/Inst: TALKBACK/TALKBACK Snaps: 14662-14663 -> 1st Capture and Last Capture Snap IDs refer to Snapshot IDs witin the snapshot range -> select a1.bbid from fc__KeyTable_1098 u1, fc__KeyTable_1040 d1, fc__Ke... Plan Hash Total Elapsed 1st Capture Last Capture # Value Time(ms) Executions Snap ID Snap ID --- ---------------- ---------------- ------------- ------------- -------------- 2 3291509040 73,267,340 253 14663 14663 1 1450883762 0 0 14663 14663 ------------------------------------------------------------- Plan 1(PHV: 3291509040) ----------------------- Plan Statistics DB/Inst: TALKBACK/TALKBACK Snaps: 14662-14663 -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 Stat Name Statement Per Execution % Snap ---------------------------------------- ---------- -------------- ------- Elapsed Time (ms) ########## 289,594.2 71.9 CPU Time (ms) ########## 85,495.7 78.5 Executions 253 N/A N/A Buffer Gets ########## 5,591,339.7 95.6 Disk Reads 2,187 8.6 0.9 Parse Calls 253 1.0 0.0 Rows 227 0.9 N/A User I/O Wait Time (ms) 122,107 N/A N/A Cluster Wait Time (ms) 0 N/A N/A Application Wait Time (ms) 0 N/A N/A Concurrency Wait Time (ms) ########## N/A N/A Invalidations 0 N/A N/A Version Count 70 N/A N/A Sharable Mem(KB) 49 N/A N/A ------------------------------------------------------------- Execution Plan ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 254 (100)| | | | 1 | TABLE ACCESS BY LOCAL INDEX ROWID | FC__KEYTABLE_1016 | 1 | 11 | 0 (0)| | | | 2 | NESTED LOOPS | | 1 | 94 | 127 (0)| 00:00:02 | | | 3 | NESTED LOOPS | | 1 | 83 | 127 (0)| 00:00:02 | | | 4 | PARTITION LIST ALL | | 1 | 38 | 64 (0)| 00:00:01 | 1 | | 5 | TABLE ACCESS BY LOCAL INDEX ROWID | FC__KEYTABLE_1040 | 1 | 38 | 64 (0)| 00:00:01 | 1 | | 6 | INDEX RANGE SCAN | FC__VALUEINDEX_1040 | 1 | | 63 (0)| 00:00:01 | 1 | | 7 | PARTITION LIST ALL | | 1 | 45 | 63 (0)| 00:00:01 | 1 | | 8 | TABLE ACCESS BY LOCAL INDEX ROWID | FC__KEYTABLE_1098 | 1 | 45 | 63 (0)| 00:00:01 | 1 | | 9 | INDEX RANGE SCAN | FC__PRIMARYKEY_1098 | 1 | | 62 (0)| 00:00:01 | 1 | | 10 | PARTITION LIST ALL | | 1 | | 0 (0)| | 1 | | 11 | INDEX RANGE SCAN | FC__PRIMARYKEY_1016 | 1 | | 0 (0)| | 1 | | 12 | SORT AGGREGATE | | 1 | 94 | | | | | 13 | TABLE ACCESS BY LOCAL INDEX ROWID | FC__KEYTABLE_1016 | 1 | 11 | 0 (0)| | 1 | | 14 | NESTED LOOPS | | 1 | 94 | 127 (0)| 00:00:02 | | | 15 | NESTED LOOPS | | 1 | 83 | 127 (0)| 00:00:02 | | | 16 | PARTITION LIST ALL | | 1 | 38 | 64 (0)| 00:00:01 | 1 | | 17 | TABLE ACCESS BY LOCAL INDEX ROWID| FC__KEYTABLE_1040 | 1 | 38 | 64 (0)| 00:00:01 | 1 | | 18 | INDEX RANGE SCAN | FC__VALUEINDEX_1040 | 1 | | 63 (0)| 00:00:01 | 1 | | 19 | PARTITION LIST ALL | | 1 | 45 | 63 (0)| 00:00:01 | 1 | | 20 | TABLE ACCESS BY LOCAL INDEX ROWID| FC__KEYTABLE_1098 | 1 | 45 | 63 (0)| 00:00:01 | 1 | | 21 | INDEX RANGE SCAN | FC__PRIMARYKEY_1098 | 1 | | 62 (0)| 00:00:01 | 1 | | 22 | PARTITION LIST ALL | | 1 | | 0 (0)| | 1 | | 23 | INDEX RANGE SCAN | FC__PRIMARYKEY_1016 | 1 | | 0 (0)| | 1 | ------------------------------------------------------------------------------------------------------------------------ Plan 2(PHV: 1450883762) ----------------------- Plan Statistics DB/Inst: TALKBACK/TALKBACK Snaps: 14662-14663 -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 Stat Name Statement Per Execution % Snap ---------------------------------------- ---------- -------------- ------- Elapsed Time (ms) 0 N/A 0.0 CPU Time (ms) 0 N/A 0.0 Executions 0 N/A N/A Buffer Gets 0 N/A 0.0 Disk Reads 0 N/A 0.0 Parse Calls 0 N/A 0.0 Rows 0 N/A N/A User I/O Wait Time (ms) 0 N/A N/A Cluster Wait Time (ms) 0 N/A N/A Application Wait Time (ms) 0 N/A N/A Concurrency Wait Time (ms) 0 N/A N/A Invalidations 0 N/A N/A Version Count 70 N/A N/A Sharable Mem(KB) 11 N/A N/A ------------------------------------------------------------- Execution Plan ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 1471 (100)| | | | 1 | HASH JOIN | | 183 | 17019 | 735 (1)| 00:00:09 | | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | FC__KEYTABLE_1016 | 1 | 11 | 0 (0)| | | | 3 | NESTED LOOPS | | 183 | 10248 | 337 (0)| 00:00:05 | | | 4 | PARTITION LIST ALL | | 287 | 12915 | 337 (0)| 00:00:05 | 1 | | 5 | TABLE ACCESS BY LOCAL INDEX ROWID | FC__KEYTABLE_1098 | 287 | 12915 | 337 (0)| 00:00:05 | 1 | | 6 | INDEX RANGE SCAN | FC__VALUEINDEX_1098 | 287 | | 66 (0)| 00:00:01 | 1 | | 7 | PARTITION LIST ALL | | 1 | | 0 (0)| | 1 | | 8 | INDEX RANGE SCAN | FC__PRIMARYKEY_1016 | 1 | | 0 (0)| | 1 | | 9 | SORT AGGREGATE | | 1 | 93 | | | | | 10 | TABLE ACCESS BY LOCAL INDEX ROWID | FC__KEYTABLE_1016 | 1 | 11 | 0 (0)| | 1 | | 11 | NESTED LOOPS | | 287 | 26691 | 735 (1)| 00:00:09 | | | 12 | HASH JOIN | | 287 | 23534 | 735 (1)| 00:00:09 | | | 13 | PARTITION LIST ALL | | 287 | 12915 | 337 (0)| 00:00:05 | 1 | | 14 | TABLE ACCESS BY LOCAL INDEX ROWID| FC__KEYTABLE_1098 | 287 | 12915 | 337 (0)| 00:00:05 | 1 | | 15 | INDEX RANGE SCAN | FC__VALUEINDEX_1098 | 287 | | 66 (0)| 00:00:01 | 1 | | 16 | PARTITION LIST ALL | | 929 | 34373 | 397 (0)| 00:00:05 | 1 | | 17 | TABLE ACCESS BY LOCAL INDEX ROWID| FC__KEYTABLE_1040 | 929 | 34373 | 397 (0)| 00:00:05 | 1 | | 18 | INDEX RANGE SCAN | FC__VALUEINDEX_1040 | 929 | | 74 (0)| 00:00:01 | 1 | | 19 | PARTITION LIST ALL | | 1 | | 0 (0)| | 1 | | 20 | INDEX RANGE SCAN | FC__PRIMARYKEY_1016 | 1 | | 0 (0)| | 1 | | 21 | PARTITION LIST ALL | | 929 | 34373 | 397 (0)| 00:00:05 | 1 | | 22 | TABLE ACCESS BY LOCAL INDEX ROWID | FC__KEYTABLE_1040 | 929 | 34373 | 397 (0)| 00:00:05 | 1 | | 23 | INDEX RANGE SCAN | FC__VALUEINDEX_1040 | 929 | | 74 (0)| 00:00:01 | 1 | ------------------------------------------------------------------------------------------------------------------------ Full SQL Text SQL ID SQL Text ------------ ----------------------------------------------------------------- 0qsa92jqw24y select a1.bbid from fc__KeyTable_1098 u1, fc__KeyTable_1040 d1, f fc__KeyTable_1016 a1 where a1.bbid = u1.bbid and d1.bbid = u1.bbi d and u1.keyValue = :1 and d1.keyValue = :2 and a1.keyValue = (se lect /*+ INDEX(u1) USE_NL(d1) USE_NL(a1) */ max(a1.keyValue) from fc__KeyTable_1098 u1, fc__KeyTable_1040 d1, fc__KeyTable_1016 a1 where a1.bbid = u1.bbid and d1.bbid = u1.bbid and u1.keyValue = :3 and d1.keyValue = :4)