11g

Highly Dangerous Oracle Database Security Vulnerability

By Martin | February 26th, 2010 | Category: 10g, 11g, Bugs, Oracle Database, Security | No Comments »

I would like to draw your attention to a particularly dangerous security vulnerability, which was recently published by David Litchfield.

How dangerous is the vulnerability?

Any database user, who has “create session” privilege, which means, who can log into the database, can use the security hole to execute any OS command in the ownership of the oracle database owner. This means, that both denial of service as well as access to all data is exposed.

Which versions are affected?

Affected are database versions 10.2.0.4 (incl. 10.2.0.4.3 containing latest security patches as of January 2010) as well as 11g (incl. 11.2.0.1).

What can I do to close this security vulernability?

You can revoke privileges from PUBLIC:

revoke execute on dbms_java from PUBLIC;
revoke execute on dbms_java_test from PUBLIC;
revoke execute on “oracle/aurora/util/Wrapper” from PUBLIC;
grant execute on sys.dbms_jvm_exp_perms to IMP_FULL_DATABASE;
grant execute on sys.dbms_jvm_exp_perms to EXP_FULL_DATABASE;
revoke execute on sys.dbms_jvm_exp_perms from PUBLIC;

If you are using a third party vendor application, you should contact your vendor to check compatibility with revoked privileges or test before implementing in production.

Speech at DOAG Conference 2009 – RAC PreProduction Testing

By Martin | November 20th, 2009 | Category: 10g, 11g, Oracle Database, Real Application Clusters | No Comments »

I just came back from the DOAG Conference 2009, the german Oracle user group conference in Nürnberg where I had a speech about RAC PreProduction Testing. I have uploaded the slides and the paper to the papers section. At this time, the presentation is available in german only.

Oracle Clusterware / ASM 11.1.0.7: ASM Instance crash

By Martin | October 28th, 2009 | Category: 11g, Bugs, Linux, MetaLink, Oracle Database, Solaris, Unix | No Comments »

During a 11gR1 Clusterware installation for a Single Instance Failover Cluster at a customer site, I have experienced an interesting behaviour, which was caused by an Oracle Bug.

The environment was:

2 Node Oracle Clusterware 11.1.0.7 Cluster on Linux x86_64 using latest Recommended Patches as of October 19th. (pre PSU 11.1.0.7.1)
Clusterware installed as unix user crs
ASM installed as unix user oracle

The ASM instances could be started with SQL*Plus without any problems, but if the ASM instances were started by means of clusterware using srvctl (either from root, crs or oracle) the ASM instances would crash at diskgroup mount with:

ORA-07445: exception encountered: core dump  [sskgds_find_rtn_hdr()+1171]
[SIGBUS] [ADDR:0x2AACD701342C] [PC:0x25799DF]
[Non-existent physical address] []

Oracle Support identified this behaviour as Bug 6952915, for which there are patches for Linux x86, x86_64 and Solaris Sparc64.

Patch Set Update (PSU) October 2009 released

By Martin | October 21st, 2009 | Category: 10g, 11g, MetaLink, Oracle Database | No Comments »

Oracle has released the October 2009 Patch Set Update (PSU) which contains several interesting news:

first PSU, which is available for Grid Control 10.2.0.5
seperate PSU Patches for Clusterware (CRS)
Patch Set Updates are now released for ALL non-Windows platform whereas previous PSUs were released for special platforms (e.g. Linux Itanium) on request only.

More info can be found in Metalink Note 854428.1.

Oracle Database version 11g Release 2 for Linux finally released

By Martin | September 2nd, 2009 | Category: 11g, Linux, Oracle Database | No Comments »

Yesterday, on September 1st, Oracle released the much awaited database version 11gR2 for Linux x86 for both 32bit and 64bit platforms. The software can be downloaded via OTN: http://www.oracle.com/technology/software/products/database/index.html.

Out-of-Memory killer on 32bit Linux with big RAM

By Martin | August 25th, 2009 | Category: 10g, 11g, 9iR2, Linux, Oracle Database | 1 Comment »

It is not very known that you can run into serious problems if you run Linux x86-32bit with a big amount of RAM installed, if using RHEL below 5. The official name for the issue is called “Low Memory Starvation”. The best solution is to use x86-64bit to be able to address the whole amount of RAM efficiently.

However, if that is not feasible, then make sure that you at least run the hugemem kernel if you use RHEL < 5. In RHEL5-32bit, the hugemem kernel is default. A quick demonstration about what can happen if you don´t use hugemem kernel is shown here: We realized that RMAN backup is taking more than 24 hours. Querying v$session, we find out that one session is in ACTION "STARTED", whereas the other sessions are FINISHED.

SQL> select program, module,action 
      from v$session 
      where username = 'SYS' and program like 'rman%'
/      

PROGRAM                    MODULE                       ACTION             
-------------------------- ---------------------------  -------------------
rman@ora-vm1 (TNS V1-V3)    backup full datafile        0000078 FINISHED129
rman@ora-vm1 (TNS V1-V3)    backup full datafile        0000272 STARTED16  
rman@ora-vm1 (TNS V1-V3)    backup full datafile        0000084 FINISHED129
rman@ora-vm1 (TNS V1-V3)    rman@ora-vm1 (TNS V1-V3)                       
rman@ora-vm1 (TNS V1-V3)    rman@ora-vm1 (TNS V1-V3)    0000004 FINISHED131
rman@ora-vm1 (TNS V1-V3)    backup full datafile        0000092 FINISHED129

Then we check the SID,serial# from v$session of this session and also query the UNIX PID from v$process.spid

SQL> select sid,serial# from v$session where event like 'RMAN%';

       SID    SERIAL#
---------- ----------
      4343       5837

We activate SQL Tracing for this session to determine its activity:

SQL> select spid from v$process where addr = 
   (select paddr from v$session where sid = 4343);

SPID
------------
1681

SQL> begin dbms_monitor.session_trace_enable(4343,5837,true,true);
  2  end;
  3  /

However, no trace file gets created. Then we start tracing system calls with strace:

ora-vm1:# strace -fp 1681
attach: ptrace(PTRACE_ATTACH, ...): Operation not permitted

“Not permitted”? – Although I am connected as root?

ps -ef|grep 1681
oracle    1681 1582  0 Aug24 ?        00:00:09 [oracle] <defunct>

The linux command “ps” reports the server process as “defunct”.

ora-vm1:/usr/oracle/admin/labo/udump$ ps -ef|grep 1582
oracle   1582 21578  0 Aug24 ?        00:00:02 rman oracle/product/10.2.0/bin/rman nocatalog
oracle   21663 1582  0 Aug24 ?        00:00:01 oraclelabo (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   21665 1582  0 Aug24 ?        00:00:03 oraclelabo (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   1681 1582   0 Aug24 ?        00:00:09 [oracle] <defunct>
oracle   21691 1582  0 Aug24 ?        00:01:36 oraclelabo (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   21695 1582  0 Aug24 ?        00:01:41 oraclelabo (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   21793 1582  0 Aug24 ?        00:01:30 oraclelabo (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

Next, I checked logfile /var/log/messages.1 and realized that the kernel out-of-memory killer (OOM) killed this PID because of low memory starvation.

/var/log/messages.1:
Aug  24 22:32:44 ora-vm1 kernel: Out of Memory: Killed process 1681 (oracle).

Book review: HOWTO Secure and Audit Oracle 10g and 11g

By Martin | June 27th, 2009 | Category: 10g, 11g, Oracle Database, Security | No Comments »

I have added a new book review to my bookshelf: HOWTO Secure and Audit Oracle 10g and 11g – Ron Ben Natan

ASSM Problem with too low PCTFREE

By Martin | April 9th, 2009 | Category: 10g, 11g, Bugs, MetaLink, Oracle Database | 3 comments

During the Hotsos Symposium 2009 Training Day, Jonathan Lewis presented a problem which appears even on current 10g/11g databases. What is especially interesting is how this issue can be diagnosed. I reproduced the problem in 11.1.0.7 and will provide the steps you can use to verify. The problem can be demonstrated best when comparing response time of an update statement for 8k blocksize and 16k blocksize.

8k Blocksize

SQL> DROP TABLE t1_8k purge;
 
SQL> CREATE TABLESPACE DEMO8K datafile SIZE 128M blocksize 8192;
 
TABLESPACE created.
 
Elapsed: 00:00:07.35
 
SQL> CREATE TABLE mdecker.t1_8k
(n1 NUMBER,
 n2 NUMBER)
 TABLESPACE DEMO8K;
 
TABLE created.
 
Elapsed: 00:00:00.02
 
SQL> INSERT INTO t1_8k
SELECT TRUNC(dbms_random.VALUE(10000000,100000000)) n1,
           TO_NUMBER(NULL) AS n2
    FROM dual
CONNECT BY LEVEL <= 500000
/
 
 
500000 ROWS created.
 
Elapsed: 00:00:06.89
 
SQL> BEGIN dbms_stats.gather_table_stats(
        ownname => 'MDECKER',
        tabname => 'T1_8K',
        estimate_percent => 100);
END;
/
 
PL/SQL PROCEDURE successfully completed.
 
Elapsed: 00:00:02.13
 
SQL> SELECT num_rows,blocks FROM dba_tables WHERE table_name = 'T1_8K';
 
  NUM_ROWS     BLOCKS
---------- ----------
    500000        874
 
Elapsed: 00:00:00.15
SQL> UPDATE t1_8k SET n2 = n1;
 
500000 ROWS updated.
 
Elapsed: 00:01:09.04
SQL> COMMIT;

16k Blocksize

SQL> CREATE TABLESPACE DEMO16K datafile SIZE 128M blocksize 16384;
 
TABLESPACE created.
 
Elapsed: 00:00:14.75
 
SQL> CREATE TABLE mdecker.t1_16k
(n1 NUMBER,
 n2 NUMBER)
 TABLESPACE DEMO16K;
 
TABLE created.
 
Elapsed: 00:00:00.03
 
SQL> INSERT INTO t1_16k
SELECT TRUNC(dbms_random.VALUE(10000000,100000000)) n1,
           TO_NUMBER(NULL) AS n2
    FROM dual
CONNECT BY LEVEL <= 500000
/  
 
 
500000 ROWS created.
 
Elapsed: 00:00:05.51
SQL> BEGIN dbms_stats.gather_table_stats(
        ownname => 'MDECKER',
        tabname => 'T1_16K',
        estimate_percent => 100);
END;
/  
 
PL/SQL PROCEDURE successfully completed.
 
Elapsed: 00:00:01.78
SQL> SELECT num_rows,blocks FROM dba_tables WHERE table_name = 'T1_16K';
 
  NUM_ROWS     BLOCKS
---------- ----------
    500000        436
 
Elapsed: 00:00:00.07
 
SQL>  UPDATE t1_16k SET n2 = n1;
 
500000 ROWS updated.
 
Elapsed: 00:20:20.89

As you can see, the update statement for the 8k blocksize table took around 69 seconds whereas the same update for the table in the 16k tablespace took more than 20 minutes. When executing oradebug short_stack, you can see that for the 16k update, the stacktrace is similar for many executions. So, a lot of time is spent in the kernel functions ktspfsrch() and ktspscan_bmb().

SQL> oradebug setospid 23668 Oracle pid: 18, Unix process pid: 23668, image: oracle@ora-vm1.intra (TNS V1-V3) SQL> oradebug short_stack .....ktspfsrch()+559<-ktspscan_bmb()+315 ..... SQL> oradebug short_stack .....ktspfsrch()+559<-ktspscan_bmb()+315 ..... SQL> oradebug short_stack .....ktspfsrch()+559<-ktspscan_bmb()+315 .....

It is important to understand that the problem is not necessarily related to the blocksize, but to the PCTFREE value. More information about this topic can be found here:

http://structureddata.org/files/jl_test_case.html
http://structureddata.org/2008/09/08/understanding-performance/
http://www.oraclealchemist.com/oracle/hey-guys-does-size-matter/

Autostart Oracle Services at Clusterware startup

By Martin | March 19th, 2009 | Category: 10g, 11g, Real Application Clusters | No Comments »

I recently recommended the utilization of Oracle Services to a new client. I knew that you have to start a service manually if you restart your cluster. I told them that I will investigate on how to set the services to autostart. I knew that there is an AUTO_START property of the clusterware service resource set to “restore”, which means that at instance crash, Clusterware will restore the services to the status before crash. However, on manual shutdown/startup, the services will not be autostarted.

[root@ora-vm1 tmp]# crs_stat -p ora.MDDB.S_TEST.MDDB1.srv
NAME=ora.MDDB.S_TEST.MDDB1.srv
TYPE=application
AUTO_START=restore
…

I did some research and learned that the easiest way to configure autostart is with user callouts. A quick web search referred me to the OTN site “Real Application Clusters Sample Scripts” which contains a FAN callout script to autostart services.

The steps to install the scripts are:

Copy them to $ORA_CRS_HOME/racg/usrco on both nodes
Set permissions to 710 and ownership to oracle:oinstall or whatever your installation is using.
Modify perl Scripts to contain your ORA_CRS_HOME, ORACLE_HOME. Moreover, if you have used short hostnames for your CRS installation, you have to change “/bin/hostname” to “/bin/hostname -s”.

I just realized that Dan Norris also blogged about this topic.

Intelligent (aka adaptive) Cursor Sharing Problem

By Martin | March 12th, 2009 | Category: 11g, Bugs, MetaLink, Oracle Database | 4 comments

Today, at the last day of Hotsos Symposium 2009 Ric van Dyke presented on “Intelligent (aka adaptive) Cursor Sharing”, a new feature of Oracle Database 11g. The feature was created to fix the shortcomings of bind peeking on columns with histograms. Tom Kyte presented a few years ago on this topic when he talked about the impact of the wheather on Oracle database performance.

During the very interesting presentation Ric presented with a demo how this feature works but also showed a nasty side effect. He tested it on a million row table with a column “object type”, which was skewed. He bound several different values to the object_type bind variable causing 2 different execution plans to be used for the same sql_id. The problem with it was that he ended up with dozens of child cursors showing V$SQL.IS_SHARABLE => N.

It was not known whether this is a bug or whether the whole concept is flawed. I searched on metalink and found the appropriate document:

Bug 6644714 – High number of child cursors with adaptive cursor sharing
This issue is fixed in

* 11.2 (Future Release)
* 11.1.0.7 (Server Patch Set)
Lots of child cursors may be produced with adaptive cursor sharing enabled,
sometimes with overlapping or same bind value selectivity ranges (in v$sql_cs_selectivity).
This problem shows as LOAD_OPTIMIZER_STATS=’Y’ for many of the cursors in V$SQL_SHARED_CURSOR.

– and –

Bug 7213010 – Adaptive cursor sharing generates lots of child cursors

This issue is fixed in

* 11.2 (Future Release)
* 11.1.0.7 (Server Patch Set)

Lots of child cursors may be produced for a query with bind variables
appearing in at least two simple relational predicates
(eg. col :b).

v$sql_cs_selectivity shows that there are multiple equivalent / overlapping
ranges for a single cursor. The number of ranges gets bigger and bigger
as the query is run more times.

I then tried to verify if the problem indeed is gone in 11.1.0.7 and found out, that it isn´t.

Here is my testcase:

SQL> @/tmp/setup.sql
Connected.
SQL> DROP TABLE tab1 purge;
 
TABLE dropped.
 
SQL> CREATE TABLE tab1 (a INT,
  2                     b varchar2(20),
  3                                     padding varchar2(200))
  4  tablespace USERS;
 
TABLE created.
 
SQL>
SQL> DROP SEQUENCE myid;
 
SEQUENCE dropped.
 
SQL> CREATE SEQUENCE myid cache 1000;
 
SEQUENCE created.
 
SQL>
SQL> BEGIN
  2  FOR i IN 1..100000
  3  loop
  4          INSERT INTO tab1 VALUES (myid.nextval,
  5                                                           'AAAAAAAAAA',
  6                                                           rpad('*',200,'*'));
  7  END loop;
  8
  9  FOR i IN 1..1000
 10  loop
 11          INSERT INTO tab1 VALUES (myid.nextval,
 12                                                           'BBBBBBBBBB',
 13                                                           rpad('*',200,'*'));
 14  END loop;
 15
 16  FOR i IN 1..1000
 17  loop
 18          INSERT INTO tab1 VALUES (myid.nextval,
 19                                                           'CCCCCCCCCC',
 20                                                           rpad('*',200,'*'));
 21  END loop;
 22
 23  FOR i IN 1..100
 24  loop
 25          INSERT INTO tab1 VALUES (myid.nextval,
 26                                                           'DDDDDDDDDD',
 27                                                           rpad('*',200,'*'));
 28  END loop;
 29
 30  FOR i IN 1..10
 31  loop
 32          INSERT INTO tab1 VALUES (myid.nextval,
 33                                                           'EEEEEEEEEE',
 34                                                           rpad('*',200,'*'));
 35  END loop;
 36
 37  INSERT INTO tab1 VALUES (myid.nextval,
 38                                                           'FFFFFFFFFF',
 39                                                           rpad('*',200,'*'));
 40
 41  commit;
 42  END;
 43  /
 
PL/SQL PROCEDURE successfully completed.
 
SQL>
SQL> CREATE INDEX tab1_idx1 ON tab1 (b);
 
INDEX created.
 
SQL>
SQL> BEGIN dbms_stats.gather_table_stats(ownname => 'MDECKER',
  2                                          tabname => 'TAB1',
  3                                          method_opt =>'FOR ALL COLUMNS SIZE 254',
  4                                          cascade=> TRUE,
  5                                          estimate_percent => 100,
  6                                          no_invalidate => FALSE);
  7  END;
  8  /
 
PL/SQL PROCEDURE successfully completed.
 
SQL>
SQL> SET LINES 300
SQL> col TABLE_NAME format a10
SQL> col owner format a8
SQL> SELECT owner, TABLE_NAME,num_rows, blocks, round((blocks*8192)/1024/1024) AS MB
  2  FROM dba_tables WHERE TABLE_NAME = 'TAB1' AND owner = 'MDECKER';
 
OWNER    TABLE_NAME   NUM_ROWS     BLOCKS         MB
-------- ---------- ---------- ---------- ----------
MDECKER  TAB1           102111       3268         26
 
SQL>
SQL> prompt "Press Enter to continue"
"Press Enter to continue"
SQL> pause
 
SQL>
SQL> /*
SQL> OWNER       TABLE_NAME   NUM_ROWS  BLOCKS         MB
SQL> -------- ---------- ---------- ---------- ----------
SQL> MDECKER  TAB1           102111       3268         26
SQL>
SQL> */
SQL>
SQL>
SQL>
SQL> SELECT b , COUNT(*) FROM mdecker.tab1 GROUP BY b ORDER BY 1 DESC;
 
B                      COUNT(*)
-------------------- ----------
FFFFFFFFFF                    1
EEEEEEEEEE                   10
DDDDDDDDDD                  100
CCCCCCCCCC                 1000
BBBBBBBBBB                 1000
AAAAAAAAAA               100000
 
6 ROWS selected.
 
SQL>
SQL> /*
SQL> B                      COUNT(*)
SQL> -------------------- ----------
SQL> FFFFFFFFFF                    1
SQL> EEEEEEEEEE                   10
SQL> DDDDDDDDDD                  100
SQL> CCCCCCCCCC                 1000
SQL> BBBBBBBBBB                 1000
SQL> AAAAAAAAAA               100000
SQL>
SQL> 6 rows selected.
SQL>
SQL> */
SQL>
SQL> prompt "Press Enter to continue"
"Press Enter to continue"
SQL> pause
 
SQL> SELECT num_buckets, histogram FROM dba_tab_col_statistics WHERE TABLE_NAME = 'TAB1' AND column_name = 'B';
 
NUM_BUCKETS HISTOGRAM
----------- ---------------
          6 FREQUENCY
 
SQL>
SQL> /*
SQL> NUM_BUCKETS HISTOGRAM
SQL> ----------- ---------------
SQL>           6 FREQUENCY
SQL> */
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> SHOW USER
USER IS "MDECKER"
SQL> @/tmp/sql1.txt
SQL> SET LINES 300
SQL> SET echo ON
SQL> SET pages 10000
SQL> col bind format a120
SQL> prompt "Press enter to run"
"Press enter to run"
SQL> pause
 
SQL>
SQL> variable myvar varchar2(20)
SQL> EXEC :myvar := 'AAAAAAAAAA';
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET termout off
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------
SQL_ID  fhtgpcv2m2j4s, child NUMBER 0
-------------------------------------
SELECT /* MD_TESTCASE */ * FROM tab1 WHERE b = :myvar
 
Plan hash VALUE: 2211052296
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   889 (100)|          |
|*  1 |  TABLE ACCESS FULL| TAB1 |   100K|    20M|   889   (1)| 00:00:11 |
--------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - FILTER("B"=:MYVAR)
 
 
18 ROWS selected.
 
SQL> SELECT sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number FROM
  2   v$sql WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER
------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------
fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0
 
SQL>
SQL> prompt "Press enter to run"
"Press enter to run"
SQL> pause
 
SQL> EXEC :myvar := 'BBBBBBBBBB';
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET termout off
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------
SQL_ID  fhtgpcv2m2j4s, child NUMBER 0
-------------------------------------
SELECT /* MD_TESTCASE */ * FROM tab1 WHERE b = :myvar
 
Plan hash VALUE: 2211052296
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   889 (100)|          |
|*  1 |  TABLE ACCESS FULL| TAB1 |   100K|    20M|   889   (1)| 00:00:11 |
--------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - FILTER("B"=:MYVAR)
 
 
18 ROWS selected.
 
SQL> SELECT sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number FROM
  2   v$sql WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER
------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------
fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0
 
SQL>
SQL> EXEC :myvar := 'CCCCCCCCCC';
 
PL/SQL PROCEDURE successfully completed.
 
SQL> prompt "Press enter to run"
"Press enter to run"
SQL> pause
 
SQL> SET termout off
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------
SQL_ID  fhtgpcv2m2j4s, child NUMBER 1
-------------------------------------
SELECT /* MD_TESTCASE */ * FROM tab1 WHERE b = :myvar
 
Plan hash VALUE: 1918680838
 
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |    36 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1      |  1000 |   210K|    36   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TAB1_IDX1 |  1000 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - access("B"=:MYVAR)
 
 
19 ROWS selected.
 
SQL> SELECT sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number FROM
  2   v$sql WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER
------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------
fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0
fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA()))           1918680838            1
 
SQL>
SQL> EXEC :myvar := 'DDDDDDDDDD';
 
PL/SQL PROCEDURE successfully completed.
 
SQL> prompt "Press enter to run"
"Press enter to run"
SQL> pause
 
SQL> SET termout off
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------
SQL_ID  fhtgpcv2m2j4s, child NUMBER 2
-------------------------------------
SELECT /* MD_TESTCASE */ * FROM tab1 WHERE b = :myvar
 
Plan hash VALUE: 1918680838
 
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     5 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1      |   100 | 21600 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TAB1_IDX1 |   100 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - access("B"=:MYVAR)
 
 
19 ROWS selected.
 
SQL> SELECT sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number FROM
  2   v$sql WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER
------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------
fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA()))           1918680838            1
fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'DDDDDDDDDD', ANYDATA()))           1918680838            2
 
SQL>
SQL> EXEC :myvar := 'EEEEEEEEEE';
 
PL/SQL PROCEDURE successfully completed.
 
SQL> prompt "Press enter to run"
"Press enter to run"
SQL> pause
 
SQL> SET termout off
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------
SQL_ID  fhtgpcv2m2j4s, child NUMBER 3
-------------------------------------
SELECT /* MD_TESTCASE */ * FROM tab1 WHERE b = :myvar
 
Plan hash VALUE: 1918680838
 
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1      |    10 |  2160 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TAB1_IDX1 |    10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - access("B"=:MYVAR)
 
 
19 ROWS selected.
 
SQL> SELECT sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number FROM
  2   v$sql WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER
------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------
fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA()))           1918680838            1
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'DDDDDDDDDD', ANYDATA()))           1918680838            2
fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'EEEEEEEEEE', ANYDATA()))           1918680838            3
 
SQL>
SQL> EXEC :myvar := 'FFFFFFFFFF';
 
PL/SQL PROCEDURE successfully completed.
 
SQL> prompt "Press enter to run"
"Press enter to run"
SQL> pause
 
SQL> SET termout off
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------
SQL_ID  fhtgpcv2m2j4s, child NUMBER 4
-------------------------------------
SELECT /* MD_TESTCASE */ * FROM tab1 WHERE b = :myvar
 
Plan hash VALUE: 1918680838
 
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1      |     1 |   216 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TAB1_IDX1 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - access("B"=:MYVAR)
 
 
19 ROWS selected.
 
SQL> SELECT sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number FROM
  2   v$sql WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER
------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------
fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA()))           1918680838            1
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'DDDDDDDDDD', ANYDATA()))           1918680838            2
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'EEEEEEEEEE', ANYDATA()))           1918680838            3
fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'FFFFFFFFFF', ANYDATA()))           1918680838            4

SQL> @/tmp/setup.sql Connected. SQL> drop table tab1 purge; Table dropped. SQL> create table tab1 (a int, 2 b varchar2(20), 3 padding varchar2(200)) 4 tablespace USERS; Table created. SQL> SQL> drop sequence myid; Sequence dropped. SQL> create sequence myid cache 1000; Sequence created. SQL> SQL> begin 2 for i in 1..100000 3 loop 4 insert into tab1 values (myid.nextval, 5 'AAAAAAAAAA', 6 rpad('*',200,'*')); 7 end loop; 8 9 for i in 1..1000 10 loop 11 insert into tab1 values (myid.nextval, 12 'BBBBBBBBBB', 13 rpad('*',200,'*')); 14 end loop; 15 16 for i in 1..1000 17 loop 18 insert into tab1 values (myid.nextval, 19 'CCCCCCCCCC', 20 rpad('*',200,'*')); 21 end loop; 22 23 for i in 1..100 24 loop 25 insert into tab1 values (myid.nextval, 26 'DDDDDDDDDD', 27 rpad('*',200,'*')); 28 end loop; 29 30 for i in 1..10 31 loop 32 insert into tab1 values (myid.nextval, 33 'EEEEEEEEEE', 34 rpad('*',200,'*')); 35 end loop; 36 37 insert into tab1 values (myid.nextval, 38 'FFFFFFFFFF', 39 rpad('*',200,'*')); 40 41 commit; 42 end; 43 / PL/SQL procedure successfully completed. SQL> SQL> create index tab1_idx1 on tab1 (b); Index created. SQL> SQL> begin dbms_stats.gather_table_stats(ownname => 'MDECKER', 2 tabname => 'TAB1', 3 method_opt =>'FOR ALL COLUMNS SIZE 254', 4 cascade=> TRUE, 5 estimate_percent => 100, 6 no_invalidate => FALSE); 7 end; 8 / PL/SQL procedure successfully completed. SQL> SQL> set lines 300 SQL> col table_name format a10 SQL> col owner format a8 SQL> select owner, table_name,num_rows, blocks, round((blocks*8192)/1024/1024) as MB 2 from dba_tables where table_name = 'TAB1' and owner = 'MDECKER'; OWNER TABLE_NAME NUM_ROWS BLOCKS MB -------- ---------- ---------- ---------- ---------- MDECKER TAB1 102111 3268 26 SQL> SQL> prompt "Press Enter to continue" "Press Enter to continue" SQL> pause SQL> SQL> /* SQL> OWNER TABLE_NAME NUM_ROWS BLOCKS MB SQL> -------- ---------- ---------- ---------- ---------- SQL> MDECKER TAB1 102111 3268 26 SQL> SQL> */ SQL> SQL> SQL> SQL> select b , count(*) from mdecker.tab1 group by b order by 1 desc; B COUNT(*) -------------------- ---------- FFFFFFFFFF 1 EEEEEEEEEE 10 DDDDDDDDDD 100 CCCCCCCCCC 1000 BBBBBBBBBB 1000 AAAAAAAAAA 100000 6 rows selected. SQL> SQL> /* SQL> B COUNT(*) SQL> -------------------- ---------- SQL> FFFFFFFFFF 1 SQL> EEEEEEEEEE 10 SQL> DDDDDDDDDD 100 SQL> CCCCCCCCCC 1000 SQL> BBBBBBBBBB 1000 SQL> AAAAAAAAAA 100000 SQL> SQL> 6 rows selected. SQL> SQL> */ SQL> SQL> prompt "Press Enter to continue" "Press Enter to continue" SQL> pause SQL> select num_buckets, histogram from dba_tab_col_statistics where table_name = 'TAB1' and column_name = 'B'; NUM_BUCKETS HISTOGRAM ----------- --------------- 6 FREQUENCY SQL> SQL> /* SQL> NUM_BUCKETS HISTOGRAM SQL> ----------- --------------- SQL> 6 FREQUENCY SQL> */ SQL> SQL> SQL> SQL> SQL> SQL> show user USER is "MDECKER" SQL> @/tmp/sql1.txt SQL> set lines 300 SQL> set echo on SQL> set pages 10000 SQL> col bind format a120 SQL> prompt "Press enter to run" "Press enter to run" SQL> pause SQL> SQL> variable myvar varchar2(20) SQL> exec :myvar := 'AAAAAAAAAA'; PL/SQL procedure successfully completed. SQL> set termout off SQL> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------- SQL_ID fhtgpcv2m2j4s, child number 0 ------------------------------------- SELECT /* MD_TESTCASE */ * from tab1 where b = :myvar Plan hash value: 2211052296 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 889 (100)| | |* 1 | TABLE ACCESS FULL| TAB1 | 100K| 20M| 889 (1)| 00:00:11 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("B"=:MYVAR) 18 rows selected. SQL> select sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number from 2 v$sql where sql_id = 'fhtgpcv2m2j4s'; SQL_ID I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER ------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------ fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA())) 2211052296 0 SQL> SQL> prompt "Press enter to run" "Press enter to run" SQL> pause SQL> exec :myvar := 'BBBBBBBBBB'; PL/SQL procedure successfully completed. SQL> set termout off SQL> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------- SQL_ID fhtgpcv2m2j4s, child number 0 ------------------------------------- SELECT /* MD_TESTCASE */ * from tab1 where b = :myvar Plan hash value: 2211052296 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 889 (100)| | |* 1 | TABLE ACCESS FULL| TAB1 | 100K| 20M| 889 (1)| 00:00:11 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("B"=:MYVAR) 18 rows selected. SQL> select sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number from 2 v$sql where sql_id = 'fhtgpcv2m2j4s'; SQL_ID I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER ------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------ fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA())) 2211052296 0 SQL> SQL> exec :myvar := 'CCCCCCCCCC'; PL/SQL procedure successfully completed. SQL> prompt "Press enter to run" "Press enter to run" SQL> pause SQL> set termout off SQL> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------- SQL_ID fhtgpcv2m2j4s, child number 1 ------------------------------------- SELECT /* MD_TESTCASE */ * from tab1 where b = :myvar Plan hash value: 1918680838 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 36 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1000 | 210K| 36 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TAB1_IDX1 | 1000 | | 4 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("B"=:MYVAR) 19 rows selected. SQL> select sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number from 2 v$sql where sql_id = 'fhtgpcv2m2j4s'; SQL_ID I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER ------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------ fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA())) 2211052296 0 fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA())) 1918680838 1 SQL> SQL> exec :myvar := 'DDDDDDDDDD'; PL/SQL procedure successfully completed. SQL> prompt "Press enter to run" "Press enter to run" SQL> pause SQL> set termout off SQL> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------- SQL_ID fhtgpcv2m2j4s, child number 2 ------------------------------------- SELECT /* MD_TESTCASE */ * from tab1 where b = :myvar Plan hash value: 1918680838 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 5 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | 100 | 21600 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TAB1_IDX1 | 100 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("B"=:MYVAR) 19 rows selected. SQL> select sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number from 2 v$sql where sql_id = 'fhtgpcv2m2j4s'; SQL_ID I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER ------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------ fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA())) 2211052296 0 fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA())) 1918680838 1 fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'DDDDDDDDDD', ANYDATA())) 1918680838 2 SQL> SQL> exec :myvar := 'EEEEEEEEEE'; PL/SQL procedure successfully completed. SQL> prompt "Press enter to run" "Press enter to run" SQL> pause SQL> set termout off SQL> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------- SQL_ID fhtgpcv2m2j4s, child number 3 ------------------------------------- SELECT /* MD_TESTCASE */ * from tab1 where b = :myvar Plan hash value: 1918680838 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | 10 | 2160 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TAB1_IDX1 | 10 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("B"=:MYVAR) 19 rows selected. SQL> select sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number from 2 v$sql where sql_id = 'fhtgpcv2m2j4s'; SQL_ID I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER ------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------ fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA())) 2211052296 0 fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA())) 1918680838 1 fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'DDDDDDDDDD', ANYDATA())) 1918680838 2 fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'EEEEEEEEEE', ANYDATA())) 1918680838 3 SQL> SQL> exec :myvar := 'FFFFFFFFFF'; PL/SQL procedure successfully completed. SQL> prompt "Press enter to run" "Press enter to run" SQL> pause SQL> set termout off SQL> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------- SQL_ID fhtgpcv2m2j4s, child number 4 ------------------------------------- SELECT /* MD_TESTCASE */ * from tab1 where b = :myvar Plan hash value: 1918680838 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 216 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TAB1_IDX1 | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("B"=:MYVAR) 19 rows selected. SQL> select sql_id, is_obsolete, is_bind_sensitive, is_bind_aware, is_shareable, dbms_sqltune.extract_binds(bind_data) bind, plan_hash_value, child_number from 2 v$sql where sql_id = 'fhtgpcv2m2j4s'; SQL_ID I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER ------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------ fhtgpcv2m2j4s N Y N Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA())) 2211052296 0 fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA())) 1918680838 1 fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'DDDDDDDDDD', ANYDATA())) 1918680838 2 fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'EEEEEEEEEE', ANYDATA())) 1918680838 3 fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'FFFFFFFFFF', ANYDATA())) 1918680838 4

You can see that after parsing for bind CCCCCCCC, the execution plan changes from full table scan to index range scan. Moreover child cursor 1 is marked “is_bind_aware=Y”. When now bind DDDDDDDD is used, there is a new child (#2) and child 1 is set to is_sharable=N. After using bind EEEEEEEE, child #2 is set to sharable=N and a new child #3 is created. This repeats until there are 5 child cursors, 2 of which are set to is_sharable=Y. When the script is executed multiple times, there is even a sixth child cursor.

SQL_ID        I I I I BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS
------------- - - - - ------------------------------------------------------------------------------------------------------------------------ --------------- ------------ ----------
fhtgpcv2m2j4s N Y N N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            0          2
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'CCCCCCCCCC', ANYDATA()))           1918680838            1          1
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'DDDDDDDDDD', ANYDATA()))           1918680838            2          1
fhtgpcv2m2j4s N Y Y N SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'EEEEEEEEEE', ANYDATA()))           1918680838            3          1
fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'FFFFFFFFFF', ANYDATA()))           1918680838            4         16
fhtgpcv2m2j4s N Y Y Y SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 178, NULL, NULL, 32, '12-MAR-09', 'AAAAAAAAAA', ANYDATA()))           2211052296            5          3

I wondered why the cursors are not sharable and queried v$sql_shared_cursor:

SQL>   SELECT * FROM v$sql_shared_cursor WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L F E B P I S T A B D L T R I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L R L H
------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
fhtgpcv2m2j4s 23F4E438 23F4DAB0            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N
fhtgpcv2m2j4s 23F4E438 23D924D8            1 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
fhtgpcv2m2j4s 23F4E438 23D7E888            2 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
fhtgpcv2m2j4s 23F4E438 23D6B8C8            3 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
fhtgpcv2m2j4s 23F4E438 23D574D8            4 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
fhtgpcv2m2j4s 23F4E438 23B983B0            5 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
 
6 ROWS selected.
 
SQL> SELECT sql_id, address, child_number, LOAD_OPTIMIZER_STATS, row_level_sec_mismatch FROM v$sql_shared_cursor WHERE sql_id = 'fhtgpcv2m2j4s';
 
SQL_ID        ADDRESS  CHILD_NUMBER L R
------------- -------- ------------ - -
fhtgpcv2m2j4s 245CE53C            0 Y N
fhtgpcv2m2j4s 245CE53C            1 N Y
fhtgpcv2m2j4s 245CE53C            2 N Y
fhtgpcv2m2j4s 245CE53C            3 N Y
fhtgpcv2m2j4s 245CE53C            4 N Y
fhtgpcv2m2j4s 245CE53C            5 N Y
 
SQL> SELECT * FROM v$sql_cs_statistics WHERE sql_id = 'fhtgpcv2m2j4s';
 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
-------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
245CE53C 3308340376 fhtgpcv2m2j4s            5          3946114705 N          1              0           2          0
245CE53C 3308340376 fhtgpcv2m2j4s            5          2785428864 Y          1              0           2          0
245CE53C 3308340376 fhtgpcv2m2j4s            4          1680726256 Y          1              2           3          0
245CE53C 3308340376 fhtgpcv2m2j4s            3          1438126164 Y          1             20           5          0
245CE53C 3308340376 fhtgpcv2m2j4s            2           451655377 Y          1            200          21          0
245CE53C 3308340376 fhtgpcv2m2j4s            1          4078512309 Y          1           2000         169          0
245CE53C 3308340376 fhtgpcv2m2j4s            0           479772785 Y          1         100000        9823          0
 
7 ROWS selected.
 
SQL> SELECT * FROM v$sql_cs_selectivity WHERE sql_id = 'fhtgpcv2m2j4s';
 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
-------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
245CE53C 3308340376 fhtgpcv2m2j4s            5 =MYVAR                                            0 0.000004   0.010773
245CE53C 3308340376 fhtgpcv2m2j4s            4 =MYVAR                                            0 0.000009   0.010773
245CE53C 3308340376 fhtgpcv2m2j4s            3 =MYVAR                                            0 0.000088   0.010773
245CE53C 3308340376 fhtgpcv2m2j4s            2 =MYVAR                                            0 0.000881   0.010773
245CE53C 3308340376 fhtgpcv2m2j4s            1 =MYVAR                                            0 0.008814   0.010773

SQL> select * from SQL_ID ADDRESS CHILD_AD ------------- -------- -------- fhtgpcv2m2j4s 23F4E438 23F4DAB0 fhtgpcv2m2j4s 23F4E438 23D924D8 fhtgpcv2m2j4s 23F4E438 23D7E888 fhtgpcv2m2j4s 23F4E438 23D6B8C8 fhtgpcv2m2j4s 23F4E438 23D574D8 fhtgpcv2m2j4s 23F4E438 23B983B0 6 rows selected. SQL> select sql_id, address, SQL_ID ADDRESS CHILD_NUMBER L R ------------- -------- ------------ - - fhtgpcv2m2j4s 245CE53C 0 Y N fhtgpcv2m2j4s 245CE53C 1 N Y fhtgpcv2m2j4s 245CE53C 2 N Y fhtgpcv2m2j4s 245CE53C 3 N Y fhtgpcv2m2j4s 245CE53C 4 N Y fhtgpcv2m2j4s 245CE53C 5 N Y SQL> select * from v$sql_cs_statistics ADDRESS HASH_VALUE SQL_ID -------- ---------- ------------- 245CE53C 3308340376 fhtgpcv2m2j4s 245CE53C 3308340376 fhtgpcv2m2j4s 245CE53C 3308340376 fhtgpcv2m2j4s 245CE53C 3308340376 fhtgpcv2m2j4s 245CE53C 3308340376 fhtgpcv2m2j4s 245CE53C 3308340376 fhtgpcv2m2j4s 245CE53C 3308340376 fhtgpcv2m2j4s 7 rows selected. SQL> select * from v$sql_cs_selectivity ADDRESS HASH_VALUE SQL_ID -------- ---------- ------------- 245CE53C 3308340376 fhtgpcv2m2j4s 245CE53C 3308340376 fhtgpcv2m2j4s 245CE53C 3308340376 fhtgpcv2m2j4s 245CE53C 3308340376 fhtgpcv2m2j4s 245CE53C 3308340376 fhtgpcv2m2j4s v$sql_shared_cursor where sql_id = 'fhtgpcv2m2j4s'; CHILD_NUMBER U S O O S L F E B P I S T A B D L T R I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L R L H ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N N 1 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 2 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 3 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 4 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 5 N N N N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N child_number, LOAD_OPTIMIZER_STATS, row_level_sec_mismatch from v$sql_shared_cursor where sql_id = 'fhtgpcv2m2j4s'; where sql_id = 'fhtgpcv2m2j4s'; CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME ------------ ------------------- - ---------- -------------- ----------- ---------- 5 3946114705 N 1 0 2 0 5 2785428864 Y 1 0 2 0 4 1680726256 Y 1 2 3 0 3 1438126164 Y 1 20 5 0 2 451655377 Y 1 200 21 0 1 4078512309 Y 1 2000 169 0 0 479772785 Y 1 100000 9823 0 where sql_id = 'fhtgpcv2m2j4s'; CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ------------ ---------------------------------------- ---------- ---------- ---------- 5 =MYVAR 0 0.000004 0.010773 4 =MYVAR 0 0.000009 0.010773 3 =MYVAR 0 0.000088 0.010773 2 =MYVAR 0 0.000881 0.010773 1 =MYVAR 0 0.008814 0.010773

It is not clear, why the cursors are not shared and why this is expressed in the flag “row_level_sec_mismatch”. From these tests we can see that the number of child cursors is still quite high.