Search My Oracle Blog

Custom Search

15 February, 2012

CURSOR_SHARING FORCE and Child Cursors

A demonstration of how a "simple" SQL statement can have multiple child cursors.

I start with the target table :

SQL> create table target_table (column_1  varchar2(250), column_2 varchar2(300));

Table created.

SQL> 
I then generate some SQL statements as INSERTs.

SQL> var b1 varchar2(250)
SQL> var b2 varchar2(300)
SQL> exec :b1 := 'X'

PL/SQL procedure successfully completed.

SQL> exec :b2 := 'Y'

PL/SQL procedure successfully completed.

SQL> insert into target_table  values (:b1, :b2);

1 row created.

SQL> 
SQL> exec :b1 := 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';

PL/SQL procedure successfully completed.

SQL> insert into target_table  values (:b1, :b2);

1 row created.

SQL> 
Currently, I still have only 1 child :

SQL> select sql_id, address, child_address, child_number from v$sql_shared_cursor where sql_id='5h01ayw0ytgrw';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER
------------- -------- -------- ------------
5h01ayw0ytgrw 2D44DFB4 2D44DD30            0

SQL> 

SQL> exec :b2 := 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY';

PL/SQL procedure successfully completed.

SQL> insert into target_table  values (:b1, :b2);

1 row created.

SQL>
SQL> select sql_id, address, child_address, child_number from v$sql_shared_cursor where sql_id='5h01ayw0ytgrw';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER
------------- -------- -------- ------------
5h01ayw0ytgrw 2D44DFB4 2D44DD30            0

SQL> 
So, I still have the same child cursor !

Now I force cursor sharing and use literals.


SQL> commit;

Commit complete.

SQL> alter session set cursor_sharing=FORCE;

Session altered.

SQL> insert into target_table  values ('X1','Y1');

1 row created.

SQL> 
SQL> select sql_id from v$sql where sql_text like 'insert into target_table%';

SQL_ID
-------------
aj061rs0uw9qn
5h01ayw0ytgrw

SQL> select sql_id, address, child_address, child_number from v$sql_shared_cursor where sql_id='aj061rs0uw9qn';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER
------------- -------- -------- ------------
aj061rs0uw9qn 2D78A0C4 3259BB9C            0

SQL> 
SQL_ID aj061rs0uw9qn is the new SQL statement.

What if I have a longer bind ?

SQL> insert into target_table  values ('X123456789012345678901234567890123456','Y2');

1 row created.

SQL> 

SQL> select sql_id, address, child_address, child_number from v$sql_shared_cursor where sql_id='aj061rs0uw9qn';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER
------------- -------- -------- ------------
aj061rs0uw9qn 2D78A0C4 3259BB9C            0
aj061rs0uw9qn 2D78A0C4 2D466040            1

SQL> 

SQL> select * from v$sql_shared_cursor
  2  where sql_id = 'aj061rs0uw9qn'
  3  order by child_number;

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
------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - -
B 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 P B
- - - - -
aj061rs0uw9qn 2D78A0C4 3259BB9C            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 N N N N
N N N N N

aj061rs0uw9qn 2D78A0C4 2D466040            1 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 N N N N
N N N N Y


SQL> 

SQL> desc v$sql_shared_cursor
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SQL_ID                                             VARCHAR2(13)
 ADDRESS                                            RAW(4)
 CHILD_ADDRESS                                      RAW(4)
 CHILD_NUMBER                                       NUMBER
 UNBOUND_CURSOR                                     VARCHAR2(1)
 SQL_TYPE_MISMATCH                                  VARCHAR2(1)
 OPTIMIZER_MISMATCH                                 VARCHAR2(1)
 OUTLINE_MISMATCH                                   VARCHAR2(1)
 STATS_ROW_MISMATCH                                 VARCHAR2(1)
 LITERAL_MISMATCH                                   VARCHAR2(1)
 FORCE_HARD_PARSE                                   VARCHAR2(1)
 EXPLAIN_PLAN_CURSOR                                VARCHAR2(1)
 BUFFERED_DML_MISMATCH                              VARCHAR2(1)
 PDML_ENV_MISMATCH                                  VARCHAR2(1)
 INST_DRTLD_MISMATCH                                VARCHAR2(1)
 SLAVE_QC_MISMATCH                                  VARCHAR2(1)
 TYPECHECK_MISMATCH                                 VARCHAR2(1)
 AUTH_CHECK_MISMATCH                                VARCHAR2(1)
 BIND_MISMATCH                                      VARCHAR2(1)
 DESCRIBE_MISMATCH                                  VARCHAR2(1)
 LANGUAGE_MISMATCH                                  VARCHAR2(1)
 TRANSLATION_MISMATCH                               VARCHAR2(1)
 BIND_EQUIV_FAILURE                                 VARCHAR2(1)
 INSUFF_PRIVS                                       VARCHAR2(1)
 INSUFF_PRIVS_REM                                   VARCHAR2(1)
 REMOTE_TRANS_MISMATCH                              VARCHAR2(1)
 LOGMINER_SESSION_MISMATCH                          VARCHAR2(1)
 INCOMP_LTRL_MISMATCH                               VARCHAR2(1)
 OVERLAP_TIME_MISMATCH                              VARCHAR2(1)
 EDITION_MISMATCH                                   VARCHAR2(1)
 MV_QUERY_GEN_MISMATCH                              VARCHAR2(1)
 USER_BIND_PEEK_MISMATCH                            VARCHAR2(1)
 TYPCHK_DEP_MISMATCH                                VARCHAR2(1)
 NO_TRIGGER_MISMATCH                                VARCHAR2(1)
 FLASHBACK_CURSOR                                   VARCHAR2(1)
 ANYDATA_TRANSFORMATION                             VARCHAR2(1)
 INCOMPLETE_CURSOR                                  VARCHAR2(1)
 TOP_LEVEL_RPI_CURSOR                               VARCHAR2(1)
 DIFFERENT_LONG_LENGTH                              VARCHAR2(1)
 LOGICAL_STANDBY_APPLY                              VARCHAR2(1)
 DIFF_CALL_DURN                                     VARCHAR2(1)
 BIND_UACS_DIFF                                     VARCHAR2(1)
 PLSQL_CMP_SWITCHS_DIFF                             VARCHAR2(1)
 CURSOR_PARTS_MISMATCH                              VARCHAR2(1)
 STB_OBJECT_MISMATCH                                VARCHAR2(1)
 CROSSEDITION_TRIGGER_MISMATCH                      VARCHAR2(1)
 PQ_SLAVE_MISMATCH                                  VARCHAR2(1)
 TOP_LEVEL_DDL_MISMATCH                             VARCHAR2(1)
 MULTI_PX_MISMATCH                                  VARCHAR2(1)
 BIND_PEEKED_PQ_MISMATCH                            VARCHAR2(1)
 MV_REWRITE_MISMATCH                                VARCHAR2(1)
 ROLL_INVALID_MISMATCH                              VARCHAR2(1)
 OPTIMIZER_MODE_MISMATCH                            VARCHAR2(1)
 PX_MISMATCH                                        VARCHAR2(1)
 MV_STALEOBJ_MISMATCH                               VARCHAR2(1)
 FLASHBACK_TABLE_MISMATCH                           VARCHAR2(1)
 LITREP_COMP_MISMATCH                               VARCHAR2(1)
 PLSQL_DEBUG                                        VARCHAR2(1)
 LOAD_OPTIMIZER_STATS                               VARCHAR2(1)
 ACL_MISMATCH                                       VARCHAR2(1)
 FLASHBACK_ARCHIVE_MISMATCH                         VARCHAR2(1)
 LOCK_USER_SCHEMA_FAILED                            VARCHAR2(1)
 REMOTE_MAPPING_MISMATCH                            VARCHAR2(1)
 LOAD_RUNTIME_HEAP_FAILED                           VARCHAR2(1)
 HASH_MATCH_FAILED                                  VARCHAR2(1)
 PURGED_CURSOR                                      VARCHAR2(1)
 BIND_LENGTH_UPGRADEABLE                            VARCHAR2(1)

SQL> 
Child cursor 1 has BIND_LENGTH_UPGRADEABLE as 'Y'.

........ to be updated with more examples ..... ???? ...........
.
.
.


2 comments:

Anonymous said...

Hi,

I can see there is a difference in bind lenght. But why it creates a new child cursor if you use literals with cursor_sharing force?

Regards

Alice Lucas said...

Good job this is my first visit at your blog and really like your post you did a good work keep it up.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com