| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: DBA_SEGMENTS QUERY
Hi!
> 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 .
>
I don't have a 8i handy here, but in 9.2, the dba_segments view executes dbms_space_admin.segment_number_extents once and dbms_space_admin.segment_number_blocks functions twice for every row returned from it's underlying view sys_dba_segs which is based on seg$.
SEG$ columns blocks and extents aren't used for storing the number of extents and blocks of a segment residing in LMT tablespace anymore. So now, when allocating an extent, we don't have to do a costly recursive transaction on data dictionary anymore, we just update the segment header block directly.
When selecting blocks, bytes and extents from dba_segments, above mentioned dbms_space_admin functions will go and read the extent and block amounts for each segment directly from segment header. The bad thing is that these functions always do a physical read on the header block - no caching here!
So, when selecting all columns from dba_segments, at least 3 physical IOs have to be done for each row returned - meaning for each segment reported!
The dba_extents view does something similar for LMT tablespaces, possibly causing excessive PIOs, when too unrestricted queries are ran against it.
So how to improve performance?
Don't query them (or these problematic columns) unless you really have to!
If your monitoring system is configured to do a full fetch on them every 5
minutes, then this is probably overkill.
If you really want to query dba_segments, then you might be able to get rid of one third of PIOs by rewriting the view in that way that dbms_space_admin.segment_number_blocks wouldn't be executed twice for every row, once is actually enough. Oracle executes it the first time to get the number of blocks for segment and the second time to get number of bytes for the segment - which is basically getting number of blocks and multiplying it with the block size of corresponding tablespace, which you could do without regetting the block count from segment header again.
Tanel.
-- 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 Thu Apr 22 2004 - 16:28:27 CDT
 |
 |