Bob,
Just to add a couple of points to your note. From Oracle 8.1.6,
you can alter the number of freelists without having to "reorg"
the table, using the ALTER TABLE command.
Also, the "age old" recommendation (from Oracle Docs. and other
published material), that the number of freelists for a table
should be equal to the "number of concurrent inserts" is not
feasible on most systems. It probably can be classified as
"folklore" on most production systems today.
For e.g., if you have an environment with 400 concurrent
sessions inserting data into a table, creating 400 freelists is
unreasonably high. This is because, the potential overhead
incurred in tracking and accessing so many freelists when
concurrent inserts occur on the system, is very high.
Also, configuring too many freelists, will have the effect of
"artificially increasing the high water mark" of a table. An
"artificially high" high-water-mark for a table, will cause
Oracle to perform more I/O on the table than required for
full-table scans, there by imposing an undesired stress on the
I/O sub-system.
Realize, that if you have multiple freelists, when an extent
gets allocated, the blocks of the allocated extent, get
disbursed across the number of freelists for the table. An
optimal value of "2 x # of CPUs", is more than adequate on most
systems.
Best Regards,
Gaja
- Bob VanderMey <BVanderMey_at_OrderZone.com> wrote:
> Ruth,
>
> We had a similar problem and it turned out that the freelists
> for a table
> needed to be increased. If you have a table that has a lot of
> inserting you
> may need to increase the freelists for that table. I'm not
> sure what is the
> easiest way to find the table that needs its freelist
> parameter increased.
> We spent a bunch of time trying to track it down by looking at
> I/O against
> all the tables and then narrowing it down to the few most
> likely ones. Once
> you find the table(s), the only way I know to increase the
> freelists is to
> rebuild that table (we did an export, rebuild and import). If
> I remember
> correctly, the freelists should be set to the number of
> concurrent inserts
> against that table.
>
> Hope that helps!
>
> Bob VanderMey
> Oracle DBA
> Works.com
> 847 573-2687
> bvandermey_at_works.com
> 565 Lakeview Parkway, Suite 250
> Vernon Hills, IL 60061
> http://www.works.com
>
>
> -----Original Message-----
> Sent: Wednesday, August 30, 2000 8:42 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Steve, and everyone else,
>
> Well, that's not it. Buffer Busy Waits is near the bottem.
> SQL*Net Message
> from client is at the top, followed by Slave Wait and CPU used
> by this
> session.
>
> Any other ideas? I will be a hero if I get a solution.
> Ruth
>
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, August 30, 2000 9:17 AM
>
>
> > Hi Ruth,
> >
> > Try running the system_times.sql script from the Ixora web
> site. If
> 'buffer busy
> > waits' is near the top and represents a significant
> proportion of the
> total
> > times, then you may have correctly identified the problem.
> >
> > @ Regards,
> > @ Steve Adams
> > @ http://www.ixora.com.au/
> > @ http://www.christianity.net.au/
> > @
> > @ Going to OpenWorld?
> > @ Catch the Ixora performance tuning seminar too!
> > @ See http://www.ixora.com.au/seminars/ for details.
> >
> >
> > -----Original Message-----
> > Sent: Wednesday, 30 August 2000 22:35
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Dear Listers,
> >
> > When I query v$waitstat I get a large number for the class
> data block and
> > my database is running very slowly. Are these two things
> related and is
> > there anything I can do?
> >
> > As always, thank you for any light you can shed.
> >
> > Ruth B. Gramolini
> > ORACLE & DB2 DBA
> > VT Dept. of Taxes
> > ph# 802.828.5708
> > fax# 802.828..3754
> > rgramolini_at_tax.state.vt.us
> >
> > --
> > Author: Ruth Gramolini
> > INET: rgramolini_at_tax.state.vt.us
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> > San Diego, California -- Public Internet access /
> Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an E-Mail
> message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).
> You may
> > also send the HELP command for other information (like
> subscribing).
> >
> > --
> > Author: Steve Adams
> > INET: steve.adams_at_ixora.com.au
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> > San Diego, California -- Public Internet access /
> Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an E-Mail
> message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).
> You may
> > also send the HELP command for other information (like
> subscribing).
> >
>
> --
> Author: Ruth Gramolini
> INET: rgramolini_at_tax.state.vt.us
>
> Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> San Diego, California -- Public Internet access /
> Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail
> message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).
> You may
> also send the HELP command for other information (like
> subscribing).
>
> --
> Author: Bob VanderMey
> INET: BVanderMey_at_OrderZone.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> San Diego, California -- Public Internet access /
> Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail
> message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).
> You may
> also send the HELP command for other information (like
subscribing).
Gaja Krishna Vaidyanatha
Director, Storage Management Products
Quest Software Inc.
(972)-304-1170
gajav_at_yahoo.com
Received on Wed Aug 30 2000 - 19:48:34 CDT
