1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Long running session more than 2 days

Discussion in 'General' started by alexcol, Feb 2, 2018.

  1. alexcol

    alexcol Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Colombia
    Good evening, thanks again for your support and your time for this issue. I really appreciate it.

    I run the above query (TABLE(DBMS_XPLAN.display_cursor) to another session because the underlying session finished.
    But the ongoing query relates the same table, here you go with the output

    Could u tell me briefly if its possible what exactly this output means ? for instance, is it costly?, indexes, and so on in order to troubleshoot this problematic sql

    Query:
    Code (SQL):
          326       7365

    SELECT ED.ID,   ED.FRANCHISE,   ED.ANUM_OPERATOR,   EDS.EDR_STATUS_ID STATUS_FOR_NEXT_RECORD,   ED.EVENT_START_DATE,   AGR.ID CHECK_IF_AGG_EXIST,   SYSDATE,   EDS.ERROR_CODE ERROR_CODE_PREV,   EDR_DETAIL_STATUS_SEQ.NEXTVAL NEXT_STATUS_ID FROM EDR_DETAIL ED,   EDR_DETAIL_STATUS EDS,   OBC_AGREEMENT AGR WHERE ED.EDR_DETAIL_STATUS_ID = EDS.ID AND EDS.EDR_STATUS_ID      = 'PENDING' AND (ED.FRANCHISE             = AGR.FRANCHISE(+) AND ED.ANUM_OPERATOR          = AGR.OPERATOR(+)) AND agr.id IS NULL
    Output:
    Code (SQL):
    PLAN_TABLE_OUTPUT

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    SQL_ID  7x8bvzzpyts6m, child NUMBER 0

    -------------------------------------

    SELECT ED.ID,   ED.FRANCHISE,   ED.ANUM_OPERATOR,   EDS.EDR_STATUS_ID

    STATUS_FOR_NEXT_RECORD,   ED.EVENT_START_DATE,   AGR.ID

    CHECK_IF_AGG_EXIST,   SYSDATE,   EDS.ERROR_CODE ERROR_CODE_PREV,

    EDR_DETAIL_STATUS_SEQ.NEXTVAL NEXT_STATUS_ID FROM EDR_DETAIL ED,

    EDR_DETAIL_STATUS EDS,   OBC_AGREEMENT AGR WHERE

    ED.EDR_DETAIL_STATUS_ID = EDS.ID AND EDS.EDR_STATUS_ID         =

    'PENDING' AND (ED.FRANCHISE             = AGR.FRANCHISE(+) AND

    ED.ANUM_OPERATOR          = AGR.OPERATOR(+)) AND agr.id IS NULL


    Plan hash VALUE: 1182213507


    -------------------------------------------------------------------------------------------------------------------------------------

    | Id  | Operation                               | Name                     | E-ROWS |E-Bytes| Cost (%CPU)| E-TIME   | Pstart| Pstop |

    -------------------------------------------------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT                        |                          |        |       |  2580K(100)|          |       |       |

    |   1 |  SEQUENCE                               | EDR_DETAIL_STATUS_SEQ    |        |       |            |          |       |       |

    |   2 |   NESTED LOOPS                          |                          |   1696K|    93M|  2580K  (1)| 00:01:41 |       |       |

    |   3 |    NESTED LOOPS                         |                          |   1696K|    93M|  2580K  (1)| 00:01:41 |       |       |

    |*  4 |     FILTER                              |                          |        |       |            |          |       |       |

    |   5 |      MERGE JOIN OUTER                   |                          |   1696K|    67M|  2071K  (1)| 00:01:21 |       |       |

    |   6 |       TABLE ACCESS BY GLOBAL INDEX ROWID| EDR_DETAIL               |    169M|  4852M|  2071K  (1)| 00:01:21 | ROWID | ROWID |

    |   7 |        INDEX FULL SCAN                  | IDX_EDR_DETAIL_FRAN_OPER |    169M|       | 61051   (1)| 00:00:03 |       |       |

    |*  8 |       SORT JOIN                         |                          |    135 |  1620 |     3  (34)| 00:00:01 |       |       |

    |   9 |        VIEW                             | INDEX$_join$_003         |    135 |  1620 |     2   (0)| 00:00:01 |       |       |

    |* 10 |         HASH JOIN                       |                          |        |       |            |          |       |       |

    |  11 |          INDEX FAST FULL SCAN           | OBC_AGREEMENT_FR_OP_IDX  |    135 |  1620 |     1   (0)| 00:00:01 |       |       |

    |  12 |          INDEX FAST FULL SCAN           | OBC_AGREEMENT_PK         |    135 |  1620 |     1   (0)| 00:00:01 |       |       |

    |* 13 |     INDEX UNIQUE SCAN                   | EDR_DETAIL_STATUS_PK     |      1 |       |     1   (0)| 00:00:01 |       |       |

    |* 14 |    TABLE ACCESS BY GLOBAL INDEX ROWID   | EDR_DETAIL_STATUS        |      1 |    16 |     1   (0)| 00:00:01 | ROWID | ROWID |

    -------------------------------------------------------------------------------------------------------------------------------------


    Query Block Name / Object Alias (IDENTIFIED BY operation id):

    -------------------------------------------------------------


       1 - SEL$1

       6 - SEL$1        / ED@SEL$1

       7 - SEL$1        / ED@SEL$1

       9 - SEL$84D1845C / AGR@SEL$1

      10 - SEL$84D1845C

      11 - SEL$84D1845C / indexjoin$_alias$_001@SEL$84D1845C

      12 - SEL$84D1845C / indexjoin$_alias$_002@SEL$84D1845C

      13 - SEL$1        / EDS@SEL$1

      14 - SEL$1        / EDS@SEL$1


    Predicate Information (IDENTIFIED BY operation id):

    ---------------------------------------------------


       4 - FILTER("AGR"."ID" IS NULL)

       8 - access("ED"."FRANCHISE"="AGR"."FRANCHISE" AND "ED"."ANUM_OPERATOR"="AGR"."OPERATOR")

           FILTER(("ED"."ANUM_OPERATOR"="AGR"."OPERATOR" AND "ED"."FRANCHISE"="AGR"."FRANCHISE"))

      10 - access(ROWID=ROWID)

      13 - access("ED"."EDR_DETAIL_STATUS_ID"="EDS"."ID")

      14 - FILTER("EDS"."EDR_STATUS_ID"='PENDING')

    COLUMN Projection Information (IDENTIFIED BY operation id):

    -----------------------------------------------------------


       1 - "ED"."FRANCHISE"[VARCHAR2,10], "AGR"."FRANCHISE"[VARCHAR2,10], "ED"."ANUM_OPERATOR"[VARCHAR2,10],

           "AGR"."OPERATOR"[VARCHAR2,10], "ED".ROWID[ROWID,10], "ED"."ID"[NUMBER,22], "ED"."EDR_DETAIL_STATUS_ID"[NUMBER,22],

           "ED"."EVENT_START_DATE"[DATE,7], ROWID[ROWID,10], "ID"[NUMBER,22], "EDS".ROWID[ROWID,10], "EDS"."ID"[NUMBER,22],

           "EDS"."EDR_STATUS_ID"[VARCHAR2,20], "EDS"."ERROR_CODE"[VARCHAR2,15]

       2 - "ED"."FRANCHISE"[VARCHAR2,10], "AGR"."FRANCHISE"[VARCHAR2,10], "ED"."ANUM_OPERATOR"[VARCHAR2,10],

           "AGR"."OPERATOR"[VARCHAR2,10], "ED".ROWID[ROWID,10], "ED"."ID"[NUMBER,22], "ED"."EDR_DETAIL_STATUS_ID"[NUMBER,22],

           "ED"."EVENT_START_DATE"[DATE,7], ROWID[ROWID,10], "ID"[NUMBER,22], "EDS".ROWID[ROWID,10], "EDS"."ID"[NUMBER,22],

           "EDS"."EDR_STATUS_ID"[VARCHAR2,20], "EDS"."ERROR_CODE"[VARCHAR2,15]

       3 - "ED"."FRANCHISE"[VARCHAR2,10], "AGR"."FRANCHISE"[VARCHAR2,10], "ED"."ANUM_OPERATOR"[VARCHAR2,10],

           "AGR"."OPERATOR"[VARCHAR2,10], "ED".ROWID[ROWID,10], "ED"."ID"[NUMBER,22], "ED"."EDR_DETAIL_STATUS_ID"[NUMBER,22],

           "ED"."EVENT_START_DATE"[DATE,7], ROWID[ROWID,10], "ID"[NUMBER,22], "EDS".ROWID[ROWID,10], "EDS"."ID"[NUMBER,22]

       4 - "ED"."FRANCHISE"[VARCHAR2,10], "AGR"."FRANCHISE"[VARCHAR2,10], "ED"."ANUM_OPERATOR"[VARCHAR2,10],

           "AGR"."OPERATOR"[VARCHAR2,10], "ED".ROWID[ROWID,10], "ED"."ID"[NUMBER,22], "ED"."EDR_DETAIL_STATUS_ID"[NUMBER,22],

           "ED"."EVENT_START_DATE"[DATE,7], ROWID[ROWID,10], "ID"[NUMBER,22]

       5 - "ED"."FRANCHISE"[VARCHAR2,10], "AGR"."FRANCHISE"[VARCHAR2,10], "ED"."ANUM_OPERATOR"[VARCHAR2,10],

           "AGR"."OPERATOR"[VARCHAR2,10], "ED".ROWID[ROWID,10], "ED"."ID"[NUMBER,22], "ED"."EDR_DETAIL_STATUS_ID"[NUMBER,22],

           "ED"."EVENT_START_DATE"[DATE,7], ROWID[ROWID,10], "ID"[NUMBER,22]

       6 - "ED".ROWID[ROWID,10], "ED"."ID"[NUMBER,22], "ED"."FRANCHISE"[VARCHAR2,10], "ED"."ANUM_OPERATOR"[VARCHAR2,10],

           "ED"."EVENT_START_DATE"[DATE,7], "ED"."EDR_DETAIL_STATUS_ID"[NUMBER,22]

       7 - "ED".ROWID[ROWID,10], "ED"."FRANCHISE"[VARCHAR2,10], "ED"."ANUM_OPERATOR"[VARCHAR2,10]

       8 - (#keys=2) "AGR"."FRANCHISE"[VARCHAR2,10], "AGR"."OPERATOR"[VARCHAR2,10], ROWID[ROWID,10], "ID"[NUMBER,22]

       9 - ROWID[ROWID,10], "ID"[NUMBER,22], "OPERATOR"[VARCHAR2,10], "FRANCHISE"[VARCHAR2,10]

      10 - (#keys=1) ROWID[ROWID,10], ROWID[ROWID,10], "AGR"."OPERATOR"[VARCHAR2,10], "AGR"."FRANCHISE"[VARCHAR2,10],

           "AGR"."ID"[NUMBER,22]

      11 - ROWID[ROWID,10], "AGR"."FRANCHISE"[VARCHAR2,10], "AGR"."OPERATOR"[VARCHAR2,10]

      12 - ROWID[ROWID,10], "AGR"."ID"[NUMBER,22]

      13 - "EDS".ROWID[ROWID,10], "EDS"."ID"[NUMBER,22]

      14 - "EDS".ROWID[ROWID,10], "EDS"."EDR_STATUS_ID"[VARCHAR2,20], "EDS"."ERROR_CODE"[VARCHAR2,15]


    Note

    -----

       - dynamic statistics used: dynamic sampling (level=2)

       - Warning: basic plan statistics NOT available. These are ONLY collected WHEN:

           * hint 'gather_plan_statistics' IS used FOR the statement OR

           * parameter 'statistics_level' IS SET TO 'ALL', at SESSION OR system level

       - 1 SQL Plan Directive used FOR this statement

    96 ROWS selected.
     
  2. alexcol

    alexcol Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Colombia
    Good afternnoon. Thanks again for your support and valuable tips given in this thread ive learn a lot.

    In order to close this issue as a matter of knowledge how do i interpret the output of the above query execution plan? or a link that tells you how to analize this output?

    Thank you very much
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    773
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.

    For a start ,the expensive steps are in your sql plan of 5-7 ;9-12.
    1)
    on 7 step to system returned 167M rows;after (6 step) reading data table and as result 1.667M (5 step) rows.

    By estimate on plan it is ~ 1%.

    " merge join" that is expensive step,because needed sorting two sets.

    2) The steps 10-12 also are expensive, because oracle create virtual index
    and use hash-join.


    3) try this
    Code (SQL):

    SELECT  /*+ leading(EDS ED)  no_use_merge(EDS) no_use_merge(AGR)  */
       ED.ID,
       ED.FRANCHISE,
       ED.ANUM_OPERATOR,
       EDS.EDR_STATUS_ID STATUS_FOR_NEXT_RECORD,
       ED.EVENT_START_DATE,
       AGR.ID CHECK_IF_AGG_EXIST,
       SYSDATE,
       EDS.ERROR_CODE ERROR_CODE_PREV,
       EDR_DETAIL_STATUS_SEQ.NEXTVAL NEXT_STATUS_ID
    FROM
       EDR_DETAIL ED,
       EDR_DETAIL_STATUS EDS,
       OBC_AGREEMENT AGR
    WHERE
       ED.EDR_DETAIL_STATUS_ID = EDS.ID
       AND EDS.EDR_STATUS_ID  = 'PENDING'
       AND ED.FRANCHISE  = AGR.FRANCHISE(+)
       AND ED.ANUM_OPERATOR  = AGR.OPERATOR(+)
       AND agr.id IS NULL;
     
    can you execute this query and provide here result about of time of execution.

    How much rows in table with EDR_STATUS_ID = 'PENDING' by statistics and by real of data?


    Additional links :
    dbms_xplan

    white papers dbms_xplan
     
    Last edited: Mar 9, 2018
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    773
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    Is anyone result ?
     
  5. alexcol

    alexcol Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Colombia
    Good Morning, I have not been to allow to run any process nor any query to the datadase because Support Company and DBA were installing some patchs to fix the problem and i had no access to treh database, Today i run the query and yield no rows

    So what does it mean there are not rows for this query?


    Code (SQL):
    6:25:13 SQL> SELECT  /*+ leading(EDS ED)  no_use_merge(EDS) no_use_merge(AGR)  */
    06:25:41   2     ED.ID,
    06:25:41   3     ED.FRANCHISE,
    06:25:41   4     ED.ANUM_OPERATOR,
    06:25:42   5     EDS.EDR_STATUS_ID STATUS_FOR_NEXT_RECORD,
    06:25:42   6     ED.EVENT_START_DATE,
    06:25:42   7     AGR.ID CHECK_IF_AGG_EXIST,
    06:25:42   8     SYSDATE,
    06:25:42   9     EDS.ERROR_CODE ERROR_CODE_PREV,
    06:25:42  10     EDR_DETAIL_STATUS_SEQ.NEXTVAL NEXT_STATUS_ID
    06:25:42  11  FROM
    06:25:42  12     EDR_DETAIL ED,
    06:25:42  13     EDR_DETAIL_STATUS EDS,
    06:25:42  14     OBC_AGREEMENT AGR
    06:25:42  15  WHERE
    06:25:42  16     ED.EDR_DETAIL_STATUS_ID = EDS.ID
    06:25:42  17     AND EDS.EDR_STATUS_ID  = 'PENDING'
    06:25:42  18     AND ED.FRANCHISE  = AGR.FRANCHISE(+)
    06:25:42  19     AND ED.ANUM_OPERATOR  = AGR.OPERATOR(+)
    06:25:42  20     AND agr.id IS NULL;

    no ROWS selected