Oracle-L: RE: insert performance

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Thu, 11 Apr 2002 12:18:30 -0800
Message-ID: <F001.004424F8.20020411121830@fatcity.com>

Paul - My point is that I have seen performance decrease when too many processes are used. For example, with imports, a multi-cpu system may import faster with two import jobs running. But at some point it seems that Oracle and/or the operating system is just trying to switch between each process to make sure they are all getting attention, and the overall performance suffers. It certainly doesn't just focus on one process and ignore all the others. Given that you are throwing 50 processes at the 4-cpu system, I could see it running slower. Typically on for a meaningful benchmark, you would vary the number of processes to see where the optimum throughput is achieved. This is why benchmarks are so expensive to perform -- very time-consuming. On a practical level, as a DBA if you must move a lot of data in a short window, a weekend conversion for example, it is a good idea to try a little benchmarking to try to improve the data movement rate. Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Thursday, April 11, 2002 2:29 PM
To: Multiple recipients of list ORACLE-L

Dennis--
The faster machine had 10 database connections, the slower, 50. Not all were used in either case. But my comparison is for inserts done by a single session, i.e., it's not the aggregate insert rate, but a direct comparison of the same insert statement in a single session on each server.

As for the disk layout, I don't have that information readily available (the SA department doesn't like to let the DBA department get involved in such things!). I don't think it's significantly different, though.

Thanks,
Paul

DENNIS WILLIAMS <DWILLIAMS_at_lifetouch.com> wrote:
> Paul - How many insert processes did you run on each system? Is the
> disk
> layout identical in terms of spreading across devices?
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Wednesday, April 10, 2002 6:43 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Greetings!
>
> I am trying to diagnose a performance difference between two
> databases
> running the same test. They are similarly configured (same SGA size,
> etc.), and the servers are identical except for the number of CPUs
> (server A has 4, server B has 6).
>
> On database A, INSERT performance is about 190 rows/second.
>
> On database B, INSERT performance is over 500 rows/second.
>
> I saw some cache buffers chains, buffer busy, and library cache latch
> waits on database A while the test was running, as well as redo log
> sync waits. The waits didn't seem excessive, though. I checked for
> "checkpoint not complete" redo allocation messages in database A's
> alert log and found none. The db_block_lru_latches parameter is set
> to
> one-half the number of CPUs in both machines.
>
> I'd much appreciate any suggestions as to what else to check. I know
> that freelists can be an issue (there are multiple sessions
> performing
> inserts); how can I check to see if there's freelist contention?
> Anything else I should investigate?
>
> Many TIA,
>
>
>
>
>
> =====
> Paul Baumgartel, Adept Computer Associates, Inc.
> paul.baumgartel_at_aya.yale.edu
>
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Tax Center - online filing with TurboTax
> http://taxes.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Paul Baumgartel
> INET: treegarden_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: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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!?
Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Baumgartel
  INET: treegarden_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: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.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 Apr 11 2002 - 15:18:30 CDT