dbms_stats and analyze 10g Release 2

From: Joe Smith <joe_dba_at_hotmail.com>
Date: Wed, 13 Dec 2006 11:25:08 -0600
Message-ID: <BAY133-F19EDD1D4B1866FC2E5BD6197D60@phx.gbl>

Does the CBO use stats collected with "analyze table..." on LONG and LONG RAW columns? I think that the CBO only uses stats collected by dbms_stats.

I have inheirted this shell script ( from 9.2.0.5.0 ) and it has this entry in it:

select distinct('Analyze Table ' || dba_tables.owner || '.' || dba_tables.table_name || ' estimate statistics sample 20 percent;') from dba_tables , dba_tab_columns
where dba_tables.owner not in ('SYS','SYSTEM','MDSYS','WMSYS','CTXSYS')

and dba_tables.owner = dba_tab_columns.owner
and dba_tables.table_name = dba_tab_columns.table_name
and dba_tab_columns.data_type = 'LONG RAW'

I am modifying this script to use dbms_stats.gather_database_stats and dbms_stats.gather_system_stats, does the above statement do me any good in 10gRelease 2. Does the CBO acutally use these stats?

---

MSN Shopping has everything on your holiday list. Get expert picks by style, age, and price. Try it!
http://shopping.msn.com/content/shp/?ctId=8000,ptnrid=176,ptnrdata=200601&tcode=wlmtagline

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 13 2006 - 11:25:08 CST