Hi experts,
I have performance issue with a query in my application. The query is running since 600 mins and the session is in ACTIVE status without waiting for any resource.
The query consist of 3 tables with their stats as follows
Header Table – 3 Lakh records
Details Table - 70 million records
Another table – 4 thousand records
Stats of all the 3 tables are updated. Below is the explain plan of the query .
Plan |
SELECT STATEMENT ALL_ROWS Cost: 119 Bytes: 148 Cardinality: 2 |
| 15 COUNT |
| | 14 VIEW CPTPF. Cost: 119 Bytes: 148 Cardinality: 2 |
| | | 13 SORT ORDER BY Cost: 118 Bytes: 125 Cardinality: 2 |
| | | | 12 UNION-ALL |
| | | | | 1 TABLE ACCESS FULL TABLE USER1.ANOTHER_TABLE Cost: 5 Bytes: 23 Cardinality: 1 |
| | | | | 11 HASH UNIQUE Cost: 113 Bytes: 102 Cardinality: 1 |
| | | | | | 10 NESTED LOOPS SEMI Cost: 112 Bytes: 102 Cardinality: 1 |
| | | | | | | 7 NESTED LOOPS Cost: 111 Bytes: 84 Cardinality: 1 |
| | | | | | | | 4 VIEW VIEW SYS.VW_DTP_D99AD0E8 Cost: 6 Bytes: 55 Cardinality: 1 |
| | | | | | | | | 3 HASH UNIQUE Cost: 6 Bytes: 23 Cardinality: 1 |
| | | | | | | | | | 2 TABLE ACCESS FULL TABLE USER1.ANOTHER_TABLE Cost: 5 Bytes: 23 Cardinality: 1 |
| | | | | | | | 6 TABLE ACCESS BY INDEX ROWID TABLE USER2.DTLS_TAB Cost: 104 Bytes: 29 Cardinality: 1 |
| | | | | | | | | 5 INDEX RANGE SCAN INDEX (UNIQUE) USER2.DTLS_PK Cost: 104 Cardinality: 70,922,696 |
| | | | | | | 9 TABLE ACCESS BY INDEX ROWID TABLE USER2.HDR_TAB Cost: 1 Bytes: 5,594,310 Cardinality: 310,795 |
| | | | | | | | 8 INDEX RANGE SCAN INDEX (UNIQUE) USER2.HDR_PK Cost: 1 Cardinality: 1 |
The query uses 2 user functions and has an exist clause in it. The current event is shown as “db file sequential read” (from the start of query execution). I am stuck at how to proceed with fixing this issue.
Can someone please let me know steps to identify cause of issue?
Imran.