10 Replies Latest reply: Feb 20, 2013 5:08 AM by rks RSS

    Query tuning

    rks
      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
          Hi,

          Show us your SQL..
          Regards,
          • 2. Re: Query tuning
            Niket Kumar
            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
              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
                What's your question?
                • 5. Re: Query tuning
                  rks
                  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
                    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
                      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
                        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
                          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
                            Hi Etbin,

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