Performance Degrade After analyze table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: Performance Degrade After analyze table

  1. #1
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323

    Performance Degrade After analyze table

    Hi Guru's

    I have a table with 10Million rows. I analyzed the same table using analyze table estimate statistics; after analyzing this table I found performance degrade.

    Do I need to rebuild the indexes as well, along with analyze table? If I delete statistics, do I get the previous performance which is more better than after analyze? Please advice.

    Please treat this an urgent.

    I am using Oracle 8.1.7. Optimizer Mode CHOOSE. Block Size 4K

    Regards
    Nagesh

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    You do not need to rebuild indexes after analyzing table. Try analyzing indexes also. or..
    May be your queries work better in Rule based optimizer. If you delete statistics the queries will use RBO again.

    Sanjay

  3. #3
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Originally posted by SANJAY_G
    You do not need to rebuild indexes after analyzing table. Try analyzing indexes also. or..
    May be your queries work better in Rule based optimizer. If you delete statistics the queries will use RBO again.

    Sanjay
    Thanks Sanjay. Exactly I did the same. Now performance is back to normal. And it is fine.

    One more Doubt, Which analyze table option Estimate/Compute is the best? If I use estimate, will it analyze indexes also? Please guide me. And If I want my queries to use CBO, Do I need to set any parameters? Please advice.

    Regards
    Nagesh

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    When you use ESTIMATE you can define sample size as percent or rows while COMPUTE analyze all the rows for a table.
    Generally ESTIMATE results are very close to accurate.

    You need to analyze your tables and indexes separately or you can use DBMS_STAT package to analyze entire SCHEMA or DATABASE.

    The parameters to use CBO are FIRST_ROWS and ALL_ROWS but usually it is not set in the init.ora instead the default setting CHOOSE is used. When your optimizer mode is choose Oracle usees CBO if the statistics for the object is present else it uses RBO.

    Sanjay

  5. #5
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Thanks Sanjay,

    If I am not wrong, analyzing tables will do the analyze indexes also? Right! still do I need to analyze indexes separately?

    Regards
    Nagesh
    Nagesh

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Yes you are right ANALYZE TABLE does analyze indexes on the table as well.

    Sanjay

  7. #7
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Thanks Sanjay.

    I have below query, taking 1 min over to fetch a record from over 10Million rows table. Which is not considerable.

    SELECT USER_NO,VALUE_LEFT,EXPIRE_DATE
    FROM MST_USER
    WHERE ACCOUNT_NO != '7594299380771992' /*'3839059713682615'*/
    AND SALE_STATUS='V'
    AND (MST_USER.STATUS_ID='A' OR MST_USER.STATUS_ID='C')
    AND VALUE_LEFT = 0
    AND ROWNUM <= 1

    I have an composite Index on this table based on ACCOUNT_NO,SALE_STATUS, STATUS_ID, VALUE_LEFT. If I see explain plan for this statement it is still FTS. Help me to tune this statement.

    Explain Plan:
    LEVEL ID OPERATION OPTIONS OBJECT POSITION
    ---------- --- ------------------------------ ------------------ -------------------- ----------
    1 0 SELECT STATEMENT cost=9232
    2 1 COUNT STOPKEY
    3 2 TABLE ACCESS FULL MST_USER


    If I use INDEX Hint :

    LEVEL ID OPERATION OPTIONS OBJECT POSITION
    ---------- --- ------------------------------ ------------------ -------------------- ----------
    1 0 SELECT STATEMENT cost=56057
    2

    2 1 COUNT STOPKEY
    3 2 TABLE ACCESS BY INDEX ROWID MST_USER
    4 3 INDEX FULL SCAN MSTUSERIDX


    Can you please help me to tune this statement.

    Regards
    Nagesh

  8. #8
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Check you DB_FILE_MULTIBLOCK_READ_COUNT parameter.. now that you've analyzed your tablesa, the CBO probably thinks that a FTS is cheaper than and index lookup. Sometimes the DB__FILE_MULTIBLOCK_READ_COUNT can be set to high making the CBO choose FTS's. Try a setting of 8.
    OCP 8i, 9i DBA
    Brisbane Australia

  9. #9
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Since you are using != for ACCOUNT_NO, an index on this will not help.
    Your SALE_STATUS and STATUS_ID also seems like low cardinality columns, so any b-tree index on this column also won't be of any help.
    To me, it seems like FTS is inevitable here, as suggested by grjohnson try tuning the FTS by using higher value for DB_FILE_MULTIBLOCK_READ_COUNT. Would like to see more clues from others.

    Sanjay

    PS: grjohnson, your typing speed seems to be improved Cheers..

  10. #10
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Originally posted by grjohnson
    Check you DB_FILE_MULTIBLOCK_READ_COUNT parameter.. now that you've analyzed your tablesa, the CBO probably thinks that a FTS is cheaper than and index lookup. Sometimes the DB__FILE_MULTIBLOCK_READ_COUNT can be set to high making the CBO choose FTS's. Try a setting of 8.
    Thanks.

    SQL> show parameter db_file

    NAME TYPE VALUE
    ------------------------------------ ------- ------------------------------
    db_file_direct_io_count integer 64
    db_file_multiblock_read_count integer 8
    db_file_name_convert string
    db_files integer 200
    SQL>

    Do I need to specify explicitly db_file_multiblock_read_count = 8 in parameter file.

    Regards
    Nagesh

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