Concurrent Tuning Scripts

find os process id
=====================================

select p.spid,s.sid,s.serial#,s.username,s.status,s.last_call_et,p.program,p.terminal,logon_time,module,s.osuser
from V$process p,V$session s
where s.paddr = p.addr and s.status = 'ACTIVE' and s.username not like '%SYS%';

to find locked session
===============================================

select * from v$session where sid IN (select session_id from dba_locks where blocking_others like 'Blocking%');

select * from v$session where blocking_session='NOTNULL';

to find sql hash value,sql id
============================================

select process,sql_hash_value,module,program,event,wait_class,vs.* from v$session vs where type !='BACKGROUND' and sid='259';

to find long running session
============================================

select * from v$session_longops where sid=2781;

to find which sql executing
==============================================

select * from v$sql where sql_id='9rxxntrr9fk0r';

to find what are the sql executed
====================================================

select * from v$sql_plan where sql_hash_value=4002891799;


To find sid from concurrent request
========================================================

select * from fnd_concurrent_requests where request_id='20774172';

select * from v$session where audsid='30762086';  --audsid is oracle session id


to find long Running Concurrent
===========================================================


SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC;

During Patch execution Activity
--------------------------------------------------------
select * from apps.AD_PARALLEL_updates where script_name like 'adobjcmp.sql';

SELECT * FROM APPS.AD_PARALLEL_UPDATE_UNITS WHERE UPDATE_ID=6544 AND STATUS='A'

SELECT count(*) FROM APPS.AD_PARALLEL_UPDATE_UNITS WHERE UPDATE_ID=1454 --AND STATUS='A'

select * from dba_objects where object_id=27505

select count(*) from dba_objects where status='INVALID'


======================================



TKM Project:
=============


Base concurret request find DB access information:
==================================================

 select event,program,blocking_session,row_wait_obj#,row_wait_block#,SQL_HASH_VALUE,vs.* from v$session vs where audsid in (select oracle_session_id
   from fnd_concurrent_requests where request_id in ('2104772','2104771','2104770','2104769'));


Based on above query given:
==========================

 select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
 
 
   SELECT SQL_FULLTEXT FROM V$SQL
   WHERE HASH_VALUE=2323454545;---2323454545---529293362---529293362
 
 select * from dba_objects where object_id='332714';


select address, hash_value from v$sqlarea where sql_id like '8uk2jxy57u6kj';
 
  exec dbms_shared_pool.purge('0000000523894160,2323454545','C');
 
   529293362
   529293362
  select * From v$sess_io where sid in (2329, 2106, 2147);


"SELECT NVL(SUM(prb.balance_value),0) FROM /* IN Assignment for Run RB */
  /* $Header: peinroutes.ldt 120.0.12010000.6 2013/02/07 12:19:07 pthummal noship $ */
        pay_run_balances            prb,
        pay_defined_balances        pdb
  where pdb.balance_type_id    = :U1
    AND pdb.balance_dimension_id = :U2
    AND pdb.defined_balance_id   = prb.defined_balance_id
    AND prb.assignment_action_id = :ASSIGNMENT_ACTION_ID"



 
   SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id =&req_id
AND a.phase_code = 'R';

1843

select * from v$session where sid=1843;


select * From fnd_concurrent_requests where request_id=2100226;



2160 2697
2124 25744
2112 11071
2213 31857

session after killed its showed it status=killed:
================================================

select * From v$session where sid=1961;

  paddr 00000005E8D79FC0

  select * From v$process where addr='00000005E8D79FC0';

 spid :  13707


kill - 9


================================================

Objects locked:

select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id;

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,
       b.osuser, b.machine
  FROM v$locked_object a, gv$session b, dba_objects c
 WHERE b.SID = a.session_id
   AND a.object_id = c.object_id
   AND b.status = 'INACTIVE';

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,
       b.osuser, b.machine
  FROM v$locked_object a, gv$session b, dba_objects c
 WHERE b.SID = a.session_id
   AND a.object_id = c.object_id
   AND b.status = 'ACTIVE';



select event,program,blocking_session,row_wait_obj#,row_wait_block#,SQL_HASH_VALUE,vs.* from v$session vs where audsid in (select oracle_session_id
   from fnd_concurrent_requests where request_id in ('2104202','2104201','2104200','2104199'));