Re: dbms_stats and analyze 10g Release 2
- From: "Paul Drake" <bdbafh@xxxxxxxxx>
- To: Andrew.Kerber@xxxxxxx
- Date: Wed, 13 Dec 2006 13:07:41 -0500
On 12/13/06, Kerber, Andrew <Andrew.Kerber@xxxxxxx> wrote:
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
SQL> select version from v$instance;
VERSION
-----------------
10.2.0.2.0
1 select count(1)
2 from dba_views v, dba_tab_columns c
3 where v.owner='SYS'
4 and v.owner = c.owner
5 and v.view_name = c.table_name
6 and c.data_type='LONG'
7* order by 1
SQL> /
COUNT(1)
----------
185
Deprecated is still more supported than de-supported or obsolete.
They're still there.
Paul
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Joe Smith
Sent: Wednesday, December 13, 2006 11:25 AM
To: oracle-l@xxxxxxxxxxxxx
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?
Other related posts:
