Re: Log file I/O throughput

billjohnson <member45480_at_dbforums.com> wrote in message news:<3516938.1066939108_at_dbforums.com>...
> Ok - I read through the replies and there seems to be a fair amount of
> discussion regarding hardware. I have heard the same song and dance
> from our UNIX Admin team that the Disk I/O is raid-1, HP Disk, dedicated
> drives....for our redo logs. We are seeing 30% of the DB time in a
> statspack report going to "log file sync" wait. It just jumped through
> the roof from 5% about 1 week ago. I need to find some other
> measurement that will allow me to confirm whether this is a hardware
> issue or a program commit/update issue. When looking at the stats pack
> report, the "user commits" line shows the following:
>
> Statistic Total per Second per
> Trans
>
> --------------------------------- ---------------- ------------ ------------
>
> user commits 92,676 25.7
> 1.0

There is a stats summary at the top of the statspack. This includes per second and per transaction statistics. You might want to check to see if your per transaction logical reads, physical reads and redo size are increasing over time.

Also check the avg time for "log sync wait".

It's also always a good idea to review the Top-SQL. See if any particular queries are having a particularly heavy impact on the system.

25 commits per second isn't all that much really. If your app can't keep up then I would guess that you've got poorly tuned disks for your redo logs or your (update) transaction size is growing.

This is of course just a stab in the dark.

>
>
>
> This number is no different in average commits per second than then the
> system was only spending 5% of the overall time in log file sync wait.
> I need some additional direction on nailing down the real source of the
> problem. This is a 16-way RP-8400 HP-UX 11.i environment running SAP
> 4.0B on an Oracle 8.1.7.3 instance. We have about 5,000 users on this
> system. Any ideas? I am a DBA - not a UNIX Admin.
Received on Thu Oct 23 2003 - 23:39:35 CDT