Wednesday, July 11, 2007

performance problem in a Report

Tune the Report Main Query
Create indexes on columns used in where condition (eliminate full table scan)
set trace on in before report and set trace off in after report

Before Report:
srw.do_sql('alter session set sql_trace=true');

After Report:
srw.do_sql('alter session set sql_trace=false');

Trace file will be generated at location:

select value from v$parameter
where name = 'user_dump_dest';

Get the trace file location path

see execution plans in a trace file, you need to format the
generated trace file with tkprof statement.

Store Code statics in local drive.

No comments: