Re: Oracle Design For Performance Question

From: Dba DBA <>
Date: Tue, 10 Feb 2015 13:46:34 -0500
Message-ID: <>

You are not actually asking the correct questions.
  1. how big are the messages in bytes? This will determine how much archivelog you will generate. Your bottleneck will likely be in archivelog
  2. For performance work with your SAN engineerings to spread the redo and archive logs out over the physical layer of the SAN which means different RAID Groups. When they present you with 'LUNs' they will need to map this below that level.
  3. Critical: Don't commit after each insert. You batch inserts and bundle up atleast 1000 at a time. The bottleneck will be on the 'commit' long before it is on the insert. You can do this with JDBC. Google 'jdbc batch inserts'

Its more about spreading out the redo/archive , and to a lesser extent datbase files across the SAN to different RAID groups than it is about different DBs. It also depends on how you query and index it. If you don't have anyone who understands #1 and #2 use different DBs on different servers. You will likely still need to code for #3.

On Thu, Jan 29, 2015 at 11:14 AM, Michael Cunningham <> wrote:

> Great info Stephan, thanks.
> BTW, I agree about learning being the best part of the job. That and the
> people we get to collaborate with.
> Michael
> On Thu, Jan 29, 2015 at 4:03 AM, Stefan Koehler <> wrote:
>> Hi Michael,
>> well adding some more required meta information to the text message - the
>> amount of redo is absolutely manageable. Assuming your expected numbers in
>> peak: 2.000 message (per sec) * 968 bytes (redo and undo for 500 bytes
>> per raw message) = 1.936.000 bytes = 1.8 MB per sec
>> So nothing to scare about. You can scale up these numbers with your real
>> volume later on. You can also still use private redo with that amount of
>> redo
>> per transaction (=< 128KB for 64 bit as far as i can remember) and
>> avoiding much more (latch) contention on public redo log buffers.
>> Oracle RAC is a whole different topic as private redo strands do not
>> exist and you have to keep in mind / consider the used SCN generation
>> schemes
>> (nowadays usually broadcast-on-commit) as well. The LGWR argument for
>> Oracle RAC is more related to I/O or CPU bounds of a single process, but
>> you are
>> far away from that with 1.8 MB per sec.
>> All the other main performance things are more related to data design and
>> application implementation. Please also keep in mind that you may need to
>> implement things differently for single instance and RAC.
>> I would suggest that you get the business requirements first and then
>> choose the architecture (RAC or SI) accordingly.
>> P.S.: Yes, Oracle RAC is complex and you need to (re-)learn things, but
>> it is very interesting as well. You should not be afraid of it, learning new
>> things (from others) every day is the best part of our job in my opinion.
>> Best Regards
>> Stefan Koehler
>> Freelance Oracle performance consultant and researcher
>> Homepage:
>> Twitter: _at_OracleSK
>> > Michael Cunningham <> hat am 28. Januar 2015
>> um 18:45 geschrieben:
>> >
>> > Thanks everyone for the input. Here is a little more info in case it
>> helps.
>> >
>> > The text column is varchar2(4000), but the avg size is much less (not
>> sure yet, but let's go with 250). There are no attachments in this system.
>> > I had not thought of LGWR yet so thanks for mentioning that. RAC
>> licensing is not a problem, but the learning curve does concern me a
>> little. I
>> > never thought that RAC could improve performance by scaling the LGWR to
>> different servers.
>> >
>> > If you have more to say please do.
>> >
>> > Thanks a bunch,
>> > Michael
> --
> Michael Cunningham

Received on Tue Feb 10 2015 - 19:46:34 CET

Original text of this message