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

oraora oraora£¬

    hi, i think use bind variable will give better performance, but it has nothing to do with the buffer busy wait.Using bind variable will solve the repeated parse and thus reduce library latch free wait.

	And seperate table and index won't help either. There is a greate thread in dbforums.com talking about seperate table and index .
	As for the buffer busy wait, i do not have any good idear either.

Good luck!

            chaos
            chaospku_at_163.net

zhu chao
DBA of Eachnet.com
86-021-32174588-667

ÔÚ 2002-07-11 20:23:00 You wrote:
>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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: chaos
  INET: chaospku_at_163.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).