Hi All,
I am facing a self join query with lot of complexity. We are trying to rewrite the query because we are seeing the CPU 100% .
We have very limited access so I have only explain of the query.
Oracle version :
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
Query :
SELECT DISTINCT ID1,
ID2,
TRUNC(AC_DATE)
FROM
(SELECT TAB1A.TABLE1_C3 AS ID1,
TAB1C.TABLE1_C3 AS IDall,
Prty_a.PARTY_ID AS ID2,
TAB1A.TABLE1_C6 AS AC_DATE,
MAX (TAB1C.TABLE1_C4) over (partition BY TAB1C.TABLE1_C3) AS MAXALL
FROM TABLE1 TAB1A,
TABLE2 TAB2A,
TABLE3 TAB3A,
TABLE1 TAB1B,
TABLE2 TAB2B,
TABLE3 TAB3B,
TABLE1 TAB1C
WHERE TAB2A.TABLE2_C1 =to_date('12/31/9999', 'MM/DD/YYYY')
AND TAB1A.TABLE1_C1 =TAB2A.TABLE2_C2
AND TAB1A.TABLE1_C2 =TAB3A.TABLE3_C2
AND TAB3A.TABLE3_C1 IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P')
AND TAB2B.TABLE2_C1 =to_date('12/31/9999', 'MM/DD/YYYY')
AND TAB1B.TABLE1_C5 =TAB2B.TABLE2_C2
AND TAB1B.TABLE1_C2 =TAB3B.TABLE3_C2
AND TAB3B.TABLE3_C1 ='A'
AND LENGTH(TAB1A.TABLE1_C3(+)) =5
AND TAB1A.TABLE1_C3 =TAB1B.TABLE1_C3
AND TAB1C.TABLE1_C3 = TAB1A.TABLE1_C3
AND to_number(TAB1A.TABLE1_C3(+))>=10000
)
WHERE AC_DATE= MAXALL
AND ID1 = IDALL
Explain plan :
Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
0 | SELECT STATEMENT | | | | 84593 (100) | | |
1 | HASH UNIQUE | | 1 | 48 | 84593 (7) | 00:01:48 | |
2 | VIEW | | 1 | 48 | 84592 (7) | 00:01:48 | |
3 | WINDOW SORT | | 1 | 246 | 84592 (7) | 00:01:48 | |
4 | NESTED LOOPS | | 1 | 246 | 84591 (7) | 00:01:48 | |
5 | NESTED LOOPS | | 1 | 229 | 84589 (7) | 00:01:48 | |
6 | HASH JOIN | | 1 | 206 | 84586 (7) | 00:01:48 | |
7 | NESTED LOOPS | | 1 | 186 | 38768 (6) | 00:00:50 | |
8 | HASH JOIN | | 9 | 738 | 38759 (6) | 00:00:50 | |
9 | TABLE ACCESS BY INDEX ROWID | TABLE1 | 1 | 31 | 11834 (1) | 00:00:16 | |
10 | NESTED LOOPS | | 1 | 57 | 21223 (5) | 00:00:27 | |
11 | TABLE ACCESS FULL | TABLE3 | 46 | 1196 | 1290 (12) | 00:00:02 | |
12 | INDEX RANGE SCAN | TABLE1_FK_TABLE3_C2 | 125K | | 180 (9) | 00:00:01 | |
13 | INDEX FAST FULL SCAN | TABLE1_UNIQUE_UK | 7788K | 185M | 17085 (5) | 00:00:22 | |
14 | TABLE ACCESS BY INDEX ROWID | TABLE3 | 1 | 104 | 1 (0) | 00:00:01 | |
15 | INDEX UNIQUE SCAN | TABLE3_PK | 1 | | 0 (0) | | |
16 | TABLE ACCESS FULL | TABLE1 | 7788K | 148M | 45368 (6) | 00:00:58 | |
17 | PARTITION RANGE SINGLE | | 1 | 23 | 3 (0) | 00:00:01 | 2 | 2 |
18 | TABLE ACCESS BY LOCAL INDEX ROWID | TABLE2 | 1 | 23 | 3 (0) | 00:00:01 | 2 | 2 |
19 | INDEX RANGE SCAN | TABLE2_KEY_ UK | 1 | | 2 (0) | 00:00:01 | 2 | 2 |
20 | PARTITION RANGE SINGLE | | 1 | 17 | 2 (0) | 00:00:01 | 2 | 2 |
21 | INDEX RANGE SCAN | TABLE2_KEY_ UK | 1 | 17 | 2 (0) | 00:00:01 | 2 | 2 |
Can you please guide us for where to start in query re write ?