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:
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
Good job this is my first visit at your blog and really like your post you did a good work keep it up.
Post a Comment