Performance of data dictionary views

From: Steven Patenaude <stevenp_at_coop.com>
Date: Fri, 29 May 1998 12:53:30 -0500
Message-ID: <6kmsmd$25k$1@camel29.mindspring.com>

I'd like to know what kind of performance people are getting when selecting from data dictionary views. Doing a simple count from ALL_TABLES is taking about 7 to 8 seconds (without tracing set on and with it being run before so it should be cached). This is without anyone else using the database. Anyone have ideas?

Description of client:
SQL*Plus 3.3 on Win95, 10Mbit network, currently light traffic

Description of server:
Oracle Workgroup 7.3.2 on Novell Netware 4.11, 128 Megs, P133, 5 disks

SQL*Plus output: (running as SYS)
SQL> set autotrace on
SQL> Select count(*)
  2 from ALL_TABLES
  3 order by Table_Name
  4 ;

 COUNT(*)

---

      911

Execution Plan

---

   0 SELECT STATEMENT Optimizer=RULE    1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       NESTED LOOPS
   4    3         NESTED LOOPS
   5    4           NESTED LOOPS (OUTER)
   6    5             NESTED LOOPS (OUTER)
   7    6               NESTED LOOPS
   8    7                 TABLE ACCESS (FULL) OF 'OBJ$'
   9    7                 TABLE ACCESS (CLUSTER) OF 'TAB$'
  10    9                   INDEX (UNIQUE SCAN) OF 'I_OBJ#' (CLUSTER)
  11    6               INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
  12    5             TABLE ACCESS (CLUSTER) OF 'SEG$'
  13   12               INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (CLUSTER)
  14    4           TABLE ACCESS (CLUSTER) OF 'TS$'
  15   14             INDEX (UNIQUE SCAN) OF 'I_TS#' (CLUSTER)
  16    3         TABLE ACCESS (CLUSTER) OF 'USER$'
  17   16           INDEX (UNIQUE SCAN) OF 'I_USER#' (CLUSTER)
  18    2       NESTED LOOPS
  19   18         FIXED TABLE (FULL) OF 'X$KZSRO'
  20   18         INDEX (RANGE SCAN) OF 'I_OBJAUTH2' (NON-UNIQUE)
  21    2       FIXED TABLE (FULL) OF 'X$KZSPR'

Statistics

---

          7  recursive calls
          2  db block gets
      37819  consistent gets
          0  physical reads
          0  redo size
        180  bytes sent via SQL*Net to client
        290  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Thanks!
Steven
--

Steven Patenaude               Cooperative Computing, Inc.
Database Administrator         512-328-2300 ex. 5202

stevenp_at_coop.com Received on Fri May 29 1998 - 12:53:30 CDT