ORA-10173

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.

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?