| performance prob. [message #59040] |
Fri, 24 October 2003 09:49  |
Terminator.
Messages: 1
Registered: October 2003
|
Junior Member |
|
|
Hi,
can someone help me, Im in trouble. I've a server on which i've 4 different database instances. few days back server was working very smooth but recently the cpu utilization of the server has reached to 100 and no idle time has been shown by TOP command. I've observed one thing that there are 4 different processes running on 2 instances each are hogging the CPU and generally these sessions goes on and on for 2-3 days very few times i've seen these sessions inactive. But for most of the time they remain active. The query that has been run by these 4 processes are referring to same tables (select query is run by these sessions.) and v$session_wait shows that these processes are waiting for "latch free" of type "cache buffer chains". How would i troubleshoot this.
Thanks a lot for all your valuable comments.
Terminator.
|
|
|
|
| Re: performance prob. [message #59041 is a reply to message #59040] |
Fri, 24 October 2003 13:04  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Looks like your processes are waiting for Buffer cache LRU latches.
What is the value of your DB_BLOCK_LRU_LATCHES ?
Also do this
SELECT CHILD#, SLEEPS / GETS RATIO
FROM V$LATCH_CHILDREN
WHERE NAME = 'cache buffers lru chain';
but no amount of latches are going to help you if your SQL is not tuned well. So I would do a TKPROF on those processes and analyze its execution plan,statistics etc to figure out why they are consuming 100% of the CPU.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
