# [Oracle] DB Optimizer Part V – Introduction of dynamic sampling and why it is used in SAP BI environments by (SAP) default

## Introduction

Have you ever wondered why SAP recommends different cost based optimizer settings (like init parameter OPTIMIZER_DYNAMIC_SAMPLING) for OLTP and OLAP systems? Have you ever wondered why Oracle is not clever enough to handle “complex” predicates very well? I see similar issues with Oracle databases in SAP or non-SAP based environments from time to time and so here we go with part 5 of my DB optimizer blog series.

This blog is focused on dynamic sampling and how it can effect the cardinality estimations in execution plans in several cases. It will also demonstrate a common advantage of dynamic sampling in SAP BI environments.

## Dynamic sampling basics

We should clarify some dynamic sampling basics first, before we dig into a common use case of it. This section covers the basics of dynamic sampling only. Randolf Geist has already written a detailed blog series about dynamic sampling, its possibilities and how to enable it in several ways. Please check the reference section in this blog, if you want to dig deeper into it by reading the blog posts of Randolf Geist.

**Oracle Documentation**

*With dynamic sampling, the database augments statistics by issuing recursive SQL to scan a small random sample of table blocks.*

*Dynamic sampling augments missing or insufficient optimizer statistics. Using dynamic sampling the optimizer can improve plans by making better estimates for predicate selectivity. Dynamic sampling can supplement statistics such as table block counts, applicable index block counts, table cardinalities (estimated number of rows), and relevant join column statistics.*

*13.6.2.2 When the Optimizer Uses Dynamic Sampling*

*During compilation, the optimizer decides whether to use dynamic sampling based on a number of factors, including whether the statements use parallel processing or serial processing.*

*For parallel statements, the optimizer automatically decides whether to use dynamic sampling and which level to use. The decision depends on the size of the tables and the complexity of the predicates. The optimizer expects parallel statements to be resource-intensive, so the additional overhead at compile time is worth it to ensure the best plan. The database ignores the OPTIMIZER_DYNAMIC_SAMPLING setting unless set to a nondefault value, in which case the value is honored.*

*For serially processed SQL statements, the dynamic sampling level depends on the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter and is not triggered automatically by the optimizer. Serial statements are typically short-running, so that any overhead at compile time could have a huge impact on their performance.*

*In both the serial and parallel cases, the database performs dynamic sampling when existing statistics are not sufficient:*

*Missing statistics*

*When one or more of the tables in the query do not have statistics, the optimizer gathers basic statistics on these tables before optimization. In this case, the statistics are not as high-quality or as complete as the statistics gathered using the DBMS_STATS package. This tradeoff is made to limit the impact on the compile time of the statement.*

*Collected statistics cannot be used or are likely to lead to poor estimates*

*For example, a statement may contain a complex predicate expression, but extended statistics are not available (see “Extended Statistics”). Extended statistics help the optimizer get good quality cardinality estimates for complex predicate expressions. Dynamic sampling can compensate for the lack of extended statistics.*

*Note:*

*If no rows have been inserted, deleted, or updated in the table being sampled, then dynamic sampling is repeatable. This means that the optimizer generates the same statistics each time you run dynamic sampling against the table.*

Please check the following table for more information about the dynamic sampling levels (the default level 2 and level 6 is used in SAP based environments by default): Dynamic Sampling Levels

** Footnote: The information about the block sample sizes is not entirely correct in this table, but we will see this in the CBO trace files later on.

**SAP Documentation**

*SAPnote #1431798 – Oracle 11.2.0: Database Parameter Settings*

*OPTIMIZER_DYNAMIC_SAMPLING (OLTP: **Do not set! / OLAP: 6)*

Dynamic sampling can be activated on system level (init parameter), session level (init parameter), table level (hint DYNAMIC_SAMPLING) or on SQL level with the help of SQL profiles or SQL patches (hint opt_param) without modifying the SQL itself.

Let’s summarize all that information related to SAP environments:

- Dynamic sampling gathers statistics “on-the-fly”, if there are no statistics at all or it gathers additional statistics in various scenarios (dependent on sampling level)
- Dynamic sampling can have a negative impact on the performance due to recursive queries / sampling (typically for short running queries in OLTP environments and not for long running OLAP queries) while compiling SQLs
- Dynamic sampling has the default value 2 in OLTP based environment and 6 in OLAP environments
- Dynamic sampling can provide additional information for “complex” queries, if predicate values can be evaluated (bind variable peeking and adaptive cursor sharing is disabled in SAP based environment – keyword “literals” which are commonly used in BI queries)

## Common use case

The following use case was created and run on an Oracle 11.2.0.3 database with OEL 6.2.

```
SYS@T11:133> create table DYNTEST (COUNTRY VARCHAR2(40), WERKS VARCHAR(20),
TEXT VARCHAR(4000));
SYS@T11:133> exec DBMS_STATS.GATHER_TABLE_STATS('SYS', 'DYNTEST',
method_opt => 'FOR ALL COLUMNS SIZE 1');
```

This example is lean on a common SAP BI scenario, but without the extra dimension tables and no parallel execution to keep it as simple as possible. Basically we have a table called DYNTEST with 3 columns (one for the country , one for the plant number and one for a various text). The most important point to notice is, that we have a correlation between the columns COUNTRY and WERKS (plant number).

Let’s assume, that the plant number 1200 references to a plant in Munich.

In consequence 1000 data sets can only be relevant in combination with *COUNTRY = ‘DE’ and WERKS = ‘1200’ *. There could never be a plant 1200 in the countries US or CH. We (as a human) know that there is a correlation, but the cost based optimizer does not know this by default (basic table / column statistics).

So let’s check the cardinality estimation, if we combine both columns in the WHERE clause.

**Default cardinality estimation (without extended statistics, histograms, dynamic sampling, etc.)**

The optimizer is not able to calculate the right cardinality (it expects 273, but in reality there are 1000 rows returned), because of it does not know anything about the correlation. It uses its default formula for calculating the cardinality: 7650 rows * 0.25 (COUNTRY) * 0.142857143 (WERKS) = round(273.2142859875) = 273 rows

The optimizer is not able to calculate the right cardinality (it expects 273, but in reality there are 5000 rows returned), because of it does not know anything about the correlation. It uses its default formula for calculating the cardinality: 7650 rows * 0.25 (COUNTRY) * 0.142857143 (WERKS) = round(273.2142859875) = 273 rows

The optimizer is not able to calculate the right cardinality (it expects 273, but in reality there are 0 rows returned), because of it does not know anything about the correlation. It uses its default formula for calculating the cardinality: 7650 rows * 0.25 (COUNTRY) * 0.142857143 (WERKS) = round(273.2142859875) = 273 rows

**Cardinality estimation with dynamic sampling set to 4**

`SYS@T11:144> alter session set optimizer_dynamic_sampling = 4;`

The optimizer samples additional statistics “on-the-fly” to get an idea of the “complex” predicate and its correlation. The optimizer is closer to the real world (1133 rows to 1000 rows) after sampling and adjusting the estimation. It doesn’t hit it exactly, but it is much better than before (273 estimated rows).

The optimizer samples additional statistics “on-the-fly” to get an idea of the “complex” predicate and its correlation. The optimizer is closer to the real world (4635 rows to 5000 rows) after sampling and adjusting the estimation. It doesn’t hit it exactly, but it is much better than before (273 estimated rows).

The optimizer samples additional statistics “on-the-fly” to get an idea of the “complex” predicate and its correlation. The optimizer got it right (E-Rows = 1 means 1 or 0 rows) after sampling and adjusting the estimation.

**Cardinality estimation with dynamic sampling set to 9**

`SYS@T11:144> alter session set optimizer_dynamic_sampling = 9;`

The optimizer samples additional statistics “on-the-fly” to get an idea of the “complex” predicate and its correlation. The optimizer hit it exactly (E-Rows = 1000 / A-Rows 1000) after sampling and adjusting the estimation with a higher sample size.

The optimizer samples additional statistics “on-the-fly” to get an idea of the “complex” predicate and its correlation. The optimizer hit it exactly (E-Rows = 5000 / A-Rows 5000) after sampling and adjusting the estimation with a higher sample size.

The result is still correct, but the optimizer already got it correct with a smaller sample size.

**The CBO trace for dynamic sampling level 4 (COUNTRY = ‘US’ and WERKS = ‘3400’) **

I will post only one CBO trace section for illustration here. I have chosen the query with COUNTRY = ‘US’ and WERKS=’3400′.

In the CBO trace you will find the (recursive) dynamic sampling query, which is executed by compiling the SQL / generating the SQL execution plan (= overhead of dynamic sampling). You will also see the level (level: 4), but in case of level 4 you also find a little discrepancy with the official documentation. The documentation states, that 64 blocks will be sampled by level 4, but in reality the max sample block count is only 32 blocks. So in my case the table got 44 blocks (regarding the gathered basic statistics – in reality the segment consists of 48 blocks), but only 31 blocks are sampled dynamically.

Oracle calculated a new selectivity (0.60589319) after dynamic sampling has finished, which results in 4635 estimated rows in consequence (7650 rows * 0.60589319 = round(4635.0829035) = 4635 rows)

**The CBO trace for dynamic sampling level 9 (COUNTRY = ‘US’ and WERKS = ‘3400’) **

I will post only one CBO trace section for illustration here. I have chosen the query with COUNTRY = ‘US’ and WERKS=’3400′.

In the CBO trace you will find the (recursive) dynamic sampling query, which is executed by compiling the SQL / generating the SQL execution plan (= overhead of dynamic sampling). You will also see the level (level: 9), but in case of level 9 the official documentation and the reality does not match in full detail as well. The max sample block count is 4096 and not 4086 (like mentioned). However my demo table is much smaller and so only 44 blocks are sampled anyway.

Oracle calculated a new selectivity (0.60589319) after dynamic sampling the whole segment, which results in 5000 estimated rows in consequence (7650 rows * 0.65359477 = round(4999.9999905) = 5000 rows)

## Summary

We just covered one possibility of how dynamic sampling can help the optimizer to get better estimations, but i think it is a pretty good example to get the key point. You can also create extended statistics (>= Oracle 11) to get the similar effect (without sampling overhead), but you need to know all possible query combinations to create the corresponding extended statistics. This would be possible on a limited number of possibilities, but just think about dynamic BEx queries with drill downs and so on – nearly impossible.

Check out one of my previous blogs, if you don’t know why it is so important, that the cost based optimizer estimates the correct cardinality.

Dynamic sampling has its limitations and issues as well, but this would go too far right now (check the mentioned blog posts of Randolf Geist, if you want to know more about the different scenarios and solutions).

If you have any further questions – please feel free to ask or get in contact directly, if you need assistance by troubleshooting Oracle database performance issues.

## References

- Oracle Documentation – Dynamic Sampling Levels
- Oracle Documentation – Init Parameter OPTIMIZER_DYNAMIC_SAMPLING
- SAPnote #1431798 – Oracle 11.2.0: Database Parameter Settings
- Detailed blog post series about Dynamic Sampling by Randolf Geist
- Dynamic Sampling (I), an Introduction – Part 1
- Dynamic Sampling (I), an Introduction – Part 2
- Dynamic Sampling (II) – Controlling the Activity – Part 1
- Dynamic Sampling (II) – Controlling the Activity – Part 2
- Dynamic Sampling (III) – Real-Life Data – Part I
- Dynamic Sampling (III) – Real-Life Data – Part II

Hi Stefan,

Brilliant doc.

Best Regards

Marius

Very good and detailed!