performance degradation after analyze table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: performance degradation after analyze table

  1. #1
    Join Date
    Aug 2002
    Posts
    115

    performance degradation after analyze table

    Hi all,

    We are using RBO for all selects.

    We have one unix job that truncates a table ..say A..and reloads data into it using sql loader.(around 6 million records)

    then another job runs that uses data from this table.

    At fisrt we found that when the 2nd job runs, is doesnt use the index present in table A and does a FTS.Due to this, the 2nd job runs slow.

    Then we added an additional analyze command as:

    ANALYZE TABLE A estimate statistics sample 7 percent for table for all indexes;

    still, the 2nd job ran slow.

    Then we opted for deleting the stats using

    ANALYZE TABLE A delete statistics;

    after this, the 2nd job ran fast. Now our main concern is do we need to analyze the table A again...after the completion of 2nd job..becuase we are not sure if after analyzing the table, a 3rd job that uses table A would use the indexes or do a FTS.

    Also why did the 2nd job run slow after analyze?


    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    if you analzye any table you aren't using the RBO anymore, you use the CBO.

    it could of ran slow for any number of reasons

    If you have many tables involved in the query and you only analyze one then you will get duff plans because it has to guess at the other tables.

    Or you didnt give the optimzer enough information and it thought a different plan was a better option

  3. #3
    Join Date
    Aug 2002
    Posts
    115
    Thanks Davey.

    We explicitly alter the session to use RBO before the job begins.

    Why does the optimizer fail to use the index present in A even if we are using RBO unless we delete the stats?

    Thanks

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    A prime cause is that the CBO does not have correct information on the relative costs of accessing indexes and tables on your hardware. You can set the init parameter "optimizer_index_cost_adj" to 20 (the default of 100 is too high) at first, but then you ought to start using the DBMS_STATS.GATHER_SYSTEM_STATS() procedure to get benchmarks for CPU and disk performance.

    If you are switching over from RBO to CBO (which is a worthwhile effort in the long run) then get stuck into the Oracle Concepts Guide and the Performance guide at http://tahiti.oracle.com. I also blogged some stuff on the optimizer and efficient SQL here http://oraclesponge.blogspot.com
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    if you explicitly alter the session to rbo then stats are useless as you wont be using them

  6. #6
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by davey23uk
    if you explicitly alter the session to rbo then stats are useless as you wont be using them
    really? is this always the case or are you speaking in general terms?
    I'm stmontgo and I approve of this message

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    unless im mistaken, if you give a rule hint or alter session set optimizer_mode = rule then it will use the RBO and therefore stats wont be used as only the CBO uses them.

  8. #8
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by davey23uk
    unless im mistaken, if you give a rule hint or alter session set optimizer_mode = rule then it will use the RBO and therefore stats wont be used as only the CBO uses them.
    iot's, partition tables, degree of parallelism in the table....ringing any bells here? They'll employ CBO to parse and in the absence of available stats will use system assigned stats which we all know are never representative of the users data and ergo are very bad.
    I'm stmontgo and I approve of this message

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