RE: SHOULD WE ANALYZE 9.2 SYS tables?

From: <J.Velikanovs_at_alise.lv>
Date: Fri, 30 Jul 2004 15:37:37 +0300
Message-ID: <OFF22595A1.9C138D5E-ONC2256EE1.0044D642-C2256EE1.00461AC0@alise.lv>

>> I still haven't seen anyone own up to analyzing SYS tables. Sure seems like someone has.
We have one shop with gathered statistics on SYS schema. Oracle EBS 11.5.7 + Patches
9.2.0.4 + 3 node RAC (Tru64 Alphas)
SYS:XXXX> select to_char(LAST_ANALYZED, 'YYYY/MM/DD') , count(*) from dba_tables

SYS:XXXX> where owner = 'SYS'
SYS:XXXX> group by to_char(LAST_ANALYZED, 'YYYY/MM/DD')
SYS:XXXX> /

TO_CHAR(LA COUNT(*)
---------- ----------

2004/04/30        322
2004/07/25          1
                   29

SYS:XXXX> There is no DD SQL perfomance problem in this env so far. BTW: Compute stats is recuested in Apps env. Take a look on 245051.1

Jurijs
9268222

http://otn.oracle.com/ocm/jvelikanovs.html

Forwarded by Jurijs Veïikanovs/IT ALISE/LV on 30.07.2004 15:39 -----

J.Velikanovs_at_alise.lv
Sent by: oracle-l-bounce_at_freelists.org
20.07.2004 18:03
Please respond to oracle-l

        To:     oracle-l_at_freelists.org
        cc: 
        Subject:        Re: No stats, why the heck is CBO running???

It is quite interesting, that Oracle require to gather statistics on DD within Apps environment:

Metalink Note 216550.1
“Interoperability Notes Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)”
step 18 Gather statistics for SYS schema

.
However doesn’t recommend in general:

Metalink Note 245051.1
“Gathering Data Dictionary Statistics”
Gathering statistics on the Data Dictionary in Oracle9i is a supported activity.
In the majority of cases, gathering Data Dictionary statistics should not be
necessary (and would not normally be recommended) since the dictionary has been optimized to cater for most common database setups.

.
:)
Jurijs
9268222

http://otn.oracle.com/ocm/jvelikanovs.html

Tanel Põder <tanel.poder.003_at_mail.ee>
Sent by: oracle-l-bounce_at_freelists.org
20.07.2004 17:50
Please respond to oracle-l

        To:     <oracle-l_at_freelists.org>
        cc: 
        Subject:        Re: No stats, why the heck is CBO running???

> Yet a simple query to find the "next extext that wont fit" is always
using
the CBO???
>
> This SQL queries DBA_TABLES and DBA_FREE_SPACE. There are NO hints in
the
SQL.
>
> What the heck?

But there are hints in DBA_FREE_SPACE:

SQL> select text from dba_views where view_name = 'DBA_FREE_SPACE';

TEXT

select ts.name, fi.file#, f.block#,

       f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#

  and f.ts# = fi.ts#
  and f.file# = fi.relfile#
  and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */

       ts.name, fi.file#, f.ktfbfebno,
       f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno

from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi where ts.ts# = f.ktfbfetsn
  and f.ktfbfetsn = fi.ts#
  and f.ktfbfefno = fi.relfile#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0

These kind of issues are one of the reasons why one should avoid putting hints inside views...

Now, when you join several unanalyzed tables with one analyzed one, CBO will
be used by default and default statistics will be used for unanalyzed tables
(or dynamic sampling depending on optimizer_dynamic_sampling parameter).

Tanel.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Jul 30 2004 - 07:42:18 CDT