Mohamed Houri’s Oracle Notes

December 30, 2014

2014 in review

Filed under: Oracle — hourim @ 8:59 am

I have wished in my 2013 annual blogging review to publish 100 blog articles in 2014. Instead of those hoped 100 posts, there have been only 36 posts. In my defence I can use the list of articles I have published for Oracle Otn, Allthings Oracle and Toad World summarized here. Anyway, my 2014 blogging activity was not as I have expected it to be. I hope that the next year will be more fruitful and boosting as far as there are in front of me several challenging issues to investigate and all what I need is:

  • a reproducible model
  • few tests
  • few tests again with different Oracle releases
  • observations
  • consolidations

And end up by writing a document and submit it for critics and review.

Here below is my 2014 blogging activity summary. I wish you a happy new year

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 31,000 times in 2014. If it were a concert at Sydney Opera House, it would take about 11 sold-out performances for that many people to see it.

Click here to see the complete report.

December 25, 2014

Subquery Coalescing

Filed under: Oracle — hourim @ 9:01 am

It’s fairly clear that to improve a performance of a SQL query we sometimes need to rewrite it so that we end up by either reducing the number of scanned tables or by giving the CBO an alternative path the original query was pre-empting it to take. In a previous article, I’ve looked at ways of turning a disjunctive subquery into a conjunctive subquery so that it can be unnested and merged with its parent query block. In this article I aim to explain how to improve performance by coalescing two distinct subqueries. This tuning strategy will be preceded by the conjunction and disjunction subquery concept definition. Finally, simple examples of subquery coalescing will be presented and explained. I aim also to show that sometime de-coalescing can reveal to be a good tuning strategy as far as it might open a new path for an optimal execution plan. This article is based on the Oracle published paper entitled Enhanced Subquery Optimization in Oracle

Conjunction, Disjunction and Containment property

Two subqueries are eligible to be coalesced provided they verify the containment property. A subquery subq1 is said to contain a second subq2 if the result of subq2 is a subset of the subq1 result. In this case subq1 is called the container query block while subq2 is called the contained query block. The same two subqueries will verify the contained property when subq2 contains a conjunctive predicate which, if suppressed, makes subq1 and subq2 equivalent.

Let’s picture the containment property to make it crystal clear. Below I have the following three distinct query blocks

select 
     t1.*
from 
     t1,t2
where t2.id1 = t1.id1;

9000 rows selected.

select 
     t1.*
from 
     t1,t2
where t2.id1 = t1.id1
and   t2.status = 'COM';

8900 rows selected.

select 
     t1.*
from 
     t1,t2
where t2.id1 = t1.id1
and   t2.id1 = t2.id;

4 rows selected.

If we get rid of the predicate part on the status column from the second query block, the first and the second query become equivalent. They will then verify the containment property and therefore can be coalesced. If we look at the second and the third query blocks they are also verifying the containment property as far as the result set of the third query is a subset of the result set of the second one. That is the containment property definition.

A conjunction is the action of linking two subqueries with an AND operator. The conjunction of the two subqueries is true if the two subqueries are simultaneously true; otherwise it is false.

A disjunction is the action of liking two subqueries with an OR predicate. The disjunction of two subqueries is true when one of the two subqueries is true and is false when both are simultaneously true.

Coalescing two subqueries of the same type (exists)

Two conjunctive subqueries satisfying the containment property can be coalesced. Consider the following reproducible model (11.2.0.3)

create table t1
   as select 
    rownum                id1, 
    trunc((rownum-1/3))   n1, 
    date '2012-06-07' + mod((level-1)*2,5) start_date,
    lpad(rownum,10,'0')   small_vc, 
    rpad('x',1000)        padding 
from dual
connect by level <= 1e4;   

create table t2
as select 
    rownum id
    ,mod(rownum,5) + mod(rownum,10)* 10  as id1
    ,case
       when mod(rownum, 1000) = 7 then 'ERR'
       when rownum <= 9900 then 'COM'
       when mod(rownum,10) between 1 and 5 then 'PRP'
     else
       'UNK'
     end status
     ,lpad(rownum,10,'0')    as small_vc  
     ,rpad('x',70)           as padding
from dual
connect by level <= 1e4;

And the following query with two exists subqueries of the same type (exists)

select 
     start_date
    ,count(1) 
from t1 
where 
     start_date >= to_date('10062012','ddmmyyyy')
and (exists (select null
             from t2
             where t2.id1 = t1.id1
             and t2.status = 'COM'
             )
  or exists (select null
             from t2
             where t2.id1 = t1.id1
             and   t2.id1 = t2.id
             )
     )
group by start_date;     

START_DATE          COUNT(1)
----------------- ----------
20120611 00:00:00          2
20120610 00:00:00          1

--------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Starts | E-Rows | A-Rows | Buffers | Reads  |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |      1 |        |      2 |    1950 |   1665 |
|   1 |  HASH GROUP BY        |         |      1 |      1 |      2 |    1950 |   1665 |
|*  2 |   HASH JOIN SEMI      |         |      1 |      1 |      3 |    1950 |   1665 |
|*  3 |    TABLE ACCESS FULL  | T1      |      1 |   4908 |   4000 |    1670 |   1665 |
|   4 |    VIEW               | VW_SQ_1 |      1 |  11843 |   9894 |     280 |      0 |
|   5 |     UNION-ALL         |         |      1 |        |   9894 |     280 |      0 |
|*  6 |      TABLE ACCESS FULL| T2      |      1 |      1 |      4 |     140 |      0 |
|*  7 |      TABLE ACCESS FULL| T2      |      1 |  11842 |   9890 |     140 |      0 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("VW_COL_1"="T1"."ID1")
   3 - filter("START_DATE">=TO_DATE(' 2012-06-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - filter("T2"."ID1"="T2"."ID")
   7 - filter("T2"."STATUS"='COM')

Note
-----
   - dynamic sampling used for this statement (level=2)

Since the above two exists subqueries verify the containment property, let’s then coalesce them and note the performance consequences we will reach through this transformation

select 
     start_date
    ,count(1) 
from t1 
where 
     start_date >= to_date('10062012','ddmmyyyy')
and (exists (select null
            from t2
            where t2.id1 = t1.id1
            and(t2.status = 'COM'
                or t2.id1 = t2.id)
            )                 
          )
group by start_date;     

----------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows | Buffers | Reads  |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      2 |    1808 |   1665 |
|   1 |  HASH GROUP BY      |      |      1 |      1 |      2 |    1808 |   1665 |
|*  2 |   HASH JOIN SEMI    |      |      1 |     10 |      3 |    1808 |   1665 |
|*  3 |    TABLE ACCESS FULL| T1   |      1 |   4000 |   4000 |    1669 |   1665 |
|*  4 |    TABLE ACCESS FULL| T2   |      1 |   9890 |   9890 |     139 |      0 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."ID1"="T1"."ID1")
   3 - filter("START_DATE">=TO_DATE(' 2012-06-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   4 - filter(("T2"."STATUS"='COM' OR "T2"."ID1"="T2"."ID"))

Thanks to this coalescing operation we are now doing only a single access full t2 table instead of two initial full table scan of the same table. As such, we saved 140 logical I/O dropping the total number of buffers from 1950 to 1808.

Coalescing two subqueries of different type (exists and not exists)
Real life examples can show cases of subqueries verifying the containment property but having a different type. An example of such situation is presented in the below query:

select 
     start_date,
     id1,
     n1
from t1 
where 
     start_date >= to_date('10062012','ddmmyyyy')
and exists     (select null
                from t2 a
                where a.id1 = t1.id1                
                )
and not exists (select null
                from t2 b
                where b.id1 = t1.id1
                and   b.id1 != 83
                );

START_DATE               ID1         N1
----------------- ---------- ----------
20120611 00:00:00         83         82

----------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows | Buffers | Reads  |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |    1947 |   1665 |
|*  1 |  HASH JOIN ANTI     |      |      1 |      7 |      1 |    1947 |   1665 |
|*  2 |   HASH JOIN SEMI    |      |      1 |     10 |      3 |    1808 |   1665 |
|*  3 |    TABLE ACCESS FULL| T1   |      1 |   4000 |   4000 |    1669 |   1665 |
|   4 |    TABLE ACCESS FULL| T2   |      1 |  10000 |  10000 |     139 |      0 |
|*  5 |   TABLE ACCESS FULL | T2   |      1 |   9000 |   9000 |     139 |      0 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"."ID1"="T1"."ID1")
   2 - access("A"."ID1"="T1"."ID1")
   3 - filter("START_DATE">=TO_DATE(' 2012-06-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - filter("B"."ID1"<>83)

The above query contains two distinct subqueries:

exists (select null
        from t2 a
        where a.id1 = t1.id1                
        )

not exists (select null
            from t2 b
            where b.id1 = t1.id1
            and   b.id1 != 83
            );

They verify the containment property because if we get rid of the predicate (b.id1 != 83) from the second one it becomes equivalent to the first one. The sole difference is that they are of different types: the first one is an exists subquery while the second one is a not exists subquery. Coalescing such a kind of subquery yields to the following query

select 
     start_date,
     id1,
     n1
from t1 
where 
     start_date >= to_date('10062012','ddmmyyyy')
and exists     (select null
                from t2 a
                where a.id1 = t1.id1                
                having sum (case when  a.id1 != 83 
                            then 1 else 0 end) = 0
               );
START_DATE               ID1         N1
----------------- ---------- ----------
20120611 00:00:00         83         82

-----------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows | Buffers | Reads  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      1 |     557K|   1665 |
|*  1 |  FILTER              |      |      1 |        |      1 |     557K|   1665 |
|*  2 |   TABLE ACCESS FULL  | T1   |      1 |   4000 |   4000 |    1670 |   1665 |
|*  3 |   FILTER             |      |   4000 |        |      1 |     556K|      0 |
|   4 |    SORT AGGREGATE    |      |   4000 |      1 |   4000 |     556K|      0 |
|*  5 |     TABLE ACCESS FULL| T2   |   4000 |   1000 |   3000 |     556K|      0 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   2 - filter("START_DATE">=TO_DATE(' 2012-06-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   3 - filter(SUM(CASE  WHEN "A"."ID1"<>83 THEN 1 ELSE 0 END )=0)
   5 - filter("A"."ID1"=:B1)

One of the immediate and obvious observations is that the coalescing process produces a dramatic performance alteration as far as we went from a clean 1947 logical I/O to a terrific 557 thousands of Buffers. This is a clear lesson that it is not because we’ve eliminated a full table access from the picture that we will have a performance improvement. Sometimes visiting twice a table might make the CBO generating an optimal execution plan. The collateral effect of the above coalescing process is that the CBO has been unable to unnest the new exist subquery and therefore it execute it as filter operation even though that the most resource expensive operation is operation 5. Important information in the new suboptimal plan is the terrific (4000) number of executed operations as shown by the Starts column in the execution plan. The operation at line 2 produces 4000 rows and it dictates the number of times the operation at line 3 (and its children operations at lines 4 and 5) it will be executed.

The CBO has been unable to unnest the new coalesced exist subquery because of the presence of the aggregate function (sum) as proofed by the corresponding 10053 trace file

*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that 
does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$2 (#2)
SU:     SU bypassed: Failed aggregate validity checks.
SU:     SU bypassed: Failed basic validity checks.
SU:   Validity checks failed.

As far as unnesting is impossible de-coalescing might represent a good strategy in this case. And as far as we can go from the coalesced query to the original one thanks to the containment property, we can therefore open the unnesting path leading to original optimal plan we thought that we are going to improve by coalescing the two subqueries.

I was thinking in this particular case of subquery unnesting impossibility to take advantage of virtual column and hide the aggregation function in the coalesced exists subquery. Unfortunately it is impossible to create a virtual column using an aggregation function as shown below:

alter table t2 add aggr_virt number generated always 
                    as (sum(case when id1 != 83 then 1 else 0 end)) virtual;
alter table t2 add aggr_virt number generated always as (sum(case when id1 != 83 then 1 else 0 end)) virtual
ERROR at line 1:
ORA-00934: group function is not allowed here

The Oracle documentation says the following about creating virtual columns

When you define a virtual column, the defining column_expr must refer only to columns of the subject table that have already been defined, in the current statement or in a prior statement.

Conclusion

Coalescing two subqueries of the same type might drastically reduce the number of logical I/O as far as it can eliminate an entire table access. Coalescing two subqueries of different types might pre-empt the CBO from taking advantage of the unnesting transformation. Fortunately if you know how to coalesce two different subqueries you will know how to de-coalesce them to allow the CBO taking advantage of unnesting the subqueries with their main query blocks.

Keep always an open eye on the concept of conjunction and disjunction with subqueries. The CBO is unable to unnest a subquery when it appears into a disjunction operation leading therefore to several executing of the subquery as a filter operation applied on the result set of the main query block. The CBO is however able to transform a conjunction of two subqueries of the same time into a single conjunction subquery and unnest it with its parent query block.

December 17, 2014

Adaptive plan

Filed under: Oracle — hourim @ 1:12 pm

Have you ever seen such kind of execution plan

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |      5 |00:00:00.04 |       4 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| T1_T2_MV |      1 |      5 |00:00:00.04 |       4 |
------------------------------------------------------------------------------------------

Note
-----
   - this is an adaptive plan

Or this one:

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last +adaptive'));

-----------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |      5 |       4 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| T1_T2_MV |      1 |      5 |       4 |
-----------------------------------------------------------------------------

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)    

Where are those inactive rows marked ‘-‘ in the plan?

I was writing an article on tuning disjunctive subqueries when I have been prompted to check something with materialized views. Coincidentaly at the time the materialized view question kicks in, I was finishing the model for the disjunctive subquery purpose. Thereofe, I’ve decided to created a materialied view using this model and here what happens.

select /*+ qb_name(parent) */
     id,
     n1
from
     t1 
where
     n1 = 100
and  exists
      (select /*+ qb_name(child) */  
            null 
       from t2 
       where 
            t2.id = t1.id
       and  t2.x1 = 100
       );     
-------------------------------------------------------------------------------------------
|   Id  | Operation                                | Name     | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                         |          |      1 |      5 |      13 |
|- *  1 |  HASH JOIN                               |          |      1 |      5 |      13 |
|     2 |   NESTED LOOPS                           |          |      1 |      5 |      13 |
|     3 |    NESTED LOOPS                          |          |      1 |      5 |      11 |
|-    4 |     STATISTICS COLLECTOR                 |          |      1 |      5 |       3 |
|     5 |      SORT UNIQUE                         |          |      1 |      5 |       3 |
|     6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T2       |      1 |      5 |       3 |
|  *  7 |        INDEX RANGE SCAN                  | T2_X1_I1 |      1 |      5 |       2 |
|  *  8 |     INDEX RANGE SCAN                     | T1_ID_I1 |      5 |      5 |       8 |
|  *  9 |    TABLE ACCESS BY INDEX ROWID           | T1       |      5 |      5 |       2 |
|- * 10 |   TABLE ACCESS FULL                      | T1       |      0 |      0 |       0 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T2"."ID"="T1"."ID")
   7 - access("T2"."X1"=100)
   8 - access("T2"."ID"="T1"."ID")
   9 - filter("N1"=100)
  10 - filter("N1"=100)

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

The materialized view on the top of the above query resembles to this:

create materialized view t1_t2_mv as
select /*+ qb_name(parent) */
     id,
     n1
from
     t1 
where
     n1 = 100
and  exists
      (select /*+ qb_name(child) */  
            null 
       from t2 
       where 
            t2.id = t1.id
       and  t2.x1 = 100
       );     
Materialized view created.

And I have finished the setup by enabling query rewrite on the materialized view:

alter materialized view t1_t2_mv enable query rewrite;
Materialized view altered.

Finally I re-executed the initial query and get the corresponding execution plan as usual

select /*+ qb_name(parent) */
     id,
     n1
from
     t1 
where
     n1 = 100
and  exists
      (select /*+ qb_name(child) */  
            null 
       from t2 
       where 
            t2.id = t1.id
       and  t2.x1 = 100
       );     
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |      5 |00:00:00.04 |       4 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| T1_T2_MV |      1 |      5 |00:00:00.04 |       4 |
------------------------------------------------------------------------------------------

Note
-----
   - this is an adaptive plan

And the corresponding outline is:

Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      DB_VERSION('12.1.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$35C14E55")
      REWRITE(@"SEL$F743C7BF" "T1_T2_MV")
      OUTLINE(@"SEL$F743C7BF")
      REWRITE(@"PARENT" "T1_T2_MV")
      OUTLINE(@"PARENT")
      FULL(@"SEL$35C14E55" "T1_T2_MV"@"SEL$518C1272")
    END_OUTLINE_DATA
  */	   

Spot in passing how the number of Buffers drops from 13 to 4 when the CBO decided to use the materialized view instead of the initial query.

Bottom line: The Note about adaptive plan you might see when materialized views are used is related to the SQL behind the materialized view and not to the materialized view itself.

December 10, 2014

Bind Aware – Part III

Filed under: Oracle — hourim @ 12:30 pm

After having explained

  • In Part I, how Oracle is monitoring a cursor candidate for a bind aware status via the number of processed rows each bind variable value is generating and how this is linked with the count of the three different bucket_id
  • In Part II, how Oracle is exploiting the tandem (bucket_id, count) to decide when time has come to make a cursor bind aware.Part in which I think I have succeeded to figure out the secret sauce the Adaptive Cursor Sharing Layer code is using to mark a cursor bind aware in two cases which are (a) when only to adjacent bucket_id are concerned (the count of the third bucket_id = 0) and (b) when only to distant bucket_id are concerned (that is the count of bucket_id n°1 = 0).

I left the third case which concerns all bucket_id  (that is the count of all bucket_id != 0) for the current blog article.

I will immediately temper your enthusiasm and say that unfortunately I didn’t succeed to figure out the secret sauce Oracle is using to mark a cursor bind aware in this third case. Nevertheless, this failure didn’t pre-empted me to share with you all the observations and tests I have done hoping that someone will take over the task and come up with a nice conclusion

You will have pointed out that I’ve also made an exploit by reserving two articles on the Adaptive Cursor Sharing without showing a single execution plan! This is because I aimed through this series of articles to show how Oracle is internally managing and monitoring this feature rather than to show its goal which is to produce several optimal execution plans for a SQL statement using bind variables and fulfilling particular pre-requisites.

Finally, you might have also noticed that, despite 3 articles, I still have not exposed nor explain what happens when the cursor is finally made bind aware. In fact once a cursor is made bind aware there is a new piece of internal Oracle code that takes over the monitoring job. This is the Extended Cursor Sharing (ECS) feature which monitors the selectivity (or a range of selectivities) for each child cursor that has been previously made bind aware by ACS internal code. While the ACS feature uses the tandem (bucket_id, count) for its internal secret sauce (half secret from now and on), the ECS feature is based on the v$sql_cs_selectivity view.

Back now to the content of this third and last part of the series which exposes my tests when trying to decipher the ACS working mechanism when all bucket_id are concerned

I have decomposed my tests in 3 categories (all bucket_id have a count != 0)

  • The first case corresponds to a cursor marked bind aware following an increment of the count of bucket_id n°0
  • The second case is when a cursor is marked bind aware following an increment of the count of bucket_id n°1
  • The third category corresponds to a cursor marked bind aware following an increment the count of the bucket_id n°2

Based on those different observations, I was aiming to derive an empirical algorithm that might explain how Oracle is taking its decision to mark a cursor bind aware when all bucket-ids are concerned. Unfortunately, in contrast to the case exposed in Part II of this series, I have been unable to derive that algorithm. Nevertheless, I’ve decided to share with you find all my tests.

1.    First category of tests

As exposed above the first category of my tests concerns a cursor marked bind aware following an execution done using a bind variable that increments the count of bucket_id n°0. For example I have the below situation:

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID  COUNT
------------ ---------- ----------
0             0          5 ---> +1 execution
0             1          8
0             2          3

Looking at the above picture, you can understand that I’ve managed to execute my query in the following execution order

  • bind variable value ‘j10000’ 8 times  (increment count of bucket_id 1 to 8)
  • bind variable value ‘j100’ 5 times    (increment count of bucket_id 0 to 5)
  • bind variable value ‘j>million’ 3 times (increment count of bucket_id 2 to 3)

Of course these executions have been done in a way to avoid two adjacent bucket_id reaching the same count while the remaining bucket_id has a count =0. This is why I managed to have all bucket_id incremented to avoid bind aware situations exposed in Part II. To accomplish this you can for example start by executing your query using the ‘j10000’ bind variable 8 times then jump to the adjacent bucket_id n°0 using bind variable ‘j100’ execute it 5 times then go to the other bind variable value ‘j>million’ and execute it 3 times. Proceeding as such you will arrive exactly at the above situation exposed in v$sql_cs_histogram for the 6f6wzmu3yzm43 sql_id.

At this stage of the experiment, if you execute the same query using the first bind variable value ‘j100’, your will see that Oracle has made your cursor bind aware as shown below (a new child cursor n°1 appears):

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID COUNT
------------ ---------- ----------
1             0          1
1             1          0
1             2          0
0             0          5
0             1          8
0             2          3

2.    Second category of tests

The second category of my tests is the one that corresponds to a cursor becoming bind aware following an execution at bucket_id n° 1

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID COUNT
------------ ---------- ----------
0             0          8
0             1          3 + 1 execution
0             2          2

CHILD_NUMBER  BUCKET_ID COUNT
------------ ---------- ----------
1             0          0
1             1          1
1             2          0
0             0          8
0             1          3
0             2          2

The same precaution have been taken so that bind aware reasons exposed in Part II of the series are avoided before having the count of all bucket_id incremented

3.    Third category of tests

Finally the third category corresponds to a cursor becoming bind aware following an execution at bucket_id n° 2 as shown below:

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID  COUNT
------------ ---------- ----------
0             0          8
0             1          3
0             2          2  ----> + 1 execution

CHILD_NUMBER  BUCKET_ID  COUNT
------------ ---------- ----------
1             0          0
1             1          0
1             2          1
0             0          8
0             1          3
0             2          2

Looking at the above situation there is no apparent clue indicating what secret sauce ACS is using to mark a cursor bind aware when all bucket_id are concerned.

I have attached this tests with 3 bucket document where you can find several similar cases I have done and that have not put me in the right direction as well.

I hope that these 3 articles help someone.

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)