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