DBA_SEGMENTS QUERY
- From: Wolfson Larry - lwolfs <lawrence.wolfson@xxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Apr 2004 22:35:10 -0500
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@xxxxx:SQL <mailto:thiru@xxxxx:SQL> >set autotrace on
select">thiru@xxxxx:SQL <mailto:thiru@xxxxx: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@xxxxx:SQL <mailto:thiru@xxxxx: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@xxxxx:SQL <mailto:thiru@xxxxx: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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
