Re: how to get correct info on freelists?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 7 Apr 2006 16:43:40 +0100
Message-ID: <4NidnXgIQImtFavZnZ2dnUVZ8qOdnZ2d@bt.com>

"Ben" <balvey_at_comcast.net> wrote in message news:1144078276.420335.13990_at_i39g2000cwa.googlegroups.com...
> Jonathan,
> I've been running your see_space.sql script and before I run it on my
> prod schema, I was wondering how big of an impact it has on the system.
> Should I wait until off hours to run it?
> Thanks
> Ben
>

I've just run a quick test on 9.2.
The procedure seems to walk the freelist one block at a time. So if you think you have lots of objects with very large freelists, then you might want to wait for a quiet time when the I/O load is low.

Alternative strategy:
select segment_name, owner, header_file, header_block from dba_segments;

For each interesting segment

alter system dump datafile {header_file} {header_block};

then look at the trace file, which will have a section like:

Extent Control Header

Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 511 last map 0x00000000 #maps: 0 offset: 4128 Highwater:: 0x0240041a ext#: 3 blk#: 17 ext size: 128 #blocks in seg. hdr's freelists: 400
#blocks below: 400
mapblk 0x00000000 offset: 3
Unlocked
Map Header:: next 0x00000000 #extents: 4 obj#: 42458 flag: 0x40000000 Extent Map

The line you want is:

#blocks in seg. hdr's freelists: 400

The presence of this line is what made me think that the dbms_space call no longer walked the list.

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

Received on Fri Apr 07 2006 - 10:43:40 CDT