After a recent upgrade to a 12.1.0.2 warehouse-type database, we began seeing a flood of ORA-10173 errors in the alert log. While some resources link this to the Tuning Advisor, we weren't running that feature, necessitating a deeper look into the cause.
The Diagnostics: Trapping the Error
When dealing with an error of uncertain origin, the first step is to set up an error trap. In Oracle, this is achieved by setting an errorstack event.
Activating the Event
It is best practice to set this on the smallest scope possible (session level) before considering system-wide changes.
-- Enable the error trap alter session set events '10173 trace name errorstack level 1';
Deactivating the Event
Once you have captured a few occurrences in your trace files, disable it immediately:
-- Disable the error trap alter session set events '10173 trace name errorstack off';
Analyzing the Evidence
Activating the event forces Oracle to write a trace file to the diagnostics repository the next time the exception is raised. The trace file revealed the specific SQL statement and a stack trace.
The Problematic SQL
The trace file identified the current SQL statement (SQL_ID: anbp9r5n8ysu6) triggering the event:
----- Current SQL Statement ----- SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */ SUM(C1) FROM (...)
The DS_SVC comment and the result_cache(snapshot=3600) hint provided significant clues. These point toward a 12c feature: Dynamic Statistics (or Adaptive Statistics).
The Culprit: 12c Dynamic Statistics
Dynamic Statistics is an enhancement to dynamic sampling where the optimizer automatically gathers more data during the parse phase.
Why the Error Occurs
Oracle controls this sampling with an internal timeout. If the timeout is hit before the sampling completes, the database raises ORA-10173.
- Is it a crash? No. Your query will not fail; the error is internal.
- Is it a problem? Potentially. It indicates "wasted work" where Oracle spent time gathering data, hit a limit, and threw the partial data away.
Identifying Adaptive Sampling
You can verify if this feature is active by checking the execution plan via dbms_xplan.display_cursor. Look for this in the notes section:
Note: dynamic statistics used: dynamic sampling (level=AUTO)
Performance Impact and Tuning
Determining whether this feature is helpful depends entirely on your workload.
When to Ignore
If you run long-running queries (minutes or hours), spending a few seconds on extra statistics gathering is often a worth-while trade-off for a better execution plan.
When to Take Action
If your queries are generally fast and Oracle is spending more time parsing/sampling than executing, or if you see thousands of ORA-10173 errors, you should consider tuning.
| Metric |
Recommendation |
| Parameter |
optimizer_dynamic_sampling (Default is 2). |
| PDB/Session Level |
Disable for specific fast-query sessions. |
| Hidden Parameters |
_optimizer_ads_result_cache_life (Default 3600s) controls how long sampled data stays in the cache. |
In our case, with thousands of errors, we've opened a case with Oracle. One potential workaround is increasing the result cache lifetime to reduce sampling frequency, but always consult Oracle Support before adjusting hidden parameters
Oracle Database Consulting Services
Ready to optimize your Oracle Database for the future?
.svg){kind=small}
.svg){kind=small}