RE: dbms_stats and analyze 10g Release 2

From: Kerber, Andrew <Andrew.Kerber_at_umb.com>
Date: Wed, 13 Dec 2006 11:31:07 -0600
Message-ID: <D40740337A3B524FA81DB598D2D7EBB304A93CC7@x6009a.umb.corp.umb.com>

I don't think you have any long or long raw columns in 10g. They have been deprecated in favor of the blob/clob.

Andrew W. Kerber
Oracle DBA
UMB "If at first you dont succeed, dont take up skydiving"

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joe Smith Sent: Wednesday, December 13, 2006 11:25 AM To: oracle-l_at_freelists.org
Subject: dbms_stats and analyze 10g Release 2

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=20060 1&tcode=wlmtagline

--

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

NOTICE: This electronic mail message and any attached files are confidential. The information is exclusively for the use of the individual or entity intended as the recipient. If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited. If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

--

http://www.freelists.org/webpage/oracle-l Received on Wed Dec 13 2006 - 11:31:07 CST