Tune pl*sql batch
Date: 1996/03/12
Message-ID: <4i3266$buv_at_news.nyc.pipeline.com>#1/1
Running pl*sql batch process against 7.2.3. HP Glance Plus indicates Disk is being 100% utilized. So started looking at other ways of improving performance.
Max IO hapens against Redo logs followed by Rollback Segments. Log switch occurs after 4-5 mins (3 redo logs of 20MB each, ie 4 MB/min - Is this normal ?)
Ckpt happens only on log switch (log_checkpoint_interval too high). Alert file is full of following error :
CHECK POINT NOT COMPLETE. CANNOT ALLOCATE NEXT LOG. Does this mean logs are switching too fast ? What's the problem here ? Redo log size or number of logs or both ?
System stat of REDO LOG SPACE REQUETS keeps on growing at a steady pace. Log buffer size is already near 200k. (Is this ok ? Too high ? Can it be increased further ?) How do I speed up log writer ? (Ckpt already runs as a separate process). Something similar to multiple Dbwr (I have 6 running) can there be multiple lgwr ?
Someone suggested the other day that WRITE REQUESTS should be significantly lower than PHYSICAL WRITES in this news group. I am getting 150,000 and 5,000 (ie write batch size of 30 blocks per write req). Is this right ?
Machine is HP 9000/715. DB size 6 GB. No other users on the system when this batch runs. Cpu is not bottleneck (no table scans - verified using tkprof - except for the initial build of hugh sorted recordset cursor which is processed in this batch).
Any suggestions appreciated, Thanks.
utrankar_at_nyc.pipline.com (Sameer Utrankar) All of us can't be heros. Someone has to sit by the curb and clap as they go by. Received on Tue Mar 12 1996 - 00:00:00 CET