Find consuming sql from process id

Find consuming sql from process id

Get cpu consuming PID from top command and then execute below script to find relevant SQL statements along with SQL ID & Username

prompt "Please Enter The UNIX Process ID"

set lines 200

set long 20000

set pages 0

select

s.username su, sa.SQL_ID, sa.sql_fulltext

from v$process p,

v$session s,

v$sqlarea sa

where p.addr=s.paddr

and s.username is not null

and s.sql_address=sa.address(+)

and s.sql_hash_value=sa.hash_value(+)

and spid=&SPID;

for eg,

[oracle@igrexandc003d02 ~]$ top

top - 09:14:40 up 86 days, 10 min,  3 users,  load average: 13.56, 13.70, 14.04

Tasks: 818 total,  14 running, 804 sleeping,   0 stopped,   0 zombie

Cpu(s): 56.4%us,  0.9%sy,  0.0%ni, 42.5%id,  0.0%wa,  0.0%hi,  0.2%si,  0.0%st

Mem:  148704036k total, 113554696k used, 35149340k free,  1573068k buffers

Swap: 25165816k total,     3712k used, 25162104k free, 53273236k cached

   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

 21899 oracle    20   0 32.4g  64m  32m R 99.7  0.0   1873:07 oracle

 21903 oracle    20   0 32.4g  63m  32m R 99.7  0.0   1826:24 oracle

 21905 oracle    20   0 32.3g  64m  32m R 99.7  0.0   1654:55 oracle

 21915 oracle    20   0 32.3g  63m  31m R 99.7  0.0   1826:48 oracle

[oracle@igrexandc003d02 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 28 09:14:43 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> prompt "Please Enter The UNIX Process ID"

"Please Enter The UNIX Process ID"

SQL> set lines 200

set long 20000

SQL> SQL> set pages 0

SQL> select

  2  s.username su, sa.SQL_ID, sa.sql_fulltext

  3  from v$process p,

  4  v$session s,

  5  v$sqlarea sa

  6  where p.addr=s.paddr

and s.username is not null

  7    8  and s.sql_address=sa.address(+)

  9  and s.sql_hash_value=sa.hash_value(+)

 10  and spid=&SPID;

Enter value for spid: 21899

old  10: and spid=&SPID

new  10: and spid=21899

OBI_RO                         byk85pccx0ydb

                                             select T454950.MASTER_VALUE_CHAR as c1,

                                                  count(distinct T436164.CHANGE_ISSUE_NUM) as c2

                                             from

                                                  WC_PRODUCTIVITY_PROJ_D T436164 /* Dim_WC_PRODUCTIVITY_PROJ_D */ ,

                                                  WC_PRODUCTIVITY_PROJ_SNAP_D T454994 /* Dim_WC_PRODUCTIVITY_PROJ_SNAP_D */ ,

                                                  WC_PRODUCTIVITY_PROJ_CODE_D T455950 /* Dim_WC_PRODUCTIVITY_PROJ_CODE_D_Fina

                                             ncial_Unit */ ,

                                                  WC_PRODUCTIVITY_PROJ_NOTE_F T454975 /* Fact_WC_PRODUCTIVITY_PROJ_NOTE_F */

                                             ,

                                                  WC_PRODUCTIVITY_PROJ_CODE_D T454950 /* Dim_WC_PRODUCTIVITY_PROJ_CODE_D_Note

                                              */ ,

                                                  W_PROD_CAT_DH T441548 /* Dim_W_PROD_CAT_DH_Prod_Proj */

                                             where  ( T436164.CHANGE_ISSUE_NUM = T454975.CHANGE_ISSUE_NUM and T436164.PROD_CA

                                             T_WID = T441548.ROW_WID and T436164.SNAPSHOT_DT_WID = T454994.SNAPSHOT_DT_WID an

                                             d T436164.FINANCIAL_UNIT_WID = T455950.ROW_WID and T436164.SAVINGS_TYPE = 'Hard

                                             Savings P&L' and T436164.VARIANCE_TYPE = 'Productivity' and T441548.L6ANC_PRODCA

                                             T_NAME = 'Valves & Tube Fabrication' and T441548.L7ANC_PRODCAT_NAME = 'Valves &

                                             Tube Fabrication' and T441548.HIERARCHY_CODE = 'AUTO_CUSTOM_CATEGORY1' and T4549

                                             50.ROW_WID = T454975.NOTE_TYPE_WID and T454994.SNAPSHOT_NAME = '2013-05-27 (Dail

                                             y Refresh)' and T455950.GROUP_NAME = 'Asia-Pac' and T455950.MASTER_CODE <> 'ZZ -

                                              Hussmann' and (T436164.PROJECT_TYPE in ('Material Other / In bound Logistics',

                                             'Material Price', 'Material VAVE - external (Supplier driven)', 'Material VAVE -

                                              internal (IR driven)')) and T454975.CREATED_ON_DT >= TO_DATE('2012-05-07 00:00:

                                             00' , 'YYYY-MM-DD HH24:MI:SS') )

                                             group by T454950.MASTER_VALUE_CHAR

                                             order by c1

SQL>