LLI Wiki — My Digital Knowledge Base

Intermittently slow Oracle query: execution plans and cardinality feedback

Diagnose an Oracle query that is sometimes fast and sometimes slow: 10046 trace, TKPROF, and the _optimizer_use_feedback hidden parameters.

A query that is sometimes fast and sometimes slow is a classic Oracle symptom. This article lists the usual suspects, shows how to capture the execution plan of a fast and a slow run so they can be compared, and describes cardinality feedback (the _optimizer_use_feedback family of hidden parameters), which can make a query change plan after its first execution.

Common causes of intermittent slowness

Cause What to check
Resource contention (memory, CPU, disk I/O, locks) AWR, ASH and the V$ dynamic performance views
Cache effects Data served from the buffer cache or result cache is fast; data read from disk is slow (V$BUFFER_CACHE, V$SQL_WORKAREA)
System load Does the query only slow down when the overall load is high?
Changing execution plan Plans can vary with statistics, parameters and bind variable values; compare them with DBMS_XPLAN or SQL Tuning Advisor
Bind variables and Adaptive Cursor Sharing A plan suited to one bind value may be poor for another
Stale statistics Refresh them with DBMS_STATS

Capture and compare the plans of the fast and slow runs

Enable an extended SQL trace (event 10046, level 8 includes bind values and wait events) in the session that runs the fast query, with a trace file identifier:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'fast_query';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

Run the query, then disable tracing:

ALTER SESSION SET EVENTS '10046 trace name context off';

Do the same for the slow run with another identifier:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'slow_query';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
ALTER SESSION SET EVENTS '10046 trace name context off';

Find the trace directory:

SHOW PARAMETER dump_dest;

Look for the files containing your identifiers (fast_query, slow_query), then format them with TKPROF:

tkprof input_trace_file output_text_file explain=USER/PASSWORD@CONNECT_IDENTIFIER

In each output file, the plan is in the Rows Row Source Operation section. Compare the two plans to spot the difference.

Cardinality feedback

Cardinality feedback (11g) lets the optimizer learn from its estimation mistakes: after the first execution, a query may be marked as a candidate for a second look at re-optimization, and a different plan can be chosen on the next execution. This can explain a query that is fast on its first run and slower afterwards (see Ask TOM and the Oracle optimizer blog).

Characteristics of cardinality feedback:

To test the hypothesis on one statement, disable the feature for that query with a hint:

select /*+ opt_param('_optimizer_use_feedback' 'false') */ ...

Another approach mentioned in the source discussion is to create a stored outline from the fast execution.

These are underscore (hidden) parameters. Change them only for diagnosis, preferably at session level, and ideally with Oracle Support guidance.

Parameter Role Default
_optimizer_use_feedback Whether the optimizer uses cardinality feedback TRUE
_optimizer_gather_feedback Whether the optimizer asks the execution engine to detect mis-estimates and create feedback (independent from OPTIMIZER_ADAPTIVE_STATISTICS) TRUE
_optimizer_use_feedback_for_join Whether the optimizer uses join cardinality feedback not covered by _optimizer_use_feedback FALSE

Check the current value of a hidden parameter (requires access to the X$ tables, i.e. SYS):

col Name for a31
col Value for a10
select ksppinm "Name", ksppstvl "Value"
from x$ksppi a, x$ksppsv b
where a.indx = b.indx and ksppinm = '_optimizer_use_feedback';
Name                            Value
------------------------------- ----------
_optimizer_use_feedback         TRUE

Enable or disable at instance level:

alter system set "_optimizer_use_feedback" = true scope=both;
alter system set "_optimizer_use_feedback" = false scope=both;

Or test at session level:

alter session set "_optimizer_use_feedback" = true;
alter session set "_optimizer_use_feedback" = false;

The same syntax applies to the other two parameters:

alter system set "_optimizer_gather_feedback" = TRUE scope=both;
alter system set "_optimizer_gather_feedback" = FALSE scope=both;
alter system set "_optimizer_use_feedback_for_join" = TRUE scope=both;
alter system set "_optimizer_use_feedback_for_join" = FALSE scope=both;

Notes

Mastodon LLIMastodon SocialFosstodon