dcsimg
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: CBO not using index

  1. #1
    Join Date
    Oct 2002
    Posts
    182

    CBO not using index

    from production environment on 9.2.0.4 Solaris
    table was analyzed in the past hour
    PHP Code:
               1  SELECT /*+ index (METER_READING METER_READING_PK)*/
    15:30:48   2  MAX(rcpt_dt
    15:30:48   3  FROM METER_READING 
    15
    :30:48   4  WHERE 
    15
    :30:48   5  org_id 'XX' AND 
    15:30:48   6  svc_type_cd 'E' AND 
    15:30:48   7  cust_id '062141268001' AND 
    15:30:48   8  prem_seq_nbr AND 
    15:30:48   9  meter_seq_nbr AND 
    15:30:48  10  rdng_nbr >= 0
    15
    :30:48  11  AND rcpt_dt SYSDATE-60;
    Elapsed00:00:00.00

    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=660 Card=1 Bytes=31)
       
    1    0   SORT (AGGREGATE)
       
    2    1     TABLE ACCESS (BY INDEX ROWIDOF 'METER_READING' (Cost=660 Card=47 Bytes=1457)
       
    3    2       INDEX (RANGE SCANOF 'METER_READING_PK' (UNIQUE) (Cost=5 Card=937
    from a copy of production I export/import into another environment every morning and analyze. On a different db 9.2.0.4 also Solaris

    PHP Code:
               1  SELECT /*+ index (METER_READING METER_READING_PK)*/
    15:31:27   2  MAX(rcpt_dt
    15:31:27   3  FROM METER_READING 
    15
    :31:27   4  WHERE 
    15
    :31:27   5  org_id 'XX' AND 
    15:31:27   6  svc_type_cd 'E' AND 
    15:31:27   7  cust_id '062141268001' AND 
    15:31:27   8  prem_seq_nbr AND 
    15:31:27   9  meter_seq_nbr AND 
    15:31:27  10  rdng_nbr >= 0
    15
    :31:27  11  AND rcpt_dt SYSDATE-60;
    Elapsed00:00:00.00

    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=31)
       
    1    0   SORT (AGGREGATE)
       
    2    1     TABLE ACCESS (BY INDEX ROWIDOF 'METER_READING' (Cost=5 Card=46 Bytes=1426)
       
    3    2       INDEX (RANGE SCANOF 'METER_READING_PK' (UNIQUE) (Cost=2 Card=929
    I can't figure out why the production system thinks that the cost is so high. Why would that be??
    Last edited by Cookies; 01-26-2004 at 04:46 PM.
    - Cookies

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    init.ora parameters?

    statistics?

  3. #3
    Join Date
    Oct 2002
    Posts
    182
    Production:
    • aq_tm_processes = 1
      background_dump_dest = /u01/app/oracle/admin/kop/bdump
      compatible = 9.2.0.4.0
      control_files = ('/u01/app/oracle/oradata/kop/control01.ctl', '/u01/app/oracle/oradata/kop/control02.ctl', '/u01/app/oracle/oradata/kop/control03.ctl')
      core_dump_dest = /u01/app/oracle/admin/kop/cdump
      db_block_size = 8192
      db_cache_size = 218103808
      db_domain = ''
      db_file_multiblock_read_count = 16
      db_files = 400
      db_name = kop
      dispatchers = '(PROTOCOL=TCP) (SERVICE=kop1XDB)'
      dml_locks = 1600
      enqueue_resources = 2020
      fast_start_mttr_target = 0
      hash_area_size = 16384000
      hash_join_enabled = TRUE
      instance_name = kop1
      java_pool_size = 33554432
      job_queue_processes = 2
      large_pool_size = 16777216
      log_archive_dest_1 = 'LOCATION=/u03/oradata/kop/archive/arch'
      log_buffer = 983040
      log_checkpoint_interval = 100000000
      log_checkpoint_timeout = 18000000
      max_dump_file_size = 100000
      max_rollback_segments = 80
      open_cursors = 400
      optimizer_features_enable = 9.2.0
      pga_aggregate_target = 105906176
      processes = 150
      query_rewrite_enabled = FALSE
      remote_login_passwordfile = NONE
      service_names = kop1.xxx.com
      session_cached_cursors = 200
      sessions = 300
      shared_pool_reserved_size = 5033164
      shared_pool_size = 117440512
      shared_server_sessions = 295
      shared_servers = 1
      sort_area_retained_size = 65536
      sort_area_size = 8192000
      star_transformation_enabled = FALSE
      timed_statistics = TRUE
      transactions = 400
      undo_management = AUTO
      undo_retention = 900
      undo_tablespace = UNDO
      user_dump_dest = /u01/app/oracle/admin/kop/udump


    Copy of Production:
    • background_dump_dest = /export/oracle/u01/app/oracle/admin/kod/bdump
      compatible = 9.2.0.4
      control_files = ('/export/oracle/u03/app/oracle/oradata/kod/control01.ctl', '/export/oracle/u01/app/oracle/oradata/kod/control02.ctl', '/export/oracle/u03/app/oracle/oradata/kod/control03.ctl')
      core_dump_dest = /export/oracle/u01/app/oracle/admin/kod/cdump
      db_block_size = 8192
      db_cache_size = 218103808
      db_domain = 'xxx.com'
      db_file_multiblock_read_count = 16
      db_files = 400
      db_keep_cache_size = 16777216
      db_name = kod
      hash_area_size = 16384000
      instance_name = kod1
      job_queue_processes = 2
      large_pool_size = 33554432
      log_archive_dest_1 = 'LOCATION=/export/oracle/u01/app/oracle/product/9.2.0/dbs/arch'
      log_buffer = 983040
      log_checkpoint_interval = 100000000
      log_checkpoint_timeout = 18000000
      max_dump_file_size = 100000
      max_enabled_roles = 30
      open_cursors = 400
      optimizer_features_enable = 9.2.0
      os_authent_prefix = ''
      pga_aggregate_target = 52428800
      processes = 100
      remote_login_passwordfile = NONE
      remote_os_authent = TRUE
      service_names = kod1.xxx.com
      session_cached_cursors = 100
      sessions = 300
      shared_pool_size = 100663296
      sort_area_retained_size = 65536
      sort_area_size = 6553600
      timed_statistics = TRUE
      transactions = 400
      undo_management = AUTO
      undo_tablespace = undo
      user_dump_dest = /export/oracle/u01/app/oracle/admin/kod/udump
      workarea_size_policy = auto
    Last edited by Cookies; 01-26-2004 at 08:45 PM.
    - Cookies

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    index statistics

  5. #5
    Join Date
    Oct 2002
    Posts
    182
    Is it because I didn't analyze the imported copy?

    Production indexes:
    I just created METER_READING_IND_01 to alleviate the current problem since the disk reads were getting out of hand due to the CBO not using the PK index due to the initial problem above.
    PHP Code:
    OWNER          INDEX_NAME              INDEX_TYPE    TABLE_OWNER   TABLE_NAME      TABLE_TYPE  UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME     INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS  PCT_FREE LOG     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS     NUM_ROWS SAMPLE_SIZE LAST_ANAL DEGREE  PAR T G S BUFFER_ USE 
    -------------- ----------------------- ------------- ------------- --------------- ----------- --------- -------- ------------- ------------------ ---------- ---------- -------------- ----------- ----------- ----------- --------- --- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- --------- ------- --- - - - ------- --- 
    PLATFORM       METER_READING_PK        NORMAL        PLATFORM      METER_READING   TABLE       UNIQUE    ENABLED              5 PLAT_IND_01                 2        255          65536                       1  2147483645        10 NO           2         883        343161                       1                       1            267123 VALID        343161      343161 26-JAN-04 1       NO  N N N DEFAULT NO  
    PLATFORM       METER_READING_IND_01    NORMAL        PLATFORM      METER_READING   TABLE       NONUNIQUE DISABLED               PLAT_IND_01                 2        255          65536                       1  2147483645        10 NO           2        1940        342059                       1                       1            267136 VALID        343161      343161 26
    -JAN-04 1       NO  N N N DEFAULT NO 
    Copy of Production:
    PHP Code:
    OWNER       INDEX_NAME         INDEX_TYPE     TABLE_TYPE  UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME                 INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS  PCT_FREE LOG  STATUS     NUM_ROWS SAMPLE_SIZE LAST_ANAL DEGREE    INSTANCES   PAR T G S BUFFER_ USE 
    ----------  -----------------  -------------  ----------- --------- -------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- --------- ---  -------- ---------- ----------- --------- --------- ----------- --- - - - ------- --- 
    PROD_COPY   METER_READING_PK   NORMAL         TABLE       UNIQUE    ENABLED              5 USERS                                   2        255          65536                       1  2147483645        10 NO   VALID                                     1         1           NO  N N N DEFAULT NO 
    - Cookies

  6. #6
    Join Date
    Jan 2004
    Posts
    15
    Hey!, but it does use index!.
    i have an idea, have you heard about stability plan?
    is a feature that let your statistics be portable to another oracle database, so, you have to generate OUTLINES for your query, and you can take it to another database and you will be runing the query ander same environment, of course, if your target database is on better server..you will not be get improved performance cos the statistics remains exactly as you generated.
    Best luck.

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    In Test

    CLUSTERING_FACTOR = 267123

    but missing in the production. Why? Compare these two.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  8. #8
    Join Date
    May 2002
    Location
    England
    Posts
    78
    what is the result after running the analyse?

  9. #9
    Join Date
    Oct 2002
    Posts
    182
    Production (the new index seems to work well):
    PHP Code:

        HEIGHT     BLOCKS     NAME               LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
    ---------- ---------- ------------------ ------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
             
    3       1024 METER_READING_PK    347097        885     6247746       7992        884          7       25913       8028           0               0        347097                 1     7129116    6295083         89            1                    4        956        21424              5                0
             
        HEIGHT  BLOCKS NAME                  LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
    ---------- ------- --------------------- ------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
             
    3    2176 METER_READING_IND_01   347214       1985    14064437       7996       1984         13       61099       8028           0               0        346112                 3    15976424   14125536         89   1.00318394           4.00159197          0            0              5               40     


      1  SELECT
      2  MAX
    (rcpt_dt)
      
    3  FROM METER_READING
      4  WHERE
      5  org_id 
    'ME' AND
      
    6  svc_type_cd 'E' AND
      
    7  cust_id '062141268001' AND
      
    8  prem_seq_nbr AND
      
    9  meter_seq_nbr AND
     
    10  rdng_nbr >= 0
     11
    * AND rcpt_dt SYSDATE-60
    10
    :10:13 platform@kop1> /
    Elapsed00:00:00.00

    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=31)
       
    1    0   SORT (AGGREGATE)
       
    2    1     INDEX (RANGE SCANOF 'METER_READING_IND_01' (NON-UNIQUE) (Cost=8 Card=47 Bytes=1457)

    10:10:16 platform@kop1SELECT /*+ index (METER_READING METER_READING_PK)*/ 
    10:20:55   2  MAX(rcpt_dt
    10:20:55   3  FROM METER_READING 
    10
    :20:55   4  WHERE 
    10
    :20:55   5  org_id 'ME' AND 
    10:20:55   6  svc_type_cd 'E' AND 
    10:20:55   7  cust_id '062141268001' AND 
    10:20:55   8  prem_seq_nbr AND 
    10:20:55   9  meter_seq_nbr AND 
    10:20:55  10  rdng_nbr >= 
    10
    :20:55  11  AND rcpt_dt SYSDATE-60;
    Elapsed00:00:00.00

    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=737 Card=1 Bytes=31)
       
    1    0   SORT (AGGREGATE)
       
    2    1     TABLE ACCESS (BY INDEX ROWIDOF 'METER_READING' (Cost=737 Card=47 Bytes=1457)
       
    3    2       INDEX (RANGE SCANOF 'METER_READING_PK' (UNIQUE) (Cost=5 Card=949)




    10:20:57 platform@kop1edit
    Wrote file afiedt
    .buf

      1  SELECT 
    /*+ FULL (METER_READING)*/
      
    2  MAX(rcpt_dt)
      
    3  FROM METER_READING
      4  WHERE
      5  org_id 
    'ME' AND
      
    6  svc_type_cd 'E' AND
      
    7  cust_id '062141268001' AND
      
    8  prem_seq_nbr AND
      
    9  meter_seq_nbr AND
     
    10  rdng_nbr >= 0
     11
    * AND rcpt_dt SYSDATE-60
    10
    :21:13 platform@kop1> /
    Elapsed00:00:00.00

    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=273 Card=1 Bytes=31)
       
    1    0   SORT (AGGREGATE)
       
    2    1     TABLE ACCESS (FULLOF 'METER_READING' (Cost=273 Card=47 Bytes=1457
    Copy of Production:
    PHP Code:
    10:08:46 prod_copy@kod1SELECT /*+ index (METER_READING METER_READING_PK */
    10:09:08   2  MAX(rcpt_dt
    10:09:08   3  FROM METER_READING 
    10
    :09:08   4  WHERE 
    10
    :09:08   5  org_id 'ME' AND 
    10:09:08   6  svc_type_cd 'E' AND 
    10:09:08   7  cust_id '062141268001' AND 
    10:09:08   8  prem_seq_nbr AND 
    10:09:08   9  meter_seq_nbr AND 
    10:09:08  10  rdng_nbr >= 
    10
    :09:08  11  AND rcpt_dt SYSDATE-60;
    Elapsed00:00:00.00

    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=750 Card=1 Bytes=24)
       
    1    0   SORT (AGGREGATE)
       
    2    1     TABLE ACCESS (BY INDEX ROWIDOF 'METER_READING' (Cost=750 Card=47 Bytes=1128)
       
    3    2       INDEX (RANGE SCANOF 'METER_READING_PK' (UNIQUE) (Cost=5 Card=950)


    10:09:09 prod_copy@kod1edit
    Wrote file afiedt
    .buf

      1  SELECT
      2  MAX
    (rcpt_dt)
      
    3  FROM METER_READING
      4  WHERE
      5  org_id 
    'ME' AND
      
    6  svc_type_cd 'E' AND
      
    7  cust_id '062141268001' AND
      
    8  prem_seq_nbr AND
      
    9  meter_seq_nbr AND
     
    10  rdng_nbr >= 0
     11
    * AND rcpt_dt SYSDATE-60
    10
    :09:59 prod_copy@kod1> /
    Elapsed00:00:00.00

    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=24)
       
    1    0   SORT (AGGREGATE)
       
    2    1     INDEX (RANGE SCANOF 'METER_READING_IND_01' (NON-UNIQUE) (Cost=8 Card=47 Bytes=1128)


    10:13:58 prod_copy@kod1edit
    Wrote file afiedt
    .buf

      1  SELECT 
    /*+ FULL (meter_reading)*/
      
    2  MAX(rcpt_dt)
      
    3  FROM METER_READING
      4  WHERE
      5  org_id 
    'ME' AND
      
    6  svc_type_cd 'E' AND
      
    7  cust_id '062141268001' AND
      
    8  prem_seq_nbr AND
      
    9  meter_seq_nbr AND
     
    10  rdng_nbr >= 0
     11
    * AND rcpt_dt SYSDATE-60
    10
    :15:15 prod_copy@kod1> /
    Elapsed00:00:00.00

    Execution Plan
    ----------------------------------------------------------
       
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=267 Card=1 Bytes=24)
       
    1    0   SORT (AGGREGATE)
       
    2    1     TABLE ACCESS (FULLOF 'METER_READING' (Cost=267 Card=47 Bytes=1128
    - Cookies

  10. #10
    Join Date
    Oct 2002
    Posts
    182
    here is another example of where I get confused.
    As you see the CBO wants to use a FTS but using the PK looks to me to show better performance.


    PHP Code:
    SELECT /* PerformanceDetailDataAccess */
            
    end_dt AS intvl_end_dt,
            
    baseline_nbr AS adj_base,
            
    demand_nbr AS actual_demand,
            
    reduction_nbr AS reduction,
            
    intvl_part_nbr AS part_count,
            
    intvl_over_nbr AS over_count
    FROM    dr_agg_interval WHERE
            org_id 
    'LT' AND
            
    event_id '03111100' AND
            
    end_dt SYSDATE-360
    ORDER BY end_dt ASC

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.01       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        3      0.00       0.00          0         16          0          20
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        5      0.01       0.01          0         16          0          20

    Misses in library cache during parse
    1
    Optimizer goal
    CHOOSE
    Parsing user id
    48

    Rows     Row Source Operation
    -------  ---------------------------------------------------
         
    20  SORT ORDER BY (cr=16 r=0 w=0 time=3856 us)
         
    20   TABLE ACCESS FULL DR_AGG_INTERVAL (cr=16 r=0 w=0 time=3495 us)

    *************************************************************

    SELECT /* PerformanceDetailDataAccess */
           /*+ index (dr_agg_interval dr_agg_interval_pk)*/
            
    end_dt AS intvl_end_dt,
            
    baseline_nbr AS adj_base,
            
    demand_nbr AS actual_demand,
            
    reduction_nbr AS reduction,
            
    intvl_part_nbr AS part_count,
            
    intvl_over_nbr AS over_count
    FROM    dr_agg_interval WHERE
            org_id 
    'LT' AND
            
    event_id '03111100' AND
            
    end_dt SYSDATE-360
    ORDER BY end_dt ASC

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.01       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        3      0.00       0.00          0          4          0          20
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        5      0.01       0.00          0          4          0          20

    Misses in library cache during parse
    1
    Optimizer goal
    CHOOSE
    Parsing user id
    48

    Rows     Row Source Operation
    -------  ---------------------------------------------------
         
    20  SORT ORDER BY (cr=4 r=0 w=0 time=1243 us)
         
    20   TABLE ACCESS BY INDEX ROWID DR_AGG_INTERVAL (cr=4 r=0 w=0 time=1011 us)
         
    20    INDEX RANGE SCAN DR_AGG_INTERVAL_PK (cr=3 r=0 w=0 time=919 us)(object id 40236)

    ******************************************************* 
    - Cookies

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width