-
How to find out SQL run by a specific user
Hello All,
We are running 8.1.7.4 db in MTS mode on Windows NT 4.
Sometimes CPU utilization goes to 100% for 3 - 4 minutes and
we find out the session utilizing max CPU by following query:
SELECT SUBSTR (sn.NAME, 1, 30) parameter,
ss.username || '(' || se.SID || ',' || ss.SERIAL# || ')' user_process,
ss.OSUSER, ss.LAST_CALL_ET, se.VALUE, ss.MACHINE
FROM v$session ss, v$sesstat se, v$statname sn
WHERE se.statistic# = sn.statistic#
AND sn.NAME LIKE '%CPU used by this session%'
AND se.SID = ss.SID
ORDER BY sn.NAME, se.VALUE DESC
Now my manager has asked me to find out the queries run by
users utilizing max CPU so that we can tune those queries.
I have tried looking in v$sqlarea by joining ADDRESS colum with
both SQL_ADDRESS and PREV_SQL_ADDR columns of v$session but it
doesn't always give the results.
I would like to know is some other sure shot way of getting the
SQL. Any help in this regard will be highly appreciated.
Best Regards,
Kashif
-
Top 10 cpu consuming session
select a.sid, a.logon_time "log On ", spid, status, substr(a.program,1,15) prog, a.terminal,osuser, value/60/100 value
from v$session a, v$process b, v$sesstat c
where c.statistic#=12 and c.sid=a.sid
and a.paddr = b.addr
and trunc(value/60/100)>0
and rownum<10
order by value desc;
-
Hello Mdash,
Thanx for your reply.
As i have stated in my earlier post, we are able to
find out the top CPU consuming sessions. Now we are
looking to find out the SQL run by these sessions.
We would appreciate any help in this regard.
Thank You,
Kashif
-
I think u can use statspack with snapshot level=6 .
It will give you top sqls & also execution plan on the database.
-
Since you've narrowed it down to the rogue session, try this script from Tom's site called showsql...
Code:
column status format a10
set feedback off
set serveroutput on
select username, sid, serial#, process, status
from v$session
where username is not null
/
column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/
column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped
select username||'('||sid||','||serial#||')' username,
module,
action,
client_info
from v$session
where module||action||client_info is not null;
for more discussion on this check this out.
Cheers...
Tarry
Last edited by Tarry; 09-16-2003 at 04:04 PM.
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
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
|