dynamic SQL with DMLs

Once again, Christian Antognini is the reason for this entry (beside my curiosity of course).
In Chapter 8 - Parsing of his book Troubleshooting Oracle Performance he showed the advantage of explicit cursor handling. The package dbms_sql shows the most flexibility. Unfortunately he only showed SELECT statements, no DML (which I'm interrested for some reasons). So I had to do the tests myselve:

CREATE TABLE dyn_sql (id number, val varchar2(32));

DECLARE   v_cur_hdl         INTEGER;
  v_stmt_str        VARCHAR2(200);
  v_rows_processed  BINARY_INTEGER;
  gid               INTEGER;
  gval              VARCHAR2(32);
BEGIN   v_stmt_str := 'INSERT INTO dyn_sql VALUES (:gid, :gval)';
  v_cur_hdl := DBMS_SQL.OPEN_CURSOR(2); -- to avoid ORA-29471 in 11g  
  DBMS_SQL.PARSE(v_cur_hdl, v_stmt_str, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  1);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V01C01');
  
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  2);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V02C01');
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  COMMIT;

  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  3);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V03C02');

  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  4);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V04C02');
  
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  ROLLBACK;
  
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  5);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V05C03');
  
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  
  DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
 /

SQL> select * from dyn_sql;
        ID VAL
---------- ------
         1 V01C01
         2 V02C01
         5 V05C03

blubb
what are my findings?

1. DBMS_SQL works fine also for DMLs.
2. commit/rollback within an open cursor works fine.
3. closing a cursor implicite commits (direct sql-trace sequence: EXEC(insert) - CLOSE(insert) - EXEC(pl/sql) - CLOSE (pl/sql)Deleted after Chris commentar

There is an explicite warning in the documentation: Native Dynamic SQL is faster than DBMS_SQL. I did not test this. But be warned. (and test it yourselve, If you fear performance!)

minimal init.ora

my minimal init.ora has only 4 lines:

*.compatible='11.1.0.0.0'
*.control_files='/appl/oracle/oradata/BERX2/control01.ctl','/appl/oracle/oradata/BERX2/control02.ctl','/appl/oracle/oradata/BERX2/control03.ctl'
*.db_block_size=8192
*.db_name='BERX2'

where I have to check if I can omit the compatible. So it would be down to 3 lines. nice!
Without setting compatible, I get

ORA-00201: control file version 11.1.0.0.0 incompatible with ORACLE version
11.0.0.0.0
ORA-00202: control file: '/appl/oracle/oradata/BERX2/control01.ctl'

and a show parameter compatible gives

compatible                           string      11.0.0

strange, as the instance identifies itselve as

SQL> select version from v$instance;

VERSION
-----------------
11.1.0.7.0

So it seems the automatic value for compatible is derived only from the first Version number of v$instance.

sysstat_sample.sql

Based on Tanel Poders sample.sql and ses.sql I was interrested to get some informations about the System at all.
There are various discussions wether or not it is useful to have a look on the system when hunting a problem within a session. Nevertheless I decided I need such a tool. On the one hand to generate a 'good' baseline, on the other to have a quick, zentralised view. It's also of some use to compare the system-statistics to the values of one dedicated sessions to see if this session is the major reason for any changes or submit only a small amount, but might suffer from others in the system.

So here is the code:

select name,
  min(value)               "MIN",
  max(value)               "MAX",
  max(value) - min(value)  "overall_delta",
  max(delta)               "max_single_delta",
  sum(decode(delta,0,0,1)) "#changes"   -- count distinct 
from
  (with c as
     (select &1 counter from dual)
  select     rn,
 name,
 value,
     decode(rn,1,0,value-prev) delta  
from    ( select /*+ ordered use_nl(t) */
         r.rn,
 NAME,
 Value,
         LAG(VALUE) OVER (Partition BY NAME order by r.rn) prev
      from
        (select /*+ no_unnest */
 rownum rn,
 1 sample_dummy
          from dual
            connect by level <= (select counter from c)        ) r,        v$sysstat t      order by name, r.rn     )  ) syss
where   value     > 0
  and delta > 0
group by name
order by name
/

This piece of code samples through v$sysstat and generates for every entry (where the value > 0, to reduce the lines) a line with the minimal value, the maximal value, the delta between them (this could also be done by DBMS_LOCK.SLEEP) but also the greatest single step between 2 sequent samples.
There might also be other statistical functions of some interrest, but I have too little knowledge on statistics and how to use them (comments/suggestions welcome!).

The script can also be found here, where I will keep the most recent version all the time.

Composite Bitmap Indexes

In hist Book Troubleshoot Oracle Performance Christian Antognini wrote also about Composite Bitmap Indexes. (p400)
Unfortunately he only proclaims

Composite bitmap indexes are rarely created. This is because several indexes can be combined efficiently in order to apply a restriction. To see how powerful bitmap indexes are, let’s look at several queries. (p400)

without givin gany evidence. So I grabbed his scripts (thank you for providing them) and run some testcases (on 11.1.0.6):
I just created this additional bitmap index:

CREATE BITMAP INDEX bx_i_n456 on t (n4, n5, n6);

bitmap AND:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ *
FROM t
WHERE n4 = 6 AND n5 = 42 AND n6 = 11

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |      1 |      1 |00:00:00.01 |       7 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |      1 |00:00:00.01 |       6 |
|   3 |    BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       6 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   4 - access("N5"=42)
   5 - access("N6"=11)
   6 - access("N4"=6)

Cost: 3

composite bitmap index(CIB):

SELECT *
FROM t
WHERE n4 = 6 AND n5 = 42 AND n6 = 11

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |      1 |00:00:00.01 |       3 |
|*  3 |    BITMAP INDEX SINGLE VALUE | BX_I_N456 |      1 |        |      1 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------

   3 - access("N4"=6 AND "N5"=42 AND "N6"=11)

Cost:1

In this case the CBI wins.

bitmap OR:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |    797 |    767 |00:00:00.01 |     419 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |    767 |00:00:00.01 |       7 |
|   3 |    BITMAP OR                 |      |      1 |        |      1 |00:00:00.01 |       7 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       3 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   4 - access("N4"=6)
   5 - access("N6"=11)
   6 - access("N5"=42)

 Cost: 135

no hints:

SELECT *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |    797 |    767 |00:00:00.01 |     419 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |    767 |00:00:00.01 |       7 |
|   3 |    BITMAP OR                 |      |      1 |        |      1 |00:00:00.01 |       7 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       3 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   4 - access("N4"=6)
   5 - access("N6"=11)
   6 - access("N5"=42)

Cost:135

index_combine with BX_I_N456: 

SELECT /*+ index_combine(t BX_I_N456 i_n5 i_n6) */ *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11   

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |    797 |    767 |00:00:00.04 |     420 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |    767 |00:00:00.03 |       8 |
|   3 |    BITMAP OR                 |           |      1 |        |      1 |00:00:00.03 |       8 |
|   4 |     BITMAP MERGE             |           |      1 |        |      1 |00:00:00.03 |       4 |
|*  5 |      BITMAP INDEX RANGE SCAN | BX_I_N456 |      1 |        |    527 |00:00:00.01 |       4 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N6      |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |     BITMAP INDEX SINGLE VALUE| I_N5      |      1 |        |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------

   5 - access("N4"=6)
       filter("N4"=6)
   6 - access("N6"=11)
   7 - access("N5"=42)

Cost: 138

index_combine with BX_I_N456 on 2nd place: 

SELECT /*+ index_combine(t i_n4 BX_I_N456 i_n6) */ *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11   

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |    797 |    767 |00:00:00.01 |     420 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |    767 |00:00:00.01 |       8 |
|   3 |    BITMAP OR                 |           |      1 |        |      1 |00:00:00.01 |       8 |
|   4 |     BITMAP MERGE             |           |      1 |        |      1 |00:00:00.01 |       4 |
|*  5 |      BITMAP INDEX RANGE SCAN | BX_I_N456 |      1 |        |    527 |00:00:00.01 |       4 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N6      |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |     BITMAP INDEX SINGLE VALUE| I_N5      |      1 |        |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------

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

   5 - access("N4"=6)
       filter("N4"=6)
   6 - access("N6"=11)
   7 - access("N5"=42)

Costs: 138

in this case, the 3 seperated bitmap indices wins.

NOT EQUAL AND:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ *
FROM t
WHERE n4 != 6 AND n5 = 42 AND n6 = 11   

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |      1 |      1 |00:00:00.01 |       6 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |      2 |00:00:00.01 |       4 |
|   3 |    BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       4 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   1 - filter("N4"<>6)
   4 - access("N5"=42)
   5 - access("N6"=11)

Costs: 2

a different execution plan from Chris' Book, there a BITMAP MINUS was shown in the execution plan.
Maybe a question for a seperated session, where the BITMAP MINUS disappeared.

for some reason, this hint generated the BITMAP MINUS

SELECT /*+ index(t  BX_I_N456) */ *
FROM t
WHERE n4 != 6 AND n5 = 42 AND n6 = 11   

------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID   | T         |      1 |      1 |      1 |00:00:00.01 |      13 |
|   2 |   BITMAP CONVERSION TO ROWIDS  |           |      1 |        |      1 |00:00:00.01 |      12 |
|   3 |    BITMAP MINUS                |           |      1 |        |      1 |00:00:00.01 |      12 |
|   4 |     BITMAP MINUS               |           |      1 |        |      1 |00:00:00.01 |       8 |
|   5 |      BITMAP AND                |           |      1 |        |      1 |00:00:00.01 |       4 |
|*  6 |       BITMAP INDEX SINGLE VALUE| I_N5      |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |       BITMAP INDEX SINGLE VALUE| I_N6      |      1 |        |      1 |00:00:00.01 |       2 |
|   8 |      BITMAP MERGE              |           |      1 |        |      1 |00:00:00.01 |       4 |
|*  9 |       BITMAP INDEX RANGE SCAN  | BX_I_N456 |      1 |        |    526 |00:00:00.01 |       4 |
|  10 |     BITMAP MERGE               |           |      1 |        |      1 |00:00:00.01 |       4 |
|* 11 |      BITMAP INDEX RANGE SCAN   | BX_I_N456 |      1 |        |    526 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------------------------

   6 - access("N5"=42)
   7 - access("N6"=11)
   9 - access("N4"=6)
  11 - access("N4" IS NULL)

Cost: 4

So I decided to do a slightly different testcase:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ * 
FROM t 
WHERE n4 = 6 and n5 != 42 and n6 = 11

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID   | T    |      1 |      8 |      7 |00:00:00.01 |      15 |
|   2 |   BITMAP CONVERSION TO ROWIDS  |      |      1 |        |      7 |00:00:00.01 |       8 |
|   3 |    BITMAP MINUS                |      |      1 |        |      1 |00:00:00.01 |       8 |
|   4 |     BITMAP MINUS               |      |      1 |        |      1 |00:00:00.01 |       6 |
|   5 |      BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       4 |
|*  6 |       BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |       BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       2 |
|*  8 |      BITMAP INDEX SINGLE VALUE | I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
|*  9 |     BITMAP INDEX SINGLE VALUE  | I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------

   6 - access("N6"=11)
   7 - access("N4"=6)
   8 - access("N5"=42)
   9 - access("N5" IS NULL)

Costs: 6

CIB:

SELECT /*+ index(t BX_I_N456) */ * 
FROM t 
WHERE n4 =6 and n5 != 42 and n6 = 11

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |      8 |      7 |00:00:00.01 |      12 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |      7 |00:00:00.01 |       5 |
|*  3 |    BITMAP INDEX RANGE SCAN   | BX_I_N456 |      1 |        |      7 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------------

   3 - access("N4"=6)
       filter(("N6"=11 AND "N5"<>42 AND "N4"=6))

Costs: 107

no hints:

SELECT  * 
FROM t 
WHERE n4 =6 and n5 != 42 and n6 = 11    
    
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |      8 |      7 |00:00:00.01 |      12 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |      8 |00:00:00.01 |       4 |
|   3 |    BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       4 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   1 - filter("N5"<>42)
   4 - access("N6"=11)
   5 - access("N4"=6) 

Costs: 4

My a little bit more explicit view of composite bitmap indexes is:

• They can be useful in AND statements:
• even not optimal for OR statements, they can replace the bitmap index which is created only on the first column without high cost increasement
• in NOT EQUAL AND statements they really kill the performance if enfoced.