3 Replies Latest reply: Apr 26, 2013 8:27 AM by Martin Preiss RSS

    Query Consuming too much time.

    933257
      Hi,
      i am using Release 10.2.0.4.0 version of oracle. I am having a query, its taking too much time(~7 minutes) for indexed read. Please help me to understand the reason and workaround for same.
        select *
       FROM a,
               b
         WHERE  a.xdt_docownerpaypk = b.paypk
               AND a.xdt_doctype = 'PURCHASEORDER'
               AND b.companypk = 1202829117
               AND a.xdt_createdt BETWEEN TO_DATE (
                                                                   '07/01/2009',
                                                                   'MM/DD/YYYY')
                                                            AND TO_DATE (
                                                                   '01/01/2010',
                                                                   'MM/DD/YYYY')
      ORDER BY a.xdt_createdt DESC;
      
      
      --------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                      | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
      --------------------------------------------------------------------------------------------------------------------------------------------------------
      |   1 |  SORT ORDER BY                 |                         |      1 |      1 |    907 |00:06:45.83 |   66716 |  60047 |   478K|   448K|  424K (0)|
      |*  2 |   TABLE ACCESS BY INDEX ROWID  | a                       |      1 |      1 |    907 |00:06:45.82 |   66716 |  60047 |       |       |          |
      |   3 |    NESTED LOOPS                |                         |      1 |      1 |   6977 |00:06:45.64 |   60045 |  60030 |       |       |          |
      |   4 |     TABLE ACCESS BY INDEX ROWID| b                       |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |       |       |          |
      |*  5 |      INDEX RANGE SCAN          | IDX_PAYIDENTITYCOMPANY  |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |       |       |          |
      |*  6 |     INDEX RANGE SCAN           | IDX_XDT_N7              |      1 |   3438 |   6975 |00:06:45.64 |   60041 |  60030 |       |       |          |
      --------------------------------------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - filter(("a"."XDT_CREATEDT"<=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                    "a"."XDT_CREATEDT">=TO_DATE(' 2009-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
         5 - access("b"."COMPANYPK"=1202829117)
         6 - access("XDT_DOCTYPE"='PURCHASEORDER' AND "a"."XDT_DOCOWNERPAYPK"="b"."PAYPK")
             filter("a"."XDT_DOCOWNERPAYPK"="b"."PAYPK")
      
      
      32 rows selected.
      
      
      index 'idx_xdt_n7' is on (xdt_doctype,action_date,xdt_docownerpaypk).
      index idx_xdt_n7 details are as below.
      blevel   distinct_keys   avg_leaf_blocks_per_key   avg_data_blocks_per_key   clustering_factor       num_rows 
      3         868840             1                         47                     24020933               69871000
      
      
      But when i am deriving exact value of paypk from table b and applying to the query, its using another index(idx_xdt_n4) which is on index 'idx_xdt_n4' is on (month,year,xdt_docownerpaypk,xdt_doctype,action_date)
      and completes within ~17 seconds. below is the query/plan details.
      
      
        select *
        FROM a
          WHERE a.xdt_docownerpaypk = 1202829132
                AND xdt_doctype = 'PURCHASEORDER'
               AND a.xdt_createdt BETWEEN TO_DATE (
                                                                    '07/01/2009',
                                                                    'MM/DD/YYYY')
                                                             AND TO_DATE (
                                                                    '01/01/2010',
                                                                    'MM/DD/YYYY')
       ORDER BY xdt_createdt DESC;
      
       ------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                    | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
      ------------------------------------------------------------------------------------------------------------------------------------------------------
      |   1 |  SORT ORDER BY               |                         |      1 |   3224 |    907 |00:00:02.19 |    7001 |    339 |   337K|   337K|  299K (0)|
      |*  2 |   TABLE ACCESS BY INDEX ROWID| a                       |      1 |   3224 |    907 |00:00:02.19 |    7001 |    339 |       |       |          |
      |*  3 |    INDEX SKIP SCAN           | IDX_XDT_N4              |      1 |  38329 |   6975 |00:00:02.08 |     330 |    321 |       |       |          |
      ------------------------------------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - filter(("a"."XDT_CREATEDT"<=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                    "a"."XDT_CREATEDT">=TO_DATE(' 2009-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
         3 - access("a"."XDT_DOCOWNERPAYPK"=1202829132 AND "XDT_DOCTYPE"='PURCHASEORDER')
             filter(("a"."XDT_DOCOWNERPAYPK"=1202829132 AND "XDT_DOCTYPE"='PURCHASEORDER'))
      
      
       
      index idx_xdt_n4 details are as below.
      
      blevel   distinct_keys   avg_leaf_blocks_per_key   avg_data_blocks_per_key   clustering_factor       num_rows 
      3         868840             1                         47                     23942833              70224133
      Edited by: 930254 on Apr 26, 2013 5:04 AM
        • 1. Re: Query Consuming too much time.
          user503699
          930254 wrote:
          Hi,
          i am using Release 10.2.0.4.0 version of oracle. I am having a query, its taking too much time(~7 minutes) for indexed read. Please help me to understand the reason and workaround for same.
          In your original query, most of the query execution time is spent range scanning the index IDX_XDT_N7 at step (6). The plan also suggests that during the range scan, almost all the data blocks were read from the disk (60030 physical reads out of 60041 buffer gets). So it is possible that reading index data from disk is taking much time. I believe there are 2 ways to approach this:
          1) Should the step (6) in the plan have to visit so many blocks ? Possible cause unhelpful index column order
          2) Should the step (6) in the plan take so much time to read index blocks from the disk? Possible causes slow disks, index contention etc.
          • 2. Re: Query Consuming too much time.
            sybrand_b
            Only the first column of the index is used, because the other columns are unknown.
            You could try to swap column 2 and 3, and have the date column last.

            ---------
            Sybrand Bakker
            Senior Oracle DBA
            • 3. Re: Query Consuming too much time.
              Martin Preiss
              the first query uses the predicate "XDT_DOCTYPE"='PURCHASEORDER' to determine the range of the index IDX_XDT_N7 that has to be scanned and uses the other predicates to filter out most of the index blocks. The second query uses an INDEX SKIP SCAN ignoring the first column of the index IDX_XDT_N4 and using the predicates for the following columns ("a"."XDT_DOCOWNERPAYPK"=1202829132 AND "XDT_DOCTYPE"='PURCHASEORDER') to get a much more selective access (reading only 330 blocks instead of > 60K).

              I think there are two possible options to improve the performance:

              1. If creating a new index is an option you could define an index on table A(xdt_doctype, xdt_docownerpaypk, xdt_createdt)
              2. If creating a new index is not an option you could use an INDEX SKIP SCAN Hint (INDEX_SS(A IDX_XDT_N4)) to order the CBO to use the second index (without a hint the CBO tends to ignore the option of using a SKIP SCAN in an NL join). But using Hints in production is rarely a good idea... In 11g you could you sql baselines to avoid such hints in the code.

              Regards

              Martin