-
Explain plan Takes a long time
SQL> EXPLAIN PLAN FOR SELECT vw.CASE_ID
2 FROM prod.DOC_PROD_CASE_VW VW
3 WHERE ( (VW.CASE_NO = '495796')
4 AND VW.CASE_ID in(551,593,552,801,799,761,550,
5 247,653,800,806,613,573,803,246,804,385,252,251,250,807,810,760,
6 770,715,101,244,794,656,253,673,755,100,767,553,217,243,219,245,
7 103,805,809,655,734,784,526,693,548,1,757,248,572,446,765,659,2,
8 756,808,758,802,0))
9 GROUP BY VW.CASE_ID
10 ORDER BY VW.CASE_ID;
Explained.
Elapsed: 00:00:42.06
if i remove the group by and order by .the explain plan completes in less than 3 seconds.
Otherwise
Explain plan takes a Long time.Some times the session itself freezes....
What can be done.
Last edited by Subha; 03-06-2007 at 05:09 PM.
-
probably your view is too complex
-
why is it an issue - it gets explained in the end
-
-
SELECT STATEMENT [CHOOSE] Cost = 14
SORT GROUP BY
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
TABLE ACCESS BY INDEX ROWID DOCUMENT [ANALYZED]
INDEX RANGE SCAN DOCUMENT_CASE_IDX [ANALYZED]
INDEX UNIQUE SCAN E_DOC_DECISIONS_PK
VIEW PUSHED PREDICATE TREAD_DOCUMENT_SUBSYSTEM_VW
NESTED LOOPS OUTER
NESTED LOOPS OUTER
TABLE ACCESS BY INDEX ROWID TREAD_DOCUMENT [ANALYZED]
INDEX UNIQUE SCAN TREAD_DOC_DOCID_UK [ANALYZED]
TABLE ACCESS BY INDEX ROWID TREAD_SUBSYSTEM [ANALYZED]
INDEX RANGE SCAN TREAD_SUBSYS_TRDDOCID_IDX [ANALYZED]
INDEX UNIQUE SCAN CD_VALUE_PK [ANALYZED]
INDEX UNIQUE SCAN E_DOC_TAGS_PK
TABLE ACCESS BY INDEX ROWID PROD_DOCUMENT [ANALYZED]
INDEX RANGE SCAN PROD_DOC_DOCID_IDX [ANALYZED]
INDEX RANGE SCAN PROD_DOC_QST_PRDDOCID_FK_IDX [ANALYZED]
-
I do not see CARD, CPU values from the explain plan?
Did you analyze all tables and indexes?
Use this method:
delete from plan_table
/
explain plan for
select .......
select * from table(dbms_xplan.display)
/
When you post, use tag so that your writings will be formatted.
What is the value for optimizer_max_permutation?
-
Code:
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 59 | 14 (0)|
| 1 | SORT GROUP BY | | 1 | 59 | 14 (0)|
| 2 | NESTED LOOPS OUTER | | 105 | 6195 | 13 (0)|
| 3 | NESTED LOOPS OUTER | | 105 | 5670 | 12 (0)|
| 4 | NESTED LOOPS OUTER | | 74 | 3108 | 5 (20)|
| 5 | NESTED LOOPS OUTER | | 74 | 2146 | 4 (25)|
| 6 | NESTED LOOPS OUTER | | 74 | 1776 | 3 (34)|
|* 7 | TABLE ACCESS BY INDEX ROWID | DOCUMENT | 74 | 814 | 2 (50)|
|* 8 | INDEX RANGE SCAN | DOCUMENT_CASE_IDX | 74 | | 1 (0)|
|* 9 | INDEX UNIQUE SCAN | E_DOC_DECISIONS_PK | 1 | 13 | |
| 10 | VIEW PUSHED PREDICATE | TREAD_DOCUMENT_SUBSYSTEM_VW | 1 | 5 | |
| 11 | NESTED LOOPS OUTER | | 1 | 21 | 4 (25)|
| 12 | NESTED LOOPS OUTER | | 1 | 16 | 3 (34)|
| 13 | TABLE ACCESS BY INDEX ROWID| TREAD_DOCUMENT | 1 | 8 | 2 (50)|
|* 14 | INDEX UNIQUE SCAN | TREAD_DOC_DOCID_UK | 407 | | |
| 15 | TABLE ACCESS BY INDEX ROWID| TREAD_SUBSYSTEM | 1 | 8 | 2 (50)|
|* 16 | INDEX RANGE SCAN | TREAD_SUBSYS_TRDDOCID_IDX | 1 | | |
|* 17 | INDEX UNIQUE SCAN | CODE_VALUE_PK | 1 | 5 | |
|* 18 | INDEX UNIQUE SCAN | E_DOC_TAGS_PK | 1 | 13 | |
| 19 | TABLE ACCESS BY INDEX ROWID | PROD_DOCUMENT | 1 | 12 | 2 (50)|
|* 20 | INDEX RANGE SCAN | PROD_DOC_DOCID_IDX | 1 | | |
|* 21 | INDEX RANGE SCAN | PROD_DOC_QST_PRDDOCID_FK_IDX | 1 | 5 | |
----------------------------------------------------------------------------------------------------------
Last edited by tamilselvan; 03-07-2007 at 01:05 PM.
-
I suspect "VIEW PUSHED PREDICATE " operation takes long time because of the IN clause that fliteres with so many constants.
I suggest you store the constants in a table, and do the join.
Let us see the new plan.
Also, why do you need GROUP BY when there is no aggregate function used the query?
Last edited by tamilselvan; 03-07-2007 at 01:11 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|