RE: Perl Issues

9i (9.2.0.4) on RHEL 3.0 64bit.  No stats on the Data Dictionary.

Here's what I did as Sys.

Set Autotrace On

Select '1' From All_Tables Where Table_Name = 'PLAN_TABLE' ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         NESTED LOOPS (OUTER)
   5    4           NESTED LOOPS (OUTER)
   6    5             NESTED LOOPS (OUTER)
   7    6               NESTED LOOPS (OUTER)
   8    7                 NESTED LOOPS
   9    8                   TABLE ACCESS (FULL) OF 'OBJ$'
  10    8                   TABLE ACCESS (CLUSTER) OF 'TAB$'
  11   10                     INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE)
  12    7                 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
  13   12                   INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
  14    6               INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
  15    5             TABLE ACCESS (CLUSTER) OF 'USER$'
  16   15               INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
  17    4           TABLE ACCESS (CLUSTER) OF 'SEG$'
  18   17             INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)
  19    3         TABLE ACCESS (CLUSTER) OF 'TS$'
  20   19           INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
  21    2       TABLE ACCESS (CLUSTER) OF 'USER$'
  22   21         INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
  23    1     NESTED LOOPS
  24   23       FIXED TABLE (FULL) OF 'X$KZSRO'
  25   23       INDEX (RANGE SCAN) OF 'I_OBJAUTH2' (NON-UNIQUE)
  26    1     FIXED TABLE (FULL) OF 'X$KZSPR'

Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
       6464  consistent gets
         53  physical reads
          0  redo size
        485  bytes sent via SQL*Net to client
        652  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Jack C. Applewhite - Database Administrator
Austin I.S.D.
414.9715 (phone) / 935.5929 (pager)





"Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx> 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
02/22/2008 12:32 PM
Please respond to
rjoralist@xxxxxxxxxxxxxxxxxxxxx


To
oracle-l@xxxxxxxxxxxxx
cc

Subject
RE: Perl Issues






Interesting -- I try this and I get index hits on OBJ$, albeit with a 
paltry
12K rows in the ALL_TABLES view.

What version of Oracle?  This could be an issue with stats on the 
dictionary
tables, either having them in 9i or not having them (or having bad ones) 
in
10g.

Rich

p.s.  I will not complain about my table layout.  Ever.

> Nope.  Any query against All_Tables (or DBA_Tables or User_Tables) 
results
> in a Full Table Scan of OBJ$.  Having 150,000+ tables and 230,000+ 
indexes
> in that one schema in the database results in about 420,000 rows in 
OBJ$.
> Any query using Table_Name causes over 6,400 Consistent Gets.  Not
> horrible, but, when there are hundreds or thousands of such queries in a
> session, it adds up to a huge impact.  In a "normal" database you'd
> probably not notice.
>
> As Sys in one of your databases, set Autotrace On in SQL*Plus, do the
> select below, and check out the Explain Plan - pretty convoluted, eh? 
See
> how many rows your OBJ$ has and how many Consistent Gets the query takes 
-
> there's a correlation, at least in my little brain.
>
> Thanks.
>
> Jack C. Applewhite - Database Administrator


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



Other related posts: