RE: Tuning methodology (was T3's) and use of NetApp's

>
> 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).
[DM] Does this parameter measure anything then?

It shows the number of blocks read from the OS via calls to read().

> 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

[DM] 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.

[DM] The many bugs in the shared pool memory handling in many 8i versions often show up here too.

I'm still trying to find a plausible definition for what "total_timeouts" means when the event is 'latch free'. When I think I have it straight in my mind, I'll let you know :).

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-----
Morgan
Sent: Sunday, August 18, 2002 6:23 PM
To: Multiple recipients of list ORACLE-L

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: 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).