7 Replies Latest reply: Nov 7, 2011 8:20 AM by 894085 RSS

    SQL_TYPE_MISMATCH

    776966
      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
          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
            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
              Timur Akhmadeev
              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
                Charles Hooper
                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
                  Timur Akhmadeev
                  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
                    Charles Hooper
                    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
                      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