This discussion is archived
10 Replies Latest reply: Feb 20, 2013 3:08 AM by rks RSS

Query tuning

rks Newbie
Currently Being Moderated
Can anyone help me to interpret the below explain plan details

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4164492234

----------------------------------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58 (4)|
| 1 | SORT ORDER BY | | 58 (4)|
| 2 | NESTED LOOPS | | |
| 3 | NESTED LOOPS | | 57 (2)|
| 4 | NESTED LOOPS | | 56 (2)|
|* 5 | HASH JOIN | | 11 (10)|

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
| 6 | NESTED LOOPS | | |
| 7 | NESTED LOOPS | | 8 (0)|
|* 8 | INDEX RANGE SCAN | USER_IDX | 3 (0)|
|* 9 | INDEX RANGE SCAN | USER_FILTER_USERSK | 2 (0)|
| 10 | TABLE ACCESS BY INDEX ROWID | USER_FILTER_LT | 5 (0)|
|* 11 | TABLE ACCESS FULL | CALLCENTER_D | 2 (0)|
| 12 | PARTITION RANGE SINGLE | | 3 (0)|
|* 13 | TABLE ACCESS BY LOCAL INDEX ROWID| ASSET_VOLUME_DETAIL_F | 3 (0)|
|* 14 | INDEX RANGE SCAN | AST_VOL_DTL_ACCT_CBC_IDX | 2 (0)|
|* 15 | INDEX UNIQUE SCAN | XPKACCOUNT_D | 0 (0)|
|* 16 | TABLE ACCESS BY INDEX ROWID | ACCOUNT_D | 1 (0)|

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("A"."CALLCENTER_SK"="C"."CALLCENTER_SK")
8 - access("USER_ID"='35287021-90EB-E111-AEC9-0025B500016E')
9 - access("A"."USER_SK"="B"."USER_SK")
11 - filter("C"."INCLUDE"='Include')
13 - filter("T1"."READDATE"<=TO_DATE(' 2012-08-22 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "T1"."INSCOPE"='1' AND "T1"."ISMANAGED"='1' AND
  • 1. Re: Query tuning
    asahide Expert
    Currently Being Moderated
    Hi,

    Show us your SQL..
    Regards,
  • 2. Re: Query tuning
    Niket Kumar Pro
    Currently Being Moderated
    Please find this in article how to read explain plan it will also help you in future...

    http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf
  • 3. Re: Query tuning
    rks Newbie
    Currently Being Moderated
    SELECT T1."ACCOUNT_SK", T1."CHARGEBACK_SK", T1."ISXEROX", T1."ISDUPLEX",
    T1."MODELCLASS_SK", T1."INSCOPE", T1."BASEPRICE", T1."ISCOLOR",
    T1."UPTIME", T1."UTILIZATION", T1."AVERAGEDAILYVOLUME",
    T1."AVERAGEMONTHLYVOLUME", T1."TOTALMONOIMPRESSIONSVOLUME",
    T1."TOTALCOLORIMPRESSIONSVOLUME", T1."TOTALCOPYIMPRESSIONSVOLUME",
    T1."TOTALSCANIMPRESSIONSVOLUME", T1."TOTALFAXIMPRESSIONSVOLUME",
    T1."TOTALPRINTIMPRESSIONSVOLUME", T1."TOTALDUPLEXVOLUME", T1."TOTALIMPRESSIONSVOLUME",
    T1."TOTALPAGEVOLUME",T1."PAGECOUNTSTARTCOUNT", T1."PAGECOUNTENDCOUNT",
    T1."PAGECOUNTVOLUME", T1."PAGECOUNTBASEIMPRESSIONS", T1."BLACKIMPRESSIONSMETERPRICE",
    T1."BLACKIMPRESSIONSSTARTCOUNT", T1."BLACKIMPRESSIONSENDCOUNT", T1."BLACKIMPRESSIONSVOLUME",
    T1."BLACKIMPRESSIONSBASEIMPRESSION", T1."COLORIMPRESSIONSSTARTCOUNT", T1."COLORIMPRESSIONSENDCOUNT",
    T1."COLORIMPRESSIONSVOLUME", T1."COLORIMPRESSIONSBASEIMPRESSION", T1."PRINTEDIMPRSNSSTARTCOUNT",
    T1."PRINTEDIMPRSNSENDCOUNT", T1."PRINTEDIMPRSNSVOLUME", T1."PRINTEDIMPRSNSBASEIMPRSNS",
    T1."COPIEDIMPRSNSSTARTCOUNT", T1."COPIEDIMPRSNSENDCOUNT", T1."COPIEDIMPRSNSVOLUME",
    T1."COPIEDIMPRSNSBASEIMPRSNS", T1."FAXIMPRSNSSTARTCOUNT", T1."FAXIMPRSNSENDCOUNT",
    T1."FAXIMPRSNSVOLUME", T1."FAXIMPRSNSBASEIMPRSNS", T1."SCANNEDIMPRSNSSTARTCOUNT",
    T1."SCANNEDIMPRSNSENDCOUNT", T1."SCANNEDIMPRSNSVOLUME", T1."TOTALIMPRSNSSTARTCOUNT",
    T1."TOTALIMPRSNSENDCOUNT", T1."TOTALIMPRSNSVOLUME", T1."TOTALIMPRSNSBASEIMPRSNS",
    T1."BWANDCOLOURL1STARTMETERCOUNT", T1."BWANDCOLOURL1ENDMETERCOUNT", T1."BWANDCOLOURL1VOLUME",
    T1."COLOURL2STARTMETERCOUNT", T1."COLOURL2ENDMETERCOUNT", T1."COLOURL2VOLUME",
    T1."COLOURL3STARTMETERCOUNT", T1."COLOURL3ENDMETERCOUNT", T1."COLOURL3VOLUME",
    T1."L1STARTMETERCOUNT", T1."L1ENDMETERCOUNT", T1."L1VOLUME", T1."L2STARTMETERCOUNT",
    T1."L2ENDMETERCOUNT", T1."L2VOLUME", T1."L3STARTMETERCOUNT", T1."L3ENDMETERCOUNT",
    T1."L3VOLUME", T1."ISENABLED", T1."ISMANAGED", T1."READDATE", T15."USER_ID"
    FROM MPSDM.ASSET_VOLUME_DETAIL_F T1, MPSDM.USER_SEC_VW T15
    WHERE (T15."ACCOUNT_SK" = T1."ACCOUNT_SK") AND (T15."CBC_SK" = T1."CHARGEBACK_SK")
    AND (T1."READDATE" <= TO_DATE('22-08-2012','DD-MM-YYYY'))
    AND (T1."READDATE" >= TO_DATE('01-08-2012','DD-MM-YYYY'))
    AND (T1."ISMANAGED" = '1') AND (T1."ISMANAGED" = '1')
    AND (T1."ISENABLED" = '1') AND (T1."INSCOPE" = '1')
    AND (T15."USER_ID" = '35287021-90EB-E111-AEC9-0025B500016E')
    ORDER BY T1."MODELCLASS_SK", T1."INSCOPE",
    T1."ISCOLOR", T1."ISDUPLEX", T1."ISXEROX";
  • 4. Re: Query tuning
    SomeoneElse Guru
    Currently Being Moderated
    What's your question?
  • 5. Re: Query tuning
    rks Newbie
    Currently Being Moderated
    the above query is taking long time than expected.it's taking nearly 15 mts to execute.I trying to optimize the qury based on the explin plan.But from the plan i could see it's using all the indexes.

    I trying to looking for some help from the experts to optimize the query (like hints)

    the main table ASSET_VOLUME_DETAIL_F is a partitioned table based on READDATE column

    Edited by: rks on Feb 15, 2013 5:54 AM
  • 6. Re: Query tuning
    riedelme Expert
    Currently Being Moderated
    rks wrote:
    Can anyone help me to interpret the below explain plan details
    SELECT T1."ACCOUNT_SK",
      T1."CHARGEBACK_SK",
      T1."ISXEROX",
      T1."ISDUPLEX",
      T1."MODELCLASS_SK",
      T1."INSCOPE",
      T1."BASEPRICE",
      T1."ISCOLOR",
      T1."UPTIME",
      T1."UTILIZATION",
      T1."AVERAGEDAILYVOLUME",
      T1."AVERAGEMONTHLYVOLUME",
      T1."TOTALMONOIMPRESSIONSVOLUME",
      T1."TOTALCOLORIMPRESSIONSVOLUME",
      T1."TOTALCOPYIMPRESSIONSVOLUME",
      T1."TOTALSCANIMPRESSIONSVOLUME",
      T1."TOTALFAXIMPRESSIONSVOLUME",
      T1."TOTALPRINTIMPRESSIONSVOLUME",
      T1."TOTALDUPLEXVOLUME",
      T1."TOTALIMPRESSIONSVOLUME",
      T1."TOTALPAGEVOLUME",
      T1."PAGECOUNTSTARTCOUNT",
      T1."PAGECOUNTENDCOUNT",
      T1."PAGECOUNTVOLUME",
      T1."PAGECOUNTBASEIMPRESSIONS",
      T1."BLACKIMPRESSIONSMETERPRICE",
      T1."BLACKIMPRESSIONSSTARTCOUNT",
      T1."BLACKIMPRESSIONSENDCOUNT",
      T1."BLACKIMPRESSIONSVOLUME",
      T1."BLACKIMPRESSIONSBASEIMPRESSION",
      T1."COLORIMPRESSIONSSTARTCOUNT",
      T1."COLORIMPRESSIONSENDCOUNT",
      T1."COLORIMPRESSIONSVOLUME",
      T1."COLORIMPRESSIONSBASEIMPRESSION",
      T1."PRINTEDIMPRSNSSTARTCOUNT",
      T1."PRINTEDIMPRSNSENDCOUNT",
      T1."PRINTEDIMPRSNSVOLUME",
      T1."PRINTEDIMPRSNSBASEIMPRSNS",
      T1."COPIEDIMPRSNSSTARTCOUNT",
      T1."COPIEDIMPRSNSENDCOUNT",
      T1."COPIEDIMPRSNSVOLUME",
      T1."COPIEDIMPRSNSBASEIMPRSNS",
      T1."FAXIMPRSNSSTARTCOUNT",
      T1."FAXIMPRSNSENDCOUNT",
      T1."FAXIMPRSNSVOLUME",
      T1."FAXIMPRSNSBASEIMPRSNS",
      T1."SCANNEDIMPRSNSSTARTCOUNT",
      T1."SCANNEDIMPRSNSENDCOUNT",
      T1."SCANNEDIMPRSNSVOLUME",
      T1."TOTALIMPRSNSSTARTCOUNT",
      T1."TOTALIMPRSNSENDCOUNT",
      T1."TOTALIMPRSNSVOLUME",
      T1."TOTALIMPRSNSBASEIMPRSNS",
      T1."BWANDCOLOURL1STARTMETERCOUNT",
      T1."BWANDCOLOURL1ENDMETERCOUNT",
      T1."BWANDCOLOURL1VOLUME",
      T1."COLOURL2STARTMETERCOUNT",
      T1."COLOURL2ENDMETERCOUNT",
      T1."COLOURL2VOLUME",
      T1."COLOURL3STARTMETERCOUNT",
      T1."COLOURL3ENDMETERCOUNT",
      T1."COLOURL3VOLUME",
      T1."L1STARTMETERCOUNT",
      T1."L1ENDMETERCOUNT",
      T1."L1VOLUME",
      T1."L2STARTMETERCOUNT",
      T1."L2ENDMETERCOUNT",
      T1."L2VOLUME",
      T1."L3STARTMETERCOUNT",
      T1."L3ENDMETERCOUNT",
      T1."L3VOLUME",
      T1."ISENABLED",
      T1."ISMANAGED",
      T1."READDATE",
      T15."USER_ID"
    FROM MPSDM.ASSET_VOLUME_DETAIL_F T1,
      MPSDM.USER_SEC_VW T15
    WHERE (T15."ACCOUNT_SK" = T1."ACCOUNT_SK")
    AND (T15."CBC_SK"       = T1."CHARGEBACK_SK")
    AND (T1."READDATE"     <= TO_DATE('22-08-2012','DD-MM-YYYY'))
    AND (T1."READDATE"     >= TO_DATE('01-08-2012','DD-MM-YYYY'))
    AND (T1."ISMANAGED"     = '1')
    AND (T1."ISMANAGED"     = '1')
    AND (T1."ISENABLED"     = '1')
    AND (T1."INSCOPE"       = '1')
    AND (T15."USER_ID"      = '35287021-90EB-E111-AEC9-0025B500016E')
    ORDER BY T1."MODELCLASS_SK",
      T1."INSCOPE",
      T1."ISCOLOR",
      T1."ISDUPLEX",
      T1."ISXEROX";
    >
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 4164492234

    ----------------------------------------------------------------------------------------
    | Id | Operation | Name | Cost (%CPU)|
    ----------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 58 (4)|
    | 1 | SORT ORDER BY | | 58 (4)|
    | 2 | NESTED LOOPS | | |
    | 3 | NESTED LOOPS | | 57 (2)|
    | 4 | NESTED LOOPS | | 56 (2)|
    |* 5 | HASH JOIN | | 11 (10)|

    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------------------
    | 6 | NESTED LOOPS | | |
    | 7 | NESTED LOOPS | | 8 (0)|
    |* 8 | INDEX RANGE SCAN | USER_IDX | 3 (0)|
    |* 9 | INDEX RANGE SCAN | USER_FILTER_USERSK | 2 (0)|
    | 10 | TABLE ACCESS BY INDEX ROWID | USER_FILTER_LT | 5 (0)|
    |* 11 | TABLE ACCESS FULL | CALLCENTER_D | 2 (0)|
    | 12 | PARTITION RANGE SINGLE | | 3 (0)|
    |* 13 | TABLE ACCESS BY LOCAL INDEX ROWID| ASSET_VOLUME_DETAIL_F | 3 (0)|
    |* 14 | INDEX RANGE SCAN | AST_VOL_DTL_ACCT_CBC_IDX | 2 (0)|
    |* 15 | INDEX UNIQUE SCAN | XPKACCOUNT_D | 0 (0)|
    |* 16 | TABLE ACCESS BY INDEX ROWID | ACCOUNT_D | 1 (0)|

    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    5 - access("A"."CALLCENTER_SK"="C"."CALLCENTER_SK")
    8 - access("USER_ID"='35287021-90EB-E111-AEC9-0025B500016E')
    9 - access("A"."USER_SK"="B"."USER_SK")
    11 - filter("C"."INCLUDE"='Include')
    13 - filter("T1"."READDATE"<=TO_DATE(' 2012-08-22 00:00:00', 'syyyy-mm-dd
    hh24:mi:ss') AND "T1"."INSCOPE"='1' AND "T1"."ISMANAGED"='1' AND
    Good job using the _vw suffix in the SQL.  I was confused at first why there were table aliases listed in the predicates that weren't in the SQL.

    Joining views to tables or other views can use extra resources. This complicates tuning a lot because you will have to track down the view to see what it is doing.

    The full table scan at step 11 may be a problem. It appears to be the 2nd (lookup) table in a nested loops join. An FTS on the first table in a nested loops join might be okay but the second table should always be indedxed. It is slightly hard to tell since the plan steps were not indented and it is diffuclt to know what the step relationships are. Check the indexes on the table for that step and see if an index will help
  • 7. Re: Query tuning
    rks Newbie
    Currently Being Moderated
    Hi,

    Thank you for your suggestion.Since the Following table is going for a FTS

    * 11     TABLE ACCESS FULL     CALLCENTER_D

    and this table is part of the VW. Please find the view details below
    CREATE OR REPLACE VIEW USER_SEC_VW
    (ACCOUNT_SK, CBC_SK, USER_ID, CALLCENTER_SK, ISMANAGED)
    AS
    SELECT a.account_sk, cbc_sk, user_id, a.callcenter_sk, ismanaged
    FROM mpsdm.user_filter_lt a, mpsdm.user_lt b, mpsdm.callcenter_d c , ACCOUNT_D D
    WHERE a.user_sk = b.user_sk
    AND a.callcenter_sk = c.callcenter_sk
    AND D.ACCOUNT_SK = a.account_sk
    and d.IS_NONDEMO='Y'
    AND c.include = 'Include'

    I have created index on column "include" on table callcenter_d.
    Please let me know if my understanding is correct from your suggestion..
  • 8. Re: Query tuning
    Etbin Guru
    Currently Being Moderated
    You might transform the view definition into exaggerated predicate pushing to see what happens as this can greatly reduce the number of rows to be joined.
    It's something the optimizer is known to be very goot at, but it might help in situations when the optimizer is not aware of some details you are.
    You just undo the predicate pushing you're certain that make the present situation worse
     select t1.account_sk,
           t1.chargeback_sk,
           t1.isxerox,
           t1.isduplex,
           t1.modelclass_sk,
           t1.inscope,
           t1.baseprice,
           t1.iscolor,
           t1.uptime,
           t1.utilization,
           t1.averagedailyvolume,
           t1.averagemonthlyvolume,
           t1.totalmonoimpressionsvolume,
           t1.totalcolorimpressionsvolume,
           t1.totalcopyimpressionsvolume,
           t1.totalscanimpressionsvolume,
           t1.totalfaximpressionsvolume,
           t1.totalprintimpressionsvolume,
           t1.totalduplexvolume,
           t1.totalimpressionsvolume,
           t1.totalpagevolume,
           t1.pagecountstartcount,
           t1.pagecountendcount,
           t1.pagecountvolume,
           t1.pagecountbaseimpressions,
           t1.blackimpressionsmeterprice,
           t1.blackimpressionsstartcount,
           t1.blackimpressionsendcount,
           t1.blackimpressionsvolume,
           t1.blackimpressionsbaseimpression,
           t1.colorimpressionsstartcount,
           t1.colorimpressionsendcount,
           t1.colorimpressionsvolume,
           t1.colorimpressionsbaseimpression,
           t1.printedimprsnsstartcount,
           t1.printedimprsnsendcount,
           t1.printedimprsnsvolume,
           t1.printedimprsnsbaseimprsns,
           t1.copiedimprsnsstartcount,
           t1.copiedimprsnsendcount,
           t1.copiedimprsnsvolume,
           t1.copiedimprsnsbaseimprsns,
           t1.faximprsnsstartcount,
           t1.faximprsnsendcount,
           t1.faximprsnsvolume,
           t1.faximprsnsbaseimprsns,
           t1.scannedimprsnsstartcount,
           t1.scannedimprsnsendcount,
           t1.scannedimprsnsvolume,
           t1.totalimprsnsstartcount,
           t1.totalimprsnsendcount,
           t1.totalimprsnsvolume,
           t1.totalimprsnsbaseimprsns,
           t1.bwandcolourl1startmetercount,
           t1.bwandcolourl1endmetercount,
           t1.bwandcolourl1volume,
           t1.colourl2startmetercount,
           t1.colourl2endmetercount,
           t1.colourl2volume,
           t1.colourl3startmetercount,
           t1.colourl3endmetercount,
           t1.colourl3volume,
           t1.l1startmetercount,
           t1.l1endmetercount,
           t1.l1volume,
           t1.l2startmetercount,
           t1.l2endmetercount,
           t1.l2volume,
           t1.l3startmetercount,
           t1.l3endmetercount,
           t1.l3volume,
           t1.isenabled,
           t1.ismanaged,
           t1.readdate,
           t15.user_id
      from mpsdm.asset_volume_detail_f t1,
    /*     mpsdm.user_sec_vw t15 */
           (select a.account_sk,
                   ?.cbc_sk,  /* no alias given */
                   ?.user_id  /* no alias given */
              from mpsdm.user_filter_lt a,
                   (select user_sk,user_id  /* user_id is a guess */
                      from mpsdm.user_lt
                     where user_id = '35287021-90EB-E111-AEC9-0025B500016E'
                   ) b,
                   (select callcenter_sk
                      from mpsdm.callcenter_d
                     where include = 'Include'
                   ) c,
                   (select account_sk,cbc_sk  /* cbc_sk is a guess */
                      from account_d
                     where is_nondemo = 'Y'
                   ) d
             where a.user_sk       = b.user_sk
               and a.callcenter_sk = c.callcenter_sk
               and d.account_sk    = a.account_sk
           ) t15
     where (t15.account_sk = t1.account_sk)
       and (t15.cbc_sk     = t1.chargeback_sk)
       and (t1.readdate    <= to_date('22-08-2012','dd-mm-yyyy'))
       and (t1.readdate    >= to_date('01-08-2012','dd-mm-yyyy'))
       and (t1.ismanaged   = '1')
       and (t1.ismanaged   = '1')
       and (t1.isenabled   = '1')
       and (t1.inscope     = '1')
    order by t1.modelclass_sk,
             t1.inscope,
             t1.iscolor,
             t1.isduplex,
             t1.isxerox
    Regards

    Etbin
  • 9. Re: Query tuning
    riedelme Expert
    Currently Being Moderated
    rks wrote:
    Hi,

    Thank you for your suggestion.Since the Following table is going for a FTS

    * 11     TABLE ACCESS FULL     CALLCENTER_D

    and this table is part of the VW. Please find the view details below
    CREATE OR REPLACE VIEW USER_SEC_VW
    (ACCOUNT_SK, CBC_SK, USER_ID, CALLCENTER_SK, ISMANAGED)
    AS
    SELECT a.account_sk, cbc_sk, user_id, a.callcenter_sk, ismanaged
    FROM mpsdm.user_filter_lt a, mpsdm.user_lt b, mpsdm.callcenter_d c , ACCOUNT_D D
    WHERE a.user_sk = b.user_sk
    AND a.callcenter_sk = c.callcenter_sk
    AND D.ACCOUNT_SK = a.account_sk
    and d.IS_NONDEMO='Y'
    AND c.include = 'Include'

    I have created index on column "include" on table callcenter_d.
    Please let me know if my understanding is correct from your suggestion..
    I was thinking a composite index on d.account_sk and d.is_nondemo too.

    Did the index you created help?

    Did you consider Etbin's ideas?
  • 10. Re: Query tuning
    rks Newbie
    Currently Being Moderated
    Hi Etbin,

    I tried the way you suggested..but that too taking long time.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points