Analyze V/S Dbms_stats

hi

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



thanks

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

Quote:

---

Originally Posted by gurbirbhatia

hi

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

thanks

---

DBMS_STATS package can gather global statistics at multiple levels as specified by the granularity parameter.
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.

You can use dbms_utility.analyze_schema procedure in Oracle 7.3

Tamil

Thanks tamilselvan

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




Thanks