---
title: ORA-10173
description: What happens when you see lots of ORA-10173 in the alert log? Stefan enlightens us with answers from his research.
---

[Blog | Pythian ](https://www.pythian.com/blog)

# [ORA-10173](https://www.pythian.com/blog/ora-10173-dynamic-sampling-time-error)

 Written by [Pythian Marketing](https://www.pythian.com/blog/author/pythian-marketing) | Nov 23, 2015 5:00:00 AM

 

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?

[View full post](https://www.pythian.com/blog/ora-10173-dynamic-sampling-time-error)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Pythian Marketing"
  },
  "dateModified" : "2026-03-05T06:58:53.001Z",
  "datePublished" : "2015-11-23T05:00:00Z",
  "headline" : "ORA-10173: Dynamic sampling time-out error",
  "image" : {
    "@type" : "ImageObject",
    "height" : 60,
    "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
    "width" : 60
  },
  "mainEntityOfPage" : "https://www.pythian.com/blog/ora-10173-dynamic-sampling-time-error",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60,
      "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
      "width" : 60
    },
    "name" : "Pythian Blog"
  }
}
```