Partition. Even on 7, obvious in this case would be
partition views with maybe some changes in the app. Or
maybe not depending the app.
Stripping. Oracle stripping and OS stripping.
Freelists. It seems that the app insert and query.
Because of the inserts it is a perfect case of
freelists. If you the database is on 8.1.6, you can
change the freelists through alter table.
Reorg. If you could afford to throw the old data from
the table to a backup or another database, would be
great. In another database the users could query the
old data, just like in the production one. Because
sometimes Dev and the users prefer to leave the table
growing and leaving old data behind and they don't
admit that the data they use is only 10% of the table
and the newest. I don't know maybe I am wrong because
you said it is a customer one.
Try to keep Oracle means of mantaining referential
integrity away from the table. I mean foreign keys or
integrity controled by triggers. The less fk they
need, less indexes need to be created. And those
indexes always seem to be a nightmare when you use
rule optimizer. Because they are choosen by Oracle and
in most of the cases they don't have a good
selectivity. What I am saying maybe it is a thing
argue a lot. However I've seen contrary to popular
opinion that in big tables it is better to control the
referential intergrity through the app..
Partition indexes too.
Haven't you seen some ORA-1555 when you have a lot of
users querying the table and adding data ?. Because
for a main table like that in a place I've been, we
implemented the table in a cluster.
Regards.
- CC Harvest <ccharvest_at_yahoo.com> wrote:
> I have a big table too, about 16GB.
> It is a vert static one(customer table), will add
> data
>
> once in it, but queried all the time, and do the
> segmentations against it all the time.
>
> It also has 12 indexes. Right now , the performance
> is
> ok. Any special consideration for such a big table.
>
> Thanks,
>
> Harvest.
>
> --- Christian Trassens <ctrassens_at_yahoo.com> wrote:
> > Don't put it in a buffer keep because of the
> amount
> > of
> > consistent gets the table could have. It seems to
> be
> > a
> > high volatile table.
> >
> > Maybe it is too late but you should look on the
> > initrans, freelist of the table. Also if the table
> > has
> > foreign keys, talk with Development to leave the
> > table
> > without them. I should consider to partition the
> > table....maybe a hash one.
> >
> > Regards.
> >
> >
> > --- Walter K <alden14004_at_yahoo.com> wrote:
> > > Hi,
> > >
> > > I have a table that is going to have a large
> > amount
> > > of
> > > inserts, updates and deletes performed against
> it
> > > daily. Approximately 1,000,000 transactions per
> > day
> > > (some single-record, some multi-record). The
> table
> > > is
> > > ~100Mb in size.
> > >
> > > I'm looking for some suggestions on what I can
> do
> > to
> > > have the most optimal I/O for the table. I've
> been
> > > doing a little reading about buffer pools. Is
> > > assigning this table to a KEEP pool a practical
> > > approach or is that not going to buy me anything
> > > because DML is involved? Does anyone have any
> > other
> > > suggestions?
> > >
> > > Unfortunately, I can't put the table on a
> > dedicated
> > > disk and I am stuck with Raid-5 currently.
> > >
> > > Any suggestions would be appreciated.
> > >
> > > Thanks in advance!
> > > -w
> > >
> > >
> __________________________________________________
> > > Do You Yahoo!?
> > > Make international calls for as low as
> $.04/minute
> > > with Yahoo! Messenger
> > > http://phonecard.yahoo.com/
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Walter K
> > > INET: alden14004_at_yahoo.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).
> >
> >
> > =====
> > Eng. Christian Trassens
> > Senior DBA
> > Systems Engineer
> > ctrassens_at_yahoo.com
> > ctrassens_at_hotmail.com
> > Phone : 541149816062
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Make international calls for as low as $.04/minute
> > with Yahoo! Messenger
> > http://phonecard.yahoo.com/
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Christian Trassens
> > INET: ctrassens_at_yahoo.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).
>
>
> __________________________________________________
> Do You Yahoo!?
> Make international calls for as low as $.04/minute
> with Yahoo! Messenger
> http://phonecard.yahoo.com/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: CC Harvest
> INET: ccharvest_at_yahoo.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).
Eng. Christian Trassens
Senior DBA
Systems Engineer
ctrassens_at_yahoo.com
ctrassens_at_hotmail.com
Phone : 541149816062
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
Received on Wed Aug 22 2001 - 02:38:33 CDT
