Tuning:Finding SQL_TEXT based on the Concurrent Request

Finding SQL_TEXT(Query) based on the Concurrent Request

STEP1:

SQL> select  REQUEST_ID,ORACLE_SESSION_ID,ORACLE_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=363254;

REQUEST_ID ORACLE_SESSION_ID ORACLE_PROCESS_ID
-----------------------------------------------
363254     1135502           22933


STEP2:

SQL> select SPID,PID from v$process where spid=22933;

SPID                                        PID
------------------------------------ ----------
22933                                        70


STEP3:

SQL> SELECT b.sid,a.spid, b.sql_id FROM v$process a, v$session b WHERE a.addr=b.paddr and a.spid=22933;

SID  SPID  SQL_ID
--------------------
999  22933 39c3gyvun83f9


SQL> select SQL_TEXT,SQL_FULLTEXT from v$sqlarea where sql_id='39c3gyvun83f9';

SQL_TEXT
--------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => :ownname , tabname => :tabname ,
 estimate_percent => :estimate_percent , degree => :degree , method_opt => :meth
od_opt , block_sample => FALSE , partname => :partname , granularity => :granula
rity , stattab => :stattab , statown => :statown,                no_invalidate =
> FALSE ,               cascade => TRUE               ); end;
BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => :ownname , tabname => :tabname
 ,





SQL> select SQL_TEXT,SQL_FULLTEXT from v$sqlarea where sql_id=(select b.sql_id FROM v$process a, v$session b WHERE a.addr=b.paddr and a.spid=(select ORACLE_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=363254));

SQL_TEXT
--------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => :ownname , tabname => :tabname ,
 estimate_percent => :estimate_percent , degree => :degree , method_opt => :meth
od_opt , block_sample => FALSE , partname => :partname , granularity => :granula
rity , stattab => :stattab , statown => :statown,                no_invalidate =
> FALSE ,               cascade => TRUE               ); end;
BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => :ownname , tabname => :tabname
 ,


(OR)

--------------------
Only Enter the REQUEST_ID

select SQL_TEXT,SQL_FULLTEXT from v$sqlarea where sql_id=(select b.sql_id FROM v$process a, v$session b WHERE a.addr=b.paddr and a.spid=(select ORACLE_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=&CONCURRENT_REQUEST_ID));




Enter value for concurrent_request_id: 366200
old   1: select SQL_TEXT,SQL_FULLTEXT from v$sqlarea where sql_id=(select b.sql_id FROM v$process a, v$session b WHERE a.addr=b.paddr and a.spid=(select ORACLE_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=&CONCURRENT_REQUEST_ID))
new   1: select SQL_TEXT,SQL_FULLTEXT from v$sqlarea where sql_id=(select b.sql_id FROM v$process a, v$session b WHERE a.addr=b.paddr and a.spid=(select ORACLE_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=366200))

SQL_TEXT
--------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
SELECT TABLE_NAME , PARTITIONED FROM DBA_TABLES DT WHERE OWNER = UPPER(:B1 ) AND
 ( IOT_TYPE <> 'IOT_OVERFLOW' OR IOT_TYPE IS NULL ) AND TEMPORARY <> 'Y' AND NOT
 EXISTS (SELECT NULL FROM FND_STATS_HIST FSH WHERE DT.OWNER =FSH.SCHEMA_NAME AND
 FSH.REQUEST_ID = :B3 AND FSH.OBJECT_TYPE ='CASCADE' AND FSH.HISTORY_MODE=:B2 AN
D DT.TABLE_NAME = FSH.OBJECT_NAME AND LAST_GATHER_END_TIME IS NOT NULL ) AND NOT
 EXISTS (SELECT NULL FROM FND_EXCLUDE_TABLE_STATS FETS, FND_ORACLE_USERID FOU ,
FND_PRODUCT_INSTALLATIONS FPI WHERE FOU.ORACLE_USERNAME=UPPER(:B1 ) AND FOU.ORAC
LE_ID =FPI.ORACLE_ID AND FPI.APPLICATION_ID = FETS.APPLICATION_ID AND DT.TABLE_N
AME = FETS.TABLE_NAME ) AND NOT EXISTS ( SELECT NULL FROM DBA_TAB_STATISTICS DTS

SQL_TEXT
--------------------------------------------------------------------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
 WHERE DTS.STATTYPE_LOCKED IS NOT NULL AND DTS.TABLE_NAME=DT.TABLE_NAME AND DTS.
OWNER =DT.OWNER ) ORDER BY TABLE_NAME
SELECT TABLE_NAME , PARTITIONED FROM DBA_TABLES DT WHERE OWNER = UPPER(:B1 ) A
ND