For EBS Concurrent Requests, often we find the concurrent program is not completing within the expected time. In this case we can investigate the performance problems of the program with a database session trace while the program is running under the Concurrent Processor. Listed below are the steps to configure the trace of the concurrent program.
1) Connect into database as APPS user, execute command bellow.
A) -- Activate Trace from concurrent.
update apps.fnd_concurrent_programs
set enable_trace='Y'
where application_id=30000
and CONCURRENT_PROGRAM_ID = '57181';
commit;
B)- Check Trace Location.
SELECT 'Request id: ' || request_id, 'Trace id: ' || oracle_process_id,
'Trace Flag: ' || req.enable_trace,
'Trace Name:
'
|| dest.VALUE
|| '/'
|| LOWER (dbnm.VALUE)
|| '_ora_'
|| oracle_process_id
|| '.trc',
'Prog. Name: ' || prog.user_concurrent_program_name,
'File Name: ' || execname.execution_file_name
|| execname.subroutine_name,
'Status : '
|| DECODE (phase_code, 'R', 'Running')
|| '-'
|| DECODE (status_code, 'R', 'Normal'),
'SID Serial: ' || ses.SID || ',' || ses.serial#,
'Module : ' || ses.module
FROM fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname
WHERE req.request_id = &REQUEST_ID
AND req.oracle_process_id = proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME = 'user_dump_dest'
AND dbnm.NAME = 'db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
/
C. -- Execute tkprof and send the 3 first queries and plan from all.
tkprof TRACE_FILE.trc TRACE_FILE_TXT.txt sys=no sort='(exeela,fchela)' explain=apps/APPS_PASS
After reviewing the tkprof report we can find the elapsed time for execution and fetch on the sql statements in the SQL ID. Given the sort options, the longest running sql ids will be listed at the top.
We can then run sql tuning advisor on the long running SQL IDs
After running SQL Tunining Advisor, we can accept the profile if one is created.
After accepting the profile, we can run the trace of the program again and run the tkprof of the trace file to compare with the previous results.
If we find there are long waits for the Access Predicate in the given queries for the SQL ID, we can run the SQL Access Advisor.
We can continue the investigation on the running of the request
select fcr.request_id,, s.inst_id, s.session_id, s.module, s.action, s.program
from fnd_concurrent_requests fcr, gv$session s, gv$process p
where p.spid = fcr.oracle_process_id
and s.paddr = p.paddr
and s.session_id = 2157 and s.inst_id=3;
select CONCURRENT_PROGRAM_NAME, ENABLE_TRACE, APPLICATION_ID, CONCURRENT_PROGRAM_ID
FROM FND_CONCURRENT_PROGRAMS
WHERE CONCURRENT_PROGRAM_NAME LIKE '<program name>' OR CONCURRENT_PROGRAM_ID = '57181 ';
checking on MTL base table index status
SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, CLUSTERING_FACTOR, DEGREE
FROM DBA_INDEXES
WHERE OWNER = 'INV' AND INDEX_NAME='MTL_OBJECT_GENEALOGY_N2';
SELECT DT.OWNER, DT.TABLE_NAME, DT.NUM_ROWS, DR.LAST_ANALYZED, DS.BYTES/1024/1024, DS.BLOCKS
FROM DBA_SEGMENTS DS, DBA_TABLES DT
WHERE DT.TABLE_NAME = DS.SEGMENT_NAME
AND DT.OWNER = DS.ONWER
AND DT.TABLE_NAME='&TABLE_NAME'
AND DT.OWNER = '&TABLE_OWNER';
Comments
Post a Comment