I am using Release 11.2.0.3.0 of oracle.
I have a sql query which is taking ~1hr 10 minutes for complete execution. Then i ran cardinality estimation for same and found that the most time consuming part of the query is the index_fast_full scan of index a_pk. So below is the portion of the query plan i have removed some part of the plan purposely. The index details is as below. Its unique b-tree index having BLEVEL as '2', DISTINCT_KEYS - 2591426, clustering_factor-1895980, num_rows-2591426
table size A - 1.54 GB , total rows - 2604469
table size B - 762 MB , total rows - 2614041
index size A_PK - 97 MB
So my question is, the plan looks fine with cardinality estimation, so is it expected the fast full scan of index to take ~1hr, or there is some other issue with same?
------------------------------- cardinality estimation -------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------- Purposely removed some portion -----------------------------
|* 6 | FILTER | | 1 | | 7981 |01:04:54.81 | 53M| 5115 | | | |
| 7 | NESTED LOOPS | | 1 | | 7981 |00:00:02.62 | 38109 | 716 | | | |
| 8 | NESTED LOOPS | | 1 | 1194 | 7981 |00:00:01.30 | 30069 | 289 | | | |
| 9 | NESTED LOOPS | | 1 | 1194 | 7981 |00:00:00.60 | 1095 | 244 | | | |
| 10 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | 0 | | | |
|* 11 | TABLE ACCESS BY GLOBAL INDEX ROWID| A | 1 | 1194 | 7981 |00:00:00.59 | 1095 | 244 | | | |
|* 12 | INDEX RANGE SCAN | A_FK_05 | 1 | 1 | 7981 |00:00:00.11 | 27 | 27 | | | |
| 13 | PARTITION REFERENCE ITERATOR | | 7981 | 1 | 7981 |00:00:00.65 | 28974 | 45 | | | |
|* 14 | INDEX UNIQUE SCAN | B_PK | 7981 | 1 | 7981 |00:00:00.36 | 5031 | 42 | | | |
| 15 | TABLE ACCESS BY LOCAL INDEX ROWID | B | 7981 | 1 | 7981 |00:00:01.30 | 8040 | 427 | | | |
|* 16 | FILTER | | 7981 | | 0 |00:00:00.02 | 0 | 0 | | | |
|* 17 | INDEX UNIQUE SCAN | A_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 18 | INDEX FAST FULL SCAN | A_PK | 7981 | 2 | 7981 |01:04:51.97 | 53M| 4399 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------
Now the part of the query is as below for which i am able to get similar plan and also its too taking same amount of time for completion.
select *
FROM USER1.A
INNER JOIN USER1.B
ON A.C1 = b.c1
INNER JOIN (select 158214002 C2, 3 scp_cd from dual ) gtt -- this is a global temp table so modified the query
ON A.C2 = gtt.C2
WHERE A.C3= 4385002
and (EXISTS
(SELECT 1
FROM USER1.A
WHERE A.c1 = b.c1
OR (UPPER (b.C4) =
'0083')
)
);
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 910 | 3600 (1)| 00:00:19 | | |
|* 1 | FILTER | | | | | | | |
| 2 | NESTED LOOPS | | 1194 | 1061K| 2403 (1)| 00:00:13 | | |
| 3 | NESTED LOOPS | | 1194 | 741K| 12 (0)| 00:00:01 | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | |
|* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| A | 1194 | 741K| 10 (0)| 00:00:01 | 1 | 1 |
|* 6 | INDEX RANGE SCAN | A_FK_05 | 1 | | 2 (0)| 00:00:01 | | |
| 7 | PARTITION REFERENCE ITERATOR | | 1 | 274 | 2 (0)| 00:00:01 | KEY | KEY |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID | B | 1 | 274 | 2 (0)| 00:00:01 | KEY | KEY |
|* 9 | INDEX UNIQUE SCAN | B_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
|* 10 | INDEX FAST FULL SCAN | A_PK | 2 | 16 | 2 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "USER1"."A" WHERE "A"."C1"=:B1 OR
UPPER(:B2)=U'0083'))
5 - filter("A"."C3"=4385002)
6 - access("A"."C2"=158214002)
9 - access("A"."C1"="B"."C1")
10 - filter("A"."C1"=:B1 OR UPPER(:B2)=U'0083')