[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2769
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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