Monday, March 29, 2010

Query Being executed by Concurrent Program

Suppose you are running a program and it is taking too much time. Concurrent program does not have 'enable trace' option and you want to see the current sql statement. You may use following simple sql. This statement will give you only first 1000 characters of the statement. i think it can give you enough hint about the possible statement needing some tuning.


SELECT vs.sid,
vs.serial#,
vs.username,
optimizer_mode,
hash_value,
address,
sql_fulltext
/* sql_fulltext column is availale in 10g. for 9i, you may use sql_text.
sql_text column gives only 1000 characters of the query.
sql_fulltext probably gives full query. i have not verified it.
*/

FROM v$sqlarea sa
, apps.fnd_concurrent_requests fcr
, v$process vp
, v$session vs
WHERE fcr.request_id = :Concurrent_request_id
AND fcr.oracle_process_id = vp.spid
AND vs.paddr = vp.addr
AND vs.sql_hash_value = sa.hash_value
AND vs.sql_address = sa.address
AND vs.username IS NOT NULL

No comments:

Post a Comment