Home » RDBMS Server » Performance Tuning » High overhead on DBA_SEGMENTS
High overhead on DBA_SEGMENTS [message #65037] Mon, 05 April 2004 08:30 Go to next message
Larry Wolfson
Messages: 9
Registered: April 2004
Junior Member
I posted this on one of the other forums and Thiru suggested I post it here. He gave me an alternate view he got from somewhere, but that was worse than the orginal. I add Thiru's view at bottom

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.

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)
create view dba_segments_alt
(OWNER
,SEGMENT_NAME
,PARTITION_NAME
,SEGMENT_TYPE
,TABLESPACE_NAME
,BYTES
,BLOCKS
,EXTENTS
,INITIAL_EXTENT
,NEXT_EXTENT
,MIN_EXTENTS
,MAX_EXTENTS
,PCT_INCREASE
,BUFFER_POOL
) as
select u.name, o.name, o.subname, so.object_type, ts.name,
dbms_space_admin.segment_number_blocks(ts.ts#, s.file#,
s.block#, s.type#, s.cachehint, NVL(s.spare1,0),
o.dataobj#, s.blocks)*ts.blocksize,
dbms_space_admin.segment_number_blocks(ts.ts#, s.file#,
s.block#, s.type#, s.cachehint, NVL(s.spare1,0),
o.dataobj#, s.blocks),
dbms_space_admin.segment_number_extents(ts.ts#, s.file#,
s.block#, s.type#, s.cachehint, NVL(s.spare1,0),
o.dataobj#, s.extents),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),s.extpct),
decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from sys.user$ u, sys.obj$ o, sys.ts$ ts,
sys.sys_objects so, sys.seg$ s, sys.file$ f
where s.file# = so.header_file
and s.block# = so.header_block
and s.ts# = so.ts_number
and s.ts# = ts.ts#
and o.obj# = so.object_id
and o.owner# = u.user#
and s.type# = so.segment_type_id
and o.type# = so.object_type_id
and s.ts# = f.ts#
and s.file# = f.relfile#
;
Re: High overhead on DBA_SEGMENTS [message #65038 is a reply to message #65037] Mon, 05 April 2004 09:32 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Larry,
I just tested this ,again on a different 9i instance, and the response from dba_segments_alt was slightly better than dba_segments.

On a database with about 14000 segments, it took about 4 seconds,compared to 6 secs on dba_segments. But the consistent gets(ie buffer reads) was *always* lesser than dba_segments.

As I mentioned earlier, try the dba_segments_alt without filtering rollback,temporary,cache as they are already excluded. Also pls skip the order by clause.

Also since this is locally managed, the extent info is not readily cached ,but again you will be running this query once in a day ?

-Thiru
Re: High overhead on DBA_SEGMENTS [message #65052 is a reply to message #65038] Wed, 07 April 2004 14:11 Go to previous messageGo to next message
Larry Wolfson
Messages: 9
Registered: April 2004
Junior Member
Thriu, actually I think they're running this every 15 minutes. Thanks
Re: High overhead on DBA_SEGMENTS [message #65053 is a reply to message #65052] Wed, 07 April 2004 15:38 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Wow!, thats too frequent ,if you ask me. If you have sized your extents properly(ie the Uniform extent size) and set MAXEXTENTS sufficiently large, do you really need to run such a script on dba_segments so frequently ?

Your run time statistics(in the earlier post) shows a lot of wait time(ie waiting on some event). You may want to enable sql_trace at level 8 or 12 and find out what its waiting for.

-Thiru
Previous Topic: Parallel sub-queries
Next Topic: Explain Plan in TOAD
Goto Forum:
  


Current Time: Thu Oct 01 03:18:45 CDT 2020