Thank you, Anjo.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11
Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark
-----Original Message-----
Sent: Monday, August 19, 2002 5:04 AM
To: Multiple recipients of list ORACLE-L
On the total_timeouts column:
This column has the same meaning for all events (how can it be
different?).
There are basically two kinds of events:
- That specificies a timeout value for the wait and can be posted
(not
needed though)
if (posted = wait(max_wait_time))
{
I was posted (so time is less than max_wait_time)
}
else
{
timeout occured
}
2) That measure a certain amount of elapse time. The way this this works
in
pseudo code is:
begin time wait
do OS read system call (e.g.)
end time wait
The 'latch free' event is an example of the first one. Some latches can
be
posted (and will be posted). You can even force all latches to be
posted. So
what does total_timeouts mean on latches ? Well it only has meaning for
some
of the latches. If a certain latch is hold for a long time, other
processes
may waste a lot of
cpu for checking if these latches are free and the other processes may
not
directly see that the latch is free (only after they timeout). So if a
process frees a latch that is held for a long time, it will directly
post
the first waiter. So if the number of timeouts is high, it means that
processes are not getting posted and it indicates a high contentious
environment (or some one has fiddled around with an init.ora parameter
that
they shouldn't touch). If the number of timeouts is low it means that
processes are getting posted and (probably) means less contention.
Anjo.
- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Monday, August 19, 2002 1:23 AM
> Hi Cary
>
> Thanks for the feedback, comments inline
>
> Cary Milsap wrote
> >
> > Some questions and a couple of comments regarding Dave's note:
> >
> > 1. RE the "tuning from a blue collar DBA perspective," is it
accurate to
> >
> > paraphrase the described method as: "No matter what might be causing
the
> > performance problem, check this List Of Things first, using tools
that
> > vary significantly from one platform to the next." ?
>
> Well, I hoped I demonstrated how on at least one platform.
>
> My solution for another platform, if vocalized, will start another
> of these tedius OS religious battles
>
> I was trying to say that complex tools and traces such as
> tkprof and event 10046 are of no help if you don't understand
> the underlying system and SQL.
>
> Despite what is said there are uses for database ratios. Some
> such as disk vs memory sorts are vital to throughput.
>
> Ignore the fact that it is imperfect and use explain plan
> to look for inappropriate FTS. The payoff in quickly catching
> unecessary I/O, both physical and logical, overrides the need for
> detailed analysis
>
> Then is the time for tkprof, setting events, changing the optimizer
and
> such
>
> > 2. Dave is multiplying Oracle's time statistics by 1/1000 (wrong)
> > instead of 1/100 (correct). Oracle is really reporting 'db file
> > sequential read' average latencies of .311cs = 0.003s (not 0.0003s),
'db
> > file scattered read' latencies of .506cs = 0.005s (not 0.0005s), 'db
> > file parallel write' latencies of 3.036cs = 0.030s (not 0.003s), and
so
> > on. (Dave's I/O subsystem has consumed an average of 30ms for each
'db
> > file parallel write' call.)
>
> I don't know why but I seem to have alot of troubles with decimals on
> this list. Sure don't have the same problem with cheques :)
>
> >
> > 3. Note that it's only because the data are collected system-wide
that
> > it is necessary to ignore the 'SQL*Net%' events. This is a waste,
> > though, because with properly time-scoped session-level data, the
> > 'SQL*Net%' events constitute probably the easiest way to detect when
you
> > have bad applications code (not the SQL, but the stuff that calls
the
> > SQL).
>
> I agree the data is a waste but many do not know this and worry
> needlessly.
>
> So at the session level the SQL Net events allow me to troubleshoot
> the applications server. Cool .... I continue to learn.
>
> >
> > 4. 'db file sequential read' does *not* typically indicate a
full-table
> > scan, because 'db file sequential read' events, since Oracle8.0 are
> > almost always single-block read calls (before that, the event could
> > indicate multi-blocks reads of sort segment blocks into a PGA).
>
> Does this parameter measure anything then?
>
> >
> > 5. 'LGWR wait for redo copy' is *not* affected by the archiver not
> > keeping up. The alert log *is* a better way to detect this (because
> > 'LGWR wait for redo copy' doesn't detect it at all). An even better
way
> > is to look for occurrences of 'log file switch (archiving needed)'.
> >
> > 6. 'latch free' -- Question: Does anybody know what "total_timeouts"
> > means for the 'latch free' event? I see nothing in v$latch that
possibly
> > corresponds to something that could be called a "timeout." And
nothing
>
> I notice that these numbers often climb when Oracle has to be
aggressive
> in reclaiming shared pool memory because of literal SQL. I find that
> reducing
> the shared pool to as small as possible while storing the data
> dictionary
> optimizes performance (even if it's still bad) which are reflected in
> these numbers.
>
> The many bugs in the shared pool memory handling in many
> 8i versions often show up here too.
>
> Thanks once again Cary.
>
> On to other stuff
>
> At my current contract I am using a NetApp filer as an archive machine
> and
> I am extremely happy with how it has worked out.
>
> Archive logs are stored locally on a T3 and through NFS on the filer.
> MIN_SUCCEED_DESTINATION or whatever is set to one. If the NetApp
> disappears
> oracle keeps chugging along writing the archive logs to the local
array.
>
> All hot backups are put on the filer.
>
> I would be extremely hesitant to use it for any other files.
>
> I do have to be careful with the timing of my backups as it
> is pretty easy to overwhelm the appliance when copying files in.
>
>
> Dave
>
> Dave Morgan
> dvmrgn_at_telusplanet.net
> 403 399 2442
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Dave Morgan
> INET: dvmrgn_at_telusplanet.net
>
> 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).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Anjo Kolk
INET: anjo_at_oraperf.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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Cary Millsap
INET: cary.millsap_at_hotsos.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).
Received on Mon Aug 19 2002 - 12:53:26 CDT
