RE: wait events

Pls ignore my previous mail; I forgot about the 'Alter table .. cache' stmt.

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