0

I have a oracle 11g database. I'm testing in for inserts. The database running in noarchive mode. I have 3 redo log configured, each 2gb.

I'm trying to insert data into test table. At begin it goes fine with 15k ins/second. I make a commit after 200 inserts.

But after about 1.3m inserted records it become really slow, about 1-2k ins/second. As i noticed in resource explorer at this point we have filled all redo logs and so the inserts from this points work slow.

So my question is why it become so slow when it fills redo logs, even if i commit each 200 records. And how this situation can be fixed ( except the turning off logging completely at inserts)

7
  • Redo logs are on any raid configuration? All the redo logs are in the same logical drive? Jun 14, 2012 at 15:44
  • Yes its raid, and yes its same logical drive. Also the disk itself at any test shows 250-300mb/s but at oracle test oracle show that it can only us 50mbs. But that other issue, not sure if it related to this.
    – Aldarund
    Jun 14, 2012 at 17:00
  • Can you tell me what raid? Jun 14, 2012 at 18:09
  • It's raid 0 ...
    – Aldarund
    Jun 14, 2012 at 18:29
  • What are the wait events when the inserts are going quickly and when the inserts are going slowly? Are you sure that the redo logs really are the bottleneck? How quickly are you generating redo? Jun 15, 2012 at 14:22

1 Answer 1

1

Redo logs are written in sequential manner. The data files are written in random manner.

Each hard disk drive works best when writting sequentially and at this time it may reach full bandwidth (say 100 MB/s), because then it does not need to seek (re-position its magnetic head to another track and wait for the track to revolve to the proper place). When it needs to seek, a single HDD may become limited to, say, 200 seeks per second. So if you write 8 kB and then seek to another place, you would get from the same drive 1.56 MB/s (this is 200*8). In both cases drive would be 100% busy, but with obvious difference of throughput (200 vs 1.56). For RAID0 multiple drives statistically provide, say, n*200 seeks per second.

This does not pertain to SSD, in that case seek is almost immediate.

For the initial period, your data files are not written too much, because all the inserts go into the database cache. Redo log is always written immediately, without (much) buffering, it is fast, it is sequential. There is nothing wrong with redo log. After some time, you simply run out of the free cache and the db writer tries to free some cache by writing to the datafiles. If you would insert into simply one table in one tablespace, the datafile would be probably written sequentially or semi-sequentially.

So why the random operation? Well probably you have something extra in the database, such as: indexes, partitions, audit, other tables, etc. All these contribute to randomization.

It would be beneficial to separate every sequential operation to happen on a different set of physical hard drives, and to put all the random operation yet another set of physical drives. Otherwise sequential performance would suffer terribly. In your case, it is not ok, performance wise, to have redo files on the same physical hard drives as data files.

For any more information, it would be best to examine Automatic Workload Repository (AWR) snapshots (these are simple text reports).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .