Home » Infrastructure » Linux » My linux server is slow (Oracle 9.2.0.8)
My linux server is slow [message #351736] Thu, 02 October 2008 15:13 Go to next message
shrinika
Messages: 233
Registered: April 2008
Senior Member
Hello,

I am using Redhat redhat 4 update 4 kernal 2.6. My server is some time suddenly running slow.
The averge cpu load goes up to 50 to 60. (There is only two CPU and usually, the averge load
is between 1 to 2.5) That time, i see, there are several sessions connected.
Normally, we have 150 to 160 session. But when the server is slow, the total number of sessions
goes up to 400. At that time, it is taking long time to connect into the database. Even,
connecting into linux box is taking long time.

Our application server is showing this error message in log file.

ERROR] [2008-10-02 13:09:31] [JDBCExceptionReporter] The data source returned null from
getConnection(user,password).
org.hibernate.exception.GenericJDBCException: Cannot open connection

Here is my investigation and direction i am following.

1. Checking alert log and nothing is unusual.

2. I am running the script for every 30 min to check the hit ratio. Hit ratio is normal. It is more then
99% for library cache, dictionary cache, buffer cache.

Here are the scripts i am running for every 30 min to check
the hit ratio.

SELECT (1 - (v3.VALUE / (v1.VALUE + v2.VALUE))) * 100
INTO v_cache_hit_ratio
  FROM v$sysstat v1,
  v$sysstat v2,
  v$sysstat v3
 WHERE v1.NAME = 'db block gets'
   AND v2.NAME = 'consistent gets'
   AND v3.NAME = 'physical reads';


SELECT SUM(PINS) / (SUM(PINS) + SUM(RELOADS)) * 100
FROM V$LIBRARYCACHE;



SELECT (SUM(GETS)/(SUM(GETS)+SUM(GETMISSES))) * 100
FROM
V$ROWCACHE;



SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE),
       0,1,(a.VALUE + b.VALUE)),2)
FROM   v$sysstat a,
       v$sysstat b
WHERE  a.name = 'sorts (disk)'
AND    b.name = 'sorts (memory)';



3. I am running the script for every 30 min to capture the login session info.

Today i had a problem at 9AM. I compared the below query result between 8.00AM output,
8.30AM output, 9.00AM output, 9.30AM output. I do not see any big difference...

select
a.sid,
a.username,
machine,
program,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 elapsed_time,
sql_text,
rows_processed,
buffer_gets,
disk_reads,
parse_calls,
first_load_time,
fetches,
executions,
loads,
sharable_mem,
persistent_mem,
runtime_mem,
block_gets,
consistent_gets,
physical_reads,
block_changes,
consistent_changes,
sysdate-((4/1440)*60)
from
v$session a,
v$sqlarea b,
v$sess_io c
where
a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.sid = c.sid
;


4. I see the the below error message in my application server.


ERROR] [2008-10-02 13:09:31] [JDBCExceptionReporter] The data source returned 
null from getConnection(user,password).
org.hibernate.exception.GenericJDBCException: 
Cannot open connection


This error message shows that, oracle is not letting to accept any new connection...

Total number of connection all the time is around 100.

Here is my oracle version.
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production


Here is the SGA size.

NAME                      VALUE
-------------------- ----------
Fixed Size               452620
Variable Size        1073741824
Database Buffers      536870912
Redo Buffers            2240512

SQL> 


Any one is phasing this problem??? Any direction to pursue this issue?

I am sorry if this input is not enough to throw your thoughts/ideas. Please do let me
know if you need any other input. I am glad to provide more info.

Regards

[Updated on: Thu, 02 October 2008 15:14]

Report message to a moderator

Re: My linux server is slow [message #351738 is a reply to message #351736] Thu, 02 October 2008 15:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said in your other topic:
http://www.orafaq.com/forum/mv/msg/125783/351701/102589/#msg_351701

Regards
Michel
Re: My linux server is slow [message #351739 is a reply to message #351736] Thu, 02 October 2008 15:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10630
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As Michel already responded to your thread,
stop collecting hit ratios every 30 minutes.
It does mean something, but is not a reliable measure by itself with respect to performance issues.
Statspack reports are a good starting point.

what is the value of your PROCESSES parameter?
Bump it to much higher value and post your results.
Re: My linux server is slow [message #351864 is a reply to message #351739] Fri, 03 October 2008 08:56 Go to previous message
shrinika
Messages: 233
Registered: April 2008
Senior Member
Thanks Michel.

Mahesh, My databases processes is 1000 now. I think, 1000 should be enough. Let me
run the statspack and see any difference. Thanks again.
Previous Topic: Available space on directory.
Next Topic: Server monitoring tools
Goto Forum:
  


Current Time: Fri Nov 28 18:32:18 CST 2014

Total time taken to generate the page: 0.08525 seconds