Skip to Main Content
  • Questions
  • why multiple versions? What would be the cause of BIND_MISMATCH?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, gs.

Asked: April 29, 2003 - 10:07 pm UTC

Last updated: February 19, 2024 - 5:49 pm UTC

Version: 9.2.0.3

Viewed 10K+ times! This question is

You Asked

Tom,

I got the following statements from v$sql* .

What does it imply when the parsion_shema/user_id is 0 (SYS)?.

In the v$sql_shared_cursor I see the only difference is in BIND_MISMATCH. Is it the cause for 16 occurences of the same sql, in the v$sql?

If the sql statments are exactly same, will they always have the same ADDRESS? Is the child_adress will be different if one of the v$sql_shared_cursor columns have different value?

What would be the cause of BIND_MISMATCH?

Thanks..


SQL> select parsing_user_id, parsing_schema_id, sql_text, address, child_address from v$sql where address = '8EE280E8' ;

PARSING_USER_ID PARSING_SCHEMA_ID SQL_TEXT ADDRESS CHILD_AD
--------------- ----------------- ----------------------------------------------------- -------- ------------
0 0 update recordmst set BATCHKEY = :1, RECORDTYPE = :2, 8EE280E8 8E9425DC
ENTITY = :3 ... where RECORD# = :36

0 0 update recordmst set BATCHKEY = :1, RECORDTYPE = :2, 8EE280E8 9273E6F0
ENTITY = :3 ... where RECORD# = :36

0 0 update recordmst set BATCHKEY = :1, RECORDTYPE = :2, 8EE280E8 90377C0C
ENTITY = :3 ... where RECORD# = :36

....total 15 times

37 34 update recordmst set BATCHKEY = :1, RECORDTYPE = :2,
ENTITY = :3 ... where RECORD# = :36 8EE280E8 8C215350

16 rows selected.


SQL> select parsing_user_id, parsing_schema_id, sql_text, address, version_count from v$sqlarea where address = '8EE280E8' ;

PARSING_USER_ID PARSING_SCHEMA_ID SQL_TEXT ADDRESS VERSION_COUNT
--------------- ----------------- -------------------------------------------------------------- -------- -------------
0 0 update prrecordmst set PRBATCHKEY = :1, RECORDTYPE = :2, 8EE280E8 16
ENTITY = :3 ... where RECORD# = :36

SQL> select * from v$sql_shared_cursor where KGLHDPAR = '8EE280E8';

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
-------- -------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
8C215350 8EE280E8 N N 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

--What would be the cause of BIND_MISMATCH?

SQL> select address, child_address, CHILD_NUMBER , HASH_VALUE, PLAN_HASH_VALUE, PARSING_USER_ID, PARSING_SCHEMA_ID from v$sql where address = '8EE280E8' ;

ADDRESS CHILD_AD CHILD_NUMBER HASH_VALUE PLAN_HASH_VALUE PARSING_USER_ID PARSING_SCHEMA_ID
-------- -------- ------------ ---------- --------------- --------------- -----------------
8EE280E8 8BCF8008 0 1907582321 0 0 0
8EE280E8 8C69CD4C 1 1907582321 0 0 0
8EE280E8 8FFE65BC 2 1907582321 0 0 0
8EE280E8 8E532E2C 3 1907582321 0 0 0
8EE280E8 8E3F5A54 4 1907582321 0 0 0
8EE280E8 92C90050 5 1907582321 0 0 0
8EE280E8 91A600E0 6 1907582321 0 0 0
8EE280E8 8FBD4D84 7 1907582321 0 0 0
8EE280E8 8FD49758 8 1907582321 0 0 0
8EE280E8 8F597C14 9 1907582321 0 0 0
8EE280E8 8B94E608 10 1907582321 0 0 0
8EE280E8 8F6A24BC 11 1907582321 0 0 0
8EE280E8 8E9425DC 12 1907582321 0 0 0
8EE280E8 9273E6F0 13 1907582321 0 0 0
8EE280E8 90377C0C 14 1907582321 0 0 0
8EE280E8 8C215350 15 1907582321 3689500139 37 34


and Tom said...

It is all about bind values in this case. There are many factors concerning the ability to share a cursor -- and the length of the bind variables can affect this.

Consider this simple example:


ops$tkyte@ORA920> create table t ( x varchar2(2000) );
Table created.

ops$tkyte@ORA920> alter system flush shared_pool;
System altered.

ops$tkyte@ORA920> declare
2 a varchar2(1) := 'x';
3 b varchar2(100) := rpad('x',100,'x');
4 c varchar2(500) := rpad('x',500,'x');
5 d varchar2(1000) := rpad('x',1000,'x');
6 begin
7 insert into t values(a);
8 insert into t values(b);
9 insert into t values(c);
10 insert into t values(d);
11 end;
12 /

PL/SQL procedure successfully completed.

Now, if you ran sql_trace and tkprof -- you would find there is one query plan generated -- in fact I did and the result was:


INSERT into t values (:b1)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 3 0 0
Execute 4 0.00 0.00 0 4 29 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 7 29 4

Misses in library cache during parse: 1
Misses in library cache during execute: 2
Optimizer goal: CHOOSE
Parsing user id: 119 (recursive depth: 1)

They all resolved to the same "query text" but as you can see by the 4 parse calls -- these were 4 separate SQL statements submitted by PLSQL. Now, looking in v$sql:


ops$tkyte@ORA920> column sql_text format a30
ops$tkyte@ORA920> column address new_val ADDR
ops$tkyte@ORA920> select parsing_user_id puid, parsing_schema_id psid,
2 sql_text, address, child_address
3 from v$sql
4 where sql_text like 'INSERT%into%t%values%(:b1)'
5 /

PUID PSID SQL_TEXT ADDRESS CHILD_AD
---------- ---------- ------------------------------ -------- --------
119 119 INSERT into t values(:b1) 5743C918 57C567B4
119 119 INSERT into t values(:b1) 5743C918 57CC4554
119 119 INSERT into t values(:b1) 5743C918 573D4D3C

We see there are three versions of this insert -- they differ only because of the magnitude of the bind values assigned:

ops$tkyte@ORA920> select *
2 from v$sql_shared_cursor
3 where KGLHDPAR = '&ADDR'
4 /
old 3: where KGLHDPAR = '&ADDR'
new 3: where KGLHDPAR = '5743C918'

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
-------- -------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
57C567B4 5743C918 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
57CC4554 5743C918 N N 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
573D4D3C 5743C918 N N 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


this is caused by wide flucuations in the sizes. I think there is a break point at 32 -- 80 -- ....

ops$tkyte@ORA920> declare
2 a varchar2(1) := 'x';
3 b varchar2(100) := rpad('x',10,'x');
4 c varchar2(500) := rpad('x',20,'x');
5 d varchar2(1000) := rpad('x',30,'x');
6 begin
7 insert into t xxx values(a);
8 insert into t xxx values(b);
9 insert into t xxx values(c);
10 insert into t xxx values(d);
11 end;
12 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select parsing_user_id puid, parsing_schema_id psid,
2 sql_text, address, child_address
3 from v$sql
4 where sql_text like 'INSERT%into%t%values%(:b1)'
5 /

PUID PSID SQL_TEXT ADDRESS CHILD_AD
---------- ---------- ------------------------------ -------- --------
119 119 INSERT into t xxx values(:b1) 57C23DF0 57B9C2FC
119 119 INSERT into t values(:b1) 57CE0AEC 57301790
119 119 INSERT into t values(:b1) 57CE0AEC 57C136B0
119 119 INSERT into t values(:b1) 57CE0AEC 57C48E44

they all parsed to the same child cursor, but:

ops$tkyte@ORA920> declare
2 a varchar2(1) := 'x';
3 b varchar2(100) := rpad('x',10,'x');
4 c varchar2(500) := rpad('x',32,'x');
5 d varchar2(1000) := rpad('x',33,'x');
6 begin
7 insert into t yyy values(a);
8 insert into t yyy values(b);
9 insert into t yyy values(c);
10 insert into t yyy values(d);
11 end;
12 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select parsing_user_id puid, parsing_schema_id psid,
2 sql_text, address, child_address
3 from v$sql
4 where sql_text like 'INSERT%into%t%values%(:b1)'
5 /

PUID PSID SQL_TEXT ADDRESS CHILD_AD
---------- ---------- ------------------------------ -------- --------
119 119 INSERT into t xxx values(:b1) 57E794B4 57EEBA78
119 119 INSERT into t values(:b1) 57A6E0E4 57C1FD78
119 119 INSERT into t values(:b1) 57A6E0E4 575DA0C0
119 119 INSERT into t values(:b1) 57A6E0E4 57C18D9C
119 119 INSERT into t yyy values(:b1) 578824D4 57DF5E2C
119 119 INSERT into t yyy values(:b1) 578824D4 57DDD68C

6 rows selected.

resulted in 2 yyy versions (32/33)...


The parsing_user/schema_id is recursive sql (sql executed as sys) generally.

Rating

  (79 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Your examples and explanations are excellent.

gs, April 30, 2003 - 4:13 pm UTC

Tom,

You have four inserts in the fist PL/SQL block and has only three SQL statements in the shared pool. Is it becaz, two of them could be sharing the same SQL, though the bind length is different?

Is the version_count in v$sqlarea same as the count(KGLHDPAR) from v$sql_shared_cursor? [Assume the entries are still there in the view]

Which V$ tables are the right one to find the multiple versions of the same SQL?

Also I noticed, when the statement get invalidated, the parsing user/schemaid becomes "0". And, when someone else re-issues the same statement, the parsing user/schemaid get assigned the id of the executing user/schema.

In my original posting, none of those statements were recursive SQLs. They were issued by OCI calls from front end. Could there be any other reason other than invalidations, causing the Parsing user/schema id to be 0 [assuming they are not recursive]

Thanks....

2:47:51 DBUSR> create or replace synonym test_user.t1 for test_owner.v_t1 ;

Synonym created.

12:48:22 DBUSR> select INVALIDATIONS, executions, parsing_user_id puid, parsing_schema_id psid, parse_calls, sql_text, address, child_address from v$sql where sql_text like 'select * from t1%' ;

INVALIDATIONS EXECUTIONS PUID PSID PARSE_CALLS SQL_TEXT ADDRESS CHILD_AD
------------- ---------- ---------- ---------- ----------- --------------------------------------------- -------- --------
1 0 0 0 0 select * from t1 74B94B90 72254620

12:48:25 DBUSR> select * from v$sql_shared_cursor where KGLHDPAR = '74B94B90' ;

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
-------- -------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
72254620 74B94B90 N N N N N N N 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 TEST_USER from another session "select * from t1" from another session"

12:49:14 DBUSR> select INVALIDATIONS, executions, parsing_user_id puid, parsing_schema_id psid, parse_calls, sql_text, address, child_address from v$sql where sql_text like 'select * from t1%' ;

INVALIDATIONS EXECUTIONS PUID PSID PARSE_CALLS SQL_TEXT ADDRESS CHILD_AD
------------- ---------- ---------- ---------- ----------- --------------------------------------------- -------- --------
2 1 89 89 1 select * from t1 74B94B90 72254620

-- as TEST_OWNER from another session, "select * from t1"

12:49:20 DBUSR> select INVALIDATIONS, executions, parsing_user_id puid, parsing_schema_id psid, parse_calls, sql_text, address, child_address from v$sql where sql_text like 'select * from t1%' ;

INVALIDATIONS EXECUTIONS PUID PSID PARSE_CALLS SQL_TEXT ADDRESS CHILD_AD
------------- ---------- ---------- ---------- ----------- --------------------------------------------- -------- --------
2 1 89 89 1 select * from t1 74B94B90 72254620
0 1 88 88 1 select * from t1 74B94B90 727D2060

12:51:19 DBUSR> select * from v$sql_shared_cursor where KGLHDPAR = '74B94B90' ;

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
-------- -------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
72254620 74B94B90 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
727D2060 74B94B90 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N


Tom Kyte
April 30, 2003 - 7:26 pm UTC

yes -- this shows it was the 500/1000 pair that shared:

ops$tkyte@ORA920> create table t ( x varchar2(2000) );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> alter system flush shared_pool;

System altered.

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2          a   varchar2(1)    := 'x';
  3          b   varchar2(100)  := rpad('x',100,'x');
  4          c   varchar2(500)  := rpad('x',500,'x');
  5          d   varchar2(1000) := rpad('x',1000,'x');
  6  begin
  7          --insert into t values(a);
  8          --insert into t values(b);
  9          insert into t values(c);
 10          insert into t values(d);
 11  end;
 12  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> column sql_text format a30
ops$tkyte@ORA920> column address new_val ADDR
ops$tkyte@ORA920> select parsing_user_id puid, parsing_schema_id psid,
  2         sql_text, address, child_address
  3    from v$sql
  4   where sql_text like 'INSERT%into%t%values%(:b1)'
  5  /

      PUID       PSID SQL_TEXT                       ADDRESS  CHILD_AD
---------- ---------- ------------------------------ -------- --------
       358        358 INSERT into t values(:b1)      5CBC5DA0 5CC6A9E8


v$sqlarea is an aggregate of v$sql and yes -- it should match in parity with v$sql_shared_cursor...


The other time I've seen 0 is failed parses....


ops$tkyte@ORA920> alter system flush shared_pool;

System altered.

ops$tkyte@ORA920>
ops$tkyte@ORA920> begin
  2          for x in ( select * from not_there ) loop null; end loop;
  3  end;
  4  /
        for x in ( select * from not_there ) loop null; end loop;
                               *
ERROR at line 2:
ORA-06550: line 2, column 25:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 11:
PL/SQL: SQL Statement ignored


ops$tkyte@ORA920>
ops$tkyte@ORA920> select sql_text, parsing_user_id, parsing_schema_id
  2    from v$sql
  3   where sql_text like '%not_there%';

SQL_TEXT                       PARSING_USER_ID PARSING_SCHEMA_ID
------------------------------ --------------- -----------------
select sql_text, parsing_user_             358               358
id, parsing_schema_id   from v
$sql  where sql_text like '%no
t_there%'

begin  for x in ( select * fro               0                 0
m not_there ) loop null; end l
oop; end;





 

Different execution plans for children of same SQL

Ashish S, November 07, 2003 - 3:08 pm UTC

Is it possible that the childern of same SQL get
different execution plans?

We have a situation where there 2 children in V$SQL, both
have object_status VALID. The V$SQL_PLAN table shows 2
different execution plans. One uses index and other
one does FULL table scan.

The V$SQL_SHARED_CURSOR shows that the difference between
these child cursors is BIND_MISMATCH.


Tom Kyte
November 07, 2003 - 5:26 pm UTC

yes. absolutely. that is one of the reasons for child cursors. 100%



Good

Ashish S, November 10, 2003 - 9:13 am UTC

1. Does BIND_MISMATCH between the two different plans mean
that the optimizer is going to use different plans
depending on value of bind variable?

2. On 9i RAC, we see different in execution plans on
different nodes for the same SQL. Why this happens?

Thanks for your help!

Tom Kyte
November 10, 2003 - 12:07 pm UTC

1) COULD use different, has the ABILITY to use different. not "will"

2) you have different init.ora parameters set that influence the optimizer (eg: db_file_multiblock_read_count) or you have different SESSION settings for some of those parameters.

What can be done to alleviate this ?

Neil, February 25, 2004 - 10:38 am UTC

Hi Tom

1. If we have multiple entries in v$sql_shared_cursor for the same address, then does that mean that the statement is being re-parsed ?  and if so is it a soft or hard parse. 
Can anything be done to prevent this bind mismatch / bind size issue ? 

2. If I have multiple entries for the same address, but none of the columns in v$sql_shared_cursor has a value of 'Y', what does that mean ? I am confused because we have a statements that vary only by one bind variable, which is assigned in pl/sql by selecting nextval from a sequence.

The sql can be seen in v$sql :-

SQL> select SQL_TEXT,LOADED_VERSIONS,OPEN_VERSIONS,EXECUTIONS,INVALIDATIONS,PARSING_USER_ID,PARSING_SCHEMA_ID,ADDRESS,TYPE_CHK_HEAP,HASH_VALUE,CHILD_NUMBER,MODULE,IS_OBSOLETE from v$sql where address='00000003FDB4A980'

SQL_TEXT
----------------------------------------------------------------------------------------------------
LOADED_VERSIONS OPEN_VERSIONS EXECUTIONS INVALIDATIONS PARSING_USER_ID PARSING_SCHEMA_ID
--------------- ------------- ---------- ------------- --------------- -----------------
ADDRESS          TYPE_CHK_HEAP    HASH_VALUE CHILD_NUMBER
---------------- ---------------- ---------- ------------
MODULE                                                           IS_
---------------------------------------------------------------- ---
SELECT  X_AE_CITY, X_BE_CITY, ORDERS_COUNT, FAULTS_COUNT , X_ASSET_NUM, INSTALL_DT FROM RESULT_TAB
 WHERE QUERY_ID = :1 ORDER BY X_ASSET_NUM ASC
              0             0          0             7               0                 0
00000003FDB4A980 00                 52054600            0
JDBC Thin Client                                                 N
SELECT  X_AE_CITY, X_BE_CITY, ORDERS_COUNT, FAULTS_COUNT , X_ASSET_NUM, INSTALL_DT FROM RESULT_TAB
 WHERE QUERY_ID = :1 ORDER BY X_ASSET_NUM ASC
              1             1       1416             6              24                24
00000003FDB4A980 00                 52054600            1
JDBC Thin Client                                                 N
SELECT  X_AE_CITY, X_BE_CITY, ORDERS_COUNT, FAULTS_COUNT , X_ASSET_NUM, INSTALL_DT FROM RESULT_TAB
 WHERE QUERY_ID = :1 ORDER BY X_ASSET_NUM ASC
              0             0          0             6               0                 0
00000003FDB4A980 00                 52054600            2
JDBC Thin Client                                                 N
SELECT  X_AE_CITY, X_BE_CITY, ORDERS_COUNT, FAULTS_COUNT , X_ASSET_NUM, INSTALL_DT FROM RESULT_TAB
 WHERE QUERY_ID = :1 ORDER BY X_ASSET_NUM ASC
              0             0          0             8               0                 0
00000003FDB4A980 00                 52054600            3
JDBC Thin Client                                                 N
SELECT  X_AE_CITY, X_BE_CITY, ORDERS_COUNT, FAULTS_COUNT , X_ASSET_NUM, INSTALL_DT FROM RESULT_TAB
 WHERE QUERY_ID = :1 ORDER BY X_ASSET_NUM ASC
              0             0          0             5               0                 0
00000003FDB4A980 00                 52054600            4
JDBC Thin Client                                                 N
JDBC Thin Client                                                 N
SELECT  X_AE_CITY, X_BE_CITY, ORDERS_COUNT, FAULTS_COUNT , X_ASSET_NUM, INSTALL_DT FROM RESULT_TAB
 WHERE QUERY_ID = :1 ORDER BY X_ASSET_NUM ASC
              0             0          0             5               0                 0
00000003FDB4A980 00                 52054600            6
JDBC Thin Client                                                 N
SELECT  X_AE_CITY, X_BE_CITY, ORDERS_COUNT, FAULTS_COUNT , X_ASSET_NUM, INSTALL_DT FROM RESULT_TAB
 WHERE QUERY_ID = :1 ORDER BY X_ASSET_NUM ASC
              0             0          0             5               0                 0
00000003FDB4A980 00                 52054600            7
JDBC Thin Client                                                 N
SELECT  X_AE_CITY, X_BE_CITY, ORDERS_COUNT, FAULTS_COUNT , X_ASSET_NUM, INSTALL_DT FROM RESULT_TAB
 WHERE QUERY_ID = :1 ORDER BY X_ASSET_NUM ASC
              0             0          0             3               0                 0
00000003FDB4A980 00                 52054600            8
JDBC Thin Client                                                 N
SELECT  X_AE_CITY, X_BE_CITY, ORDERS_COUNT, FAULTS_COUNT , X_ASSET_NUM, INSTALL_DT FROM RESULT_TAB
 WHERE QUERY_ID = :1 ORDER BY X_ASSET_NUM ASC
              0             0          0             2               0                 0
00000003FDB4A980 00                 52054600            9
JDBC Thin Client                                                 N
SELECT  X_AE_CITY, X_BE_CITY, ORDERS_COUNT, FAULTS_COUNT , X_ASSET_NUM, INSTALL_DT FROM RESULT_TAB
 WHERE QUERY_ID = :1 ORDER BY X_ASSET_NUM ASC
              0             0          0             1               0                 0
00000003FDB4A980 00                 52054600           10
JDBC Thin Client                                                 N
SELECT  X_AE_CITY, X_BE_CITY, ORDERS_COUNT, FAULTS_COUNT , X_ASSET_NUM, INSTALL_DT FROM RESULT_TAB
 WHERE QUERY_ID = :1 ORDER BY X_ASSET_NUM ASC
              0             0          0             1               0                 0
00000003FDB4A980 00                 52054600           11
JDBC Thin Client                                                 N

12 rows selected.

But in v$sql_shared_cursor, I see

SQL> select * from v$sql_shared_cursor where KGLHDPAR ='0
SQL> select * from v$sql_shared_cursor where KGLHDPAR ='00000003FDB4A980';

ADDRESS          KGLHDPAR         UNB SQL OPT OUT STA LIT SEC EXP BUF PDM INS SLA TYP AUT BIN DES
---------------- ---------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
LAN TRA ROW INS INS REM LOG INC OVE SQL MV_ USE TYP NO_ FLA
--- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
00000003FDE3AE68 00000003FDB4A980 N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
00000003F6866FC8 00000003FDB4A980 N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
00000003FB19B1B8 00000003FDB4A980 N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
00000003F7F2B338 00000003FDB4A980 N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
00000003FC39EF58 00000003FDB4A980 N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
00000003FC6809B0 00000003FDB4A980 N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
00000003FA2387E8 00000003FDB4A980 N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
N   N   N   N   N   N   N   N   N   N   N   N   N   N   N0000003FDB4A980';

So nothing is set to 'Y'.

SO why are they seen as separate statements, and why only 7 rows in v$sql_shared_cursor for the 12 in v$sql ?

Many thanks

Neil

 

Tom Kyte
February 25, 2004 - 11:29 am UTC

1) each child cursor represents a hard parse. short of using consistent bind sizes, no.


2) those queries are coming from jdbc, not plsql -- plsql would use :b1, :b2, :b3 and so on.

looks like a table that is invalidated often (indexed, stats gathered, ddl performed, etc) and the child cursors are there due to the invalidations.


which child cursor is executed by a session ?

Alberto Dell'Era, February 25, 2004 - 1:30 pm UTC

Joining v$session.sql_address and v$sql.address would get *all* the child cursors "associated" with a session - is it possible (9iR2) to get the single child cursor which is/was really executed by the session ?

tnx
alberto

Tom Kyte
February 25, 2004 - 2:41 pm UTC

in 9ir2, it is hard (dbms_sql -- and the number returned by that as the cursor handle will let you do it easily)

select distinct sid, sql_hash_value, child_number
from v$session a, v$sql b, dba_kgllock c
where b.child_address = c.kgllkhdl
and a.saddr = c.kgllkuse
and a.sql_address = b.address
/

is a query i've seen people use, I have not personally vetted it myself.

in 10g, it is much more straight forward (SQL_CHILD_NUMBER added to v$session).

So each child is hard parsed, why 0 for executions

Neil campbell, February 26, 2004 - 5:10 am UTC

Tom

1) if each child is hard parsed, why is only one of the rows above showing a value for executions ? Can it be parsed without being executed ?

2)All the entries in v$sql have invalidations, yet not all appear in v$sql_shared_cursor - why is that ?

3) RE: consistent bind sizes - I am confused, beacause the value for the bind in the WHERE QUERY_ID = :1 predicate is ust a sequence number currently at about 85000 and incrementing. I'm not certain how the statement is constructed in Java, but how would it be prepared so that it made Oracle think it was such a difference in size ?
Any chance of an example ?

4) Table getting invalidated is interesting. I wasn;t really aware of that. However, I am not aware of any ddl, index creation or stats gathering as it is effectivly a temporary table (not a Global Temporary table) for sotring the resulst of the queries. Is there anything else that can cause the invalidations.

Best Regards

Neil

Tom Kyte
February 26, 2004 - 10:18 am UTC

1) absolutely, happens all of the time. generic class libraries do it, developers do it, sure.

2) you'll have queries in your shared pool that reference tables that don't even exist anymore. they get cleaned out as space is needed.

3) why do you think this was a bind mismatch -- since you have all N's and bind mismatch is one of the columns I don't think you do. You just asked about "how to solve bind mismatches" and the answer is "make sure you use the same bind size"

4) authorizations as well for example. truncates too.

Why no bind mismatch ??

Kevin, February 26, 2004 - 5:15 am UTC

this is very interesting Tom!

But in the example above from Neil, if the problem was due to inconsistent bid sizes, wouldn't it show in v$sql_shared_cursor ? In that example it doesn't ?

Tom Kyte
February 26, 2004 - 10:19 am UTC

correct, they do not have a bind mismatch -- i was just answering the question "how to avoid bind mismatch"

Ahh - I had assumed it was bind mismatch

Neil Campbell, February 26, 2004 - 11:20 am UTC

Ok - so because I see the QUERY_ID = :1
I know the developers are using binds - thats good.

But, we are also causing the parsed version to be invalidated because of a table invalidation, or perhaps something else ?

How can I investigate further what is causing these child cursors to be created ?

thanks very much

Neil


Tom Kyte
February 26, 2004 - 2:17 pm UTC

well, you could use a mixture of auditing and DDL triggers on the table to see what might be messing with the table itself.

a "temporary" table might lead me down the "truncate" path of thinking -- makes sense perhaps?

Oracle internal error

push, August 03, 2004 - 5:38 pm UTC

Tom,

One of the SQL statement from a PL/SQL stored
program is having multiple versions in the V$SQL view.

This PL/SQL stored program is called from the Java code.

This statement has 137 entries in V$SQL but has only 13 
entries in V$SQL_SHARED_CURSOR view and BIND_MISMATCH for
all the 13 entries are Y.

SQL> select * from v$sql where address='C00000002038E298'
/

...
...
137 rows selected.

SQL> select * from v$sql_shared_cursor where KGLHDPAR='C00000002038E298';

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
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
C00000002F5D59F8 C00000002038E298 N N 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
C000000023278BC0 C00000002038E298 N N 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
C00000004130CEE0 C00000002038E298 N N 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
C00000003885F650 C00000002038E298 N N 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
C00000002F334930 C00000002038E298 N N 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
C000000020699EE8 C00000002038E298 N N 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
C000000041E52B68 C00000002038E298 N N 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
C00000002F5938E0 C00000002038E298 N N 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
C00000002F56D210 C00000002038E298 N N 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
C000000023A53AD0 C00000002038E298 N N 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
C0000000411DFF58 C00000002038E298 N N 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
C0000000234CEEE8 C00000002038E298 N N 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
C0000000414A3D40 C00000002038E298 N N 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

13 rows selected.

And very quite often we are getting ORA-600 because of this SQL.
ORA-00600: internal error code, arguments: [736], [7], [0], [128], [128], [], [], []
Tue Aug  3 17:05:59 2004
Errors in file /app/oracle/admin/udump/ora_26830.trc:

There are no DDL, grants performed on the table or indexes
associated with this SQL statement.

If I call this stored program within SQLPLUS any number
of time then it does not create multiple versions in V$SQL or it does not cause ORA-0600 error.

This problem occurs only when this PL/SQL stored program 
is called from the Java code.

Thanks for your help.
 

Tom Kyte
August 03, 2004 - 6:09 pm UTC

cursor sharing is set to what? seems like it should reproduce, do you have a minimal test case? you have filed a tar with support right (ora-600 = you do that)

CURSOR_SHARING=EXACT

Push, August 04, 2004 - 2:28 pm UTC

Tom,
The CURSOR_SHARING parameter is set to "EXACT".
I am not able to reproduce this error within the
database but only happens when it is called from the
Java application. Working on a test case for that
to report to oracle.
Thanks
Push..

remove_constants

A reader, October 16, 2004 - 10:49 pm UTC

How can we make use of the new v$sql_shared_cursor view in 9iR2 to determine if apps are not using bind variables? Something like v$sql_cursor minus v$sql_shared_cursor? :)

Would this new view offer a better way to do this than your remove_constants script?

Tom Kyte
October 17, 2004 - 9:47 am UTC

that view shows why sql statements that look like they could have been shared -- were not.

it isn't useful to find sql statements that -- except for the lack of binds -- would have been shared.

A reader, October 17, 2004 - 6:11 pm UTC

"it isn't useful to find sql statements that -- except for the lack of binds -- would have been shared"

Um, isnt that exactly what your remove_constants script shows? To quote you "The output of that last query will show you statements that are identical in the shared pool after all numbers and character string constants have been removed. These statements -- and more importantly their counts -- are the potential bottlenecks. In addition to causing the contention, they will be HUGE cpu consumers"

Tom Kyte
October 17, 2004 - 6:36 pm UTC

yes, that is what my script shows.

please re-read the above followup. they were asking "hey, with this 9i v$ view -- do we still need this remove constants thing"

the answer is YES.

you cannot use the v$sql_shared_cursor view as a replacement for this. that was the crux of the answer immediately above.

A reader, October 17, 2004 - 7:53 pm UTC

"you cannot use the v$sql_shared_cursor view as a replacement for this"

Why not? Maybe I dont understand what v$sql_shared_cursor.bind_mismatch='Y' means, but isnt it saying the same thing that remove_constants is saying? i.e. this SQL couldnt be shared with an existing SQL in the shared pool because there was a bind variable mismatch i.e. one had a bind variable and the other had a literal, or both had literals?

What am I missing? Thanks

Tom Kyte
October 17, 2004 - 8:04 pm UTC

no, consider:

select * from emp where empno = 1234;
select * from emp where empno = 5678;


will those appear in v$sql_shared_cursor as the same cursor????? NO, they will not.

that is what remove_constants will do -- remove the constants so they visually become:

select * from emp where empno = @


both -- so we can see "hey, you know what, if you actually USED BINDS these would be the *same*"

until then -- they are not the same and hence could not possibly be in v$sql_shared_cursor as being mismatched because of binds!!!!!!!!! (eg: in order to have a bind mismatch, well, you have to be using binds!)

bind_mismatch

A reader, October 17, 2004 - 9:13 pm UTC

Ah, I see. But then I dont understand what bind_mismatch=Y really means?

Does it only apply to the size of the bind variable as you showed above? So when bv's are numeric, bind_mismatch will never be Y?

Tom Kyte
October 18, 2004 - 7:51 am UTC

the bind mismatch is based on the size/type -- and numbers and dates are fixed size (22/7) so if you always used a number -- there would be no bind mismatch (but there could be multiple child cursors still! cursor_sharing=similar for example...)

bind_mismatch with date or number columns?

Robert, October 27, 2004 - 9:36 am UTC

I have been troubleshooting a bind mismatch issue in my application. I have been using the following queries to pull candidates and then look at v$sql_bind_metadata to determine if the sizes of the bind columns are different. This assumes that the statements are not aged out of memory during the investigation.

-- Find those that have multiple children
-- with a bind_mismatch
select kglhdpar, count(*)
from v$sql_shared_cursor
where bind_mismatch='Y'
group by kglhdpar order by 2;

-- Copy and paste an address from the above query
-- into the following statements
select * from v$sqlarea where address='&addr';

-- Make sure ONLY bind_mismatch is set to Y
select * from v$sql_shared_cursor where kglhdpar='&addr';

-- Return those positions whose sizes do not match
-- If you want to see raw data, then eliminate the
-- having clause
select position, datatype, max_length, count(*) from v$sql_bind_metadata
where address in (select address from v$sql_shared_cursor where kglhdpar='&addr')
group by position, datatype, max_length
having count(*)!=(select count(*) from v$sql_shared_cursor where kglhdpar='&addr')
order by 1;

My problem is that often, I do not see any differences on v$sql_bind_metadata with the datatypes and max_length of the bind variables! So I am at a loss for why the statements are not shared and they are flagged as a bind mismatch.

Is it possible to get a bind mismatch with date and number columns (sizes are always 7 and 22 respectively in my database) such that it does not show up on v$sql_bind_metadata? I have noticed that the application heavily uses decode functions in the statements. Would this affect the bind mismatch?

I have been unable to reproduce a test case, but I can find several statements in the database at any given time. The application utilizes OCI calls.

Tom Kyte
October 27, 2004 - 10:04 am UTC

give me an example query to play with.

Date of size 7???

Bob B, October 27, 2004 - 10:16 am UTC

Maybe I'm losing it, but what is a date of size 7? Is it being stored as a string or something? As far as I know, a date is a date is a date ...



Tom Kyte
October 27, 2004 - 12:08 pm UTC

we store

yy 1
yy 2
mm 3
dd 4
hh 5
mm 6
ss 7

in there.

RE: Date of Size 7

Mark A. Williams, October 27, 2004 - 12:04 pm UTC

An Oracle date is of size (i.e. length) 7. You can see this in the following simple example:

SQL> connect /
Connected.
SQL> create table t
  2  (
  3    c date
  4  );

Table created.

SQL> insert into t values (sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select dump(c) from t;

DUMP(C)
--------------------------------------
Typ=12 Len=7: 120,104,10,27,11,38,52

1 row selected.

- Mark

===================================================
Mark A. Williams
Oracle DBA
Author, Professional .NET Oracle Programming
http://www.apress.com/book/bookDisplay.html?bID=378

Not that it matters

Dan Malumphy, October 27, 2004 - 2:26 pm UTC

But why the discrepency in the time columns?
SQL> get joe
  1  create table cow (dadate date);
  2  insert into cow values (sysdate);
  3  commit;
  4  select to_char(dadate, 'mm dd yyyy hh24:mi:ss'), dump(dadate)
  5* from cow;
SQL> @joe
 
Table created.
 
 
1 row created.
 
 
Commit complete.
 
:
 
TO_CHAR(DADATE,'MMD
-------------------
DUMP(DADATE)
--------------------------------------------------------------------------------
10 27 2004 14:00:25
Typ=12 Len=7: 120,104,10,27,15,1,26
 
version 9 and 10 show similar results
  

Tom Kyte
October 27, 2004 - 4:25 pm UTC

what discrepency?

it is not stored just as yy, yy, mm, dd, ....

it is stored in excess 100 notation, blah blah blah -- internal format. but each byte is used to represent yy, yy, mm, dd, hh.....



RE: Not that it matters

Mark A. Williams, October 27, 2004 - 3:22 pm UTC

The date is stored as:

yy 1 - trunc(yy / 100) + 100
yy 2 - mod(yy, 100) + 100
mm 3 - simple month (1-12)
dd 4 - simple day (1-31)
hh 5 - hh + 1
mm 6 - mm + 1
ss 7 - ss + 1

You can see this by "inserting midnight" then dumping the results:

SQL> insert into t values (trunc(sysdate));

1 row created.

SQL> commit;

Commit complete.

SQL> select c, dump(c) from t;

C         DUMP(C)
--------- ------------------------------------------------
27-OCT-04 Typ=12 Len=7: 120,104,10,27,1,1,1

1 row selected.

- Mark

===================================================
Mark A. Williams
Oracle DBA
Author, Professional .NET Oracle Programming
http://www.apress.com/book/bookDisplay.html?bID=378

Thumbs down to Mark A. Williams

Darrell, October 27, 2004 - 9:29 pm UTC

In reference to Mark A. Williams review above:

0 stars to Mr. Williams for professional ethics and etiquette.

5 stars to Mr. Williams for shameless self-promotion.

I have been an avid daily reader of this discussion forum for about 4 years now. I was going to bite my tongue on this issue, but since Mr. Williams continues to persist, I must comment.

For the past couple of months now, Mr. Williams has decided to use this forum as a venue to promote his new book by placing a link to that book in each of his reviews (this is not the only review he has done such). While I congratulate Mr. Williams for his efforts in writing the book (we all know that writing a book takes considerable time, effort and sacrifice for very little reward), I do not applaud his marketing efforts.

In these days when we are all inundated with phone calls from telemarketers, endless Spam, and the ever annoying pop-ups while visiting web sites; it was refreshing to have a place on the web that is truly devoted to the enlightenment of the Oracle community without the sales pitch.

All of us that participate in this discussion forum would benefit directly or indirectly from the promotion of some product or service. That does not mean we should take the opportunity.

If your book warrants consideration, I would encourage Tom to place a link to your book on his “Links I Like” page or even the main page, but please lets keep the discussions free from the marketing.

Although I have found no specific policy on this web site against marketing, I for one would like to keep it a purely educational site. What do you say people? Lets use this discussion forum to promote our ideas, not to promote ourselves, or our products.

Tom Kyte
October 27, 2004 - 10:19 pm UTC

I sort of like the identification he gives of himself.

far too many "a reader" out there.

Do you have the same issue with the emails I use from this site? Each of them contains:

Effective Oracle By Design
</code> http://www.amazon.com/exec/obidos/tg/detail/-/0072230657

Expert One on One Oracle
http://www.amazon.com/exec/obidos/tg/detail/-/1590592433

thomas.kyte@oracle.com

http://asktom.oracle.com/ http://asktom.oracle.com/magazine-archive.htm <code>


in them. If you ask a question, get a followup to a review, you are literally bombarded with that.

As one that has written a pair of books -- knowing the amount of time/energy/effort that goes into it (and foolishing getting ready mentally to do it all over again), I do not begrudge anyone a tagline myself. Especially since they identify themselves 100%.

I have an ad on my home page. With a picture and all.

I don't know -- I'm not against it, people have asked me before about this. "do you mind if I put a link on your site to a product I sell or that I make". If it has relevance to Oracle -- I'm all for it. If it were not relevant to Oracle/using Oracle -- I'd be against it.



Example of false bind mismatch

A reader, October 27, 2004 - 10:23 pm UTC

Tom,
This is the simplest example that I have seen so far in my investigation.  Most are much more complicated with 10's of bind variables and embedded functions (like decode).  I have logged a support TAR on this and have a bug opened (3975282), so I would understand if you wanted to pass on this.  I was mainly asking if you have ever heard of this scenario.  I have not been able to reproduce this situation in SQL*Plus.

The schema for this table from an import indexfile (I have eliminated the tablespace and storage clauses and made it more readable).

CREATE TABLE "ECO_ACTION_QUEUE" (
  "ORDER_ID" NUMBER NOT NULL ENABLE, 
  "ACTION_SEQUENCE" NUMBER NOT NULL ENABLE, 
  "ACTION_TYPE_CD" NUMBER NOT NULL ENABLE, 
  "ORDER_STATUS_CD" NUMBER NOT NULL ENABLE, 
  "EFFECTIVE_DT_TM" DATE, 
  "NEXT_INSTANCE_DT_TM" DATE, 
  "PROCESSED_DT_TM" DATE, 
  "UPDT_DT_TM" DATE NOT NULL ENABLE, 
  "UPDT_ID" NUMBER NOT NULL ENABLE, 
  "UPDT_TASK" NUMBER NOT NULL ENABLE, 
  "UPDT_CNT" NUMBER NOT NULL ENABLE, 
  "UPDT_APPLCTX" NUMBER NOT NULL ENABLE) ;

ALTER TABLE "ECO_ACTION_QUEUE" MODIFY ("ORDER_ID" DEFAULT 0 ) ;
ALTER TABLE "ECO_ACTION_QUEUE" MODIFY ("ACTION_SEQUENCE" DEFAULT 0 ) ;
ALTER TABLE "ECO_ACTION_QUEUE" MODIFY ("ACTION_TYPE_CD" DEFAULT 0 ) ;
ALTER TABLE "ECO_ACTION_QUEUE" MODIFY ("ORDER_STATUS_CD" DEFAULT 0 ) ;
ALTER TABLE "ECO_ACTION_QUEUE" MODIFY ("UPDT_DT_TM" DEFAULT SYSDATE ) ;
ALTER TABLE "ECO_ACTION_QUEUE" MODIFY ("UPDT_ID" DEFAULT 0 ) ;
ALTER TABLE "ECO_ACTION_QUEUE" MODIFY ("UPDT_TASK" DEFAULT 0 ) ;
ALTER TABLE "ECO_ACTION_QUEUE" MODIFY ("UPDT_CNT" DEFAULT 0 ) ;
ALTER TABLE "ECO_ACTION_QUEUE" MODIFY ("UPDT_APPLCTX" DEFAULT 0 ) ;

CREATE UNIQUE INDEX "XPKECO_ACTION_QUEUE" ON "ECO_ACTION_QUEUE" 
("ORDER_ID" , "ACTION_SEQUENCE" ) ;

ALTER TABLE "ECO_ACTION_QUEUE" ADD CONSTRAINT 
   "XPKECO_ACTION_QUEUE" PRIMARY KEY ("ORDER_ID", "ACTION_SEQUENCE") 
   USING INDEX ENABLE ;


Example rows (table has currently 9280 rows).  I created these insert statements by looking at the table and not from any queries that I know have created child cursors.  I have also modified some of the data to protect privacy.

insert into eco_action_queue values ( 5547020, 1, 2534, 2550, 
    to_date('29-DEC-2001 16:04:18','DD-MON-YYYY HH24:MI:SS'),
    to_date('29-DEC-2001 16:00:00','DD-MON-YYYY HH24:MI:SS'),
    to_date('29-DEC-2001 16:04:18','DD-MON-YYYY HH24:MI:SS'),
    to_date('29-DEC-2001 16:04:18','DD-MON-YYYY HH24:MI:SS'),
    1, 560500, 0, 4452248);     

insert into eco_action_queue values ( 8714715, 1, 2534, 2550, 
    to_date('02-FEB-2002 08:13:46','DD-MON-YYYY HH24:MI:SS'),
    to_date('02-FEB-2002 09:00:00','DD-MON-YYYY HH24:MI:SS'),
    to_date('02-FEB-2002 08:13:47','DD-MON-YYYY HH24:MI:SS'),
    to_date('02-FEB-2002 08:13:47','DD-MON-YYYY HH24:MI:SS'),
    1, 560500, 0, 5134025);     



SQL> exec print_table('select * from v$sqlarea where address=''07000004050BE428''');
SQL_TEXT                      :  INSERT  /*+  CCL<ORM_ADD_ECO_QUEUE:S0101:R000:Q01> */  INTO  ECO_ACTION_QUEUE  (ORDER_ID,
ACTION_SEQUENCE, ACTION_TYPE_CD, ORDER_STATUS_CD, EFFECTIVE_DT_TM, NEXT_INSTANCE_DT_TM, UPDT_DT_TM, UPDT_ID,
SHARABLE_MEM                  : 31968
PERSISTENT_MEM                : 2528
RUNTIME_MEM                   : 75056
SORTS                         : 0
VERSION_COUNT                 : 2
LOADED_VERSIONS               : 2
OPEN_VERSIONS                 : 1
USERS_OPENING                 : 5
FETCHES                       : 0
EXECUTIONS                    : 5554
USERS_EXECUTING               : 0
LOADS                         : 5
FIRST_LOAD_TIME               : 2004-10-26/02:12:57
INVALIDATIONS                 : 0
PARSE_CALLS                   : 4944
DISK_READS                    : 801
BUFFER_GETS                   : 24825
ROWS_PROCESSED                : 5554
COMMAND_TYPE                  : 2
OPTIMIZER_MODE                : MULTIPLE CHILDREN PRESENT
PARSING_USER_ID               : 31
PARSING_SCHEMA_ID             : 31
KEPT_VERSIONS                 : 0
ADDRESS                       : 07000004050BE428
HASH_VALUE                    : 1091929227
MODULE                        : cer_exe/srv_drvr@node_here (TNS V1-V3)
MODULE_HASH                   : 0
ACTION                        :
ACTION_HASH                   : 0
SERIALIZABLE_ABORTS           : 0
CPU_TIME                      : 1510000
ELAPSED_TIME                  : 10278735
IS_OBSOLETE                   : N
CHILD_LATCH                   : 17
-----------------

PL/SQL procedure successfully completed.


SQL> select piece, sql_text from v$sqltext where address='07000004050BE428' order by piece;

     PIECE SQL_TEXT
---------- ----------------------------------------------------------------
         0  INSERT  /*+  CCL<ORM_ADD_ECO_QUEUE:S0101:R000:Q01> */  INTO  EC
         1 O_ACTION_QUEUE  (ORDER_ID, ACTION_SEQUENCE, ACTION_TYPE_CD, ORDE
         2 R_STATUS_CD, EFFECTIVE_DT_TM, NEXT_INSTANCE_DT_TM, UPDT_DT_TM, U
         3 PDT_ID, UPDT_TASK, UPDT_CNT, UPDT_APPLCTX ) VALUES( :1   ,  :2
         4  ,  :3   ,  :4   ,  :5   ,  :6   ,  :7   ,  :8   ,  :9   , 0,  :
         5 10   )

6 rows selected.

SQL> select * from tmp_sql_shared_cursor where kglhdpar='07000004050BE428';

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
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
07000004050BD8B8 07000004050BE428 N N 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
0700000421752638 07000004050BE428 N N 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

SQL> 
SQL> l
  1  select * from v$sql_bind_metadata where address in (
  2  select address from v$sql_shared_cursor where kglhdpar='07000004050BE428')
  3* order by position, address
SQL> /

ADDRESS            POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
07000004050BD8B8          1          2         22          0 1
0700000421752638          1          2         22          0 1
07000004050BD8B8          2          2         22          0 2
0700000421752638          2          2         22          0 2
07000004050BD8B8          3          2         22          0 3
0700000421752638          3          2         22          0 3
07000004050BD8B8          4          2         22          0 4
0700000421752638          4          2         22          0 4
07000004050BD8B8          5         12          7          0 5
0700000421752638          5         12          7          0 5
07000004050BD8B8          6         12          7          0 6
0700000421752638          6         12          7          0 6
07000004050BD8B8          7         12          7          0 7
0700000421752638          7         12          7          0 7
07000004050BD8B8          8          2         22          0 8
0700000421752638          8          2         22          0 8
07000004050BD8B8          9          2         22          0 9
0700000421752638          9          2         22          0 9
07000004050BD8B8         10          2         22          0 10
0700000421752638         10          2         22          0 10

20 rows selected.

SQL> 

Thanks
Robert 

Tom Kyte
November 01, 2004 - 4:19 am UTC

have not been able to reproduce, is this called consistently from the same place in the code (eg: the binding is happening the same way - we always have a null indicator or never do and so on)

why are they using the DATE internal format? that is hugely dangerous, every time I see that, I see people put bad dates in their database. We do not validate the 7 bytes -- we assume they are correct and the are frequently "not"

Additional Information

Robert, October 27, 2004 - 10:29 pm UTC

Tom,
Sorry for the second followup, but I felt it was important to tell you that the Oracle version is 9.2.0.5 on the AIX platform.
Thanks
Robert

RE: Thumbs down to Mark A. Williams

Mark A. Williams, October 27, 2004 - 10:37 pm UTC

Darrell,

I am sorry if you are offended by the inclusion of a link to information on my forthcoming book. It is certainly not intended to be "spam" or "offensive" to anyone. I might point out that the link only goes to information about the book, not its listing on Amazon or something like that.

> 0 stars to Mr. Williams for professional ethics and etiquette.

I fail to see where I have violated any professional ethics or etiquette. Tom has also placed a link on the site when I was asked to respond to a question. See:

</code> http://asktom.oracle.com/pls/ask/f?p=4950:61:2763438257543583502::::P61_ID:25697289047010

In addition to this book I was a technical reviewer for Tom's "Effective Oracle By Design" - would you consider a link to that equally offensive?

? 5 stars to Mr. Williams for shameless self-promotion.

I don't see it as shameless self-promotion as you apparently do. It is only information not a solicitation -  as I said earlier you can't even buy the book from that link.

Again, I am sorry if a link in my "sig" is of more concern to you than the content of my post.

- Mark

===================================================
Mark A. Williams
Oracle DBA
Author, Professional .NET Oracle Programming
http://www.apress.com/book/bookDisplay.html?bID=378 <code>

Re: Mark A Williams

Menon, October 27, 2004 - 10:42 pm UTC

I don't see anything wrong in advertising
a book on Oracle esp if Tom has said ok about it.
One thing Mark, perhaps you can reduce the number
of lines in your signature?

I have appreciated Mark's input earlier on various
issues I believe. Can not say the same about
postings by many other readers.

I for one have posted a few questions and responses
as a "reader" for various reasons I would rather
not discuss here. As long as one is asking
relevant questions and contributing to the forum
I don't see anything wrong (or right) with that.
(And tom, I am not saying that you are saying
it is wrong or right either.)

I have seen people who give very good feedback as a
"reader". I have seen people who identify themselves
and proceed to waste everyone's time by mostly fruitless
discussions.


RE: RE: Thumbs down to Mark A. Williams

Darrell, October 27, 2004 - 11:22 pm UTC

Mark,

If it doesn't bother anyone else, then I guess I was off the mark (no pun intended). But I will respond.

>> I don't see it as shameless self-promotion as you apparently do. It is only information not a solicitation

Normally, when an independent third party makes reference to a book, it's considered informational. But, when the author mentions it himself, it's considered self-promotion (Unless of course you are referencing it as part of the discussion, which I don't think you were. So no Tom, I don't have an issue with that).

>> I fail to see where I have violated any professional ethics or etiquette.

Like I said, we all can benefit from marketing something. For example, I'm an Oracle consultant. Do you think it would be ethical for me to place an link in a review pointing to a web site that promotes my consulting services or to a link that contained my resume as a way to increase my exposure?

Maybe I'm just totally off base, but I think there is a right time and place. I just felt like it wasn't the right place.

RE: RE: RE: Mark A. Williams

Mark A. Williams, October 27, 2004 - 11:47 pm UTC

Darrell,

Going to run out of "RE:'s" pretty soon.

Thanks for the response. I guess I just viewed it like a business card or identifier. It is the same signature I use when posting on the ODP.NET Oracle forums as well. I had asked Tom about it before using it the first time and my usage of it has been pretty spotty actually. However, I am flexible enough to see your point(s) as well.

On a side note, I used to live in Orlando, and it used to be tough to get decent Oracle gigs there - still that way?

- Mark

Mark A. Williams

Darrell, October 28, 2004 - 9:19 am UTC

Mark,

What a small world. I used to live in Indianapolis for over 7 years (moved 4 years ago). I love the city. Of course I'm a little jealous of you since Tom will be visiting your city tomorrow instead of mine. I've been to many INOUG meetings at Lilly over the years.

You are right; the Oracle community in Orlando seems to be a lot smaller than in Indianapolis. But, there is no comparison as far as the weather.

I also see your point about the business card. Congratulations and good luck with the book.

Followup to false bind mismatch

Robert Hayden, November 01, 2004 - 1:44 pm UTC

Tom,
The application code would insert the row in the same manner. We funnel all requests that would be reusing the statement from a single program, similar to a PL/SQL package.

I do not know about how the OCI developer is passing in the date. We use a convert date function that the OCI layer then translates to Oracle.

Here is the source code from the application code. You can see the convert functions on the dates and that the bind variables are coming from an internal array.

INSERT FROM ( ECO_ACTION_QUEUE EA ) SET EA.ORDER_ID= REQUEST -> ORDER_ID ,
EA.ACTION_SEQUENCE= REQUEST -> ACTION_SEQUENCE ,
EA.ACTION_TYPE_CD= REQUEST -> ACTION_TYPE_CD ,
EA.ORDER_STATUS_CD= REQUEST -> ORDER_STATUS_CD ,
EA.EFFECTIVE_DT_TM= CNVTDATETIME ( REQUEST -> EFFECTIVE_DT_TM ),
EA.NEXT_INSTANCE_DT_TM= CNVTDATETIME ( REQUEST -> NEXT_INSTANCE_DT_TM ),
EA.UPDT_DT_TM= CNVTDATETIME ( CURDATE , CURTIME3 ),
EA.UPDT_ID= REQINFO -> UPDT_ID ,
EA.UPDT_TASK= REQINFO -> UPDT_TASK ,
EA.UPDT_CNT= 0 ,
EA.UPDT_APPLCTX= REQINFO -> UPDT_APPLCTX
WITH NOCOUNTER

As you can see, all the real work appears to occur under the covers in OCI.


BTW -- The Oracle bug that I have opened on this subject has it back into my court to create a reproducible test case. I have looked for patterns of usage and have found none. If you think that the bind mismatch could be coming from the bytes used in the date bind variables, then I can get that information from the OCI development team. I have asked BDE what other ways can I get a bind mismatch without changing the sizes of the bind variables? With that information, then I have a better chance on getting an example to them.

Thanks
Robert

Tom Kyte
November 02, 2004 - 6:22 am UTC

what "syntax" is that? that is not "oci" C code? what is the real language/package/api/whatever being used here?

Follow-up to false bind mismatch

Robert, November 02, 2004 - 9:44 am UTC

We use a SQL-like application called CCL that is written in C that utilizes OCI to interact with Oracle. So what I gave was the CCL code. It is then translated to SQL through OCI function calls. Pardon my ignorance, but I do not know OCI or C (I can read but not program).

I took what you said about the direct use of the DATE data type and how Oracle does no validation (assumes correct) to help find a test case. I have reproduced the false bind mismatch symptoms by coding improper syntax to the CCL date function. For some reason, the improper syntax is making it through the parse phase and being executed. The bad date column is inserted with a NULL. If a valid date column is then used (or vice verse), then you get the child cursors.

I am working with the developer to determine (1) why bad syntax to his function calls would ever make it to an Oracle parse phase and (2) if there are valid date combinations that may produce the same symptoms but with valid, working dates. There are no reports of date column issues in the application. If there were, then they would typically be found very quickly.

I still do not understand why Oracle would not reuse the statement since the length of the date column is always 7 bytes with good or bad data. For some reason, the optimizer determines that the cursor is not valid and it must create a child cursor. Could it be the NULL being passed (bad date) compared to the proper 7 bytes of a good date? Would the NULL be treated differently by the optimizer forcing a child?

Thanks for the help. I think you nudged me in the right direction.


Tom Kyte
November 02, 2004 - 10:12 am UTC

without seeing the code -- it is hard to "guess". I hope this isn't a database "independent" sql like thing - ugh.

Follow-up to false bind mismatch

Robert, November 02, 2004 - 2:22 pm UTC

I am afraid to answer that question knowing your position on the subject.

I did reproduce the false bind mismatch by first setting the date bind variable (through our OCI implementation) to a standard date. Then using the same statement, but instead passing in a NULL value for the date. This caused the bind mismatch although the v$sql_bind_metadata still shows the length of the bind value to be 7 bytes for the NULL value. It does not matter in which order you do the statements. I have passed this onto BDE to tell me if this is normal behaviour or a bug. I need to see if this occurs in a different release ....

Thanks for your help. It definitely made me look at the situation differently and ultimately found what I think is the root cause.

Thanks
Robert

clarification about number of records in V$sql_shared_cursor

amit poddar, January 20, 2005 - 7:36 pm UTC

Hi,

You mentioned that v$sqlarea.version_count would be equal to count(v$sql_shared_cursor.kglhdpar).  That makes sense.
But would it be always so, if not then what conidtions would make the above false.

I am asking this because it does not seem to be same in this database:

  1  select a.address,a.version_count,count(b.kglhdpar)
  2  from v$sqlarea a,v$sql_shared_cursor b
  3  where a.version_count > 1 and
  4        a.address=b.kglhdpar
  5  group by a.address,a.version_count
  6* order by 1
SQL> /


ADDRESS          VERSION_COUNT COUNT(B.KGLHDPAR)
---------------- ------------- -----------------
070000007C2970C0             2                 1
070000007C297468             2                 1
070000007C2DDDB8             2                 2
070000007C309760             7                 7
070000007C356348             7                 5
070000007C36C378             5                 5
070000007C3A9F08             2                 1
070000007C3BAED0             3                 3
070000007C468448             2                 1
070000007C4CD430             4                 4
070000007C4F4DD8             2                 1




 

Tom Kyte
January 20, 2005 - 7:51 pm UTC

i see where you could see I said that sort of - only because of a run on question that i answered the last part of :)

aging bits of sql out of the shared pool could have an impact on this.

so, peek at the sql and see what you see...

Re : Child cursor hard parse vs soft parse

A reader, March 20, 2005 - 12:02 pm UTC

Hi Tom,

How are you. In one of the discussion threads above, you mentioned that each child cursor represents a hard-parse. However, I am not clear about this statement. If each of the child cursor were hard-parsed, then the TKPROF output about should have reflected this as follows :
"Missed in Library Cache : 4". However, the TKPROF output shows the following instead:
"Missed in Library Cache : 1" and a parse count as 4 which I understand as 1 hard parse and the remaining 3 as soft-parses. Pls. clarify if my understanding on this is incorrect?
2. When you mean child cursor, you mean when you select from v$SQL_SHARED_CURSOR, you see three different values in child_address column for the same value in address column. Is my understanding on this correct also?


Tom Kyte
March 20, 2005 - 6:41 pm UTC

only if the tkprof you were looking at was a tkprof that resulted from a session that used all 4 child cursors.

The parse count -- how many times the session said "parse this"

the misses - how many times that session had to hard parse

that were were 4 child cursors in this case would only mean some other sessions were using different child cursors, they didn't all have to belong to this session.

v$sql, v$sql_shared_cursor, sure - many views show you details on the various child cursors out there.

Counting Hard Parse

bipul, April 05, 2005 - 6:43 am UTC

Hi Tom.

In one of the post in this thread, you mentioned that

"1) each child cursor represents a hard parse"

So can I count the number of hard parses per sql statement using the following statement:

select sql_text, address,count(distinct child_address), hash_value from v$sql group by sql_text, address, hash_value order by 3 desc;


Thanks
bipul

Tom Kyte
April 05, 2005 - 7:37 am UTC

there are invalidations and reloads to consider as well. so, no -- not in an imperfect world.

How do I find out a true hard parse

Bipul, April 05, 2005 - 9:49 am UTC

Hi Tom.

Thanks for the quick reply. How do I find hard parses in imperfect world !

The code from your book [p450] is to find out if I am using bind variable or not. But if the cursor_sharing is set to similar [or force] then all the literals are converted into bind variables. In this case how do I find out which statements are being hard parsed ?

Thanks
bipul



Tom Kyte
April 05, 2005 - 12:14 pm UTC

v$sysstat
v$sesstat

show parse counts by type.

ALL statements are hard parsed at some time.
ALL of them

and only the first time, so cursor_sharing force will cause soft parsing to kick in, they won't be hard parsed.

Hard parse again

bipul, April 06, 2005 - 6:08 am UTC

Hi Tom.

From v$sysstat and statspack report, I see that we are doing nearly 1.5 hard parse every second. The cursor_sharing is set to SIMILAR at instance level. So we shouldn't be doing any [ or atleast so many] hard parses ...am I right in this assumption?

I guess other factors such as invalidation will cause hard parse, but will it be so high. We don't change the table structure/truncate tables frequently. Any pointer on the cause of this high hard parse will be very helpful.

Size of shared_pool is 384MB.

Thanks
bipul

Tom Kyte
April 06, 2005 - 9:11 am UTC

no, cursor_sharing similar would be "ensure" that.

if your application doesn't construct queries in a predicable sense, in a predicable fashion, they will be "new queries"



Can you please explain this a bit more

bipul, April 06, 2005 - 11:47 am UTC

Hi Tom.

Can you please explain this last bit with an example.

"if your application doesn't construct queries in a predicable sense, in a predicable fashion, they will be "new queries" "

Many Thanks
bipul


Tom Kyte
April 06, 2005 - 2:05 pm UTC

if they don't construct the queries in the same way

select * from t where id = 5
select * from T where id = 5
sleect /* bob ran this */ * from t where id = 5

are all different. t <> T for example

followup

bipul, April 07, 2005 - 9:17 am UTC

Hi Tom.

The queries are constructed in the same way. What I see from v$sql is that same sql_text has more than 1000 versions.

For example:

sql_text address count(distinct child_cursor)
SELECT man_isi from man where man_pubmed = :"SYS_B_0" D9F271BC 1085

Am i correct in assuming that this sql will cause some hard parse [even though as you said earlier number of child cursors is not always hard parse in imprefect world!] ?

And if yes, then why it should?

Is there any way to find out the queries thats causing the hard parse when cursor_sharing is set to similar ?

Thanks for your help!
bipul



Tom Kyte
April 07, 2005 - 11:04 am UTC

Look at v$sql_shared_cursor and it'll tell you why there are 1,000 versions of that cursor..


the number of child cursors is less than or equal to the number of hard parses (there was a hard parse for EACH child)

search this site for v$sql_shared_cursor, effective oracle by design covers it as well.


It might be a bug

bipul, April 07, 2005 - 12:52 pm UTC

Hi Tom.

Thanks for your response.

I did check v$sql_shared_cursor and all columns are 'N' for the address.

I searched the metalink bug database and we might be hitting one of the bugs. I didn;t find anything which is exactly same as our situation, but here are some which is related.

"CURSOR_SHARING= FORCE IS BROKEN IF A QUERY HAS A NVL OR DECODE PREDICATE " - Bug - 3818541, Product Version 9.2.0.5.0

"CURSOR WITH BIND VARIABLE IS NOT BEING SHARED FROM JDBC THIN DRIVER " - Bug - 3336803, O/S Solaris , Product Version 9.2.0.4

I will raise a SR with Oracle support.

Thanks again!
bipul

review

sven, April 08, 2005 - 6:18 am UTC

Hi Tom,
Under which conditions will invalidation of shared cursor object result in new child cursor (as seen in v$sql_shared_cursor)?
I have tried to reproduce this case using a stored procedure with simple select stmt. and causing invalidation of object in lib. cache by statistics change but I didn't get any child cursors. I can see invalidation of cursor in v$sql but no child cursors.
Any idea?

ref:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:9497064796920#15934669678128 <code>


Thanks,

sven

Tom Kyte
April 08, 2005 - 8:18 am UTC

in hindsight, it is more likely it was this above issue. the invalidations should not be the cause of multiple child cursors.

Executions in v$sqlarea

Dennis, May 11, 2005 - 4:59 am UTC

Hi Tom,

The executions column in v$sqlarea what does this signify.

1.Does it give the number of executions of the sql statement from the time the
database was started.
OR
2. Is it the total number of executions for all the child cursors for that point in time when it is queried.
OR
3. If the value is say 37, can we say that the SQL was executed 37 times in the day.

How should this value be interpreted. Please clarify.

many thanks ,
Dennis


Tom Kyte
May 11, 2005 - 7:36 am UTC

1) from startup

if the value is 37, you can say the query was executed 37 times since the database instance was started.

Dennis, May 12, 2005 - 5:43 am UTC

Tom,

I was of the opinion that the value gets reset to zero
if the SQL is flushed from the shared pool and the value
will increase only if the same SQL is executed with an
exact version existing in shared pool.

Given that v$sqlarea will give executions from database
statup and the database was started one month back
how do we calculate number of executions of a SQL in
one day.

Is there any method to get this

If I take a statspack report will the "top SQLs based on executions" report give me executions of the SQL between the two snap ids or this one also gives me no. of executions of the SQL from database startup time.

thanks,
Dennis







Tom Kyte
May 12, 2005 - 8:00 am UTC

well, it will -- you didn't ask that question.  The "query" to me is "the query", if the plans get invalidated or flushed -- they are gone and that "query" no longer exists.  


ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2> select * from t look_for_me;
 
no rows selected
 
ops$tkyte@ORA9IR2> select * from t look_for_me;
 
no rows selected
 
ops$tkyte@ORA9IR2> select sql_text, executions from v$sql where sql_text = 'select * from t look_for_me';
 
SQL_TEXT                                 EXECUTIONS
---------------------------------------- ----------
select * from t look_for_me                       2
 
ops$tkyte@ORA9IR2> create index t_idx on t(x);
 
Index created.
 
ops$tkyte@ORA9IR2> select sql_text, executions from v$sql where sql_text = 'select * from t look_for_me';
 
SQL_TEXT                                 EXECUTIONS
---------------------------------------- ----------
select * from t look_for_me                       0
 
ops$tkyte@ORA9IR2> select * from t look_for_me;
 
no rows selected
 
ops$tkyte@ORA9IR2> select sql_text, executions from v$sql where sql_text = 'select * from t look_for_me';
 
SQL_TEXT                                 EXECUTIONS
---------------------------------------- ----------
select * from t look_for_me                       1
 


 

Dennis, May 13, 2005 - 1:07 am UTC

Thanks Tom .



Hard Parsing

A reader, March 31, 2006 - 1:58 pm UTC

From statspack report, it shows a lot of hard parsing:

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 116,196.48 1,675.38
Logical reads: 11,635.91 167.77
Block changes: 694.61 10.02
Physical reads: 551.32 7.95
Physical writes: 125.72 1.81
User calls: 7,036.20 101.45
Parses: 1,656.19 23.88
Hard parses: 166.67 2.40
Sorts: 49.13 0.71
Logons: 3.75 0.05
Executes: 2,717.74 39.19
Transactions: 69.36

% Blocks changed per Read: 5.97 Recursive Call %: 29.68
Rollback per transaction %: 0.36 Rows per Sort: 12.90

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 95.26 In-memory Sort %: 100.00
Library Hit %: 98.84 Soft Parse %: 89.94
Execute to Parse %: 39.06 Latch Hit %: 99.14
Parse CPU to Parse Elapsd %: 86.86 % Non-Parse CPU: 96.28

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 44.67 45.01
% SQL with executions>1: 81.77 80.30
% Memory for SQL w/exec>1: 85.74 83.98

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 7,527 64.69
db file sequential read 973,366 1,422 12.22
global cache cr request 1,293,723 1,206 10.36
log file sync 125,091 621 5.34
SQL*Net break/reset to client 613,830 215 1.85
-------------------------------------------------------------

However we are not able to understand why?

If we are doing such a huge hard parsing, isn't that our shared pool would be filled-up with all these "new" SQLs which are getting hard parsed, but we find shared pool a lot of free space.

Also, from the figure "% Non-Parse CPU: 96.28" - does that mean total parsing (hard and soft) toook 3.72% of CPU used?



Tom Kyte
March 31, 2006 - 3:00 pm UTC

you cannot understand why?

It means you are generating lots of unique sql.

and we age them out.


you are not using binds, you are building unique queries, you are never reusing them.

Hard Parsing

A reader, March 31, 2006 - 4:36 pm UTC

It means you are generating lots of unique sql.
and we age them out.
>> so I should be able to see them in v$sql. Right? But in there, ALL are unique. There is nothing in v$sql_shared_cursor as well. We also have shared_pool free about 60%. Also I can see in v$sql the queries which I ran about 3 days ago and were run only once which means SQLs have not been aged out.

you are not using binds, you are building unique queries, you are never reusing them.
>>We have CURSOR_SHARING=SIMILAR


Tom Kyte
March 31, 2006 - 4:46 pm UTC

no - not really - they are coming and going - you are not reusing them.

cursor_sharing similar does not mean you will reduce the number of unique sql's at all. It means you might, but by no means you will


You are not using binds.

Hard Parsing

A reader, March 31, 2006 - 5:20 pm UTC

Or is it possible that most of these "hard parses" are really because of parse failures? (someone pointed it out)

Total Per Second
parse count (failures) 299,616 166.6
parse count (hard) 299,676 166.7
parse count (total) 2,977,830 1,656.2

Number of hard parses are very close to number of failed parses.


Tom Kyte
March 31, 2006 - 5:28 pm UTC

yes, could well be. in fact, they are.

that's a lot of truly bad sql doing nothing for you - except of course using your cpu and preventing others from doing their job :)

that would explain why you don't see them either. You would think that an application that does 166 of them a second would have been reported to the help desk, probably just ignores the error and continues on...

Hard Parsing

A reader, March 31, 2006 - 5:35 pm UTC

>>that's a lot of truly bad sql doing nothing for you - except of course using your cpu and preventing others from doing their job :)

Just to put it in perspective - all these parse failures occur at semantic stage and I think whatever oracle does till syntax checking and symantic checking stage is really equivalent of "soft parse", bcoz it never went to the "optimization" and 'row generation" process. Maybe that is the reason even though we have such a high amount of hard parses (166 per second) , but we still don't see any latch free issue or library cachec contention and our parse-time CPU is less overall. Is this correct understanding?

We are now trying to figure out where these failed SQLs are coming from. Any idea to find out that?


Tom Kyte
March 31, 2006 - 6:58 pm UTC

it'll be hard to figure out where they are coming from - you could try a servererror trigger - but not sure if that would catch it.

You would expect the application to have been reporting it - or at least failing in some fashion.

Alberto Dell'Era, March 31, 2006 - 6:51 pm UTC

Maybe the application is a Java JDBC one that sets a Result Set to CONCUR_UPDATABLE "just in case" for a statement that happens to be non-updatable, and then doesn't actually try to update the cursor ?

The way the JDBC driver supports the updatability is by transforming the statement and injecting a rowid; if when parsing it gets a PARSE ERROR, it *silently* downgrades the cursor to "not updatable" (hence the app will not get any error). If the app doesn't actually update the cursor aftwerwards (ie it just reads it), it will experience no error.

Real trace modified for privacy:

=====================
PARSE ERROR #1:len=116 dep=0 uid=50 oct=3 lid=50 tim=1096082726727714 err=936
select rowid, DISTINCT m.* FROM XXX m WHERE ...
WAIT #1: nam='SQL*Net break/reset to client' ela= 3 p1=675562835 p2=1 p3=0
WAIT #1: nam='SQL*Net break/reset to client' ela= 371 p1=675562835 p2=0 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=675562835 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 399 p1=675562835 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=108 dep=0 uid=50 oct=3 lid=50 tim=1096082726731009 hv=1973493066 ad='8a157ec8'
SELECT DISTINCT m.* FROM XXX m WHERE ...
END OF STMT
PARSE #1:c=0,e=1957,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1096082726731004

The driver catches the PARSE ERROR, downgrades the cursor, and issues the second (succesfull) PARSE with the original stmt.

So you'll get a failed parse for each parse (as "A reader" experienced):

>parse count (failures) 299,616 166.6
>parse count (hard) 299,676 166.7

Tom Kyte
March 31, 2006 - 7:28 pm UTC

ouch, that hurts....

but thanks for the input - that would do it, yes.

Hard Parsing

A reader, April 03, 2006 - 10:48 am UTC

Thanks Alberto.

We are able to track the query to be :
PARSE ERROR #3:len=<......> err=918
SELECT <col> from <tab1>, <tab2> where <JOIN CONDITION> and <tab2>.<col>=:"SYS_B_0"

and it fails with ORA-918 error.

No we are trying to figure out the significance of this invalid sql as related to the application.

very imports

question, August 16, 2006 - 3:36 pm UTC

why have two different database versions
one 8.i(database1) and other 10.g (database2 , database3). We have lot procobol program written in 8.i database , I am unable to compile the procobol objects in 9.i so we thought of stop grading to that databse and moved further for other datbases . Some of packages from datbase1 were calling from database2 and datbase3 vice versa.
we are getting the below error
ORA-06553: PLS-908: The stored format of databse1.package1@database_link1.WORLD is not supported by this release

How do you resolve these kind of errors

sorry for posting this error here

Thanks for the help

Tom Kyte
August 16, 2006 - 4:03 pm UTC

please utilize support - fairly confusing to follow. 8i and 10g, then all of a sudden 9i.

You should be able to compile procobol - we can get that fixed.
As for the other issue - no idea, I'm not sure of your environment here at all.


question

question, August 17, 2006 - 9:44 am UTC

I am sorry for the confusion . Actually i thought that we are calling the packages from 10.g database . we are call them from 9.i

Actually some databases we migrated to 9.i from 8.i and some where in still 8.i

In 9.i migrated databse if you are calling the 8.i procedure we are getting
ORA-06553: PLS-908: The stored format of databse1.package1@database_link1.WORLD
is not supported by this release . Ho do you resolve this error

Could you please hel us

Tom Kyte
August 17, 2006 - 10:09 am UTC

please utilize support, they will collect all of the necessary information in a structured format in order to assist you.



Why?

Sai, September 29, 2006 - 3:03 am UTC

Tom,

Why Oracle spawns child cursors based on different bind lengths, what is the benefit in doing so?


It makes sense to spawn child cursors due to optimizer_mismatch, outline_mismatch,...etc.

Tom Kyte
September 29, 2006 - 8:03 am UTC

the binds are part of the cursor space.
the size of the binds could affect the plan.

and it just does, now we know, and we can use that knowledge to program it correctly.

Re: Why

Sai, September 30, 2006 - 3:15 pm UTC

Thanks Tom.

Is there anyway to prove that change in bind value length generating a different execution plan, any example?

Thanks.

How to find hash_plan_value for child cursors?

Kim Anthonisen, January 10, 2007 - 8:29 am UTC

Hi Tom

(Oracle 9.2)

In v$sql, the hash_plan_value gets set to 0 for child cursors. Do You know where I can find the value or similar information?

Best regards,
Kim

so what to do if the v$sql_shared_cursor.bind_mismatch='Y' ?

Anand, January 23, 2007 - 12:17 pm UTC

Hi Tom,

We have recently experienced a perf impact in our database and it got resolved after setting the parameter _optim_peek_user_binds to false. Now i am finding few sqls with high version counts.
I queried v$sql_shared_cursor and from thw awr ordered by version count found that few queries with bind_mismatch='Y' and USER_BIND_PEEK_MISMATCH='Y'

Now what to do to reduce the high version count,

+ Do we need to reduce/increase the size of the bind data column. so that only one version of the cursor will be in the shared pool ?? If i am wrong, what we have to do to decrease the version count?

+ what is the impact of this with cursor sharing set to exact,force or similar, can u pls explain in detail.

Thanks in advance,
Anand.

Builded a test case for the above update

Anand, January 24, 2007 - 1:55 am UTC



SQL ordered by Version Count
Version Count Executions SQL Id SQL Module SQL Text
134 856 bwbjy68ht86q5 PGIT6_01 SELECT DNSH_SYS_ID , DNSH_FIE...
==========================================================================================

bwbjy68ht86q5
SELECT DNSH_SYS_ID , DNSH_FIELD_SEPARATOR
FROM PGIM_DOC_NUMBER_SETUP_HDR
WHERE DNSH_DS_TYPE = :1
AND DNSH_DS_CODE_FM = :1
AND DNSH_COMP_CODE IS NULL
AND DNSH_DIVN_CODE IS NULL
AND DNSH_DEPT_CODE IS NULL
AND :1 BETWEEN NVL(DNSH_EFF_FM_DT , :1 ) AND NVL(DNSH_EFF_TO_DT , :1 )
==========================================================================================

SQL> select * from v$sql_bind_metadata where address='000000051CBE09F0';

ADDRESS POSITION DATATYPE MAX_LENGTH ARRAY_LEN BIND_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
000000051CBE09F0 5 12 7 0 1
000000051CBE09F0 4 12 7 0 1
000000051CBE09F0 3 12 7 0 1
000000051CBE09F0 2 1 32 0 1
000000051CBE09F0 1 1 32 0 1
==========================================================================================

SQL> select VALUE_STRING from v$sql_bind_capture where sql_id='bwbjy68ht86q5';

VALUE_STRING
--------------------------------------------------------------------------------
8
DOC-MOT-001
01/24/07 10:55:56
01/24/07 10:55:56
01/24/07 10:55:56

8
DOC-MOT-001
01/24/07 10:53:07
01/24/07 10:53:07
01/24/07 10:53:07

8
DOC-MOT-001
01/24/07 10:48:03
01/24/07 10:48:03
01/24/07 10:48:03

8
DOC-MIS-074
01/24/07 10:59:10
01/24/07 10:59:10
01/24/07 10:59:10

==========================================================================================
SQL> select distinct address,count(*) from v$sql_shared_cursor
2 where sql_id='bwbjy68ht86q5' group by address;

ADDRESS COUNT(*)
---------------- ----------
000000051CBE1920 17
==========================================================================================

SQL> column sql_text format a30
SQL> column address new_val ADDR
SQL> select parsing_user_id puid, parsing_schema_id psid,
2 sql_text, address, child_address
3 from v$sql
4 where sql_id='bwbjy68ht86q5'
5 /

PUID PSID SQL_TEXT ADDRESS CHILD_ADDRESS
---------- ---------- ------------------------------ ---------------- ----------------
41 41 SELECT DNSH_SYS_ID , DNSH_FIEL 000000051CBE1920 000000051CBE09F0
D_SEPARATOR FROM PGIM_DOC_NUMB
ER_SETUP_HDR WHERE DNSH_DS_T
YPE = :1 AND DNSH_DS_CODE_FM
= :1 AND DNSH_COMP_CODE IS
NULL AND DNSH_DIVN_CODE IS N
ULL AND DNSH_DEPT_CODE IS NU
LL AND :1 BETWEEN NVL(DNSH_E
FF_FM_DT , :1 ) AND NVL(DNSH_
EFF_TO_DT , :1 )

41 41 SELECT DNSH_SYS_ID , DNSH_FIEL 000000051CBE1920 0000000505674108
D_SEPARATOR FROM PGIM_DOC_NUMB
ER_SETUP_HDR WHERE DNSH_DS_T
YPE = :1 AND DNSH_DS_CODE_FM
= :1 AND DNSH_COMP_CODE IS
NULL AND DNSH_DIVN_CODE IS N
ULL AND DNSH_DEPT_CODE IS NU
LL AND :1 BETWEEN NVL(DNSH_E
FF_FM_DT , :1 ) AND NVL(DNSH_
EFF_TO_DT , :1 )
.
.
.
.
.


17 rows selected.

==========================================================================================

SQL> select *
2 from v$sql_shared_cursor
3 where ADDRESS = '&ADDR'
4 /
old 3: where ADDRESS = '&ADDR'
new 3: where ADDRESS = '000000051CBE1920'

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
- - - - - - - -
bwbjy68ht86q5 000000051CBE1920 000000051CBE09F0 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

bwbjy68ht86q5 000000051CBE1920 0000000505674108 1 N N 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

bwbjy68ht86q5 000000051CBE1920 00000004CD7A0378 2 N N 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 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

bwbjy68ht86q5 000000051CBE1920 00000004C23D7330 3 N N 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 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

bwbjy68ht86q5 000000051CBE1920 000000050BB7CD08 8 N N 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 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

bwbjy68ht86q5 000000051CBE1920 00000004EE774EE0 13 N N 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 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

bwbjy68ht86q5 000000051CBE1920 00000005166A01B8 14 N N 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 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

bwbjy68ht86q5 000000051CBE1920 00000004CD0F8940 16 N N 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 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

bwbjy68ht86q5 000000051CBE1920 00000004F693E5E0 18 N N 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 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

bwbjy68ht86q5 000000051CBE1920 00000004FB692130 19 N N 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 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

bwbjy68ht86q5 000000051CBE1920 0000000505561138 21 N N 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 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

bwbjy68ht86q5 000000051CBE1920 00000004DD1B2830 23 N N 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 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

bwbjy68ht86q5 000000051CBE1920 00000004C8E9AC40 26 N N 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 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

bwbjy68ht86q5 000000051CBE1920 0000000510C20DA8 28 N N 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 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

bwbjy68ht86q5 000000051CBE1920 000000052755DAD8 29 N N 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 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

bwbjy68ht86q5 000000051CBE1920 000000050BF947C0 30 N N 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 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

bwbjy68ht86q5 000000051CBE1920 00000004C8C93C60 31 N N 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 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


17 rows selected.

Bind Variabled

A reader, February 27, 2007 - 10:51 am UTC

my application uses .NET front-end and OleDb Driver and we use bind variables still DBA Views show bind variables being used for same sql sometimes & sometimes without bind variables
please tell all possible reasons for the same
Tom Kyte
February 27, 2007 - 11:06 am UTC

the possible reason:

you are not using bind variables all of the time. you have bugs in the code.

or maybe your odbc drivers are rewriting your sql and unbinding on you. trace your odbc stuff.

What is SQL ordered by Version Count?

Nikhil, March 12, 2007 - 6:11 am UTC

Hello Tom,
What is SQL ordered by Version Count?
If 'SQL ordered by Version Count' anyway related to CUSRO_SHARING = FORCE?

Thanks

Tom Kyte
March 12, 2007 - 9:05 pm UTC

it is the sql ordered by the number of "versions" that appear.

select * from t;


that could be in the shared pool 100 times (version count = 100) because:

a) there are 100 users, they all own a table T, so when each issues select * from t, they really mean "select * from user1.t", 'select * from user2.t' and so on.... they are not shareable.

b) there could be bind mismatches (I bind a number, database column is a string, string compared to number is "to_number(string) compared to number" so an index on that string column cannot be considered. You bind a string, string compared to string is OK for an index - different possible plans)

c) there could be optimizer differences...

d) any one of the v$sql_shared_cursor columns could differ


.... If 'SQL ordered by Version Count' anyway related to CUSRO_SHARING = FORCE? ...

not really, well, maybe. cursor_sharing-force could cause some query (that never existed before) to have a high version count because of a, b, c, d, .... above.

but it probably won't LOWER any, if that is what you mean.

More info, more questions,

Kim, March 16, 2007 - 6:22 am UTC

Hi again

We currently have 10 sqls in v$sql (parsing user is our application user), which all has between 300.000 and 1.500.000 executions. And they all have executions < parse_calls +10.
(And parse_calls are <= executions)

From v$sqlarea, I can see that they have from 1 to 3 versions.

From v$sql_shared_cursor, I can see only N's.

What could/should I look at next?

Br
Kim
Tom Kyte
March 17, 2007 - 2:46 pm UTC

just 3 versions? not a big deal - if you just have 10 statements we are talking about. This is normal for such a high volume sql statement. It happens - and it is not really impacting you in any measurable way.

work on reducing their parse calls, that'll be the fruit that is low hanging here.

Question

Nikhil, April 04, 2007 - 10:45 am UTC

Hello Sir,
To my question regarding "SQL ordered by Version Count" you had replied
<<
Followup:
it is the sql ordered by the number of "versions" that appear.

select * from t;


that could be in the shared pool 100 times (version count = 100) because:

a) there are 100 users, they all own a table T, so when each issues select * from t, they really mean "select * from user1.t", 'select * from user2.t' and so on.... they are not shareable.

b) there could be bind mismatches (I bind a number, database column is a string, string compared to number is "to_number(string) compared to number" so an index on that string column cannot be considered. You bind a string, string compared to string is OK for an index - different possible plans)

c) there could be optimizer differences...

d) any one of the v$sql_shared_cursor columns could differ


.... If 'SQL ordered by Version Count' anyway related to CUSRO_SHARING = FORCE? ...

not really, well, maybe. cursor_sharing-force could cause some query (that never existed before) to have a high version count because of a, b, c, d, .... above.

but it probably won't LOWER any, if that is what you mean.
>>

What do you mean by there could be optimizer differences?

Thanks



Tom Kyte
April 04, 2007 - 11:31 am UTC

first_rows versus all_rows

sort_area_size =64k versus sort_area_size = 128k


any optimizer related parameters that are set different.

v$ view in Oracle docs

A reader, April 13, 2007 - 10:20 am UTC

Which Oracle documentations should I read to get understanding what the various dynamic performance view are get in use when Oracle do parsing, bind, optimization, transformation.

Thanks.

What does language_mismatch mean?

A reader, August 02, 2007 - 10:23 pm UTC

Tom,
What does language_mismatch mean in V$_SQL_SHARED_CUROSR as the reason for multiple versions? Is it because each session is trying to change NLS_LANG settings or something else? Database version is 10.2.0.3.
Thanks

Tom Kyte
August 05, 2007 - 1:06 pm UTC

means they have different languages set - yes (and that causes different NLS_SORTS and so on...).


$ cat test.sh
#!/bin/bash -vx

export NLS_LANG=
sqlplus / <<EOF
set echo on
drop table t;

create table t as select * from all_users;
create index t_idx on t(username);
exec dbms_stats.gather_table_stats(user,'T');

select * from nls_session_parameters;
@at
select /*+ first_rows(1) */ * from t order by username;
set autotrace off
EOF

export NLS_LANG=GERMAN
sqlplus / <<EOF
set echo on
select * from nls_session_parameters;
@at
select /*+ first_rows(1) */ * from t order by username;
set autotrace off
EOF

export NLS_LANG=
sqlplus / <<EOF
set echo on
column address new_val addr
select parsing_user_id puid, parsing_schema_id psid,
       sql_text, address, child_address
  from v\$sql
 where sql_text = 'select /*+ first_rows(1) */ * from t order by username';
/

select LANGUAGE_MISMATCH
  from v\$sql_shared_cursor
 where address = '&ADDR'
/
EOF




take a script like that and you'll see output like this (on my american system anyway :) )

[tkyte@dellpe ~]$ sh test.sh

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Aug 5 12:54:11 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2>
Table dropped.

ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2>
Table created.

ops$tkyte%ORA10GR2>
Index created.

ops$tkyte%ORA10GR2>
PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2>
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 rows selected.

ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2>
Execution Plan
----------------------------------------------------------
Plan hash value: 3778778741

------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU
------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    18 |     2   (0
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    37 |   666 |     2   (0
|   2 |   INDEX FULL SCAN           | T_IDX |     1 |       |     1   (0
------------------------------------------------------------------------

ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL*Plus: Release 10.2.0.2.0 - Production on So Aug 5 12:54:11 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2>
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   GERMAN
NLS_TERRITORY                  GERMANY
NLS_CURRENCY                   \uffff
NLS_ISO_CURRENCY               GERMANY
NLS_NUMERIC_CHARACTERS         ,.
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD.MM.RR
NLS_DATE_LANGUAGE              GERMAN
NLS_SORT                       GERMAN
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              \uffff
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 rows selected.

ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2>
Execution Plan
----------------------------------------------------------
Plan hash value: 961378228

------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    37 |   666 |     4  (25)| 00:00:0
|   1 |  SORT ORDER BY     |      |    37 |   666 |     4  (25)| 00:00:0
|   2 |   TABLE ACCESS FULL| T    |    37 |   666 |     3   (0)| 00:00:0
------------------------------------------------------------------------

ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Aug 5 12:54:11 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2>   2    3    4    5
      PUID       PSID
---------- ----------
SQL_TEXT
-------------------------------------------------------------------------------
ADDRESS  CHILD_AD
-------- --------
       215        215
select /*+ first_rows(1) */ * from t order by username
37E4DE50 37E4DD6C

       215        215
select /*+ first_rows(1) */ * from t order by username
37E4DE50 37E456F0


ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2>   2    3    4  old   3:  where address = '&ADDR'
new   3:  where address = '37E4DE50'

L
-
N
Y

ops$tkyte%ORA10GR2> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[tkyte@dellpe ~]$

NLS_SORT got me too

Stew Ashton, August 05, 2007 - 1:42 pm UTC

Tom, I confirm this on my French Oracle 9iR2 system. I was expecting to use an index range scan to honor an ORDER BY without having to actually sort, but this only works when NLS_SORT is BINARY. So different values of NLS_SORT (which is implicitly changed by the other parameter you mentioned) definitely changed my plans.
Is there a "best" way to guarantee NLS_SORT being BINARY so we can count on index range scans for avoiding sorts? For the moment, I am setting it in my PL/SQL package.
Tom Kyte
August 05, 2007 - 2:19 pm UTC

but if you use binary - when you have french data - you get the wrong sort order.

are you SURE that is what you want????

French index ?

Gary, August 06, 2007 - 2:50 am UTC

If the system is entirely French, then having 'French' indexes is probably appropriate.

CREATE INDEX french_index ON employees (NLSSORT(employee_id, 'NLS_SORT=FRENCH'));

http://download.oracle.com/docs/cd/B10501_01/server.920/a96529/ch4.htm#1004626
Tom Kyte
August 06, 2007 - 11:51 am UTC

oh, yes, absolutely - I was just pointing out what the language mismatch was and why it happens.

Re: NLS_SORT and French index

Stew Ashton, August 06, 2007 - 4:00 am UTC

Thanks Tom and Gary,
This is a multinational company based in Paris, multi-lingual employee database. The search fields are limited to ASCII characters (no accents).
Whatever the proper sort order is, I still want the same sort order in the index and in the session, right?
Tom Kyte
August 06, 2007 - 11:56 am UTC

no, you want to use whatever your application dictates should be used.

If the sort order of the index is what the end user is expecting to see - then, sure.

If not, then no.


You can always use the nls_sort function as well (instead of session settings) if you want to always get a SPECIFIC sort order regardless of session settings.

NLS_SORT and French index followup

Stew Ashton, August 14, 2007 - 10:55 am UTC


Thanks again Tom. The application dictates high performance, scalability and pagination of result sets, so I want to satisfy WHERE and ORDER BY clauses with indexes.

Since I am using LIKE, I will have to stick with binary comparison and sorting as long as I am in 9i. Again, the data being compared and sorted contains only non-accented characters.

If I were in 10g, I could use 'LINGUISTIC' comparison with a linguistic index and multilingual sorting to support accented characters, even with LIKE.

To guarantee use of the indexes, I will use a logon trigger to set NLS_COMP and NLS_SORT appropriately.

about UACS

A reader, September 06, 2007 - 4:07 am UTC

Hello,Mr. Tom¿

V$SQL_SHARED_CURSOR

BIND_UACS_DIFF
VARCHAR2(1)

I can not get more details about the UACs of column BIND_UACS_DIFF .
Below is from oracle DOC:
One cursor has bind UACs and one does not

Could you kindly give me a explain on UACs?
Thanks!
Alan

Hard Parse

Reader, March 03, 2008 - 9:02 pm UTC

I have been directed to this post by many so I would appreciate your help if you have the time --

select count(*),sql_id,sql_text from v$sql group by sql_id,sql_text order by 1 desc

40 1swxb9zt8qscm begin wwv_flow.g_computation_result_vc := UPPER(:P34_BRANCH_CODE); end;

select * from v$vpd_policy where sql_id = '1swxb9zt8qscm'

No rows selected

select bind_mismatch from v$sql_shared_cursor where sql_id = '1swxb9zt8qscm'

Y
Y
.
.
.
.

select address,position,datatype,max_length,bind_name,array_len from v$sql_bind_metadata where address in
( select child_address from v$sql where sql_id='1swxb9zt8qscm')
ADDRESS POSITION DATATYPE MAX_LENGTH BIND_NAME ARRAY_LEN
---------------- -------- -------- ---------- ------------------------------ ---------
00000004A1BBD618 1 1 32512 P34_BRANCH_CODE 0
00000004A152A9A8 1 1 32512 P34_BRANCH_CODE 0

40 rows selected

Can you suggest where should we start to fix this problem ?

Thanks



Tom Kyte
March 03, 2008 - 9:42 pm UTC

can you check all of the columns, you can have multiple Y's per row

BIND_MISMATCH

Reader, March 03, 2008 - 11:39 pm UTC

I checked v$sql_shared_cursor and only bind_mismatch has 'Y'

Appreciate your help.

Thanks
Tom Kyte
March 04, 2008 - 7:19 am UTC

show me via v$sql how many child cursors there are, their executions, their current status (are some invalidated).

TOP_level_RPI_cursor missmatch

Kothai, August 03, 2009 - 5:53 am UTC

SQL> select sql_id, mismatch, count(*) from (select sql_id,UNBOUND_CURSOR||SQL_TYPE_MISMATCH||OPTIMIZER_MISMATCH||OUTLINE_MISMATCH||
  2  STATS_ROW_MISMATCH||LITERAL_MISMATCH||SEC_DEPTH_MISMATCH||
  3  EXPLAIN_PLAN_CURSOR||BUFFERED_DML_MISMATCH||PDML_ENV_MISMATCH||INST_DRTLD_MISMATCH||
  4  SLAVE_QC_MISMATCH||TYPECHECK_MISMATCH||AUTH_CHECK_MISMATCH||BIND_MISMATCH||DESCRIBE_MISMATCH||
  5  LANGUAGE_MISMATCH||TRANSLATION_MISMATCH||ROW_LEVEL_SEC_MISMATCH||INSUFF_PRIVS||INSUFF_PRIVS_REM||
  6  REMOTE_TRANS_MISMATCH||LOGMINER_SESSION_MISMATCH||INCOMP_LTRL_MISMATCH||OVERLAP_TIME_MISMATCH||SQL_REDIRECT_MISMATCH||
  7  MV_QUERY_GEN_MISMATCH||USER_BIND_PEEK_MISMATCH||TYPCHK_DEP_MISMATCH||NO_TRIGGER_MISMATCH||
  8  FLASHBACK_CURSOR||ANYDATA_TRANSFORMATION||INCOMPLETE_CURSOR||TOP_LEVEL_RPI_CURSOR||DIFFERENT_LONG_LENGTH||
  9  LOGICAL_STANDBY_APPLY||DIFF_CALL_DURN||BIND_UACS_DIFF||BIND_UACS_DIFF as mismatch
 10  from v$sql_shared_cursor)
 11  where sql_id = '75f7hyfuqvxbu'
 12  group by sql_id, mismatch;

SQL_ID        MISMATCH                                  COUNT(*)
------------- --------------------------------------- ----------
75f7hyfuqvxbu NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN          2
75f7hyfuqvxbu NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNYY          7
75f7hyfuqvxbu NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNYNNNNN        185
75f7hyfuqvxbu NNNNYNNNNNNNNNNNNNNNNNNNNNNNNNNNNYNNNNN          4


SQL> select count(*) from v$sql where sql_id='75f7hyfuqvxbu';

  COUNT(*)
----------
      1501

SQL> select PARSING_SCHEMA_ID,PARSING_USER_ID,count(*) from v$sql where
address='07000001626763E8' group by PARSING_SCHEMA_ID,PARSING_USER_ID;

  2

PARSING_SCHEMA_ID PARSING_USER_ID   COUNT(*)
----------------- --------------- ----------
                0               0       1025
               22              22          1

Tom Kyte
August 04, 2009 - 1:51 pm UTC

very pretty. thanks?

px_mismatch,

A reader, April 28, 2011 - 3:53 pm UTC

Hello,

The SQL statement "select 1 from dual" is being executed multiple times by an application. We found that SQL_ID is having very high px_mismatch values from v$sql_shared_cursor.

SQL_ID PX_MISMATCH
------------- -----------
bunvx480ynf57 515

What is PX_MISMATCH? How to debug this problem? This is causing us ORA-00600 error.

Thanks,
Tom Kyte
April 29, 2011 - 8:00 am UTC

It has to do with parallel query - which seems strange, since it is dual.

Not saying it is not a problem, but, why would an application issue that statement ever? Why would it issue it frequently????

You'll need to file a service request with support for this one, it'll need some diagnosing.

j2ee

anonymous, May 05, 2011 - 1:22 am UTC

Is the application using j2ee application server ?

Very typically they are configured so that every time they take a database connection from connection pool they first test if it still valid. And they use this statement to do that. They can also use other tables and in some cases this can be a real problem.
Tom Kyte
May 06, 2011 - 9:30 am UTC

I would write:

and in ALL cases this is a REAL problem.


Just yet another way to add an unnecessary large load of work.


and is definitely not limited to j2ee - it affects many connection pools

mismatch on ROLL_INVALID_MISMATCH

Mark, February 23, 2012 - 10:18 am UTC

I see the following:

LAST_LOAD_TIME = 2012-02-21/22:30:59
SQL_ID = fv69bg6qrsva5
CHILD_NUMBER = 0
ROLL_INVALID_MISMATCH = Y
--------------------------------------------------
LAST_LOAD_TIME = 2012-02-23/00:30:49
SQL_ID = fv69bg6qrsva5
CHILD_NUMBER = 1
ROLL_INVALID_MISMATCH = Y
--------------------------------------------------

Which tells me that the cursor invalidated as the result of 5 hour window after gather stats with cursor invalidation. What I'm not clear about is why did it keep child 0 and created child 1 instead of reparsing the whole parent/child and having new child 0? I assume that with this mismatch child 0 will never be used, so then why to keep it?

questionor, January 15, 2013 - 8:31 am UTC

Would the following query be shareable (i.e. one that has one constant that never changes (ie. hard coded) and bind variables or will any use of a literal (even if constant) be problematic?

ex.

Select * from sgbstdn where sgbstdn_stst_code = 'AS' and sgbstdn_pidm = :B1

As I mentioned, the 'AS' filter is constant and will always be sent as 'AS' by the application.

Please let me know if this query should be tuned to make the 'AS' a bind anyway.
Tom Kyte
January 15, 2013 - 2:22 pm UTC

that is shareable.


here is the rule:


bind only that which varies from execution to execution of a sql statement, literals are fine for everything else.

Bind peek every time?

Matt, May 03, 2013 - 1:14 pm UTC

We're moving from 10g to 11gR2 and have a legacy cobol routine with a fixed piece of SQL (although we can add hints), and we have bind peeking enabled. The program is run multiple times with different parameters.

The problem is our tables are partitioned with skewed datasets in each partition (based on employee id ranges). We get some iterations of the program run great, but occasionally the bind peeked plan doesn't suit a later iteration.

Is there any way to force bind peeking each time and extra versions of the same SQL plan, more suitable to each iteration of the program run?

Thanks
Tom Kyte
May 06, 2013 - 7:12 pm UTC

well, in 11gr2 you will have adaptive cursor sharing, but you also have sql plan management...

http://itnewscast.com/database/how-do-adaptive-cursor-sharing-and-sql-plan-management-interact


You might consider searching around on the former to see how that works (you'll get more than one plan with bind peeking...) and read up in the docs on the latter and consider using that...

Large number of child cursors (merge SQL) due to bind mismatch

Paul, March 07, 2022 - 5:25 am UTC

Greetings!

Database is on 19.11. and currently compatibility is on 12.1.0.2.

We have below SQL_ID : ghyd0kaht057b (merge statement) is not shareable and Oracle created 5000+ child cursors in the shared pool.

The reason for so many child cursors (non-sharable SQL) was due to BIND_MISMATCH and BIND_LENGTH_UPGRADEABLE.

We are asking Dev team to check the application on the Java side for bind mismatch.

Can you please provide some insights on this.

Question:
**********

1) On Database side, Is there any quick way to identify the bind position /column which is causing the Bind_mismatch and Bind_length_upgradeable issue ?

2) What specific place application dev team needs to check to address this issue on JDBC side?

   INST_ID SQL_ID        REASON_NAME                   R   COUNT(*)
---------- ------------- ----------------------------- - ----------
         3 ghyd0kaht057b BIND_MISMATCH                 Y       5296
         3 ghyd0kaht057b BIND_LENGTH_UPGRADEABLE       Y       2429

SQL_ID        SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
ghyd0kaht057b merge  into MSG.MU_Details using dual on (INSTR_ID=:1 ) when matched t
              hen update set valid=:2 ,cond_call_price=:3 ,cond_call_date=:4 ,refund_announcem
              ent_date=:5 ,nro_flag=:6 ,tax_credit_frequency=:7 ,issue_type_detail=:8 ,issue_k
              ey=:9 ,designated_termination_date=:10 ,secured=:11 ,sec_regulation=:12 ,called_
              redemption_type=:13 ,issue_id=:14 ,asset_claim_code=:15 ,inst_subtype=:16 ,purpo
              se_subclass=:17 ,purpose_class=:18 ,state_ind=:19 ,orig_cusip_status=:20 ,mu_s
              ecurity_type=:21 ,maturity_description_code=:22 ,orig_instrument_enhancement_ty=
              :23 ,backed_underlying_security_id=:24 ,refunding_INSTR_ID=:25 ,refund_date
              =:26 ,pac_bond_ind=:27 , mtg_insurance=:28 ,use_of_proceeds_supplementary=:29 ,u
              se_of_proceeds=:30 ,tax_credit_percent=:31 ,state_tax_status=:32 ,series_name=:3
              3 ,sale_date=:34 ,refunding_dated_date=:35 ,refund_price=:36 ,project_name=:37 ,
              other_enhancement_type=:38 ,other_enhancement_company=:39 ,mu_issue_type=:40 ,
              issue_text=:41 ,formal_award_date=:42 ,first_execution_date=:43 ,federal_tax_sta
              tus=:44 ,dtcc_status=:45 ,conduit_obligor_name=:46 ,capital_type=:47 ,bank_quali
              fied=:48 ,additional_project_text=:49 ,loaded_by=:50 ,last_updated=:51  when not
               matched then insert (valid,cond_call_price,cond_call_date,refund_announcement_d
              ate,nro_flag,tax_credit_frequency,issue_type_detail,issue_key,designated_termina
              tion_date,secured,sec_regulation,called_redemption_type,issue_id,asset_claim_cod
              e,inst_subtype,purpose_subclass,purpose_class,state_ind,orig_cusip_status,mu_s
              ecurity_type,maturity_description_code,orig_instrument_enhancement_ty,backed_und
              erlying_security_id,refunding_INSTR_ID,refund_date,pac_bond_ind, mtg_insura
              nce,use_of_proceeds_supplementary,use_of_proceeds,tax_credit_percent,state_tax_s
              tatus,series_name,sale_date,refunding_dated_date,refund_price,project_name,other
              _enhancement_type,other_enhancement_company,mu_issue_type,issue_text,formal_aw
              ard_date,first_execution_date,federal_tax_status,dtcc_status,conduit_obligor_nam
              e,capital_type,bank_qualified,additional_project_text,loaded_by,last_updated,ins
              trument_id) values (:52 ,:53 ,:54 ,:55 ,:56 ,:57 ,:58 ,:59 ,:60 ,:61 ,:62 ,:63 ,
              :64 ,:65 ,:66 ,:67 ,:68 ,:69 ,:70 ,:71 ,:72 ,:73 ,:74 ,:75 ,:76 ,:77 ,:78 ,:79 ,
              :80 ,:81 ,:82 ,:83 ,:84 ,:85 ,:86 ,:87 ,:88 ,:89 ,:90 ,:91 ,:92 ,:93 ,:94 ,:95 ,
              :96 ,:97 ,:98 ,:99 ,:100 ,:101 ,:102 )




Thanks!
Chris Saxon
March 08, 2022 - 4:20 pm UTC

Checking v$sql_bind_capture may help - this lists the data types associated with the values, so can give a clue as to when you'll get a new child cursor:

create table t (
  c1 varchar2(1000)
);

insert into t values ( 'test' );

var v varchar2(1000);
exec :v := 'test';
select * from t
where  c1 = :v;

exec :v := 'looooooooooonnnngeeer test';
select * from t
where  c1 = :v;

exec :v := rpad ( 'looooooooooonnnngeeer test', 1000, 't' );
select * from t
where  c1 = :v;

select child_number, bind_mismatch, bind_length_upgradeable
from   v$sql_shared_cursor
where  sql_id in (
  select sql_id from v$sql
  where  sql_text like 'select * from t where  c1 = :v'
);

CHILD_NUMBER B B
------------ - -
           0 N N
           1 N Y
           2 N Y

select child_number, name, datatype_string 
from   v$sql_bind_capture
where  sql_id in (
  select sql_id from v$sql
  where  sql_text like 'select * from t where  c1 = :v'
);

CHILD_NUMBER NAME   DATATYPE_STRING
------------ ------ ---------------
           2 :V     VARCHAR2(4000) 
           1 :V     VARCHAR2(128)  
           0 :V     VARCHAR2(32)  


Note this view only samples the bind values, so won't contain every different value.

It's worth checking with support too - I can see several bugs listed where bind mismatches are causing lots of child cursors.

Finally the query has 102 bind variables. With just two lengths for each variable, there are 2^102 = 5 * 10^30 combinations!

Large number of child cursors (merge SQL) due to bind mismatch

Paul, March 09, 2022 - 4:04 am UTC

Thanks Chris

multiple version count for the SQL_ID

Morgan, June 28, 2023 - 4:05 am UTC

We are seeing multiple child cursors for the same SQL_ID.
Reasons for child cursors count is shown as below: -

sql_id: akc08kgzvafyc
------------------------
- BIND_EQUIV_FAILURE count: 71
- ROLL_INVALID_MISMATCH count: 84
- LOAD_OPTIMIZER_STATS count: 1
- USE_FEEDBACK_STATS count: 36


Could you please explain the cause of this and how to fix this in the application.

Thank you!
Chris Saxon
June 28, 2023 - 12:41 pm UTC

BIND_EQUIV_FAILURE => The bind value's selectivity does not match that used to optimize the existing child cursor; i.e. the optimizer has different row estimates for different bind values. e.g. 1 row vs 1 million rows. With a big enough difference it's likely the optimizer should switch plan; this is the basis for adaptive cursor sharing.

ROLL_INVALID_MISMATCH => Marked for rolling invalidation and invalidation window exceeded. After gathering stats, by default the optimizer waits to invalidate cursors on the update tables. This is to prevent a "parsing storm" whereby all queries on a table are reoptimized the moment stats gathering is finished. This tells you that its been "too long" since the stats were gathered to use the original cursor.

LOAD_OPTIMIZER_STATS =>A hard parse is forced to initialize extended cursor sharing. This can happen due to "Lots of child cursors may be produced with adaptive cursor sharing enabled,
sometimes with overlapping or same bind value selectivity ranges" (MOS note 6644714.8).

USE_FEEDBACK_STATS => A hard parse is forced so that the optimizer can reoptimize the query with improved optimizer inputs (for example, cardinality estimates). This is due to statistics feedback https://blogs.oracle.com/optimizer/post/statistics-feedback-formerly-cardinality-feedback

For the most part these are expected reasons for the optimizer to generate a new child cursor. I wouldn't worry about these unless you're seeing lots of cursors generated in a short period of time and/or parsing is a problem on your system.

child cursors (merge SQL) due to bind mismatch

A reader, October 23, 2023 - 2:48 pm UTC

Sorry to bother you. We're plagued by bind_mismatch recently on oracle 19c, and we have dug whole internet found nothing about 'bind mismatch(19)'. Could you help us to explain what exactly does this mean? Thanks.
We have merge into sql in our app and using above method find all the reasons show below(which is not length upgrade nor other bind error):
<Chi1dNode>
<ChildNumber>107</ChildNumber>
<ID>39</ID>
<reason>Bind mismatch(19)<reason>
<size>4x8</size>
<bind position>0000004700000000 </bind_position>
<original_oacflg>0000000700000000</original_oacflg>
<original_oacfl2>0100001000000000</oiginal_oacfl2>
<new_oacf12>0100000000000000 </new_oacf12></chiidNode>
Connor McDonald
November 06, 2023 - 1:48 am UTC

There's a *lot* of potential reasons here, eg sql profiles, adaptive cursor sharing etc etc.

Your best bet here is to get a full "SQL test case", which is done via:

SQL> variable tc clob;
SQL> begin
  2    dbms_sqldiag.export_sql_testcase(
  3    directory=>'TEMP',
  4    sql_id=>'[your sql id]',
  5    testcase=>:tc);
  6  end;
  7  /


Log a call with Support and provide that. If you don't get any joy, come back here and we'll take a look

Re: Large number of child cursors (merge SQL) due to bind mismatch

Narendra, February 12, 2024 - 2:08 pm UTC

Hello Chris,

Has the behaviour changed in recent version of 19c that changes this behaviour? I tested your script in 19.20 and I can see only one cursor.

SQL> select version_full from v$instance ;

VERSION_FULL
-----------------
19.20.0.0.0

SQL> create table t (
  2    c1 varchar2(1000)
  3  );

Table created.

SQL> insert into t values ( 'test' );

1 row created.

SQL> commit ;

Commit complete.

SQL> var v varchar2(1000);
SQL> exec :v := 'test';

PL/SQL procedure successfully completed.

SQL> select * from t
  2  where  c1 = :v;

C1
--------------------------------------------------------------------------------
test

SQL> exec :v := 'looooooooooonnnngeeer test';

PL/SQL procedure successfully completed.

SQL> select * from t
  2  where  c1 = :v;

no rows selected

SQL> exec :v := rpad ( 'looooooooooonnnngeeer test', 1000, 't' );

PL/SQL procedure successfully completed.

SQL> select * from t
  2  where  c1 = :v;

no rows selected

SQL> select child_number, bind_mismatch, bind_length_upgradeable
  2  from   v$sql_shared_cursor
  3  where  sql_id in (
  4    select sql_id from v$sql
  5    where  sql_text like 'select * from t where  c1 = :v'
  6  );

CHILD_NUMBER B B
------------ - -
           0 N N

SQL>

Chris Saxon
February 19, 2024 - 5:49 pm UTC

Yes, I only see one cursor in 19.20 too - though when running the statement in different clients with different NLS settings the effect can kick in.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library