DBA_TABLES
Saminathan Seerangan, April 22, 2003 - 5:04 am UTC
Dear Tom,
Question reg. DBA_TABLES.
a)How AVE_SPACE_FREELIST_BLOCKS and NUM_FREELIST_BLOCKS column values are calculated for each tables?
b)Does it have any relationship with AVG_SPACE column?
Expecting your valuable comment or pointer.
April 22, 2003 - 7:51 am UTC
they are populated as part of a gather statistics operation:
ops$tkyte@ORA920LAP> select NUM_FREELIST_BLOCKS, AVG_SPACE_FREELIST_BLOCKS
2 from dba_tables where table_name = 'A';
NUM_FREELIST_BLOCKS AVG_SPACE_FREELIST_BLOCKS
------------------- -------------------------
ops$tkyte@ORA920LAP> analyze table a compute statistics;
Table analyzed.
ops$tkyte@ORA920LAP> select NUM_FREELIST_BLOCKS, AVG_SPACE_FREELIST_BLOCKS
2 from dba_tables where table_name = 'A';
NUM_FREELIST_BLOCKS AVG_SPACE_FREELIST_BLOCKS
------------------- -------------------------
0 0
this reports on FREELIST space, the number of blocks on the freelist (less than or equal to the number of blocks in the table) and the average freespace on the blocks on the freelist -- it is not really related to avg_space -- the average free space on ALL blocks in the table -- directly.
Hi
A reader, January 05, 2004 - 5:07 pm UTC
I have a situation where a Pro *C program is taking about 1 hour to execute on one schema (say schema A) and about 6-8 hours on the other (say schema B). There is absolutely no difference in the table structures on both the schemas. The tables are analyzed, the tables have same number of indexes and the tables have almost same number of records.
The only difference I found is the value of the "avg_space_freelist_blocks" and "num_freelist_blocks" are differnet. The values of these two attributes are higher for schema B.
Can you throw some light on this?
thanks,
January 06, 2004 - 8:00 am UTC
tkprof -- just turn on SQL_TRACE=TRUE and run both apps (make sure max_dump_file_size is set high enough -- i'm fond of "unlimited")
then look at the tkprof. All will become apparent at that point (99.99% chance of that being an accurate statement :)
AVG_SPACE x AVG_SPACE_FREELIST_BLOCKS
Daniel, August 25, 2006 - 10:06 am UTC
I use Oracle 8.1.7, and 8192 block size. I have a table with the following values after a compute statistics:
NUM_ROWS: 39196755
BLOCKS: 1096005
EMPTY_BLOCKS: 2065
AVG_SPACE: 5
AVG_SPACE_FREELIST_BLOCKS: 5183
NUM_FREELIST_BLOCKS: 693067
I don't understand how can this table have AVG_SPACE=5 in contrast with AVG_SPACE_FREELIST_BLOCKS*NUM_FREELIST_BLOCKS so high! Would it be because AVG_SPACE doesn't compute freelists blocks values?
August 27, 2006 - 8:40 pm UTC
are you using analyze? dbms_stats doesn't really maintain this stuff that the optimizer doesn't actually use.
AVG_SPACE x AVG_SPACE_FREELIST_BLOCKS
Daniel, August 28, 2006 - 9:19 am UTC
Hi there! Thanks for the followup! But I just want to know how much space had been freed after executing a lot of DELETE on data, i mean, optimizing query execution is not my goal.
August 28, 2006 - 11:00 am UTC
I would use dbms_space.free_blocks or .space_usage to check that information out.
else you sort of have to "analyze" the table