| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: too high load on Linux
Hi Andras,
Firstly, welcome to the list, and your English is fine!!
For a good session detail run the following query:
------Sessions.sql-------
select s.sid sid,
s.serial# serial#,
s.lockwait lockwait,
0 blkby,
0 blkcnt,
s.username session_user,
s.program program,
s.osuser osuser,
s.status status,
s.server server,
s.command cmd_code,
s.terminal terminal,
s.type session_type,
a.name command,
p.pid pid,
p.spid spid,
p.username process_user,
i.block_gets + i.consistent_gets logrds,
i.physical_reads phyrds,
i.block_changes + i.consistent_changes writes
from v$session s,
v$process p,
v$sess_io i,
audit_actions a
where s.paddr = p.addr
s.serial#
To find out the currently executing SQL for this session, insert the SID shown in the previous query for the offending user in to the following statement, this will show you all of the SQL that the user currently has in the SQLArea, as well as showing you their currently executing cursor which is shown with an asterix(*) in the EXECCUR column:
-----SessionSQL.sql------
select s.sid sid,
c.sql_text sql_text,
c.address address,
c.hash_value,
substrb(' ',1,2) execcur
from v$open_cursor c,
v$session s
where c.saddr = s.saddr
c.sql_text sql_text,
c.address address,
c.hash_value hash_value,
substrb('* ',1,2) execcur
from v$open_cursor c,
v$session s
where c.saddr = s.saddr
You can trace the user, by using the DBMS_SYSTEM package. Again, take a note of the offending users SID, and SERIAL#, and use the following syntax:
exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,TRUE|FALSE);
So assuming the SID is 9, and the serial is 224:
exec sys.dbms_system.set_sql_trace_in_session(9,224,TRUE);
And to stop the trace:
exec sys.dbms_system.set_sql_trace_in_session(9,224,FALSE);
Any trace files generated will be in your user_dump_destination (wherever that is specified in your initSID.ora file).
I hope this helps,
Regards
Mark
-----Original Message-----
Sent: Tuesday, September 11, 2001 10:45
To: Multiple recipients of list ORACLE-L
Hello!
I'm new in this list and I sorry for easy and stupid questions and my English. :)
I have a 8.1.7 database on Debian 2.2 and the oracle session use nearly 100 percent of CPU. I listed v$session view and noticed one session do that! But how can I get more information for this session ? How can I see what this session do ? Any trace log ?
Please help me!
Best regards:
András, Czoch
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrew Cook INET: cook_at_bkik.hu Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Tue Sep 11 2001 - 05:10:10 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: mark_at_cool-tools.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |