After analyze appln is slow
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.
you generated incorrect stats, revert them to the ones you had before you changed them
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.
Delete the statistics generated.
Use appropriate estimate percentage.
Thanks for your reply
my oracle version is 220.127.116.11.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
why didnt you do the indexes?
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);
If time permits, collect 100 % statistics on tables and indexes.
I have analyzed thru EXEC DBMS_STATS.gather_table_stats('schema name','table name')
pl tell me how to collect 100% statistics
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');
Click Here to Expand Forum to Full Width