Re: Unshared cursors redux
- From: Boris Dali <boris_dali@xxxxxxxx>
- To: goran00@xxxxxxxxx
- Date: Thu, 15 Mar 2007 12:51:57 -0400 (EDT)
(just trying to catch up with the list's emails... I
am only about two weeks behind...)
Goran,
I stand corrected.
I reproduced your test-case in oracle 9.2.0.2 on
mandrake 9 dolphin:
-- 1/ new child cursor #0:
var v varchar2(1)
select count(3) from dual where dummy=:v;
-- 2/ same child cursor #0:
var v varchar2(2)
select count(3) from dual where dummy=:v;
select hash_value, address, sql_text from v$sql where
upper(sql_text) like '%COUNT(3)%' and sql_text not
like '%hash_value%';
-- 3/ new child cursor #1:
var v varchar2(33)
select count(3) from dual where dummy=:v;
select hash_value, address, sql_text from v$sql where
upper(sql_text) like '%COUNT(3)%' and sql_text not
like '%hash_value%';
... and the results:
-- 1/ new child cursor #0:
#1: creating new child object #0
#1: child creation successful 5fc3454c 5ef27158 0
=====================
PARSING IN CURSOR #1 len=40 dep=0 uid=47 oct=3 lid=47
tim=1146462386121041 hv=1910464843 ad='5fc34ac8'
select count(3) from dual where dummy=:v
PARSE
#1:c=0,e=1181,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1146462386121028
#1: checking for already pinned child
#1: kkssbt: repinning child in exclusive mode for tca
#1: kkssbt: setting types
#1: kkssbt: opitca 5fc342d4 5fc32bb0
#1: kkssbt: opitca complete 5fc342d4 5fc32bb0
#1: checking for already pinned child
BINDS #1:
bind 0: dty=1 mxl=32(01) mal=00 scl=00 pre=00
oacflg=03 oacfl2=0 size=32 offset=0
bfp=4062b128 bln=32 avl=00 flg=05
-- 2/ same child cursor #0:
#1: pinning parent in shared mode to search 5ef26348
5fc34ac8
#1: kksfbc: calling kksscl outside while loop
#1: kksscl: next child is #0
#1: kksscl: pinning child #0 in shared mode 5ef260c0
5fc3454c
#1: kksscl: calling kksauc
#1: kksscl: unpinning the parent
=====================
PARSING IN CURSOR #1 len=40 dep=0 uid=47 oct=3 lid=47
tim=1146462391494557 hv=1910464843 ad='5fc34ac8'
select count(3) from dual where dummy=:v
PARSE
#1:c=10000,e=485,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1146462391494471
#1: checking for already pinned child
BINDS #1:
bind 0: dty=1 mxl=32(02) mal=00 scl=00 pre=00
oacflg=03 oacfl2=0 size=32 offset=0
bfp=4062b004 bln=32 avl=00 flg=05
-- 3/ new child cursor #1:
#1: pinning parent in shared mode to search 5ef26348
5fc34ac8
#1: kksfbc: calling kksscl outside while loop
#1: kksscl: next child is #0
#1: kksscl: pinning child #0 in shared mode 5ef25580
5fc3454c
#1: kksscl: calling kksauc
#1: kksscl: unpinning the parent
=====================
PARSING IN CURSOR #1 len=40 dep=0 uid=47 oct=3 lid=47
tim=1146462451077511 hv=1910464843 ad='5fc34ac8'
select count(3) from dual where dummy=:v
PARSE
#1:c=0,e=409,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1146462451077501
#1: checking for already pinned child
#1: object is invalid
#1: no valid child pinned
#1: pinning parent in shared mode to search 5ef26348
5fc34ac8
#1: kksfbc: calling kksscl outside while loop
#1: kksscl: next child is #0
#1: kksscl: pinning child #0 in shared mode 5ef25580
5fc3454c
#1: kksscl: calling kksauc
#1: kksscl: match == KKSCBTNOM --> # would be nice to
know what KKSCBTNOM means, why the match is not kosher
and new child is needed ...
#1: kksscl: releasing child
#1: no suitable child found --> # new child is born
#1: no suitable child found
#1: pinning parent in exclusive mode
#1: creating new child object #1
#1: child creation successful 5fbe65d4 5ef265d0 0
#1: downgrading child pin to share
#1: checking for already pinned child
BINDS #1:
bind 0: dty=1 mxl=128(33) mal=00 scl=00 pre=00
oacflg=03 oacfl2=0 size=128 offset=0
bfp=4062b0c8 bln=128 avl=00 flg=05
... and the results from v$sql and v$sql_shared_cursor
also nicely cross reference with 10270 and library
cache dump:
boris@dani4m6> select hash_value, address, sql_text
from v$sql where upper(sql_text) like '%COUNT(3)%' and
sql_text not like '%hash_value%';
HASH_VALUE ADDRESS SQL_TEXT
---------- --------
----------------------------------------
1910464843 5FC34AC8 select count(3) from dual where
dummy=:v
1910464843 5FC34AC8 select count(3) from dual where
dummy=:v
2 rows selected.
boris@dani4m6> select address, kglhdpar, bind_mismatch
from v$sql_shared_cursor where kglhdpar in (
2 select address from v$sql where upper(sql_text)
like '%COUNT(3)%' and sql_text not like
'%hash_value%');
ADDRESS KGLHDPAR B
-------- -------- -
5FC3454C 5FC34AC8 N
5FBE65D4 5FC34AC8 Y
2 rows selected.
BUCKET 90443:
LIBRARY OBJECT HANDLE: handle=5fc34ac8
name=select count(3) from dual where dummy=:v
hash=71df614b timestamp=03-15-2007 11:51:23
namespace=CRSR flags=RON/TIM/PN0/SML/[12010000]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=1
lwt=0x5fc34ae0[0x5fc34ae0,0x5fc34ae0]
ltm=0x5fc34ae8[0x5fc34ae8,0x5fc34ae8]
pwt=0x5fc34af8[0x5fc34af8,0x5fc34af8]
ptm=0x5fc34b50[0x5fc34b50,0x5fc34b50]
ref=0x5fc34ad0[0x5fc34ad0, 0x5fc34ad0]
lnd=0x5fc34b5c[0x5fc34b5c,0x5fc34b5c]
LIBRARY OBJECT: object=5fc34850
type=CRSR flags=EXS[0001] pflags= [00] status=VALD
load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 5fc34a0c 5fc3476c 5fc3454c
1 5fc34a0c 5fc347b8 5fbe65d4
BUCKET 90443 total object count=1
----
Comparing your test-case with my original from 2003, I
first thought the difference is in the fact that I
used anonymous pl/sql block, while yours is a straight
sql (in case of pl/sql an embedded sql is shared
regardless, but a pl/sql wrapper might or might not be
shared). Since that didn't explain, I toyed with the
idea that your bind variable appears in the "where"
clause, while mine was in the "into" clause. No joy.
Next I checked the version/platform difference - again
to no avail. Since all failed to explain the
difference, I went back to examine carefully my
test-case and that's when I finally noticed that the
bind lengths I used were 1 and 30 ... both obviously
falling into the same 1-32 bucket :-( Now I really
feel stupid that the most simple and obvious thing was
the last I paid attention to. But on the bright side I
now know that my test-case from 2003 was flawed.
Better later than never. Thanks for catching this.
Thanks,
Boris Dali.
--- goran bogdanovic <goran00@xxxxxxxxx> wrote:
> well, I have a bit different experience - at least
> at
> 10.2.0.2/Linux...thelength of bind var. do count...
>
> GB@xxxxxx > var b varchar2(100);
> GB@xxxxxx > select /* goran2 */ count(9) from brisi
> where instanceid# = :b;
>
>
> COUNT(9)
>
> ----------
>
>
> 0
>
>
> GB@xxxxxx > var b varchar2(200);
>
>
> GB@xxxxxx > select /* goran2 */ count(9) from brisi
> where instanceid# = :b;
>
>
> COUNT(9)
>
> ----------
>
>
> 0
>
>
> GB@xxxxxx > select sql_text, version_count, sql_id
> 2 from v$sqlarea
> 3 where sql_text like '%goran2%'
> 4 ;
>
> SQL_TEXT VERSION_COUNT
> SQL_ID
>
> ------------------------------ -------------
> -------------
>
> select /* goran2 */ count(9) f 2
> dv7phzzypq13t
>
> rom brisi where instanceid#
> =
>
> :b
>
>
>
> select sql_text, version_count 1
> ghqmf9yaw3ju6
>
> , sql_id from v$sqlarea
> where
>
> 1=1 and sql_text like
> '%goran2
>
> %'
>
>
>
>
> GB@xxxxxx > select sa.sql_text, sc.bind_mismatch,
> sc.child_number
> 2 from v$sql_shared_cursor sc, v$sqlarea sa
> 3 where sc.sql_id = 'dv7phzzypq13t'
> 4 and sc.sql_id = sa.sql_id
> 5 ;
>
> SQL_TEXT B
> CHILD_NUMBER
>
> ------------------------------ -
> ------------
>
> select /* goran2 */ count(9) f N
> 0
>
> rom brisi where instanceid#
> =
>
> :b
>
>
>
> select /* goran2 */ count(9) f Y
> 1
>
> rom brisi where instanceid#
> =
>
> :b
>
>
>
>
> GB@xxxxxx > spool off
>
>
> On 2/23/07, Boris Dali <boris_dali@xxxxxxxx> wrote:
> >
> > Rich,
> >
> > We discussed this issue on this list back in 2003.
> I
> > think the answer is no, child cursors created due
> to
> > different bind lengths are **not** flagged in
> > v$sql_shared_cursor in contrast to the case of
> using
> > different datatypes that does lead to raising this
> > bind_mismatch flag.
> >
> > Here's the top-level link for that discussion (and
> > from there you can folow the whole thread):
> >
> >
>
http://www.mail-archive.com/oracle-l@xxxxxxxxxxx/msg88023.html
> >
> > I didn't follow this thread closely, but if you
> are on
> > 10.2.0.3 you probably know that there's an
> infomous
> > one-off patch# 5705795 (for Linux) that you might
> want
> > to look into
> >
> > Thanks,
> > Boris Dali.
> >
> > --- Rich Jesse <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
> > wrote:
> >
> > > So, based on that, I would
> > > expect that the absence of explicit binds, along
> > > with NOT using the dreaded
> > > CURSOR_SHARING=FORCE|SIMILAR init.ora parameter,
> in
> > > a cursor would cause
> > > that cursor to either be shared or to have a
> reason
> > > in V$SQL_SHARED_CURSOR
> > > as to why it would not be shared. But binds for
> > > different executions that
> > > cross allocation sizes would seem to be the
> > > definition for the
> > > "BIND_MISMATCH" column of V$SQL_SHARED_CURSOR,
> > > wouldn't it?
> > ....
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
