Since I didn't see this yet, I thought I might add it in.
Try increasing the PCTFREE, perhaps there are two many transactions happening in the same block, and oracle is locking the entire block until the other transaction if finished. I know this is pretty common on multi-threaded updates, and PCTFREE is a great fix, but I'm not sure how much of a performance gain it will be for inserts.
Another thing, partition the table, so that the physical disk does not get hammered. It could just be IO limitations. In this case, 12 partitions each set up for a java load would work perfectly... or you might just want to hash partition it.
-----Original Message-----
From: CC Harvest [mailto:ccharvest_at_yahoo.com]
Sent: Wednesday, January 16, 2002 10:48 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: How to set DBWR_WRITE_PROCESSES in Oracle8i?
Thanks Kirti and Paul for the reply.
The instance is up for only 3 days. We know there are
very heavy inserts in the database.
Yes, There are high waits for 'buffer busy waits'
event in V$SYSTEM_EVENT view. But I didn't see 'buffer
busy waits' in V$SESSION_EVENT. I may try to increase
the freelist to 12(I don't know whether it helps,
anyway we have 12 Java threads insert data into the
same table concurrently).
Thanks,
Chris
- "Deshpande, Kirti" <kirti.deshpande_at_verizon.com>
wrote:
> How long has the instance been up?
>
> It appears that the application is trying to read
> the same data blocks &/or
> more sessions are trying to update the same data
> block or the free lists may
> need to be set properly to support concurrent
> INSERTs.
>
> So, before changing anything for DBWR process, I
> would suggest to check the
> top wait events from V$SYSTEM_EVENT view. You may
> see high waits for 'buffer
> busy wait' event. If so, I would trace it further
> using V$SESSION_EVENT and
> V$SESSION_WAIT (P1, P2 values) to find out the 'hot'
> segment and the file.
> You may have to review the pctfree, freelists,
> extent sizes etc. to address
> 'buffer busy waits'.
>
> For all you know, it could be just an application
> issue in scheduling the
> processes properly.
>
> And to answer your question about setting
> DBWR_WRITE_PROCESSES(actually the
> parameter is DB_WRITER_PROCESSES) you may want to
> check out Note# 97291.1 on
> Metalink. It has some good information.
>
> HTH,
>
> - Kirti
>
>
> -----Original Message-----
> Sent: Tuesday, January 15, 2002 7:16 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi, gurus:
> The database is oracle8.1.7.2.1 on win2k machine
> with 1 CPU and two hard drives. When I query
> v$waitstat, I got the following:
>
> CLASS COUNT TIME
> ------------------ ---------- ----------
> data block 246901435 132690159
> segment header 4869 8018
> undo block 20986 38476
> undo header 1895 1556
>
> seems like I need to set DBWR_WRITE_PROCESSES to a
> higher number, what number should I choose, 2 or
> bigger?
>
> Also, what other problems can you see from the last
> query, other than we need to add some more RBS?
>
> Thanks,
>
> Chris Harvest.
>
> __________________________________________________
> Do You Yahoo!?
> Send FREE video emails in Yahoo! Mail!
> http://promo.yahoo.com/videomail/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: CC Harvest
> INET: ccharvest_at_yahoo.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: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.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).
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: CC Harvest
INET: ccharvest_at_yahoo.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 Wed Jan 16 2002 - 13:37:18 CST
