14 Replies Latest reply: Nov 21, 2012 7:54 AM by Miled Moukhtafi RSS

    Tuning Query "Between"

    Miled Moukhtafi
      Dear sir,

      we are using oracle DB 10.2.0.5 we are using program without sources "I can't change on the queries-Built in Formes- but i can work on database"

      when we make sale order it check the discount structure, so for each item it's checking this table, so for one sale order with five items it takes 5 minutes, we are planing to buy new system but for time been i'm trying to tune the following query, and the details as showing below:
      CREATE TABLE test (
       FUNC_CODE VARCHAR2 (60),  
       FROM_LEV_01 VARCHAR2 (60), 
       UPTO_LEV_01 VARCHAR2 (60),  
       FROM_LEV_02 VARCHAR2 (60),  
       UPTO_LEV_02 VARCHAR2 (60),  
       FROM_LEV_03 VARCHAR2 (60), 
       UPTO_LEV_03 VARCHAR2 (60),  
       FROM_LEV_04 VARCHAR2 (60),  
       UPTO_LEV_04 VARCHAR2 (60),  
       FROM_LEV_05 VARCHAR2 (60),  
       UPTO_LEV_05 VARCHAR2 (60),  
       FROM_LEV_06 VARCHAR2 (60), 
       UPTO_LEV_06 VARCHAR2 (60),  
       FROM_LEV_07 VARCHAR2 (60),  
       UPTO_LEV_07 VARCHAR2 (60),  
       FROM_LEV_08 VARCHAR2 (60),  
       UPTO_LEV_08 VARCHAR2 (60),  
       FROM_LEV_09 VARCHAR2 (60), 
       UPTO_LEV_09 VARCHAR2 (60),  
       FROM_LEV_10 VARCHAR2 (60),  
       UPTO_LEV_10 VARCHAR2 (60),  
       FROM_LEV_11 VARCHAR2 (60),  
       UPTO_LEV_11 VARCHAR2 (60),  
       FROM_LEV_12 VARCHAR2 (60),  
       UPTO_LEV_12 VARCHAR2 (60),
       flex_01 VARCHAR2 (60),
       flex_02 VARCHAR2 (60),
       flex_03 VARCHAR2 (60),
       flex_04 VARCHAR2 (60),
       flex_05 VARCHAR2 (60),
       flex_06 VARCHAR2 (60),
       flex_07 VARCHAR2 (60),
       flex_08 VARCHAR2 (60),
       flex_09 VARCHAR2 (60)
      );   
      
      CREATE INDEX test_idx_01 ON test(          
       FUNC_CODE,  FROM_LEV_01,  UPTO_LEV_01,  FROM_LEV_02,  UPTO_LEV_02,  FROM_LEV_03, 
       UPTO_LEV_03,  FROM_LEV_04,  UPTO_LEV_04,  FROM_LEV_05,  UPTO_LEV_05,  FROM_LEV_06, 
       UPTO_LEV_06,  FROM_LEV_07,  UPTO_LEV_07,  FROM_LEV_08,  UPTO_LEV_08,  FROM_LEV_09, 
       UPTO_LEV_09,  FROM_LEV_10,  UPTO_LEV_10,  FROM_LEV_11,  UPTO_LEV_11,  FROM_LEV_12,  UPTO_LEV_12);   
       
      SQL> SET autotrace ON EXPLAIN;
      SQL> SELECT *
        FROM test
       WHERE FUNC_CODE = '4'
         AND 'FMCG2' BETWEEN from_lev_01 AND upto_lev_01
         AND 'FMCG201' BETWEEN from_lev_02 AND upto_lev_02
         AND '137' BETWEEN from_lev_03 AND upto_lev_03
         AND '188' BETWEEN from_lev_04 AND upto_lev_04
         AND '268' BETWEEN from_lev_05 AND upto_lev_05
         AND '452' BETWEEN from_lev_06 AND upto_lev_06
         AND '0' BETWEEN from_lev_07 AND upto_lev_07
         AND 'FMCG2' BETWEEN from_lev_08 AND upto_lev_08
         AND 'FMCG208' BETWEEN from_lev_09 AND upto_lev_09
         AND 'FMCG2' BETWEEN from_lev_10 AND upto_lev_10
         AND 'C2748' BETWEEN from_lev_11 AND upto_lev_11
         AND '10048' BETWEEN from_lev_12 AND upto_lev_12;
         
         
      SELECT STATEMENT, GOAL = ALL_ROWS               Cost=48736     Cardinality=1     Bytes=317     CPU cost=5185411896          Time=585
       HASH UNIQUE               Cost=48736     Cardinality=1     Bytes=317     CPU cost=5185411896          Time=585
        TABLE ACCESS FULL     Object owner=ORION     Object name=test     Cost=48735     Cardinality=1     Bytes=317     CPU cost=5166262434          Time=585
        • 1. Re: Tuning Query "Between"
          jeneesh
          What is the total records in your actual table?

          How many distinct values for FUNC_CODE column?

          What is the cardinality of each value in FUNC_CODE ?

          Cant you think of an INDEX on FUNC_CODE ?

          And read FAQ: {message:id=9360003}
          • 2. Re: Tuning Query "Between"
            Miled Moukhtafi
            Dear Jeneesh,

            total records : 4,854,576

            Func Code : only one value "4"

            thanks for your time.
            • 3. Re: Tuning Query "Between"
              Purvesh K
              Miled Moukhtafi wrote:
              Dear Jeneesh,

              total records : 4,854,576

              Func Code : only one value "4"

              thanks for your time.
              Can you post the output of following?
              select count(*) from test where func_code = '4';
              
              select count(*) from test where func_code is null;
              If you have more records for FUNC_CODE as 4, then, IMHO, it might not help you as Optimizer may choose to still go for a Table Full Scan rather than Index Scan. However, vice-versa, you should think of adding an index.

              I think you should think about adding indexes for the other columns viz. from_lev_01, upto_lev_01 etc.

              Can you try this and post the Explain plans and other details as mentioned at {message:id=3292438}?

              BTW, are your stats up-to-date? If not please try to get the latest and re-check the query execution time.
              • 4. Re: Tuning Query "Between"
                Miled Moukhtafi
                Dear Purvesh,
                            
                SELECT count(*) from test;
                4,854,576
                
                
                select count(*) from test where func_code = '4';
                4,854,576
                
                
                select count(*) from test where func_code is null;
                0
                as i mention before we have index:
                CREATE INDEX test_idx_01 ON test(          
                 FUNC_CODE,  FROM_LEV_01,  UPTO_LEV_01,  FROM_LEV_02,  UPTO_LEV_02,  FROM_LEV_03, 
                 UPTO_LEV_03,  FROM_LEV_04,  UPTO_LEV_04,  FROM_LEV_05,  UPTO_LEV_05,  FROM_LEV_06, 
                 UPTO_LEV_06,  FROM_LEV_07,  UPTO_LEV_07,  FROM_LEV_08,  UPTO_LEV_08,  FROM_LEV_09, 
                 UPTO_LEV_09,  FROM_LEV_10,  UPTO_LEV_10,  FROM_LEV_11,  UPTO_LEV_11,  FROM_LEV_12,  UPTO_LEV_12);   
                 
                thanks for your time.
                • 5. Re: Tuning Query "Between"
                  Purvesh K
                  I apologize for missing the Create Index part in our original post.

                  I am out of Ideas as you have 100% records with FUNC_CODE as 4 and that will, almost certainly, not use an Index to fetch the data.

                  Better wait until you receive some useful responses from Performance experts.
                  • 6. Re: Tuning Query "Between"
                    jeneesh
                    Did you read the FAQ, as already posted?
                    • 7. Re: Tuning Query "Between"
                      Miled Moukhtafi
                      Dear jeneesh,

                      thank you for the replying i read the link, but excuse me i didn't get what you want me to do exactly!!
                      but kindly check the explain plan as requested from the link:
                      Plan hash value: 1218997218
                            
                      --------------------------------------------------------------------------------------------
                      | Id  | Operation          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
                      --------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT   |                       |     1 |   317 | 48736   (1)| 00:09:45 |
                      |*  1 |   TABLE ACCESS FULL| test                  |     1 |   317 | 48735   (1)| 00:09:45 |
                      --------------------------------------------------------------------------------------------
                            
                           Predicate Information (identified by operation id):
                           ---------------------------------------------------
                            
                                2 - filter("FUNC_CODE"='4' AND "FROM_LEV_01"<='FMCG2' AND 
                                         "UPTO_LEV_01">='FMCG2' AND "FROM_LEV_02"<='FMCG201' AND 
                                         "UPTO_LEV_02">='FMCG201' AND "FROM_LEV_03"<='137' AND 
                                         "UPTO_LEV_03">='137' AND "FROM_LEV_04"<='188' AND "FRD_UPTO_LEV_04">='188' 
                                         AND "FROM_LEV_05"<='268' AND "UPTO_LEV_05">='268' AND 
                                         "FROM_LEV_06"<='452' AND "UPTO_LEV_06">='452' AND "FRD_FROM_LEV_07"<='0' 
                                         AND "UPTO_LEV_07">='0' AND "FROM_LEV_08"<='FMCG2' AND 
                                         "UPTO_LEV_08">='FMCG2' AND "FROM_LEV_09"<='FMCG208' AND 
                                         "UPTO_LEV_09">='FMCG208' AND "FROM_LEV_10"<='FMCG2' AND 
                                         "UPTO_LEV_10">='FMCG2' AND "FROM_LEV_11"<='C2748' AND 
                                         "UPTO_LEV_11">='C2748' AND "FROM_LEV_12"<='10048-1FMCG2' AND 
                                         "UPTO_LEV_12">='10048-1FMCG2')
                            
                      Statistics
                      ----------------------------------------------------------
                                1  recursive calls
                                0  db block gets
                           276040  consistent gets
                                0  physical reads
                                0  redo size
                             5637  bytes sent via SQL*Net to client
                              274  bytes received via SQL*Net from client
                                1  SQL*Net roundtrips to/from client
                                0  sorts (memory)
                                0  sorts (disk)
                                0  rows processed
                           
                      Regards,
                      • 8. Re: Tuning Query "Between"
                        jeneesh
                        On TEST table, is it taking time to run?No, right..

                        To analyse the problem in Production, execution plan/explain plan form production will be required..
                        • 9. Re: Tuning Query "Between"
                          Miled Moukhtafi
                          Dear Jeneesh,

                          SQL>  set autotrace traceonly arraysize 100
                          SQL> SELECT *
                            2             FROM om_function_rate_defn
                            3            WHERE frd_func_code = '4'
                            4              AND 'FMCG2' BETWEEN frd_from_lev_01 AND frd_upto_lev_01
                            5              AND 'FMCG201' BETWEEN frd_from_lev_02 AND frd_upto_lev_02
                            6              AND '137' BETWEEN frd_from_lev_03 AND frd_upto_lev_03
                            7              AND '188' BETWEEN frd_from_lev_04 AND frd_upto_lev_04
                            8              AND '268' BETWEEN frd_from_lev_05 AND frd_upto_lev_05
                            9              AND '452' BETWEEN frd_from_lev_06 AND frd_upto_lev_06
                           10              AND '0' BETWEEN frd_from_lev_07 AND frd_upto_lev_07
                           11              AND 'FMCG2' BETWEEN frd_from_lev_08 AND frd_upto_lev_08
                           12              AND 'FMCG208' BETWEEN frd_from_lev_09 AND frd_upto_lev_09
                           13              AND 'FMCG2' BETWEEN frd_from_lev_10 AND frd_upto_lev_10
                           14              AND 'C2748' BETWEEN frd_from_lev_11 AND frd_upto_lev_11
                           15              AND '10048-1FMCG2' BETWEEN frd_from_lev_12 AND frd_upto_lev_12;
                          
                          no rows selected
                          
                          
                          Execution Plan
                          ----------------------------------------------------------
                             0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=47324 Card=7004 By
                                    tes=2171240)
                          
                             1    0   TABLE ACCESS (FULL) OF 'OM_FUNCTION_RATE_DEFN' (TABLE) (Co
                                    st=47324 Card=7004 Bytes=2171240)
                          
                          
                          Statistics
                          ----------------------------------------------------------
                                    0  recursive calls
                                    0  db block gets
                               276040  consistent gets
                                    0  physical reads
                                    0  redo size
                                 5637  bytes sent via SQL*Net to client
                                  274  bytes received via SQL*Net from client
                                    1  SQL*Net roundtrips to/from client
                                    0  sorts (memory)
                                    0  sorts (disk)
                                    0  rows processed
                          and you are right on test and product envirnment work fine, if there is no other session work on the same sales order, after checking i'm getting buffer busy "Read from other session"
                          can you help with this issue or i have to go to another post?!!
                          • 10. Re: Tuning Query "Between"
                            Paul  Horth
                            There's no point in having func_code as the leading part of your huge index if the value is always 4.

                            Try create an index on just from_lev_01 and upto_lev_01 and see if a query just looking for 'FMCG2' BETWEEN from_lev_01 AND upto_lev_01
                            uses that index.

                            Also, have you analysed the tables/indexes?

                            Edited by: Paul Horth on 21-Nov-2012 01:50
                            • 11. Re: Tuning Query "Between"
                              Nikolay Savvinov
                              Hi,

                              your problem is twofold:

                              1) as already pointed out in this thread, the entire population of data has func_code =4 which makes this column useless for the index. it was a particularly bad idea to make it the leading column of the index.
                              2) the remaining columns are used in BETWEEN operators, which means that the predicates against these columns are open ranges. E.g. "0 between col1 and col2" is equivalent to "col1 <= 0 and col2 >= 0". Such predicates may not have a very strong selectivity -- for example, if both col1 and col2 are uniformly distributed between -1,000,000 and 1,000,000, then this condition only selects 25% of data

                              Because of this, an index may be very inefficient. For a multi-column B-tree index to work well, you need the leading columns to provide strong selectivity. If that's not the case, you may end up traversing almost the entire index just to return a few rowids.

                              The best solution would be to re-consider the data model, but then again you cannot do that because it's third party code... try removing func_code from the index and re-arranging the column order such that columns that provide best selectivity go first, let's see if this helps.

                              Best regards,
                              Nikolay
                              • 12. Re: Tuning Query "Between"
                                bencol
                                Depending on how/when om_function_rate_defn/test is written to, you might get a benefit from bitmap indexes on the lev columns. I've done a quick test on a data warehouse table which has date columns with bitmap indexes - bitmap index range scans are used and merged, when the query has inequalities on the date column. But adding filters can reduce the number of indexes used ...

                                You should not do this if the table (and indexes) are written to by concurrent sessions.
                                • 13. Re: Tuning Query "Between"
                                  Miled Moukhtafi
                                  Dear Nikolay,

                                  thanks for your help, it was really helpfull,
                                  whatever i try to the table with "BETWEEN Function" still make full scan, kindly check the following example:
                                  CREATE TABLE test (
                                   FUNC_CODE VARCHAR2 (60),  
                                   FROM_LEV_01 VARCHAR2 (60), 
                                   UPTO_LEV_01 VARCHAR2 (60),  
                                   FROM_LEV_02 VARCHAR2 (60),  
                                   UPTO_LEV_02 VARCHAR2 (60),  
                                   FROM_LEV_03 VARCHAR2 (60), 
                                   UPTO_LEV_03 VARCHAR2 (60),  
                                   FROM_LEV_04 VARCHAR2 (60),  
                                   UPTO_LEV_04 VARCHAR2 (60),  
                                   FROM_LEV_05 VARCHAR2 (60),  
                                   UPTO_LEV_05 VARCHAR2 (60),  
                                   FROM_LEV_06 VARCHAR2 (60), 
                                   UPTO_LEV_06 VARCHAR2 (60),  
                                   FROM_LEV_07 VARCHAR2 (60),  
                                   UPTO_LEV_07 VARCHAR2 (60),  
                                   FROM_LEV_08 VARCHAR2 (60),  
                                   UPTO_LEV_08 VARCHAR2 (60),  
                                   FROM_LEV_09 VARCHAR2 (60), 
                                   UPTO_LEV_09 VARCHAR2 (60),  
                                   FROM_LEV_10 VARCHAR2 (60),  
                                   UPTO_LEV_10 VARCHAR2 (60),  
                                   FROM_LEV_11 VARCHAR2 (60),  
                                   UPTO_LEV_11 VARCHAR2 (60),  
                                   FROM_LEV_12 VARCHAR2 (60),  
                                   UPTO_LEV_12 VARCHAR2 (60),
                                   flex_01 VARCHAR2 (60),
                                   flex_02 VARCHAR2 (60),
                                   flex_03 VARCHAR2 (60),
                                   flex_04 VARCHAR2 (60),
                                   flex_05 VARCHAR2 (60),
                                   flex_06 VARCHAR2 (60),
                                   flex_07 VARCHAR2 (60),
                                   flex_08 VARCHAR2 (60),
                                   flex_09 VARCHAR2 (60)
                                  );   
                                  
                                  CREATE INDEX test_idx_01 ON test(          
                                   FROM_LEV_01,  UPTO_LEV_01);  
                                  
                                  SQL> set autotrace traceonly arraysize 100
                                  SQL> SELECT *
                                    2    FROM test
                                    3   WHERE 'FMCG2' BETWEEN from_lev_01 AND upto_lev_01;
                                  
                                  no rows selected
                                  
                                  
                                  Execution Plan
                                  ----------------------------------------------------------
                                     0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=108
                                            8)
                                  
                                     1    0   TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=2 Card=1 Bytes
                                            =1088)
                                  
                                  
                                  
                                  
                                  
                                  Statistics
                                  ----------------------------------------------------------
                                          644  recursive calls
                                            0  db block gets
                                           69  consistent gets
                                            0  physical reads
                                            0  redo size
                                         1821  bytes sent via SQL*Net to client
                                          274  bytes received via SQL*Net from client
                                            1  SQL*Net roundtrips to/from client
                                            0  sorts (memory)
                                            0  sorts (disk)
                                            0  rows processed
                                  • 14. Re: Tuning Query "Between"
                                    Miled Moukhtafi
                                    I found the problem is related to read from other session and db_file_scatered read,
                                    so just i will move my question to Database forum,

                                    thank you for all....