Re: how to get correct info on freelists?

For accurate freelist block counts,
you can use the dbms_space package
There is an example of this on my website

    http://www.jlcomp.demon.co.uk/freespac.html

However I note that the script was written in 1998, and the article was last updated in 2001, with references to "new" types appearing in 8.0. The package may have been enhanced since then. Note that in the example there is an explicit limit on any freelist walk that might take place, and the scripts only addresses the first freelist group.

If you think you have trouble with freelists, then you can check v$waitstat for waits for class "segment header" (if you had multiple freelist groups, you would also have to check for waits for class "free list") and then cross check with v$segstat to see which segments are responsible for most of them (there is a statistic in v$segstat called "buffer busy waits" - do not use v$segment_statistics as it is an expensive way to get the name, owner and tablespace of the object).

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


"Ben" <balvey_at_comcast.net> wrote in message 
news:1143691305.349112.299420_at_i39g2000cwa.googlegroups.com...


> running 9.2.0.5 on AIX5 with compatible parameter set to 8.1.7 and

> using DMT.

> Everything I've read so far was that dbms_stats was the best thing next

> to sliced bread. I just read in a manual though that it doesn't update

> freelist block information. So what do you do? revert back to analyze

> until you can start using LMT? I know that I have a large amount of

> blocks on the freelist and I suspect that we have a bad freelist

> contention problem with all of our tables set to 1 freelist with 1

> group on an system that is a combination oltp with large batch jobs. Is

> there a better way to get freelist block information? Is it true that

> dbms_stats doesn't gather freelist info?

>