| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Unshared cursors redux
(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:
... and the results:
... and the results from v$sql and v$sql_shared_cursor also nicely cross reference with 10270 and library cache dump:
boris_at_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
---------- --------
2 rows selected.
boris_at_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
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.
> well, I have a bit different experience - at least > at > 10.2.0.2/Linux...thelength of bind var. do count... > > GB_at_xxxxxx > var b varchar2(100); > GB_at_xxxxxx > select /* goran2 */ count(9) from brisi > where instanceid# = :b; > > > COUNT(9) > > ---------- > > > 0 > > > GB_at_xxxxxx > var b varchar2(200); > > > GB_at_xxxxxx > select /* goran2 */ count(9) from brisi > where instanceid# = :b; > > > COUNT(9) > > ---------- > > > 0 > > > GB_at_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_at_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_at_xxxxxx > spool off > > > On 2/23/07, Boris Dali <boris_dali_at_yahoo.ca> 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): > > > > >
> > > > 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_at_society.servebeer.com> > > 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!?
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 15 2007 - 11:51:57 CDT
 |
 |