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.

probably your view is too complex

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

Post the explain plan.

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

---

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?