How to turn on the Adaptive Cursor Sharing at statement level even though, _optim_peek_user_binds=false.

Although, the Adaptive Cursor Sharing(ACS) is a very useful feature, but, if you experiences some performance problem of bind peeking, maybe you turn off the bind peeking feature(_optim_peek_user_binds=false). In my previous post, I mentioned that if you turn off the bind peeking then the ACS won’t work.

In this case, we use OPT_PARAM and BIND_AWARE hint for using ACS at statement level.

 SQL> show parameter _optim

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
_optim_peek_user_binds               boolean                FALSE
_optimizer_adaptive_cursor_sharing   boolean                FALSE
_optimizer_extended_cursor_sharing_r string                 NONE
el


var b1 number
exec :b1:=10;
 
select /*+BIND_AWARE opt_param('_optim_peek_user_binds','true') */
       count(*), max(c1), avg(c1)
from   t1
where  c2=:b1;
 
  COUNT(*)    MAX(C1)    AVG(C1)
---------- ---------- ----------
         1         10         10

select * from table(dbms_xplan.display_cursor);

SQL_ID  2xsn3xundg0yz, child number 0
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    17 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    17 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

select child_number, executions, buffer_gets,
       is_bind_sensitive, is_bind_aware
from   v$sql
where  sql_id='2xsn3xundg0yz';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
           0          1           3 Y                 Y

exec :b1:=1001; 

select /*+BIND_AWARE opt_param('_optim_peek_user_binds','true') */
       count(*), max(c1), avg(c1)
from   t1
where  c2=:b1;
 

  COUNT(*)    MAX(C1)    AVG(C1)
---------- ---------- ----------
     99000     100000    50500.5

 
SQL_ID  2xsn3xundg0yz, child number 1
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4134 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 99016 |  1643K|  4134   (1)| 00:00:50 |
---------------------------------------------------------------------------

select child_number, executions, buffer_gets,
       is_bind_sensitive, is_bind_aware
from   v$sql
where  sql_id='2xsn3xundg0yz';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
           0          1           3 Y                 Y
           1          1       14593 Y                 Y

The behavior of BIND_AWARE hint.

Oracle supports BIND_AWARE and NO_BIND_AWARE hints that could control the Adaptive Cursor Sharing. From the name of each hint, We could expect the behavior. The test of NO_BIND_AWARE hint is unnecessary. That hint is so clear. But, BIND_AWARE hint isn’t clear to me. So let’s try to test.

BIND_AWARE hint.
For the test, the disable parameter value would be needed.

alter session set "_optimizer_adaptive_cursor_sharing"=false;
alter session set "_optimizer_extended_cursor_sharing_rel"=false;

Next, execute query using hint. The test environment is all same as previous post.
From the test result, we know that BIND_WARE hint works well regardless of the parameter value.

var b1 number
exec :b1:=10; 
 
select /*+BIND_AWARE*/
       count(*), max(c1)
from   t1
where  c2=:b1;
  
  COUNT(*)    MAX(C1)
---------- ----------
         1         10 
 
select * from table(dbms_xplan.display_cursor);

SQL_ID  9avbm7xarm430, child number 0
------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    17 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    17 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

select child_number, executions, buffer_gets,
       is_bind_sensitive, is_bind_aware
from   v$sql
where  sql_id='9avbm7xarm430';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
           0          1           3 Y                 Y
    
exec :b1:=1001;
 
select /*+BIND_AWARE*/
       count(*), max(c1)
from   t1
where  c2=:b1;
  COUNT(*)    MAX(C1)
---------- ----------
     99000     100000
  
SQL_ID  9avbm7xarm430, child number 1
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4134 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 99016 |  1643K|  4134   (1)| 00:00:50 |
---------------------------------------------------------------------------
 
select child_number, executions, buffer_gets,
       is_bind_sensitive, is_bind_aware
from   v$sql
where  sql_id='9avbm7xarm430';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
           0          1           3 Y                 Y
           1          1       14593 Y                 Y

After the test, I wonder that BIND_AWARE hint can even work, even if “_optim_peek_user_binds”=false? As expected, if “_optim_peek_user_binds”=false then ACS can’t work. Because, if you turn off the feature of bind peeking then the oracle doesn’t peek any bind variable.

alter session set "_optim_peek_user_binds"=false; 

var b1 number
exec :b1:=10;
 
select /*+BIND_AWARE*/
       count(*), max(c1), min(c1)
from   t1
where  c2=:b1;
 
  COUNT(*)    MAX(C1)    MIN(C1)
---------- ---------- ----------
         1         10         10
 
select * from table(dbms_xplan.display_cursor);

SQL_ID  1wx1pmnfj5ajp, child number 0
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |    16 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    17 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |   100 |  1700 |    16   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX1 |   100 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
select child_number, executions, buffer_gets,
       is_bind_sensitive, is_bind_aware
from   v$sql
where  sql_id='1wx1pmnfj5ajp';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
           0          1           3 N                 N 

The mechanism of Adaptive Cursor Sharing.

Adaptive Cursor Sharing(ACS) was introduced in oracle 11g. Until 10g, the oracle only supports bind peeking (from 9i). But, bind peeking has a big problem, when the column in the where clause with the bind has a skew data.To solve that problem ACS need to use a column histogram. Now, try to test to understand the ACS.

(1) Create a table and insert skew data.

create table t1 (c1 number, c2 number, dummy char(1000));
 
insert into t1
select level, level, 'dummy'
from   dual
connect by level<=1000
union all
select level+1000, 1001, 'dummy'
from   dual
connect by level<=99000;
 
commit; 
 
create index idx1 on t1(c2) nologging;
exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'FOR ALL INDEXED COLUMNS SIZE 254');

(2) c2 column has (HEIGHT BALANCED) histogram.

select column_name, histogram from user_tab_columns where table_name='T1'
order by column_id;
 
COLUMN_NAME  HISTOGRAM
-----------  ----------------
C1           NONE
C2           HEIGHT BALANCED
DUMMY        NONE

(3) Now, let’s try to test.
The value of all hidden parameters that was related to ACS are default (_optimizer_adaptive_cursor_sharing=true, _optimizer_extended_cursor_sharing_rel=simple). First bind value is 10 and the number of rows is 1, so optimizer decided to index scan. it was good choice.

var b1 number
exec :b1:=10;
 
select /*_ACS1*/
       count(*), max(c1)
from   t1
where  c2=:b1;
 
  COUNT(*)    MAX(C1)
---------- ----------
         1         10
 
select * from table(dbms_xplan.display_cursor);
 
SQL_ID  as9xzr2tdxj5u, child number 0
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    17 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    17 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

And that cursor was marked IS_BIND_SENSITIVE=’Y’.

select child_number, executions, buffer_gets,
       is_bind_sensitive, is_bind_aware
from   v$sql
where  sql_id='as9xzr2tdxj5u';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
           0          1           3 Y                 N

Second bind value is 1001 and the number of rows is 99,000. So, in this case, full scan is better than index scan. But, against my expectations, the optimizer still has been decided to do index scan.
exec :b1:=1001;

 
select /*_ACS1*/
       count(*), max(c1)
from   t1
where  c2=:b1;
 
  COUNT(*)    MAX(C1)
---------- ----------
     99000     100000
 
select * from table(dbms_xplan.display_cursor);
 
SQL_ID  as9xzr2tdxj5u, child number 0
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    17 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    17 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

If that statement was executed one more time by same bind value (1001), then oracle will create a new child cursor(#1) and the optimizer will do full scan. And then, that cursor was marked IS_BIND_AWARE=’Y’.

select /*_ACS1*/
count(*), max(c1)
from t1
where c2=:b1;
 
 COUNT(*) MAX(C1)
---------- ----------
    99000 100000
 
select * from table(dbms_xplan.display_cursor);
         
SQL_ID  as9xzr2tdxj5u, child number 1
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4134 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 99016 |  1643K|  4134   (1)| 00:00:50 |
---------------------------------------------------------------------------
 
select child_number, executions, buffer_gets,
       is_bind_sensitive, is_bind_aware
from   v$sql
where  sql_id='as9xzr2tdxj5u';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
           0          2       14356 Y                 N
           1          1       14593 Y                 Y
 
The third bind value is 20 and the number of rows is 1. When we execute that sql statement, oracle creates a new child cursor(#2) and optimizer decided to index scan. From that time, the child cursor (#0) was no longer be used. That was marked IS_SHAERABLE='N'.
exec :b1:=20
 
elect /*_ACS1*/
       count(*), max(c1)
from   t1
where  c2=:b1;
 
select * from table(dbms_xplan.display_cursor);
 
SQL_ID  as9xzr2tdxj5u, child number 2
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    17 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 |    17 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
select child_number, executions, buffer_gets,
       is_bind_sensitive, is_bind_aware, is_shareable
from   v$sql
where  sql_id='as9xzr2tdxj5u';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
------------ ---------- ----------- ----------------- ------------- ------------
           0          2       14356 Y                 N             N
           1          1       14593 Y                 Y             Y
           2          1           3 Y                 Y             Y

The simple example of unpivot.

Unpivot provides very conveninent way, when we search on v$sql_shared_cursor view.
When, we want to know the reason of multiple child cursors, we look at the v$sql_shared_cursor view. But, that view isn’t user-friendly. In this case, we can use unpivot feature from oracle 11g like this. The oracle version of test environment is 11.2.0.3.

-- fnd_shared_cursor.sql
 
WITH v1 AS (
          SELECT *
          FROM   v$sql_shared_cursor
          WHERE  sql_id=:sql_id 
     )
SELECT sql_id ,
       child_number ,
       reason_type ,
       result
FROM   v1
unpivot (result FOR reason_type IN
(
-- columns are vary depend on oracle version
"UNBOUND_CURSOR",
"SQL_TYPE_MISMATCH",
"OPTIMIZER_MISMATCH",
"OUTLINE_MISMATCH",
"STATS_ROW_MISMATCH",
"LITERAL_MISMATCH",
"FORCE_HARD_PARSE",
"EXPLAIN_PLAN_CURSOR",
"BUFFERED_DML_MISMATCH",
"PDML_ENV_MISMATCH",
"INST_DRTLD_MISMATCH",
"SLAVE_QC_MISMATCH",
"TYPECHECK_MISMATCH",
"AUTH_CHECK_MISMATCH",
"BIND_MISMATCH",
"DESCRIBE_MISMATCH",
"LANGUAGE_MISMATCH",
"TRANSLATION_MISMATCH",
"BIND_EQUIV_FAILURE",
"INSUFF_PRIVS",
"INSUFF_PRIVS_REM",
"REMOTE_TRANS_MISMATCH",
"LOGMINER_SESSION_MISMATCH",
"INCOMP_LTRL_MISMATCH",
"OVERLAP_TIME_MISMATCH",
"EDITION_MISMATCH",
"MV_QUERY_GEN_MISMATCH",
"USER_BIND_PEEK_MISMATCH",
"TYPCHK_DEP_MISMATCH",
"NO_TRIGGER_MISMATCH",
"FLASHBACK_CURSOR",
"ANYDATA_TRANSFORMATION",
"PDDL_ENV_MISMATCH",
"TOP_LEVEL_RPI_CURSOR",
"DIFFERENT_LONG_LENGTH",
"LOGICAL_STANDBY_APPLY",
"DIFF_CALL_DURN",
"BIND_UACS_DIFF",
"PLSQL_CMP_SWITCHS_DIFF",
"CURSOR_PARTS_MISMATCH",
"STB_OBJECT_MISMATCH",
"CROSSEDITION_TRIGGER_MISMATCH",
"PQ_SLAVE_MISMATCH",
"TOP_LEVEL_DDL_MISMATCH",
"MULTI_PX_MISMATCH",
"BIND_PEEKED_PQ_MISMATCH",
"MV_REWRITE_MISMATCH",
"ROLL_INVALID_MISMATCH",
"OPTIMIZER_MODE_MISMATCH",
"PX_MISMATCH",
"MV_STALEOBJ_MISMATCH",
"FLASHBACK_TABLE_MISMATCH",
"LITREP_COMP_MISMATCH",
"PLSQL_DEBUG",
"LOAD_OPTIMIZER_STATS",
"ACL_MISMATCH",
"FLASHBACK_ARCHIVE_MISMATCH",
"LOCK_USER_SCHEMA_FAILED",
"REMOTE_MAPPING_MISMATCH",
"LOAD_RUNTIME_HEAP_FAILED",
"HASH_MATCH_FAILED",
"PURGED_CURSOR",
"BIND_LENGTH_UPGRADEABLE",
"USE_FEEDBACK_STATS")
)
where result='Y';
 
--Sample Output
SQL_ID        CHILD_NUMBER  REASON_TYPE          RESULT
c9q44tap900dw 1             EXPLAIN_PLAN_CURSOR  Y
c9q44tap900dw 2             EXPLAIN_PLAN_CURSOR  Y

From 11g, you can use DBMS_SPM to influence the execution plan without changing the sql statement.

Until 10g, oracle provided outline and sql_profile package to influence execution plan without changing sql statement. And from 11g, oracle provide DBMS_SPM.

1) Create a sample table and a index.

SQL> create table t1 as
select '1' col1 ,
       LEVEL col2 ,
       mod(LEVEL , 10000 ) col3 ,
       'dummy' col4
from   dual
connect by level <=100000; 

SQL> create unique index t1_uk on t1 (col1, col2, col3) nologging; 
SQL> create index t1_idx1 on t1 (col3) nologging; 
SQL> exec dbms_stats.gather_table_stats(user,'t1');

2) Execute the bad sql statement.
Oracle optimizer choose T1_UK index, but col2 range is wide. So that is bad choise.

SQL> var b1 char(1);
SQL> var b2 number;
SQL> var b3 number;
SQL> var b4 number
SQL> exec :b1:='1';
SQL> exec :b2:=1
SQL> exec :b3:=50000
SQL> exec :b4:=1000 

SQL> SELECT *
FROM   t1
WHERE  col1=:b1
AND    col2 BETWEEN :b2 AND :b3
AND    col3=:b4;
 
call     count       cpu    elapsed       disk      query    current       rows        mis   Wait Ela
------- ------  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.01       0.01          0          0          0          0          1       0.00
Exec         1      0.00       0.01          0          0          0          0          0       0.00
Fetch        1      0.06       0.06          0        151          0          5          0       0.00
------- ------  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total        3      0.07       0.07          0        151          0          5          1       0.00
 
Rows       Row Source Operation
---------- ---------------------------------------------------
         5 FILTER  (cr=151 pr=0 pw=0 time=45586 us)
         5  TABLE ACCESS BY INDEX ROWID T1 (cr=151 pr=0 pw=0 time=45509 us cost=4 size=17 card=1)
         5   INDEX RANGE SCAN T1_UK (cr=146 pr=0 pw=0 time=45307 us cost=3 size=0 card=1) 

3) Find sql_id of bad sql statement.

SQL> SELECT sql_id, plan_hash_value, sql_text from v$sql where sql_text like '%col2 BETWEEN :b2 AND :b3%'
 
SQL_ID         PLAN_HASH_VALUE  SQL_TEXT
-------------- ---------------- ---------------------------------------------
1mkrscbk593ut  555629870        SELECT * FROM   t1 WHERE  col1=:b1 AND    col 

4) Create a sql plan baseline for the bad sql statement.

$ sqlplus "/ as sysdba"
 
SQL> var cnt number;
SQL> exec :cnt:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'1mkrscbk593ut');

SQL> col sql_handle for a20
SQL> col plan_name  for a40

SQL> SELECT sql_handle ,
       plan_name ,
       enabled
FROM   dba_sql_plan_baselines
WHERE  signature IN (
                     SELECT exact_matching_signature
                     FROM   v$sql
                     WHERE  sql_id='1mkrscbk593ut'
       )
   
SQL_HANDLE           PLAN_NAME                                ENABLE
-------------------- ---------------------------------------- ------
SQL_32cd48c2f499da8d SQL_PLAN_35ma8sbu9mqnde4162105           YES 

5) Captured plan is not efficient, so it should be disabled.

 SQL> exec :cnt:=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_32cd48c2f499da8d', -
 plan_name=>'SQL_PLAN_35ma8sbu9mqnde4162105', -
 attribute_name=>'enabled', -
 attribute_value=>'NO');

6) Modify sql statement to use hint & execute it.

SQL> select /*+ index(t1 t1_idx1) */
      * from t1
 where col1=:b1
 and   col2 between :b2 and :b3
 and   col3=:b4
 
 call     count       cpu    elapsed       disk      query    current       rows        mis   Wait Ela
------- ------  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.01       0.00          0          0          0          0          1       0.00
Exec         1      0.01       0.01          0          0          0          0          0       0.00
Fetch        1      0.00       0.00          0         12          0          5          0       0.00
------- ------  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total        3      0.02       0.01          0         12          0          5          1       0.00
 
Event waited on                             Count Zero Ela     Elapse   AVG(Ela)   MAX(Ela)   Blocks
---------------------------------------- -------- -------- ---------- ---------- ---------- --------
SQL*Net message to client                       2        0       0.00       0.00       0.00        0
SQL*Net message from client                     2        0       0.00       0.00       0.00        0
 
Rows       Row Source Operation
---------- ---------------------------------------------------
         5 FILTER  (cr=12 pr=0 pw=0 time=739 us)
         5  TABLE ACCESS BY INDEX ROWID T1 (cr=12 pr=0 pw=0 time=670 us cost=11 size=17 card=1)
        10   INDEX RANGE SCAN T1_IDX1 (cr=2 pr=0 pw=0 time=282 us cost=1 size=0 card=10) 

7) Find sql_id and plan_hash_value for hinted sql statement.

SQL> SELECT sql_id, plan_hash_value, sql_text from v$sql where sql_text like '%/*+ index(t1 t1_idx1) */%'
 
SQL_ID         PLAN_HASH_VALUE    SQL_TEXT
-------------- ------------------ ---------------------------------------
6q33rurh1snrg  1633758621         select /*+ index(t1 t1_idx1) */ * from 

8) Change bad sql execution plan to hinted sql execution plan.

SQL> exec :cnt:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'6q33rurh1snrg',-
 plan_hash_value=> 1633758621, -
 sql_handle=>'SQL_32cd48c2f499da8d'); 
 
 *) sql_id => sql_id of hinted sql statement 
 *) plan_hash_value => plan_hash_value of hinted sql statement 
 *) sql_handle => sql_handle of bad sql statement

9) Run bad sql and check the result of execution plan.

SQL> SELECT *
FROM t1
WHERE col1=:b1
AND col2 BETWEEN :b2 AND :b3
AND col3=:b4;

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('1mkrscbk593ut', null)); 
PLAN_TABLE_OUTPUT
SQL_ID  1mkrscbk593ut, child number 0
-------------------------------------
SELECT * FROM   t1 WHERE  col1=:b1 AND    col2 BETWEEN :b2 AND :b3 AND
  col3=:b4
 
Plan hash value: 555629870 
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     4 (100)|          |
|*  1 |  FILTER                      |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    17 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_UK |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
--------------------------------------------------- 
   1 - filter(TO_NUMBER(:B2)<=TO_NUMBER(:B3))
   3 - access("COL1"=:B1 AND "COL2">=TO_NUMBER(:B2) AND "COL3"=TO_NUMBER(:B4)
              AND "COL2"<=TO_NUMBER(:B3))
       filter("COL3"=TO_NUMBER(:B4))

 SQL_ID  1mkrscbk593ut, child number 1
-------------------------------------
SELECT * FROM   t1 WHERE  col1=:b1 AND    col2 BETWEEN :b2 AND :b3 AND
  col3=:b4
 
Plan hash value: 1633758621 
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |    11 (100)|          |
|*  1 |  FILTER                      |         |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1      |     5 |    85 |    11   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_IDX1 |    10 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
--------------------------------------------------- 
   1 - filter(TO_NUMBER(:B2)<=TO_NUMBER(:B3))
   2 - filter(("COL2"<=TO_NUMBER(:B3) AND "COL1"=:B1 AND
              "COL2">=TO_NUMBER(:B2)))
   3 - access("COL3"=TO_NUMBER(:B4))
 
Note
-----
   - SQL plan baseline SQL_PLAN_35ma8sbu9mqnd97e27072 used for this statement

There are two child cursor (cursor number 0 and 1) in the v$sql. And, the execution plan of child cursor #1 is the same with the execution plan of hinted sql.

Identify root cause session using V$WAIT_CHAINS from 11g.

From 11g, oracle provide V$WAIT_CHAINS dynamic performance view.
It’s very powerful and convenient to use. So, you can easily idenfify root cause session among the many sessions using that view. The contents of V$WAIT_CHAINS view are very similar with the section of the output of ‘hanganalyze 1’. So, you don’t need to anymore execute ‘hanganalyze’ oradebug command. Instead, you just run below query.

SELECT decode( a.blocker_sid , NULL , '<chain id#' ||a.chain_id||'>' ) chain_id,
       RPAD( '+' , LEVEL , '-' ) ||a.sid sid,
       RPAD( ' ' , LEVEL , ' ' ) ||a.wait_event_text wait_event
FROM   V$WAIT_CHAINS  a
CONNECT BY PRIOR a.sid=a.blocker_sid
AND    PRIOR a.sess_serial#=a.blocker_sess_serial#
AND    PRIOR a.instance = a.blocker_instance START WITH a.blocker_is_valid='FALSE'
ORDER  BY a.chain_id ,
       LEVEL
/
 
CHAIN_ID             SID        WAIT_EVENT
-------------------- ---------- --------------------------------------------------
<chain id#1>         +190        SQL*Net message from client
                     +-4          enq: TX - row lock contention
<chain id#2>         +221        SQL*Net message from client
                     +-108        enq: TX - row lock contention
                     +--8          enq: TX - row lock contention
<chain id#3>         +67         SQL*Net message from client
                     +-155        enq: TX - row lock contention
                     +--146        enq: TX - row lock contention
                     +-156        enq: TX - row lock contention 
 

About partition incremental statistics in 11g

If you want to effectively manage global statistics of partitioned table, partition incremental statistics is good for you.

Partition incremental statistics (PIS) was introduced in 11g to improve the performance of gathering statistics on large partitioned table. Actually, Until 11g, many DBA hardly ever gather global statistics on partitioned table, if that table is very large. But, in some cases, the management of global statistics is very important in terms of performance. So, oracle introduced PIS. If you use PIS, oracle doesn’t gather global statistics via full table scan. Oracle gathers global statistics via partition full scan.
From a performance point, this is a very good idea. Let’s check on.

(1) Create partitioned table, data loading into p1_1, p1_2 and gather global statistics.

drop table p1;
 
create table p1
(
 c1 date,
 c2 number,
 dummy char(100)
)
partition by range (c1)
(
  partition p1_1 values less than (to_date('20130331','YYYYMMDD')),
  partition p1_2 values less than (to_date('20130430','YYYYMMDD')),
  partition p1_3 values less than (to_date('20130531','YYYYMMDD'))  
);
 
insert  into p1
select  to_date('20130301','YYYYMMDD'),
        level,
        'dummy1'
from    dual
connect by level<=100000;
 
insert  into p1
select  to_date('20130401','YYYYMMDD'),
        level,
        'dummy1'
from    dual
connect by level<=100000;
 
commit;
 
exec dbms_stats.gather_table_stats(user, tabname=>'P1', granularity=>'GLOBAL AND PARTITION');

(2) Data loading into p1_3 and gather global statistics without PIS.

insert into p1
select to_date('20130501','YYYYMMDD'),
       decode(mod(level,2),0,200000,999999),
       'dummy1'
from   dual
connect by level<=100000;
 
commit;
 
alter session set sql_trace=true;
 
exec dbms_stats.gather_table_stats(user, tabname=>'P1', granularity=>'GLOBAL AND PARTITION');
 
alter session set sql_trace=false;

Below is the result of sql trace. As you can see, oracle execute two sql statement. first one is gathered for partition statistics. and second one is gathered for global statistics.

select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  no_substrb_pad  */to_char(count("C1")),to_char(substrb(dump(min("C1"),16,0,
  32),1,120)),to_char(substrb(dump(max("C1"),16,0,32),1,120)),
  to_char(count("C2")),to_char(substrb(dump(min("C2"),16,0,32),1,120)),
  to_char(substrb(dump(max("C2"),16,0,32),1,120)),to_char(count("DUMMY")),
  to_char(substrb(dump(min("DUMMY"),16,0,32),1,120)),
  to_char(substrb(dump(max("DUMMY"),16,0,32),1,120))
from
 "SCOTT"."P1" t  where TBL$OR$IDX$PART$NUM("SCOTT"."P1",0,4,0,"ROWID") =:objn 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.01       0.00          0          0          0           0
Fetch        3      3.64       5.35       4265       6490          3           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      3.65       5.36       4265       6490          3           3
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE
    100000      66667     100000   APPROXIMATE NDV AGGREGATE
    100000     100000     100000    PARTITION RANGE SINGLE PARTITION: KEY KEY
    100000     100000     100000     TABLE ACCESS FULL P1 PARTITION: KEY KEY
 
 
select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  no_substrb_pad  */to_char(count("C1")),to_char(substrb(dump(min("C1"),16,0,
  32),1,120)),to_char(substrb(dump(max("C1"),16,0,32),1,120)),
  to_char(count("C2")),to_char(substrb(dump(min("C2"),16,0,32),1,120)),
  to_char(substrb(dump(max("C2"),16,0,32),1,120)),to_char(count("DUMMY")),
  to_char(substrb(dump(min("DUMMY"),16,0,32),1,120)),
  to_char(substrb(dump(max("DUMMY"),16,0,32),1,120))
from
 "SCOTT"."P1" t  /* NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      3.91       4.04       4246       5340          3           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      3.92       4.05       4246       5340          3           1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE
    287397     287397     287397   APPROXIMATE NDV AGGREGATE
    300000     300000     300000    PARTITION RANGE ALL PARTITION: 1 3
    300000     300000     300000     TABLE ACCESS FULL P1 PARTITION: 1 3

(3) Repeat step(1), and apply PIS to the table and check it.

-- repeat step(1)
exec dbms_stats.set_table_prefs(user, tabname=>'P1', pname=>'INCREMENTAL', pvalue=>'TRUE');
 
SQL> select dbms_stats.get_prefs('INCREMENTAL','SCOTT', 'P1') from dual;
DBMS_STATS.GET_PREFS('INCREMENTAL','SCOTT','P1')
--------------------------------------------------------------------------------
TRUE

Repeat step(2). In this case, oracle use PIS. Below is the result of sql trace. As you can see, there is no sql statement to gather global statistics, because, oracle can calculate global statistics using p1_3 partition statistics. For that, oracle stores the synopsis for each partition in SYSAUX. The “SYN” in the sql statement stands for synopsis.

select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  no_substrb_pad  */to_char(count("C1")),to_char(substrb(dump(min("C1"),16,0,
  32),1,120)),to_char(substrb(dump(max("C1"),16,0,32),1,120)),
  to_char(count("C2")),to_char(substrb(dump(min("C2"),16,0,32),1,120)),
  to_char(substrb(dump(max("C2"),16,0,32),1,120)),to_char(count("DUMMY")),
  to_char(substrb(dump(min("DUMMY"),16,0,32),1,120)),
  to_char(substrb(dump(max("DUMMY"),16,0,32),1,120))
from
 "SCOTT"."P1" t  where TBL$OR$IDX$PART$NUM("SCOTT"."P1",0,4,0,"ROWID") =:objn 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.01       0.00          0          0          0           0
Fetch        3      3.52       4.06       4412       6447          3           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      3.53       4.08       4412       6447          3           3

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE 
         2      66667     100000   APPROXIMATE NDV AGGREGATE 
    100000     100000     100000    PARTITION RANGE SINGLE PARTITION: KEY KEY 
    100000     100000     100000     TABLE ACCESS FULL P1 PARTITION: KEY KEY  

V$SQL_MONITOR displays bind values.

What are the bind values of a long running query?

When you’re monitoring a long running query, you have to know the bind values of that query. Because, depending on the bind values, the performance of query is very different. For example:

1. between :b1 and :b2

-> If the range of :b1 and :b2 is wide, in some cases, Full Table Scan is better than Index Range Scan.

2. col1 LIKE :b1||’%’

-> If that query is slow, we have to check the contents of :b1. Maybe :b1 is NULL or a very short character (e.g ‘A’).

3. col1 = :b1

-> If col1 column has an index and that query is slow, we have to check column suppressing.

and so on.

Until 10g, to find the bind values, we dump the process state by oradebug, as follows:

$ sqlplus "/ as sysdba"
SQL> oradebug setospid
SQL> oradebug dump processstate 10
SQL> oradebug tracefile_name

And then, search the “Bind#” in the trace file for that cursor. But, sometimes the bind values doesn’t exist in the tracefile. Morever, if the bind values are exist, It’s a little difficult to find the bind values.
Of course, from oracle 10g, oracle provides V$SQL_BIND_CAPTURE view. This view displays information on bind variables used by cursors. But, bind values aren’t always captured for this view.

To overcome this limiation, oracle 11g provide bind values by V$SQL_MONITOR view. This view displays SQL cursors which execution have been (or are being) monitored. SQL monitoring is automatically started when a SQL cursor runs parallel or when it has consumed at least 5 seconds of CPU or I/O time ( This is controlled by “_sqlmon_threshold”). Also, the view provide bind values in “BIND_XML” column. So, you can find the bind values as follows:

SELECT bv.name  name ,
       bv.pos   position,
       bv.type  type,
       bv.value value
FROM   v$sql_monitor s ,
       xmltable( '/binds/bind'
                  passing xmltype( s.binds_xml )
                  COLUMNS name  VARCHAR2( 30 )   path '@name' ,
                          pos   NUMBER           path '@pos',
                          type  VARCHAR2( 15 )   path '@dtystr' ,
                          value VARCHAR2( 4000 ) path '.'
               ) bv
WHERE  s.sql_id=&sql_id 
AND    s.sid=&sid
ORDER  BY bv.pos;
 
 
 
NAME POSITION TYPE VALUE
:B1 1 VARCHAR2(4000) 1
:B2 2 VARCHAR2(4000) 10000001
:B3 3 VARCHAR2(4000) 20000001 

DBMS_SQLTUNE.REPORT_SQL_MONITOR provide very convenient way to analyze the performance of Parallel Query.

Until 10g, analyzing the performance of parallel query isn’t easy work. For monitoring PQ, we usually use v$px_session, v$px_sesstat, v$session and so on. But, that views support only real time statistics. So, when we need to analyze the whole sql statstiics, we have to use 10046 trace. But, if we used 10046 trace, there will be many trace files on the dump destination. So, we could hardly analyze them. Recently my co-worker suggested dbms_sqltune package to analyze the PQ in 11g. That’s very amazing. We can analyze the PQ with GUI. The usage is very simple.

(1) Find SID or SQL_ID for running sessions

(2) We can make report using dbms_sqltune.report_sql_monitor.
‘A’ option is for ‘Active-X’ type output. ‘H’ option is for ‘HTML’ type output.

 
 set trim on
 set pagesize 0
 set linesize 1000
 set long 1000000
 set longchunksize 10000000
 set serveroutput off
 set heading off
 set feedback off
 set echo off
 SET TRIMSPOOL off
 set verify off
 set timing off
 column file_str new_value file_str;
 column rtype new_value rtype; 

 select decode(upper('&2'), 'T', 'TEXT', 'H', 'HTML', 'A', 'ACTIVE', 'TEXT') rtype from dual; 
 
 select to_char(sysdate, 'yyyymmdd')||'_'|| '&1' || decode(upper('&2'), 'T', '_T.out', 'H', '_H.html', 'A', '_A.html', '.out')file_str from dual;
 spool &file_str

 --for SQL
 --select dbms_sqltune.report_sql_monitor( sql_id => '&1', report_level=>'ALL', type=> '&rtype' ) from dual;
 
 --for SESSION
 select dbms_sqltune.report_sql_monitor( session_id => '&1', report_level=>'ALL', type=> '&rtype' ) from dual;

 spool end
 set heading on
 set feedback on
 set verify on
 set timing on 

Bellow Pictures are the output of dbms_sqltune.report_sql_monitor. It’s beautiful. The first picture is the output of HTML type. Others are the output of ACTIVE-X type.