Explain plan Takes a long time

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