Row cache lock tuning 
Author Message
 Row cache lock tuning

Oracle 8.1.7.3.0 on HP-UX 11i, single instance, not OPS

I have an instance running a batch process, it's fairly busy (steady
thirty megabytes per second redo generated), the top wait event is
"row cache lock" wait taking over 50% of the wait time. I've looked
around in the usual places, and found good descriptions of what it is,
but not what to do about it.

There's plenty of spare CPU capacity and disk bandwidth, library cache
hit is 100%, buffer cache 99.7%. Application is Pro*C and using bind
variables everywhere. This should be able to go faster.

Is there any knob to tweak?

--
Andrew Mobbs - http://www.***.com/ ~andrewm/



Sat, 04 Sep 2004 01:50:36 GMT
 Row cache lock tuning

Quote:

> I have an instance running a batch process, it's fairly busy (steady
> thirty megabytes per second redo generated)

30 MB/second?  Busy is a bit of an understatement...  30 Mb/sec = 1.8
gigs/minute = 108 gigs/hour = 2.6 terabytes of redo a day?  That's
pretty impressive - what's your backup strategy like?

Thanks,
Sean



Sat, 04 Sep 2004 02:13:54 GMT
 Row cache lock tuning

Quote:


>> I have an instance running a batch process, it's fairly busy (steady
>> thirty megabytes per second redo generated)

>30 MB/second?  Busy is a bit of an understatement...  30 Mb/sec = 1.8
>gigs/minute = 108 gigs/hour = 2.6 terabytes of redo a day?  That's
>pretty impressive - what's your backup strategy like?

Fortunately, that isn't a problem for me. This isn't a production
instance, I try to stay away from those. :-)

The particular application isn't running all the time, and the others
aren't quite so enthusiastic about generating redo. The shortish run
I made took 1.5 hours and got through 5x30GB log files.  I need to do
a run at least 4 times as long in the next few days, and hope to spend
less than all day doing it.

Seriously though, it does worry me how our customer's DBAs could cope with
backup of some of the redo rates that I see in benchmarks, especially
given the predicted increases in volume. While it's not quite up to
30MB/s yet, it's at least in the same order of magnitude. If anybody's
got experience of backing up instances that generate a few hundred GB
per day redo, I'd be interested to hear.

I imagine the answer involves giving a lot of money to tape-library
vendors. With a load of log groups (say 9), have a 6 drive DLT library,
30 MB/s / 6 drives => 5MB/s per drive - just about plausible I guess
given Quantum claim 6MB/s streaming for DLT8000. That's still 86 tapes
to shuffle through in a day.  ...and then there's data file backup.

--
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/



Sat, 04 Sep 2004 03:57:56 GMT
 Row cache lock tuning

Quote:

> The particular application isn't running all the time, and the others
> aren't quite so enthusiastic about generating redo. The shortish run
> I made took 1.5 hours and got through 5x30GB log files.  I need to do
> a run at least 4 times as long in the next few days, and hope to spend
> less than all day doing it.

Yikes.

Quote:
> Seriously though, it does worry me how our customer's DBAs could cope with
> backup of some of the redo rates that I see in benchmarks, especially
> given the predicted increases in volume. While it's not quite up to
> 30MB/s yet, it's at least in the same order of magnitude. If anybody's
> got experience of backing up instances that generate a few hundred GB
> per day redo, I'd be interested to hear.

Just as an example: probably our biggest "offender" does between 100 and
300 gigs a day.  But we backup four times that amount since we mirror
the archives (at the Oracle level) and take 2 copies of each log to tape
(one copy stays onsite).  The solution is not, how do you say... cheap?
Gotta pay to play.

Quote:
> I imagine the answer involves giving a lot of money to tape-library
> vendors. With a load of log groups (say 9), have a 6 drive DLT library,
> 30 MB/s / 6 drives => 5MB/s per drive - just about plausible I guess
> given Quantum claim 6MB/s streaming for DLT8000. That's still 86 tapes
> to shuffle through in a day.  ...and then there's data file backup.

:)  Sounds like fun.  Except it might be hard to catch up with that redo
stream the day you lose a tape drive.  Redundancy is the datacenter
credo.

Regards,
Sean



Sat, 04 Sep 2004 11:06:17 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Library Cache and Dictionary Cache Tuning - Help

2. Waited Too Long For Row Cache Enqueue Lock

3. waited too long for row cache enqueue lock

4. library cache lock | transaction enqueue lock

5. TUNING THE DATA DICTIONARY CACHE

6. Tuning buffer cache (8.0 vs 8i)

7. Advice on data cache tuning ...

8. Attn all Sybase Tuning Gurus: Large Number of Cache Misses

9. Performance tuning: cannot get %write cache above 85%

10. Page lock to simulate row level lock

11. Row lock automagically being escalated to Table Locks


 
Powered by phpBB® Forum Software