Inactive Session/Application Slow
We are running an application on Oracle 8.1.7. I have compliants from the application users about the application being slow sometimes.When I monitor the DB - the DB instance everything looks OK. I am unable to find out the exactly cause of the slowness.
I come across some inactive sessions. When I say Inactive session - these are the session that show up in v$session view. This application is running a process in 32 parallel and out of those few sessions are getting INACTIVE and the process getting slow.
The application is simple CLIENT - SERVER based. No Application server (connection pool) is there in the middle.
What might be causing the inactive sessions?
Thanks a lot in advance.
What kind of monitoring have you done to investigate the user's claim of "slowness"? Have you traced one of their sessions, or are you just looking at instance level stats?
My application was running ok in past. Recently it started getting slow. I monitored the followings in my DB
1) SGA status: Fine
2) Memory: Fine 99% hit rate
3) Checkpoint occurance
4) Rollback contention
5) Session waits etc
My top 5 wait events are
1) db_file_sequential_read 4057992
2) buffer_busy_waits 475055
3) SQL* net more data to client 305261
4) latch free 124169
5) db_file_parallel_write 111110
And most importantly I saw in v$session few sessions are becoming inactive. I am running a process 32 sessions wide but 5 -6 sessions are getting INACTIVE.
I checked v$session_waits and see that they are waiting for 1) db_file_sequential_read and 2) Sql* net more from client
A SMALL CHANGE FOUND: recently client added 8 more CPUs in the box, earlier it was 50.
but they did not update db_block_lru_latch form 100 to 116
and cpu_count 50 to 58
DOES THAT MAKE ANY DIFF IN PERFORMANCE as mentioned above (waits)
Click Here to Expand Forum to Full Width