Explain plan Takes a long time

[Subha]

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.

[pando]

probably your view is too complex

[davey23uk]

why is it an issue - it gets explained in the end

[tamilselvan]

Post the explain plan.

[Subha]

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]

[tamilselvan]

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?

[Subha]

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 |            |
----------------------------------------------------------------------------------------------------------

[tamilselvan]

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?