COPY FROM apps/pwd@inst1 TO apps/pwd@inst2 INSERT xx.xxamg_ar_invoice_interface_all USING SELECT * FROM xx.xxamg_ar_invoice_interface_all;
This will not work for 'appsread' user
Monday, August 12, 2013
How to Trace a Concurrent Request And Generate TKPROF File
Enable Tracing For The Concurrent Manager Program
Responsibility: System Administrator
Navigate: Concurrent > Program > Define
Query Concurrent Program
Select the Enable Trace Checkbox
Turn On Tracing
Responsibility: System Administrator
Navigate: Profiles > System
Query Profile Option Concurrent: Allow Debugging
Set profile to Yes
Run Concurrent Program With Tracing Turned On
Logon to the Responsibility that runs the Concurrent Program
In the Submit Request Screen click on Debug Options (B)
Select the Checkbox for SQL Trace
2. Find Trace File Name
Run the following SQL to find out the Raw trace name and location for the concurrent program. The SQL prompts the user for the request id
SELECT
req.request_id
,req.logfile_node_name node
,req.oracle_Process_id
,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name
,phase_code
,status_code
,ses.SID
,ses.serial#
,ses.module
,ses.machine
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 1=1
AND req.request_id = &request --Request ID
/opt/oracle/enable/admin/ENBL1/udump --> trace file location
3. TKPROF Trace File
Once you have obtained the Raw trace file you need to format the file using TKPROF.
$tkprof enbl2_ora_23852_837166_CR1814778.trc /home/vk837166/837166_CR1814778 explain=apps/nobel4pce sort=(exeela,fchela) sys=no
Where: raw_trace_file.trc: Name of trace file
output_file: tkprof out file
explain: This option provides the explain plan for the sql statements
sort: his provides the sort criteria in which all sql statements will be sorted. This will bring the bad sql at the top of the outputfile.
sys=no:Disables sql statements issued by user SYS
Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10? long running queries
$ tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort=’(prsela,exeela,fchela)’ print=10
Enabling FND debug
Set these at the user level
1) FND: Debug Log Level --> set to statement level
2) FND: Debug Log Enabled --> Yes
3) FND: Debug Log Module --> %
4) After reproducing the issue
select * from fnd_log_messages
where user_id =
and timestamp > sysdate-3
order by log_sequence desc
1) FND: Debug Log Level --> set to statement level
2) FND: Debug Log Enabled --> Yes
3) FND: Debug Log Module --> %
4) After reproducing the issue
select * from fnd_log_messages
where user_id =
and timestamp > sysdate-3
order by log_sequence desc
Useful SQL queries (FND)
Query for Profile Options (This will work in read only access too)
SELECT fpot.user_profile_option_name AS "USER_PROFILE_OPTION_NAME",
DECODE (level_id,
'10001', 'Site',
'10002', 'Application',
'10003', 'Responsibility',
'10004', 'User',
'10005', 'Server',
'10007', 'Organization'
) AS "LEVEL",
fpov.profile_option_value AS "Value", fpo.profile_option_name,
SUBSTR (fat.application_name, 1, 20) AS "Application",
(SELECT responsibility_name
FROM fnd_responsibility_tl frt
WHERE frt.responsibility_id = fpov.level_value
AND fpov.level_id = 10003) "Responsibility",
(SELECT user_name
FROM fnd_user fu
WHERE user_id = fpov.level_value
AND fpov.level_id = 10004) "User"
FROM fnd_profile_options fpo,
fnd_profile_options_tl fpot,
fnd_application_tl fat,
fnd_profile_option_values fpov
WHERE fpo.application_id = fat.application_id
AND fpov.application_id = fat.application_id
AND fpov.profile_option_id = fpo.profile_option_id
AND fpo.profile_option_name = fpot.profile_option_name
AND fpov.level_id = NVL (:p_level_id, fpov.level_id)
AND UPPER (fpot.user_profile_option_name) LIKE
NVL (UPPER ('%&User_Profile_Option_Name%'),
UPPER (fpot.user_profile_option_name)
);
------------Calculating time taken to complete execution of the program---------------------------
select user_concurrent_program_name, actual_start_date,actual_completion_date
,trunc(86400*(actual_completion_date - actual_start_date ))-60*(trunc((86400*(actual_completion_date - actual_start_date ))/60)) "Sec"
,trunc((86400*(actual_completion_date - actual_start_date ))/60)-60*(trunc(((86400*(actual_completion_date - actual_start_date ))/60)/60)) "Min"
,trunc(((86400*(actual_completion_date - actual_start_date ))/60)/60)-24*(trunc((((86400*(actual_completion_date - actual_start_date ))/60)/60)/24)) "Hrs"
,r.argument_text,r.request_id,r.parent_request_id
from apps.fnd_concurrent_programs_vl p
,apps.fnd_executables e
,apps.fnd_lookup_values flv
,apps.fnd_concurrent_requests r
,apps.fnd_responsibility_vl resp
,apps.fnd_user fu
where 1=1
and p.user_concurrent_program_name like '%CP%'
and p.executable_id = e.executable_id
and r.requested_by = fu.user_id
and flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
and flv.lookup_code = e.execution_method_code
and r.concurrent_program_id = p.concurrent_program_id
and resp.responsibility_id = r.responsibility_id
and r.requested_start_date between '27-AUG-2013' and sysdate
-- and r.argument1 = 'parameter1'
-- and r.request_id = 7344623
--and r.parent_request_id IN ('7329248','7313636','7312292') --'7314309',
order by r.requested_start_date desc;
-------User Responsibilities----------
select u.user_name, resp.responsibility_name
from apps.fnd_user_resp_groups g,
apps.fnd_responsibility_vl resp
,apps.fnd_user u
where u.user_name = '837166'
and u.user_id = g.user_id
and g.responsibility_id = resp.responsibility_id
order by 2;
-- Query to find request group and application name for a concurrent program
-------------------------------------------------------------------------------
SELECT cpt.user_concurrent_program_name "Concurrent Program Name",
DECODE(rgu.request_unit_type,
'P', 'Program',
'S', 'Set',
rgu.request_unit_type) "Unit Type",
cp.concurrent_program_name "Concurrent Program Short Name",
rg.application_id "Application ID",
rg.request_group_name "Request Group Name",
fat.application_name "Application Name",
fa.application_short_name "Application Short Name",
fa.basepath "Basepath"
FROM fnd_request_groups rg,
fnd_request_group_units rgu,
fnd_concurrent_programs cp,
fnd_concurrent_programs_tl cpt,
fnd_application fa,
fnd_application_tl fat
WHERE rg.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = cp.concurrent_program_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND rg.application_id = fat.application_id
AND fa.application_id = fat.application_id
AND cpt.language = USERENV('LANG')
AND fat.language = USERENV('LANG')
AND cpt.user_concurrent_program_name = 'CP';
-----Getting submitted concurrent requests information---------
select r.request_id,p.user_concurrent_program_name
, e.executable_name, flv.meaning, execution_file_name
,r.requested_start_date,r.actual_start_date,r.actual_completion_date
,fu.user_name
, r.phase_code, r.status_code, r.parent_request_id,
resp.responsibility_name
, r.argument_text, r.argument1, r.argument2, r.argument3,r.argument4,r.argument5,r.argument6,r.argument7,r.argument8,
r.argument9,r.argument10,r.argument11,r.argument12,r.argument13,r.argument14,r.argument15,r.argument16,r.argument17
from apps.fnd_concurrent_programs_vl p
,apps.fnd_executables e
,apps.fnd_lookup_values flv
,apps.fnd_concurrent_requests r
,apps.fnd_responsibility_vl resp
,apps.fnd_user fu
where 1=1
and p.user_concurrent_program_name like 'Statement Generation Program%'
and p.executable_id = e.executable_id
and r.requested_by = fu.user_id
--and r.phase_code ='E'
-- and r.request_id in (7284898,7284934)
--(7284901,7284935)
and flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
and flv.lookup_code = e.execution_method_code
and r.concurrent_program_id = p.concurrent_program_id
and resp.responsibility_id = r.responsibility_id
-- and r.requested_start_date between '21-AUG-2011' and sysdate
--and argument1 like 'APAC%'
--and r.phase_code != 'C'
order by r.requested_start_date desc;
-----to find session ID for a particular concurrent request-----------
SELECT ses.sid,
ses.serial#
FROM v$session ses,
v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = &request_id);
-----Form Functions and responisbilities------
SELECT LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.entry_sequence SEQUENCE,
LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.prompt prompt,
menu_entry.grant_flag grant_flag,
DECODE (menu_entry.sub_menu_id,
NULL, 'FUNCTION',
DECODE (menu_entry.function_id, NULL, 'SUBMENU', 'BOTH')
) TYPE,
menu2.user_menu_name, func2.user_function_name
FROM fnd_menu_entries_vl menu_entry,
fnd_menus_tl menu,
fnd_form_functions_tl func,
fnd_form_functions_tl func2,
fnd_menus_tl menu2
WHERE menu_entry.sub_menu_id = menu.menu_id(+)
AND menu_entry.function_id = func.function_id(+)
AND menu_entry.sub_menu_id = menu2.menu_id(+)
AND menu_entry.function_id = func2.function_id(+)
AND grant_flag = 'Y'
and func2.user_function_name like '%Customer Hold%'
START WITH menu_entry.menu_id =
(SELECT menu_id
FROM fnd_menus_tl menu2
WHERE menu2.user_menu_name = :MENU_NAME)
CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
ORDER SIBLINGS BY menu_entry.entry_sequence;
-----Checking Patch Levels-------
select * from apps.ad_bugs
where bug_number = '4602427';
select * from apps.ad_applied_patches
where patch_name = '4602427';
SELECT fpot.user_profile_option_name AS "USER_PROFILE_OPTION_NAME",
DECODE (level_id,
'10001', 'Site',
'10002', 'Application',
'10003', 'Responsibility',
'10004', 'User',
'10005', 'Server',
'10007', 'Organization'
) AS "LEVEL",
fpov.profile_option_value AS "Value", fpo.profile_option_name,
SUBSTR (fat.application_name, 1, 20) AS "Application",
(SELECT responsibility_name
FROM fnd_responsibility_tl frt
WHERE frt.responsibility_id = fpov.level_value
AND fpov.level_id = 10003) "Responsibility",
(SELECT user_name
FROM fnd_user fu
WHERE user_id = fpov.level_value
AND fpov.level_id = 10004) "User"
FROM fnd_profile_options fpo,
fnd_profile_options_tl fpot,
fnd_application_tl fat,
fnd_profile_option_values fpov
WHERE fpo.application_id = fat.application_id
AND fpov.application_id = fat.application_id
AND fpov.profile_option_id = fpo.profile_option_id
AND fpo.profile_option_name = fpot.profile_option_name
AND fpov.level_id = NVL (:p_level_id, fpov.level_id)
AND UPPER (fpot.user_profile_option_name) LIKE
NVL (UPPER ('%&User_Profile_Option_Name%'),
UPPER (fpot.user_profile_option_name)
);
------------Calculating time taken to complete execution of the program---------------------------
select user_concurrent_program_name, actual_start_date,actual_completion_date
,trunc(86400*(actual_completion_date - actual_start_date ))-60*(trunc((86400*(actual_completion_date - actual_start_date ))/60)) "Sec"
,trunc((86400*(actual_completion_date - actual_start_date ))/60)-60*(trunc(((86400*(actual_completion_date - actual_start_date ))/60)/60)) "Min"
,trunc(((86400*(actual_completion_date - actual_start_date ))/60)/60)-24*(trunc((((86400*(actual_completion_date - actual_start_date ))/60)/60)/24)) "Hrs"
,r.argument_text,r.request_id,r.parent_request_id
from apps.fnd_concurrent_programs_vl p
,apps.fnd_executables e
,apps.fnd_lookup_values flv
,apps.fnd_concurrent_requests r
,apps.fnd_responsibility_vl resp
,apps.fnd_user fu
where 1=1
and p.user_concurrent_program_name like '%CP%'
and p.executable_id = e.executable_id
and r.requested_by = fu.user_id
and flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
and flv.lookup_code = e.execution_method_code
and r.concurrent_program_id = p.concurrent_program_id
and resp.responsibility_id = r.responsibility_id
and r.requested_start_date between '27-AUG-2013' and sysdate
-- and r.argument1 = 'parameter1'
-- and r.request_id = 7344623
--and r.parent_request_id IN ('7329248','7313636','7312292') --'7314309',
order by r.requested_start_date desc;
-------User Responsibilities----------
select u.user_name, resp.responsibility_name
from apps.fnd_user_resp_groups g,
apps.fnd_responsibility_vl resp
,apps.fnd_user u
where u.user_name = '837166'
and u.user_id = g.user_id
and g.responsibility_id = resp.responsibility_id
order by 2;
-- Query to find request group and application name for a concurrent program
-------------------------------------------------------------------------------
SELECT cpt.user_concurrent_program_name "Concurrent Program Name",
DECODE(rgu.request_unit_type,
'P', 'Program',
'S', 'Set',
rgu.request_unit_type) "Unit Type",
cp.concurrent_program_name "Concurrent Program Short Name",
rg.application_id "Application ID",
rg.request_group_name "Request Group Name",
fat.application_name "Application Name",
fa.application_short_name "Application Short Name",
fa.basepath "Basepath"
FROM fnd_request_groups rg,
fnd_request_group_units rgu,
fnd_concurrent_programs cp,
fnd_concurrent_programs_tl cpt,
fnd_application fa,
fnd_application_tl fat
WHERE rg.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = cp.concurrent_program_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND rg.application_id = fat.application_id
AND fa.application_id = fat.application_id
AND cpt.language = USERENV('LANG')
AND fat.language = USERENV('LANG')
AND cpt.user_concurrent_program_name = 'CP';
-----Getting submitted concurrent requests information---------
select r.request_id,p.user_concurrent_program_name
, e.executable_name, flv.meaning, execution_file_name
,r.requested_start_date,r.actual_start_date,r.actual_completion_date
,fu.user_name
, r.phase_code, r.status_code, r.parent_request_id,
resp.responsibility_name
, r.argument_text, r.argument1, r.argument2, r.argument3,r.argument4,r.argument5,r.argument6,r.argument7,r.argument8,
r.argument9,r.argument10,r.argument11,r.argument12,r.argument13,r.argument14,r.argument15,r.argument16,r.argument17
from apps.fnd_concurrent_programs_vl p
,apps.fnd_executables e
,apps.fnd_lookup_values flv
,apps.fnd_concurrent_requests r
,apps.fnd_responsibility_vl resp
,apps.fnd_user fu
where 1=1
and p.user_concurrent_program_name like 'Statement Generation Program%'
and p.executable_id = e.executable_id
and r.requested_by = fu.user_id
--and r.phase_code ='E'
-- and r.request_id in (7284898,7284934)
--(7284901,7284935)
and flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
and flv.lookup_code = e.execution_method_code
and r.concurrent_program_id = p.concurrent_program_id
and resp.responsibility_id = r.responsibility_id
-- and r.requested_start_date between '21-AUG-2011' and sysdate
--and argument1 like 'APAC%'
--and r.phase_code != 'C'
order by r.requested_start_date desc;
-----to find session ID for a particular concurrent request-----------
SELECT ses.sid,
ses.serial#
FROM v$session ses,
v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = &request_id);
-----Form Functions and responisbilities------
SELECT LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.entry_sequence SEQUENCE,
LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.prompt prompt,
menu_entry.grant_flag grant_flag,
DECODE (menu_entry.sub_menu_id,
NULL, 'FUNCTION',
DECODE (menu_entry.function_id, NULL, 'SUBMENU', 'BOTH')
) TYPE,
menu2.user_menu_name, func2.user_function_name
FROM fnd_menu_entries_vl menu_entry,
fnd_menus_tl menu,
fnd_form_functions_tl func,
fnd_form_functions_tl func2,
fnd_menus_tl menu2
WHERE menu_entry.sub_menu_id = menu.menu_id(+)
AND menu_entry.function_id = func.function_id(+)
AND menu_entry.sub_menu_id = menu2.menu_id(+)
AND menu_entry.function_id = func2.function_id(+)
AND grant_flag = 'Y'
and func2.user_function_name like '%Customer Hold%'
START WITH menu_entry.menu_id =
(SELECT menu_id
FROM fnd_menus_tl menu2
WHERE menu2.user_menu_name = :MENU_NAME)
CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
ORDER SIBLINGS BY menu_entry.entry_sequence;
-----Checking Patch Levels-------
select * from apps.ad_bugs
where bug_number = '4602427';
select * from apps.ad_applied_patches
where patch_name = '4602427';
Subscribe to:
Posts (Atom)