This discussion is archived
11 Replies Latest reply: May 7, 2013 4:16 AM by 1002650 RSS

RAC vs. DB link on availability and performance

1002650 Newbie
Currently Being Moderated
My prod database is having big availability and performance issue, now it's just using single database instance.

One of major job for this database is to output log in a table, it's writing out 2 mill text message/record (255 characters) per business hour, for sure it's using tons of I/O and disk space.

To distribute this task for gaining better availability and performance, I'm wondering, whether setting up RAC or db link can help, and which is better?if using RAC, any configuration option to instruct database server such that for a log table, always use node 2, all other tables always use node 1?

--
Thanks.
John
  • 1. Re: RAC vs. DB link on availability and performance
    damorgan Oracle ACE Director
    Currently Being Moderated
    The chance of RAC being the solution to your specific issue is nill.

    Writing 2M records per hour is nothing. On my laptop I routinely demonstrate writing 500K records per second.

    So the questions I would put to you are:
    1. Why are you doing this?
    2. What are you writing? (post the DML)
    Why so much disk space and I/O? (post a sample row)
    3. Are you configured for direct I/O or async I/O?
    4. What is your storage configuration (manufacturer, model, disk type, etc.)
    5. Assuming a SAN or similar storage how is the write cache configured?
    6. What operating system?
    7. What Oracle database version?
    8. How much of what you are writing is repetitive nonsense? By which I mean if you are writing 1.995M rows of "everything is fine" and a few rows of "here is an issue" then reconsider your methodology.
    9. Are you using table compression?
    10. Are there indexes? How many? Doing what other than creating more I/O?
    11. What is your database block size?
    12. How much redo are you generating per transaction?

    ... you get the idea ... you didn't provide sufficient information for anyone to help you with much of anything other than writing a lot of questions you need to have answers for.

    Hope this helps.

    PS: A DB link, I have no idea how it relates to your issue, but the generic answer is that it will make thing slower.
  • 2. Re: RAC vs. DB link on availability and performance
    onedbguru Pro
    Currently Being Moderated
    The only way to increase performance would be to increase available CPU.. DBLink cannot do that. DBLink also cannot provide availability. if the initial db is down you don't have a dblink...
  • 3. Re: RAC vs. DB link on availability and performance
    Mihael Pro
    Currently Being Moderated
    May be, placing this table with its indexes into separate buffer pool, can give some benefit.
  • 4. Re: RAC vs. DB link on availability and performance
    1002650 Newbie
    Currently Being Moderated
    Yes, you are right - that's what we found out and apply; initially we thought I/O was the bottleneck, and tried some hardware to fix, but eventually we upgraded prod CPU from 4 to 8 then 16 (32 CPU just too expensive).

    I'm very interested to know, what and how made you think CPU is the only way and no other (as least that's what we found so far)

    Thanks.

    John
  • 5. Re: RAC vs. DB link on availability and performance
    1002650 Newbie
    Currently Being Moderated
    Hi,

    How to place table in other buffer pool? what benefit will be?

    Thanks.
    John
  • 6. Re: RAC vs. DB link on availability and performance
    Mihael Pro
    Currently Being Moderated
    You can configure "recycle" pool, for example :
    alter system set db_recycle_cache_size = 100m;
    
    alter table ... storage (buffer_pool recycle);
    alter index ... storage (buffer_pool recycle);
    This can avoid flushing out of other objects from default pool and rereading them from disk.
  • 7. Re: RAC vs. DB link on availability and performance
    1002650 Newbie
    Currently Being Moderated
    Mihael wrote:
    You can configure "recycle" pool, for example :
    alter system set db_recycle_cache_size = 100m;
    
    alter table ... storage (buffer_pool recycle);
    alter index ... storage (buffer_pool recycle);
    This can avoid flushing out of other objects from default pool and rereading them from disk.
    Thank you Mihael - I'll have my DBA give it a shot.
    As per writing to this log table, a commit is followed, would this comment cause slow as well? if changing to a batch commit, with this recycle pool, any cache issue we'll have to consider?

    John
  • 8. Re: RAC vs. DB link on availability and performance
    Mihael Pro
    Currently Being Moderated
    As per writing to this log table, a commit is followed, would this comment cause slow as well? if changing to a batch commit, with this recycle pool, any cache issue we'll have to consider?
    If your application performs "commit" after each inserted record, you will have a huge amount of transactions, it is slow because each "commit" will cause write on disk. Try to group insert. This will not affect cache.
  • 9. Re: RAC vs. DB link on availability and performance
    1002650 Newbie
    Currently Being Moderated
    onedbguru wrote:
    The only way to increase performance would be to increase available CPU.. DBLink cannot do that. DBLink also cannot provide availability. if the initial db is down you don't have a dblink...
    Hello onedbguru

    Yes, you are right - that's what we found out and apply; initially we thought I/O was the bottleneck, and tried some hardware to fix, but eventually we upgraded prod CPU from 4 to 8 then 16 (32 CPU just too expensive).

    I'm very interested to know, what and how made you think CPU is the only way and no other (as least that's what we found so far)

    Thanks.

    John
  • 10. Re: RAC vs. DB link on availability and performance
    onedbguru Pro
    Currently Being Moderated
    Experience.

    Going to a single-node with lots of cpu's can work, but you also stated you wanted availability. High availability could not be accomplished using either dblink or single-node with high cpu count and definitely not using a RAC in VM. You lose the "host" and your HA goes down with it.

    You also did not specify your platform (or version??).

    I do agree with DAMorgan that 2M/hr is really not that much. I have seen rates as high 1TB in 8hr day. (38M/sec if my math is correct). I have also seen rates of 1M rec/sec - on a 2cpu desktop. There are tons of things to consider when needing a high-ingest rate, but the two primary variables are CPU and I/O.
  • 11. Re: RAC vs. DB link on availability and performance
    1002650 Newbie
    Currently Being Moderated
    onedbguru wrote:
    Experience.

    Going to a single-node with lots of cpu's can work, but you also stated you wanted availability. High availability could not be accomplished using either dblink or single-node with high cpu count and definitely not using a RAC in VM. You lose the "host" and your HA goes down with it.

    You also did not specify your platform (or version??).

    I do agree with DAMorgan that 2M/hr is really not that much. I have seen rates as high 1TB in 8hr day. (38M/sec if my math is correct). I have also seen rates of 1M rec/sec - on a 2cpu desktop. There are tons of things to consider when needing a high-ingest rate, but the two primary variables are CPU and I/O.
    Aix box and Tivoli are used, with Oracle v 11.1
    I know 2M/hr is NOT a bit deal on batch processing; however, in a case where all business logic/process done in database, thousands of API/stored procedure executed in a transaction, and every step must be logged, what is more, every writing into log must be committed before next writting..
    This becomes a big problem.
    So I'm trying to see whether the logging can be distributed; as lots of effort has been made on database/sql/stored procedure/hardware side.

    Thanks.
    John

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points