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
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.
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!
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
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
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
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 ?
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
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.
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?
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"
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
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?
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.
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 ...
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
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.
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
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
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.
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
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?
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
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
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
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
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
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
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
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
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?
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
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.
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?
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
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
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
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.
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
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
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
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
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.
April 13, 2007 - 2:11 pm UTC
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
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.
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
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?
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
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
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
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,
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.
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.
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
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!
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!
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>
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>
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.