Re: dbms_stats and analyze 10g Release 2

From: Paul Drake <bdbafh_at_gmail.com>
Date: Wed, 13 Dec 2006 13:07:41 -0500
Message-ID: <910046b40612131007y601c3425p639aecc70ef20450@mail.gmail.com>

On 12/13/06, Kerber, Andrew <Andrew.Kerber_at_umb.com> 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_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?

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

Received on Wed Dec 13 2006 - 12:07:41 CST