This discussion is archived
7 Replies Latest reply: Nov 7, 2011 6:20 AM by 894085 RSS

SQL_TYPE_MISMATCH

776966 Newbie
Currently Being Moderated
hai ,
SELECT * FROM V$SQL_SHARED_CURSOR WHERE KGLHDPAR='00000005A6710DD0';

ADDRESS          KGLHDPAR         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 L
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
00000005A67105B8 00000005A6710DD0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
00000005B0113270 00000005A6710DD0 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
As value y suggest that the child cursor is not shared due to mismatch in sql_type
i want to know what is sql_type_mismatch .

Regards,
Meeran
  • 1. Re: SQL_TYPE_MISMATCH
    fjfranken Expert
    Currently Being Moderated
    Use Metalink Note: "VIEW: "V$SQL_SHARED_CURSOR" Reference Note [ID 120655.1]" for a reference of this view.

    It states: SQL_TYPE_MISMATCH : The SQL type does not match the existing child cursor

    HTH,

    FJFranken
    My Blog: http://managingoracle.blogspot.com
  • 2. Re: SQL_TYPE_MISMATCH
    776966 Newbie
    Currently Being Moderated
    Hai FJ,

    I want do know the reason why child cursor is not shared and meaning of sql type mismatch.

    Regards,
    Meeran
  • 3. Re: SQL_TYPE_MISMATCH
    680087 Pro
    Currently Being Moderated
    I think you should ask Oracle support with this question, since there's no reasonable explanation out there. Don't forget to share their reply here.
  • 4. Re: SQL_TYPE_MISMATCH
    CharlesHooper Expert
    Currently Being Moderated
    Meeran wrote:
    hai ,

    As value y suggest that the child cursor is not shared due to mismatch in sql_type
    i want to know what is sql_type_mismatch .

    Regards,
    Meeran
    It appears that SQL_TYPE_MISMATCH is set to Y in V$SQL_SHARED_CURSOR to indicate that the SQLTYPE in V$SQL changed. For example (note that this SQL statement specifiies a specific SQL_ID):
    SELECT
      SS1.SQL_TEXT,
      SS1.SQL_ID,
      SS1.CHILD_NUMBER,
      SS1.SQLTYPE,
      SS2.CHILD_NUMBER,
      SS2.SQLTYPE
    FROM
      V$SQL SS1,
      V$SQL SS2
    WHERE
      SS1.SQL_ID=SS2.SQL_ID
      AND SS1.SQLTYPE< SS2.SQLTYPE
      AND SS1.SQL_ID='39tw34mramfdv'
    ORDER BY
      SS1.SQL_TEXT,
      SS1.CHILD_NUMBER;
     
    SQL_TEXT                       SQL_ID        CHILD_NUMBER    SQLTYPE CHILD_NUMBER    SQLTYPE
    ------------------------------ ------------- ------------ ---------- ------------ ----------
    SELECT BANNER FROM V$VERSION   39tw34mramfdv            0          2            1          6
    Now to check V$SQL_SHARED_CURSOR:
    SELECT
      *
    FROM
      V$SQL_SHARED_CURSOR
    WHERE
      SQL_ID='39tw34mramfdv'
    ORDER BY
      CHILD_NUMBER;
     
    SQL_ID        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            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            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
    The documentation is not very clear regardiing the SQLTYPE column in V$SQL:
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2113.htm
    "SQLTYPE: Denotes the version of the SQL language used for this statement"

    I have not been able to find additional clarity what the various SQLTYPE values indicate - the column was added in Oracle Database 9i R2 (edit: column exists in 9i R1 based on output that appears in the book "Optimizing Oracle Performance", it appears that I misread the "What's New Documentation"). I see values of 0, 2, and 6 for the SQLTYPE of the same query. I would be interested to know what causes the different values to appear (with and without ANSI support?).

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.

    Edited by: Charles Hooper on Jul 20, 2010 3:37 PM
    "Optimizing Oracle Performance": http://books.google.com/books?id=mvJW6t7mYU0C&pg=PT214
    "What's New Documentation": http://download.oracle.com/docs/cd/B10500_01/server.920/a96536/whatsnew.htm
  • 5. Re: SQL_TYPE_MISMATCH
    680087 Pro
    Currently Being Moderated
    Charles Hooper wrote:
    It appears that SQL_TYPE_MISMATCH is set to Y in V$SQL_SHARED_CURSOR to indicate that the SQLTYPE in V$SQL changed. For example (note that this SQL statement specifiies a specific SQL_ID):
    SELECT
    SS1.SQL_TEXT,
    SS1.SQL_ID,
    SS1.CHILD_NUMBER,
    SS1.SQLTYPE,
    SS2.CHILD_NUMBER,
    SS2.SQLTYPE
    FROM
    V$SQL SS1,
    V$SQL SS2
    WHERE
    SS1.SQL_ID=SS2.SQL_ID
    AND SS1.SQLTYPE< SS2.SQLTYPE
    AND SS1.SQL_ID='39tw34mramfdv'
    ORDER BY
    SS1.SQL_TEXT,
    SS1.CHILD_NUMBER;
    
    SQL_TEXT                       SQL_ID        CHILD_NUMBER    SQLTYPE CHILD_NUMBER    SQLTYPE
    ------------------------------ ------------- ------------ ---------- ------------ ----------
    SELECT BANNER FROM V$VERSION   39tw34mramfdv            0          2            1          6
    Charles,

    what have you done before running this statement? What is your database version & platform?
  • 6. Re: SQL_TYPE_MISMATCH
    CharlesHooper Expert
    Currently Being Moderated
    Timur Akhmadeev wrote:
    Charles,

    what have you done before running this statement? What is your database version & platform?
    Timur,

    I waited for a period of time in a production database when there was minimal activity in the database and executed the SQL statement without specifying a specific SQL_ID. 39tw34mramfdv was one of the few SQL_IDs identified with a SQL statement that did not use bind variables - that SQL statement was also generic enough that others might find the same SQL statement in the library cache of their database. I did not spend a lot of time investigating, but there were several internal SQL statements returned and most of those had the SQLTYPE set to 0 (for at least one child cursor) and seemed to be initially parsed by SYS (if I recall correctly).

    The server is running Oracle 10.2.0.2 on 64 bit Windows. Portions of the commercial application that uses the database were coded in Centura/Gupta/Unify SQL Windows (compatible with Oracle 8/8i through 10g R2) and other portions were apparently created with C++ using ADO (that might explain the SQLTYPE 2 and 6).

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 7. Re: SQL_TYPE_MISMATCH
    894085 Explorer
    Currently Being Moderated
    OCIStmtPrepare() allows a client to either specify that SQL is parsed against the native or specific version of SQL. When the parsing lanaguage changes, you tend to get two child cursors with different sqltypes under the same sql_id. The "native" SQL version will depend on which client side library is linked. Running the same client application against two different major versions of client will yield different child cursors in the server.

    Edited by: user1135333 on Nov 7, 2011 6:19 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points