I just ran into a situation using flashback and dbreplay.
See my next post on DBReplay. this was the culprit.
See my next post on DBReplay. this was the culprit.
Oracle Database Architecture blog sharing my experiences as an oracle Architect.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 54186084
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 162 | 14 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 3 | 162 | 14 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| TAB1 | 3 | 75 | 7 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | TAB1_PK | 3 | | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | TAB2_IX | 1 | | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | TAB2 | 1 | 29 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."COL1"='WRH$_DB_CACHE_ADVICE' OR "A"."COL1"='WRH$_PARAMETER_PK'
OR "A"."COL1"='WRH$_SEG_STAT')
6 - access("COL1"="COL2")
filter("COL2"='WRH$_DB_CACHE_ADVICE' OR "COL2"='WRH$_PARAMETER_PK' OR
"COL2"='WRH$_SEG_STAT')
23 rows selected.
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1964798218
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 1404 | 23 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 26 | 1404 | 23 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 25 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | TAB1_PK | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TAB2 | 26 | 754 | 21 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | TAB2_IX | 26 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"='WRH$_DB_CACHE_ADVICE')
5 - access("COL2"='WRH$_DB_CACHE_ADVICE')
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2805776637
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77 | 7014 | 71 (68)| 00:00:01 |
| 1 | SORT UNIQUE | | 77 | 7014 | 71 (68)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS | | 26 | 1404 | 23 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | TAB1 | 1 | 25 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | TAB1_PK | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | TAB2 | 26 | 754 | 21 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | TAB2_IX | 26 | | 2 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | | | | |
| 9 | NESTED LOOPS | | 51 | 5610 | 46 (0)| 00:00:01 |
| 10 | VIEW | VW_JF_SET$623BBB07 | 2 | 162 | 4 (0)| 00:00:01 |
| 11 | SORT UNIQUE | | 2 | 50 | 4 (50)| 00:00:01 |
| 12 | UNION-ALL | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 25 | 2 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | TAB1_PK | 1 | | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 25 | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | TAB1_PK | 1 | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | TAB2_IX | 26 | | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | TAB2 | 26 | 754 | 21 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("COL1"='WRH$_PARAMETER_PK')
7 - access("COL2"='WRH$_PARAMETER_PK')
14 - access("COL1"='WRH$_DB_CACHE_ADVICE')
16 - access("COL1"='WRH$_SEG_STAT')
17 - access("ITEM_1"="COL2")