I'm quite surprise that an 8K block give worst
performance than a 4K block even on an oltp system.
I've did a little tuning lately on our system. The
best gain was obtained by rewriting some sql, not ny
tuning the database.
Even though the optimiser is getting better, it's not
perfect, not in 8.1.5 anyway.
Look at the sql being executed a lot and the ones
having a lot of gets. You can do marvels there as very
few developpers know what is an access plan or how to
interpret it.
- Deepak Sharma <sharmakdeep_at_yahoo.com> a icrit : >
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-----
> >
> >
> > Hi All,
> >
> >
> > I have run utlestat & utlbstat plus some other
> > scripts and have a lot of
> > wait
> > events
> >
>
> > *************************
> > Event Name Count
> Total
> > Time Avg Time
> > -------------------------------- -------------
> > ------------- -------------
> > SQL*Net message from client 443489
>
> > 4645139 10.47
> > rdbms ipc message 1995
>
> > 999996 501.25
> > db file scattered read 1087432
>
> > 798071 .73
> > PL/SQL lock timer 1
>
> > 10001 10001
> > buffer busy waits 3542
>
> > 2415 .68
> > latch free 6804
>
> > 2149 .32
> > db file sequential read 35326
>
> > 1812 .05
> > SQL*Net message to client 443501
>
> > 617 0
> > log file sync 1354
>
> > 514 .38
> > SQL*Net more data to client 5142
>
> > 98 .02
> > control file sequential read 18
>
> > 4 .22
> > enqueue 1
>
> > 0 0
> > file open 13
>
> > 0 0
> > refresh controlfile command 6
>
> > 0 0
> > 14 rows selected.
> > SVRMGR>
> > SVRMGR>
> > SVRMGR> Rem System wide wait events for background
> > processes (PMON, SMON,
> > etc)
> > SVRMGR> select n1.event "Event Name",
> > 2> n1.event_count "Count",
> > 3> n1.time_waited "Total Time",
> > 4> round(n1.time_waited/n1.event_count, 2)
> > "Avg Time"
> > 5> from stats$bck_event n1
> > 6> where n1.event_count > 0
> > 7> order by n1.time_waited desc;
> > Event Name Count
> Total
> > Time Avg Time
> > -------------------------------- -------------
> > ------------- -------------
> > rdbms ipc message 5441
>
> > 1383003 254.18
> > smon timer 7
>
> > 210005 30000.71
> > pmon timer 668
>
> > 200693 300.44
> > log file parallel write 1456
>
> > 499 .34
> > control file parallel write 666
>
> > 420 .63
> > latch free 8
>
> > 5 .63
> > control file sequential read 12
>
>
=== message truncated ===
Stephane Paquette
DBA Oracle
Received on Wed May 17 2000 - 02:37:31 CDT
