Explain plan Takes a long time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Explain plan Takes a long time

  1. #1
    Join Date
    Feb 2001
    Posts
    119

    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 04:09 PM.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    probably your view is too complex

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    why is it an issue - it gets explained in the end

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Post the explain plan.

  5. #5
    Join Date
    Feb 2001
    Posts
    119
    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]

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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?

  7. #7
    Join Date
    Feb 2001
    Posts
    119
    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 12:05 PM.

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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 12: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
  •  



Click Here to Expand Forum to Full Width