How to tune/trace sql statements ?

Trace Commands

alter system set timed_statistics=true; -- Enable trace
alter session set sql_trace=true;
alter session set tracefile_identifier = testtrace;

run sql query
eg:select * from emp;--statements

alter session set sql_trace=false; -- Disable trace

find trace file name from current session :

select
u_dump.value || '/' ||
db_name.value || '_ora_' ||
v$process.spid ||
nvl2(v$process.traceid, '_' || v$process.traceid, null )
|| '.trc' "Trace File"
from
v$parameter u_dump
cross join v$parameter db_name
cross join v$process
join v$session
on v$process.addr = v$session.paddr
where
u_dump.name = 'user_dump_dest' and
db_name.name = 'db_name' and
v$session.audsid=sys_context('userenv','sessionid');

tkprof commands :

tkprof testtrace.trc testtrace.trcprf EXPLAIN=apps/apps sys=n

tkprof testtrace.trc testtrace.prf EXPLAIN=apps/apps sys=n

tkprof testtrace.trc testtrace.prf EXPLAIN=apps/apps sys=n sort=execpu

tkprof testtrace.trc testtrace.prf EXPLAIN=apps/apps sys=n

After getting .trc file we change into readable format by using following command :

tkprof file_name_time_stamp.trc file_name_time_stamp.prf sort=(PRSDSK,EXEDSK,FCHDSK,EXECPU,FCHCPU)

Note : We can read by many options , above is one option.

RETCODE & ERRBUFF

RETCODE & ERRBUFF

Hi ALL,
As we all know there are two mandatory parameters that need to be pased for all the procedures called
1.ERRBUFF
2.RETCODE..
They are very useful parameters if used properly..like setting the status of the conucrrent program and putting the log messages...
i think it is better we make some rule like we make the program end in Error when ever there is a unidentified exception occurs based on your business rule...

define ERRBUFF as the first parameter and Retcode as the second one...Dont forget to specify the out variable type in it...

CREATE PROCEDURE SAMPLE_PROC (ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
p_1 varchar2)

The retcode has three values returned by the concurrent manager
0--Success
1--warning
2--Error

we can set our concurrent program to any of the three status by using these parameters...
for example
Begin
.....
Exception
when others then
fnd_File.put_line(fnd_file.log,'Unhandled exception occurred in package.procedure'||SQLERRM);
retcode='2';
end;
Even you can use fnd_concurrent.set_completion_Status to send the concurrent program to more status than success,error and warning.....

How to stop the PO Receipt Traveler report for specific organization using Form personalization:

Stop Receipt Traveler – FLG Form Personalization

FORM NAME: RCVRCERC

Trigger Event : WHEN-NEW-BLOCK-INSTANCE

Condition : fnd_profile.value(‘MFG_ORGANIZATION_ID’)=306

Target object: PO_STARTUP_VALUES.PRINT_TRAVELLER_VALUE

Property Name: Value

Value: N

Note : Here if flag value is reset to 'N' then Receipt Traveler Report program never call .