milanushka
asked on
Find sql text and user by pid and sid
Hi Guys,
Does anyone has a script of how to find most offensive SQL statements by sid and pid?
Thanks,
Mia
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT *
FROM (SELECT Substr(a.sql_text,1,100) sql_text,
Trunc(a.disk_reads/Decode( a.executio ns,0,1,a.e xecutions) ) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address,b.sid,c.spid
FROM v$sqlarea a ,V$SESSION b, V$PROCESS WHERE a.hash_value = b.hash_value and a.addr = b.paddr
ORDER BY 2 DESC)
WHERE rownum <= 100;
FROM (SELECT Substr(a.sql_text,1,100) sql_text,
Trunc(a.disk_reads/Decode(
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address,b.sid,c.spid
FROM v$sqlarea a ,V$SESSION b, V$PROCESS WHERE a.hash_value = b.hash_value and a.addr = b.paddr
ORDER BY 2 DESC)
WHERE rownum <= 100;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much!
Please remember to accept answer
ASKER
I have a scrip that shows most offencive sql. Is there a way to modify that script to show sid or pid
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
SELECT *
FROM (SELECT Substr(a.sql_text,1,100) sql_text,
Trunc(a.disk_reads/Decode(
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= 100;
SET PAGESIZE 14
Thanks again
Mia