Re : High Version Count with usage of Parallel Query
- From: Tanel Poder <tanel@xxxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 7 Jul 2010 23:25:01 +0800
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-Count-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:
