Home » RDBMS Server » Performance Tuning » High overhead on DBA_SEGMENTS
| High overhead on DBA_SEGMENTS [message #65037] |
Mon, 05 April 2004 08:30  |
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  |
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 #65053 is a reply to message #65052] |
Wed, 07 April 2004 15:38 |
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
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 13:23:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
