Your examples and explanations are excellent.
April 30, 2003 - 4pm Central time zone
Reviewer: gs
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
Followup April 30, 2003 - 7pm Central time zone:
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
November 7, 2003 - 3pm Central time zone
Reviewer: Ashish S from New York, NY
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.
Followup November 7, 2003 - 5pm Central time zone:
yes. absolutely. that is one of the reasons for child cursors. 100%
Good
November 10, 2003 - 9am Central time zone
Reviewer: Ashish S from New York, NY
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!
Followup November 10, 2003 - 12pm Central time zone:
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 ?
February 25, 2004 - 10am Central time zone
Reviewer: Neil from UK
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,AD
DRESS,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
Followup February 25, 2004 - 11am Central time zone:
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 ?
February 25, 2004 - 1pm Central time zone
Reviewer: Alberto Dell'Era from Milan, Italy
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
Followup February 25, 2004 - 2pm Central time zone:
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
February 26, 2004 - 5am Central time zone
Reviewer: Neil campbell from UK
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
Followup February 26, 2004 - 10am Central time zone:
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 ??
February 26, 2004 - 5am Central time zone
Reviewer: Kevin from Ireland
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 ?
Followup February 26, 2004 - 10am Central time zone:
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
February 26, 2004 - 11am Central time zone
Reviewer: Neil Campbell from UK
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
Followup February 26, 2004 - 2pm Central time zone:
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
August 3, 2004 - 5pm Central time zone
Reviewer: push from NJ, USA
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.
Followup August 3, 2004 - 6pm Central time zone:
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
August 4, 2004 - 2pm Central time zone
Reviewer: Push from NJ, USA
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
October 16, 2004 - 10pm Central time zone
Reviewer: A reader
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?
Followup October 17, 2004 - 9am Central time zone:
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.
October 17, 2004 - 6pm Central time zone
Reviewer: A reader
"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"
Followup October 17, 2004 - 6pm Central time zone:
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.
October 17, 2004 - 7pm Central time zone
Reviewer: A reader
"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
Followup October 17, 2004 - 8pm Central time zone:
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
October 17, 2004 - 9pm Central time zone
Reviewer: A reader
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?
Followup October 18, 2004 - 7am Central time zone:
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?
October 27, 2004 - 9am Central time zone
Reviewer: Robert from Kansas City, MO
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.
Followup October 27, 2004 - 10am Central time zone:
give me an example query to play with.
Date of size 7???
October 27, 2004 - 10am Central time zone
Reviewer: Bob B from Albany, NY
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 ...
Followup October 27, 2004 - 12pm Central time zone:
we store
yy 1
yy 2
mm 3
dd 4
hh 5
mm 6
ss 7
in there.
RE: Date of Size 7
October 27, 2004 - 12pm Central time zone
Reviewer: Mark A. Williams from Indianapolis, IN USA
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
October 27, 2004 - 2pm Central time zone
Reviewer: Dan Malumphy from Cleveland, OH USA
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
Followup October 27, 2004 - 4pm Central time zone:
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
October 27, 2004 - 3pm Central time zone
Reviewer: Mark A. Williams from Indianapolis, IN USA
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
October 27, 2004 - 9pm Central time zone
Reviewer: Darrell from Orlando, FL
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.
Followup October 27, 2004 - 10pm Central time zone:
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
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://www.oracle.com/oramag
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
October 27, 2004 - 10pm Central time zone
Reviewer: A reader
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
Followup November 1, 2004 - 4am Central time zone:
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
October 27, 2004 - 10pm Central time zone
Reviewer: Robert from Kansas City, MO
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
October 27, 2004 - 10pm Central time zone
Reviewer: Mark A. Williams from Indianapolis, IN USA
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:
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
Re: Mark A Williams
October 27, 2004 - 10pm Central time zone
Reviewer: Menon
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
October 27, 2004 - 11pm Central time zone
Reviewer: Darrell from Orlando, FL
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
October 27, 2004 - 11pm Central time zone
Reviewer: Mark A. Williams from Indianapolis, IN USA
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
October 28, 2004 - 9am Central time zone
Reviewer: Darrell from Orlando, FL
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
November 1, 2004 - 1pm Central time zone
Reviewer: Robert Hayden from Kansas City, MO
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
Followup November 2, 2004 - 6am Central time zone:
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
November 2, 2004 - 9am Central time zone
Reviewer: Robert from Kansas City, MO
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.
Followup November 2, 2004 - 10am Central time zone:
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
November 2, 2004 - 2pm Central time zone
Reviewer: Robert from Kansas City, MO
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
January 20, 2005 - 7pm Central time zone
Reviewer: amit poddar from new haven, CT
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
Followup January 20, 2005 - 7pm Central time zone:
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
March 20, 2005 - 12pm Central time zone
Reviewer: A reader from NJ, USA
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?
Followup March 20, 2005 - 6pm Central time zone:
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
April 5, 2005 - 6am Central time zone
Reviewer: bipul
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
Followup April 5, 2005 - 7am Central time zone:
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
April 5, 2005 - 9am Central time zone
Reviewer: Bipul
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
Followup April 5, 2005 - 12pm Central time zone:
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
April 6, 2005 - 6am Central time zone
Reviewer: bipul
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
Followup April 6, 2005 - 9am Central time zone:
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
April 6, 2005 - 11am Central time zone
Reviewer: bipul
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
Followup April 6, 2005 - 2pm Central time zone:
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
April 7, 2005 - 9am Central time zone
Reviewer: bipul
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
Followup April 7, 2005 - 11am Central time zone:
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
April 7, 2005 - 12pm Central time zone
Reviewer: bipul
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
April 8, 2005 - 6am Central time zone
Reviewer: sven
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:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:9497064796920#15934669678128
Thanks,
sven
Followup April 8, 2005 - 8am Central time zone:
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
May 11, 2005 - 4am Central time zone
Reviewer: Dennis from India
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
Followup May 11, 2005 - 7am Central time zone:
1) from startup
if the value is 37, you can say the query was executed 37 times since the database instance was
started.
May 12, 2005 - 5am Central time zone
Reviewer: Dennis from India
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
Followup May 12, 2005 - 8am Central time zone:
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
May 13, 2005 - 1am Central time zone
Reviewer: Dennis from India
Thanks Tom .
Hard Parsing
March 31, 2006 - 1pm Central time zone
Reviewer: A reader
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?
Followup March 31, 2006 - 3pm Central time zone:
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
March 31, 2006 - 4pm Central time zone
Reviewer: A reader
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
Followup March 31, 2006 - 4pm Central time zone:
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
March 31, 2006 - 5pm Central time zone
Reviewer: A reader
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.
Followup March 31, 2006 - 5pm Central time zone:
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
March 31, 2006 - 5pm Central time zone
Reviewer: A reader
>>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?
Followup March 31, 2006 - 6pm Central time zone:
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.
March 31, 2006 - 6pm Central time zone
Reviewer: Alberto Dell'Era from Milan, Italy
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
Followup March 31, 2006 - 7pm Central time zone:
ouch, that hurts....
but thanks for the input - that would do it, yes.
Hard Parsing
April 3, 2006 - 10am Central time zone
Reviewer: A reader
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
August 16, 2006 - 3pm Central time zone
Reviewer: question from uk
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
Followup August 16, 2006 - 4pm Central time zone:
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
August 17, 2006 - 9am Central time zone
Reviewer: question
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
Followup August 17, 2006 - 10am Central time zone:
please utilize support, they will collect all of the necessary information in a structured format
in order to assist you.
Why?
September 29, 2006 - 3am Central time zone
Reviewer: Sai from San Jose, CA, USA
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.
Followup September 29, 2006 - 8am Central time zone:
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
September 30, 2006 - 3pm Central time zone
Reviewer: Sai from San Jose, CA, USA
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?
January 10, 2007 - 8am Central time zone
Reviewer: Kim Anthonisen from Denmark
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' ?
January 23, 2007 - 12pm Central time zone
Reviewer: Anand from India
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
January 24, 2007 - 1am Central time zone
Reviewer: Anand from India
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
February 27, 2007 - 10am Central time zone
Reviewer: A reader
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
Followup February 27, 2007 - 11am Central time zone:
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?
March 12, 2007 - 6am Central time zone
Reviewer: Nikhil from India
Hello Tom,
What is SQL ordered by Version Count?
If 'SQL ordered by Version Count' anyway related to CUSRO_SHARING = FORCE?
Thanks
Followup March 12, 2007 - 9pm Central time zone:
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,
March 16, 2007 - 6am Central time zone
Reviewer: Kim
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
Followup March 17, 2007 - 2pm Central time zone:
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
April 4, 2007 - 10am Central time zone
Reviewer: Nikhil
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
Followup April 4, 2007 - 11am Central time zone:
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
April 13, 2007 - 10am Central time zone
Reviewer: A reader
Which Oracle documentations should I read to get understanding what the various dynamic performance view are get in use when Oracle do parsing, bind, optimization, transformation.
Thanks.
What does language_mismatch mean?
August 2, 2007 - 10pm Central time zone
Reviewer: A reader
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
Followup August 5, 2007 - 1pm Central time zone:
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
August 5, 2007 - 1pm Central time zone
Reviewer: Stew Ashton from Paris, France
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.
Followup August 5, 2007 - 2pm Central time zone:
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 ?
August 6, 2007 - 2am Central time zone
Reviewer: Gary from Sydney, Aus
If the system is entirely French, then having 'French' indexes is probably appropriate.
CREATE INDEX french_index ON employees (NLSSORT(employee_id, 'NLS_SORT=FRENCH'));
http://download.oracle.com/docs/cd/B10501_01/server.920/a96529/ch4.htm#1004626
Followup August 6, 2007 - 11am Central time zone:
oh, yes, absolutely - I was just pointing out what the language mismatch was and why it happens.
Re: NLS_SORT and French index
August 6, 2007 - 4am Central time zone
Reviewer: Stew Ashton from Paris, France
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?
Followup August 6, 2007 - 11am Central time zone:
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
August 14, 2007 - 10am Central time zone
Reviewer: Stew Ashton from Paris, France
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
September 6, 2007 - 4am Central time zone
Reviewer: A reader
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
March 3, 2008 - 9pm Central time zone
Reviewer: Reader from US
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
Followup March 3, 2008 - 9pm Central time zone:
can you check all of the columns, you can have multiple Y's per row
BIND_MISMATCH
March 3, 2008 - 11pm Central time zone
Reviewer: Reader from US
I checked v$sql_shared_cursor and only bind_mismatch has 'Y'
Appreciate your help.
Thanks
Followup March 4, 2008 - 7am Central time zone:
show me via v$sql how many child cursors there are, their executions, their current status (are some invalidated).
TOP_level_RPI_cursor missmatch
August 3, 2009 - 5am Central time zone
Reviewer: Kothai from India
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_R
EDIRECT_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_LE
NGTH||
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
Followup August 4, 2009 - 1pm Central time zone:
very pretty. thanks?
|