# CBO

## Introduction

The idea for this blog post is based on a recent Twitter discussion with Martin Berger, Martin Bach and Mauro Pagano about revealing SQL Plan Directive details for an existing cursor as walking through the standard Oracle data dictionary views can be very time consuming/slow and there are still some details missing about the dynamic sampling task itself, even if you have found what you are looking for.

## Dynamic Sampling

Following on from an OTN posting about dynamic sampling difficulties I had planned to write a blog post about the difference between “not sampling when hinted” and “ignoring the sample” – but Mohamed Houri got there before me.

It’s just worth highlighing a little detail that is often overlooked, though: there are two versions of the dynamic_sampling() hint, the cursor level and the table level, and the number of blocks sampled at a particular level is dependent on which version you are using.  Level 4 at the cursor level, for example, will sample 64 blocks if and only if a certain condition is met,  but at the table level it will sample 256 blocks unconditionally.

## CBO Series

About a year ago I came across a couple of useful articles from Stefan Koehler, which is when I added his name to my blog roll. As an introduction for other readers I’ve compiled an index for a series of articles he wrote about the CBO viewed, largely, from the perspective of using Oracle to run SAP. Today I realised I hadn’t got around to publishing it, and there’s been a couple of additions since I first started to compile the list.

## Temp Table Transformation Cardinality Estimates - 2

Continuing from the previous part - which was about the Temp Table Transformation and join cardinality estimates - using the same simple table setup here is a slight variation of the previously used query to demonstrate the potential impact on single table cardinality estimates:

`explain plan forwithcte as (select /* inline */ id from t1 twhere 1 = 1)select /*+           no_merge(a) no_merge(b)       */ * from cte a, cte bwhere a.id = b.idand a.id > 990 and b.id > 990;-- 11.2.0.x Plan with TEMP transformation--------------------------------------------------------------------------------`

## Temp Table Transformation Cardinality Estimates - 1

Having published recently two notes about the Temp Table Transformation highlighting the heuristics based decision and other weaknesses, for example regarding the projection of columns, it's time to publish some more notes about it.The transformation can also have significant impact on cardinality estimates, both join and single table cardinality.Looking at the difference in the join cardinality estimates of following simple example:

`create table t1asselect        rownum as id      , mod(rownum, 10) + 1 as id2      , rpad('x', 100) as fillerfrom        dualconnect by        level <= 1000;`

## Heuristic Temp Table Transformation - 2

Some time ago I've demonstrated the non-cost based decision for applying the temp table transformation when using CTEs (Common Table/Subquery Expressions). In this note I want to highlight another aspect of this behaviour.Consider the following data creating a table with delibrately wide columns:

`create table aas  select          rownum as id        , rownum as id2        , rpad('x', 4000) as large_vc1        , rpad('x', 4000) as large_vc2        , rpad('x', 4000) as large_vc3from          dualconnect by          level <= 1000;exec dbms_stats.gather_table_stats(null, 'a')`

## Function-Based Indexes And CURSOR_SHARING = FORCE

In general it is known that Function-Based Indexes (FBIs) can no longer be used by the optimizer if the expression contains literals and CURSOR_SHARING = FORCE / SIMILAR (deprecated) turns those literals into bind variables.

## Not Exists

The following requirement appeared recently on OTN:

## Combined ACCESS And FILTER Predicates - Excessive Throw-Away

Catchy title... Let's assume the following data setup:

`create table t1asselect        rownum as id      , 1 as id2      , rpad('x', 100) as fillerfrom        dualconnect by        level <= 1e4;create table t2asselect        rownum as id      , 1 as id2      , rpad('x', 100) as fillerfrom        dualconnect by        level <= 1e4;create table t3asselect        rownum as id      , 1 as id2      , rpad('x', 100) as fillerfrom        dualconnect by        level <= 1e4;exec dbms_stats.gather_table_stats(null, 't1')exec dbms_stats.gather_table_stats(null, 't2')exec dbms_stats.gather_table_stats(null, 't3')`

## push_pred – evolution

Here’s a query (with a few hints to control how I want Oracle to run it) that demonstrates the difficulty of trying to solve problems by hinting (and the need to make sure you know where all your hinted code is):