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!

Query with views taking long to run, needs performance tuning

Discussion in 'SQL PL/SQL' started by Bharat, May 31, 2013.

  1. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi all,

    We had developed the following query and attached to concurrent program for some reporting purpose. When submitting the program its taking more than 30 minutes to complete. Can anyone suggest us to increase the performance of the query.

    Code (Text):

    SELECT A.PROJECT_ID, B.NAME PROJECT_NAME, B.SEGMENT1 PROJECT_NUMBER,
    B.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORG_ID,
    B.ORGANIZATION_NAME PROJECT_ORG, SUBSTR(B.PROJECT_TYPE,1,2) PROJECT_TYPE,
    C.INCURRED_BY_PERSON_ID PERSON_ID,
    (TRIM(PER.FIRST_NAME)||' '||TRIM(PER.LAST_NAME)) EMP_NAME,
    C.EXPENDITURE_TYPE, A.DRAFT_INVOICE_NUM, A.LINE_NUM,
    NVL(C.UNIT_OF_MEASURE,'NA') UNIT_OF_MEASURE,
    DECODE(C.UNIT_OF_MEASURE,'HOURS',SUM(C.QUANTITY),1) QTY,
    DECODE(C.UNIT_OF_MEASURE,'HOURS',C.BILL_RATE,SUM(C.QUANTITY)) RATE,
    TO_CHAR(MIN(C.EXPENDITURE_ITEM_DATE),'DD-MON-YYYY') FROM_DATE,
    TO_CHAR(MAX(C.EXPENDITURE_ITEM_DATE),'DD-MON-YYYY') TO_DATE
    FROM PA_DRAFT_INVOICE_LINES_V A,
    PA_PROJECTS_V B,
    PA_DRAFT_INV_LINE_DETAILS_V C,
    (SELECT * FROM PER_ALL_PEOPLE_F
    WHERE TRUNC(SYSDATE) BETWEEN TRUNC(EFFECTIVE_START_DATE) AND TRUNC(NVL(EFFECTIVE_END_DATE,SYSDATE+10))) PER
    WHERE A.PROJECT_ID = B.PROJECT_ID
    AND A.PROJECT_ID = C.PROJECT_ID(+)
    AND A.DRAFT_INVOICE_NUM = C.DRAFT_INVOICE_NUM(+)
    AND A.LINE_NUM = C.DRAFT_INVOICE_ITEM_LINE_NUM(+)
    AND C.INCURRED_BY_PERSON_ID = PER.PERSON_ID(+)
    GROUP BY A.PROJECT_ID, B.NAME, B.SEGMENT1, B.CARRYING_OUT_ORGANIZATION_ID, B.ORGANIZATION_NAME,
    B.PROJECT_TYPE, C.INCURRED_BY_PERSON_ID, (TRIM(PER.FIRST_NAME)||' '||TRIM(PER.LAST_NAME)),
    C.EXPENDITURE_TYPE, A.DRAFT_INVOICE_NUM, A.LINE_NUM, C.UNIT_OF_MEASURE, C.BILL_RATE
     
    Finally after 1 hour its completed.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    An execution plan would be beneficial as no one can tell, just by looking at that code, what could be causing the holdup.

    Please post the query plan for this statement.
     
  3. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Might also check the plans for the views as well assuming the _V's are actually views. I do question the select in the FROM clause...it doesn't appear to shortcut anything.
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi David,

    Can you please let me know how to know execution plan for the query ?
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    V$SQL_PLAN can provide that information; you will need the sql_id of the query which can be obtained from that same view or from V$SQL:

    Code (SQL):

    SELECT sql_id FROM v$sql WHERE sql_text LIKE 'SELECT A.PROJECT_ID, B.NAME PROJECT_NAME, B.SEGMENT1 PROJECT_NUMBER,%';
     
    You can then retrieve the plan from V$SQL_PLAN using this set of queries:

    Code (SQL):

    COLUMN "Operation" format A40
    COLUMN "Object Name" format A30
    COLUMN "PStart" format A6
    COLUMN "PStop" format A6
    COLUMN "I" format A3
    COLUMN ACCESS_PREDICATES format A80
    COLUMN FILTER_PREDICATES format A80
    SET LINES 9999
    SET pages 99
    SET trimspool ON
    SET verify off
    SET heading ON
    SELECT  /*+ NO_MERGE */ rownum  ||
    decode(access_predicates,NULL,decode(filter_predicates,NULL,'','*'),'*') "I",
            substr(lpad(' ',2*(depth-1)) || operation,1,20) ||
            decode(options,NULL,'',' (' || options || ')') "Operation",
            substr(object_name,1,30) "Object Name",
            cardinality "# Rows",
            bytes,
            cost,
            partition_start "PStart",
            partition_stop "PStop"
      FROM (SELECT * FROM v$sql_plan WHERE sql_id = '&&1') a
      START WITH id = 0
      CONNECT BY prior id = parent_id
          AND prior nvl(sql_id, 0 ) = nvl(sql_id, 0 )
      ORDER BY id, POSITION
    /
    SET heading off
    SELECT 'Access Predicates .....' FROM dual;
    SELECT * FROM (
    SELECT  /*+ NO_MERGE */ rownum  ||
    decode(access_predicates,NULL,decode(filter_predicates,NULL,'','*'),'*') "I",
        decode(access_predicates,NULL,'###',access_predicates) "ACCESS_PREDICATES"
      FROM (SELECT * FROM v$sql_plan WHERE sql_id = '&&1') a
      START WITH id = 0
      CONNECT BY prior id = parent_id
          AND prior nvl(sql_id, 0 ) = nvl(sql_id, 0 )
      ORDER BY id, POSITION
    ) WHERE access_predicates <> '###'
    /
    SET heading off
    SELECT 'Filter predicates .....' FROM dual;
    SELECT * FROM (
    SELECT  /*+ NO_MERGE */ rownum  ||
    decode(access_predicates,NULL,decode(filter_predicates,NULL,'','*'),'*') "I",
        decode(filter_predicates,NULL,'###',filter_predicates) "FILTER_PREDICATES"
      FROM (SELECT * FROM v$sql_plan WHERE sql_id = '&&1') a
      START WITH id = 0
      CONNECT BY prior id = parent_id
          AND prior nvl(sql_id, 0 ) = nvl(sql_id, 0 )
      ORDER BY id, POSITION
    ) WHERE filter_predicates <> '###'
    /

     
     
    Please post the results so we can see the plan and possibly find where the query is running long.
     
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi David,

    Please find the attached excel sheet for execution plan output.
     

    Attached Files:

  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Check your statistics -- you shouldn't be seeing a MERGE JOIN CARTESIAN in your plan.
     
  8. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi David,

    Can you please explain a bit more on this. As we are getting required records but taking lots of time to fetch the results and these views are standard one as given by Oracle
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which explains much, really. Normally one does NOT want a merge join cartesian in a plan. You also did not use the queries I provided else there would have been row counts and bytes returned values:

    Code (SQL):
    SQL> @plan_query 7kvy2vdfs9ffv
     
    I   Operation                                Object Name                        # ROWS      BYTES    COST PStart PStop
    --- ---------------------------------------- ------------------------------ ---------- ---------- ---------- ------ ------
    1   DELETE STATEMENT                                                                                   15
    2   DELETE                                   SCHEDULER$_EVENT_LOG
    3     NESTED LOOPS                                                                1472      44160      15
    4       VIEW                                 VW_NSO_1                             1472      19136      14
    5         SORT (UNIQUE)                                                           1472      60352
    6*          FILTER
    7*            HASH JOIN (RIGHT OUTER)                                             1472      60352      14
    8               TABLE AC (FULL)              SCHEDULER$_CLASS                       13        104       2
    9*              TABLE AC (FULL)              SCHEDULER$_EVENT_LOG                 1472      48576      11
    10*     INDEX (RANGE SCAN)                   SCHEDULER$_INSTANCE_PK                  1         17       0
     
    10 ROWS selected.

    Access Predicates .....

    7*  "E"."CLASS_ID"="C"."OBJ#"
    10* "LOG_ID"="LOG_ID" AND "DBID" IS NULL

    FILTER predicates .....

    6*  SYS_EXTRACT_UTC("E"."LOG_DATE")<SYS_EXTRACT_UTC(:B2-NUMTODSINTERVAL(NVL("C"."LOG
        _HISTORY"
    ,:B1),'DAY'))
    9*  ("E"."TYPE#"=66 AND "OPERATION" NOT LIKE 'CHAIN%' AND (BITAND("E"."FLAGS",2)=0 O
        R "E"."FLAGS" IS NULL))

    SQL>
    It is difficult to determine if the merge join cartesian is between two multi-row tables or a multi-row table and a single row table.

    Normally a merge join cartesian against two tables with many rows each returns the desired data AND a good bit of useless data based on un-correlated data (keys don't match). This is not a usually good thing.