Analyze V/S Dbms_stats

[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

[davey23uk]

dbms_stats in 8.1.7 / 9.2 as it collects more information than a simple analyze does

Not sure about 7.3.4

[gurbirbhatia]

does optimizer mode 'choose' use any statistics generated by analyze table/index

in 7.3.4,8.1.7 and 9.2

[davey23uk]

If the optimizer mode is choose and the table has stats, the CBO will be used

It 'chooses' which optimizer method to use

[dbasan]

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.

[tamilselvan]

You can use dbms_utility.analyze_schema procedure in Oracle 7.3

Tamil

[gurbirbhatia]

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