One great way to get time analysis information for a query in Oracle is to use row source statistics. Not everyone has the tuning and diagnostic pack, meaning not everyone can generate SQL Monitor reports. Row source statistics serves as a great alternative to SQL Monitor reports.
Setting Up For Row Source Statistics
Choosing How to Turn Row Source Statistics On
The first thing you have to do is to turn row source statistics on. You can do this in one of 3 different ways:
- Turning on tracing
or - Using the /*+ gather_plan_statistics */ hint in your query.
or - alter session set statistics_level = all;
I tend to shy away from tracing, because it can be a bit involved. Unless I’m going to be collecting a trace file anyway, I tend not to use this option. That leaves the /*+ gather_plan_statistics */ hint and setting statistics_level = all.
There’s actually a difference between these two options. There’s a hidden parameter in Oracle (_rowsource_statistics_sampfreq) which defaults to 128. This parameter must always be set to a power of 2, by the way. Whenever you use the /*+ gather_plan_statistics */ hint, Oracle determines how frequently to sample timing information with the _rowsource_statistics_sampfreq parameter. The _rowsource_statistics_sampfreq parameter defaults to 128. Setting it to a higher value will cause Oracle to sample fewer times. Setting it to a lower value will cause Oracle to sample more frequently.
If you use alter session set statistics_level = all, by default Oracle will ignore the _rowsource_statistics_sampfreq parameter and use a sample frequency of “1”.
But actually, it’s more confusing than this…because you can manually set _rowsource_statistics_sampfreq manually, and Oracle won’t ignore it. So if you leave this underscore parameter at its default, Oracle ignores it and uses a sample frequency of 1. But if you manually set it, then Oracle will use whatever you manually set as the value.
So if you use statistics_level = all (or set _rowsource_statistics_sampfreq to 1), Oracle will sample your query runtimes more frequently. But as a result the timing information that Oracle gives to you will be more precise.
Turning Off Serveroutput
If you’re using SQL*Plus then you want to issue a set serveroutput off. This also applies to any clients that emulate SQL*Plus like Toad, or SQL Developer. This will make it so that serveroutput doesn’t interfere with grabbing the last query that was run. So it makes grabbing the last query’s execution plan easier. Otherwise, you would have to specify the SQL_ID and CURSOR_CHILD_NO inside dbms_xplan.display_cursor(). It’s just easier to set serveroutput off.
Getting the Row Source Statistics Information Back
Once you’ve turned on row-source statistics and you’ve set serveroutput off, you run your query to completion. In other words, don’t just fetch the first 100 rows…you’ve must fetch all the rows in your result set. Then you can view the information using dbms_xplan.display_cursor(format => ‘allstats last’).
Also, my personal recommendation is to add the advanced +adaptive flags to the format list as well. This will show you almost everything about the execution plan.
set serveroutput off
alter session set statistics_level = all;
-- Run your query here
select ...
select *
from table(dbms_xplan.display_cursor(format => 'allstats last advanced +adaptive'));Free Oracle SQL Tuning Guide
Check out my FREE guide! 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!
Get it here: tuningsql.com/secrets
