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
Ben wrote: > 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
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.
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.
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.
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.
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.
>> 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.
