Monday, April 23, 2007

Share and Share alike...

I learned two new things today :) 

First, one about a combination of a PLSQL memory optimization that takes place on the 2000 byte boundary that affected the findings of a test case...

And second, was something Alberto Dell'Era said on the same thread that made me "think and go, hmmmm" for a minute.

I did not realize that running my test backwards would come to a totally different outcome, very cool.  Another bit of trivia than can be used to explain the sometimes unexplainable.  The number of child cursors you'll see due to a length bind mismatch depends on the initial order of execution after an instance is started.  The child cursors are created with "small bind spaces" that can grow by spawning new child cursors - but if the first entry is big and the rest get smaller - that won't happen.  Read this entry, and compare this simple test to it: 

ops$tkyte%ORA10GR2> create table t ( x varchar2(2000) );
Table created.

ops$tkyte%ORA10GR2> declare
2 a varchar2(1) := 'x';
3 b varchar2(100) := rpad('x',100,'x');
4 c varchar2(500) := rpad('x',500,'x');
5 d varchar2(1000) := rpad('x',1000,'x');
6 begin
7 insert into t big_to_small values(d);
8 insert into t big_to_small values(c);
9 insert into t big_to_small values(b);
10 insert into t big_to_small values(a);
11 insert into t small_to_big values(a);
12 insert into t small_to_big values(b);
13 insert into t small_to_big values(c);
14 insert into t small_to_big values(d);
15 end;
16 /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> column sql_text format a45
ops$tkyte%ORA10GR2> column address new_val ADDR
ops$tkyte%ORA10GR2> select sql_text, address, child_address
2 from v$sql
3 where sql_text like 'INSERT%INTO%T%VALUES(%:B1%)'
4 /

SQL_TEXT ADDRESS CHILD_AD
--------------------------------------------- -------- --------
INSERT INTO T BIG_TO_SMALL VALUES(:B1 ) 29A77AEC 29AEC3DC
INSERT INTO T SMALL_TO_BIG VALUES(:B1 ) 260543F8 2993E05C
INSERT INTO T SMALL_TO_BIG VALUES(:B1 ) 260543F8 2994BB4C
INSERT INTO T SMALL_TO_BIG VALUES(:B1 ) 260543F8 29B3AA68

POST A COMMENT

3 Comments:

Blogger Q u a d r o said....

"The number of child cursors you'll see due to a length bind mismatch depends on the initial order of execution after an instance is started."

There was similar thread on Jonathan's blog.

Bind Variables

First comment points to exactly the same observation.

Mon Apr 23, 09:47:00 PM EDT  

Blogger Alberto Dell'Era said....

> There was a similar thread on Jonathan's blog (snip)
> First comment points to exactly the same observation.

In fact, that was appropriately credited in my comment:

"This idea is not mine but is by Alexander Fatkulin in this thread:

http://jonathanlewis.wordpress.com/2007/01/05/bind-variables/"

That is exactly the "first comment" you mention, which I referred to by using named notation instead of positional notation :)

Tue Apr 24, 05:02:00 AM EDT  

Blogger Q u a d r o said....

Alberto,

I don't have any problems with this idea being credited or not - the problem is what I have read this post before reading the previous (and before referring to your comment). I just thought "oh, I know what happened, seen this before" - and submitted a comment.

Thanks for the credits :)

Tue Apr 24, 06:43:00 AM EDT  

POST A COMMENT

<< Home