Analyze V/S Dbms_stats
for optimizer mode-'choose' to have current statistics for tables and indexes which of the two is best ' analyze table (compute/estimate) or dbms_stats.gather_table_stats' whether analyze table (compute/estimate ) gather the statistics for optimizer mode 'choose'.The cost of executing the statement is same in 'plan_table'
i am working in 7.3.4 , 8.1.7 ,9.2
guide me for 7.3.4 , 8.1.7 ,9.2
dbms_stats in 8.1.7 / 9.2 as it collects more information than a simple analyze does
Not sure about 7.3.4
does optimizer mode 'choose' use any statistics generated by analyze table/index
in 7.3.4,8.1.7 and 9.2
If the optimizer mode is choose and the table has stats, the CBO will be used
It 'chooses' which optimizer method to use
DBMS_STATS package can gather global statistics at multiple levels as specified by the granularity parameter.
Originally Posted by gurbirbhatia
ANALYZE command collects statistics only at the lowest level.
Oracle recommends to use DBMS_STATS mainly to collect optimizer statistics in 8i and 9i. For 8i and below use analyze.
Remember to monitor the table to keep the stats upto date.
"What is past is PROLOGUE"
You can use dbms_utility.analyze_schema procedure in Oracle 7.3
is there difference b/t analyze table and dbms_utility.analyze_schema procedure in Oracle 7.3
can i used this procedure for both tables and indexes
Click Here to Expand Forum to Full Width