RE: Re : High Version Count with usage of Parallel Query
- From: "Oliver Jost" <Oliver.Jost@xxxxxxxxxxxxxx>
- To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 7 Jul 2010 11:49:16 -0400
I've seen this as well... inconsistently for about 4 queries (to excess)
on my environment. I've raised a number of tars on this issue with
Oracle and it has not been successfully remedied yet. Generally
speaking, I see the behaviour Tanel has outlined with the exception that
it seems to only occur for pure numerical binds. I've seen some queries
go over 2000 on the version count. I have not found a resolution as of
yet. One 'fix' ended in core dumps and ora-0600 errors (every 3 mins or
so). It was a system level event setting which was to allow a maximum
length on all binds (a bug in earlier releases, I'm on 10.2.0.4)...
since it was fixed in our release already it managed to 'undo' the fix
and throw the error (now I have a bug being registered). I have my
suspicions as to how this is occurring but have not successfully
replicated in our test environment (packages calling the same query with
non-standardized parameters).
Thanks,
Oliver
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tanel Poder
Sent: Wednesday, July 07, 2010 8:25 AM
To: Oracle-L Freelists
Subject: Re : High Version Count with usage of Parallel Query
It looks what's happening is the fairly classic case of non-sharing of
cursors due mismatching bind metadata. The PX engine further amplifies
this issue and adds these funky-named reasons into v$sql_shared_cursor.
You could compare the bind metadata (especially the max varchar2
lengths) in V$SQL_BIND_METADATA for your different cursors.
To reproduce this issue on your test environment, you'll need a more
complex query. You can do something like this
var v1 varchar2(10)
var v2 varchar2(10)
...
var v10 varchar2(10)
SELECT /*+ parallel(16) */ ...... your query WHERE col1 = v1 AND col2 =
v2 ....
Then change the datatype of the v1,v2 variables to varchar2(100), one by
one and rerun your query every time after a bind variable definition
change, you should see a new cursor popping up every time, due the bind
metadata mismatch (bind handling logic treats each varchar2(32)
differently than varchar2(33) internally (other thresholds are 128, 1000
and 2000 bytes)
--
Tanel Poder
http://tech.e2sn.com
http://blog.tanelpoder.com
---------- Forwarded message ----------
From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
Date: Wed, Jul 7, 2010 at 11:20 PM
Subject: Re: Re : High Version Count with usage of Parallel Query
To: hkchital@xxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
It looks what's happening is the fairly classic case of non-sharing of
cursors due mismatching bind metadata. The PX engine further amplifies
this issue and adds these funky-named reasons into v$sql_shared_cursor.
You could compare the bind metadata (especially the max varchar2
lengths) in V$SQL_BIND_METADATA for your different cursors.
To reproduce this issue on your test environment, you'll need a more
complex query. You can do something like this
var v1 varchar2(10)
var v2 varchar2(10)
...
var v10 varchar2(10)
SELECT /*+ parallel(16) */ ...... your query WHERE col1 = v1 AND col2 =
v2 ....
Then change the datatype of the v1,v2 variables to varchar2(100), one by
one and rerun your query every time after a bind variable definition
change, you should see a new cursor popping up every time, due the bind
metadata mismatch (bind handling logic treats each varchar2(32)
differently than varchar2(33) internally (other thresholds are 128, 1000
and 2000 bytes)
--
Tanel Poder
http://tech.e2sn.com
http://blog.tanelpoder.com
On Wed, Jul 7, 2010 at 10:39 PM, Hemant K Chitale
<hkchital@xxxxxxxxxxxxxx> wrote:
As a follow up to my earlier email :
<//www.freelists.org/post/oracle-l/High-Version-Count-with-usage-of
-Parallel-Query>//www.freelists.org/post/oracle-l/High-Version-Coun
t-with-usage-of-Parallel-Query
Greg : Yes, mixing PQ with Binds is a bad idea. And I am "taking out"
the PQ (the table sizes and queries don't justify PQ at all)
Tanel : 10.2.0.3 V$SQL_SHARED_CURSOR shows these mismatches :
For 648 "child"s of a particular simple SQL statement :
TOP_LEVEL_RPI_CURSOR : 208 (all the same CHILDs as
BIND_PEEKED_PQ_MISMATCH)
BIND_UACS_DIFF : 198
PQ_SLAVE_MISMATCH : 434 (which means that the statements do not really
need PX OR is Bug 4367986 in 10.2.0.3)
BIND_PEEKED_PQ_MISMATCH : 208
Other related posts:
