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

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016