| Glenn |
Posted: Mar 01, 2008 07:19:14 AM |
Total Post: 2
Joined: Jan, 2008
|
Why do the Oracle CBO calculate the cost diffrent when I run this statment.
DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME => 'APP01',
tabname => 'Table1',
ESTIMATE_PERCENT => 10 ,
block_sample => TRUE,
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',
CASCADE => TRUE,
GRANULARITY => 'ALL',
DEGREE => DBMS_STATS.DEFAULT_DEGREE
that should be equel to this ANALYEZED statment.
Analyze Table 'APP01.Table1' Estimate Statistics Sample 10 Percent';
I get a better execution plan with the ANALYEZED statment then DBMS_STATS.GATHER_TABLE_STATS.
Can someone try to explain this for me?
Oracle recomend you to use DBMS_STATS.GATHER_TABLE_STATS before ANALYZED.
Regards,
//Glenn |
|
|
 Mohammed Taj |
| Posted: Mar 01, 2008 07:31:56 AM |  |
|
Total Post: 634
Joined: Jul, 2007
|
Analyze command is desupported and oracle only recommneded to use DBMS_STATS package to gather statistics.
analyze command give wrong statistics information.
regards
Taj
|
|
|
|
|
| Glenn |
| Posted: Mar 01, 2008 08:04:47 AM | |
|
Total Post: 2
Joined: Jan, 2008
|
HI,
But why do I get a better execution plan on my SQL query with ANALYZE vs DBMS.STATS, this is biggest concern.
//Glenn
|
|
|
|
|
| Mohammed Taj |
| Posted: Mar 01, 2008 08:46:51 AM | |
|
Total Post: 634
Joined: Jul, 2007
|
... I get a better execution plan on my SQL query with ANALYZE ...
This is not better, this is wrong execution plan which you get from ANALYZE command.
|
|
|
|
|
| MOH_DBA |
| Posted: Mar 11, 2008 06:14:32 PM | |
|
Total Post: 80
Joined: Nov, 2007
|
Analyze and dbms_stats have defferent woking methods, capabalities and pupuses, this is an overview of that:
http://www.idevelopment.info/data/DBA_tips/Tuning/TUNING_17.shtml#AnalyzevsDBMS_STATS
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
