Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: CBO not using index and using bad explain

    I posted this in another forum, but I thought I would also try here:
    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??

    other data that might be helpful

    Production:
    PHP Code:
    OWNER                          TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME                   IOT_NAME                         PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS LOG B   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE     INSTANCES  CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE     T S NES BUFFER_ ROW_MOVE GLO USE DURATION        SKIP_COR MON CLUSTER_OWNER                  DEPENDEN COMPRESS
    ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- --- - ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- ---------- ---------- ----- -------- ----------- --------- --- ------------ - - --- ------- -------- --- --- --------------- -------- --- ------------------------------ -------- --------
    This is Production             METER_READING                  PLAT_TAB_01                                                                                          10                     1        255          65536                       1  2147483645                                         NO  N     341963       2700            0          0          0          51                         0                   0          1          1     N ENABLED       341963 26-JAN-04 NO               N N NO  DEFAULT DISABLED YES NO                  DISABLED NO                                 DISABLED DISABLED 
    • 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:
    PHP Code:
    OWNER                          TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME                   IOT_NAME                         PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS LOG B   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE     INSTANCES  CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE     T S NES BUFFER_ ROW_MOVE GLO USE DURATION        SKIP_COR MON CLUSTER_OWNER                  DEPENDEN
    ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- --- - ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- ---------- ---------- ----- -------- ----------- --------- --- ------------ - - --- ------- -------- --- --- --------------- -------- --- ------------------------------ --------
    PROD_COPY                      METER_READING                  USERS                                                                                                10                     1        255          65536                       1  2147483645                                         NO  N     339232       2700            0          0          0          51                         0                   0          1          1     N ENABLED       339232 26-JAN-04 NO               N N NO  DEFAULT DISABLED YES NO                  DISABLED NO                                 DISABLED 
    • 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
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    gah!
    I think it had to do with not analyzing the imported copy.
    F**K!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Hey Duck.... Could it also be that the production table is fragmented into many extents? I'm guessing that the table is about 16m in size. With an initial extent or 64kb, I would imagine the table is in many segments. The table "GETS" many truely have a higher cost since accessing the data is probably in many more IO's that on the non production database. Export/Import will compress extents for you and give you a NICE single extent...

    Check out the number of segments for the table (and indexes)

    select count(*),sum(bytes) from dba_extents where segment_name = 'METER_READING ';

    HTH
    Gregg

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Normally I would say "That is possible!" but I just recently exported/imported. Granted, I did not use direct load for the one extent.

    Here is the followup. Any Advice is appreciated.

    PHP Code:
      1  select count(*),sum(bytesbytes
      2  from dba_extents
      3  where segment_name 
    'METER_READING'
      
    4* and owner 'PLATFORM'
    09:25:34 platform@kop1> /

      
    COUNT(*)      BYTES
    ---------- ----------
            
    38   24117248


      1  select count
    (*),sum(bytesbytes
      2  from dba_extents
      3  where segment_name 
    'METER_READING_PK'
      
    4* and owner 'PLATFORM'
    09:28:25 platform@kop1> /

      
    COUNT(*)      BYTES
    ---------- ----------
            
    23    8388608 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Is it possible that the index on PROD is "Browned" ie, deleted rows ??
    Check for HEIGHT, DEL_LF_ROWS and BLKS_GETS_PER_ACCESS ...
    Any difference between the 2 databases ?

    validate index METER_READING_PK;

    select * from INDEX_STATS;

    -- Column Name Description
    -- ----------------------- ----------------------------------------------------
    -- HEIGHT height of the b-tree
    -- BLOCKS blocks allocated to the segment
    -- NAME name of the index
    -- LF_ROWS number of leaf rows
    -- LF_BLKS number of leaf blocks in the b-tree
    -- LF_ROWS_LEN sum of the lengths of all the leaf rows
    -- LF_BLK_LEN useable space in a leaf block
    -- BR_ROWS number of branch rows
    -- BR_BLKS number of branch blocks in the b-tree
    -- BR_ROWS_LEN sum of the lengths of all the branch block rows
    -- BR_BLK_LEN useable space in a branch block
    -- DEL_LF_ROWS number of deleted leaf rows in the index
    -- DEL_LF_ROWS_LEN total length of all deleted rows in the index
    -- DISTINCT_KEYS number of distinct keys in the index
    -- MOST_REPEATED_KEY how many times the most repeated key is repeated
    -- BTREE_SPACE total space currently allocated in the b-tree
    -- USED_SPACE total space that is currently being used
    -- PCT_USED percent of space allocated that is being used
    -- ROWS_PER_KEY average number of rows per distinct key
    -- BLKS_GETS_PER_ACCESS Expected number of consistent mode block
    -- gets per row. This assumes that a row
    -- chosen at random from the table is being
    -- searched for using the index

    Gregg

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    FYI - the table gets a LOT of inserts throughout the day.
    data from PK index:

    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 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Wellllll ..... Doesn't appear to be any problems with the index ... as also shown in your initial explain plan... (660 -vs- 5)

    I have to go back to the fetching of data on the table...

    Are the number of extents on METER_READING and METER_READING_PK the same in both DB's ???

    run tkprof on both DB's ... What do these:


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


    look like ???

    Gregg

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    I created a new index based on the where clause.
    This looks a lot better.

    Production:
    PHP Code:
    SELECT
    MAX
    (rcpt_dt)
    FROM METER_READING
    WHERE
    org_id 
    'NU' AND
    svc_type_cd 'E' AND
    cust_id '726236575' AND
    prem_seq_nbr AND
    meter_seq_nbr AND
    rdng_nbr >= 0
    AND rcpt_dt SYSDATE-60

    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        2      0.10       0.10          0         71          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.11       0.10          0         71          0           1

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

    Rows     Row Source Operation
    -------  ---------------------------------------------------
          
    1  SORT AGGREGATE (cr=71 r=0 w=0 time=100120 us)
      
    11003   INDEX RANGE SCAN METER_READING_IND_01 (cr=71 r=0 w=0 time=84178 us)(object id 42647)

    ****************************************** 
    Copy of Production:
    PHP Code:
    SELECT
    MAX
    (rcpt_dt)
    FROM METER_READING
    WHERE
    org_id 
    'NU' AND
    svc_type_cd 'E' AND
    cust_id '726236575' AND
    prem_seq_nbr AND
    meter_seq_nbr AND
    rdng_nbr >= 0
    AND rcpt_dt SYSDATE-60

    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        2      0.13       0.12         70         70          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.14       0.13         70         70          0           1

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

    Rows     Row Source Operation
    -------  ---------------------------------------------------
          
    1  SORT AGGREGATE (cr=70 r=70 w=0 time=125703 us)
      
    10972   INDEX RANGE SCAN METER_READING_IND_01 (cr=70 r=70 w=0 time=103610 us)(object id 270823)

    ********************************* 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I think you've got it ... That does look a lot better ....

    Gregg

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    here is my main question.
    Look at the example below.
    The CBO wants to do a FTS but the PK is obviously more efficient!

    WTF?!
    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)

    ************************************************ 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    sql> analyze table DR_AGG_INTERVAL estimate statistics sample x percent for all indexed columns;

    See if there is the data is somewhat schewed (possibly histograms)...

    Any replies that are meaningful from dbasupport.com postings ???

    Gregg

  12. #12
    Join Date
    Jan 2004
    Posts
    99
    I have a similar situation, by adding an index on a table column of the query [where clause]..the elasped time for the query was reduced by 4 seconds! [tkprof] which is good stuff, however this particular table also has very heavy inserts....which means an additional index slows the inserts[from what iv'e read]. Due to the number of unique statements this is also killing my hit ratio...any suggestions?? does this relate?

  13. #13
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Check your latches and waits ... If you find that you have db_block_locks, you probably need to increase the freelist and initran
    for both the table and indexes ...

    Gregg

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by gbrabham
    Check your latches and waits ... If you find that you have db_block_locks, you probably need to increase the freelist and initran
    for both the table and indexes ...

    Gregg
    Are these viable in 9i?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  15. #15
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
    13:21:32 perfstat@kop1SELECT FROM v$waitstat;

    CLASS                   
    COUNT       TIME
    ------------------ ---------- ----------
    data block            4843004  673497454
    sort block                  0          0
    save undo block             0          0
    segment header              9          9
    save undo header            0          0
    free 
    list                   0          0
    extent map                  0          0
    1st level bmb              59        182
    2nd level bmb               0          0
    3rd level bmb               0          0
    bitmap block                0          0
    bitmap index block          0          0
    file header block          11       1660
    unused                      0          0
    system undo header          0          0
    system undo block           0          0
    undo header              1037       1660
    undo block                 82        312 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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