Oracle-L: Re: Tuning Question and Advice needed ..

From: Sergey V Dolgov <dsv_at_pptus.oilnet.ru>
Date: Fri, 26 Apr 2002 19:28:18 -0800
Message-ID: <F001.004513F8.20020426192818@fatcity.com>

Hello Bunyamin,

These values are values between starting utlbstat and utlestat (not from database startup).
It's impossible to make all wait events=0. Importance of different events depends on what database you have (OLTP, DSS, mixed). Optimims for wait events are zero wait time :-) You should query another views for identifying are these values optimal. For example for Latch Free event you should do the following query:

select name, sleeps/gets
from v$latch
where name='cache buffers lru chain';

sleeps/gets should be > 99% or you have to increase DB_BLOCK_LRU_LATCHES (default value for this parameter should be num_cpu*1.5 maximum value not greater then num_cpu*6)

So for another events.

Your values are normal for approx 10 Gb database with hundred or more concurrent sessions (working hard).

Saturday, April 27, 2002, 2:18:32 AM, you wrote:

BKK> Dear Gurus , I need your help ..

BKK> I run UTLBSTAT and then after 2 hour run the UTLESTAT.sql scripts as internal and look at the report.txt generated ...

BKK> On report.txt , I see that

BKK> System wide wait events for background processes: 
BKK> System wide wait events for non-background processes :
BKK> Latch statistics
BKK> Statistics on no_wait gets of latches
BKK> Sum IO operations over tablespaces

BKK> and other things also is in the report.

BKK> Question 1) 
BKK> What I wonder is  
BKK> Are these values between the start and end times of scripts. 
BKK> What I mean is  , for example  Is  the wait events for non-background processes list in report are the wait events occured between UTLBSTAT script run time T1 and UTLESTAT script run time T2   ?
BKK> or  are they total wait events from the startup of   database ?

BKK> Question 2) 
BKK> I see lots of wait events occur .. 
BKK> What will I give importance to ?

BKK> will I try to make 0 wait event in report. What are the optimums of wait events. ? BKK> For example I see

BKK> "Latch free event" occured for 3015 times , 
BKK> "control file sequential read" occured for 182 times ,
BKK> "log file sync" occured for 1756 times 
BKK> "direct path read" occured for 11000 times
BKK> "direct path write" occured for 6000 times

BKK> Are these values normal ? Will I try to make them 0 in the tomarrows report ?

BKK> How can I comment on the report ? Is there a link you can tell me to understand the problems and will help me to read and action about the problems ?

BKK> Thank you...

BKK> Bunyamin K. Karadeniz           
BKK> Oracle DBA / Developer
BKK> Civilian IT Department
BKK> Havelsan A.S. Eskisehir yolu 
BKK> 7.km Ankara Turkey
BKK> Phone: +90 312 2873565 / 1217
BKK> Mobile : +90 535 3357729

--

Best regards,

 Sergey                            mailto:dsv_at_pptus.oilnet.ru

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Sergey V Dolgov
INET: dsv_at_pptus.oilnet.ru

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 Fri Apr 26 2002 - 22:28:18 CDT