Comments on: Bind Variableshttps://jonathanlewis.wordpress.com/2007/01/05/bind-variables/
Just another Oracle weblogWed, 07 Dec 2016 20:47:06 +0000hourly1http://wordpress.com/By: Jonathan Lewishttps://jonathanlewis.wordpress.com/2007/01/05/bind-variables/#comment-50274
Tue, 18 Sep 2012 10:58:50 +0000http://jonathanlewis.wordpress.com/2007/01/05/bind-variables/#comment-50274There’s been some chat on oracle-l recently about event 10503 not working at the session level – with a reference given, though, to Bug 10274265 : “EVENT 10503 NOT WORKING ON THE SESSION LEVEL” and followup reports that this bug is now fixed with various patches available.
]]>By: Udayhttps://jonathanlewis.wordpress.com/2007/01/05/bind-variables/#comment-46050
Fri, 13 Apr 2012 05:13:35 +0000http://jonathanlewis.wordpress.com/2007/01/05/bind-variables/#comment-46050Hello Jonathan,

we see 2 namespaces in v$librarycache in 11.2 (‘SQL AREA BUILD’ and ‘SQL AREA STATS’) , I have not found any details on these 2. What are these 2 new namespace relates to ?

]]>By: Ritahttps://jonathanlewis.wordpress.com/2007/01/05/bind-variables/#comment-23580
Thu, 01 Nov 2007 22:35:17 +0000http://jonathanlewis.wordpress.com/2007/01/05/bind-variables/#comment-23580We are using VPD.
As a result of this we are seeing that we are getting plenty of child cursors (in some cases 600+).I checked the auth_check_mismatch in v$sql_shared_cursor and it has a ‘Y’ value.
I would really appreciate if you could help us in understanding and resolving this issue.

If i use bind variables it is taking nearly 4min where as without bind variables it takes only 20Sec to finish the job can you help me explain me the concept in detail that will be great help.

2. will soft parse undergo latches.

Regards

Balu.

]]>By: Jonathan Lewishttps://jonathanlewis.wordpress.com/2007/01/05/bind-variables/#comment-9749
Sat, 26 May 2007 13:33:08 +0000http://jonathanlewis.wordpress.com/2007/01/05/bind-variables/#comment-9749Saibabu, it is possible, though perhaps not by design. Say you have query with a range predicate:char_col < :b1
When you first run the statement your value is ‘aaa’, very short and close to the start of the alphabet. When I run the query some time later my value is ‘zzz…..zzz’ – very long and close to the end of the alphabet.
Because of the differences in length I get a second child cursor; because of the differences in value I may get a different execution plan. But, as I said, this may be accident rather then design.
Answer to question 2: I don’t know, I didn’t write the specification or the code; but it’s a reasonable guess. Maybe this is a hangover from the days when memory was very tightly limited and “wasting” 4KB was important. In 10g a lot of the code has apparently changed to try and reduce fragmentation of memory by sticking (where possible) to a small number of allocation sizes – maybe this strategy will also surface at some future time with this bind-variable issue.
]]>By: Vivek Sharmahttps://jonathanlewis.wordpress.com/2007/01/05/bind-variables/#comment-9645
Fri, 25 May 2007 13:52:42 +0000http://jonathanlewis.wordpress.com/2007/01/05/bind-variables/#comment-9645In Response to Akhileshs’ query about the table in remote database and the query showing a high version count. A probable solution would be to create a view in the local database as select * from emp@remote. Now, when this local view is referenced, the version count should come down.

1) Does Oracle ever come up with a different plan for child cursors when they were spawned because of their datatype lengths?
2) If answer to the above question is no, Why is Oracle is spawning different child cursor due to bind lengths, is it to save on shadow process memory?

I think you know more than me about the library cache inner workings, as demonstrated by the fact that I don’t know the answers to your questions ;)

Thanks for the very interesting test case, it is absolutely convincing that Oracle will try to use the “current” (last) child first if it is compatible instead of performing a full search to find the most compatible one – so (potentially?) using a less efficient plan but avoiding a lot of pinning. I guess (stress on guess) that this will make the first child colder and colder, so eventually, in a library cache really pressed for space, eligible for being removed – but look at Jonathan’s comment on my test case above.