[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2901
  • Last Modified:

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
0
milanushka
Asked:
milanushka
  • 3
  • 2
2 Solutions
 
johnsoneSenior Oracle DBACommented:
To get text by sid:

select sql_text
from v$session a,
      v$sqltext b
where a.sql_hash_value = b.hash_value and
     a.sid = <sid>
order by piece;

To get text by pid:

select sql_text
from v$process a,
     v$session b,
     v$sql_text c
where a.addr = b.paddr and
     b.sql_hash_value = c.hash_value and
    a.spid = <pid>
order by piece;
0
 
milanushkaAuthor Commented:
Thank you very much.
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.executions,0,1,a.executions)) reads_per_execution,
               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
0
 
sathyagiriCommented:
SELECT *
FROM   (SELECT Substr(a.sql_text,1,100) sql_text,
               Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) 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;
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sathyagiriCommented:
Use this
SELECT *
FROM   (SELECT Substr(a.sql_text,1,100) sql_text,
               Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) 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 c WHERE a.hash_value =  b.sql_hash_value and c.addr = b.paddr
        ORDER BY 2 DESC)
WHERE  rownum <= 100
/
0
 
milanushkaAuthor Commented:
Thank you very much!
0
 
sathyagiriCommented:
Please remember  to accept answer
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now