After analyze appln is slow
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: After analyze appln is slow

  1. #1
    Join Date
    Sep 2006
    Posts
    19

    After analyze appln is slow

    Hi

    I have some doubts in analyze. Recenly I have analyzed some of the tables thru dbms_stats in production. I thought next day it will imporve the performance. However users complained that the appln is going very slow.

    pl help me to solve this issue and what could be the reason for the above problem.

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    you generated incorrect stats, revert them to the ones you had before you changed them

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Can you let us know Oracle version and dbms_stats sample size?

    Just in case be sure your sample size includes not less than 2,500 rows on any given table, if your choice was sample=auto you might want to specify a sample size of 25% or larger.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Oct 2006
    Location
    Mumbai
    Posts
    184
    Delete the statistics generated.

    Use appropriate estimate percentage.

  5. #5
    Join Date
    Sep 2006
    Posts
    19
    Hi

    Thanks for your reply

    my oracle version is 9.2.0.1.0

    and i have used the following command to analyze the tables one by one.

    EXEC DBMS_STATS.gather_table_stats('schema name','table name')
    and max no of records in those tables will be around 8000 records only

    Thanks .

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    why didnt you do the indexes?

  7. #7
    Join Date
    Oct 2006
    Location
    Mumbai
    Posts
    184
    Follow this:

    EXEC DBMS_STATS.DELETE_TABLE_STATS (ownname =>'',tabname =>'')

    EXEC DBMS_STATS.gather_table_stats (ownname =>'',tabname =>'', cascade =>true, estimate_percent => dbms_stats.auto_sample_size);

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    If time permits, collect 100 % statistics on tables and indexes.

  9. #9
    Join Date
    Sep 2006
    Posts
    19
    I have analyzed thru EXEC DBMS_STATS.gather_table_stats('schema name','table name')

    pl tell me how to collect 100% statistics

  10. #10
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    try this:
    exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ownname, TABNAME => 'T1Tab', CASCADE => TRUE);
    and then later to refresh only Table and Indexed columns
    +
    exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ownname, TABNAME => 'T1Tab', CASCADE => FALSE, METHOD_OPT => 'FOR ALL INDEXED COLUMNS size 1');
    ---------------

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