Hi, We are using version 11.2.0.4 of oracle exadata. We are seeing delayed block cleanout effect i.e. statistics 'transaction tables consistent reads - undo records applied' while the execution takes longer time and the current_obj is often showing as '0' i.e UNDO. And its 'index range scan'+'table access by local index rowid' section of a local index in a partition table in the plan where we dont have filter on partition key as per business need. Now considering its a critical job for us we want to scan this object beforehand(after load) so that this critical SELECT query wont go for the UNDO read. We have certain restriction wrt stats which is why we cant gather stats on the index/table to update the transaction table slots with the commit scn. So we are trying to do a forced full scan on table partition and index partition by doing a forced FTS as below.
Sometimes we see even after doing those full scans, the query(mainly index) still enters into the delayed block cleanout UNDO, so wanted to understand , if the full scan which we are performing as below method ,will really cleanout or update the commit scn for all the index/table blocks? Or we are doing it wrong way?
The index is a function based index as below.
CREATE INDEX TAB1_IX1 ON tab1 (DECODE(TO_CHAR("C1"),NULL,1,NULL), DECODE(TO_CHAR("C1"),NULL,"C2",NULL)
We are scanning table as below:-
SELECT /*+ full(TAB1)*/
COUNT (*)
FROM TAB1;
We are scanning index as below:-
SELECT /*+ index_ffs(TAB1 TAB1_IX1)*/
COUNT (*)
FROM TAB1
WHERE DECODE (TO_CHAR (C1), NULL, 1, NULL) = 1
Below is the exact section of the plan where we spend time on reading from UNDO.
| 11 | PARTITION LIST ALL | | 1 | 474 | 2197 | +2 | 34424 | 16M | | | | |
| 12 | PARTITION RANGE ALL | | 1 | 474 | 2197 | +2 | 68848 | 16M | | | 0.20 | Cpu (13) |
| 13 | TABLE ACCESS BY LOCAL INDEX ROWID | TAB1 | 1 | 474 | 2197 | +2 | 5M | 16M | 3M | 27GB | 40.45 | gc cr grant 2-way (96) |
| | | | | | | | | | | | | gc current block 2-way (24) |
| | | | | | | | | | | | | gc remaster (2) |
| | | | | | | | | | | | | Cpu (242) |
| | | | | | | | | | | | | gcs drm freeze in enter server mode (11) |
| | | | | | | | | | | | | latch: redo allocation (1) |
| | | | | | | | | | | | | cell single block physical read (2310) |
| 14 | INDEX RANGE SCAN | TAB1_IX1 | 1 | 474 | 2198 | +1 | 5M | 25M | 4M | 29GB | 54.19 | Cpu (265) |
| | | | | | | | | | | | | latch: gcs resource hash (1) |
| | | | | | | | | | | | | latch: redo allocation (3) |
| | | | | | | | | | | | | cell single block physical read (2923) |
| | | | | | | | | | | | | cell single block read request (5) |
| | | | | | | | | | | | | read by other session (401) |