-
I usually run top on my sun unix box to see whats running and how much load there is on the box. I find manytimes i get a process in oraclet or oraclep which has taken 15minutes of cpu and a cpu load of 45%. How to i figure why what that process is doing and which user is running that process in oracle.?
running:
ORacle 8i with 11i apps. unix
thanks
Jigar
-
select s.sid, s.serial#, p.spid server_pid, s.username, s.program, s.server, s.status
from v$session s, v$process p
where p.addr = s.paddr
order by to_number(p.spid)
In the above query, "server_pid" is the process id from ps -ef
Jeff Hunter
-
Thanks but what i need to know is what is that process doing and is it a runaway process or dead process? I would like to look at the sql of that process as well if possible
thanks
-
select s.username, s.terminal, s.program, sa.sql_text
from v$session s, v$sql_area sa, v$process p
where s.paddr = s.addr
and a.sql_address = sa.address
Jeff Hunter
-
I think you meant V$sqlarea instead of V$sql_area
-
Jeff i dont think your sql is correct
select s.username, s.terminal, s.program, sa.sql_text
from v$session s, v$sql_area sa, v$process p
where s.paddr = s.sql_address
and sa.address = sa.address
i modified this sql to run but i freezes up on me
-
Try this:
rem this is ps_view.sql
col username format a15
col osuer format a10
col program format a20
set verify off
select a.username, a.osuser, a.program, spid, sid, a.serial#
from v$session a, v$process b
where a.paddr = b.addr
and spid = '&pid';
rem this is ps_sql.sql
set verify off
column username format a15
column sql_text format a60
undefine sid
undefine serial#
accept sid prompt 'sid: '
accept serial prompt 'serial#: '
select 'SQL Currently Executing: '
from dual;
select b.username, a.sql_text
from v$sql a, v$session b
where b.sql_address = a.address
and b.sql_hash_value = a. hash_value
and b.sid = &sid
and b.serial# ='&serial';
select 'Open Cursors: '
from dual;
select b.username 'USERNAME', a.sql_text 'SQL_TEXT'
from v$open_cursor a, v$session b
where b.sql_address =a.address
and b.sql_hash_value = a. hash_value
and b.sid = &sid
and b.serial# ='&serial';
Note: pid comes from top command.
Any problem, let me know.
-
Run all this together? in a shell script? or sqlplus?
Jigar
-
hello,
i didnt get an anwer for my question, shall i just take the script you gave me and run it all together.??
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|