SQL_TYPE_MISMATCH in V$SQL_SHARED_CURSOR

23 07 2010

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.








Follow

Get every new post delivered to your Inbox.

Join 139 other followers