Use mulitiple buffer pool. Check the doc for the BUFFER_POOL_KEEP pool.
Winnie
Deepak Sharma <sharmakdeep_at_yahoo.com> on 05/18/2000 10:48:06 AM
Please respond to ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc: (bcc: Winnie Liu/HQ/ISC)
Subject: RE: wait events
Chuck,
We are running version 8.0.4.0.0. How can we keep
(pin) tables in cache, if possible.
Thanks,
Deepak
- CHUCK_HAMILTON_at_qvc.com wrote:
>
> They may or may not represent a problem, depending
> on the type of
> application. They indicate that there's a lot of
> waiting for disk i/o. If
> it's a data warehouse application it's probably
> normal. If it's an oltp
> application, then you may be able to reduce them.
> Look at your
> db_buffer_cache hit ratio and try to improve it.
> Here are a couple of
> things to try
>
> 1. Increase the number of block buffers.
> 2. Look for smallish tables that are frequently
> scanned. Often these tables
> are used as code tables in joins to get a code's
> description. Pinning them
> in cache improves the overall hit ratio.
> 3. If it's a peoplesoft application and you have the
> process scheduler
> running, pin the PSPRCSRQST table in cache. It's
> scanned every 10 seconds
> by the process scheduler and unless it's pinned, the
> scans force it to end
> of the LRU list where it's constantly being removed
> from the cache.
> 4. Try increasing the
> db_file_mutltiblock_read_count. This can reduce wait
> times on table scans. You can alter this one at the
> session level to test
> different values.
> 5. Make sure you're not doing unnecesary table scans
> by indexing columns
> frequently used in WHERE clauses.
> --
> Chuck Hamilton
> QVC Inc.
> Enterprise Technical Services
> Oracle DBA
>
>
>
>
>
> Deepak Sharma
>
>
> <sharmakdeep@ To:
> Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> yahoo.com> cc:
> (bcc: CHUCK HAMILTON/QVC)
>
> Ext: NA Subject:
> RE: wait events
>
> Sent by:
>
>
> root_at_fatcity.
>
>
> com
>
>
>
>
>
>
>
>
> 05/16/00
>
>
> 07:34 PM
>
>
> Please
>
>
> respond to
>
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
>
>
>
> Hi All,
>
> We are getting large waits for 'db file sequential
> read' and 'db file scattered read' on the production
> system. How has your individual experience been on
> changing (increase/decrease) the db_block_buffers
> and
> db_file_multiblock_read_count in such cases ?
>
> Thanks,
>
> Deepak
>
>
> --- "Gait, Christopher" <cgait_at_condor.nrl.navy.mil>
> wrote:
> > Jack,
> >
> > I have a few comments on places to look. A lot of
> > the items near the top of
> > your list are normally ignored (like the 'SQL*Net
> > message from client') but
> > very high figures can indicate a networking issue
> > either in how the network
> > itself is doing (competition from other traffic,
> > packet size, presence of a
> > 'chattering' card on the net, etc.) or a Net8
> issue.
> > The high number of db
> > file scattered reads may be of more concern,
> > however, since it shows a lot
> > of full table scans are going on. This would point
> > to getting on your
> > developers 'top ten SQL queries from hell' and
> > optimizing them. This usually
> > represents a large part of the problem that is
> > 'Oracle's fault' or 'that
> > dang DBA.' Be nice to your developers, as a rule,
> > but if they continue to
> > write badly tuned SQL that ignores indexes and
> just
> > look at you oddly when
> > you talk about cost-based optimization and hints,
> > consider having one of
> > them hung in a cage above the cubicles as an
> example
> > to the others.
> >
> > I would be interested in seeing the results of
> this
> > query on your system at
> > a busy time:
> >
> > SELECT
> > Event,
> > SUM(Total_Waits),
> > SUM(Total_Timeouts)
> > FROM
> > V$Session_Event
> > WHERE
> > total_timeouts > 0
> > GROUP BY
> > Event
> >
> > This should pop some problem children to the
> > surface. Some of your values
> > look like you have a similar problem to us: bad
> I/O,
> > particularly redo. We
> > have always had problems with redo, and could
> still
> > use more tuning.
> >
> > How balanced is your I/O? Do you have the luxury
> of
> > having things spread
> > out, or are you stuck with what an SA laid down in
> > concrete for disk/array
> > setup? Also, what kind of a system is it, OLTP,
> DSS,
> > mixed? High transaction
> > rate?
> >
> > I just read in your next message in this thread
> that
> > you moved to a 4K block
> > size. Generally not a good idea, though I suppose
> > there are some really
> > high-transaction OLTP systems that can actually
> > benefit. We're comfortable
> > with an 8K on both our OLTP and DSS instances, but
> > then we have a
> > transaction rate a fast typist could probably keep
> > up with (~1,000
> > transactions a day) and you should probably
> consider
> > 16 or higher for
> > anything faintly resembling a warehouse.
> >
> > Regards,
> > Chris Gait
> >
> > -----Original Message-----
> >
>
=== message truncated ===
Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.
http://im.yahoo.com/
--
Author: Deepak Sharma
INET: sharmakdeep_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
Received on Thu May 18 2000 - 12:27:24 CDT
