July 23, 2010
While reading the OTN forums a couple of days ago I found a simple request regarding the meaning of the SQL_TYPE_MISMATCH column in V$SQL_SHARED_CURSOR. The Oracle Database 8i documentation and the Oracle Database 11.2 documentation both state the following very limited description for that column in V$SQL_SHARED_CURSOR:
“The SQL type does not match the existing child cursor”
Just what does the above mean? Analysis indicates that the SQL_TYPE_MISMATCH column indicates that something caused a setting change originating from the client (possibly Oracle client version from one client computer to the next or a change from DAO, ADO, RDO, OO4O, etc.), and that change triggered a hard parse resulting in the generation of a new child cursor with a different value in the SQLTYPE column of V$SQL. I have not found the triggering element that causes apparently the same client computer with the same username and with the same ERP package to generate multiple child cursors for the same SQL statement, with just different values displayed in the SQLTYPE column.
For example (do not execute this SQL statement in a busy database):
SELECT SS1.SQL_TEXT, SS1.SQL_ID, SS1.CHILD_NUMBER, SS2.CHILD_NUMBER, SS1.SQLTYPE, SS2.SQLTYPE, SS1.PARSING_SCHEMA_NAME, SS2.PARSING_SCHEMA_NAME FROM V$SQL SS1, V$SQL SS2 WHERE SS1.SQL_ID=SS2.SQL_ID AND SS1.SQLTYPE < SS2.SQLTYPE ORDER BY SS1.SQL_TEXT, SS1.CHILD_NUMBER, SS2.CHILD_NUMBER;
A sampling of the output from a production database:
SQL_ID CHILD_NUMBER CHILD_NUMBER SQLTYPE SQLTYPE PARSING_SCHEMA_NAME PARSING_SCHEMA_NAME ------------- ------------ ------------ ---------- ---------- ------------------------------ ------------------------------ SELECT BANNER FROM V$VERSION 39tw34mramfdv 0 1 2 6 USER3 USER4 select SYSDATE from APPLICATION_GLOBAL crmxa1g4nrc89 0 2 2 6 USER1 USER1 SELECT ro.obj#, '"'||ru.name||'"' || '.' || '"'||ro.name||'"', decode(rl.ectx#, 0, decode(rm.ectx#, 0, rs.ectx#, rm.ectx#), rl.ectx#), decode (rl.ectx#, 0, decode(rm.ectx#, 0, (select '"'||u1.name||'"' || '.' || '"'||o1.name||'"' from user$ u1, obj$ o1 where o1.obj# = rs.ectx# and o1.owner# = u1.user#), (select '"'||u2.name||'"' || '.' || '"'||o2.name||'"' from user$ u2, obj$ o2 where o2.obj# = rm.ectx# and o2.owner# = u2.user#)), (select '"'||u3.name||'"' || '.' || '"'||o3.name||'"' from user$ u3, obj$ o3 where o3.obj# = rl.ectx# and o3.owner# = u3.user#)) from obj$ so, user$ su, rule_map$ rm, obj$ ro, user$ ru, rule$ rl, rule_set$ rs where su.name = :1 and so.name = :2 and so.owner# = su.user# and so.obj# = rm.rs_obj# and rm.r_obj# = ro.obj# and ro.owner# = ru.user# and rl.obj# = rm.r_obj# and rs.obj# = rm.rs_obj# 3hgxzypxz2xpg 3 1 0 2 SYS SYS SELECT ro.obj#, '"'||ru.name||'"' || '.' || '"'||ro.name||'"', decode(rl.ectx#, 0, decode(rm.ectx#, 0, rs.ectx#, rm.ectx#), rl.ectx#), decode (rl.ectx#, 0, decode(rm.ectx#, 0, (select '"'||u1.name||'"' || '.' || '"'||o1.name||'"' from user$ u1, obj$ o1 where o1.obj# = rs.ectx# and o1.owner# = u1.user#), (select '"'||u2.name||'"' || '.' || '"'||o2.name||'"' from user$ u2, obj$ o2 where o2.obj# = rm.ectx# and o2.owner# = u2.user#)), (select '"'||u3.name||'"' || '.' || '"'||o3.name||'"' from user$ u3, obj$ o3 where o3.obj# = rl.ectx# and o3.owner# = u3.user#)) from obj$ so, user$ su, rule_map$ rm, obj$ ro, user$ ru, rule$ rl, rule_set$ rs where su.name = :1 and so.name = :2 and so.owner# = su.user# and so.obj# = rm.rs_obj# and rm.r_obj# = ro.obj# and ro.owner# = ru.user# and rl.obj# = rm.r_obj# and rs.obj# = rm.rs_obj# 3hgxzypxz2xpg 3 2 0 2 SYS SYS alter session set events 'immediate trace name krb_options level 5' fw1b57tbvz13j 1 0 0 6 SYS SYS delete from source$ where obj#=:1 5t480bb4uh8hw 1 0 0 2 SYS SYS delete from source$ where obj#=:1 5t480bb4uh8hw 3 0 0 2 SYS SYS update next_number_gen set next_number = :1 where table_name = :2 and column_name = :3 0jw74fwfdkv1x 6 0 2 6 USER5 USER5 update next_number_gen set next_number = :1 where table_name = :2 and column_name = :3 0jw74fwfdkv1x 6 1 2 6 USER5 USER5 update next_number_gen set next_number = :1 where table_name = :2 and column_name = :3 0jw74fwfdkv1x 6 2 2 6 USER5 USER5 update next_number_gen set next_number = :1 where table_name = :2 and column_name = :3 0jw74fwfdkv1x 6 3 2 6 USER5 USER9 update next_number_gen set next_number = :1 where table_name = :2 and column_name = :3 0jw74fwfdkv1x 6 4 2 6 USER5 USER5 update next_number_gen set next_number = :1 where table_name = :2 and column_name = :3 0jw74fwfdkv1x 6 5 2 6 USER5 USER10 ...
—
Checking V$SQL_SHARED_CURSOR for several of the above SQL statements to determine why the additional child cursors were created:
SELECT SSC.* FROM V$SQL S, V$SQL_SHARED_CURSOR SSC WHERE S.SQL_ID='39tw34mramfdv' AND S.SQL_ID=SSC.SQL_ID AND S.CHILD_ADDRESS=SSC.CHILD_ADDRESS ORDER BY SSC.CHILD_NUMBER; SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L ------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 39tw34mramfdv 00000001847D4CA0 00000001847D3D70 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 39tw34mramfdv 00000001847D4CA0 00000001655BB248 1 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 N N N N N N N N N (only SQL_TYPE_MISMATCH)
SELECT SSC.* FROM V$SQL S, V$SQL_SHARED_CURSOR SSC WHERE S.SQL_ID='crmxa1g4nrc89' AND S.SQL_ID=SSC.SQL_ID AND S.CHILD_ADDRESS=SSC.CHILD_ADDRESS ORDER BY SSC.CHILD_NUMBER; SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L ------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - crmxa1g4nrc89 000000018460A3F8 00000001846094C8 0 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 N N N Y N N N N N crmxa1g4nrc89 000000018460A3F8 0000000161263120 2 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 N N N Y N N N N N (only SQL_TYPE_MISMATCH and ROLL_INVALID_MISMATCH)
SELECT SSC.* FROM V$SQL S, V$SQL_SHARED_CURSOR SSC WHERE S.SQL_ID='3hgxzypxz2xpg' AND S.SQL_ID=SSC.SQL_ID AND S.CHILD_ADDRESS=SSC.CHILD_ADDRESS ORDER BY SSC.CHILD_NUMBER; SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L ------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 3hgxzypxz2xpg 000000018CFE1950 0000000185222478 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 3hgxzypxz2xpg 000000018CFE1950 0000000184C8AFE0 2 N N N N N N N N N N N N N N N N N N N N N N N N 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 3hgxzypxz2xpg 000000018CFE1950 0000000160FA5908 3 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 N N N N N N N N N (only SQL_TYPE_MISMATCH and OPTIMIZER_MODE_MISMATCH)
SELECT SSC.* FROM V$SQL S, V$SQL_SHARED_CURSOR SSC WHERE S.SQL_ID='0jw74fwfdkv1x' AND S.SQL_ID=SSC.SQL_ID AND S.CHILD_ADDRESS=SSC.CHILD_ADDRESS ORDER BY SSC.CHILD_NUMBER; SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L ------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0jw74fwfdkv1x 0000000167748680 000000017CC367C0 0 N Y 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 Y N N N N N 0jw74fwfdkv1x 0000000167748680 000000016F344C28 1 N Y 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 Y N N N N N 0jw74fwfdkv1x 0000000167748680 0000000162D93820 2 N Y 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 0jw74fwfdkv1x 0000000167748680 0000000154E037E0 3 N Y 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 Y N N N N N 0jw74fwfdkv1x 0000000167748680 0000000176A5B698 4 N Y 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 Y N N N N N 0jw74fwfdkv1x 0000000167748680 000000016B9DEA38 5 N Y 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 Y N N N N N 0jw74fwfdkv1x 0000000167748680 000000018D8A5370 6 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 N N N Y N N N N N 0jw74fwfdkv1x 0000000167748680 0000000187D7A1C8 7 N Y 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 Y N N N N N 0jw74fwfdkv1x 0000000167748680 0000000171C634F0 8 N Y 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 Y N N N N N (SQL_TYPE_MISMATCH, BIND_MISMATCH and ROLL_INVALID_MISMATCH)
The last of the above SQL statements which was investigated included bind variables, and V$SQL_SHARED_CURSOR indicated that the additional child cursors were created in part due to a bind mismatch. Let’s take a look at the bind variable definitions for the SQL statement:
SELECT S.CHILD_NUMBER, SBM.POSITION, SBM.DATATYPE, SBM.MAX_LENGTH, SBM.BIND_NAME FROM V$SQL S, V$SQL_BIND_METADATA SBM WHERE S.SQL_ID='0jw74fwfdkv1x' AND S.CHILD_ADDRESS=SBM.ADDRESS ORDER BY S.CHILD_NUMBER, SBM.POSITION; CHILD_NUMBER POSITION DATATYPE MAX_LENGTH BIND_NAME ------------ ---------- ---------- ---------- --------- 0 1 1 32 1 0 2 96 32 2 0 3 96 32 3 1 1 1 32 1 1 2 96 32 2 1 3 96 32 3 2 1 1 32 1 2 2 96 32 2 2 3 96 32 3 3 1 1 32 1 3 2 96 32 2 3 3 96 32 3 4 1 1 32 1 4 2 96 32 2 4 3 96 32 3 5 1 1 32 1 5 2 96 32 2 5 3 96 32 3 6 1 1 32 1 6 2 96 32 2 6 3 96 32 3 7 1 1 32 1 7 2 96 32 2 7 3 96 32 3 8 1 1 32 1 8 2 96 32 2 8 3 96 32 3
The bind variable definitions appear to be consistent from one child cursor to the next even though V$SQL_SHARED_CURSOR reported a difference in the bind variable definitions or data lengths. The commercial ERP application, which is the primary source of SQL statements in this database, typically does not pre-initialize bind variable values during the initial parse call, which results in the MAX_LENGTH of VARCHAR2 bind variables being set to 2000. The above shows that what should be a numeric bind variable is defined as a VARCHAR2 and what should be a VARCHAR2 is defined as CHAR – those child cursors with non-initialized bind variables might have aged out of the libary cache.
From the above analysis we see that the change in the SQLTYPE is not just confined to just SQL statements with bind variables, not confined to a specific username (in some cases the same username appears for both child cursors with different SQLTYPE values), and is not specific to just application level SQL. It is still a bit of a mystery to me what causes the SQLTYPE column in V$SQL to change for internal SQL statements and SQL statements that are executed by the same client computer.
Any idea what triggers the change in the SQLTYPE column? For this particular database, values of 0 and 2 appear to be used when SQL is parsed by SYS, while values of 2 and 6 appear to be used when SQL is parsed by application code. This is likely more of a odd behavior than an actual problem.
Charles Hooper's Oracle Notes 
Leave a comment