It is right that the "Commit" wait event should lead you to investigate log
buffer and redo logs, but the advice provided could have exactly the
opposite effect and may increase the time spent waiting for commits if your
log buffer is too large and users commit infrequently. Much depends on the
work profile of your instance.


Also, if users commit very frequently (at which point LGWR flushes sessions
redo from log buffer to redo logs), or your redo logs are on slow disks
(maybe RAID 5?) or disks with lots of other activity on them (archive redo
logs perhaps !?), then your database may be spending far too much time
writing to redo log, so delaying commits and roll backs for users in the way
reported.

Not all wait events are necessarily the "fault" of the database. For
instance you might look out for user sessions which upsert/delete large
numbers of rows and which commit at every row, a common mistake in pl/sql
blocks. Try altering procedures/functions to commit every 1000 or more rows
instead - then log writer has to start work much less often.

LGWR writes the contents of log buffer to disk:
1. at every commit
2 when log buffer is one third full or has reached 1Mb used
3. every 3 secs
4. when DBWR performs a checkpoint

As a general rule of thumb I normally set this around 750k for starters
(even on large high throughput dbs), increasing up to 3Mb as necessary after
checking log file sync waits etc. Over 10Mb is usually a typo, and very bad
for performance!



-----Original Message-----
From: "Rüdiger J. Schulz"
[mailto:oracledba-ezmlmshield-x69368490.[Email address protected]
Sent: 29 June 2007 15:26
To: LazyDBA Discussion
Subject: Re: oracle errors

increase log_buffer and / or increase amount of redo-log-groups and-size
hth
Rüdiger J. Schulz
sosys-berlin.de

Schleicher Jeremy D. schrieb:

>I am seeing a ton of these errors in my database, any idea what I need
>to do to fix them?
>
>
>
>Jun 29, 2007 8:56:13 AM Metrics "Database Time Spent Waiting (%)" is at
>92.75452 for event class "Commit" -
>
> Jun 29, 2007 8:53:12 AM Metrics "Database Time Spent Waiting (%)" is at
>9.3135 for event class "Commit" -
>
> Jun 29, 2007 8:29:12 AM Metrics "Database Time Spent Waiting (%)" is at
>56.29599 for event class "Commit" -
>
> Jun 29, 2007 7:57:05 AM Metrics "Database Time Spent Waiting (%)" is at
>5.98784 for event class "Commit" -
>
> Jun 29, 2007 7:50:02 AM Metrics "Database Time Spent Waiting (%)" is at
>70.79859 for event class "Commit" -
>
> Jun 29, 2007 7:48:02 AM Metrics "Database Time Spent Waiting (%)" is at
>43.99604 for event class "Commit" -
>
> Jun 29, 2007 7:02:32 AM Metrics "Database Time Spent Waiting (%)" is at
>69.77716 for event class "Commit" -
>
> Jun 29, 2007 7:01:32 AM Metrics "Database Time Spent Waiting (%)" is at
>38.07206 for event class "Commit" -
>
> Jun 29, 2007 6:42:13 AM Metrics "Database Time Spent Waiting (%)" is at
>81.01243 for event class "Commit" -
>
> Jun 29, 2007 6:40:13 AM Metrics "Database Time Spent Waiting (%)" is at
>37.00963 for event class "Commit"
>
>
>
>
>
>Thanks!
>
>
>
>
>
>Jeremy Schleicher
>
>ERP Database Administrator
>
>Florida State University
>
>
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>To post a dba job: http://jobs.lazydba.com
>To Subscribe : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>
>

--
Rüdiger J. Schulz
Normannenstraße 4
D-14129 Berlin
mobil: +49 (0) 178 / 815 13 36
fax/ab: +49 (0) 30 / 80 40 47 56
eMail: [Email address protected]
im web: sosys-berlin.de





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html





Oracle LazyDBA home page