DBA_SEGMENTS QUERY

From: Wolfson Larry - lwolfs <lawrence.wolfson_at_acxiom.com>
Date: Wed, 21 Apr 2004 22:35:10 -0500
Message-ID: <A366E86AF2FBD611AB920002B31E58B0096DC798@conmsx07.corp.acxiom.net>

One of our DBAs just took a performance class from Kevin Loney. He reviewed one of her Perfstat reports and suggested we re-write the query below. Gotta say I was suprised when I saw trace.  

DB is 8.1.7.4 with PS HR&Payroll The largest tablespaces and indexes were converted to LMTs  

What would you suggest? This kicks off a page if any rows are returned.  

Below someone sent me create view dba_segments_alt (see below)  which seemed to work better on his 9.2 system than this one at 8.1.7.4    

Thanks  

select owner
,tablespace_name
,segment_name
,segment_type
,extents
,max_extents

from dba_segments
where extents >= (max_extents-20)
and segment_type not in ('CACHE','ROLLBACK', 'TEMPORARY') order by owner
,tablespace_name
,segment_name
 

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 1 0.20 0.19 25 375 3 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 16.64 88.39 15470 149090 14878 0 ------- ------ -------- ---------- ---------- ---------- ----------

---

total 3 16.84 88.58 15495 149465 14881 0  

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS  

Rows Row Source Operation

------- ---------------------------------------------------

0 SORT ORDER BY
0 VIEW SYS_DBA_SEGS
0 UNION-ALL
0 NESTED LOOPS
1 NESTED LOOPS
22064 NESTED LOOPS
22064 NESTED LOOPS
22735 NESTED LOOPS
22735 VIEW SYS_OBJECTS
22735 UNION-ALL
9717 TABLE ACCESS FULL TAB$
545 TABLE ACCESS FULL TABPART$
10 TABLE ACCESS FULL CLU$
11183 TABLE ACCESS FULL IND$
1264 TABLE ACCESS FULL INDPART$
21 TABLE ACCESS FULL LOB$
1 TABLE ACCESS FULL TABSUBPART$
1 TABLE ACCESS FULL INDSUBPART$
1 TABLE ACCESS FULL LOBFRAG$
45468 TABLE ACCESS BY INDEX ROWID OBJ$
45468 INDEX UNIQUE SCAN (object id 33)
44797 TABLE ACCESS CLUSTER SEG$
44797 INDEX UNIQUE SCAN (object id 9)
44126 INDEX UNIQUE SCAN (object id 39)
22063 TABLE ACCESS CLUSTER TS$
22063 INDEX UNIQUE SCAN (object id 7)
0 TABLE ACCESS CLUSTER USER$
0 INDEX UNIQUE SCAN (object id 11)
0 FILTER
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS FULL UNDO$
0 INDEX UNIQUE SCAN (object id 39)
0 TABLE ACCESS CLUSTER SEG$
0 INDEX UNIQUE SCAN (object id 9)
0 TABLE ACCESS CLUSTER TS$
0 INDEX UNIQUE SCAN (object id 7)
0 TABLE ACCESS CLUSTER USER$
0 INDEX UNIQUE SCAN (object id 11)
0 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
170 TABLE ACCESS FULL FILE$
169 TABLE ACCESS CLUSTER SEG$
24455 INDEX RANGE SCAN (object id 9)
0 TABLE ACCESS CLUSTER TS$
0 INDEX UNIQUE SCAN (object id 7)
0 TABLE ACCESS CLUSTER USER$
0 INDEX UNIQUE SCAN (object id 11)
Did Kevin Loney tell you the reason and how to change it,just curious ?  

One of the reasons why a query against dba_Segments would be slower is becos of Locally Managed tablespace! becos the information about extents is not readily cached in data dictionary like dictionary managed tablespaces .  

The other reason is dba_Segments takes into account all type of segments and for the most part,we are interested in tables,indexes.          

• your original query , this time on a 9i database

set">thiru_at_9.2.0:SQL <mailto:thiru_at_9.2.0:SQL> >set autotrace on  

select">thiru_at_9.2.0:SQL <mailto:thiru_at_9.2.0:SQL> >select owner  

  2 ,tablespace_name  

  3 ,segment_name  

  4 ,segment_type  

  5 ,extents  

  6 ,max_extents  

  7 from dba_segments  

  8 where extents >= (max_extents-20)  

  9 and segment_type not in ('CACHE','ROLLBACK', 'TEMPORARY')  

 10 order by owner  

 11 ,tablespace_name  

 12 ,segment_name  

 13 ;  

no rows selected      

Execution Plan  

---

 

   0 SELECT STATEMENT Optimizer=CHOOSE  

   1 0 SORT (ORDER BY)      2 1 VIEW OF 'SYS_DBA_SEGS'      3 2 UNION-ALL      4 3 NESTED LOOPS      5 4 NESTED LOOPS      6 5 NESTED LOOPS      7 6 NESTED LOOPS  

   8    7                 NESTED LOOPS
 
   9    8                   VIEW OF 'SYS_OBJECTS'
 
  10    9                     UNION-ALL
 
  11   10                       TABLE ACCESS (FULL) OF 'TAB$'
 
  12   10                       TABLE ACCESS (FULL) OF 'TABPART$'
 
  13   10                       TABLE ACCESS (FULL) OF 'CLU$'
 
  14   10                       TABLE ACCESS (FULL) OF 'IND$'
 
  15   10                       TABLE ACCESS (FULL) OF 'INDPART$'
 
  16   10                       TABLE ACCESS (FULL) OF 'LOB$'
 
  17   10                       TABLE ACCESS (FULL) OF 'TABSUBPART$'
 
  18   10                       TABLE ACCESS (FULL) OF 'INDSUBPART$'
 
  19   10                       TABLE ACCESS (FULL) OF 'LOBFRAG$'
 
  20    8                   TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
 
  21   20                     INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
 
  22    7                 TABLE ACCESS (CLUSTER) OF 'SEG$'
 
  23   22                   INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#'

(NON-UNIQUE)     24 6 INDEX (UNIQUE SCAN) OF 'I_FILE2' (UNIQUE)     25 5 TABLE ACCESS (CLUSTER) OF 'TS$'     26 25 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)     27 4 TABLE ACCESS (CLUSTER) OF 'USER$'     28 27 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)     29 3 NESTED LOOPS     30 29 NESTED LOOPS     31 30 NESTED LOOPS     32 31 NESTED LOOPS  

  33   32                 TABLE ACCESS (FULL) OF 'UNDO$'
 
  34   32                 INDEX (UNIQUE SCAN) OF 'I_FILE2' (UNIQUE)
 
  35   31               TABLE ACCESS (CLUSTER) OF 'SEG$'
 
  36   35                 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#'

(NON-UNIQUE)     37 30 TABLE ACCESS (CLUSTER) OF 'TS$'     38 37 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)     39 29 TABLE ACCESS (CLUSTER) OF 'USER$'     40 39 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)     41 3 NESTED LOOPS     42 41 NESTED LOOPS     43 42 NESTED LOOPS     44 43 TABLE ACCESS (FULL) OF 'FILE$'     45 43 TABLE ACCESS (CLUSTER) OF 'SEG$'  

  46   45                 INDEX (RANGE SCAN) OF 'I_FILE#_BLOCK#'

(NON-UNIQUE)     47 42 TABLE ACCESS (CLUSTER) OF 'TS$'     48 47 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)     49 41 TABLE ACCESS (CLUSTER) OF 'USER$'     50 49 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)           Statistics  

---

 

       3404 recursive calls  

         66 db block gets  

      29031 consistent gets  

       3197 physical reads  

      12644 redo size  

        518 bytes sent via SQL*Net to client  

        368 bytes received via SQL*Net from client  

          1 SQL*Net roundtrips to/from client  

        102 sorts (memory)  

          0 sorts (disk)  

          0 rows processed  

• Creating an alternate dba_segments view(forgot where I got this)

create">thiru_at_9.2.0:SQL <mailto:thiru_at_9.2.0:SQL> >create view dba_segments_alt (  

  2 OWNER ,     3 SEGMENT_NAME ,     4 PARTITION_NAME,     5 SEGMENT_TYPE ,     6 TABLESPACE_NAME,     7 BYTES ,     8 BLOCKS ,     9 EXTENTS ,    10 INITIAL_EXTENT ,    11 NEXT_EXTENT ,    12 MIN_EXTENTS ,    13 MAX_EXTENTS ,    14 PCT_INCREASE ,    15 BUFFER_POOL ) as  

 16 select u.name, o.name, o.subname, so.object_type, ts.name,  

 17 dbms_space_admin.segment_number_blocks(ts.ts#, s.file#,  

 18 s.block#, s.type#, s.cachehint, NVL(s.spare1,0),  

 19 o.dataobj#, s.blocks)*ts.blocksize,  

 20 dbms_space_admin.segment_number_blocks(ts.ts#, s.file#,  

 21 s.block#, s.type#, s.cachehint, NVL(s.spare1,0),  

 22 o.dataobj#, s.blocks),  

 23 dbms_space_admin.segment_number_extents(ts.ts#, s.file#,  

 24 s.block#, s.type#, s.cachehint, NVL(s.spare1,0),  

 25 o.dataobj#, s.extents),  

 26 s.iniexts * ts.blocksize,  

 27 decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extsize * ts.blocksize),  

 28 s.minexts, s.maxexts,  

 29 decode(bitand(ts.flags, 3), 1, to_number(NULL),s.extpct),  

 30 decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)  

 31 from sys.user$ u, sys.obj$ o, sys.ts$ ts,  

 32 sys.sys_objects so, sys.seg$ s, sys.file$ f  

 33 where s.file# = so.header_file  

 34 and s.block# = so.header_block  

 35 and s.ts# = so.ts_number  

 36 and s.ts# = ts.ts#  

 37 and o.obj# = so.object_id  

 38 and o.owner# = u.user#  

 39 and s.type# = so.segment_type_id  

 40 and o.type# = so.object_type_id  

 41 and s.ts# = f.ts#  

 42 and s.file# = f.relfile#;  

View created.  

• modified query

select">thiru_at_9.2.0:SQL <mailto:thiru_at_9.2.0:SQL> >select owner,segment_name,segment_type,extents,max_extents  

  2 from dba_Segments_alt where extents >= (max_extents-20) ;  

no rows selected      

Execution Plan  

---

 

   0 SELECT STATEMENT Optimizer=CHOOSE  

   1 0 NESTED LOOPS      2 1 NESTED LOOPS      3 2 NESTED LOOPS      4 3 NESTED LOOPS      5 4 NESTED LOOPS      6 5 VIEW OF 'SYS_OBJECTS'      7 6 UNION-ALL  

   8    7                 TABLE ACCESS (FULL) OF 'TAB$'
 
   9    7                 TABLE ACCESS (FULL) OF 'TABPART$'
 
  10    7                 TABLE ACCESS (FULL) OF 'CLU$'
 
  11    7                 TABLE ACCESS (FULL) OF 'IND$'
 
  12    7                 TABLE ACCESS (FULL) OF 'INDPART$'
 
  13    7                 TABLE ACCESS (FULL) OF 'LOB$'
 
  14    7                 TABLE ACCESS (FULL) OF 'TABSUBPART$'
 
  15    7                 TABLE ACCESS (FULL) OF 'INDSUBPART$'
 
  16    7                 TABLE ACCESS (FULL) OF 'LOBFRAG$'
 
  17    5             TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
 
  18   17               INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
 
  19    4           TABLE ACCESS (CLUSTER) OF 'SEG$'
 
  20   19             INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)
 
  21    3         INDEX (UNIQUE SCAN) OF 'I_FILE2' (UNIQUE)
 
  22    2       TABLE ACCESS (CLUSTER) OF 'TS$'
 
  23   22         INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
 
  24    1     TABLE ACCESS (CLUSTER) OF 'USER$'
 
  25   24       INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
 
 
 
 
 

Statistics  

---

 

        121 recursive calls  

         21 db block gets  

      24921 consistent gets  

          1 physical reads  

       4892 redo size  

        455 bytes sent via SQL*Net to client  

        368 bytes received via SQL*Net from client  

          1 SQL*Net roundtrips to/from client  

          0 sorts (memory)  

          0 sorts (disk)  

          0 rows processed  

• gives much better response(lesser consistent gets,no sorts,lesser recursive calls).

  ---

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited.
If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You.

---

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 Wed Apr 21 2004 - 22:32:45 CDT