Re: Re: Buffer busy waits are 10.96% of non-idle waits

From: oraora oraora <oraoraora_at_rediffmail.com>
Date: Thu, 11 Jul 2002 20:23:20 -0800
Message-ID: <F001.00495FB0.20020711202320@fatcity.com>

Hi Kavitha,

querying v$waitstat gives me the o/p below.

CLASS                   COUNT       TIME
------------------ ---------- ----------
data block          131525173  225446798
sort block                  0          0
save undo block             0          0
segment header           4968      16264
save undo header            0          0
free list                   0          0
extent map                  0          0
bitmap block                0          0
bitmap index block          0          0
unused                      0          0
system undo header          0          0
system undo block           0          0
undo header              1582         14
undo block              45965       3008

the data block above belongs to a datafile USERS01.DAT which has all the tables and indexes the application uses.

the top 25 SQL statements are always SELECT statements. they get executed repeatedly.
is it b'coz all SQLs are with literals and no bind variables ?

it's a highly read OLTP system.

will not

• using bind variables instead of literals
• seperating tables and indexes to diferent tabelspace

solve my problem ?

Regards,
prem.

On Fri, 12 Jul 2002 Kavitha Muthukumaren wrote :
>
>Hi ,
>
>TOAD gives this alarm often. what does it mean ? which view
>will
>give me the wait statistics ?
>
>Answer :
>======
>Please run STATSPACK to if this is one of the top waitevents to
>check if the percentage of wait - can be treated as problematic
>one
>
> SELECT p1 "File", p2 "Block", p3 "Reason"
> FROM v$session_wait
> WHERE event='buffer busy waits'
>Repeatedly run the above statement and collect the output. After
>a period
>of time sort the results to see which file & blocks are showing
>contention:
>
>
>" it occurs when a session cannot access a block because it is
>in
>use by another session. The two most common causes are
>insufficient free lists for a table or insufficient rollback
>segments. " --- IS THIS THE REASON ?
>
>Answer:
>======
> Yes on top of the wait event could also occur
> could also occur if
>
>a. if the application is going against a set of same blocks (hot
>blocks)
>
>Thanks,
>Kavi

---

There is always a better job for you at Monsterindia.com. Go now http://monsterindia.rediff.com/jobs

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: oraora  oraora
  INET: oraoraora_at_rediffmail.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 Thu Jul 11 2002 - 23:23:20 CDT