Re: difference in dba_tables and dba

From: Brian Peasland <oracle_dba_at_nospam.peasland.net>
Date: Wed, 21 Jun 2006 15:55:07 GMT
Message-ID: <J17w7x.754@igsrsparc2.er.usgs.gov>

hpuxrac wrote:

> Ben wrote:

>> Mladen Gogala wrote:
>>> On Tue, 20 Jun 2006 07:58:45 -0700, Ben wrote:
>>>
>>>> I'd rather use dba_table instead of joining between the two to get
>>>> num_rows, last_analyzed, and size values, but if these are returning
>>>> different values how do I know which one is correct?
>>>> Thank you,
>>> Very simple: go through DBA_EXTENTS and sum up sizes of all extents
>>> comprising the table. See which number will you get. My guess is that
>>> the number will be the one from DBA_SEGMENTS for the reason mentioned
>>> by Brian.
>>>
>>> --
>>> http://www.mgogala.com
>>
>> So why would you use DBA_EXTENTS as the golden rule as to what it
>> should be?

> 
> Umm because it is the accurate answer?
>

Why is DBA_EXTENTS more accurate than DBA_SEGMENTS?

SQL> select bytes from dba_segments where segment_name='SDE_BLK_9037';

BYTES

65,781,366,784

SQL> select sum(bytes) as bytes from dba_extents

2 where segment_name='SDE_BLK_9037';

BYTES

65,781,366,784

The segment shown above has 1,165 extents.

Querying DBA_SEGMENTS and DBA_EXTENTS for the total size of the segment should, and does produce the same result. To say one view is more accurate than the other is not correct. DBA_SEGMENTS relies heavily on SYS.SEG$ for its information, as does DBA_EXTENTS. However, DBA_EXTENTS does also query X$KTFBUE (assuming LMT's, or SYS.UET$ for DMT's) which is the file bitmap used extent fixed table. But DBA_EXTENTS needs X$KTFBUE because you are looking on a different level...at the extent level. DBA_SEGMENTS looks one level higher, the segment level. In either case, the number of BYTES reported for the segment in both views should be the same. And in all of my tests, it is.

Chers,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Wed Jun 21 2006 - 10:55:07 CDT

Re: difference in dba_tables and dba_segments??