| blocking problem [message #128230] |
Fri, 15 July 2005 17:55  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
On 1 of my databases I start getting a blocking issue.
For a long time I use the following simple script:
SELECT sid, serial#, blocking_session_status, blocking_session
FROM v$session
WHERE blocking_session IS NOT NULL;
The script shows that I have a blocking and the blocking process is "oracle@database_name (LGWR)".
How can I find out how often are the flushes for the redo log?
How to determine the size of the _log_io_size - the x$ view doen't help...
In my stat pack I do not have any LGWR Latching...
but I do have quite a bit log wait_for_sync.
Please, advise how to handle this situation.
Thanks in advance.
|
|
|
|
| Re: blocking problem [message #128267 is a reply to message #128230] |
Sat, 16 July 2005 11:35  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I am not the most experienced in tuning specific redo log issues, more often this is handled on the administrators forum. But, what is the size of your redo log files? And how many redo log groups do you have? My guess is you need to increase both. Make sure archiving is keeping up with redo.
|
|
|
|
| Re: blocking problem [message #128448 is a reply to message #128267] |
Mon, 18 July 2005 11:38 |
ziggy
Messages: 27
Registered: July 2005
Location: Moscow, Russia
|
Junior Member |
|
|
Are you sure that this blocking is a problem?
Of course, checkpoints and other log buffer flushes
occurs soon or later.
Try to use statspack or v$system_event and look on time waiting for events like log file sync, log free space and so on.
If waiting time is significant see alert log and detect how frequently log switch occurs (usually one time in 30 min).
If you don't find anything like this - don't worry.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
