Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

difference in dba_tables and dba_segments??

132 views
Skip to first unread message

Ben

unread,
Jun 20, 2006, 10:58:45 AM6/20/06
to
Running Enterprise 9.2.0.5 with the compatible parameter set to 8.1.0
on an IBM Unix/AIX5L system.
Why would the following selects return a different value for blocks?
This table is analyzed using dbms_stats.gather_schema_stats.

select owner, segment_name, round(blocks*8/1024,2) as Megs
from dba_segments
where owner = 'PRODDTA'
and segment_name = 'F0911'

returns 35840.02 for Megs

select owner, table_name, round(blocks*8/1024,2) as Megs
from dba_tables
where owner = 'PRODDTA'
and table_name = 'F0911'

returns 35750.84 for Megs

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,
Ben

Brian Peasland

unread,
Jun 20, 2006, 11:17:37 AM6/20/06
to

In DBA_TABLES, the BLOCKS column is populated when you calculate
statistics on the object. When you *estimate* stats, you may not get a
truly accurate number.

In DBA_SEGMENTS, the BLOCKS column is the actual number of blocks
allocated for that segment. I use this view for my true value.

HTH,
Brian

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

Brian Peasland
oracl...@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

Mladen Gogala

unread,
Jun 20, 2006, 10:58:18 PM6/20/06
to
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

Ben

unread,
Jun 21, 2006, 9:21:42 AM6/21/06
to


So why would you use DBA_EXTENTS as the golden rule as to what it
should be?

hpuxrac

unread,
Jun 21, 2006, 11:03:30 AM6/21/06
to

Umm because it is the accurate answer?

gazzag

unread,
Jun 21, 2006, 11:19:16 AM6/21/06
to
Ben wrote:
> So why would you use DBA_EXTENTS as the golden rule as to what it
> should be?

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3111.htm#sthref2069

HTH

-g

Brian Peasland

unread,
Jun 21, 2006, 11:55:07 AM6/21/06
to

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,

0 new messages