Love your demonstrative example. It is very clear.

From your statement
"well the reason it does not apply to static sql is that static sql is (by its very definition) fixed at compile time. Every time you run that program -- the static sql will be the SAME. It will be 100% shareable"
So, does it mean that static SQL need not use BIND VARIABLES? When I run the function remove_constants (pg. 449 from your back), since the "some value" changes in the where clause, I think it captures static SQL too.. Pls. advise.

Hello Mr Tom,

The use of Bind variables not only improves the efficiency of the application, but also of application maintainence. The code becomes easier to read and modify (that is one more + point for bind variables).

You are right Mr Tom, you should look into a small book on bind variables, its importance and use!

Hi Tom, 
I issued 4 static SQL statements in the database and am sending you the results:

SQL> select ename from emp where empno = 10;

ENAME
----------


SQL> select ename from emp where empno = 20;

ENAME
----------


SQL> select ename from emp where empno = 7786;

no rows selected

SQL> select ename from emp where empno = 7374;

no rows selected

SQL> insert into sql_area_tmp (sql_text)
  2  select sql_text from v$sqlarea;

121 rows created.

SQL> commit;

Commit complete.

SQL> update sql_area_tmp
  2  set sql_text_wo_constants = remove_constants(sql_text);

121 rows updated.

SQL> select sql_text_wo_constants, count(*) from sql_area_tmp
  2  group  by sql_text_wo_constants having count(*) > 2 order by 2;

SQL_TEXT_WO_CONSTANTS
------------------------------------------------------------------------------------------------------------------------
  COUNT(*)
----------

SELECT ENAME FROM EMP WHERE EMPNO = @
         4

Now is the above query a "problem Query" ?
 

Well, finding shared sql by hash value is fastest method, no doubt, but is parsing that is necessary for literal replacement proven to be significantly more expensive? AFAIK parsers in general are not doing significantly more than one pass through the parsed text; but when calculating hash value one pass through query string is needed as well. If current literal replacement implementation doesn't perform, then just improve it!

Oracle has a good track of implementing every feature user asked for. Isn't it clear yet, that users don't want to know about bind variables?

Tom Pls Clarify after reading lot of articles still.....

Here is the Sceneario:
In The Stored procedure:
Cursor definition
Execute the cursor
Looping thru the cursor
//Confusion Point
INSERT INTO TABLE YIELD VALUES(
:ls_val1,:ls_val2)
END LOOP
Close Cursor

Point is:
Will the Insert Statement will be shared for each looping or it will not be shared bcos for each looping there will be different value for ls_val1 and la_val2 (i.e FOR ROW 1:Insert into table yield Values('12',500)
For Row 2:Insert into table yield Values('17',800)
How oracle will take the above statment?

Pls Clarify this Point

Thx
Ashok

Thank you very much for the untiring time to answer issues on bind variables.... and they're very (3x) useful.
I am a DBA currently working out with this similar problem in our site. My company bought a software package written in Visual Basic that issues a lot of SQL statements that makes my Database server "scrambling" for resources all the time. I just have one question re this issue, what if i execute my statements on front-end tool other than oracle tools, i.e. VisualBasic, how could an sql statment using bind variables be written? All i know is to declare a variable and pass its value to an SQL statement.

Hi Tom,

brilliant example! And with hard data. And I love your comments about "supposed to be professionals".

Finally single location to point out.

Ok I understand what you say about bind variables, especially how PL/SQL uses them

To take things a step further:
function isValid ( i_pkey in table.column%TYPE) return boolean is
begin
for r in (select 'x' from table t where t.pkey = i_pkey) loop
return true;
end loop;
return false;
end;

This function will use bind variables by virtue of PL/SQL binding.

Will the cursor be re-used for repeated calls to isValid provided it hasn't been shunted out of the shared pool?

Given the advantages of for loops, is this an economical use where we exit with a return statement?

Playing around with the examples in </code> http://www.programmersheaven.com/other/BookSamples/book28/css/4826_Chap01.htm <code>
to emulate repeated calls as follows:

This is your example (amended for my schema)
declare
type rc is ref cursor;
l_rc rc;
l_dummy accounts.reference%TYPE;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 100000
loop
open l_rc for
select reference
from accounts
where reference = lpad(i, 10, '0')
;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line(round((dbms_utility.get_time-l_start)/100, 2 )
||' seconds...' );
end;
/

This kind of emulates repeated calls to a function (the inner loop which uses an exit instead of a return statement):
declare
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 100000 loop
for r in (select reference
from accounts
where reference = lpad(i, 10, '0')) loop
exit;
end loop;
end loop;
dbms_output.put_line(round((dbms_utility.get_time-l_start)/100, 2 )
||' seconds...' );
end;
/

The latter showed a two fold improvement in performance, presumably because the overhead of managing the cursor variable was removed - this suprised me!

Thanks for all the useful info...

Very good examples ...

Tom's examples show that parsing is expensive, but I actually think he's under-stressing the cost. Recently when trying to see what limits the size of SQL's could be (amongst other things), we tried a:

'select * from dual union all
select * from dual union all
...'

repeated up to 'n' times. For large values of 'n', we killed the session after 10 minutes - and it will still in the parse phase. Parsing is a nightmare - bind variables are a dream come true

Hi Tom,

Sorry for my silly question.

Your example was excellent. But i could not get how you got the value. You said in

"there is the real kicker. Look at the latches.. 45,442 when not using binds, 2,400 using binds"

Hod did you get 45442 and 2400???

Thanx in advance.

REgards,
Sagi

>> ops$tkyte@ORA817DEV.US.ORACLE.COM> ALTER SYSTEM FLUSH
>> SHARED_POOL;

Tom, been wondering about flushing the pool...is this
something you'd grant developers - in a dev instance - the ability to do ?
Im a little "FUD-dish" in doing it cause I dont understand it.

What consequences should one consider ? Will it cause any problem, inconvenience to other sessions other than the obvious ?
Can I just do it ANYTIME I find a need to compare stats ?
Thanks

Tom:

Why when I run your example here I get different results than yours. I get the same total for latches (about 10000)


SQL> @c:\tools\harness_ex (copied from yours)
Input truncated to 2 characters
 27  /
14 hsecs
9 hsecs

PL/SQL procedure successfully completed.

IMSD> @harness_q

SUBSTR(A.NAME,1,20)        RUN1       RUN2       DIFF
-------------------- ---------- ---------- ----------
LATCH.cache buffers           1          2          1
STAT...calls to get        1003       1002         -1
STAT...db block chan         17         16         -1
STAT...opened cursor          1          2          1
STAT...recursive cal       1011       1012          1
STAT...total file op          1          0         -1
STAT...enqueue reque          4          3         -1
LATCH.undo global da          2          5          3
LATCH.redo allocatio          7         11          4
STAT...redo size          20756      20880        124
STAT...opened cursor       1002          3       -999
STAT...parse count (       1002          3       -999
STAT...table scans (          0       1000       1000
LATCH.shared pool          2001          1      -2000
STAT...db block gets         14       4014       4000
STAT...session logic         25       4025       4000
STAT...session uga m          0       4264       4264
LATCH.library cache       10025       2027      -7998
LATCH.cache buffers          80       8113       8033

19 rows selected.
 

Look at the latches a little closer:

library cache -- down 8k
shared pool   -- down 2k

cache buffers (SOMETHING, i don't know what since you nicely chopped it off) up 8k. 

So, investigate why one of 

ops$tkyte@ORA920> select name from v$latchname where name like 'cache buffer%';

NAME
------------------------------
cache buffers lru chain
cache buffers chains
cache buffer handles

went up for some reason.  it could well be a side effect of a tiny buffer cache and the insert we do in the middel for example.


It does not invalidate at all the results of this test.  What we were trying to reduce was the library cache contention induced by hard parsing.  We did.  Although I must say -- this looks definitely like a "second run" of this without a flush of the shared pool.


Analyze the results -- test things out -- explore -- investigate.  What more can I say.....


 

"All plsql variables referenced in SQL in PLSQL are in fact bind variables."

Hi does the above apply to constants in plsql. I thought
you mentioned somewhere (cant find it now) that even constants are actually bound on in plsql. I ran the
following sql.
reate or replace procedure p
as
l_dummy varchar2(10);
begin
select name
into l_dummy
from t1
where name = 'name1'
and rownum <= 1;
dbms_output.put_line ( 'l_dummy ' || l_dummy );
end;
/
alter session set sql_trace=true;
exec p;
--
the tkprof shows:
--
SELECT name
from t1
where name = 'name1'
and rownum <= 1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 0 2 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 228 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY
1 INDEX RANGE SCAN (object id 112797)

********************************************************************************

-- this seems to imply that the constant was not substituted
for a bind variable (from the select text).

Please clarify.

Thank you!

"no, i've never said that."
I apologize for that then! May be you meant to say
that the "constants" defined in the package using constant
keyword are bound as variables. Or may be my mind is playing tricks - it has been known to do that before !;)

Thanx for all your help, Tom!

Tom,

what about constants in dynamic sql like below:

open o_emp_cur for
'select empno, empname,sal, nvl(comm,0) comm
from emp where empno = :eno' using i_empno;

where 0 is constant in the above query do we have to bind that also?

and one more, how do i know, whether my dnymic query has bind variables and it used previously parsed query? can i use any data dictionary view for this?

thanks in advance

One of the applications I inherited has implemented Oracle user/role type functionality on its own, talk about reinventing the wheel. Anyway, I have to work with it for now, but I have a question about dynamic SQL and bind variables regarding this.

There is a table like

create table user_groups
(
userid varchar2(10),
groupid int,
defaultgroup varchar2(1),
constraint pk1 primary key (userid,groupid)
);

Given a userid, it returns all the groups that the user has access to. One of these rows will have the 'defaultgroup' marked Y to indicate the user's default group.

Each of the "groups" has a corresponding schema in Oracle like GRPxx with 1 table GRPxxTBL

Now, suppose the above list returns groupids 1,4 and 10. I need to construct a query like

select name from cg1.cg1tbl
where name like '%string%'
union
select name from cg4.cg4tbl
where name like '%string%'
union
select name from cg10.cg10tbl
where name like '%string%';

The requirement is, given a userid and the search string, return all the applicable data as per the query above.

The way I am doing it now, I am dynamically constructing the above SQL for each request with a different number of UNION blocks (depending on what my user_groups table returns). No bind variables anywhere in sight!

How can I do this efficiently using bind variables and reusable code so I dont have to build all these different SQLs?

Thanks

you are kidding -- someone built a multi-schema model like that.  ooouuuucccchhh.

"it works really fast" is a statement you won't be hearing very often.

You do NOT want to use union -- not a chance.

you could use partitioned views perhaps.  consider this setup:

drop user a cascade;
drop user b cascade;
drop user c cascade;
grant dba to a identified by a;
grant dba to b identified by b;
grant dba to c identified by c;
                                                                                                               
@connect a/a
create table t ( x int primary key, string varchar2(10) );
insert into t (x,string) select object_id, substr(object_id,1,10) from all_objects;
analyze table t compute statistics for table;
                                                                                                               
@connect b/b
create table t ( x int primary key, string varchar2(10) );
insert into t (x,string) select object_id, substr(object_id,1,10) from all_objects;
analyze table t compute statistics for table;
                                                                                                               
@connect c/c
create table t ( x int primary key, string varchar2(10) );
insert into t (x,string) select object_id, substr(object_id,1,10) from all_objects;
analyze table t compute statistics for table;
                                                                                                               
@connect /
create or replace view v
as
select x,string,  1 grp from a.t
UNION ALL
select x,string,  2 grp from b.t
UNION ALL
select x,string,  3 grp from c.t
/



Now, we query that view (after making sure

 
ops$tkyte@ORA9IR2> show parameter view
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
partition_view_enabled               boolean     TRUE

is set).  Watch what happens with these queries:

variable g1 number;
variable g2 number;
variable g3 number;
                                                                                                                                               
@trace
                                                                                                                                               
exec :g1 := 1; :g2 := 2; :g3 := 3;
select count(*) from v where grp in ( :g1, :g2, :g3 );
                                                                                                                                               
exec :g1 := 1; :g2 := NULL; :g3 := NULL;
select count(*) from v where grp in ( :g1, :g2, :g3 );
                                                                                                                                               
exec :g1 := 2; :g2 := NULL; :g3 := NULL;
select count(*) from v where grp in ( :g1, :g2, :g3 );


select count(*) from v where grp in ( :g1, :g2, :g3 )
                                                                                                               
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.42       0.41          0        279          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.42       0.41          0        279          0           1
                                                                                                               
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=279 r=0 w=0 time=414882 us)
  92220   VIEW  (cr=279 r=0 w=0 time=375405 us)
  92220    UNION-ALL  (cr=279 r=0 w=0 time=298361 us)
  30738     FILTER  (cr=93 r=0 w=0 time=42250 us)
  30738      INDEX FAST FULL SCAN SYS_C005432 (cr=93 r=0 w=0 time=16142 us)(object id 38032)
  30740     FILTER  (cr=93 r=0 w=0 time=42551 us)
  30740      INDEX FAST FULL SCAN SYS_C005433 (cr=93 r=0 w=0 time=15854 us)(object id 38034)
  30742     FILTER  (cr=93 r=0 w=0 time=42036 us)
  30742      INDEX FAST FULL SCAN SYS_C005434 (cr=93 r=0 w=0 time=15936 us)(object id 38036)

<b>so, when all 3 groups are set -- all three tables are searched but.... set just a single group:</b>

********************************************************************************
select count(*) from v where grp in ( :g1, :g2, :g3 )
                                                                                                               
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.14       0.13          0         93          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.14       0.13          0         93          0           1
                                                                                                               
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=93 r=0 w=0 time=135463 us)
  30738   VIEW  (cr=93 r=0 w=0 time=122201 us)
  30738    UNION-ALL  (cr=93 r=0 w=0 time=95857 us)
  30738     FILTER  (cr=93 r=0 w=0 time=42173 us)
  30738      INDEX FAST FULL SCAN SYS_C005432 (cr=93 r=0 w=0 time=16071 us)(object id 38032)
      0     FILTER  (cr=0 r=0 w=0 time=0 us)
      0      INDEX FAST FULL SCAN SYS_C005433 (cr=0 r=0 w=0 time=0 us)(object id 38034)
      0     FILTER  (cr=0 r=0 w=0 time=0 us)
      0      INDEX FAST FULL SCAN SYS_C005434 (cr=0 r=0 w=0 time=0 us)(object id 38036)

</b>and that filter preempts scanning the 2 tables that don't count -- same query, different binds, different execution characteristics</b>

********************************************************************************
select count(*) from v where grp in ( :g1, :g2, :g3 )
                                                                                                               
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.14       0.13          0         93          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.14       0.13          0         93          0           1
                                                                                                               
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=93 r=0 w=0 time=137351 us)
  30740   VIEW  (cr=93 r=0 w=0 time=123708 us)
  30740    UNION-ALL  (cr=93 r=0 w=0 time=96227 us)
      0     FILTER  (cr=0 r=0 w=0 time=1 us)
      0      INDEX FAST FULL SCAN OBJ#(38032) (cr=0 r=0 w=0 time=0 us)(object id 38032)
  30740     FILTER  (cr=93 r=0 w=0 time=42325 us)
  30740      INDEX FAST FULL SCAN OBJ#(38034) (cr=93 r=0 w=0 time=16218 us)(object id 38034)
      0     FILTER  (cr=0 r=0 w=0 time=1 us)
      0      INDEX FAST FULL SCAN OBJ#(38036) (cr=0 r=0 w=0 time=0 us)(object id 38036)

<b>change the groups and the tables searched change</b>


 

Beautiful! Thanks!

I have a query like

1 select * from all_payees a,user_groups b
2 where b.userid=:u
3 and a.groupid=b.GROUPID
4 and upper(payeedescr1||payeedescr2) like '%PRICE%'
5* and payeeaddr1 is not null

I do

var u varchar2(10)
exec :u := 'JACK'
/

I get no rows selected.

I change the query to use

2 where b.userid='JACK'

and now it returns data.

Why is the bind variable not working? Thanks

are you using CHAR's?

ops$tkyte@ORA9IR2> create table t ( x char(2), y varchar2(2) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 'a', 'a' );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable v varchar2(2)
ops$tkyte@ORA9IR2> variable c char(2)
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec :v := 'a'; :c := 'a'
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select case when x = :v then 'x=v' else 'x<>v' end xv,
  2         case when x = :c then 'x=c' else 'x<>c' end xc,
  3         case when y = :v then 'y=v' else 'y<>v' end yv,
  4         case when y = :c then 'y=c' else 'y<>c' end yc,
  5         case when x = 'a' then 'x=a' else 'x<>a' end xa,
  6         case when y = 'a' then 'y=a' else 'y<>a' end ya
  7    from t
  8  /
 
XV   XC   YV   YC   XA   YA
---- ---- ---- ---- ---- ----
x<>v x=c  y=v  y<>c x=a  y=a


when you compare a CHAR to a VARCHAR (as your bind must be), you HAVE to pad out the varchar (ansi says so, we didn't make this up).

when you compare a VARCHAR to a CHAR -- same thing.

when you compare a VARCHAR | CHAR to a constant -- ansi says "promote constant to the type of the field being compared to" - so the string 'a' becomes 'a ' when compared to a char(2) and stays 'a' when compared to a varchar2(2)


<b>bottom line suggestion is Never Never Never -- not for anything, not even for char(1) -- use char, use varchar2 always</b>
 

Here are the details from the 10046 level 12 trace

********************************************************************************

select * from all_payees a,user_groups b
where b.userid=:u
and a.groupid=b.GROUPID
and upper(payeedescr1||payeedescr2) like '%PRICE%'
and payeeaddr1 is not null

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.13 0.12 0 6 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 12 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.13 0.13 0 18 0 0


PARSING IN CURSOR #3 len=168 dep=0 uid=1392 oct=3 lid=1392 tim=1289505550754 hv=1368290280 ad='43175210'
select * from all_payees a,user_groups b
where b.userid=:u
and a.groupid=b.GROUPID
and upper(payeedescr1||payeedescr2) like '%PRICE%'
and payeeaddr1 is not null
END OF STMT
PARSE #3:c=130000,e=122713,p=0,cr=9,cu=0,mis=1,r=0,dep=0,og=0,tim=1289505550723
BINDS #3:
bind 0: dty=1 mxl=128(100) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=128 offset=0
bfp=ffffffff7cf69fd8 bln=128 avl=07 flg=05
value="JACKSON"
EXEC #3:c=0,e=5392,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1289505777099
WAIT #3: nam='SQL*Net message to client' ela= 10 p1=1413697536 p2=1 p3=0
FETCH #3:c=0,e=3598,p=0,cr=12,cu=0,mis=0,r=0,dep=0,og=4,tim=1289505781047
WAIT #3: nam='SQL*Net message from client' ela= 590459 p1=1413697536 p2=1 p3=0
XCTEND rlbk=0, rd_only=1
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='HASH JOIN (cr=12 r=0 w=0 time=3579 us)'


Also, when I execute this again, I get

select * from all_payees a,user_groups b
where b.userid=:u
and a.groupid=b.GROUPID
and upper(payeedescr1||payeedescr2) like '%PRICE%'
and payeeaddr1 is not null

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.00 0 12 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.02 0.01 0 12 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 1392

Since it uses bind variables, it shouldnt be hard parsed again, thats the whole point, right? So why does the 'parse' count show 1 above while the 'misses in library cache during parse' show 0? Doesnt this contradict each other?

Thanks

Hm, I just saw that the tkprof shows a fetch=1 count, but I still get 'no rows returned' from sqlplus. So what is it fetching?

SQL> get /tmp/f1
  1  select * from all_payees a,user_groups b
  2  where b.userid=:u
  3  and a.groupid=b.GROUPID
  4  and upper(payeedescr1||payeedescr2) like '%PRICE%'
  5* and payeeaddr1 is not null
SQL> print u 

U
--------------------------------------------------------------------------------
JACKSON

SQL> /

no rows selected

Help? Thanks 

I have enforced the parameter:
alter system set cursor_sharing=force;

but I still found the sql statements have been not using bind variables:

SQL_TEXT_WO_CONSTANTS
---------------------------------------------------------------------------------------------
COUNT(*)
---------
SELECT ERROR_TYPE_ID,ERROR_ACTIVE FROM PE_CONV_ERROR_TYPES WHERE ERROR_TYPE = '#'
20

SELECT COUNT( USER_ID ) FROM USERENG WHERE DB_ENGINE_TYPE = '#' AND NAMED_USER_FLAG = '#'
8

Why?

Thanks.

Thank for the input.
I did the alter system flush shared_pool. and verified all those statement were gone after.
and a couple hours later those resufaced. Looks like hitting a bug.

not at all, people can set cursor_sharing OFF.

and static sql inside of plsql, which is always known to be properly 100% bound, isn't subject to cursor sharing.


ops$tkyte@ORA9IR2> alter system flush shared_pool;
 
System altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter session set cursor_sharing=force;
 
Session altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from dual in_sqlplus where 1=1;
 
D
-
X
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2          for x in ( select * from dual in_good_plsql where 1=1 )
  3          loop
  4                  null;
  5          end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select sql_text from v$sql where upper(sql_text) like 'SELECT * FROM DUAL % WHERE %=%';
 
SQL_TEXT
-------------------------------------------------------------------------------
select * from dual in_sqlplus where :"SYS_B_0"=:"SYS_B_1"
SELECT * FROM DUAL IN_GOOD_PLSQL WHERE 1=1


<b>no need to ping PLSQL with the heinous effects of cursor sharing after all</b>
 

Further to your very useful insight above into using bind variables, I have a further question.

While upgrading to 9i we have suffered performance issues with one particular query. While investigating the problem I noted that we are binding more than I thought - we have a common constants package spec which is used to hold commonly used constants within the database side of application in one central, easily maintained, location. I have now noted that on both 8i and 9i the predicates joining with eg. pkg_constants.MYCONSTANT end up being bound. I am aware, from you book and above discussion, of over-binding and the detrimental side effects it can have. Obviously there is little point in binding constant values.

Can you please clarify to me whether there is a way to have a common source location to store constants that are used in our queries without the constant ending up in a bind variable ?

Also the performance issue on 9i is that FULL table scans are being performed, whereas on 8i index access is chosen. The constants are being bound on 8i and 9i. When the constants are applied as literals the query runs efficiently on 9i as it previously did on 8i. It therefore looks to me that if we can have a common location that does not cause binding then we will have resolved our performance problem ?

Thanks for any advice you can give,

Scott Duncan

Thanks for your prompt reply.

Are you saying that you believe the constants package spec should not result in bind variables for the constants ?

By way of a simple example for you to work with I tried the following on our 9i database :-

A constant definition in our package is :-

BUS_STAT_CDE_ACTIVE CONSTANT
ft_t_acct.acct_stat_typ%TYPE := 'ACTIVE';

where the ft_t_acct.acct_stat_typ%TYPE is CHAR(8).

Simple example is :-

declare
l_count NUMBER := 0;
begin

SELECT count(*)
INTO l_count
FROM dual
WHERE 'ACTIVE' =
pkg_cms_constants.BUS_STAT_CDE_ACTIVE;

end;

Then querying the sqlarea :
select * from v$sqlarea where sql_text like '%count(*)%' order by first_load_time desc

I could see :-

SELECT count(*) FROM dual WHERE 'ACTIVE' = :b1

and also :-

declare l_count NUMBER := 0; begin SELECT count(*) INTO l_count FROM dual WHERE 'ACTIVE' = PKG_cms_constants.BUS_STAT_CDE_ACTIVE;

Thanks again for any further advice you can give.

Thanks for your follow on, I have tkprof'd both with and without package constants on Oracle 9.2.0.4 :-

Sorry if this is too much but to be completely clear our real query is as follows :-

-- in real case we have package constants where
-- you can see literal strings below.
SELECT acct.acct_id
FROM (
SELECT cst_id
FROM cs_cust_ext
WHERE
cst_id = :party_gsid
AND end_tms IS NULL
UNION ALL
SELECT cst_id
FROM ft_t_ccrl
WHERE
prnt_cst_id = :party_gsid
AND
cst_id != :party_gsid
AND rl_typ = 'LEGHREPT'
AND end_tms IS NULL
) cust,
ft_t_cacr cacr,
ft_t_acct acct
WHERE cust.cst_id = cacr.cst_id
AND cacr.org_id = 'CSFB'
AND cacr.bk_id = 'CUST'
AND cacr.rl_typ = 'CUSTACCT'
AND cacr.end_tms IS NULL
AND acct.org_id = cacr.org_id
AND acct.bk_id = cacr.bk_id
AND acct.acct_id = cacr.acct_id
AND acct.acct_stat_typ = 'ACTIVE'
AND acct.acct_cls_dte IS NULL

a) row source operations for plsql with package constants ( I can see in tkprof that these bind ) :-

Rows Row Source Operation
------- ---------------------------------------------------
3956 NESTED LOOPS (cr=832307 r=13279 w=0 time=41036813 us)
271897 HASH JOIN (cr=12601 r=12071 w=0 time=12272428 us)
271893 TABLE ACCESS FULL FT_T_ACCT (cr=7893 r=7793 w=0 time=2914062 us)
271897 TABLE ACCESS FULL FT_T_CACR (cr=4708 r=4278 w=0 time=4118696 us)
3956 VIEW (cr=819706 r=1208 w=0 time=27368333 us)
3956 UNION-ALL PARTITION (cr=819706 r=1208 w=0 time=26758584 us)
40 FILTER (cr=200 r=3 w=0 time=224445 us)
40 TABLE ACCESS BY INDEX ROWID CS_CUST_EXT (cr=200 r=3 w=0 time=24236 us)
40 INDEX UNIQUE SCAN XPKCS_CUST_EXT (cr=120 r=1 w=0 time=11200 us)(object id 21986)
3916 FILTER (cr=819506 r=1205 w=0 time=20082742 us)
3916 TABLE ACCESS BY INDEX ROWID FT_T_CCRL (cr=819506 r=1205 w=0 time=19648603 us)
3935 INDEX UNIQUE SCAN FT_T_CCRL_PK (cr=815571 r=53 w=0 time=11174377 us)(object id 13657)

************************************************************

b) row source operation for literal strings instead of package constants ( I can see in tkprof that these do not bind ) :-

3956 NESTED LOOPS (cr=26666 r=3361 w=0 time=18167863 us)
3956 NESTED LOOPS (cr=14526 r=1362 w=0 time=8479480 us)
3634 VIEW (cr=2626 r=299 w=0 time=2616206 us)
3634 UNION-ALL (cr=2626 r=299 w=0 time=2607932 us)
1 TABLE ACCESS BY INDEX ROWID CS_CUST_EXT (cr=5 r=0 w=0 time=157 us)
1 INDEX UNIQUE SCAN XPKCS_CUST_EXT (cr=3 r=0 w=0 time=73 us)(object id 21986)
3633 TABLE ACCESS BY INDEX ROWID FT_T_CCRL (cr=2621 r=299 w=0 time=2589610 us)
5141 INDEX RANGE SCAN FT_T_CCRL_PK (cr=312 r=0 w=0 time=56849 us)(object id 13657)
3956 TABLE ACCESS BY INDEX ROWID FT_T_CACR (cr=11900 r=1063 w=0 time=5781342 us)
4191 INDEX RANGE SCAN FT_T_CACR_PK (cr=7827 r=0 w=0 time=215427 us)(object id 13635)
3956 TABLE ACCESS BY INDEX ROWID FT_T_ACCT (cr=12140 r=1999 w=0 time=9620778 us)
3956 INDEX UNIQUE SCAN FT_T_ACCT_PK (cr=8177 r=642 w=0 time=2562154 us)(object id 13455)

Interestingly you have prompted me to notice that in the tkprof'd output with the package constants, the trace shows that the query is run immediately after a single internal data dictionary query - whereas in the trace with literals there are quite a lot of internal data dictionary queries prior to the query being executed. As though, seeing the literal value is allowing it to query more data dictionary information to determine how best to run the query or something like that ?

Hope this helps and is not too much.

Thanks again.

I mean like this (a full up test case showing me step by step):

ops$tkyte@ORA9IR2> create table t
  2  as
  3  select 99 id, a.* from all_objects a;
Table created.
 
ops$tkyte@ORA9IR2> update t set id = 1 where rownum = 1;
1 row updated.
 
ops$tkyte@ORA9IR2> create index t_idx on t(id);
Index created.
 
ops$tkyte@ORA9IR2> begin
  2     dbms_stats.gather_table_stats
  3     ( user, 'T',
  4       method_opt=>'for all indexed columns size 254',
  5           cascade=>TRUE );
  6  end;
  7  /
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> alter system flush shared_pool;
System altered.
 
ops$tkyte@ORA9IR2> create or replace package my_pkg
  2  as
  3          my_const constant number := 1;
  4
  5          procedure p;
  6  end;
  7  /
Package created.
 
ops$tkyte@ORA9IR2> create or replace package body my_pkg
  2  as
  3
  4  procedure p
  5  is
  6  begin
  7          for x in ( select * from t where id = my_pkg.my_const )
  8      loop
  9                  null;
 10          end loop;
 11  end;
 12
 13  end;
 14  /
Package body created.

<b>so, I have a query using a constant as a bind in it, now if we just explain plan this query using a bind:</b>

 
ops$tkyte@ORA9IR2> variable x number
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where id = to_number(:x);
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=61 Card=13936 Bytes=1393600)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=61 Card=13936 Bytes=1393600)
 
<b>we can see that autotrace says "full scan" -- why? because autotrace doesn't know what value :x will have -- so it assumes anything.  It thinks 50% of the rows will come back on average.  PLSQL however supplies a bind variable, and bind variable peeking will let the optimizer do something different:</b>
 
 
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> @trace
ops$tkyte@ORA9IR2> alter session set events '10046 trace name context forever, level 12';
 
Session altered.
 
ops$tkyte@ORA9IR2> exec my_pkg.p
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from t where id = 1;
....


The tkprof shows:

********************************************************************************
select * from t where id = :b1
                                                                                                                                                         
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.00          0          4          0           1
                                                                                                                                                         
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=4 r=0 w=0 time=47 us)
      1   INDEX RANGE SCAN T_IDX (cr=3 r=0 w=0 time=29 us)(object id 38257)
********************************************************************************
select * from t where id = 1
                                                                                                                                                         
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           1
                                                                                                                                                         
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID OBJ#(38256) (cr=4 r=0 w=0 time=62 us)
      1   INDEX RANGE SCAN OBJ#(38257) (cr=3 r=0 w=0 time=44 us)(object id 38257)



<b>so you see, with bind variable peeking -- the "where id = my_pkg.my_const" was really "where id = 1"

That is why I need to see a full up test case where this is *not true*.</b>



Perhaps you have a MIXTURE of constants and TRUE BINDS?

eg:

select * from t
where c1 = my_pkg.CONSTANT_1
and c2 = p_parameter_to_routine
and c3 = my_pkg.CONSTANT_2

?
 

<quote>i believe they should not result in different plans being used at runtime.</quote>

I think there is no question one can get different execution plan when using binds (packaged constants) versus the exact constants right into the sql text ... I can post the test case but is rather long ... and I doubt there is anything in there you have not already seen (just an example of overbinding).

Packaged constants are treated as binds ... bind peeking kicks in (the plan is good for one case but not the other) ... you pass the constant values right into the sql ... the optimizer gets the good plan in both cases.

So the issues still remains ... if one decides to have these constants defined in one single place (which sounds rather nice from the perspective of modularization/centralization) then there might be some performance drawbacks.

Personally, this is one area where I would like to see an improvement to the pl/sql engine ... the ability to have some sort of a pre-compiler (you know #define of sorts).

At a minimum, upon compilation of a pl/sql module, it would be nice to have all variables defined as CONSTANTs _replaced_ in the text of the SQL statements in scope.

I may be wrong or unreasonable ... of course.

<quote>
I think you have

a) some binds that are CONSTANT
b) some that are NOT <<<========

and b) is the issue, a) is not. not in 9i anyway!
</quote>

I have:
select count(*) cnt_bind
into cnt
from fred
where v = k_female <== packaged CONSTANT
and id > 30000
;
vs.
select count(*) cnt_no_bind
into cnt
from fred
where v = 'female'
and id > 30000
;

which I don't think is an unreasonable construct ... quite common I would say.

The issue is if the practice of defining packaged constants (good for modularization) comes with a performance caveat ... and it does. Hence my pl/sql pre-compiler woes.

create table fred ( id number(8) , v varchar2(8));

insert into fred
select object_id, decode(mod(object_id,100),0,'male','female')
from all_objects;

insert into fred select * from fred;
insert into fred select * from fred;
insert into fred select * from fred;
insert into fred select * from fred;

create index fredidx on fred (v);

analyze table fred compute statistics
for table
for all indexes
for all columns
;

create or replace package pkg as
k_male constant fred.v%type := 'male' ;
k_female constant fred.v%type := 'female';
procedure pfk;
procedure pmk;
procedure pf;
procedure pm;
end;
/
show errors


create or replace package body pkg as
procedure pfk is
cnt pls_integer;
begin
select count(*) cnt_bind
into cnt
from fred
where v = k_female
and id > 30000
;
end;
procedure pmk is
cnt pls_integer;
begin
select count(*) cnt_bind
into cnt
from fred
where v = k_male
and id > 30000
;
end;
procedure pm is
cnt pls_integer;
begin
select count(*) cnt_no_bind
into cnt
from fred
where v = 'female'
and id > 30000
;
end;
procedure pf is
cnt pls_integer;
begin
select count(*) cnt_no_bind
into cnt
from fred
where v = 'male'
and id > 30000
;
end;
end;
/
show errors

=====================================================

D:\zzz\sql\tom>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Jan 31 14:49:58 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

idle> connect sys@flop as sysdba
Enter password:
Connected.
idle> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.00
idle> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

D:\zzz\sql\tom>sqlplus flip@flop

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Jan 31 14:50:33 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

flip@FLOP> ALTER session SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

Session altered.

Elapsed: 00:00:00.02
flip@FLOP> exec pkg.pfk

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.00
flip@FLOP> exec pkg.pmk

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
flip@FLOP> exec pkg.pf

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
flip@FLOP> exec pkg.pm

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
flip@FLOP> exit


D:\zzz\sql\tom>tkprof flop_ora_1228.trc hhhh12.txt sys=no

TKPROF: Release 9.2.0.1.0 - Production on Mon Jan 31 14:52:59 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

===========================>

SELECT count(*) cnt_bind
from fred
where v = :b1
and id > 30000


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.01 0 5 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.18 0.18 0 1758 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.18 0.20 0 1763 0 2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
16 TABLE ACCESS FULL FRED


SELECT count(*) cnt_no_bind
from fred
where v = 'male'
and id > 30000


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 865 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 865 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
16 TABLE ACCESS BY INDEX ROWID FRED
3856 INDEX RANGE SCAN FREDIDX (object id 31035)


SELECT count(*) cnt_no_bind
from fred
where v = 'female'
and id > 30000


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.12 0.12 0 879 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.12 0.12 0 879 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1296 TABLE ACCESS FULL FRED


So I executed in this order:

pkg.pfk
pkg.pmk
pkg.pf
pkg.pm

Now the same thing in this order (flush and everything):

pkg.pmk
pkg.pfk
pkg.pm
pkg.pf

SELECT count(*) cnt_bind
from fred
where v = :b1
and id > 30000


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.01 0 5 0 0
Execute 2 0.01 0.00 0 0 0 0
Fetch 2 0.56 2.67 1411 2678 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.57 2.68 1411 2683 0 2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1296 TABLE ACCESS BY INDEX ROWID FRED
374736 INDEX RANGE SCAN FREDIDX (object id 31037)


SELECT count(*) cnt_no_bind
from fred
where v = 'female'
and id > 30000


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.12 0.20 14 879 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.12 0.20 14 879 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1296 TABLE ACCESS FULL FRED


SELECT count(*) cnt_no_bind
from fred
where v = 'male'
and id > 30000


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 865 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 865 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
16 TABLE ACCESS BY INDEX ROWID FRED
3856 INDEX RANGE SCAN FREDIDX (object id 31037)


So the plans for the SQL with the constants pasted in are always the same ... the ones for the constants as binds depend on what gets peeked first ... hence, one or the other is different than the plans with no binds.

BTW ... I also had the 30000 defined as a packaged constant (bind+bind vs. pasted_in+pasted_in) and got the same behavior.

It is the peeking that's doing the _damage_. No CONSTANTs ... no problem.

<quote>both the queries with BINDS morphed to the same</quote>

Yes, that was the intent … to show that it is possible.

<quote>… but it would take some doing for that to "happen" in real life</quote>

True. In real life the programmer (having read that no routine should be more than _few_ lines) would’ve likely written just one function like:

function f (av in fred.v%type) return number is
cnt pls_integer;
begin
select count(*)
into cnt
from fred
where v = :av
and id > 30000
;
return cnt;
end;

and use the constants as calling parameters … which is nice and modular and easier to maintain and everything … except for that nasty peeking for wacky data distributions.

So, how about my pre-compiler woes … has that ever been considered within Oracle? Do you see any value in having one?

Hi Tom

What is the difference between Dynamic SQL inside a stored procedure and SQL send from Java Application using prepare statement through JDBC. To me both looks dynamic SQL and will have the same effect of soft parsing. Would you please enlighten me on this.

Rgds
Anil

Thanks Tom for the Clear answer
Rgds
Anil

Hi Tom,

I have written a procedure to test usage of bind variables;
(This is just for testing :) )

PROCEDURE TEST_BIND is

v_count number;
v_stmt varchar2(300);

begin
for x in (select object_name, owner
from dba_objects
where owner='XXX'
and object_type='TABLE')
loop

execute immediate 'insert into tab_list(tab_name) values(:vb_value)' using x.object_name;
dbms_output.put_line(x.object_name);
end loop;
end; --end test_bind

I have traced my session. And from the tkprof output I got the following extract;

********************************************************************************

insert into tab_list(tab_name)
values
(:vb_value)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 194 0.01 0.00 0 0 0 0
Execute 194 0.02 0.02 3 196 621 194
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 388 0.03 0.02 3 196 621 194

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 170 (recursive depth: 1)
********************************************************************************

When bind variables are used, it's supposed to be "parse once and execute many times" no ?

But why there are 194 parses ?

May be the way I have used bind variables is not correct ?
Can please explain ?

Thanks in advance.

>in 10g is it:
>
>if ( this statement != last statement )
>then
> close if needed
> open
> parse
>end if;
>bind
>execute

And there's a statement cached for each "call point" of "execute immediate" (in 10.1.0.3)!

dellera@ORACLE10> create or replace procedure test_cursors
2 as
3 l number;
4 begin
5 execute immediate 'select 1 from dual' into l;
6 execute immediate 'select 2 from dual' into l;
7 end;
8 /

dellera@ORACLE10> begin
2 for i in 1..100 loop
3 test_cursors;
4 end loop;
5 end;
6 /

********************************************************************************
select 1
from
dual

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.05 0.05 0 0 0 0
Fetch 100 0.00 0.00 0 0 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 201 0.05 0.05 0 0 0 100

********************************************************************************
select 2
from
dual

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.07 0.05 0 0 0 0
Fetch 100 0.00 0.00 0 0 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 201 0.07 0.05 0 0 0 100

If there were only a single "cache slot" for all calls to execute immediate, we would see 100 parses for each statement; instead we see 1, much like static sql.
Moreover it's easy to see that we have the statements cached:

dellera@ORACLE10> select sql_text from v$open_cursor where sid=(select sid from v$mystat where rownum=1);

SQL_TEXT
-------------------------------------------------------
select 1 from dual
select sql_text from v$open_cursor where sid=(select sid fro
select 2 from dual

Your answer above says:
"""
you parsed it, we soft parsed it. a parse is a parse is a parse. they can be
"hard", "soft" and "softer"
""
We know hard and soft parse, what is softer parse? pl explain.
In the same seesion, if same sql fireed and got it from shared pool, then it will be a softer parse. is it so?

Hi Tom,
I have been very impressed by your site. I have been trying to put up a question but to no vain. I bought your book but my question is really not covered anywhere i feel.
I have a simple query which doesnt come back at all.
select sum(wa.availability) total_zone_OU,wa.week_year_number, z.zone_id
from WEEKLY_AVAILABILITY wa, availability_submissions av, zones z,ZONE_SUBSTN_DEFN zs, bmgroup bg,bmu b
where wa.asn_id = av.id
and wa.availability_date between sysdate+2 and sysdate+365
and wa.submission_date = (select max(wa2.submission_date)
from WEEKLY_AVAILABILITY wa2,
availability_submissions av2
where wa2.submission_date <= sysdate
and wa2.asn_id = av2.id
and av2.bmu_id = av.bmu_id
and wa.week_year_number = wa2.week_year_number)
and wa.availability_date between z.start_date and nvl(z.end_date,wa.availability_date)
and z.zone_id = zs.zone_id
and zs.is_deleted = 0
and bg.substation_id = zs.substation_id
and wa.availability_date between bg.effective_from_date and NVL(bg.effective_to_date,wa.availability_date)
and bg.ngt_bmgroup_id = b.ngt_bmgroup_id
and b.bmu_id = av.bmu_id
group by wa.week_year_number,trunc(wa.availability_date),z.zone_id ;
--------------------
Then i wrote a query
------------------------
SELECT SUM(av.availability) total_zone_ou, av.week_year_number zone_week_year_number,TRUNC(av.availability_date)
zone_availability_date, bmu_zones.zone_id,DECODE(bmu_zones.zone_type, 1, 'IMPORT', 0, 'EXPORT', 'UNKNOWN ZONE TYPE') zone_type,
bmu_zones.zone_name, bmu_zones.description zone_description FROM (SELECT aa1.bmu_id, aa2.availability_date,
aa1.week_year_number, aa1.availability FROM (SELECT av.bmu_id, TRUNC(wa.availability_date)
availability_date, wa.week_year_number, wa.availability, wa.submission_date FROM availability_submissions av, weekly_availability wa
WHERE av.id = wa.asn_id) aa1, (SELECT MAX(wa2.submission_date) submission_date, av2.bmu_id, TRUNC(wa2.availability_date) availability_date, wa2.week_year_number
FROM weekly_availability wa2, availability_submissions av2 WHERE wa2.submission_date <= SYSDATE AND wa2.asn_id = av2.id AND wa2.availability_date >= SYSDATE+2 AND wa2.availability_date <= SYSDATE+365
GROUP BY av2.bmu_id, TRUNC(wa2.availability_date),wa2.week_year_number) aa2 WHERE aa2.submission_date = aa1.submission_date AND aa2.bmu_id = aa1.bmu_id AND aa2.week_year_number = aa1.week_year_number) av, (SELECT bmu_bmg.bmu_id, zs.zone_id, bmu_bmg.effective_from_date, bmu_bmg.effective_to_date, zs.start_date, zs.end_date, bmu_bmg.substation_id, zs.zone_type, zs.zone_name, zs.description
FROM (SELECT bmu_bmg_1.bmu_id, bmu_bmg_1.ngt_bmgroup_id, bmu_bmg_2.effective_from_date,
bmu_bmg_2.effective_to_date, bmu_bmg_2.substation_id FROM (SELECT bmu1.bmu_id, bmu1.ngt_bmgroup_id
FROM (SELECT bmu_id, ngt_bmgroup_id, timestamp FROM bmu) bmu1, (SELECT bmu_id, MAX(timestamp) timestamp
FROM bmu GROUP BY bmu_id) bmu2 WHERE bmu1.timestamp = bmu2.timestamp AND bmu1.bmu_id = bmu2.bmu_id) bmu_bmg_1, ( SELECT bmg1.ngt_bmgroup_id,
bmg1.effective_from_date, bmg1.effective_to_date,bmg1.substation_id FROM (SELECT ngt_bmgroup_id, effective_from_date, effective_to_date, timestamp,substation_id FROM bmgroup) bmg1, (SELECT ngt_bmgroup_id, MAX(timestamp)timestamp
FROM bmgroup GROUP BY ngt_bmgroup_id) bmg2 WHERE bmg1.timestamp = bmg2.timestamp AND bmg1.ngt_bmgroup_id = bmg2.ngt_bmgroup_id) bmu_bmg_2
WHERE bmu_bmg_2.ngt_bmgroup_id = bmu_bmg_1.ngt_bmgroup_id) bmu_bmg, (SELECT z_zs_1.zone_id,
z_zs_1.start_date, z_zs_1.end_date, z_zs_2.substation_id, z_zs_1.description, z_zs_1.zone_type, z_zs_1.zone_name
FROM (SELECT z1.zone_id, z1.start_date, z1.end_date, z1.description, z1.zone_type,z1.zone_name FROM (SELECT zone_id, start_date, end_date, zone_name, zone_type, description, is_deleted, timestamp
FROM zones) z1, (SELECT zone_id, MAX(timestamp) timestamp FROM zones GROUP BY zone_id) z2
WHERE z1.zone_id = z2.zone_id AND z1.timestamp = z2.timestamp
AND z1.is_deleted = 0) z_zs_1, (SELECT zs1.zone_id, zs1.substation_id FROM (SELECT zone_id, submission_date, is_deleted, substation_id
FROM zone_substn_defn) zs1, (SELECT zone_id, substation_id, MAX(submission_date) submission_date
FROM zone_substn_defn GROUP BY zone_id, substation_id) zs2
WHERE zs1.zone_id = zs2.zone_id AND zs1.substation_id = zs2.substation_id
AND zs1.submission_date = zs2.submission_date AND zs1.is_deleted = 0) z_zs_2
WHERE z_zs_1.zone_id = z_zs_2.zone_id) zs WHERE bmu_bmg.substation_id = zs.substation_id) bmu_zones
WHERE av.bmu_id = bmu_zones.bmu_id AND av.availability_date BETWEEN bmu_zones.effective_from_date AND
NVL(bmu_zones.effective_to_date, av.availability_date) AND av.availability_date BETWEEN bmu_zones.start_date AND NVL(
bmu_zones.end_date, av.availability_date) AND UPPER('both') IN ('BOTH', 'ZONAL') GROUP BY av.week_year_number, TRUNC(av.availability_date),
bmu_zones.zone_id, DECODE(bmu_zones.zone_type, 1, 'IMPORT', 0, 'EXPORT', 'UNKNOWN ZONE TYPE'), bmu_zones.zone_name, bmu_zones.description ORDER BY 5 ASC, 4 ASC, 6 ASC, 7 ASC
------------------------
This improved by the performance from 11 mins earlier query to 20 secs (Query 2). I can submit the Execution plans as well. I had to use Organization Index for my tables. The largest table is Weekly_availability and has 160000 records.

I really find "bind variable peeking" quite evil. I have faced many ERP queries related problems and many times are because of "bind variable peeking", when they are disabled query goes fast again!

everytime I faced these problematic queries, when bind variable peeking is disabled problems goes away :-S

From above <quote> bind variable peeking is
disabled problems goes away <quote>

How do I disable it in 9i? Thanks.

is the partition_view_enabled parameter still available in 10g. 

SQL> alter session set partition_view_enabled=TRUE;

Session altered.

SQL> show parameter view
SQL> 

So I don't see it in the parameter list and more importantly, I don't see the effects in the access plan. 

Hi Tom,

Is it possible to trigger bind variable peeking when parsing with dbms_sql?
My observation (in 10053 trace) is that bv peeking is not used in this case. 
See test script:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> ALTER SESSION SET EVENTS '10053 trace name context forever';

Session altered.

SQL> declare
  2   l_theCursor     integer default dbms_sql.open_cursor;
  3   l_bind_var_value  integer;
  4   l_status       integer;
  5  begin
  6   --set bind variable
  7   l_bind_var_value := 1;
  8   dbms_sql.parse(  l_theCursor,  'select /* dbms_sql with 1 */ * from t where n = :n1', dbms_sql.native );
  9   Dbms_Sql.bind_variable( l_theCursor, ':n1', l_bind_var_value);
 10   l_status := dbms_sql.execute(l_theCursor);
 11   DBMS_OUTPUT.PUT_LINE('status  '||l_status);
 12  --
 13  end;
 14  /

PL/SQL procedure successfully completed.


the relevant fragment from the 10053 trace

*******************************************
Peeked values of the binds in SQL statement
*******************************************
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=00 csi=00 siz=24 off=0
  No bind buffers allocated

...
============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |     2 |           |
| 1   |  TABLE ACCESS FULL | T       |    11 |    33 |     2 |  00:00:01 |
-------------------------------------+-----------------------------------+

compare with the 10053 trace of a normal execution . . .

SQL> ALTER SESSION SET EVENTS '10053 trace name context forever';

Session altered.

SQL> execute :n1 := 1;

PL/SQL procedure successfully completed.

SQL> select /* run with 1 */ * from t where n = :n1;

the relevant fragment from the 10053 trace

*******************************************
Peeked values of the binds in SQL statement
*******************************************
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=08e10590  bln=22  avl=02  flg=05
  value=1

. . .

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |     2 |           |
| 1   |  TABLE ACCESS FULL | T       |     3 |     9 |     2 |  00:00:01 |
-------------------------------------+-----------------------------------+

Am I doing something wrong?
Thanks in advance for your explanation.

Jaromir D.B. Nemec

http://www.db-nemec.com

PS: my test environment

-- prepare environment
drop table t;
create table t as
select trunc(sqrt(rownum)) n from dual
connect by level < 100;
-- analyze with histogram
exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'t', method_opt=>'for all columns size 10');
 

Thanks Tom,

this is exactly my point. I'm looking for the possibility to set bind variables and parse / execute the statement without actually running it.
In other words to see the real execution plan (with peek on work) but as lightweight as with explain plan.
I found the workaround with jdbc 10g driver </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:19398056075583 <code>but I'd appreciate something more database focused:)

Regards,

Jaromir

Oh - ok

ops$tkyte@ORA10GR2> create table t
  2  as
  3  select 99 id, a.* from all_objects a;

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> update t set id = 1 where rownum = 1;

1 row updated.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create index t_idx on t(id);

Index created.

ops$tkyte@ORA10GR2> begin
  2     dbms_stats.gather_table_stats
  3     ( user, 'T',
  4       method_opt=>'for all indexed columns size 254',
  5           cascade=>TRUE );
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> @trace
ops$tkyte@ORA10GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte@ORA10GR2> declare
  2          l_99    number := 99;
  3          l_1     number := 1;
  4          cursor c1 is select count(object_type) from t bind_was_99 where id = l_99;
  5          cursor c2 is select count(object_type) from t bind_was_1 where id = l_1;
  6  begin
  7          open c1;
  8          open c2;
  9          close c1;
 10          close c2;
 11  end;
 12  /

PL/SQL procedure successfully completed.

Just open and close the cursor - tkprof will show:

SELECT COUNT(OBJECT_TYPE) FROM T BIND_WAS_99 WHERE ID = :B1

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT AGGREGATE (cr=0 pr=0 pw=0 time=25 us)
      0   TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=8 us)

--------------------------------------------------------------------------------
SELECT COUNT(OBJECT_TYPE) FROM T BIND_WAS_1 WHERE ID = :B1

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT AGGREGATE (cr=0 pr=0 pw=0 time=58 us)
      0   TABLE ACCESS BY INDEX ROWID T (cr=0 pr=0 pw=0 time=27 us)
      0    INDEX RANGE SCAN T_IDX (cr=0 pr=0 pw=0 time=17 us)(object id 56675)


And if you need to do this "dynamically", perhaps you dynamically construct the above block and execute immediate it. 

Hi Tom,

<quote>
in 10g is it:

if ( this statement != last statement )
then
close if needed
open
parse
end if;
bind
execute
</quote>

Does this mean that there is no difference between Dynamic SQL using bind variables & static SQL from release 10g onwards(Apart from the fact that Dynamic SQL looks really ugly & untidy)? Or are there any more performance overheads involved with Dynamic SQL ?

Tom,
Your explanation of bind variables is excellent. However, I am unable to comprehend from an application context, how any one could ever code an application without using bind variable. Can you give a real life example of such bad coding? I am unable to understand from the examples how some one could hard code a user input value.

Thanks

Tom,
When we do a "INSERT INTO TABLE A (a,b,c) SELECT p,q,r FROM TABLE B" is there an implicit bind of some kind at work? Please explain

Thanks

Hi Tom,

please correct me if I wrong: There is no Dictionary-View available where I can find the values of the bind variables?
The only way to find the values is TKPROF?

Bye,

Rumburak

Ok, then I have to be careful with grants on this view whe we go on 10g. I don't want, that somebody would see the entered passwords of my own authorization function.

Bye,

Jens

CASE 1
-----------------------

DECLARE
 v_emp   emp.empno%TYPE;
 v_ename emp.ename%TYPE;
BEGIN
 v_emp:=7566;
 SELECT ename INTO v_ename
   FROM emp
  WHERE empno=v_emp;
 SELECT ename INTO v_ename
  FROM emp
 WHERE empno=v_emp;
END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.09          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.09          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 57  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        5.66          5.66
********************************************************************************

SELECT ENAME 
FROM
 EMP WHERE EMPNO=:B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.01       0.05          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.01       0.05          0          4          0           2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=30 us)
      1   INDEX UNIQUE SCAN EMP_PRIMARY_KEY (cr=1 pr=0 pw=0 time=18 us)(object id 132142)




********************************************************************************

Why the soft parse is getting soft parse of the above two statments individually though i am using bind variables?


When i use the same query within sp and then execute this sp 3 times its getting parse one and execute 3 times

CASE 2
-----------

CREATE OR REPLACE PROCEDURE p_test (v_emp  emp.empno%TYPE) AS
v_ename emp.ename%TYPE;
BEGIN
 SELECT ename INTO v_ename
   FROM emp
  WHERE empno=v_emp;
END;
BEGIN
 p_test(7566);
 p_test(7566);
 p_test(7566);
END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.07          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.07          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 57  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        5.07          5.07
********************************************************************************

SELECT ENAME 
FROM
 EMP WHERE EMPNO=:B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      3      0.00       0.07          0          0          0           0
Fetch        3      0.00       0.00          0          6          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.00       0.07          0          6          0           3

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      3  TABLE ACCESS BY INDEX ROWID EMP (cr=6 pr=0 pw=0 time=87 us)
      3   INDEX UNIQUE SCAN EMP_PRIMARY_KEY (cr=3 pr=0 pw=0 time=47 us)(object id 132142)




********************************************************************************

how can i get rid the extra soft parses in first case without encapsulating the sql in sp?

raman

SQL> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 12'
  2  /

Session altered.

SQL> DECLARE
  2    v   emp.empno%TYPE;
  3  BEGIN
  4    EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH SHARED_POOL';
  5    SELECT empno INTO v
  6      FROM emp
  7     WHERE empno=7566;
  8    EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH SHARED_POOL';
  9    SELECT empno INTO v
 10      FROM emp
 11     WHERE empno=7566;
 12    EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH SHARED_POOL';
 13    SELECT empno INTO v
 14      FROM emp
 15     WHERE empno=7566;
 16  END;
 17  .
SQL> /

PL/SQL procedure successfully completed.

SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

As shared pool cache the identical statment and reuse the last identical statment plan,in above code i flushes the shared pool which causes to re-parse for each identical statment.Here is proof

SELECT EMPNO 
FROM
 EMP WHERE EMPNO=7566


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.03       0.01          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0          3          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.03       0.01          0          3          0           3

Misses in library cache during parse: 3
Optimizer mode: ALL_ROWS
Parsing user id: 57     (recursive depth: 1)

Misses in library cache during parse: 3 it means hard parsing for each statment due to get flushing the shared pool.

But when i use the same statment inside the loop and get flushed the shared pool at each iteration it causes to age out the Misses in library cache during execute ,here is code

SQL> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 12'
  2  /

Session altered.

SQL> DECLARE
  2    v   emp.empno%TYPE;
  3  BEGIN
  4    FOR m IN 1..3
  5    LOOP
  6    EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH SHARED_POOL';
  7    SELECT empno INTO v
  8      FROM emp
  9     WHERE empno=7566;
 10    END LOOP;
 11  END;
 12  .
SQL> /

PL/SQL procedure successfully completed.

SQL> DISCONNECT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

and here is tkprof

SELECT EMPNO 
FROM
 EMP WHERE EMPNO=7566


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      3      0.00       0.01          0          0          0           0
Fetch        3      0.00       0.00          0          3          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.01       0.01          0          3          0           3

Misses in library cache during parse: 1
Misses in library cache during execute: 2
Optimizer mode: ALL_ROWS
Parsing user id: 57     (recursive depth: 1)

Misses in library cache during execute: 2 ,my question is why misses in library cache during execute is not in first case although i am flushing the shared pool which may cause to age out the plan?

as well if i am getting the shared pool flush why its not reparsing the statment at each iteration like in first scenario?

raman

first time through loop you

a) flush pool
b) open a cursor, parse it (miss during parse = 1 now)

plsql does NOT close the cursor, cursor open

/*this opened cursor are within the shared area like shared SQL area m i right??*/

second time through loop you

a) flush pool

/* Oracle process PL/SQL program in the same fashion as it does for sql and allocate a
shared area to PL/SQL within shared pool,upon flushing at this step the whole opened cursor
should be broken and should be reparsed for subsequent calling and should be exposed by misses in library cache
during parse cause misses in library cahce during execute tell us aging out the plan and indicated reoptimization*/


Tom what is implicit parse?

i am also not getting yours this point too

b) execute cursor, which does an implicit 'parse' which is counted as a miss during execute
raman

"Let us implicitly parse the sql again so I can execute it"

k but why it is not exposed by misses in libraray cache during parse because it is parsing again the whole process syntax,symentic ,optimization and fetch.

its k misses in libraray cahce during execute but it is also going to parse again.Why parsing not exposing?

raman

you did not make a parse call, you made an execute call. there was a miss in the library cache during the EXECUTION of that already parsed by you sql statement.

we implicitly re-optimized it for you.

Tom sorry to bothering you again may be i am not understanding ,i am confused how will you re-optimized the statment which is flushed at second time from shared pool,you dont have parse tree for which you will re optimized.how will you re optimized when everything is gone from there ?guess you will reload the statment and do syntax,semantic,optimization and fetch which is hard parse

raman

Tom thanx a lot now i understand what the story behind misses in library cahce during execute tom please just last question,just want to make sure that what i understnad from yours that following points.

while true that much of what it means to "parse" is done, it is not a parse call from the client - hence it is reported differently here.

you mean to say misses in libraray cahce do the parse again but not from client like we parse,it does the same like we do , there is no longer the statment in shared pool during execute so it does the parse step (syntax ,semantic,optimization and fetch) am i right tom?

one more thing tom (syntax ,semantic,optimization and fetch) is the step for hard parsing if it doing repeteadly.Its k we are not doing these above step from client site but it is doing ,if we found misses in libraray cache during execute then it should be alarming situation to avoid (syntax ,semantic,optimization and fetch) it am i right tom?

raman

Tom as you said implicit parse will show in tkprof report as misses in library cache parse or misses during execute its not from the client site but when cga lost the point from sga which is no longer exists that was the execution plan then it does the implicit parse (it re-populates the library cache with whatever it needs) and then if i dare to say "whatever it needs" mean it does the implicit step (syntax,symentic,optimization and fetch) then why its only showing the count in misses library cache during execute only, its k it should show misses in library cache during execute cause the cga refrences lost everything from sga during execute but it is also repopulating (i.e implicit step syntax,symentic,optimization and fetch) ,it should also show the number in tkprof report misses in library cahce during parse due to repopulating.

raman

tom when i use literals inside the loop it doesnt show me misses in library cahce,when i use bind variable it shows me misses in libraray cahce during execute why diffrent beahviour ?

********************************************************************************

DECLARE
 l_empno   NUMBER;
BEGIN
 FOR m IN 1..1000
 LOOP
  SELECT empno INTO l_empno
    FROM emp
   WHERE empno=7566;
 END LOOP;
END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.08          0         18          0           0
Execute      1      0.03       0.04          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.06       0.13          0         18          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        2.63          2.63
********************************************************************************

SELECT EMPNO 
FROM
 EMP WHERE EMPNO=7566


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   1000      0.03       0.01          0          0          0           0
Fetch     1000      0.01       0.00          0       1000          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2001      0.04       0.03          0       1000          0        1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  INDEX UNIQUE SCAN EMP_PRIMARY_KEY (cr=1000 pr=0 pw=0 time=5633 us)(object id 132142)

DECLARE
 l_empno   NUMBER;
 v_empno   NUMBER:=7566;
BEGIN
 FOR m IN 1..1000
 LOOP
  SELECT empno INTO l_empno
    FROM emp
   WHERE empno=v_empno;
 END LOOP;
END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.09          0         18          0           0
Execute      1      0.04       0.05          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.07       0.14          0         18          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        2.44          2.44
********************************************************************************

SELECT EMPNO 
FROM
 EMP WHERE EMPNO=:B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   1000      0.04       0.04          0          0          0           0
Fetch     1000      0.00       0.00          0       1000          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2001      0.04       0.05          0       1000          0        1000

Misses in library cache during parse: 1
Misses in library cache during execute: 1<------
Optimizer mode: ALL_ROWS
Parsing user id: 57     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  INDEX UNIQUE SCAN EMP_PRIMARY_KEY (cr=1000 pr=0 pw=0 time=5740 us)(object id 132142)

tom one more question plz as you said

"You get the number of times we re-optimized upon executing the statement. (misses in library cache during execute call) "

what re-optmized is it (syntax,semantic,optimize and fetch)?

raman

many many thanx tom,here is my script..

First time

SQL> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 12'
  2  /

Session altered.

SQL> DECLARE
  2   l_empno   NUMBER;
  3   v_empno   NUMBER:=7566;
  4  BEGIN
  5   FOR m IN 1..1000
  6   LOOP
  7    SELECT empno INTO l_empno
  8      FROM emp
  9     WHERE empno=v_empno;
 10   END LOOP;
 11  END;
 12  .
SQL> /

PL/SQL procedure successfully completed.

SQL> DISCONNECT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

TKPROF RESULT

DECLARE
 l_empno   NUMBER;
 v_empno   NUMBER:=7566;
BEGIN
 FOR m IN 1..1000
 LOOP
  SELECT empno INTO l_empno
    FROM emp
   WHERE empno=v_empno;
 END LOOP;
END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.17          0          0          0           0
Execute      1      0.06       0.20          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.06       0.38          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        6.15          6.15
********************************************************************************

SELECT EMPNO 
FROM
 EMP WHERE EMPNO=:B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   1000      0.01       0.29          0          0          0           0
Fetch     1000      0.01       0.00          0       1000          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2001      0.03       0.30          0       1000          0        1000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  INDEX UNIQUE SCAN EMP_PRIMARY_KEY (cr=1000 pr=0 pw=0 time=5680 us)(object id 132142)




********************************************************************************

Second Time

SQL> CONN SCOTT/TIGER
Connected.
SQL> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 12'
  2  /

Session altered.

SQL> DECLARE
  2   l_empno   NUMBER;
  3   v_empno   NUMBER:=7566;
  4  BEGIN
  5   FOR m IN 1..1000
  6   LOOP
  7    SELECT empno INTO l_empno
  8      FROM emp
  9     WHERE empno=v_empno;
 10   END LOOP;
 11  END;
 12  .
SQL> /

PL/SQL procedure successfully completed.

SQL> DISCONNECT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

TKRPROF

********************************************************************************

DECLARE
 l_empno   NUMBER;
 v_empno   NUMBER:=7566;
BEGIN
 FOR m IN 1..1000
 LOOP
  SELECT empno INTO l_empno
    FROM emp
   WHERE empno=v_empno;
 END LOOP;
END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.09          0         18          0           0
Execute      1      0.01       0.05          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.03       0.14          0         18          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        2.29          2.29
********************************************************************************

SELECT EMPNO 
FROM
 EMP WHERE EMPNO=:B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   1000      0.06       0.04          0          0          0           0
Fetch     1000      0.01       0.00          0       1000          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2001      0.07       0.05          0       1000          0        1000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  INDEX UNIQUE SCAN EMP_PRIMARY_KEY (cr=1000 pr=0 pw=0 time=5677 us)(object id 132142)


********************************************************************************

raman

Dear Tom,
If I have a sql like this:
SELECT ... FROM employees WHERE emp_id = p_emp_id OR p_emp_id IS NULL;

when different parameters are used on p_emp_id (null and not null) will it result in different plans?

Thank you.

how i will avoid delayed BINDING to safe deffred parse.

raman

ahh, it looks like you tripped a deferred parse.

"where empno = :b1" - plsql would parse that - but not optimize it (until the bind is supplied) and when it executed it it - the deferred parse would kick in. So, it would 'miss' on the first execute.

tom as you said it is defferred parse,plsql parse but not optimize it unless bind is not supplied when the bind comes to supply it optimize and it kicks the previous parsed one (which was not optimized).

i want to why delay in bind how can i avoid delay in binding?

raman

tom i assume that it degrades performance please correct me if i am wrong at this assumption?

tom the same scenario when i do at my home pc it does not delay in binding variable why its not at my home pc?

raman

tom its the same at my home pc i was wrong or be i was napping at that time.tom you said it is a performance feature.how can be it is performance feature a delay is delay and why it is if it is then it degrades a performance because it is delay.

raman

Good evening Tom

I was glad when I saw a 11g feature called Adaptive Cursor Sharing, it should be a big one since bind peeking ha given us in the past quite a few problems.

So now with ths feature bind peeking happens several times in a same query with binds but different values. So far so good when I just noticed it does not work with packages or procedures and functions.

This is because IMHO PL/SQL only parse once and execute many, very nice feature as well.

So is there any way to use adaptive cursor sharing inside PL/SQL?

Thank you

Best Regards

Pinguman

Tom,

We have various production issues with bind peeking causing performance problems, specifically because grants on an underlying object are causing invalidations, and the SQL cursor is being reparsed frequently.

Given that the plan may change over time, how do we know when we tkprof the 10046 trace output that we are getting the same plan that actually executed? Does the 10046 trace store plan information, or pointers to historic plan information?

Thanks.

Hi Tom,

I do agree that bind variables are very essential when it comes to Scalability of an Application. What is the performance impact (Latches and Scalability), if an application executes a procedure thousands of times with different input values and all these are passed as Literals ? In this case, I can see multiple copies of these procedures. For example :

exec test('01-APR-2008',:p_out);
exec test('12-JUN-2008',:p_out);

Assuming this procedure TEST runs through out the day and with different Input Dates. Will I see any performance benefit, if this Input date is passed as a bind variable, like

exec test(:p_in,:p_out);

Would appreciate your reply.

Regards
VLS

In your reply to pinguman's question regarding adaptive cursor sharing on September 22, 2007, you said "you'd have to defeat the good practice of parse one, execute many. dynamic open c for 'string'". Can you please elaborate on that statement and, if possible, provide a simple example? Thank you.

is
   l_cursor sys_refcursor;
begin
    open l_cursor for select .....
    fetch l_cursor into ...
    loop
        exit when l_cursor%notfound;
        ....
        fetch l_cursor into ....
    end loop;
    close l_cursor;
end;

Is this approach also valid way of dealing with the problem without using context as you posted here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279

For example it would look like this:
1. Value passed during creation of where part of the query.
For example you provided it would be:

WHERE   x = pkg1.getvalof(i);

2. Call function with parameter name which would use global package record type filled during creation of query(similar to context method).
For example you provided it would be:

WHERE   x = pkg1.getvalueof('parameter_name');

We use Oracle 10.2.0.4 if that matters at all in this case.

 14    if ( p_ename is NOT NULL )
 15    then
 16      l_query := l_query ||
 17      ' where ename like
 18          ''%''||:p_ename||''%'' ';
 19    else
 20      l_query := l_query ||
 21      ' where (1=1 or
 22          :p_ename is null) ';
 23    end if;

Hi Tom,

Below is small part of procedure that i am running. There is a perticular sql query (marked in bold) that gets repeated as part of where clause in cursor many times in the procedure (approx. 15).

1. Can i take the output of the common query in a variable and use that variable in the where clause?
2. Is there a way I can use bind variables in place of the query?
3. Can you please suggest any better way to de this.

CREATE or REPLACE PROCEDURE PRC_DOWNLOADEXCEL_ALLDRs (v_user varchar2) AS
V_YEAR number;
V_YEAR1 number;
V_YEAR2 number;
V_DR varchar2(30);
n_TotalRecs number;

cursor cur_HR is select ORACLE_HR_ID, EMPLOYEE_NAME, EMPLOYEMENT_TYPE, SUPERVISOR_OHR_ID, HR_MGR_GLOBAL_OHRID, POLE, VINTAGE, BAND, PROCESS_CODE, QUALIFICATION, CURRENT_ADDRESS, PERMANENT_ADDRESS, WORK_LOCATION, EMERGENCY_CONTACT_NUM, EMAIL_ID, BLOOD_GROUP, MARITAL_STATUS, TOT_MONTH_PREV_EXP, LAST_INDUSTRY, SERVICE_AGREEMENT_END_DATE, RELEASABLE_BY, TENNURE_IN_ROLE, WORK_FROM_HOME, DOB, DOJ from sa_prod_emp_demographics Where Supervisor_OHR_ID in
(Select S.oracle_hr_id from sa_prod_emp_demographics s ,
(Select Supervisor_OHR_ID from SA_PROD_EMP_DEMOGRAPHICS Where HR_MGR_GLOBAL_OHRID = v_user and Supervisor_OHR_ID <> v_user and Oracle_HR_ID <> v_user Group by Supervisor_OHR_ID HAVING COUNT(*) > 0) q
where q.supervisor_ohr_id = s.ORACLE_HR_ID)
and HR_MGR_GLOBAL_OHRID = v_user Order by ORACLE_HR_ID;

BEGIN
SELECT TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) INTO V_YEAR FROM DUAL;
V_YEAR1 := V_YEAR - 1;
V_YEAR2 := V_YEAR - 2;
for i in cur_HR loop
INSERT INTO TMPHIERARCHY (OHR_ID, IDLEVEL) VALUES (i.ORACLE_HR_ID, V_YEAR2);
INSERT INTO TMPHIERARCHY (OHR_ID, IDLEVEL) VALUES (i.ORACLE_HR_ID, V_YEAR1);
INSERT INTO TMPHIERARCHY (OHR_ID, IDLEVEL) VALUES (i.ORACLE_HR_ID, V_YEAR);
end loop;

Declare
Cursor CNB6 is Select D.Oracle_HR_ID Oracle_HR_ID, nvl(SALARY_PAID,'Not Available') SALARY_PAID, LEAVE_BALANCE, TO_CHAR(LAST_PROMOTION_DATE,'ddth Mon yyyy') LAST_PROMOTION_DATE, nvl(TO_CHAR(LAST_INCREMENT_DATE,'ddth Mon yyyy'),'Not Available') LAST_INCREMENT_DATE, nvl(INCREMENT_REASON,'Not Available') INCREMENT_REASON, nvl(TO_CHAR(INCREMENT_DUE_DATE,'ddth Mon yyyy'),'Not Available') INCREMENT_DUE_DATE, nvl(TO_CHAR(LAST_LEAVE_TAKEN_DT,'ddth Mon yyyy'),'Not Available') LAST_LEAVE_TAKEN_DT, nvl(LEAVE_TYPE,'Not Available') LEAVE_TYPE,nvl(trim(D.POLE),'GENPACT INDIA') POLE From Sa_Prod_emp_demographics D Left Join SA_PROD_EMP_LEAVES L on D.Oracle_HR_ID = L.Oracle_HR_ID Where D.Oracle_HR_ID in
(select ORACLE_HR_ID from sa_prod_emp_demographics Where Supervisor_OHR_ID in
(Select S.oracle_hr_id from sa_prod_emp_demographics s ,
(Select Supervisor_OHR_ID from SA_PROD_EMP_DEMOGRAPHICS Where HR_MGR_GLOBAL_OHRID = v_user and Supervisor_OHR_ID <> v_user and Oracle_HR_ID <> v_user Group by Supervisor_OHR_ID HAVING COUNT(*) > 0
) q where q.supervisor_ohr_id = s.ORACLE_HR_ID
) and HR_MGR_GLOBAL_OHRID = v_user
) Order by ORACLE_HR_ID;
Begin
for i in CNB6 loop
htp.p('<tr>
<td class="Row2">'||i.ORACLE_HR_ID||'</td>
<td class="Row2">'||i.SALARY_PAID||'</td>
<td class="Row2">'||i.LEAVE_BALANCE||'</td>
<td class="Row2">'||i.LAST_PROMOTION_DATE||'</td>
<td class="Row2">'||i.LAST_INCREMENT_DATE||'</td>
<td class="Row2">'||i.INCREMENT_REASON||'</td>
<td class="Row2">'||i.INCREMENT_DUE_DATE||'</td>
<td class="Row2">'||i.LAST_LEAVE_TAKEN_DT||'</td>
<td class="Row2">'||i.LEAVE_TYPE||'</td>
</tr>');
end loop;
end;

Declare
n_Year number;
n_CTC varchar2(55);
n_CTCPer varchar2(50);
v_CTCCurr varchar2(50);
Cursor CNB1 is select T.OHR_ID, idlevel, decode(nvl(ctc_emp,0),0,'Not Available',(ctc_emp)) ctc_emp, decode(ctc_percentage_incr,null,'Not Available',round(ctc_percentage_incr,2) || '%') n_CTCPer, currency_code from tmphierarchy t left join SA_PROD_COMPENSATION_BONUS S on T.OHR_ID = S.Oracle_hr_id and idlevel = ctc_year where ohr_id in
(select ORACLE_HR_ID from sa_prod_emp_demographics Where Supervisor_OHR_ID in
(Select S.oracle_hr_id from sa_prod_emp_demographics s ,
(Select Supervisor_OHR_ID from SA_PROD_EMP_DEMOGRAPHICS Where HR_MGR_GLOBAL_OHRID = v_user and Supervisor_OHR_ID <> v_user and Oracle_HR_ID <> v_user Group by Supervisor_OHR_ID HAVING COUNT(*) > 0
) q where q.supervisor_ohr_id = s.ORACLE_HR_ID
) and HR_MGR_GLOBAL_OHRID = v_user
) Order by ORACLE_HR_ID, idlevel desc;
Begin
n_TotalRecs := 0;
Open CNB1;
Loop
Fetch CNB1 into V_DR, n_Year, n_CTC, n_CTCPer, v_CTCCurr;
if n_TotalRecs = 0 and CNB1%notfound then
htp.p('<tr><td colspan="5" class="Row3">Not Available</td></tr>');
end if;
Exit When CNB1%NotFound;
n_TotalRecs := n_TotalRecs + 1;
htp.p('<tr>
<td class="Row2">' || V_DR || '</td>
<td class="Row2">' || n_Year || ' </td>
<td class="Row2">' || n_CTC || ' </td>
<td class="Row2">' || n_CTCPer || ' </td>
<td class="Row2">' || nvl(v_CTCCurr,'&nbsp;') || ' </td>
</tr>');
End Loop;
Close CNB1;
Exception
When NO_DATA_FOUND then
htp.p('<tr><td colspan="5" class="Row3">Not Available</td></tr>');
When Others then
htp.p('<tr><td colspan="5" class="Row3">' || sqlerrm || '</td></tr>');
End;
END;

Thanks Tom, That clarifies most of my queries.

Just to add to that, from perfomrance perspective,
1. Will it be better to use a temperory table or a perameterized cursor in place of that query?
2. Is better to declare a cursor and then call it in For loop
Eg.
Declare
cursor cur1 <select stmt>
Begin
For i in cur1
----
---
OR
Is it better to put the select statement in For itself
Eg.
For i in <select stmt>

Thanks again for your help :)

Hi Tom,

I have a doubt on 11g adaptive cursor sharing and pl/sql procedure, function or packages, based on query by Pinguman in this thread.

"This is because IMHO PL/SQL only parse once and execute many, very nice feature as well.
So is there any way to use adaptive cursor sharing inside PL/SQL?"

Does that mean adaptive cursor sharing is not used for queries inside a pl/sql procedure?

If a pl/sql procedure P1 has two queries sql1 and sql2 (no dynamic sql) during compiling the procedure the queries get parsed?
I think the parsing of the queries will happen only during the first execution of the query right?
If not, bind variable peeking wiil not happen for the query because at compile time the values for the bind variable are unknown?

Can you clarify this.?
Thanks in advance for your precious time.