Adaptive Cursor Sharing

Posted on March 11, 2018March 28, 2018 by Saibal

Adaptive Cursor Sharing:

This document will explain step by step to analyze adaptive cursor sharing.

Let’s start with the basics. Let’s create a table as a copy of dba_objects.

SQL> create table t as select * from dba_objects;

Now, if we run a query like ‘select object_name from t where object_id=1027’, then it must go through the full process of hard parsing. Each time we put a literal as the object_id value, Oracle sees it as a new statement and does a hard parse. And as we know, a hard parse is an expensive operation, one that we must try to avoid as much as possible. Major problems that result from using literal values include the following:

There is a security threat as applications that concatenates literals are soft targets for SQL Injection attacks. Not using literals, i.e. using Bind Variables eliminates that threat.
For every statement that is hard parsed, cursors are not being shared, and more memory is being consumed.
Hard Parsing implicitly increases latch contention, as Oracle must latch the shared pool and library cache while doing a hard parse.

Okay, let’s first run the following three queries, each one using a different object_id, i.e. a new literal for each statement:

select object_name from t where object_id=1027;

select object_name from t where object_id=1028;

select object_name from t where object_id=1029;

So far, so good. So, let’s check how Oracle internally views each of these statements. For that let’s run the following statement:

SQL> select sql_text, sql_id, version_count, hash_value from v$sqlarea where sql_text like ‘select object_name from t%’;

And we can see the query of V$SQLAREA shows that the three statements require three different parent cursors. As shown by VERSION_COUNT, each parent cursor requires its own child cursor.

So, to overcome the problem of hard parsing every time, we can use bind variables to share the cursor.

Let’s have a look at the following example:

So, what we have done is that we have run three different statements with three different object_id values, but used a bind variable which we defined with the object_id, before running the statement. The expectation here is that Oracle will share the cursor, and define the object_id value at run time. Let’s see how it actually worked:

The VERSION_COUNT value of 1 indicates that the database reused the same child cursor rather than creating three separate child cursors. Using a bind variable made this reuse possible.

However, there is a catch. When a query uses literals, the optimizer can use the literal values to find the best plan. However, when a query uses bind variables, the optimizer must select the best plan without the presence of literals in the SQL text. This task can be extremely difficult. What Oracle does is, at the outset it peeks at the bind variable value for the initial hard parse, and uses that information to generate an execution plan for all subsequent values of the bind variable, thus leaving itself open to working with sub optimal plans, because the plan produced during the initial hard parse may or may not be the best plan for all values of the bind variable.

Let’s see whether supposedly similar statements can produce different execution plans or not.

Let’s first create an index on the object_id column of the table t.

create index i1 on t(object_id)

Then let’s run the following three statements and see the execution plan for each one of them.

In the third case where we are selecting a significantly larger number of rows from the table, the optimizer considers a full table scan more efficient than an index scan, thus validating what we were guessing-that execution plans do change for ‘similar-looking statements’ but with very different literals.

But before we move ahead let’s check the same three statements using bind variables. As a preparatory step before actually running our queries, let us flush the shared pool and buffer cache, just so we are sure about the results we get.

alter system flush shared_pool;

alter system flush buffer_cache;

It is clear that Oracle continues to use the plan developed during the initial hard parse, even though we know the INDEX RANGE SCAN is not the most optimal plan for object_id<19000, as we already saw a FULL TABLE SCAN occurring when we gave the optimizer the benefit of literals in the earlier example.

So, although there are some obvious benefits of using bind variables, it is definitely not the solution for all our SQL tuning problems. With bind peeking, the optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. The optimizer determines the cardinality of any WHERE clause condition as if literals had been used instead of bind variables. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, this plan may not be good for all values.

So herein comes the benefit of ADAPTIVE CURSOR SHARING. In adaptive cursor sharing, the database monitors data accessed over time for different bind values, ensuring the optimal choice of cursor for a specific bind value. Let’s say the optimizer chooses a particular plan for bind value 1000, then it can very well choose a different plan for bind value 19000, which may be relatively the more optimal plan for that value of the bind variable.

Thus, cursor sharing is “adaptive” because the cursor adapts its behavior so that the optimizer does not always choose the same plan for each execution or bind variable value. So, the optimizer automatically detects when different execution of a statement would benefit from different execution plans.

Adaptive cursor sharing works only if you are using bind variables and have a histogram on at least one of the columns that is being used in the WHERE clause of the query.

Let’s create a new table ‘S’ as a copy of dba_objects and gather statistics on it as well as create a histogram on the object_id column.

The next step would be to run a query on this table using a bind variable.

With Adaptive Cursor Sharing, two additional columns are there in V$SQL. IS_BND_SENSITIVE and IS_BIND_AWARE. A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable.

The database has examined the bind value when computing cardinality, and considers the query “sensitive” to plan changes based on different bind values. The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.

The optimizer uses the following criteria to decide whether a cursor is bind-sensitive:

The optimizer has peeked at the bind values to generate cardinality estimates.
The bind is used in an equality or a range predicate.

For each execution of the query with a new bind value, the database records the execution statistics for the new value and compares them to the execution statistics for the previous value. If execution statistics vary greatly, then the database marks the cursor bind-aware.

At this point, let us see what is the status of the columns IS_BIND_SENSITIVE and IS_BIND_AWARE for the query we ran. We notice that IS_BIND_SENSITIVE is flagged to ‘Y’ while IS_BIND_AWARE is flagged as ‘N’.

This means that Oracle is ‘sensitive’ to the fact that bind variable is being used and the optimal plan is predicated on the bind variable value, i.e. whatever value we pass in the bind variable subsequently will determine whether the plan is good, bad, or maybe somewhere in-between.

Let’s try one more time with a bind variable value not too different from what we used earlier:

The cursor continues to be bind sensitive. Now, let us do something different. Let us use a bind variable value which is significantly different, and let us see what we get:

So Oracle, now in its wisdom decides to make the cursor bind aware as well. Because the cursor is bind-aware, the database does the following:

Determines whether the cardinality of the new values falls within the same range as the stored cardinality. If it falls within that range it reuses the execution plan in the existing child cursor.

What happens if it doesn’t fall in the range of stored cardinality? In that case, Oracle does a hard parse and creates a new child cursor with a different execution plan, stores metadata about the predicate, including the cardinality of the bound values, in the cursor and executes the cursor.

So, the essential point here is, we now have two child cursors catering to two different sets of bind variable values.

Great! What next? Here it starts getting really interesting. Let’s say we now use a bind variable value which doesn’t match the stored cardinality of the existing child cursors, so the optimizer goes through the painful process of hard parsing and creates another child cursor which it then executes. Assume this execution plan used an index. Oracle then checks and sees that one of the existing child cursors had also used an index scan. Oracle now tries to optimize things by merging the two child cursors containing index access plans. This implicitly involves storing the combined cardinality statistics into one child cursor and deleting the other one. This is really efficient, because instead of two child cursors, we now have one child cursor which can be reused, and it sores the combined cardinality of more than one bind variable value.

So, in conclusion it can be said that Adaptive Cursor Sharing is a really cool feature in Oracle that allows us to use to use Bind Variables in an unfettered way, leveraging the reuse of child cursors without having to compromise with living with suboptimal execution plans.

********************************************************************************************************************************************

Comparison between exadata and non-exadata executions test case

Posted on March 9, 2018March 29, 2018 by debasis maity

First we need to load data

SQL> alter session set tracefile_identifier=’ABC’;

Session altered.

Elapsed: 00:00:00.01
SQL> alter system set events ‘10046 trace name context forever, level 12′;

System altered.

Elapsed: 00:00:00.18
SQL> create table t1(c1 number,c2 char(2000));

Table created.

Elapsed: 00:00:00.03
SQL> set autotrace on;
SQL> set autotrace traceonly;
SQL> insert into t1 select rownum,’A’ from dual connect by rownum<100000;

99999 rows created.

Elapsed: 00:00:22.81

Exadata plan

Non-Exadata plan

Tkprof-exadata

tkprof-non-exadata

Statistics of how many blocks in table

SQL> select BLOCKS from user_tables where table_name=’T1′;

BLOCKS
———-
67217

Full table scan with count

SQL> select count(1) from t1;

Elapsed: 00:00:00.06

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Full table scan with all data from table.Buffer fetched will be much higher than count

SQL> select * from t1;

199998 rows selected.

Elapsed: 00:00:01.62

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Create index in both exadata and non-exadata

SQL> create index i1_t1 on t1(c1);

Index created.

Elapsed: 00:00:00.22
SQL> exec dbms_stats.gather_table_stats(user,’T1′);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.34

Statistics of how many blocks in Index

SQL> select BLEVEL,LEAF_BLOCKS from user_indexes where index_name=’I1_T1′;

BLEVEL LEAF_BLOCKS
———- ———–
1 443

Index range scan

SQL> select * from t1 where c1 between 1 and 15;

30 rows selected.

Elapsed: 00:00:00.00

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Retrieve single block for index scan with table access

SQL> select * from t1 where c1=1;

Elapsed: 00:00:00.00

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Retrieve single block for index scan without table access

SQL> select c1 from t1 where c1=1;

Elapsed: 00:00:00.01

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Index fast scan to check behavior of index access

SQL> select count(c1) from t1;

Elapsed: 00:00:00.02

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

SPM oracle 12c test case

Posted on March 8, 2018May 11, 2018 by debasis maity

A.Enabling SPM manually to stabilize plan for a particular query

For this test case I will create a table with skewed data so that for one bind value it will go for one execution plan and for other bind value,it will go for another execution plan.Ideally this should be good to go for different execution plan depending on the bind value.But to show SPM, I was intentionally picked this test case to show how SPM can stabilize the plan even our bind value gets changed.

Test case:-

1.Let us create the data first.

I will create 2 tables and create some skewed data for example 99999 rows for ‘A’ and 99 rows for ‘B’

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.05


SQL> create table t1(c1 number,c2 char(1000));

Table created.

Elapsed: 00:00:00.14


SQL> insert into t1 select rownum,'A' from dual connect by rownum<100000;

99999 rows created.

Elapsed: 00:00:13.41


SQL> commit;

Commit complete.

Elapsed: 00:00:00.03


SQL> insert into t1 select rownum,'B' from dual connect by rownum<100;

99 rows created.

Elapsed: 00:00:00.01

SQL> commit;

Commit complete.

Elapsed: 00:00:00.01


SQL> create index i1_t1 on t1(c2);


SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.89

2.Now we will check the execution plan.It shows “INDEX FAST FULL SCAN” for below cases.

SQL> exec :c1 := 'A';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00


SQL> select count(1) from t1 where c2=:c1;

  COUNT(1)

----------

     99999

Elapsed: 00:00:02.13


SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

SQL_ID  fb8r4b2qtw3p3, child number 0

-------------------------------------

select count(1) from t1 where c2=:c1

Plan hash value: 698100246

-------------------------------------------------------------------------------

| Id  | Operation             | Name | Rows | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      | | | | 3735 (100)|          |

|   1 | SORT AGGREGATE       | | 1 | 1001 |           | |

|*  2 |  INDEX FAST FULL SCAN| I1_T1 | 99998 |    95M| 3735 (1)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("C2"=:C1)

19 rows selected.

Elapsed: 00:00:00.04


SQL> exec :c1 := 'B';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

SQL> select count(1) from t1 where c2=:c1;

  COUNT(1)

----------

        99

Elapsed: 00:00:00.07


SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

SQL_ID  fb8r4b2qtw3p3, child number 0

-------------------------------------

select count(1) from t1 where c2=:c1

Plan hash value: 698100246

-------------------------------------------------------------------------------

| Id  | Operation             | Name | Rows | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      | | | | 3735 (100)|          |

|   1 | SORT AGGREGATE       | | 1 | 1001 |           | |

|*  2 |  INDEX FAST FULL SCAN| I1_T1 | 99998 |    95M| 3735 (1)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("C2"=:C1)

19 rows selected.

Elapsed: 00:00:00.04

3.From above output ,Both cases we get same cost but ideally cost should be different if we could have histograms.I expect second query with less number of rows should go for index range scan.

SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'FOR COLUMNS C2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.37

SQL> exec :c1 := 'B';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

SQL> select count(1) from t1 where c2=:c1;

  COUNT(1)

----------

        99

Elapsed: 00:00:00.00

SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

SQL_ID  fb8r4b2qtw3p3, child number 1

-------------------------------------

select count(1) from t1 where c2=:c1

Plan hash value: 2778874372

---------------------------------------------------------------------------

| Id  | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  | | |  | 20 (100)| |

|   1 | SORT AGGREGATE   | | 1 | 1001 |            | |

|*  2 |  INDEX RANGE SCAN| I1_T1 |    98 | 98098 | 20 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("C2"=:C1)

19 rows selected.

Elapsed: 00:00:00.06


SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

4.Now We will check what was impact of plan change from AWR history snapshots.

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
col bind_value for a10
break on plan_hash_value on startup_time skip 1
select ss.snap_id,s.optimizer_cost cost ,ss.instance_number node, begin_interval_time, s.sql_id, s.plan_hash_value,
nvl(s.executions_delta,0) execs,
(s.elapsed_time_delta/decode(nvl(s.executions_delta,0),0,1,s.executions_delta))/1000000 avg_etime,
(s.buffer_gets_delta/decode(nvl(s.buffer_gets_delta,0),0,1,s.executions_delta)) avg_lio,sss.value_string bind_value
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS,dba_hist_sqlbind SSS
where s.sql_id = nvl('&sql_id','fb8r4b2qtw3p3')
and ss.snap_id = S.snap_id and s.snap_id=SSS.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0 and s.sql_id=sss.sql_id
order by 1, 2, 3
/

5.Now I will create sql tuning set for the sql_id

BEGIN  
DBMS_SQLTUNE.CREATE_SQLSET(    sqlset_name => 'MySTS01',    description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(begin_snap=>11336, end_snap=>11337,basic_filter=>'sql_id = ''fb8r4b2qtw3p3''',attribute_list=>'ALL')
              ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'MySTS01', populate_cursor=>cur);
  CLOSE cur;
END;
/

6.Let us check sql tuning set result

-- List out SQL Tuning Set contents to check we got what we wanted

SELECT
  first_load_time          ,
  executions as execs              ,
  parsing_schema_name      ,
  elapsed_time  / 1000000 as elapsed_time_secs  ,
  cpu_time / 1000000 as cpu_time_secs           ,
  buffer_gets              ,
  disk_reads               ,
  direct_writes            ,
  rows_processed           ,
  fetches                  ,
  optimizer_cost           ,
  sql_plan                ,
  plan_hash_value          ,
  sql_id                   ,
  sql_text
   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'MySTS01')
)

7.Now let us pin the correct plan_hash_value for which I need my plan to be stabilized in spite of changing the bind value .

SQL> DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'MySTS01',
    basic_filter=>'plan_hash_value = ''2778874372'''
    );
END;
/  

PL/SQL procedure successfully completed.

8.Let us check whether my sql is taking base lined plan or not.

SQL> variable c1 char;

SQL> exec :c1 := 'A';

PL/SQL procedure successfully completed.

SQL> select count(1) from t1 where c2=:c1;

  COUNT(1)

----------

     99999

SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

SQL_ID  fb8r4b2qtw3p3, child number 2

-------------------------------------

select count(1) from t1 where c2=:c1

 Plan hash value: 2778874372

---------------------------------------------------------------------------

| Id  | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  | | |  | 14328 (100)| |

|   1 | SORT AGGREGATE   | | 1 | 1001 |            | |

|*  2 |  INDEX RANGE SCAN| I1_T1 | 99998 |    95M| 14328 (1)| 00:00:01 |

---------------------------------------------------------------------------

 Predicate Information (identified by operation id):

---------------------------------------------------
   2 - access("C2"=:C1)

Note

-----

   - SQL plan baseline SQL_PLAN_8w2csch0r83kz7a01406e used for this statement

 


SQL> variable c1 char;

SQL>  exec :c1 := 'B';

PL/SQL procedure successfully completed.

SQL> select count(1) from t1 where c2=:c1;

   COUNT(1)

----------

        99

SQL> select * from table (dbms_xplan.display_cursor());

 PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

SQL_ID  fb8r4b2qtw3p3, child number 2

-------------------------------------

select count(1) from t1 where c2=:c1
Plan hash value: 2778874372
---------------------------------------------------------------------------

| Id  | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  | | |  | 14328 (100)| |

|   1 | SORT AGGREGATE   | | 1 | 1001 |            | |

|*  2 |  INDEX RANGE SCAN| I1_T1 | 99998 |    95M| 14328 (1)| 00:00:01 |

---------------------------------------------------------------------------

Note

-----

   - SQL plan baseline SQL_PLAN_8w2csch0r83kz7a01406e used for this statement

B.Influence plan to add hint without changing main sql query

In this test case I will first create a table with index on column C2. First I will check this query if it picks up index.Then I will force full table scan with hint.After that I will stabilize plan with SPM in such a way that oracle CBO will choose full table scan with out any hint.

1.Let us check the query is picking the index

SQL> select count(*) from t1 where c2= 'A';

COUNT(*)
 ----------
 99999



SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
 --------------------------------------------------------------------------------
 SQL_ID 43u8cjvr7ctcz, child number 0
 -------------------------------------
 select count(*) from t1 where c2= 'A'

Plan hash value: 698100246

-------------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 -------------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 3824 (100)| |
 | 1 | SORT AGGREGATE | | 1 | 1001 | | |

|* 2 | INDEX FAST FULL SCAN| I1_T1 | 50049 | 47M| 3824 (1)| 00:00:01 |
 -------------------------------------------------------------------------------

Predicate Information (identified by operation id):
 ---------------------------------------------------

2 - filter("C2"='A')

19 rows selected.

2.Now let me force full table scan hint.

SQL> select /*+ full(t1) */ count(*) from t1 where c2= 'A'

  COUNT(1)

----------

     99999

 
SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
 --------------------------------------------------------------------------------
 SQL_ID cyxfgfy9bav2s, child number 0
 -------------------------------------
 select /*+ full(t1) */ count(*) from t1 where c2= 'A'

Plan hash value: 3724264953

---------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 ---------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 4122 (100)| |
 | 1 | SORT AGGREGATE | | 1 | 1001 | | |

|* 2 | TABLE ACCESS FULL| T1 | 50049 | 47M| 4122 (1)| 00:00:01 |
 ---------------------------------------------------------------------------

Predicate Information (identified by operation id):
 ---------------------------------------------------

2 - filter("C2"='A')

3.Let me load plan from cache to base line and will decide which plan we need to stabilize

variable cnt number
 exec :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => '43u8cjvr7ctcz');

variable cnt number
 exec :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => 'cyxfgfy9bav2s');

4.Let me check sql_handle now for both the query with hint and without hint

5.Now We need to disable undesirable plan.In this case,I do not want my query to go for index scan hence need to disable from sql plan baseline.

SQL> variable cnt number

exec :cnt := dbms_spm.alter_sql_plan_baseline (-
 SQL_HANDLE => 'SQL_0519f9d2859107e0',-
 PLAN_NAME => 'SQL_PLAN_0a6gtua2t21z0ecb116c3',-
 ATTRIBUTE_NAME => 'enabled',-
 ATTRIBUTE_VALUE => 'NO');SQL> > > > >

PL/SQL procedure successfully completed.

6.Now We need to load hinted plan into baseline

sql_id and plan_hash_value should be from hinted plan

sql_handle from non-hinted plan.

SQL> variable cnt number
 exec :cnt := dbms_spm.load_plans_from_cursor_cache(-
 sql_id => 'cyxfgfy9bav2s', -
 plan_hash_value => 3724264953, -
 sql_handle => 'SQL_0519f9d2859107e0');

7.Now we can see even we not used hint,first query is going for full table scan instead of index scan.

SQL> select count(*) from t1 where c2= 'A';

COUNT(*)
 ----------
 99999

SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
 --------------------------------------------------------------------------------
 SQL_ID 43u8cjvr7ctcz, child number 2
 -------------------------------------
 select count(*) from t1 where c2= 'A'

Plan hash value: 3724264953

---------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 ---------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 4122 (100)| |
 | 1 | SORT AGGREGATE | | 1 | 1001 | | |

|* 2 | TABLE ACCESS FULL| T1 | 50049 | 47M| 4122 (1)| 00:00:01 |
 ---------------------------------------------------------------------------

Predicate Information (identified by operation id):
 ---------------------------------------------------

2 - filter("C2"='A')

Note
 -----
 - SQL plan baseline SQL_PLAN_0a6gtua2t21z0616acf47 used for this statement

PLAN_TABLE_OUTPUT
 --------------------------------------------------------------------------------

23 rows selected.

8.Now We will check what SQL plan is enabled

Logical IO vs Physical IO vs Consistent gets oracle

Posted on March 3, 2018March 29, 2018 by debasis maity

Logical IO vs Physical IO vs Consistent gets

I have created 1 GB table T1.

Let me check how many blocks are there in the table after I have analyzed it.

SQL> select BLOCKS from user_tables where table_name=’T1′;

BLOCKS

———-

139153

Autotrace report

Let me clean buffer cache and check the auto trace report.

SQL>set autotrace traceonly;

SQL> select * from sh.t1;

415584 rows selected.

Execution Plan

———————————————————-

Plan hash value: 3617692013

————————————————————————–

————————————————————————–

| 0 | SELECT STATEMENT | | 415K| 797M| 37716 (1)| 00:00:02 |

| 1 | TABLE ACCESS FULL| T1 | 415K| 797M| 37716 (1)| 00:00:02 |

————————————————————————–

Statistics

———————————————————-

1 recursive calls

0 db block gets

166620 consistent gets

138907 physical reads

0 redo size

9193927 bytes sent via SQL*Net to client

305307 bytes received via SQL*Net from client

27707 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

415584 rows processed

BLOCKS READ ALGORITHM

A Concepual Knowledge on Oracle Blocks Read Algorithm is as under :

User Issues a Query.
Query is Parsed and Optimal Execution path is generated and stored in the Library Cache.
Based on the Execution Path, required Index or Table block is searched in the the Cache.
If the block is not found in the cache, a disk read request is made, and the block is read into the Cache.
The block, from the Cache is then read into a private memory area (UGA) of the User.
Once the Block is read into the private memory of the User, the required row is fetched.

The value of Consistent Read is incremented each time a new block is read into a private memory area of the User, which also means that, a single block, if read multiple times, affect the value of the Consistent Read. While an in-efficient SQL contributes largely to Consistent Read, but one factor that also have significant impact is ARRAY SIZE. I am not going to discuss about In-efficient Queries, but will write on the calculation that undergoes and impact Consistent Gets.

How consistent gets ORACLE calculated:-

Calculation:-No of physical block+total no of rows fetched/array size

By default array size is 15.

So In my case

138907+415584/15=166620

When you fetched 15 rows, Oracle paused, gave you the data. When you went back to get the next 15, it got the buffer again to resume your query

PHYSICAL IO and LOGICAL IO

db_block_gets + consistent_gets = LOGICAL IO=166620

physical_reads( typically no of block in case of full scan) = PHYSICAL IO=138907

TKPROF command

SQL> alter session set tracefile_identifier=’ABC’;

Session altered.

SQL> alter system set events ‘10046 trace name context forever, level 12’;

System altered.

SQL>select * from t1;

SQL>alter session set events ‘10046 trace name context off’

tkprof ERMAN_ora_26624_ABC.trc abc.txt sys=no sort='(prsela,exeela,fchela)’ explain=test/test

TKPROF output

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

——- —— ——– ———- ———- ———- ———- ———-

Parse 22 0.00 0.01 1 63 2 0

Execute 20 0.11 0.67 15 417 22 3

Fetch 27727 0.20 0.97 138909 166680 0 415654

——- —— ——– ———- ———- ———- ———- ———-

total 27769 0.33 1.65 138925 167160 24 415657

Misses in library cache during parse: 13

Misses in library cache during execute: 6

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

—————————————- Waited ———- ————

Disk file operations I/O 30 0.00 0.00

SQL*Net message to client 27741 0.00 0.01

SQL*Net message from client 27741 154.26 345.66

SGA: allocation forcing component growth 201 0.10 19.90

SQL*Net break/reset to client 6 0.00 0.00

DLM cross inst call completion 1 0.52 0.52

db file sequential read 57 0.00 0.04

db file scattered read 1 0.00 0.00

log file sync 1 0.00 0.00

direct path read 68 0.00 0.16

In AWR

In the below part it is evident we have 46 IOPS.

Instance Activity Stats

physical read total IO requests	2,667	45.47	190.50
physical read total bytes	1,159,954,432	19,777,569.17	82,853,888.00

IOSTAT

SQL> show parameter multi;

NAME TYPE VALUE
———————————— ———– ——————————
db_file_multiblock_read_count integer 128

SO 128 Oracle blocks could be written in one sequential I/O (In case of Full table scan) or individual “random” I/Os (In case of index scan)

Device Status Output

Units	Definition
rrqm/s	The number of read requests merged per second queued to the device.
wrqm/s	The number of write requests merged per second queued to the device.
r/s	The number of read requests issued to the device per second.
w/s	The number of write requests issued to the device per second.
rMB/s	The number of megabytes read from the device per second. (I chose to used MB/s for the output.)
wMB/s	The number of megabytes written to the device per second. (I chose to use MB/s for the output.)
avgrq-sz	The average size (in sectors) of the requests issued to the device.
avgqu-sz	The average queue length of the requests issued to the device.
await	The average time (milliseconds) for I/O requests issued to the device to be served. This includes the time spent by the requests in queue and the time spent servicing them.
r_await	The average time (in milliseconds) for read requests issued to the device to be served. This includes the time spent by the requests in queue and the time spent servicing them.
w_await	The average time (in milliseconds) for write requests issued to the device to be served. This includes the time spent by the requests in queue and the time spent servicing them.
svctm	The average service time (in milliseconds) for I/O requests issued to the device. Warning! Do not trust this field; it will be removed in a future version of sysstat.
%util	Percentage of CPU time during which I/O requests were issued to the device (bandwidth utilization for the device). Device saturation occurs when this values is close to 100%.

Oracle 12c gather statistics oracle deep dive

Posted on February 22, 2018March 29, 2018 by debasis maity

I referred some reference from my favorite Tim Hall blog (ORACLE_BASE)

A.Basic of optimizer statistics: –

The optimizer cost model relies on statistics collected about the objects involved in a query, and the database and host where the query runs. Statistics are critical to the optimizer’s ability to pick the best execution plan for a SQL statement.

Object	What influences statistics	Views
Table Statistics	1.No of rows. 2.No of blocks. 3.Avg row length.	1.DBA_TABLES 2.DBA_TAB_STATISTICS
Column Statistics	1.No of distinct values. 2.No of nulls 3.Data distribution(Histogram) 4.Extended statistics	1.DBA_TAB_COLS 2.DBA_TAB_COL_STATISTICS 3.USER_PART_COL_STATISTICS 4.USER_SUBPART_COL_STATISTICS
Index Statistics	No of distinct values. No of leaf blocks. Clustering g factor	1.DBA_INDEXES 2.DBA_IND_STATISTICS
Histograms	Data distribution in columns	DBA_TAB_HISTOGRAMS
Extended Stats	Relation between data stored in different columns of the same table	DBA_TAB_COL_STATISTICS
System statistics	I/O performance and utilization. CPU performance and utilization	sys.aux_stats$

B.Configuring Automatic Optimizer Statistics Collection Using Cloud Control FOR gather statistics oracle

Please go to “Administration”->”Oracle Schedure”->”Automated Maintenance Tasks”

You can see 3 default automatic jobs scheduled. You must choose “Optimizer Statistics Gathering”

Now click on “Optimizer Statistics Gathering”

You may click on “Global Statistics Gathering Options” for viewing configured parameters.

You must choose “Configure” button to enable or disable /Change Window.

You may check by sqlplus also to check status of Automatics Optimizer Stats Collection

SQL> COL CLIENT_NAME FORMAT a31

SELECT CLIENT_NAME, STATUS

FROM DBA_AUTOTASK_CLIENT

WHERE CLIENT_NAME = ‘auto optimizer stats collection’;

SQL> 2 3

CLIENT_NAME STATUS

——————————- ——–

auto optimizer stats collection ENABLED

C.Manual Statistics Gather standard parameters FOR gather statistics oracle

Preference	Description	Default (11gR2)	Scope	Version
CASCADE	Determines if index stats should be gathered for the current table (TRUE, FALSE, AUTO_CASCADE).	DBMS_STATS.AUTO_CASCADE	G, D, S, T	10gR1+
DEGREE	Degree of parallelism (integer or DEFAULT_DEGREE).	DBMS_STATS.DEFAULT_DEGREE	G, D, S, T	10gR1+
ESTIMATE_PERCENT	Percentage of rows to sample when gathering stats (0.000001-100 or AUTO_SAMPLE_SIZE).	DBMS_STATS.AUTO_SAMPLE_SIZE	G, D, S, T	10gR1+
METHOD_OPT	Controls column statistics collection and histogram creation.	FOR ALL COLUMNS SIZE AUTO	G, D, S, T	10gR1+
NO_INVALIDATE	Determines if dependent cursors should be invalidated as a result of new stats on objects (TRUE, FALSE or AUTO_INVALIDATE).	DBMS_STATS.AUTO_INVALIDATE	G, D, S, T	10gR1+
AUTOSTATS_TARGET	Determines which objects have stats gathered (ALL, ORACLE, AUTO).	AUTO	G	10gR2+
GRANULARITY	The granularity of stats to be collected on partitioned objects (ALL, AUTO, DEFAULT, GLOBAL, ‘GLOBAL AND PARTITION’, PARTITION, SUBPARTITION).	AUTO	G, D, S, T	10gR2+
PUBLISH	Determines if gathered stats should be published immediately or left in a pending state (TRUE, FALSE).	TRUE	G, D, S, T	11gR2+
INCREMENTAL	Determines whether incremental stats will be used for global statistics on partitioned objects, rather than generated using table scans (TRUE, FALSE).	FALSE	G, D, S, T	11gR2+
CONCURRENT	Should objects statistics be gathered on multiple objects at once, or one at a time (MANUAL, AUTOMATIC, ALL, OFF).	OFF	G	12cR1+
GLOBAL_TEMP_TABLE_STATS	Should stats on global temporary tables be session-specific or shared between sessions (SHARED, SESSION).	SESSION	G, D, S	12cR1+
INCREMENTAL_LEVEL	Which level of synopses should be collected for incremental partitioned statistics (TABLE, PARTITION).	PARTITION	G, D, S, T	12cR1+
INCREMENTAL_STALENESS	How is staleness of partition statistics determined (USE_STALE_PERCENT, USE_LOCKED_STATS, NULL).	NULL	G, D, S, T	12cR1+
TABLE_CACHED_BLOCKS	The number of blocks cached in the buffer cache during calculation of index cluster factor. Jonathan Lewis recommends “16” as a sensible value.	1	G, D, S, T	12cR1+
OPTIONS	Used for the OPTIONS parameter of the GATHER_TABLE_STATS procedure (GATHER, GATHER AUTO).	GATHER	G, D, S, T	12cR1+

D.Some standard statistics gather FOR gather statistics oracle

Gather statistics:-

EXEC DBMS_STATS.gather_database_stats;

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’); EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’, estimate_percent => 15);

EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’, estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’);

EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’, estimate_percent => 15);

EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’, estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_table_stats(ownname=>’C##TEST’,tabname=>’TT1′,method_opt=>’FOR COLUMNS C1′);

Delete statistics:-

EXEC DBMS_STATS.delete_database_stats;

EXEC DBMS_STATS.delete_schema_stats(‘SCOTT’);

EXEC DBMS_STATS.delete_table_stats(‘SCOTT’, ‘EMP’);

EXEC DBMS_STATS.delete_column_stats(‘SCOTT’, ‘EMP’, ‘EMPNO’);

EXEC DBMS_STATS.delete_index_stats(‘SCOTT’, ‘EMP_PK’);

E.Determining When Optimizer Statistics Are Stale FOR gather statistics oracle

A.First create a demo table and load some data.

SQL> set linesize 300;

SQL> create table tab1(c1 number(10),c2 char(100));

Table created.

SQL> insert into tab1 select rownum,’A’ from dual connect by rownum<10000;

9999 rows created.

SQL> commit;

Commit complete.

B.Now statistics are not populated.

SQL> select NUM_ROWS,BLOCKS,STALE_STATS from dba_tab_statistics where table_name=’TAB1′;

NUM_ROWS BLOCKS STA

———- ———- —

C.Let me gather statistics now

SQL> exec dbms_stats.gather_table_stats(‘TEST’,’TAB1′);

PL/SQL procedure successfully completed.

SQL> select NUM_ROWS,BLOCKS,STALE_STATS from dba_tab_statistics where table_name=’TAB1′;

NUM_ROWS BLOCKS STA

———- ———- —

9999 244 NO

D.Now again let me load more data

SQL> insert into tab1 select rownum,’A’ from dual connect by rownum<10000;

9999 rows created.

SQL> commit;

Commit complete.

E.Still DBA_TAB_STATISTICS shows STALE statistics NO.

SQL> select NUM_ROWS,BLOCKS,STALE_STATS from dba_tab_statistics where table_name=’TAB1′;

NUM_ROWS BLOCKS STA

———- ———- —

9999 244 NO

F.Now let me flush/write the database monitoring information from memory to disk

SQL> BEGIN

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

END;

/ 2 3 4

PL/SQL procedure successfully completed.

G.Now the statistics will show STALE

SQL> select NUM_ROWS,BLOCKS,STALE_STATS from dba_tab_statistics where table_name=’TAB1′;

NUM_ROWS BLOCKS STA

———- ———- —

9999 244 YES

F.Gathering Statistics for Fixed Objects FOR gather statistics oracle

Fixed objects are dynamic performance tables and their indexes. These objects record current database activity. Unlike other database tables, the database does not automatically use dynamic statistics for SQL statement referencing X$ tables when optimizer statistics are missing. Instead, the optimizer uses predefined default values. These defaults may not be representative and could potentially lead to a suboptimal execution plan. Thus, it is important to keep fixed object statistics current.

To gather schema statistics using GATHER_FIXED_OBJECTS_STATS:

Start SQL*Plus, and connect to the database with the appropriate privileges for the procedure that you intend to run.
Run the DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure, specifying the desired parameters. Chapter 12 Gathering Optimizer Statistics Manually

G.Gathering Statistics for Volatile Tables Using Dynamic Statistics FOR gather statistics oracle

A.Let us think my table TAB2 is extremely volatile. So We want dynamic statistics to be collected on the fly to avoid optimizer gather wrong statistics.

First let me create the demo table.

SQL> create table tab2(c1 number(10),c2 char(100));

Table created.

Let me gather statistics now.

SQL> exec dbms_stats.gather_table_stats(‘TEST’,’TAB2′);

PL/SQL procedure successfully completed.

SQL> select NUM_ROWS,BLOCKS,STALE_STATS from dba_tab_statistics where table_name=’TAB2′;

NUM_ROWS BLOCKS STA

———- ———- —

0 0 NO

Now I need to load data again

SQL> insert into tab2 select rownum,’A’ from dual connect by rownum<10000;

9999 rows created.

SQL> commit;

Commit complete.

Now check explain plan does not show proper cardinality

SQL> set autotrace traceonly;

SQL> select * from tab2;

9999 rows selected.

Execution Plan

———————————————————-

Plan hash value: 2156729920

————————————————————————–

————————————————————————–

| 0 | SELECT STATEMENT | | 1 | 115 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| TAB2 | 1 | 115 | 2 (0)| 00:00:01 |

Now We need to delete the statistics and lock the statistics. So optimizer will not gather statistics if there is any change in table.

sqlplus test/test

SQL> exec dbms_stats.delete_table_stats(‘TEST’,’TAB2′);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.lock_table_stats(‘TEST’,’TAB2′);

PL/SQL procedure successfully completed.

Now as there is no statistics, Oracle optimizer uses dynamic sampling.

The following table describes the levels for dynamic statistics. Note the following:

If dynamic statistics are not disabled, then the database may choose to use dynamic statistics when a SQL statement uses parallel execution.
If the OPTIMIZER_ADAPTIVE_FEATURES initialization parameter is true, then the optimizer uses dynamic statistics when relevant SQL plan directives exist. The database maintains the resulting statistics in the server result cache, making them available to other queries.

SQL> set autotrace traceonly;

SQL> select * from tab2;

9999 rows selected.

Execution Plan

———————————————————-

Plan hash value: 2156729920

————————————————————————–

————————————————————————–

| 0 | SELECT STATEMENT | | 8435 | 947K| 68 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| TAB2 | 8435 | 947K| 68 (0)| 00:00:01 |

————————————————————————–

Note

—–

– dynamic statistics used: dynamic sampling (level=2)

H.Enabling Concurrent Statistics Gathering FOR gather statistics oracle

Oracle Database 11g Release 2 (11.2.0.2) introduces a new statistics gathering mode, ‘concurrent statistics gathering’. The goal of this new mode is to enable a user to gather statistics on multiple tables in a schema (or database), and multiple (sub)partitions within a table concurrently. Gathering statistics on multiple tables and (sub)partitions concurrently can reduce the overall time it takes to gather statistics by allowing Oracle to fully utilize a multi-processor environment.

To enable concurrent statistics gathering, use the DBMS_STATS.SET_GLOBAL_PREFS procedure to set the CONCURRENT preference. Possible values are as follows:

MANUAL Concurrency is enabled only for manual statistics gathering.
AUTOMATIC Concurrency is enabled only for automatic statistics gathering.
ALL Concurrency is enabled for both manual and automatic statistics gathering.
OFF Concurrency is disabled for both manual and automatic statistics gathering. This is the default value.

This tutorial in this section explains how to enable concurrent statistics gathering.

Create a partitioned table.

SQL> create table t1 (id number, sometext varchar2(50),my_date date) tablespace data partition by hash (id) partitions 16;

Please note that concurrency is OFF.

SQL> SELECT DBMS_STATS.GET_PREFS(‘CONCURRENT’) FROM DUAL;

DBMS_STATS.GET_PREFS(‘CONCURRENT’)

——————————————————————————–

OFF

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ‘DEFAULT_PLAN’;

System altered.

Check if you have sufficient job process

SQL> show parameter job;

NAME TYPE VALUE

———————————— ———– ——————————

job_queue_processes integer 1000

Now you change concurrency to ‘ALL’

SQL> BEGIN

DBMS_STATS.SET_GLOBAL_PREFS(‘CONCURRENT’,’ALL’);

END;

/ 2 3 4

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_STATS.GET_PREFS(‘CONCURRENT’) FROM DUAL;

DBMS_STATS.GET_PREFS(‘CONCURRENT’)

——————————————————————————–

ALL

Create a procedure to parallel insert data (30 sessions will insert data parallely)

SQL> create or replace procedure manyinserts as

v_m number;

begin

for i in 1..1000000 loop

select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;

insert /*+ new2 */ into t1 values (v_m, ‘DOES THIS’||dbms_random.value(),sysdate);

end loop;

commit;

end;

Procedure created.

SQL> create or replace procedure manysessions as

v_jobno number:=0;

begin

FOR i in 1..30 LOOP

dbms_job.submit(v_jobno,’manyinserts;’, sysdate);

END LOOP;

commit;

end;

Procedure created.

SQL> exec manysessions;

PL/SQL procedure successfully completed.

Now gather schema in another session.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘TEST’);

PL/SQL procedure successfully completed.

Please check status now.

SQL> /

SQL> SET LINESIZE 1000

SQL> COLUMN TARGET FORMAT a8

COLUMN TARGET_TYPE FORMAT a25

SQL> SQL> COLUMN JOB_NAME FORMAT a14

COLUMN START_TIME FORMAT a40

SELECT TARGET, TARGET_TYPE, JOB_NAME,

SQL> SQL> 2 TO_CHAR(START_TIME, ‘dd-mon-yyyy hh24:mi:ss’)

3 FROM DBA_OPTSTAT_OPERATION_TASKS

4 WHERE STATUS = ‘IN PROGRESS’

AND OPID = (SELECT MAX(ID)

5 6 FROM DBA_OPTSTAT_OPERATIONS

WHERE OPERATION = ‘gather_schema_stats’);

TARGET TARGET_TYPE JOB_NAME TO_CHAR(START_TIME,’DD-MON-YY

——– ————————- ————– —————————–

TEST.T1 TABLE 20-feb-2018 02:22:41

TEST.T1. TABLE PARTITION 20-feb-2018 02:22:41

SYS_P786

I.Incremental Statistics FOR gather statistics oracle

:-

In typical data warehousing environment existence of huge partitioned tables is very common, gathering statistics on such tables is challenging tasks. For partitioned tables there are two types of statistics Global and Partition level statistics. Gathering global statistics is very expensive and resource consuming operation as it scans whole table. Hence most of the time people use to reduce estimate_percent down to less than 1 percent. This does helps in reducing time taken to gather stats but may not be sufficient to represent the data distribution. Gathering partition level statistics is not so expensive as it gathers only for the partitions where data has been changed.

Traditionally statistics are gathered in two phase

Scan complete table to gather Global statistics
Scan only the partitions where data has been changed

Obviously global stats can be derived by using partition level stats like say for example number of rows at table level = just sum number of rows from all the partitions. But global stats like NDV(Number of Distinct Values) which is very important in calculating cardinality can’t be derived so easily. The only way to derive them is by scanning the whole table.

These synopsis data are stored in WRI$_OPTSTAT_SYNOPSIS$ and WRI$_OPTSTAT_SYNOPSIS_HEAD$ tables residing in SYSAUX tablespace. Table WRI$_OPTSTAT_SYNOPSIS$ will grow enormously as there will be individual synopsis created for each hash proportional to distinct value existing at table,partition and column level. Table WRI$_OPTSTAT_SYNOPSIS_HEAD$ will have each record for every table, partition, and column. In 11.1 release gathering incremental statistics would take longer time if you have wide tables with many partitions due to delete statement working on WRI$_OPTSTAT_SYNOPSIS$ table. In 11.2 this issue has been resolved by Range-Hash partitioning the WRI$_OPTSTAT_SYNOPSIS$ table.

Create a range based partition

CREATE TABLE t_range

(ID NUMBER,

CREATE_DATE DATE,

NAME CHAR(100))

PARTITION BY RANGE(CREATE_DATE)

INTERVAL(NUMTODSINTERVAL(1,’DAY’)) STORE IN (tbs1,tbs2,tbs3,tbs4)

(PARTITION part_old VALUES LESS THAN (TO_DATE(’04-FEB-2018′, ‘DD-MON-YYYY’)) TABLESPACE tbs1

);

Insert data in the table

SQL> Declare

Begin

For i in 1..10 loop

For j in 1..1000 loop

Insert into t_range values(j,sysdate+i,’DEBASIS’||to_char(i));

End loop;

end;

PL/SQL procedure successfully completed.

Let me gather the statistics now:-

SQL> exec dbms_stats.gather_table_stats(‘SYS’,’T_RANGE’);

Please check the analyze date

SELECT partition_name,

to_char( last_analyzed, ‘DD-MON-YYYY, HH24:MI:SS’ ) last_analyze,

num_rows

FROM DBA_TAB_PARTITIONS

WHERE table_name = ‘T_RANGE’

ORDER BY partition_position;

SQL> /

PARTITION_NAME LAST_ANALYZE NUM_ROWS

—————————— —————————— ———-

PART_OLD 21-FEB-2018, 22:38:05 0

SYS_P463 21-FEB-2018, 22:38:05 1000

SYS_P464 21-FEB-2018, 22:38:05 1000

SYS_P465 21-FEB-2018, 22:38:05 1000

SYS_P466 21-FEB-2018, 22:38:05 1000

SYS_P467 21-FEB-2018, 22:38:05 1000

SYS_P468 21-FEB-2018, 22:38:05 1000

SYS_P469 21-FEB-2018, 22:38:05 1000

SYS_P470 21-FEB-2018, 22:38:05 1000

SYS_P471 21-FEB-2018, 22:38:05 1000

SYS_P472 21-FEB-2018, 22:38:05 1000

Please check initial setting of statistics related parameters

SQL> SELECT dbms_stats.get_prefs(‘INCREMENTAL’,’SYS’,’T_RANGE’) “INCREMENTAL” FROM dual;

INCREMENTAL

——————————————————————————–

FALSE

SQL> SELECT dbms_stats.get_prefs(‘PUBLISH’,’SYS’,’T_RANGE’) “PUBLISH” FROM dual;

PUBLISH

——————————————————————————–

TRUE

SQL> SELECT dbms_stats.get_prefs(‘ESTIMATE_PERCENT’,’SYS’,’T_RANGE’) “ESTIMATE_PERCENT” FROM dual;

ESTIMATE_PERCENT

——————————————————————————–

DBMS_STATS.AUTO_SAMPLE_SIZE

SQL> SELECT dbms_stats.get_prefs(‘GRANULARITY’,’SYS’,’T_RANGE’) “GRANULARITY” FROM dual;

GRANULARITY

——————————————————————————–

AUTO

Now let me set TRUE to INCREMENTAL statistics

exec dbms_stats.set_table_prefs(‘TEST’,’T1′,’INCREMENTAL’,’TRUE’);

Please check synopsis table now

SELECT o.name “Table Name”,

p.subname “Part”,

c.name “Column”,

h.analyzetime “Synopsis Creation Time”

FROM WRI$_OPTSTAT_SYNOPSIS_HEAD$ h,

OBJ$ o,

USER$ u,

COL$ c,

( ( SELECT TABPART$.bo# BO#,

TABPART$.obj# OBJ#

FROM TABPART$ tabpart$ )

UNION ALL

( SELECT TABCOMPART$.bo# BO#,

TABCOMPART$.obj# OBJ#

FROM TABCOMPART$ tabcompart$ ) ) tp,

OBJ$ p

WHERE u.name = ‘SYS’ AND

o.name = ‘T_RANGE’ AND

tp.obj# = p.obj# AND

h.bo# = tp.bo# AND

h.group# = tp.obj# * 2 AND

h.bo# = c.obj#(+) AND

h.intcol# = c.intcol#(+) AND

o.owner# = u.user# AND

h.bo# = o.obj#

ORDER BY 4,1,2,3

Table Name Part Column Synopsis

——————– ——————– ——————– ———

T_RANGE PART_OLD CREATE_DATE 21-FEB-18

T_RANGE PART_OLD ID 21-FEB-18

T_RANGE PART_OLD NAME 21-FEB-18

T_RANGE SYS_P463 CREATE_DATE 21-FEB-18

T_RANGE SYS_P463 ID 21-FEB-18

T_RANGE SYS_P463 NAME 21-FEB-18

T_RANGE SYS_P464 CREATE_DATE 21-FEB-18

T_RANGE SYS_P464 ID 21-FEB-18

T_RANGE SYS_P464 NAME 21-FEB-18

T_RANGE SYS_P465 CREATE_DATE 21-FEB-18

T_RANGE SYS_P465 ID 21-FEB-18

Table Name Part Column Synopsis

——————– ——————– ——————– ———

T_RANGE SYS_P465 NAME 21-FEB-18

T_RANGE SYS_P466 CREATE_DATE 21-FEB-18

T_RANGE SYS_P466 ID 21-FEB-18

T_RANGE SYS_P466 NAME 21-FEB-18

T_RANGE SYS_P467 CREATE_DATE 21-FEB-18

T_RANGE SYS_P467 ID 21-FEB-18

T_RANGE SYS_P467 NAME 21-FEB-18

T_RANGE SYS_P468 CREATE_DATE 21-FEB-18

T_RANGE SYS_P468 ID 21-FEB-18

T_RANGE SYS_P468 NAME 21-FEB-18

T_RANGE SYS_P469 CREATE_DATE 21-FEB-18

Table Name Part Column Synopsis

——————– ——————– ——————– ———

T_RANGE SYS_P469 ID 21-FEB-18

T_RANGE SYS_P469 NAME 21-FEB-18

T_RANGE SYS_P470 CREATE_DATE 21-FEB-18

T_RANGE SYS_P470 ID 21-FEB-18

T_RANGE SYS_P470 NAME 21-FEB-18

T_RANGE SYS_P471 CREATE_DATE 21-FEB-18

T_RANGE SYS_P471 ID 21-FEB-18

T_RANGE SYS_P471 NAME 21-FEB-18

T_RANGE SYS_P472 CREATE_DATE 21-FEB-18

T_RANGE SYS_P472 ID 21-FEB-18

T_RANGE SYS_P472 NAME 21-FEB-18

Table Name Part Column Synopsis

——————– ——————– ——————– ———

T_RANGE SYS_P473 CREATE_DATE 21-FEB-18

T_RANGE SYS_P473 ID 21-FEB-18

T_RANGE SYS_P473 NAME 21-FEB-18

Now I will again load data only to one partition

SQL> Declare

Begin

For j in 1..1000 loop

Insert into t_range values(j,sysdate+1,’DEBASIS’||to_char(j));

End loop;

end; 2 3 4 5 6

7 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Let me gather stats again

SQL> exec dbms_stats.gather_table_stats(‘SYS’,’T_RANGE’);

PL/SQL procedure successfully completed.

Now We will notice that the partition,where we load data is only analyzed avoiding costly full scan of all partitions

SQL> SELECT partition_name,

to_char( last_analyzed, ‘DD-MON-YYYY, HH24:MI:SS’ ) last_analyze,

num_rows

FROM DBA_TAB_PARTITIONS

WHERE table_name = ‘T_RANGE’

ORDER BY partition_position; 2 3 4 5 6

PARTITION_NAME LAST_ANALYZE NUM_ROWS

—————————— —————————— ———-

PART_OLD 21-FEB-2018, 22:46:58 0

SYS_P473 21-FEB-2018, 22:46:58 1000

SYS_P463 21-FEB-2018, 22:50:15 2000

SYS_P464 21-FEB-2018, 22:46:58 1000

SYS_P465 21-FEB-2018, 22:46:58 1000

SYS_P466 21-FEB-2018, 22:46:58 1000

SYS_P467 21-FEB-2018, 22:46:58 1000

SYS_P468 21-FEB-2018, 22:46:58 1000

SYS_P469 21-FEB-2018, 22:46:58 1000

SYS_P470 21-FEB-2018, 22:46:58 1000

SYS_P471 21-FEB-2018, 22:46:58 1000

SYS_P472 21-FEB-2018, 22:46:58 1000

We can see the preference for the INCREMENTAL still TRUE

SQL> SELECT dbms_stats.get_prefs(‘INCREMENTAL’,’SYS’,’T_RANGE’) “INCREMENTAL” FROM dual;

INCREMENTAL

——————————————————————————–

TRUE

Now let me turn INCREMENTAL statistics to FALSE again

SQL> exec dbms_stats.set_table_prefs(‘SYS’,’T_RANGE’,’INCREMENTAL’,’FALSE’);

PL/SQL procedure successfully completed.

SQL> SELECT dbms_stats.get_prefs(‘INCREMENTAL’,’SYS’,’T_RANGE’) “INCREMENTAL” FROM dual;

INCREMENTAL

——————————————————————————–

FALSE

SQL> SELECT o.name “Table Name”,

2 p.subname “Part”,

c.name “Column”,

3 4 h.analyzetime “Synopsis Creation Time”

FROM WRI$_OPTSTAT_SYNOPSIS_HEAD$ h,

5 6 OBJ$ o,

USER$ u,

7 8 COL$ c,

( ( SELECT TABPART$.bo# BO#,

TABPART$.obj# OBJ#

9 10 11 FROM TABPART$ tabpart$ )

UNION ALL

( SELECT TABCOMPART$.bo# BO#,

12 13 14 TABCOMPART$.obj# OBJ#

FROM TABCOMPART$ tabcompart$ ) ) tp,

15 16 OBJ$ p

WHERE u.name = ‘SYS’ AND

o 17 .name = ‘T_RANGE’ AND

18 19 tp.obj# = p.obj# AND

h.bo# = tp.bo# AND

20 21 h.group# = tp.obj# * 2 AND

h.bo# = c.obj#(+) AND

h.intcol# = c.intcol#(+) AND

22 23 24 o.owner# = u.user# AND

h.bo# = o.obj#

ORDER BY 4,1,2,3

25 26 27 /

Table Name Part Column Synopsis

——————– ——————– ——————– ———

T_RANGE PART_OLD CREATE_DATE 21-FEB-18

T_RANGE PART_OLD ID 21-FEB-18

T_RANGE PART_OLD NAME 21-FEB-18

T_RANGE SYS_P464 CREATE_DATE 21-FEB-18

T_RANGE SYS_P464 ID 21-FEB-18

T_RANGE SYS_P464 NAME 21-FEB-18

T_RANGE SYS_P465 CREATE_DATE 21-FEB-18

T_RANGE SYS_P465 ID 21-FEB-18

T_RANGE SYS_P465 NAME 21-FEB-18

T_RANGE SYS_P466 CREATE_DATE 21-FEB-18

T_RANGE SYS_P466 ID 21-FEB-18

Table Name Part Column Synopsis

——————– ——————– ——————– ———

T_RANGE SYS_P466 NAME 21-FEB-18

T_RANGE SYS_P467 CREATE_DATE 21-FEB-18

T_RANGE SYS_P467 ID 21-FEB-18

T_RANGE SYS_P467 NAME 21-FEB-18

T_RANGE SYS_P468 CREATE_DATE 21-FEB-18

T_RANGE SYS_P468 ID 21-FEB-18

T_RANGE SYS_P468 NAME 21-FEB-18

T_RANGE SYS_P469 CREATE_DATE 21-FEB-18

T_RANGE SYS_P469 ID 21-FEB-18

T_RANGE SYS_P469 NAME 21-FEB-18

T_RANGE SYS_P470 CREATE_DATE 21-FEB-18

Table Name Part Column Synopsis

——————– ——————– ——————– ———

T_RANGE SYS_P470 ID 21-FEB-18

T_RANGE SYS_P470 NAME 21-FEB-18

T_RANGE SYS_P471 CREATE_DATE 21-FEB-18

T_RANGE SYS_P471 ID 21-FEB-18

T_RANGE SYS_P471 NAME 21-FEB-18

T_RANGE SYS_P472 CREATE_DATE 21-FEB-18

T_RANGE SYS_P472 ID 21-FEB-18

T_RANGE SYS_P472 NAME 21-FEB-18

T_RANGE SYS_P473 CREATE_DATE 21-FEB-18

T_RANGE SYS_P473 ID 21-FEB-18

T_RANGE SYS_P473 NAME 21-FEB-18

Table Name Part Column Synopsis

——————– ——————– ——————– ———

T_RANGE SYS_P463 CREATE_DATE 21-FEB-18

T_RANGE SYS_P463 ID 21-FEB-18

T_RANGE SYS_P463 NAME 21-FEB-18

36 rows selected.

SQL> commit;

Commit complete.

Let me gather statistics again

SQL> exec dbms_stats.gather_table_stats(‘SYS’,’T_RANGE’);

PL/SQL procedure successfully completed.

Now please note statistics updated all the partitions of the table again which will go for costly full table scan for all partitions

SQL> SELECT partition_name,

to_char( last_analyzed, ‘DD-MON-YYYY, HH24:MI:SS’ ) last_analyze,

num_rows

FROM DBA_TAB_PARTITIONS

WHERE table_name = ‘T_RANGE’

ORDER BY partition_position; 2 3 4 5 6

PARTITION_NAME LAST_ANALYZE NUM_ROWS

—————————— —————————— ———-

PART_OLD 21-FEB-2018, 23:06:47 0

SYS_P473 21-FEB-2018, 23:06:47 1000

SYS_P463 21-FEB-2018, 23:06:47 3000

SYS_P464 21-FEB-2018, 23:06:47 1000

SYS_P465 21-FEB-2018, 23:06:47 1000

SYS_P466 21-FEB-2018, 23:06:47 1000

SYS_P467 21-FEB-2018, 23:06:47 1000

SYS_P468 21-FEB-2018, 23:06:47 1000

SYS_P469 21-FEB-2018, 23:06:47 1000

SYS_P470 21-FEB-2018, 23:06:47 1000

SYS_P471 21-FEB-2018, 23:06:47 1000

SYS_P472 21-FEB-2018, 23:06:47 1000

12 rows selected.

J.SQL Plan directives FOR gather statistics oracle

SQL Plan Directives are one of the functionalities that make up Adaptive Query Optimization in Oracle Database 12c. SQL plan directives are like “extra notes” for the optimizer, to remind it that it previously selected a suboptimal plan, typically because of incorrect cardinality estimates. Incorrect cardinality estimates are often caused by issues like missing statistics, stale statistics, complex predicates or complex operators. Unlike SQL profiles, which are statement specific, SQL plan directives are linked to query expressions, so they can be used by several statements containing matching query expressions. Situations like missing histograms or missing extended statistics may result in SQL plan directives being generated.

The database manages SQL plan directives internally. Situations like automatic reoptimization may result in SQL plan directives being written to the SGA and later persisted to the SYSAUX tablespace, at which point they can be displayed using the DBA_SQL_PLAN_DIRECTIVES and DBA_SQL_PLAN_DIR_OBJECTS views. Alternatively, existing SQL plan directives can be persisted manually using the DBMS_SPD package.

Let me create test seup first

sqlplus test/test
SQL> DROP TABLE tab1 PURGE;
Table dropped.

SQL> CREATE TABLE tab1 (
id NUMBER,
gender VARCHAR2(1),
has_y_chromosome VARCHAR2(1),
CONSTRAINT tab1_pk PRIMARY KEY (id),
CONSTRAINT tab1_gender_chk CHECK (gender IN (‘M’, ‘F’)),
CONSTRAINT tab1_has_y_chromosome_chk CHECK (has_y_chromosome IN (‘Y’, ‘N’))
);

Table created.

SQL>

INSERT /*+ APPEND */ INTO tab1

SELECT level, ‘M’, ‘Y’

FROM dual

CONNECT BY level <= 10;

COMMIT;

10 rows created.

SQL>Commit complete.

SQL>

INSERT /*+ APPEND */ INTO tab1

SELECT 10+level, ‘F’, ‘N’

FROM dual

CONNECT BY level <= 90;

COMMIT;

90 rows created.

SQL>Commit complete.

SQL> CREATE INDEX tab1_gender_idx ON tab1(gender);

CREATE INDEX tab1_has_y_chromosome_idx ON tab1(has_y_chromosome);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, ‘TAB1’);

PL/SQL procedure successfully completed.

Now please check there is no histogram collected

SQL> COLUMN column_name FORMAT A20

SELECT column_id,

column_name,

histogram

FROM user_tab_columns

WHERE table_name = ‘TAB1’

ORDER BY column_id;

COLUMN_ID COLUMN_NAME HISTOGRAM

———- ——————– —————

1 ID NONE

2 GENDER NONE

3 HAS_Y_CHROMOSOME NONE

Please note that we observe cardinality miscalculation in explain plan,which is evident here.Cardinality mismatch always leads to improper explain plan hence poor performance.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */

FROM tab1

WHERE gender = ‘M’

AND has_y_chromosome = ‘Y’;

SET LINESIZE 200 PAGESIZE 100

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => ‘allstats last’)); 2 3 4 5

ID G H

———- – –

1 M Y

2 M Y

3 M Y

4 M Y

5 M Y

6 M Y

7 M Y

8 M Y

9 M Y

10 M Y

10 rows selected.

PLAN_TABLE_OUTPUT

—————————————————————————————————————————————–

SQL_ID 5t8y8p5mpb99j, child number 1

————————————-

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM tab1 WHERE gender

= ‘M’ AND has_y_chromosome = ‘Y’

Plan hash value: 1552452781

—————————————————————————————————————–

—————————————————————————————————————–

| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 4 |

|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 25 | 10 |00:00:00.01 | 4 ||* 2 | INDEX RANGE SCAN | TAB1_GENDER_IDX | 1 | 50 | 10 |00:00:00.01 | 2 |

—————————————————————————————————————–

Predicate Information (identified by operation id):

—————————————————

1 – filter(“HAS_Y_CHROMOSOME”=’Y’)

2 – access(“GENDER”=’M’)

21 rows selected.

We will also observe that Oracle found that this sql query can be optimized further

SQL> COLUMN sql_text FORMAT A40

COLUMN is_reoptimizable FORMAT A16

SELECT sql_text, is_reoptimizable

FROM v$sql

WHERE sql_id = ‘5t8y8p5mpb99j’;SQL> SQL> SQL> 2 3

SQL_TEXT IS_REOPTIMIZABLE

—————————————- —————-

SELECT /*+ GATHER_PLAN_STATISTICS */ Y

* FROM tab1 WHERE gender = ‘M’ AN

D has_y_chromosome = ‘Y’

SELECT /*+ GATHER_PLAN_STATISTICS */ Y

* FROM tab1 WHERE gender = ‘M’ AN

D has_y_chromosome = ‘Y’

At this point, the optimizer has written SQL plan directives into the SGA, but they are not visible to us as they have not been persisted to the SYSAUX tablespace.

SQL> SET LINESIZE 200
COLUMN dir_id FORMAT A20
COLUMN owner FORMAT A10
COLUMN object_name FORMAT A10
COLUMN col_name FORMAT A10SQL> SELECT TO_CHAR(d.directive_id) dir_id, o.owner, o.object_name,
o.subobject_name col_name, o.object_type, d.type, d.state, d.reason
2 3 FROM dba_sql_plan_directives d, dba_sql_plan_dir_objects o
WHERE d.directive_id=o.directive_id
4 5 AND o.owner = ‘TEST’
ORDER BY 1,2,3,4,5; 6no rows selected

We can wait for the database to persist the SQL plan directives, or manually persist them using the DBMS_SPD package.

SQL> EXEC DBMS_SPD.flush_sql_plan_directive;

PL/SQL procedure successfully completed.

If we repeat the previous query, the SQL plan directives will be visible.

SQL> SET LINESIZE 200

COLUMN dir_id FORMAT A20

COLUMN owner FORMAT A10

COLUMN object_name FORMAT A10

COLUMN col_name FORMAT A10

SQL> SELECT TO_CHAR(d.directive_id) dir_id, o.owner, o.object_name,

o.subobject_name col_name, o.object_type, d.type, d.state, d.reason

FROM dba_sql_plan_directives d, dba_sql_plan_dir_objects o

WHERE d.directive_id=o.directive_id

AND o.owner = ‘TEST’

ORDER BY 1,2,3,4,5;

DIR_ID OWNER OBJECT_NAM COL_NAME OBJECT TYPE STATE REASON

——————– ———- ———- ———- —— —————- ———- ————————————

796134444163473829 TEST TAB1 GENDER COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE

796134444163473829 TEST TAB1 TABLE DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE

Now let me rerun the query and query explain plan will show proper cardinality with help of sql plan directive

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */

FROM tab1

WHERE gender = ‘M’

AND has_y_chromosome = ‘Y’;

SET LINESIZE 200 PAGESIZE 100

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => ‘allstats last’));

2 3 4 5

ID G H

———- – –

1 M Y

2 M Y

3 M Y

4 M Y

5 M Y

6 M Y

7 M Y

8 M Y

9 M Y

10 M Y

10 rows selected.

SQL> SQL> SQL>

PLAN_TABLE_OUTPUT

—————————————————————————————————————————————–

SQL_ID 5t8y8p5mpb99j, child number 2

————————————-

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM tab1 WHERE gender

= ‘M’ AND has_y_chromosome = ‘Y’

Plan hash value: 1552452781

—————————————————————————————————————–

—————————————————————————————————————–

| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 4 |

|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 10 | 10 |00:00:00.01 | 4 |

|* 2 | INDEX RANGE SCAN | TAB1_GENDER_IDX | 1 | 10 | 10 |00:00:00.01 | 2 |

—————————————————————————————————————–

Predicate Information (identified by operation id):

—————————————————

1 – filter(“HAS_Y_CHROMOSOME”=’Y’)

2 – access(“GENDER”=’M’)

Note

—–

– dynamic statistics used: dynamic sampling (level=2)

– statistics feedback used for this statement

– 1 Sql Plan Directive used for this statement

27 rows selected.

K.Extended Statistics FOR gather statistics oracle

In real-world data, there is often a relationship or correlation between the data stored in different columns of the same table. For example, consider a customers table where the values in a cust_state_province column are influenced by the values in a country_id column, because the state of California is only going to be found in the United States. If the Oracle Optimizer is not aware of these real-world relationships, it could potentially miscalculate the cardinality estimate if multiple columns from the same table are used in the where clause of a statement. With extended statistics you have an opportunity to tell the optimizer about these real-world relationships between the columns.

By creating extended statistics on a group of columns, the optimizer can determine a more accurate cardinality estimate when the columns are used together in a where clause of a SQL statement.You can use DBMS_STATS.CREATE_EXTENDED_STATS to define the column group you want to have statistics gathered on as a whole. Once the group has been established Oracle will automatically maintain the statistics on that column group when statistics are gathered on the table.

If we continue with the initial example of the customers table, When the value of cust_state_province is ‘CA’ we know the value of country_id will be 52790 or the USA. There is also a skew in the data in these two columns; because the company used in the SH is based in San Francisco so the majority of rows in the table have the values ‘CA’ and 52790. Both the relationship between the columns and the skew in the data can make it difficult for the optimizer to calculate the cardinality of these columns correctly when they are used together in a query.

Let us connect to SH schema:-

SQL> select count(1) from customers where cust_state_province=’CA’ and country_id=52790;

COUNT(1)

———-

3341

It is evident from explain plain that cardinality estimate incorrect.

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

——————————————————————————–

SQL_ID f3rfbwhagqcss, child number 0

————————————-

select count(1) from customers where cust_state_province=’CA’ and

country_id=52790

Plan hash value: 296924608

——————————————————————————–

——————————————————————————–

| 0 | SELECT STATEMENT | | | | 423 (100)| |

PLAN_TABLE_OUTPUT

——————————————————————————–

| 1 | SORT AGGREGATE | | 1 | 16 | | |

|* 2 | TABLE ACCESS FULL| CUSTOMERS | 128 | 2048 | 423 (1)| 00:00:01 |

——————————————————————————–

Predicate Information (identified by operation id):

—————————————————

2 – filter((“CUST_STATE_PROVINCE”=’CA’ AND “COUNTRY_ID”=52790))

20 rows selected.

Prior to Oracle Database 11g the only option open to us would be to make the optimizer aware of the data skew in both the country_id column (most rows have 52790 as the value) and the cust_state_province column (most rows have ‘CA’ as the value). We can do this by gathering histograms on the skewed columns.

SQL> exec dbms_stats.gather_table_stats(user,’CUSTOMERS’,method_opt=>’for all columns size skewonly’);

PL/SQL procedure successfully completed.

Let me create extended statistics on the two column used in where clause

select dbms_stats.create_extended_stats(null,’customers’,'(country_id,cust_state_province)’) from dual;

SQL> col COLUMN_NAME format a30;

SQL> col HISTOGRAM format a30;

SQL> select column_name,histogram from user_tab_col_statistics where table_name=’CUSTOMERS’;

COLUMN_NAME HISTOGRAM

—————————— ——————————

SYS_STUJGVLRVH5USVDU$XNV4_IR#4 FREQUENCY

CUST_STATE_PROVINCE_ID FREQUENCY

COUNTRY_ID FREQUENCY

CUST_MAIN_PHONE_NUMBER HYBRID

CUST_INCOME_LEVEL FREQUENCY

CUST_CREDIT_LIMIT FREQUENCY

CUST_EMAIL HYBRID

CUST_TOTAL FREQUENCY

CUST_TOTAL_ID FREQUENCY

CUST_SRC_ID NONE

CUST_EFF_FROM FREQUENCY

CUST_EFF_TO NONE

CUST_VALID FREQUENCY

CUST_ID HYBRID

CUST_FIRST_NAME HYBRID

CUST_LAST_NAME HYBRID

CUST_GENDER FREQUENCY

CUST_YEAR_OF_BIRTH FREQUENCY

CUST_MARITAL_STATUS FREQUENCY

CUST_STREET_ADDRESS HYBRID

CUST_POSTAL_CODE HYBRID

CUST_CITY HYBRID

CUST_CITY_ID HYBRID

CUST_STATE_PROVINCE FREQUENCY

SQL> select count(1) from customers where cust_state_province=’CA’ and country_id=52790;

COUNT(1)

———-

3341

We can now see it goes for dynamic sampling and adaptive optimizer feature

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

—————————————————————————————————————————————–

SQL_ID f3rfbwhagqcss, child number 0

————————————-

select count(1) from customers where cust_state_province=’CA’ and

country_id=52790

Plan hash value: 296924608

——————————————————————————–

——————————————————————————–

| 0 | SELECT STATEMENT | | | | 423 (100)| |

| 1 | SORT AGGREGATE | | 1 | 16 | | |

|* 2 | TABLE ACCESS FULL| CUSTOMERS | 3424 | 54784 | 423 (1)| 00:00:01 |

——————————————————————————–

Predicate Information (identified by operation id):

—————————————————

2 – filter((“CUST_STATE_PROVINCE”=’CA’ AND “COUNTRY_ID”=52790))

Note

—–

PLAN_TABLE_OUTPUT

—————————————————————————————————————————————–

– dynamic statistics used: dynamic sampling (level=2)

– 1 Sql Plan Directive used for this statement

25 rows selected.

Optionally we can see report of the column

SQL> set long 100000

SQL> set lines 120

SQL> set pages 0

SQL> select dbms_stats.report_col_usage(user,’CUSTOMERS’) from dual;

LEGEND:

…….

EQ : Used in single table EQuality predicate

RANGE : Used in single table RANGE predicate

LIKE : Used in single table LIKE predicate

NULL : Used in single table is (not) NULL predicate

EQ_JOIN : Used in EQuality JOIN predicate

NONEQ_JOIN : Used in NON EQuality JOIN predicate

FILTER : Used in single table FILTER predicate

JOIN : Used in JOIN predicate

GROUP_BY : Used in GROUP BY expression

…………………………………………………………………….

###############################################################################

COLUMN USAGE REPORT FOR SH.CUSTOMERS

………………………………

COUNTRY_ID : EQ EQ_JOIN
CUST_CITY_ID : EQ_JOIN
CUST_ID : EQ_JOIN
CUST_STATE_PROVINCE : EQ
CUST_STATE_PROVINCE_ID : EQ_JOIN
CUST_TOTAL_ID : EQ_JOIN
SYS_STUJGVLRVH5USVDU$XNV4_IR#4 : EQ
(CUST_STATE_PROVINCE, COUNTRY_ID) : FILTER

###############################################################################

Also I need to turn off optimizer dynamic sampling and optimizer adaptive feature to simulate extended statistics.

SQL> alter system set optimizer_dynamic_sampling=0;

SQL> alter system set optimizer_adaptive_features=false;

SQL> select count(1) from customers where cust_state_province=’CA’ and country_id=52790;

COUNT(1)

———-

3341

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

——————————————————————————–

SQL_ID f3rfbwhagqcss, child number 2

————————————-

select count(1) from customers where cust_state_province=’CA’ and

country_id=52790

Plan hash value: 296924608

——————————————————————————–

——————————————————————————–

| 0 | SELECT STATEMENT | | | | 424 (100)| |

PLAN_TABLE_OUTPUT

——————————————————————————–

| 1 | SORT AGGREGATE | | 1 | 35 | | |

|* 2 | TABLE ACCESS FULL| CUSTOMERS | 13 | 455 | 424 (1)| 00:00:01 |

——————————————————————————–

Predicate Information (identified by operation id):

—————————————————

2 – filter((“CUST_STATE_PROVINCE”=’CA’ AND “COUNTRY_ID”=52790))

Let me drop the extended statistics

SQL> exec dbms_stats.drop_extended_stats(null,’customers’,'(country_id,cust_state_province)’);

PL/SQL procedure successfully completed.

I will recreate statistics again

SQL> select dbms_stats.create_extended_stats(null,’customers’,'(country_id,cust_state_province)’) from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(NULL,’CUSTOMERS’,'(COUNTRY_ID,CUST_STATE_PROVINCE)’)

—————————————————————————————————————————————–

SYS_STUJGVLRVH5USVDU$XNV4_IR#4

Let me gather statistics again

SQL> exec dbms_stats.gather_table_stats(user,’CUSTOMERS’);

PL/SQL procedure successfully completed.

Still it will show bad cardinality estimation

SQL> select count(1) from customers where cust_state_province=’CA’ and country_id=52790;

COUNT(1)

———-

3341

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

—————————————————————————————————————————————–

SQL_ID f3rfbwhagqcss, child number 2

————————————-

select count(1) from customers where cust_state_province=’CA’ and

country_id=52790

Plan hash value: 296924608

——————————————————————————–

——————————————————————————–

| 0 | SELECT STATEMENT | | | | 423 (100)| |

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

| 1 | SORT AGGREGATE | | 1 | 16 | | |

|* 2 | TABLE ACCESS FULL| CUSTOMERS | 1115 | 17840 | 423 (1)| 00:00:01 |

——————————————————————————–

Predicate Information (identified by operation id):

—————————————————

2 – filter((“CUST_STATE_PROVINCE”=’CA’ AND “COUNTRY_ID”=52790))

20 rows selected.

Now let me again gather statistics with skewonly option of histogram.It will show correct estimate.

SQL> exec dbms_stats.gather_table_stats(user,’CUSTOMERS’,method_opt=>’for all columns size skewonly’);

PL/SQL procedure successfully completed.

SQL> select count(1) from customers where cust_state_province=’CA’ and country_id=52790;

COUNT(1)

———-

3341

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

—————————————————————————————————————————————–

SQL_ID 74w1jdh78t4w8, child number 0

————————————-

select count(1) from customers where cust_state_province=’CA’ and

country_id=52790

Plan hash value: 296924608

——————————————————————————–

——————————————————————————–

| 0 | SELECT STATEMENT | | | | 423 (100)| |

| 1 | SORT AGGREGATE | | 1 | 16 | | |

|* 2 | TABLE ACCESS FULL| CUSTOMERS | 3341 | 53456 | 423 (1)| 00:00:01 |

——————————————————————————–

Predicate Information (identified by operation id):

—————————————————

2 – filter((“CUST_STATE_PROVINCE”=’CA’ AND “COUNTRY_ID”=52790))

L.How to enable old statistics from history FOR gather statistics oracle

Let me check what are old statistics collected in previous test case

SQL> COL TABLE_NAME FORMAT a10

SQL>SELECT TABLE_NAME,

TO_CHAR(STATS_UPDATE_TIME,’YYYY-MM-DD:HH24:MI:SS’) AS STATS_MOD_TIME

FROM DBA_TAB_STATS_HISTORY

WHERE TABLE_NAME=’CUSTOMERS’

AND OWNER=’SH’

ORDER BY STATS_UPDATE_TIME DESC;

TABLE_NAME STATS_MOD_TIME

———- ——————-

CUSTOMERS 2018-02-22:03:30:57

CUSTOMERS 2018-02-22:03:24:07

CUSTOMERS 2018-02-22:03:20:27

CUSTOMERS 2018-02-22:03:00:37

CUSTOMERS 2018-02-22:02:16:48

CUSTOMERS 2018-02-22:02:01:26

CUSTOMERS 2018-02-22:01:42:21

CUSTOMERS 2018-02-07:12:15:38

8 rows selected.

Let me enable older statistics now

SQL> BEGIN

DBMS_STATS.RESTORE_TABLE_STATS( ‘SH’,’CUSTOMERS’,

TO_TIMESTAMP(‘2018-02-22:03:24:07′,’YYYY-MM-DD:HH24:MI:SS’) );

END;

2 3 4 5

PL/SQL procedure successfully completed.

Now if we check explain plan,it went back to previous explain plan cost

SQL> select count(1) from customers where cust_state_province=’CA’ and country_id=52790;

COUNT(1)

———-

3341

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

—————————————————————————————————————————————–

SQL_ID f3rfbwhagqcss, child number 2

————————————-

select count(1) from customers where cust_state_province=’CA’ and

country_id=52790

Plan hash value: 296924608

——————————————————————————–

——————————————————————————–

| 0 | SELECT STATEMENT | | | | 423 (100)| |

PLAN_TABLE_OUTPUT

| 1 | SORT AGGREGATE | | 1 | 16 | | |

|* 2 | TABLE ACCESS FULL| CUSTOMERS | 1115 | 17840 | 423 (1)| 00:00:01 |

——————————————————————————–

Predicate Information (identified by operation id):

—————————————————

2 – filter((“CUST_STATE_PROVINCE”=’CA’ AND “COUNTRY_ID”=52790))

20 rows selected.

M.Online Statistics FOR GATHER STATISTICS ORACLE

In Oracle Database 12c, online statistics gathering “piggybacks” statistics gather as part of a direct-path data loading operation such as, create table as select (CTAS) and insert as select (IAS) operations. Gathering statistics as part of the data loading operation means no additional full data scan is required to have statistics available immediately after the data is loaded.

Online statistics gathering does not gather histograms or index statistics, as these types of statistics require additional data scans, which could have a large impact on the performance of the data load. To gather the necessary histogram and index statistics without re-gathering the base column statistics use the DBMS_STATS.GATHER_TABLE_STATS procedure with the new options parameter set to GATHER AUTO. Note that for performance reasons, GATHER AUTO builds histogram using a sample of rows rather than all rows in the table.

BUFFER CACHE DEMYSTIFIED CBC EXPLAINED

Posted on February 20, 2018March 29, 2018 by debasis maity

Oracle PGA (Program Global Area) in 12c

Posted on February 9, 2018March 30, 2018 by debasis maity

What is PGA

The Program Global Area (PGA) is a private memory region that contains the data and control information for a server process. Only a server process can access the PGA. Oracle Database reads and writes information in the PGA on behalf of the server process. An example of such information is the run-time area of a cursor. Each time a cursor is executed, a new run-time area is created for that cursor in the PGA memory region of the server process executing that cursor.

For complex queries (such as decision support queries), a big portion of the run-time area is dedicated to work areas allocated by memory intensive operators, including:

Sort-based operators, such as ORDER BY, GROUP BY, ROLLUP, and window functions
Hash-join
Bitmap merge
Bitmap create
Write buffers used by bulk load operations

A sort operator uses a work area (the sort area) to perform the in-memory sorting of a set of rows. Similarly, a hash-join operator uses a work area (the hash area) to build a hash table from its left input.

Work Area Sizes

Oracle Database enables you to control and tune the sizes of work areas. Generally, bigger work areas can significantly improve the performance of a particular operator at the cost of higher memory consumption. The available work area sizes include:

OptimalOptimal size is when the size of a work area is large enough that it can accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. This is the ideal size for the work area.
One-passOne-pass size is when the size of the work area is below optimal size and an extra pass is performed over part of the input data. With one-pass size, the response time is increased.
Multi-passMulti-pass size is when the size of the work area is below the one-pass threshold and multiple passes over the input data are needed. With multi-pass size, the response time is dramatically increased because the size of the work area is too small compared to the input data size.

For example, a serial sort operation that must sort 10 GB of data requires a little more than 10 GB to run as optimal size and at least 40 MB to run as one-pass size. If the work area is less than 40 MB, then the sort operation must perform several passes over the input data.

When sizing the work area, the goal is to have most work areas running with optimal size (more than 90%, or even 100% for pure OLTP systems), and only a small number of them running with one-pass size (less than 10%). Multi-pass executions should be avoided for the following reasons:

Multi-pass executions can severely degrade performance.A high number of multi-pass work areas has an exponentially adverse effect on the response time of its associated SQL operator.
Running one-pass executions does not require a large amount of memory.Only 22 MB is required to sort 1 GB of data in one-pass size.

Even for DSS systems running large sorts and hash-joins, the memory requirement for one-pass executions is relatively small. A system configured with a reasonable amount of PGA memory should not need to perform multiple passes over the input data.

Setting the Initial Value for PGA_AGGREGATE_TARGET

Set the initial value of the PGA_AGGREGATE_TARGET initialization parameter based on the amount of available memory for the Oracle database instance. This value can then be tuned and dynamically modified at the instance level. By default, Oracle Database uses 20% of the SGA size for this value. However, this setting may be too low for a large DSS system.

To set the initial value for PGA_AGGREGATE_TARGET:

Determine how much of the total physical memory to reserve for the operating system and other non-Oracle applications running on the same system.For example, you might decide to reserve 20% of the total physical memory for the operating system and other non-Oracle applications, dedicating 80% of the memory on the system to the Oracle database instance.
Divide the remaining available memory between the SGA and the PGA:
- For OLTP systems, the PGA memory typically makes up a small fraction of the available memory, leaving most of the remaining memory for the SGA.Oracle recommends initially dedicating 20% of the available memory to the PGA, and 80% to the SGA. Therefore, the initial value of the PGA_AGGREGATE_TARGET parameter for an OLTP system can be calculated as:PGA_AGGREGATE_TARGET = (total_mem * 0.8) * 0.2 where total_mem is the total amount of physical memory available on the system.
- For DSS systems running large, memory-intensive queries, PGA memory can typically use up to 70% of the available memory.Oracle recommends initially dedicating 50% of the available memory to the PGA, and 50% to the SGA. Therefore, the initial value of the PGA_AGGREGATE_TARGET parameter for a DSS system can be calculated as:PGA_AGGREGATE_TARGET = (total_mem * 0.8) * 0.5 where total_mem is the total amount of physical memory available on the system.

For example, if an Oracle database instance is configured to run on a system with 4 GB of physical memory, and if 80% (or 3.2 GB) of the memory is dedicated to the Oracle database instance, then initially set PGA_AGGREGATE_TARGET to 640 MB for an OLTP system, or 1,600 MB for a DSS system.

About PGA_AGGREGATE_LIMIT

The PGA_AGGREGATE_LIMIT initialization parameter enables you to specify a hard limit on PGA memory usage. If the PGA_AGGREGATE_LIMIT value is exceeded, Oracle Database aborts or terminates the sessions or processes that are consuming the most untunable PGA memory in the following order:

Calls for sessions that are consuming the most untunable PGA memory are aborted.
If PGA memory usage is still over the PGA_AGGREGATE_LIMIT, then the sessions and processes that are consuming the most untunable PGA memory are terminated.

In determining the sessions and processes to abort or terminate, Oracle Database treats parallel queries as a single unit.

By default, the PGA_AGGREGATE_LIMIT parameter is set to the greater of 2 GB, 200% of the PGA_AGGREGATE_TARGET value, or 3 MB times the value of the PROCESSES parameter. However, it will not exceed 120% of the physical memory size minus the total SGA size. The default value is printed into the alert log. A warning message is printed in the alert log if the amount of physical memory on the system cannot be determined.

AUTOMATIC PGA MEMORY MANAGEMENT

Automatic PGA memory management resolved above mentioned issues by allowing DBA to allocate an aggregate PGA to all the server processes for all the SQL operations which could be distributed as per the requirement. In this case, Oracle dynamically adapts the SQL memory allocation based on

– PGA memory available
– SQL operator needs
– System workload

With automatic PGA memory management, sizing of SQL work areas for all dedicated server sessions is automatic and all *_AREA_SIZE initialization parameters are ignored for these sessions. At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of PGA_AGGREGATE_TARGET minus the PGA memory allocated for other purposes (for example, session memory). The resulting PGA memory is then allotted to individual active work areas based on their specific memory requirements.

To implement it, two parameters need to be set.

WORKAREA_SIZE_POLICY = AUTO
PGA_AGGREGATE_TARGET = Target size of PGA for the total instance.

Given the specified target PGA, Oracle itself tunes the size of individual PGA’s depending

Earlier releases required DBA to manually specify the maximum workarea size for each type of SQL operator.

PGA Overallocation vs. tunable and non-tunable areas

PGA has two components : Tunable and untunable

– Untunable PGA : consists of

Context information of each session
Each open cursor
PL/SQL, OLAP or Java memory

This component of PGA can’t be tuned i.e. whatever is memory is needed it will be consumed else the operation fails.

– Tunable PGA : consists of memory available to SQL work areas (used by various sort operations)

approx. 90% of PGA in DSS systems
approx. 10% of PGA in OLTP systems

This component is tunable in the sense that memory available and hence consumed may be less than what is needed and the operation will still complete but may spill to disk. Hence, increasing available memory may improve performance of such operations.

We can control the number of cursors by the init.ora OPEN_CURSORS parameter, but if this is exceeded we get an ORA-1000 error, which is clearly undesirable. See unpublished Note:1012266.6 – “Overview of ORA-1000 Maximum Number of Cursors Exceeded” for more info. More importantly, however, we have no control over the size of a cursor, and users may open very large cursors dependent on their SQL or PLSQL.

Also note that if we set PGA_AGGREGATE_TARGET too small to accommodate the non-tunable part of the PGA plus the minimum memory required to execute the tunable part, then Oracle cannot honour the PGA_AGGREGATE_TARGET value, and will attempt to allocate extra memory. This is known as overallocation, and an estimation of this can be seen in the view V$PGA_TARGET_ADVICE under the column ESTD_OVERALLOC_COUNT.

As PGA memory is divided as tunable and non-tunable areas, while tunable is constrained under PGA_AGGREGATE_TARGET, non-tunable can be over allocated in any size (till ORA-04030), and Oracle records these activities in column ESTD_OVERALLOC_COUNT of V$PGA_TARGET_ADVICE. Therefore ESTD_OVERALLOC_COUNT is caused by over request of non-tunable areas.

Before each overallocation, probably Oracle tries to deallocate certain less used memory (for example, LRU Algorithm) at first. If not satisfied, new memory is allocated.

OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. Under overallocation, session cursor caches could be subject to memory deallocation.

Test Case

Test case 1:-

First we will create a table with 3 GB size.My pga_aggregate_limit =2G and pga_aggregate_target =1G.We will sort the table to check the behavior of PGA.

Initially before running the query let me capture the statistics.

col NAME for a40;

SELECT NAME, VALUE, UNIT FROM V$PGASTAT

where NAME in ( ‘aggregate PGA target parameter’, ‘aggregate PGA auto target’, ‘total PGA inuse ‘,

‘total PGA allocated’, ‘maximum PGA used for auto workareas’, ‘cache hit percentage’, ‘over allocation count’);

NAME VALUE UNIT

—————————————- ———- ————

aggregate PGA target parameter 1073741824 bytes

aggregate PGA auto target 821412864 bytes

total PGA allocated 216955904 bytes

maximum PGA used for auto workareas 0 bytes

over allocation count 0

cache hit percentage 100 percent

6 rows selected.

SQL> show parameter pga;

NAME TYPE VALUE

———————————— ———– ——————————

pga_aggregate_limit big integer 2G

pga_aggregate_target big integer 1G

SQL> select bytes/1024/1024/1024 from dba_segments where segment_name=’T1′;

BYTES/1024/1024/1024

——————–

3.31640625

SQL> SELECT name profile, cnt, DECODE(total, 0, 0, ROUND(cnt*100/total)) percentage

FROM (SELECT name, value cnt, (SUM(value) over ()) total

FROM V$SYSSTAT

WHERE name

LIKE ‘workarea exec%’); 2 3 4 5

PROFILE CNT

—————————————————————- ———-

PERCENTAGE

———-

workarea executions – optimal 6002

100

workarea executions – onepass 0

workarea executions – multipass 0

SQL>exec dbms_workload_repository.create_snapshot;

SQL> COL NAME FOR A35

SELECT NAME, VALUE/1024/1024 VALUE_MB

FROM V$PGASTAT

WHERE NAME IN (‘aggregate PGA target parameter’,

‘total PGA allocated’,

‘total PGA inuse’,

‘over allocation count’);SQL> SQL> 2 3 4 5 6

NAME VALUE_MB

———————————– ———-

aggregate PGA target parameter 1024

total PGA inuse 144.376953

total PGA allocated 196.063477

over allocation count 0

Now I will run the query in another session

SQL> set autotrace traceonly;

SQL> select * from t1 order by c1;

2999999 rows selected.

In the same time I will check what happens in WORK AREA using the following query:

col sid for 99999

col operation for a20

col start_time for a20

col expected_size_MB for 99,999.9

col Act_Used_MB for 99,999.9

col Max_Used_MB for 99,999.9

col TEMP_Seg_MB for 999,999

select to_char(sql_exec_start, ‘yyyy-mm-dd hh24:mi:ss’) start_time, sql_id,

to_number(decode(sid, 65535, NULL, sid)) sid,

operation_type operation, trunc(expected_size/1024/1024) expected_size_MB,

trunc(actual_mem_used/1024/1024) Act_Used_MB, trunc(max_mem_used/1024/1204) Max_Used_MB,

number_passes pass, trunc(tempseg_size/1024/1024) TEMP_Seg_MB

FROM v$sql_workarea_active

ORDER BY 1,2;

START_TIME SQL_ID SID OPERATION EXPECTED_SIZE_MB ACT_USED_MB MAX_USED_MB PASS TEMP_SEG_MB

——————– ————- —— ——————– —————- ———– ———– ———- ———–

2018-02-08 07:37:42 0k6yvhdy619tc 357 SORT (v2) 14.0 14.0 87.0 1 2,955

Let me capture the statistics again.

SQL> col NAME for a40;

SQL> SELECT NAME, VALUE, UNIT FROM V$PGASTAT

where NAME in ( ‘aggregate PGA target parameter’, ‘aggregate PGA auto target’, ‘total PG 2 A inuse ‘,

‘total PGA allocated’, ‘maximum PGA used for auto workareas’, ‘cache hit percentage’, ‘over allocation count’);

NAME VALUE UNIT

—————————————- ———- ————

aggregate PGA target parameter 1073741824 bytes

aggregate PGA auto target 830739456 bytes

total PGA allocated 203883520 bytes

maximum PGA used for auto workareas 107125760 bytes

over allocation count 0

cache hit percentage 51.45 percent

6 rows selected.

SQL> COL NAME FOR A35

SELECT NAME, VALUE/1024/1024 VALUE_MB

FROM V$PGASTAT

WHERE NAME IN (‘aggregate PGA target parameter’,

‘total PGA allocated’,

‘total PGA inuse’,

‘over allocation count’);SQL> SQL> 2 3 4 5 6

NAME VALUE_MB

———————————– ———-

aggregate PGA target parameter 1024

total PGA inuse 143.730469

total PGA allocated 194.438477

over allocation count 0

SQL> SELECT name profile, cnt, DECODE(total, 0, 0, ROUND(cnt*100/total)) percentage

FROM (SELECT name, value cnt, (SUM(value) over ()) total

FROM V$SYSSTAT

WHERE name

LIKE ‘workarea exec%’); 2 3 4 5

PROFILE CNT

—————————————————————- ———-

PERCENTAGE

———-

workarea executions – optimal 6484

100

workarea executions – onepass 2

workarea executions – multipass 0

Test case 2:-Let me run the sort again.

SQL> set autotrace traceonly;

SQL> select * from t1 order by c1;

2999999 rows selected.

Let me capture the statistics again

SQL> COL NAME FOR A35

SELECT NAME, VALUE/1024/1024 VALUE_MB

FROM V$PGASTAT

WHERE NAME IN (‘aggregate PGA target parameter’,

‘total PGA allocated’,

‘total PGA inuse’,

‘over allocation count’);SQL> SQL> 2 3 4 5 6

NAME VALUE_MB

———————————– ———-

aggregate PGA target parameter 1024

total PGA inuse 153.366211

total PGA allocated 209.775391

over allocation count 0

SQL> SELECT NAME, VALUE, UNIT FROM V$PGASTAT

where NAME in ( ‘aggregate PGA target parameter’, ‘aggregate PGA auto target’, ‘total PG 2 A inuse ‘,

‘total PGA allocated’, ‘maximum PGA used for 3 auto workareas’, ‘cache hit percentage’, ‘over allocation count’);

NAME VALUE UNIT

———————————– ———- ————

aggregate PGA target parameter 1073741824 bytes

aggregate PGA auto target 823560192 bytes

total PGA allocated 216297472 bytes

maximum PGA used for auto workareas 107125760 bytes

over allocation count 0

cache hit percentage 50.93 percent

6 rows selected.

SQL> SELECT name profile, cnt, DECODE(total, 0, 0, ROUND(cnt*100/total)) percentage

FROM (SELECT name, value cnt, (SUM(value) over ()) total

FROM V$SYSSTAT

WHERE name

LIKE ‘workarea exec%’); 2 3 4 5

PROFILE CNT

—————————————————————- ———-

PERCENTAGE

———-

workarea executions – optimal 7095

100

workarea executions – onepass 4

workarea executions – multipass 0

AWR statistics related to PGA

Test Case 3:-

Let me increase the PGA_AGGREGATE_TARGET to 10GB.

SQL> set autotrace traceonly;

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select * from t1 order by c1;

2999999 rows selected.

In the same time I will check what happens in WORK AREA using the following query:

SQL> col sid for 99999

SQL> col operation for a20

col start_time for a20

SQL> SQL> col expected_size_MB for 99,999.9

col Act_Used_MB for 99,999.9

SQL> SQL> col Max_Used_MB for 99,999.9

col TEMP_Seg_MB for 999,999

SQL> SQL> select to_char(sql_exec_start, ‘yyyy-mm-dd hh24:mi:ss’) start_time, sql_id,

to_number(decode(sid, 65535, NULL, sid)) sid,

2 3 operation_type operation, trunc(expected_size/1024/1024) expected_size_MB,

trunc(actual_mem_used/1024/1024) Act_Used_MB, trunc(max_mem_used/1024/1204) Max_Used_MB,

4 5 number_passes pass, trunc(tempseg_size/1024/1024) TEMP_Seg_MB

FROM v$sql_workarea_active

6 7 ORDER BY 1,2;

START_TIME SQL_ID SID OPERATION EXPECTED_SIZE_MB ACT_USED_MB MAX_USED_MB PASS TEMP_SEG_MB

——————– ————- —— ——————– —————- ———– ———– ———- ———–

2018-02-08 07:58:23 0k6yvhdy619tc 357 SORT (v2) 1.0 .0 870.0 1 2,955

COL NAME FOR A35

SELECT NAME, VALUE/1024/1024 VALUE_MB

FROM V$PGASTAT

WHERE NAME IN (‘aggregate PGA target parameter’,

‘total PGA allocated’,

‘total PGA inuse’,

‘over allocation count’);

NAME VALUE_MB

———————————– ———-

aggregate PGA target parameter 10240

total PGA inuse 1081.51172

total PGA allocated 1245.37207

over allocation count 0

Let me capture statistics again after query is executed.

SQL> SELECT name profile, cnt, DECODE(total, 0, 0, ROUND(cnt*100/total)) percentage

FROM (SELECT name, value cnt, (SUM(value) over ()) total

FROM V$SYSSTAT

WHERE name

LIKE ‘workarea exec%’); 2 3 4 5

PROFILE CNT PERCENTAGE

—————————————————————- ———- ———-

workarea executions – optimal 7711 100

workarea executions – onepass 8 0

workarea executions – multipass 0 0

SQL> SELECT NAME, VALUE, UNIT FROM V$PGASTAT

where NAME in ( ‘aggregate PGA target parameter’, ‘aggregate PGA auto target’, ‘total PG 2 A inuse ‘,

‘total PGA allocated’, ‘maximum PGA used for 3 auto workareas’, ‘cache hit percentage’, ‘over allocation count’); 3

NAME VALUE UNIT

———————————– ———- ————

aggregate PGA target parameter 1.0737E+10 bytes

aggregate PGA auto target 9517178880 bytes

total PGA allocated 218789888 bytes

over allocation count 0

cache hit percentage 50.57 percent

AWR report related to PGA:-

Test case 4:-

Now we will test about Non-tunable component like PL/SQL area and we will see effect of PGA_AGGREGATE_LIMIT.

Let me set the PGA parameters to less value

SQL> show parameter pga;

NAME TYPE VALUE

———————————— ———– ——————————

pga_aggregate_limit big integer 20G

pga_aggregate_target big integer 10G

SQL> alter system set pga_aggregate_target=1G;

System altered.

SQL> alter system set pga_aggregate_limit=2G;

System altered.

Now create PL/SQL table and execute it

SQL> create or replace package demo_pkg As

type array is table of char(2000) index by binary_integer;

g_data array;

end;

begin

for i in 1 .. 200000

loop

demo_pkg.g_data(i) := ‘x’;

end loop;

end;

Let me capture the statistics

SQL> SELECT name profile, cnt, DECODE(total, 0, 0, ROUND(cnt*100/total)) percentage

FROM (SELECT name, value cnt, (SUM(value) over ()) total

FROM V$SYSSTAT

WHERE name

LIKE ‘workarea exec%’); 2 3 4 5

PROFILE CNT

—————————————————————- ———-

PERCENTAGE

———-

workarea executions – optimal 7954

100

workarea executions – onepass 8

workarea executions – multipass 0

SQL> SELECT NAME, VALUE/1024/1024 VALUE_MB

FROM V$PGASTAT

WHERE NAME IN (‘aggregate PGA target parameter’,

‘total PGA allocated’,

‘total PGA inuse’,

‘over allocation count’); 2 3 4 5 6

NAME VALUE_MB

—————————————————————- ———-

aggregate PGA target parameter 1024

total PGA inuse 604.05957

total PGA allocated 656.980469

over allocation count 0

Now I will increase 2000000 to simulate error .This will cause PL/SQL table to allocate more memory in PGA.

SQL> begin

for i in 1 .. 2000000

loop

demo_pkg.g_data(i) := ‘x’;

end loop;

end;

/ 2 3 4 5 6 7

begin

ERROR at line 1:

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Let me capture statistics after failure

SQL> SELECT NAME, VALUE/1024/1024 VALUE_MB

FROM V$PGASTAT

WHERE NAME IN (‘aggregate PGA target parameter’,

‘total PGA allocated’,

‘total PGA inuse’,

‘over allocation count’); 2 3 4 5 6

NAME VALUE_MB

—————————————————————- ———-

aggregate PGA target parameter 1024

total PGA inuse 593.813477

total PGA allocated 627.732422

over allocation count .000016212

SQL> SELECT name profile, cnt, DECODE(total, 0, 0, ROUND(cnt*100/total)) percentage

FROM (SELECT name, value cnt, (SUM(value) over ()) total

FROM V$SYSSTAT

WHERE name

LIKE ‘workarea exec%’); 2 3 4 5

PROFILE CNT

—————————————————————- ———-

PERCENTAGE

———-

workarea executions – optimal 8067

100

workarea executions – onepass 8

workarea executions – multipass 0

Test case 5:

Now I will increase pga_aggregate_limit to 10G and check the behavior.The PL/SQL table will run fine now.

SQL> alter system set pga_aggregate_limit=10G;

System altered.

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> begin

for i in 1 .. 2000000

loop

demo_pkg.g_data(i) := ‘x’;

end loop;

end;

/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> SELECT NAME, VALUE/1024/1024 VALUE_MB

FROM V$PGASTAT

WHERE NAME IN (‘aggregate PGA target parameter’,

‘total PGA allocated’,

‘total PGA inuse’,

‘over allocation count’); 2 3 4 5 6

NAME VALUE_MB

—————————————————————- ———-

aggregate PGA target parameter 1024

total PGA inuse 4607.88965

total PGA allocated 4641.04688

over allocation count .000023842

AWR report status:-

Reference:-

https://docs.oracle.com/database/121/TGDBA/tune_pga.htm#TGDBA95344

http://oracleinaction.com/tune-pga-i/

Clustering Factor and it’s effect on non-assm and assm tablespace

Posted on February 5, 2018 by debasis maity

Clustering Factor and it’s effect on non-assm and assm tablespace.

The clustering factor is a measure of the ordernesss of an index in comparison to the table that it is based on. It is used to check the cost of a table lookup following an index access (multiplying the clustering factor by the index’s selectivity gives you the cost of the operation). The clustering factor records the number of blocks that will be read when scanning the index. If the index being used has a large clustering factor, then more table data blocks have to be visited to get the rows in each index block (because adjacent rows are in different blocks). If the clustering factor is close to the number of blocks in the table, then the index is well ordered, but if the clustering factor is close to the number of rows in the table, then the index is not well ordered. The clustering factor is computed by the following (explained briefly):

1. The index is scanned in order.
2. The block portion of the ROWID pointed at by the current indexed value is compared to the
previous indexed value (comparing adjacent rows in the index).
3. If the ROWIDs point to different TABLE blocks, the clustering factor is incremented (this is
done for the entire index).

The CLUSTERING_FACTOR column in the USER_INDEXES view gives an indication as to how organized the data is compared to the indexed columns. If the value of the CLUSTERING_FACTOR column value is close to the number of leaf blocks in the index, the data is well ordered in the table. If the value is not close to the number of leaf blocks in the index, then the data in the table is not well ordered. The leaf blocks of an index store the indexed values as well as the ROWIDs to which they point.

Test Case:-

A.In the test case I will create a non-assm tablespace .After that I will create a sequence and load data using the sequence from 30 parallel session.

SQL> create sequence id_seq;

Sequence created.

SQL> CREATE TABLESPACE data_non_assm

DATAFILE ‘+DATA’

SIZE 10M EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT MANUAL; 2 3 4

Tablespace created.

SQL> create table t (id number, sometext varchar2(50),my_date date) tablespace data_non_assm;

Table created.

SQL> create or replace procedure manyinserts as

begin

for i in 1..1000000 loop

insert into t values (id_seq.nextval, ‘DOES THIS CAUSE BUFFER BUSY WAITS?’,sysdate);

end loop;

commit;

end;

/ 2 3 4 5 6 7 8

Procedure created.

SQL> create or replace procedure manysessions as

v_jobno number:=0;

begin

FOR i in 1..30 LOOP

dbms_job.submit(v_jobno,’manyinserts;’, sysdate);

END LOOP;

commit;

end;

/ 2 3 4 5 6 7 8 9

Procedure created.

SQL> create index i_t on t(id);

Index created.

SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL>exec manysessions;

SQL> exec dbms_stats.gather_table_stats(‘C##TEST’,’T’);

PL/SQL procedure successfully completed.

B.In the test case I will create a assm tablespace .After that I will create a sequence and load data using the sequence from 30 parallel session.

SQL> CREATE TABLESPACE data_assm

DATAFILE ‘+DATA’

SIZE 5G EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO; 2 3 4

Tablespace created.

SQL> create table t (id number, sometext varchar2(50),my_date date) tablespace data_assm;

Table created.

SQL> create or replace procedure manyinserts as

begin

for i in 1..1000000 loop

insert into t values (id_seq.nextval, ‘DOES THIS CAUSE BUFFER BUSY WAITS?’,sysdate);

end loop;

commit;

end;

/ 2 3 4 5 6 7 8

Procedure created.

SQL> create or replace procedure manysessions as

v_jobno number:=0;

begin

FOR i in 1..30 LOOP

dbms_job.submit(v_jobno,’manyinserts;’, sysdate);

END LOOP;

commit;

end;

/ 2 3 4 5 6 7 8 9

Procedure created.

SQL> create index i_t on t(id) tablespace data_assm;

Index created.

SQL>exec manysessions;

SQL> exec dbms_stats.gather_table_stats(‘C##TEST’,’T’);

PL/SQL procedure successfully completed.

SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

Conclusion:-

For NON-ASSM,cluster factor is 8 times lower but buffer busy wait is much greater .Also DB CPU is greater in case of NON-ASSM.

	Cluster Factor	Buffer busy wait	DB CPU
Non-ASSM	3023653	29625561	7040
ASSM	25514790	7351694	2166

Index in 12c by example test case

Posted on February 4, 2018May 9, 2018 by debasis maity

A. B-Tree Indexes

I have discussed more details in the below blog.

Index internal oracle using simple test case

B.Invisible Indexes

Deciding which columns to index is hard. The primary key is automatically indexed, the foreign keys should also be indexed, but then what? Even more difficult is deciding which index to remove that might be a bad index. Every time a record is inserted, all of the indexes have to be updated. If the column of an index is updated, the index has to be updated.Use /+ use_invisible_indexes / hint to access all invisible indexes.

Test Case:-

SQL> create table t1 as select * from dba_objects;

SQL>create index I1_T1 on t1(OBJECT_ID);

SQL> select OBJECT_NAME from t1 where object_id=30;

---------------------------------------------------------------------------------------------

| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |       |     1 |    30 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |     1 |    30 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                  | I1_T1 |     1 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=30)

SQL>alter index I1_T1 invisible;

SQL> select OBJECT_NAME from t1 where object_id=30;

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    30 |   426   (1)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T1   |     1 |    30 |   426   (1)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID"=30)

SQL> select /*+ use_invisible_indexes */ OBJECT_NAME from t1 where object_id=30;

---------------------------------------------------------------------------------------------

| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------- -------------

|   0 | SELECT STATEMENT                    |       |     1 |    30 |     2   (0 )| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |     1 |    30 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                  | I1_T1 |     1 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

2 - access("OBJECT_ID"=30)

SQL>  select index_name,visibility from dba_indexes where index_name='I1_T1';

INDEX_NAME                                                                                                                       VISIBILIT

-------------------------------------------------------------------------------------------------------------------------------- ---------

I1_T1                                                                                                                            INVISIBLE

C.Multiple Types of Indexes on the Same Column(s)

There can be multiple types of indexes on the same column(s), but only one index may be set to visible at the same time, unless the other index is a function-based index (since a function-based index isn’t really on the same column; it’s on the function of the column). This feature is great for variable workloads on the same table. It is great to use different types of indexes for batch, query, or data warehousing at different times of the day or night.

Test case:-

In the test case we use both bitmap and btree index on same column but only one index will be visible at one time.

SQL> create index I2_t1 on t1(object_type);

SQL> alter index I2_t1 invisible;

SQL> create bitmap index T3_BM_T1 on t1(object_type);

SQL> alter index I2_T1 invisible;

SQL> alter index T3_BM_T1 visible;

SQL> select object_name from t1 where object_type='PROCEDURE';

Execution Plan

------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |          |  2068 | 70312 |   281   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1       |  2068 | 70312 |   281   (0)| 00:00:01 |

|   2 |   BITMAP CONVERSION TO ROWIDS       |          |       |       |            |          |

|*  3 |    BITMAP INDEX SINGLE VALUE        | T3_BM_T1 |       |       |            |          |

------------------------------------------------------------------------------------------------

3 - access("OBJECT_TYPE"='PROCEDURE')

SQL> alter index T3_BM_T1 invisible;

SQL> alter index I2_T1 visible;

SQL> select object_name from t1 where object_type='PROCEDURE';

---------------------------------------------------------------------------------------------

| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |       |  2068 | 70312 |   100   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |  2068 | 70312 |   100   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                  | I2_T1 |  2068 |       |     6   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_TYPE"='PROCEDURE')

It is possible to create two visible indexes on a column at once, if one of those indexes is a function-based index.

By using invisible indexes, in 12c, you can now create multiple indexes on the same column or columns. This can be helpful with variable workloads. Perhaps use a b-tree index for the daily workload and use a reverse key index at night. But, keep in mind that there is internal index maintenance and also costs for INSERT, DELETE and when you update the indexed column on all of these indexes.

D.Concatenated Indexes and Skip scan indexes

When a single index has multiple columns that are indexed, it is called a concatenated or composite index. While Oracle’s introduction of skip-scan index access has increased the optimizer’s options when using concatenated indexes, you should be careful when selecting the order of the columns in the index. In general, the leading column of the index should be the one most likely to be used in WHERE clauses and also the most selective column of the set.

Test case:-

SQL> create index I1_comp_t1 on t1(object_id,object_type);

SQL> drop index I2_T1;

SQL> drop index T3_BM_T1;

SQL> select object_name from t1 where object_id=30;

--------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |            |     1 |    30 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1         |     1 |    30 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                  | I1_COMP_T1 |     1 |       |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

--------------------------------------------------

2 - access("OBJECT_ID"=30)

Skip scan:-

As discussed in the section “Concatenated Indexes” , the index skip-scan feature enables the optimizer to use a concatenated index even if its leading column is not listed in the WHERE clause. Index skip-scans are faster than full scans of the index, requiring fewer reads to be performed.

SQL> select object_name from t1 where object_type='SEQUENCE';

--------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |            |  2068 | 70312 |   372   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1         |  2068 | 70312 |   372   (0)| 00:00:01 |

|*  2 |   INDEX SKIP SCAN                   | I1_COMP_T1 |  2068 |       |   314   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_TYPE"='SEQUENCE')

filter("OBJECT_TYPE"='SEQUENCE')

E.Index Fast full scan

The fast full scan can be used if all of the columns in the query for the table are in the index with the leading edge of the index not part of the WHERE condition.During a fast full scan of an index, Oracle reads all of the leaf blocks in a b-tree index. The index is
being read sequentially, so multiple blocks can be read at once. The DB_FILE_MULTIBLOCK_READ_COUNT parameter in the initialization file controls the number of blocks that can be read simultaneously. The fast full scan usually requires fewer physical I/Os than a full table scan, allowing the query to be resolved faster.

Test case:-
 SQL> create table t1(c1 number,c2 char(10),c3 date);

SQL>  insert into t1 select rownum,'A',sysdate from dual connect by rownum<100000;

SQL> commit;

SQL> insert into t1 select rownum,'B',sysdate from dual connect by rownum<100

SQL> commit;

SQL> exec dbms_stats.gather_table_stats('C##TEST','T1');

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly;

SQL> select c1 from t1 where c2='B';

Execution Plan

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 50049 |   782K|   137   (1)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T1   | 50049 |   782K|   137   (1)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("C2"='B')

SQL> create index I1_T1 on T1(c1,c2);

Index created.

SQL> exec dbms_stats.gather_table_stats('C##TEST','T1');

PL/SQL procedure successfully completed.

SQL> select c1 from t1 where c2='B';

Execution Plan

| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |    98 |  1568 |   104   (1)| 00:00:01 |

|*  1 |  INDEX FAST FULL SCAN| I1_T1 |    98 |  1568 |   104   (1)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("C2"='B')

F.Bitmap Index

Bitmap indexes are not generally recommended for online transaction processing (OLTP) applications. B-tree indexes contain a ROWID with the indexed value. So, when updating tables and their indexes, Oracle has the ability to lock individual rows. Bitmap indexes are stored as compressed indexed values, which can contain a range of ROWIDs. Therefore, Oracle has to lock the entire range of the ROWIDs for a given value. This type of locking has the potential to cause deadlock situations with certain types of DML statements. SELECT statements are not affected by this locking problem. A solution to updates is to drop the index, do the updating in batch during off-hours, and then rebuild the bitmap index (you could also add/drop an index on a column(s) that makes the update faster possibly as well).

Bitmap indexes have several restrictions:

Bitmap indexes are not considered by the rule-based optimizer.

Performing an ALTER TABLE statement and modifying a column that has a bitmap index built on it invalidates the index.

Bitmap indexes do not contain any of the data from the column and cannot be used for any type of integrity checking.

Bitmap indexes cannot be declared as unique.

Bitmap indexes have a maximum length of 30 columns.

Test case:-

In this test case we will test how bitmap join can be helpful over btree index.

1.First with bitmap index

SQL> create table test_bitmap(c1 number,c2 char(100),c3 char(100));

SQL> insert into test_bitmap select rownum,'A','AA' from dual connect by rownum<1000000;

SQL> insert into test_bitmap select rownum,'B','BB'  from dual connect by rownum<100000;

SQL> insert into test_bitmap select rownum,'C','CC' from dual connect by rownum<10000;

SQL> create bitmap index B_test_bitmap on test_bitmap(c2);

SQL> create bitmap index B_test_bitmap1 on test_bitmap(c3);

SQL> exec dbms_stats.gather_table_stats('C##TEST','TEST_BITMAP');

SQL> select c3 from test_bitmap where c2='B';

99999 rows selected.

Elapsed: 00:00:00.36

Execution Plan

----------------------------------------------------------

Plan hash value: 892894935

--------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |                  |   369K|    71M|  8665   (1)| 00:00:01 |

|*  1 |  VIEW                         | index$_join$_001 |   369K|    71M|  8665   (1)| 00:00:01 |

|*  2 |   HASH JOIN                   |                  |       |       |            |          |

|   3 |    BITMAP CONVERSION TO ROWIDS|                  |   369K|    71M|    12   (0)| 00:00:01 |

|*  4 |     BITMAP INDEX SINGLE VALUE | B_TEST_BITMAP    |       |       |            |          |

|   5 |    BITMAP CONVERSION TO ROWIDS|                  |   369K|    71M|    35   (0)| 00:00:01 |

|   6 |     BITMAP INDEX FULL SCAN    | B_TEST_BITMAP1   |       |       |            |          |

--------------------------------------------------------------------------------------------------

2.With Btree index.

SQL> create index I_TEST_BITMAP on TEST_BITMAP(C2);

Index created.

Elapsed: 00:00:02.98

SQL> create index I_TEST_BITMAP1 on TEST_BITMAP(C3);

Index created.

Elapsed: 00:00:02.09

SQL>  select c3 from test_bitmap where c2='B';

99999 rows selected.

Elapsed: 00:00:00.59

Execution Plan

------------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |               |   369K|    71M| 16675   (1)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_BITMAP   |   369K|    71M| 16675   (1)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                  | I_TEST_BITMAP |   369K|       |  5788   (1)| 00:00:01 |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("C2"='B')

Please note there is performance improvement in case of bitmap index join.

G.Reverse key index

When sequential data is loaded, the index may encounter I/O-related bottlenecks. During the data loads, one part of the index, and one part of the disk, may be used much more heavily than any other part. To remedy this problem, you should store your index tablespaces on disk architectures that permit the files to be physically striped across multiple disks.
Oracle provides reverse key indexes as another solution to this performance problem. When data is stored in a reverse key index, its values are reversed prior to being stored in the index. Thus, the values 1234, 1235, and 1236 are stored as 4321, 5321, and 6321. As a result, the index may update
different index blocks for each inserted row.

In the below test case,I will show how buffer busy wait can be minimized in case 30 parallel session insert data into a table using sequence.There is index on primary key of the table .

Test case:-

1.Using simple BTree index

SQL> CREATE TABLESPACE data_assm DATAFILE ‘+DATA’ SIZE 5G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

SQL> CREATE TABLESPACE ind_assm DATAFILE ‘+DATA’ SIZE 5G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; SQL> create table t (id number, sometext varchar2(50),my_date date) tablespace data_assm;

SQL> create or replace procedure manyinserts as
begin
for i in 1..1000000 loop
insert into t values (id_seq.nextval, ‘DOES THIS CAUSE BUFFER BUSY WAITS?’,sysdate);
end loop;
commit;
end;
/

Procedure created.

SQL> create or replace procedure manysessions as
v_jobno number:=0;
begin
FOR i in 1..30 LOOP
dbms_job.submit(v_jobno,’manyinserts;’, sysdate);
END LOOP;
commit;
end;
/

Procedure created.

SQL> create index i_t on t(id) tablespace ind_assm;

Index created.

SQL>exec manysessions; (This will start load data with 30 multiple session)

SQL> exec dbms_stats.gather_table_stats(‘C##TEST’,’T’);

PL/SQL procedure successfully completed.

SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

In AWR,top 10 wait event:-

2.Using reverse key index

SQL> CREATE TABLESPACE data_assm DATAFILE '+DATA' SIZE 5G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

SQL> CREATE TABLESPACE ind_assm DATAFILE '+DATA' SIZE 5G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; SQL> create table t (id number, sometext varchar2(50),my_date date) tablespace data_assm;

SQL> create or replace procedure manyinserts as
begin
for i in 1..1000000 loop
 insert into t values (id_seq.nextval, 'DOES THIS CAUSE BUFFER BUSY WAITS?',sysdate);
end loop;
commit;
end;
/

Procedure created.

SQL> create or replace procedure manysessions as
v_jobno number:=0;
begin
FOR i in 1..30 LOOP
dbms_job.submit(v_jobno,'manyinserts;', sysdate);
END LOOP;
commit;
end;
/

Procedure created.

SQL> create index i_t on t(id) reverse tablespace ind_assm;

Index created.

SQL>exec manysessions; (This will start load data with 30 multiple session)

SQL> exec dbms_stats.gather_table_stats('C##TEST','T');

PL/SQL procedure successfully completed.

SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

AWR

H.Function based index

You can create function-based indexes on your tables. Without function-based indexes, any query that performed a function on a column could not use that column’s index.

Test case:-

This test case will show when we need to create function based index and How we can avoid by modifying where clause.

create table test_func(c1 number,c2 date);

insert into test_func select rownum,sysdate from dual connect by rownum<100;

insert into test_func select rownum,sysdate-1 from dual connect by rownum<100;

insert into test_func select rownum,sysdate-2 from dual connect by rownum<100;

insert into test_func select rownum,sysdate-3 from dual connect by rownum<100;

insert into test_func select rownum,sysdate-4 from dual connect by rownum<100;

insert into test_func select rownum,sysdate-5 from dual connect by rownum<100;

insert into test_func select rownum,sysdate-6 from dual connect by rownum<100;

insert into test_func select rownum,sysdate-7 from dual connect by rownum<100;

insert into test_func select rownum,sysdate-8 from dual connect by rownum<100;

create index I_C2 on test_func(c2);

create index I_C2_FUNC on test_func(trunc(c2));

exec dbms_stats.gather_table_stats('C##TEST','TEST_FUNC');

SQL> select count(1) from test_func where trunc(c2)='03-FEB-18';

COUNT(1)

----------

100

Execution Plan



-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |     1 |     8 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |           |     1 |     8 |            |          |

|*  2 |   INDEX RANGE SCAN| I_C2_FUNC |    39 |   312 |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access(TRUNC(INTERNAL_FUNCTION("C2"))='03-FEB-18')

SQL> select count(1) from test_func where c2>'03-FEB-18' and c2<(to_date('03-FEB-18')+0.99999);

COUNT(1)

----------

100

Execution Plan

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    16 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |    16 |            |          |

|*  2 |   FILTER           |      |       |       |            |          |

|*  3 |    INDEX RANGE SCAN| I_C2 |    29 |   464 |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter(TO_DATE('03-FEB-18')+.999988425925925925925925925925925925

9259>'03-FEB-18')

3 - access("C2">'03-FEB-18' AND "C2"<TO_DATE('03-FEB-18')+.9999884259

259259259259259259259259259259)

filter(TRUNC(INTERNAL_FUNCTION("C2"))>=TRUNC('03-FEB-18') AND

TRUNC(INTERNAL_FUNCTION("C2"))<=TRUNC(TO_DATE('03-FEB-18')+.999988425925

9259259259259259259259259259))

I.Index organized table

Properties and restrictions

An IOT must contain a primary key.
Rows are accessed via a logical rowid and not a physical rowid like in heap-organized tables.
An IOT cannot be in a cluster.
An IOT cannot contain a column of LONG data type.
You cannot modify an IOT index property using ALTER INDEX (error ORA-25176), you must use an ALTER TABLE instead.

Advantages of an IOT

As an IOT has the structure of an index and stores all the columns of the row, accesses via primary key conditions are faster as they don’t need to access the table to get additional column values.
As an IOT has the structure of an index and is thus sorted in the order of the primary key, accesses of a range of primary key values are also faster.
As the index and the table are in the same segment, less storage space is needed.
In addition, as rows are stored in the primary key order, you can further reduce space with key compression.
As all indexes on an IOT uses logical rowids, they will not become unusable if the table is reorganized.

Row overflow area

If some columns of the table are infrequently accessed, it is possible to offload them into another segment named the overflow area. An overflow segment will decrease the size of the main (or top) segment and will increase the performance of statements that do not need access the columns in the overflow area. The overflow segments can reside in a tablespace different from the main segments.

Notes:

The overflow area can contains only columns that are not part of the primary key.
If a row cannot fit in a block, you must define an overflow area.
Consequently, the primary key values of an IOT must fit in a single block.

The columns of the table that are recorded in the overflow segment are defined using the PCTHRESHOLD and/or INCLUDING options of the OVERFLOW clause (see example below).

Test case:-

SQL> CREATE TABLE test_iot (c1 INTEGER PRIMARY KEY, c2 char(100),c3 date) ORGANIZATION INDEX INCLUDING c2 OVERFLOW;

SQL> insert into test_iot select rownum,'A',sysdate from dual connect by rownum<100000;

SQL> exec dbms_stats.gather_table_stats('C##TEST','TEST_IOT');

PL/SQL procedure successfully completed.

SQL> select count(1) from test_iot where c1=1;

COUNT(1)

----------

1

Execution Plan

----------------------------------------------------------------------------------------

| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                   |     1 |     5 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |                   |     1 |     5 |            |          |

|*  2 |   INDEX UNIQUE SCAN| SYS_IOT_TOP_92575 |     1 |     5 |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

How histogram can affect query plan change hence performance

Posted on January 22, 2018January 23, 2018 by debasis maity

How histogram can affect query plan change hence performance.

1.Let me create a test case.I am loading the table with following distribution.Obviously it means data is somewhat skewed.

Value:-1
Range:-10

Value:-2
Range:-100

Value:-3
Range:-1000

Value:-4
Range:-10000

Value:-5
Range:-100000

C##TEST@TESTDB1> create table tt1(c1 number,c2 char(10));

Table created.

C##TEST@TESTDB1> declare
begin
for j in 1..10 loop
2 3 4 insert into tt1 values(1,’A’);
5 end loop;
6 for j in 1..100 loop
7 insert into tt1 values(2,’A’);
8 end loop;
9 for j in 1..1000 loop
10 insert into tt1 values(3,’A’);
11 end loop;
12 for j in 1..10000 loop
13 insert into tt1 values(4,’A’);
end loop;
14 15 for j in 1..100000 loop
insert into tt1 values(5,’A’);
16 17 end loop;
18 commit;
19 end;
20 /

PL/SQL procedure successfully completed.

2.Gather statistics.

C##TEST@TESTDB1> EXEC DBMS_STATS.gather_table_stats(‘C##TEST’, ‘TT1’);

PL/SQL procedure successfully completed.

3.The query shows the statistics collection did not gather histogram.

C##TEST@TESTDB1> COLUMN column_name FORMAT A20

SELECT column_id,
column_name,
histogram
FROM user_tab_columns
WHERE table_name = ‘TT1’
ORDER BY column_id;

COLUMN_ID COLUMN_NAME HISTOGRAM
———- ——————– —————
1 C1 NONE
2 C2 NONE

4.Let me create index on C1 column.

C##TEST@TESTDB1> create index I1_TT1 on TT1(c1);

Index created.

5.Checking plan with c1=1.It must select 10 rows but query estimator will estimate the query would resturn 22222 rows (Total_rows/5)

C##TEST@TESTDB1> set serveroutput off;

C##TEST@TESTDB1> select /*+ gather_plan_statistics */ count(1) from tt1 where c1=1;

COUNT(1)
———-
10

C##TEST@TESTDB1> SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>’ALLSTATS LAST’)); 2

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
SQL_ID cvywn9uwakwgx, child number 0
————————————-
select /*+ gather_plan_statistics */ count(1) from tt1 where c1=1

Plan hash value: 1950687611

————————————————————————————–
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN| I1_TT1 | 1 | 22222 | 10 |00:00:00.01 | 2 |
————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

2 – access(“C1″=1)

19 rows selected.

6.5.Checking plan with c1=5.It must select 10 rows but query estimator will estimate the query would resturn 22222 rows (Total_rows/5)

C##TEST@TESTDB1> select /*+ gather_plan_statistics */ count(1) from tt1 where c1=5;

COUNT(1)
———-
100000

C##TEST@TESTDB1> SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>’ALLSTATS LAST’)); 2

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
SQL_ID af3u4z2rmuq3s, child number 0
————————————-
select /*+ gather_plan_statistics */ count(1) from tt1 where c1=5

Plan hash value: 1950687611

———————————————————————————————–
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.15 | 198 | 197 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.15 | 198 | 197 |
|* 2 | INDEX RANGE SCAN| I1_TT1 | 1 | 22222 | 100K|00:00:00.01 | 198 | 197 |
———————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

2 – access(“C1″=5)

19 rows selected.

7.Now let me collect frequency based histogram using below command.

C##TEST@TESTDB1> EXEC DBMS_STATS.gather_table_stats(ownname=>’C##TEST’,tabname=>’TT1′,method_opt=>’FOR COLUMNS C1′);

PL/SQL procedure successfully completed.

8.Check bucket distribution using following query.

C##TEST@TESTDB1> select
table_name
, column_name,
histogram,num_distinct,num_buckets from user_tab_col_statistics
Where table_name = ‘TT1’ and column_name = ‘C1’; 2 3 4 5

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS
—————————— ——————– ————— ———— ———–
TT1 C1 FREQUENCY 5 5

C##TEST@TESTDB1> SELECT endpoint_value,
endpoint_number,
endpoint_number – LAG(endpoint_number, 1, 0) OVER (ORDER BY endpoint_value) AS frequency
FROM user_tab_histograms
WHERE table_name = ‘TT1’
AND column_name = ‘C1’
ORDER BY endpoint_value; 2 3 4 5 6 7

ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY
————– ————— ———-
1 10 10
2 110 100
3 1110 1000
4 11110 10000
5 111110 100000

8.Checking plan with c1=1 again (Please re-parse the statement ).

It must select 10 rows and query estimator will also estimate the query would resturn 10 rows (due to histogram)

C##TEST@TESTDB1> select /*+ gather_plan_statistics */ count(1) from tt1 where c1= 1;

COUNT(1)
———-
10

C##TEST@TESTDB1> SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>’ALLSTATS LAST’)) 2
3 ;

PLAN_TABLE_OUTPUT
——————————————————————————–
SQL_ID 61sr5p68bawwz, child number 0
————————————-
select /*+ gather_plan_statistics */ count(1) from tt1 where c1= 1

Plan hash value: 1950687611

——————————————————————————–
——

——————————————————————————–
——

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 |
2 |

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 |
2 |

|* 2 | INDEX RANGE SCAN| I1_TT1 | 1 | 10 | 10 |00:00:00.01 |
2 |

——————————————————————————–
——

Predicate Information (identified by operation id):
—————————————————

2 – access(“C1″=1)

9.Checking plan with c1=5 again (Please re-parse the statement ).

It must select 100K rows and query estimator will also estimate the query would resturn 100K rows (due to histogram).
Also note It now goes for INDEX FAST FULL SCAN rather than INDEX RANGE SCAN.

C##TEST@TESTDB1> select /*+ gather_plan_statistics */ count(1) from tt1 where c1= 5;

COUNT(1)
———-
100000

C##TEST@TESTDB1> SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>’ALLSTATS LAST’)) 2 ;

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
SQL_ID 14pmbpytcgv7y, child number 0
————————————-
select /*+ gather_plan_statistics */ count(1) from tt1 where c1= 5

Plan hash value: 3548606959

——————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
——————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 226 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 226 |
|* 2 | INDEX FAST FULL SCAN| I1_TT1 | 1 | 100K| 100K|00:00:00.01 | 226 |
——————————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – filter(“C1″=5)

Adaptive Cursor Sharing:

Let’s start with the basics. Let’s create a table as a copy of dba_objects.

There is a security threat as applications that concatenates literals are soft targets for SQL Injection attacks. Not using literals, i.e. using Bind Variables eliminates that threat.

For every statement that is hard parsed, cursors are not being shared, and more memory is being consumed.

Hard Parsing implicitly increases latch contention, as Oracle must latch the shared pool and library cache while doing a hard parse.

Okay, let’s first run the following three queries, each one using a different object_id, i.e. a new literal for each statement:

So far, so good. So, let’s check how Oracle internally views each of these statements. For that let’s run the following statement:

And we can see the query of V$SQLAREA shows that the three statements require three different parent cursors. As shown by VERSION_COUNT, each parent cursor requires its own child cursor.

So, to overcome the problem of hard parsing every time, we can use bind variables to share the cursor.

Let’s have a look at the following example:

The VERSION_COUNT value of 1 indicates that the database reused the same child cursor rather than creating three separate child cursors. Using a bind variable made this reuse possible.

Let’s see whether supposedly similar statements can produce different execution plans or not.

Let’s first create an index on the object_id column of the table t.

Then let’s run the following three statements and see the execution plan for each one of them.

But before we move ahead let’s check the same three statements using bind variables. As a preparatory step before actually running our queries, let us flush the shared pool and buffer cache, just so we are sure about the results we get.

Adaptive cursor sharing works only if you are using bind variables and have a histogram on at least one of the columns that is being used in the WHERE clause of the query.

Let’s create a new table ‘S’ as a copy of dba_objects and gather statistics on it as well as create a histogram on the object_id column.

The next step would be to run a query on this table using a bind variable.

With Adaptive Cursor Sharing, two additional columns are there in V$SQL. IS_BND_SENSITIVE and IS_BIND_AWARE. A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable.

The optimizer uses the following criteria to decide whether a cursor is bind-sensitive:

The optimizer has peeked at the bind values to generate cardinality estimates.

The bind is used in an equality or a range predicate.

For each execution of the query with a new bind value, the database records the execution statistics for the new value and compares them to the execution statistics for the previous value. If execution statistics vary greatly, then the database marks the cursor bind-aware.

At this point, let us see what is the status of the columns IS_BIND_SENSITIVE and IS_BIND_AWARE for the query we ran. We notice that IS_BIND_SENSITIVE is flagged to ‘Y’ while IS_BIND_AWARE is flagged as ‘N’.

This means that Oracle is ‘sensitive’ to the fact that bind variable is being used and the optimal plan is predicated on the bind variable value, i.e. whatever value we pass in the bind variable subsequently will determine whether the plan is good, bad, or maybe somewhere in-between.

Let’s try one more time with a bind variable value not too different from what we used earlier:

The cursor continues to be bind sensitive. Now, let us do something different. Let us use a bind variable value which is significantly different, and let us see what we get:

So Oracle, now in its wisdom decides to make the cursor bind aware as well. Because the cursor is bind-aware, the database does the following:

Determines whether the cardinality of the new values falls within the same range as the stored cardinality. If it falls within that range it reuses the execution plan in the existing child cursor.

What happens if it doesn’t fall in the range of stored cardinality? In that case, Oracle does a hard parse and creates a new child cursor with a different execution plan, stores metadata about the predicate, including the cardinality of the bound values, in the cursor and executes the cursor.

So, the essential point here is, we now have two child cursors catering to two different sets of bind variable values.

So, in conclusion it can be said that Adaptive Cursor Sharing is a really cool feature in Oracle that allows us to use to use Bind Variables in an unfettered way, leveraging the reuse of child cursors without having to compromise with living with suboptimal execution plans.

First we need to load data

Exadata plan

Non-Exadata plan

Tkprof-exadata

tkprof-non-exadata

Statistics of how many blocks in table

Full table scan with count

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Full table scan with all data from table.Buffer fetched will be much higher than count

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Create index in both exadata and non-exadata

Statistics of how many blocks in Index

Index range scan

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Retrieve single block for index scan with table access

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Retrieve single block for index scan without table access

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Index fast scan to check behavior of index access

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

A.Enabling SPM manually to stabilize plan for a particular query

Test case:-

1.Let us create the data first.

I will create 2 tables and create some skewed data for example 99999 rows for ‘A’ and 99 rows for ‘B’

2.Now we will check the execution plan.It shows “INDEX FAST FULL SCAN” for below cases.

3.From above output ,Both cases we get same cost but ideally cost should be different if we could have histograms.I expect second query with less number of rows should go for index range scan.

4.Now We will check what was impact of plan change from AWR history snapshots.

5.Now I will create sql tuning set for the sql_id

6.Let us check sql tuning set result

7.Now let us pin the correct plan_hash_value for which I need my plan to be stabilized in spite of changing the bind value .