| Slowness Problem when logging in by tool Oracle sql developer [message #386293] |
Fri, 13 February 2009 08:10  |
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
|
Senior Member |
|
|
Hi,
I am using Oracle 10g 10.2.0.3.0 on linux 64 bit, sometimes when user try to login in tool Oracle sql developer , the database gets slow and EM show the Network Wait. When i am querying following query
select sid, EVENT, TOTAL_WAITS, TIME_WAITED, WAIT_CLASS
from V$SESSION_EVENT
where WAIT_CLASS != 'Idle'
order by TIME_WAITED;
following are some top events when i am ordering by Time waited in descending
SID EVENTS TOTAL_WAITS TIME_WAITED WAIT_CLASS
496 log file parallel write 497452 471301 System I/O
495 control file parallel write908680 297267 System I/O
472 log file sync 67977 40318 Commit
473 log file sync 80276 39831 Commit
464 log file sync 6870 7226 Commit
471 SQL*Net break/reset to client 159928 4593 Application
492 os thread startup 1585 4065 Concurrency
My question is that top two events are oracle checkpoint sid AND oracle LGWR sid respectively, do i need to tune these two or System_event is not the actual table to find out about this network problem.
|
|
|
|
| Re: Slowness Problem when logging in by tool Oracle sql developer [message #392974 is a reply to message #386293] |
Thu, 19 March 2009 19:53  |
oraxperts
Messages: 5
Registered: March 2009
Location: Melbourne
|
Junior Member |
|
|
'Log File parallel write' and 'log file sync' event are most of the time, symptom of excessive commits at the database level. It would be worthy effort to see why there are lot of commits happening in the database. May be some program is commiting in a loop, where as a batch commit can be done.
The other thing you can do is to improve the IO through put on the system so that LGWR will write the redo blocks faster.
I would also suggest you to know the overall response profile of your database like where the databse is spending more time. There is a free beta trace file analyzer that you can use at
http://www.oraxperts.com
website. You would need to trace the SQL taht is causing the problem or simply trace the database with trace event 10046 for a short time (2 to 5 min) at peak hours.
Upload the trace file to the Online 10046 trace file analyzer tool at http://www.oraxperts.com. This will give you the response profile for the database.
You can access the trace file analyzer directly at :
http://www.oraxperts.com/oratools/traceanalyzer.html
Cheeers
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
