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

One more detail: "buffer busy waits" is not an indication of competition for a block on disk. It indicates competition for a block IN MEMORY. More memory won't help, faster disks won't help. The cure is to make the competing processes not compete.

The problem is usually a result of applications that have been "parallelized" by running several similar jobs simultaneously, without designing the jobs to work on different subsets of the data at the same time.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:

- Hotsos Clinic, Jul 23-25 Chicago
- Miracle Database Forum, Sep 20-22 Middlefart Denmark
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas

-----Original Message-----
Sent: Friday, July 12, 2002 4:14 PM
To: Multiple recipients of list ORACLE-L

> CLASS COUNT TIME
> ------------------ ---------- ----------
> data block 131525173 225446798

> will not
> -- using bind variables instead of literals
> -- seperating tables and indexes to diferent tabelspace
>
> solve my problem ?

No and no. You have a very specific problem: Two or more processes are trying to access the exact same data block at the same time. You are part
way through diagnosing your problem but you haven't finished yet.

Your job now is to figure out some very specific things: which data block
the processes are waiting for, which table or index the block belongs to,
which SQL statement keeps accessing that block so often, and why.

Diagnosis complete, you'll fix the problem. But you can't do that until you
know what the problem is. Until you know which SQL statement is causing the
problem and specifically what the nature of the problem is, you're not going
to be able to fix it.

To figure out the next steps to take, which v$ views to query, what to look
for, what the common problems are and how to fix them, do a Google search
for:

"buffer busy waits" "data block"

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