Home » RDBMS Server » Server Administration » ADDM finding (10.2.0.1)
ADDM finding [message #290021] Thu, 27 December 2007 03:07 Go to next message
amit_p_dixit
Messages: 31
Registered: November 2006
Location: Mumbai
Member

Dear all,

I am getting the following finding in ADDM Report,

**************************************************************
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.

RECOMMENDATION 1: Host Configuration, 9.1% benefit (113 seconds)
ACTION: Investigate the possibility of improving the performance of I/O
to the online redo log files.
RATIONALE: The average size of writes to the online redo log files was
59 K and the average time per write was 3 milliseconds.

SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Commit" was consuming significant database time.
(9.1% impact [113 seconds])
*************************************************************

Does this means i have to increase the size of redo log files..Pl let me know...

Regards,
Amit
Re: ADDM finding [message #290028 is a reply to message #290021] Thu, 27 December 2007 03:31 Go to previous messageGo to next message
halfydba
Messages: 20
Registered: November 2007
Location: Australia
Junior Member
"log fuile sync" waits are caused by
1) Committing too often
2) log buffer too big (trying to write too much)
3) poor i/o (you have your online redo and datafiles and archivelogs on the same disk)

I would think your problem is related to the 3rd one. I have seen a lot of setups where I/O is not balanced and you end up having a lot of these waits.


And you should try and have your redo logs switching every 20 minutes as a general rule of thumb. So if you are switching faster than that, sure, make them bigger.
Re: ADDM finding [message #290059 is a reply to message #290028] Thu, 27 December 2007 05:07 Go to previous messageGo to next message
amit_p_dixit
Messages: 31
Registered: November 2006
Location: Mumbai
Member

Thanx... but i think the cause is pt no
2. log buffer too big (trying to write too much)

as mainly select statements are fired on this database and all the data fiels , redo and archive are on different location...
so, pt no 1 & 3 are ruled out..

can u pls tell me how i set the redo log buffer size so it held 20 mins of data in it..


Amit
Re: ADDM finding [message #290066 is a reply to message #290059] Thu, 27 December 2007 05:19 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How many commits per second?
How many log switch per hour?
What is the io time on log file system?

You don't answer these questions but you know this is not points 1 and 3 but point 2. How do you know this?

Quote:

mainly select statements are fired on this database

In this case there could not be any log switch and log file sync event. You are out on your workload.

Regards
Michel


Previous Topic: to kill the session
Next Topic: Dedicated server VS Shared Server
Goto Forum:
  


Current Time: Thu Dec 08 08:20:32 CST 2016

Total time taken to generate the page: 0.22607 seconds