Initialization SQL Statement - Custom

1.Set the profile 'Initialization SQL Statement - Custom' value at user level

The value
BEGIN FND_CTL.FND_SESS_CTL ('','','TRUE','TRUE','LOG','ALTER SESSION SET TRACEFILE_IDENTIFIER=''ALLEN_ZHONG_TEST'' EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''');END;
Another example:
BEGIN FND_CTL.FND_SESS_CTL ('','','TRUE','TRUE','LOG','ALTER SESSION SET EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'''); END;
Note:
a.All quotes used on FND_CTL.FND_SESS_CTL are single quotes. Where it looks like double quotes it is actually two single quotes. After LEVEL 12 it has 3 single quotes. Exact syntax is very important.
b. Set this profile ONLY at the USER level. Do not set to your own USER. Create always a dedicated USER.
c. When using this profile, DO NOT turn trace on the menu, as doing so would actually turn off Event 10046. Turning Event 10046 to any level, turns trace automatically.
d. Event 10046 level 1 is regular trace, level 4 is tracing with bind variables, level 8 is with database waits and level 12 with both, bind variables and database waits

2. Log out and log in the system again, if you setup a wrong value for this profile you will can not log in system by your account, please refer to below doc id in metalink
Doc ID 135389.1

3. where can find this log file, log in DB server by FTP tool.
using below query can find the path. you will find the trc file which contains ALLEN_ZHONG_TEST, you also can refresh the directory and find the latest modification file.
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 = 383798608
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
AND prog.executable_id = execname.executable_id

4. why we need to use this
    mainly tuning and debug. we also have alternate method( can enable form session trace and program trace).
Appendix:
（Profile Option: ‘Initialization SQL Statement – Custom’
Oracle Applications 11i provides a profile option that allows to execute ‘custom’ code at the beginning of every database session. These sessions can be linked to an online Form, a Concurrent Program, or any other piece of code that requires accessing the database.
The most common use of this profile option is to generate detailed raw SQL Trace files including the values of the bind variables used by SQL statements. This profile is also used to report on raw SQL Trace, all database waits, used to determine gaps between elapsed and CPU times.
Values of bind variables are necessary when a bad performing SQL is found on TKPROF, and the Explain Plan shows only zeros on its Rows column. The display of the number of rows in the explain plan from TKPROF, is needed in order to narrow the area in which the SQL statement is retrieving a large volume of data.
For both, values of bind variables and database waits, an RDBMS Event is used (Event 10046). This document describes how to set the profile option ‘Initialization SQL Statement – Custom’ for one USER, in order to generate detailed raw SQL Traces to troubleshoot Apps Performance issues. Use this profile wisely.）

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13975809/viewspace-1795410/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13975809/viewspace-1795410/