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:
- it is stored only in SGA memory;
- it is not shared between sessions;
- it only covers single-table 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.
Related hidden parameters
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
- Use the session-level or hint approach first: an
alter systemon a hidden parameter affects every query of the instance. - To retrieve the
sql_idof a statement for AWR/ASH analysis, see How to find the SQL_ID of your SQL statement. - The original slow query referenced an application-specific schema and was removed; the diagnostic approach above applies to any statement.