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>

Comments

Post a Comment

Popular posts from this blog

Workflow Agent Listener Service WF_DEFERRED + Business Event not processing

-
Workflow Agent Listener Service WF_DEFERRED Will Not Start, Mailer May Be Down To implement the solution, please execute the following steps: 1.  Use this query to identify any remaining items in wf_deferred queue: SQL> select corrid, decode(state, 0, '0 = Ready', 1, '1 = Delayed', 2, '2 = Retained', 3, '3 = Exception', to_char(state)) State, count(*) COUNT from wf_deferred group by corrid, state; 2. Items with the APPS corrid will not dequeue from agent listener via service container.  This runs the deferred listener from the command lin e . Make sure that the listener and workflow service components - including the mailer - are down before you run this . This is not intended to be a substitute for the normal operation of this service container from the OAM console. Use this only during this cleanup operation. One will have to run this script manually: begin wf_log_pkg.wf_debug_flag := TRUE; wf_event...
Read more

Output Post Processor - EBS 12.2.4 Troubleshoot

-
Output Post Processor TroubleShooting Issue 1 Output Post Processor is Down with Actual Process is 0 And Target Process is 1 1. Shutdown Concurrent Managers 2. To ensure concurrent manager down; check there is no FNDLIBR process running. ps -ef | grep FNDLIBR 3. Run adadmin to relink FNDSVC executable. a. Invoke adadmin from command prompt b. Choose option 2 (2. Maintain Applications Files menu) c. Choose option 1 (1. Relink Applications programs ) d. Then type FND When prompted; ( Enter list of products to link (all for all products) [all] : FND ) e. Ensure adrelink is exiting with status 0 4. Start Concurrent Managers 5. Check the Output Post Processor Issue 2 Concurrent Processing  R12 Output Post Processor Service Not Coming Up Reason : If Service Manager for the node is not running.  Possible cause might be service manager definition is missing under Concurrent ->Manager ->Define form. If the Service Manager is not pr...
Read more

How to Diagnose Workflow Notification Mailer Issue

-
Outbound Notification Emails : 1. Check if the notification is present in the recipient/user's Worklist/Notifications page.If it does not exist     then it means that notification itself is not created and need to check the corresponding workflow     status.You may also query the notification from the wf_notifications table SQL> select recipient_role,notification_id,status,mail_status from wf_notifications where recipient_role like '&user_name';    The e-mail notification is sent only if all of following are true.                --Notification status is OPEN or CANCELED                --Notification mail_status is MAIL or INVALID 2. Check Recipient role has a valid e-mail address and notification preference MAIL% SELECT email_address, nvl(WF_PREF.get_pref(name, 'MAILT...
Read more