Hi, I am using Version-11.2.0.4 of oracle. We are seeing below error for one of the SELECT query which runs in multiple parallel threads. I see the query completes in ~10minutes and sometimes fast though. Rerun went smooth though. From the description of the error it seems there must be some direct path operation happening on one of the objects on which this query was running upon. But as i verified i dint get any Index rebuild or direct path operation on the underlying objects- TAB1. But i saw stats gather was running during that period on the underlying objects TAB1, so is there a possibility , by any chance this error can come during a SELECT query execution, when stat gather was running on same object? or any other ways to debug/fix this issue?
ORA-08176: consistent read failure; rollback data not available Database driver error.
Query:
SELECT DISTINCT TAB4.COL1 AS COL1
FROM TAB2,
TAB1,
TAB3,
TAB4
WHERE TAB1.DID = TAB3.DID
AND TAB3.CD = TAB4.CD
AND TAB4.ENIND = 0
AND TAB2.FID = TAB1.SID
AND TAB2.FID = :A;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9779 (100)| | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 | 11 | 682 | 9779 (1)| 00:01:58 | | | Q1,04 | P->S | QC (RAND) |
| 3 | SORT UNIQUE | | 11 | 682 | 9779 (1)| 00:01:58 | | | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 5959 | 360K| 9778 (1)| 00:01:58 | | | Q1,04 | PCWP | |
| 5 | PX SEND HASH | :TQ10003 | 5959 | 360K| 9778 (1)| 00:01:58 | | | Q1,03 | P->P | HASH |
| 6 | HASH JOIN | | 5959 | 360K| 9778 (1)| 00:01:58 | | | Q1,03 | PCWP | |
| 7 | HASH JOIN | | 6296 | 264K| 5668 (1)| 00:01:09 | | | Q1,03 | PCWP | |
| 8 | MERGE JOIN CARTESIAN | | 6296 | 202K| 4721 (1)| 00:00:57 | | | Q1,03 | PCWP | |
| 9 | BUFFER SORT | | | | | | | | Q1,03 | PCWC | |
| 10 | PX RECEIVE | | 1 | 16 | 4 (0)| 00:00:01 | | | Q1,03 | PCWP | |
| 11 | PX SEND BROADCAST | :TQ10000 | 1 | 16 | 4 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 12 | TABLE ACCESS BY INDEX ROWID | TAB2 | 1 | 16 | 4 (0)| 00:00:01 | | | | | |
| 13 | INDEX RANGE SCAN | TAb2_IDX_1 | 1 | | 3 (0)| 00:00:01 | | | | | |
| 14 | BUFFER SORT | | 36878 | 612K| 4717 (1)| 00:00:57 | | | Q1,03 | PCWP | |
| 15 | PX PARTITION RANGE ALL | | 36878 | 612K| 4717 (1)| 00:00:57 | 1 | 9 | Q1,03 | PCWC | |
| 16 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB1 | 36878 | 612K| 4717 (1)| 00:00:57 | 1 | 9 | Q1,03 | PCWP | |
| 17 | INDEX RANGE SCAN | TAB1_IX1 | 36937 | | 218 (1)| 00:00:03 | 1 | 9 | Q1,03 | PCWP | |
| 18 | BUFFER SORT | | | | | | | | Q1,03 | PCWC | |
| 19 | PX RECEIVE | | 767K| 7496K| 941 (1)| 00:00:12 | | | Q1,03 | PCWP | |
| 20 | PX SEND BROADCAST | :TQ10001 | 767K| 7496K| 941 (1)| 00:00:12 | | | | S->P | BROADCAST |
| 21 | INDEX FAST FULL SCAN | TAB3_IX1 | 767K| 7496K| 941 (1)| 00:00:12 | | | | | |
| 22 | BUFFER SORT | | | | | | | | Q1,03 | PCWC | |
| 23 | PX RECEIVE | | 210K| 3903K| 4108 (1)| 00:00:50 | | | Q1,03 | PCWP | |
| 24 | PX SEND BROADCAST | :TQ10002 | 210K| 3903K| 4108 (1)| 00:00:50 | | | | S->P | BROADCAST |
| 25 | TABLE ACCESS FULL | TAB4 | 210K| 3903K| 4108 (1)| 00:00:50 | | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------