empty block vs no. freelist blocks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ben - 23 Mar 2006 15:18 GMT

Running 9.2.0.5 in OEM. If you look at a table on the statistics tab it
lists out 'Empty Blocks:' and 'No. of Freelist Blks:'. What is the
difference in these two values? I've got a table with 9795 empty blocks
but only 5 freelist blocks.
Are the empty blocks ones that were grabbed when the table extended but
have never had data in them, and the freelist blocks have data but are
still available for inserts?
Where can I find these values outside of OEM? dba_tables?
Thanks for any help.

Reply to this Message

Jonathan Lewis - 23 Mar 2006 15:30 GMT

> Running 9.2.0.5 in OEM. If you look at a table on the statistics tab it
> lists out 'Empty Blocks:' and 'No. of Freelist Blks:'. What is the
[quoted text clipped - 3 lines]
> have never had data in them, and the freelist blocks have data but are
> still available for inserts?

Pretty close.

Empty blocks are the blocks which have been reserved
for use, but have not yet been put into play.

Oracle operates a 'high-water mark' for each object,
making a few of the empty blocks available for use by
moving a marker along the reserved space - typically
5 blocks at a time.

Freelist blocks are blocks which either:
have just become available because the highwater mark
has been moved up
or
have previously had some data in them, but enough data
has been deleted from them to make them available for
new data to be inserted. (See PCTUSED for further
details).

There have been lots of changes in interpretation and use
since ASSM (automatic segment space management)
appeared, though, so plenty of details I have omitted.

> Where can I find these values outside of OEM? dba_tables?
> Thanks for any help.

select
blocks, empty_blocks, num_freelist_blocks
from
user_tables
where
table_name = 'CHILD'
;

These figures will only be accurate immediately after
you have collected statistics for a table, though. (The
same applies to the OEM report, probably).

There is a dbms_space package that you can use
to get accurate figures even when the statistics have
not been collected.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

Reply to this Message

Ben - 23 Mar 2006 19:13 GMT

I should have mentioned this is on a DMT, I guess that is evident from
even posing the question though. I now have more concerns as I am
finding tables with 4 Gig of blocks on the freelist and a couple
hundred Meg as empty blocks. I know the correct answer is migrate to
LMT with ASSM, but that is a little easier said than done on a 400G
production database. For the time being I need to fix the problems at
hand. ugnh.

More questions though.

What is the relationship between the segment hwm and the free space
that shows in dba_free_space?

Are empty blocks above or below segment hwm?

Does the freelist include empty blocks?

What would cause such a high number of blocks on the freelist? Large
deletes?

Reply to this Message

Sybrand Bakker - 23 Mar 2006 22:57 GMT

>I should have mentioned this is on a DMT, I guess that is evident from
>even posing the question though. I now have more concerns as I am
[quoted text clipped - 15 lines]
>What would cause such a high number of blocks on the freelist? Large
>deletes?

1 free space in dba_free_space are the blocks in the *tablespace*
which are unused by any segment

2 Untouched blocks are above the HWM

3 Anything touched below the HWM which gets below PCTUSED

4 Yes. However, likely they are not going to be re-used EVER, as
Oracle doesn't keep searching for the most 'empty' block.
This is resolved with ASSM.

--
Sybrand Bakker, Senior Oracle DBA

Reply to this Message

Ben - 24 Mar 2006 04:27 GMT

why wouldn't the freelist blocks be used? The row length isn't longer
than the block size... Just picky data? ;)

Reply to this Message

Jonathan Lewis - 24 Mar 2006 09:04 GMT

>>What would cause such a high number of blocks on the freelist? Large
>>deletes?
>
> 4 Yes. However, likely they are not going to be re-used EVER, as
> Oracle doesn't keep searching for the most 'empty' block.
> This is resolved with ASSM.

If they are on the free list they can be used eventually
if the space is needed. Oracle HAS to start at the top
of the free list when searching for space. You may
be thinking of cases where space is wasted because
the block has been taken off the free list when it could
be used for further inserts.

There are a couple of oddities that might explain this,
but the most likely is simply a massive delete followed
by a usage pattern that never needs that much space
ever again.

The oddities include
a bug in a much earlier version

having multiple freelist groups - and managing to
get all the deleted blocks into one freelist group
and always doing the inserts in another.

As far as ASSM fixing things - one of the issues with
ASSM is (was) that it would keep searching blocks
pointlessly when they should have been marked as
unavailable (full), with the effect that a single insert
could do literally hundreds of gets on bitmap blocks
and associated data blocks looking for a block
that was empty enough.

Signature

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

Reply to this Message

Jonathan Lewis - 24 Mar 2006 09:03 GMT

>I should have mentioned this is on a DMT, I guess that is evident from
> even posing the question though. I now have more concerns as I am
[quoted text clipped - 15 lines]
> What would cause such a high number of blocks on the freelist? Large
> deletes?

Switching to ASSM is not automatically the
correct answer. But if you want to do so, bear
in mind that you need only move one table at
a time, you don't have to "fix" the whole database.

Create one LMT, then move one table (and you'll
have to rebuild its indexes - so you might want to
add LMTs for rebuilding indexes as you move tables,
of course).

It is possible that ASSM might have eliminated this
odd empty space - but it is possible that it is a relic
of a historic event such as a massive delete. If your
subsequent work is a continuous cycle of inserts
and deletes thereafter, it may be that the table
simply never needs anything like that 4GB of
available space, and a one-off rebuild may be
sufficient to deal with the issue.

Signature

Reply to this Message

Ben - 24 Mar 2006 13:46 GMT

Thank you Jonathan and Sybrand. I haven't been working as a dba for
very long and I think almost every question that I have posted on here
the two of you have given some insight on. It is greatly appreciated.

Reply to this Message

Ben - 24 Mar 2006 16:08 GMT

One other question.
If I move/rebuild these tables and indexes, would that space be
released to the tablespace or back to the datafile as unused space?
What steps would be needed to get that space back as actual OS file
space?

alter old_tablename move new_tablename new_tablespace
alter new_tablename move old_tablename old_tablespace
rebuild indexes
?

Reply to this Message

Joel Garry - 24 Mar 2006 22:33 GMT

>If I move/rebuild these tables and indexes, would that space be
>released to the tablespace or back to the datafile as unused space?
>What steps would be needed to get that space back as actual OS file
>space?

The tablespace consists of datafiles, the datafiles won't shrink unless
you tell them to with the RESIZE command, the tablespace will have more
space available. To get the OS space back, you need to shrink the
datafiles - Oracle won't let you shrink to where something exists in
the space. RESIZE is part of the ALTER DATABASE DATAFILE command.

OEM has a display option called "Show Tablespace Map" which I've found
useful for visualizing where stuff ends in the tablespace/datafile.
Databases --> your database --> storage --> tablespaces --> right click
on the tablespace and select the Show Tablespace Map. When it gets
around to coming up, you can hover over segments to get more
information. Once you see how much free space there is at the end of
the desired datafiles, you can expand the "datafiles" (under storage,
not under the tablespaces) and put in the desired file size. Since I'm
one who believes the job isn't done right unless it is repeatable (and
I often do things like this on test db's first), notice you can show
the SQL it is using and you can cut and past that into scripts without
actually doing it. Also note what gets written into the alert log.

Alternatively, you can figure out the SQL to create a segment list
(highly recommended as a learning experience for new DBA's), or just be
a neandertal and put in random resize commands in sqlplus until it
shrinks.

Oh yeah, be sure you have good backups before doing any of this. All
manner of klutziness can be overlooked with quick restores.

jg
--
@home.com is bogus.
The business model of the new millennium:
http://www.signonsandiego.com/uniontrib/20060324/news_1b24patriot.html

Reply to this Message

Ben - 27 Mar 2006 21:16 GMT

yes, I forgot to mention the datafile resize, thank you.

I am still a little confused as to where the space is released to
though. I thought even if I exported/dropped/imported the table that
would just release the free space to be used by other objects within
the tablespace. And if that space was in the middle of the datafile
with other used segments surrounding it, the you can't necessarily
resize the datafile to regain that OS space. You could only resize the
datafile down to the hwm.

Reply to this Message

Joel Garry - 31 Mar 2006 20:23 GMT

> yes, I forgot to mention the datafile resize, thank you.
>
[quoted text clipped - 5 lines]
> resize the datafile to regain that OS space. You could only resize the
> datafile down to the hwm.

That is correct. There can be "holes" in between objects. That's why
I like the tablespace map function so much, you can see right away what
you must move around to get rid of the holes. The holes aren't so bad
since Oracle implemented Locally Managed Tablespaces, since something
else can fill them, and there generally aren't any worthwhile
performance gains to be had by worrying about where things are in the
tablespace. Jonathan and others demonstrate one minor fragmentation
exception here:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5549302357655

But really, unless you have some heavily batch oriented system or
extremely volatile tables or major data migrations or way off defaults
or old Dictionary Managed Tablespaces laying about, there isn't much to
worry about if you just let Oracle handle everything. This is pretty
informative: http://dizwell.com/main/content/view/64/87/

jg
--
@home.com is bogus.
'Make a sign and hang it above your monitor so you see it every day
"The job of the dba is not to back up the database, but to recover the
database." (To paraphrase Tim Gorman)' - Daniel Fink

Reply to this Message