| DBA_SEGMENTS Alternate code? [message #65051] |
Wed, 07 April 2004 14:08  |
Larry Wolfson
Messages: 9
Registered: April 2004
|
Junior Member |
|
|
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)
|
|
|
|
| Re: DBA_SEGMENTS Alternate code? [message #65059 is a reply to message #65051] |
Fri, 09 April 2004 11:46  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
|
If it's purpose is diagnostics and it doesn't impact app performance on that database, then I would leave it alone. I'm a big fan or tuning code for business reasons, not just for the sake of it...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
