RSS Feed

Method R Discussion Group

I have identified the exact SQL that needs to be tuned, and I am now trying to tune it. Therefore, am I past the point where Hotsos can provide value?

One of the way to tune SQL is to do the following.

This will work from 9iR2 and above.

Create a standalone script with the SQL Statement in it.
Make sure that statistics_level=all is set at the session level. Don't set this at the instance level as there are some issues around it.
Run the SQL statement with the 10046 trace level 12.
Use the profiler to look at the stat lines which will show you which row source operation(s) are taking up most of the response time.

Then run dbms_xplan for that SQL Statement which will tell you the predicates for that plan that are driving those row source operation. That is a good place to start tuning.

We will have a webinar on this in the next couple of months on how to use stat lines in trace data to do SQL Tuning.

That was me above...

And here is the script.

This is to generate a trace file with stat lines that emit correct row source operation execution timing data.


alter session set timed_statistics = true;
alter session set max_dump_file_size = unlimited;
alter session set tracefile_identifier = ‘Query1_10046’;
alter session set statistics_level=all;
alter session set events '10046 trace name context forever, level 12';
<Plug sql statement or PL/SQL here>
alter session set events ’10046 trace name context off’;
exit;


Here is a script to generate a clean 10053 trace file.

alter session set timed_statistics = true;
alter session set max_dump_file_size = unlimited;
alter session set tracefile_identifier = ‘Query1_10053’;
alter session set statistics_level=all;
alter session set events '10053 trace name context forever, level 1';
<Plug sql statement here>
alter session set events ’10053 trace name context off’;
exit;