tkprof
mo, February 13, 2003 - 10:31 am UTC
Tom:
I replicated your examples and set sql_trace=true but I do not see the tkprof output as you show. Any ideas why?
Thanks
February 13, 2003 - 10:41 am UTC
umm, did you run tkprof -- the command line tool that turns the trace file that was generated in the user dump destination on the server into a readable file?
see chapter 10 of "the book". I go into how to use tkprof in depth.
For Bhavani
Paul, February 13, 2003 - 10:46 am UTC
If you really want to see the effect of not using bind variables use this:
begin
for i in 1..1000000 loop
execute immediate 'insert into t1 values('||i||')';
end loop;
end;
/
Unfortunately, I don't have the time (unlike mo, apparently ;-))
Bind Variable Peeking feature
Vikas, May 21, 2003 - 8:09 am UTC
Hi Tom,
Can you please explain me the concept of Peeking of User-Defined Bind Variables which is a new feature for 9.0.1
It would be nice if you can come up with a demo example.
Thanks
Vikas
May 21, 2003 - 9:34 am UTC
basically -- it means when the query is first hard parsed, the optimizer will peek at the binds in order to determine how to optimize the query. Stress the word hard there, it is not every parse, only on the hard parse.
So, setup a test like this:
drop table t;
create table t
as
select 1 id, a.* from all_objects a;
update t set id = 99 where rownum = 1;
create index t_idx on t(id);
analyze table t compute statistics
for table
for all indexes
for all indexed columns size 2;
massively skewed data. index on t(id) should be used if id is NOT 1... So, we run these queries against this data. Note that we run 2 queries -- and each one twice. the one tagged N_WAS_1 is hard parsed with the bind set to the value of 1. the one taggedt with N_WAS_99 is hard parsed with the bind set to the value of 99
variable n number;
exec :n := 1;
alter session set sql_trace=true;
set termout off
select * from t n_was_1 where id = :n;
exec :n := 99;
select * from t n_was_99 where id = :n;
and now we execute (using soft parses) the SAME queries -- but with the inputs flipped flopped:
select * from t n_was_1 where id = :n;
exec :n := 1;
select * from t n_was_99 where id = :n;
exit
Now, I ran tkprof with AGGREGATE=NO so we would see each of the 2 queries 2 times:
select * from t n_was_1 where id = :n
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 2004 0.41 0.39 124 2404 0 30045
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2006 0.41 0.39 124 2404 0 30045
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 126
Rows Row Source Operation
------- ---------------------------------------------------
30045 TABLE ACCESS FULL T (cr=2404 r=124 w=0 time=174004 us)
That is what you expect when the bind was set to 1 during the hard parse. A full tablescan. On the other hand:
********************************************************************************
select * from t n_was_99 where id = :n
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 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 126
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=3 r=0 w=0 time=116 us)
1 INDEX RANGE SCAN T_IDX (cr=2 r=0 w=0 time=73 us)(object id 35628)
that is the plan you expect if the bind is 99 -- index range scan. But watch out:
********************************************************************************
select * from t n_was_1 where id = :n
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.01 0.01 124 428 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 124 428 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 126
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL T (cr=428 r=124 w=0 time=19046 us)
here the bind variable is in fact 99 if you remember from the test case -- we can see that by the very number of rows. The PLAN WAS FIXED DURING THE HARD PARSE BASED ON THE INITIAL INPUTS
********************************************************************************
select * from t n_was_99 where id = :n
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 2004 0.60 0.58 206 4445 0 30045
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2006 0.60 0.58 206 4445 0 30045
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 126
Rows Row Source Operation
------- ---------------------------------------------------
30045 TABLE ACCESS BY INDEX ROWID OBJ#(35627) (cr=4445 r=206 w=0 time=338282 us)
30045 INDEX RANGE SCAN OBJ#(35628) (cr=2059 r=0 w=0 time=154266 us)(object id 35628)
same here -- now the index range scan isn't appropriate
So where does this come into play, when is this a "good thing" (this example shows it can be a confusing thing. Just think, you start up on monday morning and the first query you submit uses a bind of :N := 1, full scan plan that week. You start up next monday and the first query you submit using a bind of :N := 99 -- index range scan plan that week).
Well, suppose you use LIKE lots -- this could be very beneficial. Your application always uses LIKE without leading '%' -- meaning an index would be helpful generally. Here, the optimizer will parse
select * from t where t like :x
as if :x had a value, was a literal. That would lead it towards the index. Consider:
create table t
as
select a.* from all_objects a;
create index t_idx on t(object_name);
analyze table t compute statistics
for table for all indexes for all indexed columns
/
variable x varchar2(50)
@trace
set termout off
exec :x := '%';
select * from t x_was_percent where object_name like :x;
exec :x := 'Y%';
select * from t x_was_NOT_PCT where object_name like :x;
exit
tkprof shows us this time:
select *
from
t x_was_percent where object_name like :x
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 2005 0.41 0.41 182 2391 0 30046
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2007 0.41 0.41 182 2391 0 30046
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 126
Rows Row Source Operation
------- ---------------------------------------------------
30046 TABLE ACCESS FULL T (cr=2391 r=182 w=0 time=189486 us)
select *
from
t x_was_NOT_PCT where object_name like :x
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 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 126
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID OBJ#(35635) (cr=2 r=0 w=0 time=70 us)
0 INDEX RANGE SCAN OBJ#(35636) (cr=2 r=0 w=0 time=64 us)(object id 35636)
but since you never parse with :x := '%' and the index should be used here, this bind variable "peeking" works to your advantage
Sagi, May 21, 2003 - 10:10 am UTC
Hi Tom,
In the demo for the reply to Bhavani's question, you said
"They were soft parses but a parse is a parse and something to be avoided when possible."
So the parses in the output of tkprof is soft parses? If yes then how do we see hard parses out of the tkprof.
It would be great, if you could give a demo with tkprof output that explains soft and hard parses.
Regards,
Sagi.
May 21, 2003 - 11:11 am UTC
I covered tkprof is great detail in my book "Expert one on one Oracle" but in a tkprof -- it is trivial to see the hard/soft parses.
you will see a parse count (soft+hard)
you will see a "misses in library cache" and this will generally be 0 or 1. If it is 0, all of the parses were soft. If it is not zero, that is the number of hard parses it underwent.
"Bind Variables", version 8.1.6
Raj, May 21, 2003 - 10:29 am UTC
Absolutely proven!
Sagi, May 21, 2003 - 11:24 am UTC
Thanx tom.
As usual you are very great.
Regards,
Sagi.
How come my explain plans are different ?
Ma$e, October 16, 2003 - 11:19 pm UTC
Hi Tom:
I have a few Questions w.r.t to the explain plans generated by the tkprof.
How come your sample displays more info in the row source section than what I get ? And what do those numbers mean ?
Also: How come I don't see the "row source" section for the last sql statement ?
What is the difference between the explain plans in the "row source operation" and the "explain plan section".
Which one should we use to determine the explain plan used at run-time.
I'm using V 9.2.0.1.0 EE
Here are my results
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 82 (SCOTT)
********************************************************************************
select *
from
t n_was_1 where id = :n
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 2065 0.25 0.55 180 2471 0 30956
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2067 0.25 0.55 180 2471 0 30956
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 82 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
30956 TABLE ACCESS FULL T
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
30956 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T'
********************************************************************************
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 82 (SCOTT)
********************************************************************************
select *
from
t n_was_99 where id = :n
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 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 82 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T
1 INDEX RANGE SCAN T_IDX (object id 34487)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T'
********************************************************************************
select *
from
t n_was_1 where id = :n
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.17 163 440 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.17 163 440 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 82 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL T
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T'
********************************************************************************
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 82 (SCOTT)
********************************************************************************
select *
from
t n_was_99 where id = :n
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 2065 0.36 3.05 179 4593 0 30956
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2067 0.36 3.05 179 4593 0 30956
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 82 (SCOTT)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T'
********************************************************************************
Thanks
Ma$e
October 17, 2003 - 9:43 am UTC
9202 added:
INDEX RANGE SCAN OBJ#(35636) (cr=2 r=0 w=0 time=64 us)
which shows the consistent reads (2), physical reads (0), physical writes (0) and time (65) millionths of a second. so, in 9202, you'll start seeing this additional, useful data.
As for not seeing it for the last statement -- the STAT records (with the rowsources) are only put into the trace file when the cursors are closed -- so you must not have exited sqlplus or whatever tool you were using, the last cursor, still open.
I would suggest NOT USING explain=u/p with tkprof. It
a) makes it run lots slower
b) shows the plan that would be used in the environment that tkprof had when it connected
that last point means that if the application had some some alter session or used a different schema, or if some other index existed at runtime or if stats had been gathered -- whatever, the EXPLAIN PLAN will be not what was used -- but rather what would be used right now, in that session.
just don't use explain= anymore.
Excellent...I guess a good excuse to upgrade the test server
Ma$e, October 17, 2003 - 12:32 pm UTC
Hi Tom:
Just curious. Is this because the tkprof exposes this additional information in the report or is it now being collected by the trace utility in 9.2.2 ?
Thanks for your response.
Ma$e
October 17, 2003 - 12:56 pm UTC
tkprof is just reporting whats in the trace file:
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL OBJ#(222) (cr=3 r=0 w=0 time=103 us)'
thats the stat record from a 9203 trace -- the cr/r/w/time wont be there before.
Binds not good for predicates on columns with skewed data
Ma$e, October 17, 2003 - 1:08 pm UTC
Hi Tom:
Thanks..
Ma$e
October 17, 2003 - 2:07 pm UTC
let me phrase that differently.
binds -- mandatory on systems that parse many queries/second (transactional, no full scans going on there)
binds -- not mandatory on systems that have many seconds between parses (data warehouses, queries run for minutes or hours)
forget skewed for a moment -- the type of system will dictate it. why? you will either ALWAYS use an index or NEVER use an index in the system that parses queries/second (you are transactional there, everything should be short).
Following up on the original answer
Meyer, October 17, 2003 - 2:14 pm UTC
Think about a standard lookup on table with a
fname varchar2(50) column.
IN pl/sql, when you need to use a parameter passed into the procedure with wildcards "%" for starts with, ends with, or contains search against the varchar2 column.
1)Does the wildcard affect parsing and if yes how, do you ever consider instr? What is your preferred method to code this basic thing.
2)Do the benefits of not parsing change w/ html-db regions w (:F4950_P9_DISPLAYID) vs pl/sql packages and standard local variables. In html-db I am talking about a sql-query regions or pl/sql-returning-sql-query regions.
3)In your books in pl/sql you sometimes use format ":name" with "using"...is this for example or are do you get a benefit that you would not get using a standard local pl/sql variable that turns into a bind?
Thanks,
Meyer
October 17, 2003 - 2:32 pm UTC
1) a %...% search on a varchar2 field with like is pretty much going to be "full scan city"
instr = full scan city as well.
Now, if it was a sentence in a varchar2 field and you were looking for a WORD in the sentence -- I would
create index t_idx on t(fname) indextype is ctxsys.context;
a Text index. then I can "where contains( fname, l_word ) > 0" rather then where fname like l_word;
2) when you have SQL in PLSQL -- all plsql variables ARE bind variables. It is the same either way -- both are bound into the statement.
3) when doing dynamic sql:
execute immediate 'update t set c = :name' using l_variable;
it is the only way to use binds with dynamic sql. I only "sometimes" use that construct when doing dynamic sql and I want to bind.
Thank You
Meyer, October 17, 2003 - 2:56 pm UTC
Why was peeking introduced in the first place
Bala, October 29, 2003 - 12:01 am UTC
Tom,
I don't really understand how Peeking really helps queries chose the best plan? as you have demonstrated above it could work the other way round many times.
When we really need to run a query that needs to use histograms(typical dw queries) we could have used hard variables(where execution time is many many times the parsing or optimization time). I still can not make out how it helps. And one last thing, how can we switch off peeking in 9i?
October 29, 2003 - 6:37 am UTC
in a DW, you would generally not use binds since you run few queries that take long times.
in OLTP, you would always use binds since you run tons of queries, all that should complete very quickly, tons of times.
bind variable peeking is for OLTP, not for DW. You cannot switch it off -- you do not want to switch it off (to what end would switching it off be?). For the environment it was designed for, it does its job. the concept is -- you hard parse a query for the first time with some binds. in OLTP those binds are representative for the system - that query should (or should not) use index "X". This will make it so. That query will use index "X" all of the time. Elsewhere in your system is a similar query -- it is hard parsed for the first time with its representative inputs and could come to a different (yet correct) query plan since the inputs are "different"
Confused with your response.
Ma$e, November 01, 2003 - 11:24 pm UTC
Hi Tom:
Now I'm really confused.
In your response:
"...you start up on monday morning and the first query you submit uses a bind of :N := 1, full scan plan that week.
You start up next monday and the first query you submit using a bind of :N := 99 -- index range scan plan that week)."
This could spell disaster for our OLTP application where we have used bind vars and the data is skewed and if the first SQL that executes is resolved to using a plan that does not bode well with the next execution of the same query with different values for the bind vars.
Later on you comment:
"binds -- mandatory on systems that parse many queries/second (transactional, no full scans going on there)
binds -- not mandatory on systems that have many seconds between parses (data warehouses, queries run for minutes or hours)
forget skewed for a moment -- the type of system will dictate it. why? you will either ALWAYS use an index or NEVER use an index in the system that parses queries/second (you are transactional there, everything should be short)."
I'm having problem digesting the last part of the above response "ALWAYS .... or NEVER". But in your example of bind var peeking you proved that you want to encourage use indexes sometimes and fts other times.
Can you please clarify my confusion or lack of understanding.
Thanks
Ma$e
November 02, 2003 - 10:04 am UTC
well, it won't in reality -- will it.
You see -- your OLTP application, it will NEVER NEVER use ":n = 1" will it? (if it did, it would be the kiss of death for it would it not?)
You see -- your application is going to run a query at "point X in the code". THAT query should either
a) use an index or
b) not use an index
each and every time at that point in the code. Bind variable peeking is the tool that permits this. Without bind variable peeking the query would always either full scan or index range scan regardless of where in the code it was! With bind variable peeking -- the query will be able to use one or the other plan -- depending on WHO (what part of the code) is executing it.
This is really what you want. Really -- especially -- ESPECIALLY in oltp! Without bind variable peeking, skewed data would almost certainly imply "full scan" as the number of distinct values would be insufifcient for the optimizer to range scan. Only by looking at representative inputs for your query -- based on where in the code it executes -- will you get the right answer!
That is what I meant by that always or never. If you have a piece of code in your OLTP system that one time the query is executed uses an index range scan (few rows) and then later would want to full scan (tons of rows) -- you have totally unpredicable response patterns/usage there. Some users get immediate responses -- others get responsed in minutes. Something really wrong there -- therefore it would either ALWAYS range scan there (interactive) or ALWAYS full scan (batch report).
Beginning to make some sense....
Ma$e, November 03, 2003 - 3:32 am UTC
Thanks Tom.
It seems I am having a total eclipse in comprehension.
"each and every time at that point in the code. Bind variable peeking is the
tool that permits this. Without bind variable peeking the query would always
either full scan or index range scan regardless of where in the code it was!"
But I thought that the peeking is only performed when the query is hard parsed. I think we agree on that.
Also: Since the app will always generate the same sql syntax at the same point in the code (irrespective of who ran the code and how it got there) except for the value of the bind, the peeking will only be done the first time (at which point the plan will be generated) and not subsequently.
You are not implying that instead of using bind in my code, we use "cursor_sharing=force" ? I don't think so.
I'm in synch with everything you said in the last response (I think).
Thanks for your patience and dedication.
If I get over this issue, I'll owe you a drink/dinner at the next OOW.
Ma$e
November 03, 2003 - 7:19 am UTC
what I'm saying is -- ok, you have a piece of code. It does a poplist, populates some list box.
that piece of code will either ALWAYS index range scan (most likely) or NEVER. The design of your interactive OLTP application is such that it is ALWAYS going to use inputs that permit an efficient index range scan -- or, it is not. It is the very nature of your application here.
bind variable
Dillip K. Praharaj, December 06, 2003 - 7:06 pm UTC
Oracle uses the literal value for hard parsing a SQL because of _optim_ppek_user_binds set to true in Oracle 9i.
So it means that the plan could be different than what we see in the explain plan, how can I make sure that the plan it show by explain plan is the one it has hard parsed ?
What I do is replace the binds with the literals for generating the plan.
Tom, any other easy way to do this ?
December 07, 2003 - 8:47 am UTC
explain plan is a guestimate. I use sql_trace + tkprof -- it shows reality. there are many times that explain plan will be "tricked".
ops$tkyte@ORA920> create table t as select 99999 x, a.* from all_objects a;
Table created.
ops$tkyte@ORA920> update t set x = 1 where rownum = 1;
1 row updated.
ops$tkyte@ORA920> create index t_idx on t(x);
Index created.
ops$tkyte@ORA920> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all indexed columns',cascade=>true);
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> variable x number
ops$tkyte@ORA920> exec :x := 1
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> set autotrace traceonly explain
ops$tkyte@ORA920> select * from t where x = :x;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=15851 Bytes=1585100)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=54 Card=15851 Bytes=1585100)
ops$tkyte@ORA920> set autotrace off
ops$tkyte@ORA920>
ops$tkyte@ORA920> alter session set sql_trace=true;
Session altered.
ops$tkyte@ORA920> declare
2 l_x number := 1;
3 cursor c is select * from t where x = l_x;
4 begin
5 open c;
6 close c;
7 end;
8 /
PL/SQL procedure successfully completed.
<b>and tkprof says....</b>
SELECT *
from
t where x = :b1
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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 549 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID T (cr=0 r=0 w=0 time=0 us)
0 INDEX RANGE SCAN T_IDX (cr=0 r=0 w=0 time=0 us)(object id 58964)
Thank you
wonder, December 07, 2003 - 1:51 pm UTC
Thank you , Tom
Bind variable
Dillip K. Praharaj, December 12, 2003 - 8:08 pm UTC
Thanks Tom.
How about generating the plan from the v$SQL_PLAN ?
December 13, 2003 - 11:39 am UTC
yes, that is another option -- same PLSQL block needed (to open/close cursor to get the plan generated)
if you have my new book "Effective Oracle By Design", i demonstrate in there how to use dbms_xplan easily to get a nicely formatted query plan out of there by creating a view on v$sql_plan that makes it look just like a plan table with a statement id....
how to reset an execution plan
A reader, January 31, 2004 - 9:12 am UTC
Hi
Recently we had some problem with our database, basically it was because one query used a very bad plan because of bind peeking, I suspect when it was first hard parsed it deduced an adequate plan for that moment but since it's cached afterwards all the queries afterwards were using that plan (a partition full scan). Normally this query should do index range scan. FYI we have cursor_sharing = FORCE
I realized that something had to be done so I flushed the shared pool and performance came back as good as before.
My question is, is there a way to flush a query execution plan instead of flushing the whole shared pool?
January 31, 2004 - 10:05 am UTC
in your case - sounds like you don't want binds on this query -- sounds like you want a plan per query here.
but, you've got a system where the developers bound NOTHING, so you are using cursor_sharing=force as a crutch.
sigh.
perhaps you want to alter your session and set cursor sharing=exact before doing this particular query -- or, if you can -- use the cursor sharing hint to go exact for this query.
Ur Admirer
Sagi, February 18, 2004 - 2:22 am UTC
In reply you said
INSERT /* STATIC SQL */ into t values ( :b1 )
Parses once and executes 100000
Whereas when using EXECUTE IMMEDIATE
insert /* DYNAMIC SQL */ into t values ( :i )
Its Parsed 100000 and Exuted 100000 times. But i am not able to understand why so many soft parses too.
If you see the statement both look the same except the text /* changes here */ and both are using bind variable.
I would appreciate your explanation.
Regards,
Sagi
February 18, 2004 - 8:38 pm UTC
if you use
alter session set sql_trace=true;
begin
for i in 1 .. 10000
loop
insert /* static */ into t values ( i );
execute immediate 'insert /* dynamic */ into t values ( :x )' using i;
end loop;
end;
/
you will see what I mean. static sql in plsql is "cached for you as part of the language".
dynamic sql -- cannot be. if you use NDS (native dynamic sql) as above -- it will
o open
o parse
o bind
o execute
o close
each time. with dbms_sql, you can move the open/parse to outside the loop, bind/execute inside the loop and close after...
A reader, February 19, 2004 - 3:16 am UTC
Thanx.
Ur awesome.
Regards,
Bind in plsql
Raaghid, May 31, 2004 - 4:27 am UTC
We have 2 production server, (1 is 8.1.5 and other is 9.2)
After reading your book, I am very particular in checking whether all the packages using bind variable or not.
We found that, the packages are not using the bind variable (None of the packages. Oh my God).
In one of the package (Developed in Power builder), it has been disabled using the command available in the GUI.
(Power builder, initial setting disable bind=1... intentionally done...)
Hence now we have created the BIG plan (like a new project), for chaning to bind (by changing the logic)
Thank you very much for your advise regarding bind, which helped immensely.
But at the same time, I find that, in all the PL/SQL procedures/triggers used for those packages, are
ALWAYS WITH BIND. (tested via sql$area)
Example:
--------
INSERT INTO REPLICATE_PRODUCT_TRAN(DOC_NO,DOC_REF_NO) VALUES (:b1 ,:b2);
So I came to the conculsion that, PL/SQL procedure always uses BIND variable by default.
(irrespecive of how programmers writes coding, except when he hardcode with value)
IS MY CONCLUSIION TRUE. please confirm and explain.
May 31, 2004 - 1:10 pm UTC
that is one of the the features that makes PLSQL superior as a database development language over any other (just one, but a big one)
Unlike most other languages -- you have to go way out of your way to NOT use binds inside of plsql (you still have to get the coders to bind to their plsql calls but at least once you are in there, everything is automagically bound for you)
bind in pl sql (contd)
Raaghid, May 31, 2004 - 6:12 am UTC
Other one is running in oracle 9.0.1.1.1 (I mentioned wrongly above)
Your Feedback to Bala
David, June 03, 2004 - 4:13 pm UTC
I've been trying to be careful to include bind variable in my dynamic sql. Your comments regarding NOT using bind variables with a decision support systems makes sense and is very helpful . . . Is it actually detrimental to use bind variables with a data warehouse system. . . will the absence of values be detrimental to the optimizer collecting column statistics.
June 03, 2004 - 7:08 pm UTC
it can be -- in a DW, you execute queries that take minutes, not the same queries hundreds of times per second.
More on bind variables in Descision Support environment
A P Clarke, June 04, 2004 - 7:03 am UTC
I've just joined a DSS project as PL/SQL developer-cum-deputy DBA. This is my first real exposure to DW (beyond some ETL tuning).
One of my Java colleagues asked me to look at a query. I remarked on the use of literals rather than bind variables. He said:
(1) Because the query was dynamically generated by JDBC it was jolly hard to use bind variables (because the number of predicates in the WHERE clause was variable and hence the number of bind variables is not predictable).
(2) Furthermore it was a bad idea to use bind variables in DSS because each query needs a different explain plan.
(3) Besides we could always use the CURSOR_SHARING parameter.
My Java isn't up to answering point (1).
Point (2) is fair enough, but... The system is intended to be used by dozens, eventually hundreds of concurrent users. The requirements state that most queries are supposed to return answers within six seconds. How much impact will constant hard parsing have on the scaleability of the system?
Point (3) seems to contradict point (2) and besides CURSOR_SHARING is A Bad Thing, right?
Cheers, APC
June 04, 2004 - 8:53 am UTC
1) it is just as jolly easy, NAY easier, to bind such a query.
not only that, but it is more secure as well.
In order to glue the query together, java programmer must have something like 2 or 3 arrays:
o array of "column names"
o array of "operators" like "=", "<", "like" and so on
o array of "operands"
and perhaps an array of conjunctors (AND/OR's)
they have code such as:
query = "select .... from t where 1=1";
for( int i = 0; i < names.length(); i++ )
query = query + "and " + names(i) + operation(i) +
"'" + method_to_quote_strings( operands(i) ) + "'";
parse, execute, fetch statement
well, to bind, lets see, what would they have to do??
query = "select .... from t where 1=1";
for( int i = 0; i < names.length(); i++ )
query = query + "and " + names(i) + operation(i) + "?";
parse Q
for( int i = 0; i < operands.length(); i++ )
Q.setVal( i, operands(i) );
execute, fetch statement
no messy "quote this string" and no SQL Injection (have java programmer search google for "sql injection", or search this site)
I'm tired of java (VB, etc etc etc) programmers saying "oh, binding is sooo hard"
a) no it is not
b) it is so important
c) it is actually *easier* since you don't have quotes to be concerned with
d) it is more secure
2) sometimes, it depends. If you are a true DW, this is true (gigabytes/terabytes of data, queries that are run once and never again). Asktom is a DSS system right? I use *binds* all over the place. Why? I do a couple of transactions (searches) per second. My system would *fall over* if I did not bind.
I'll bet this application is more like "pick a table, pick some columns from table, apply predicate" and you would find that the end users are picking the same table, with the same columns and doing the same sorts of predicates against them.
3) cursor sharing is a *poor crutch* that can be used to help an ailing application not totally kill a database whilst the coders are fixing their bug.
cursor sharing is a "bad thing" (tm) if you have to use it, as it implies you have some serious application issues that need to be corrected. cursor sharing negatively impacts applications that don't need it, cursor sharing changes the behaviour of your queries (select substr( c, 1, 5 ) no longer is known to return 5 characters as it is select substr(c,:x,:y) -- applications "break").
It is seriously funny that "binding is hard"..... binding is trivial. not-binding is actually HARDER, and less secure. (i'll keep driving home the less secure, maybe a security person will pick up on that and make a rule out lawing literal substitution in all code!)
response to A P Clarke..
bob, June 04, 2004 - 8:14 am UTC
For #1..
If the select clause is always the same, and it just has a variety of differing where clauses based on the presence/absence of some filter criteria, do it in pl/sql like this, and just let his/her java call your packaged procedure which gives him a refcursor/resultset.
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>
You are the pl/sql developer, so this should be your domain.
Let the java guy go build gui front ends or slick web pages. :)
Ressponse to Bob
A P Clarke, June 04, 2004 - 10:12 am UTC
Yeah, that was my initial reaction too. The problem is, the project had already been running a year when I joined, so there's a lot of stuff built. Also, there's enough PL/SQL work to keep me more than busy for the foreseeable future without writing a Java-PL/SQL interface as well. So...
I have made my concerns known to the interested parties. We are about to enter a prototype phase with a handful of users. This is top of my list for investigating if (when!) query response time becomes an issue that needs addressing.
Thanks also to Tom for his usual lucid reply.
Take care y'all.
APC
Bind variables and CBO
A reader, June 15, 2004 - 10:52 pm UTC
Queries with bind variables look like
column = ?
and the ? is passed in at run-time
But the CBO needs the actual value of the bind variable to generate a intelligent execution plan.
How do these 2 concepts reconcile with each other? To generate a good plan, CBO needs the actual value, but then that would mean that a parse is required!
Thanks
June 16, 2004 - 12:16 pm UTC
the optimizer rarely needs that information, rarely.
In a system that executes hundreds of queries per second/minute -- you would not want the plan for a given query to sometimes be "full scan" and something "index range scan" (end users would not like your system if it were so). These are so called "transactional" systems. They are the "most popular" (prevalant). Not using binds in these systems is *killer*.
In a system that executes a query in hundreds of seconds/minutes (data warehouse), you may well want a plan to change like that.
So, in transactional (read: most all) systems, bind variables are mandatory.
In a DW, they are not mandatory for the "big" queries.
Bind variable peeking
A reader, June 16, 2004 - 1:41 pm UTC
I have heard this phrase "bind variable peeking" associated with newer Oracle versions. How does that apply here? Does the optimizer get a sneak preview of the bind variable's value and then adjust the plan accordingly, on the fly? Is this less expensive than re-parsing?
I am not sure I understand when you say "the optimizer rarely needs this information".
Lets say the very first time a query was parsed with a bind variable on a indexed column, the optimizer generated a index scan, subsequently 100s of executions of the query happened to bind the variable to a value that returned 90% of the data i.e. the indexed column is not very selective. So, wouldnt histograms and stuff kick in here, all of which need to know the actual value of the bind variable?
If you say that CBO rarely needs this information, by "this" I assume you mean the actual values of the bind variables, then why bother analyzing the tables to determine the range of values in a column, frequency, density, etc?
Thanks
June 16, 2004 - 3:36 pm UTC
bind variable peeking kicks in at hard parse time. the optimizer peeks at the bind variable value and optimizes the query once, using that value as if it were a literal.
the concept is - you are a transactional application (you are using binds). the query will either always index scan or full scan. the bind variable you use will tell it which one is most appropriate.
many systems do not *need* to have histograms. data warehouses, yes, but in most cases histograms are in fact not needed.
A reader, June 16, 2004 - 7:24 pm UTC
"bind variable peeking kicks in at hard parse time. the optimizer peeks at the bind variable value and optimizes the query once, using that value as if it were a literal"
right, but the query would be hard parsed only the first time it is encountered (and assuming it is still in the shared pool of course). So, as I said, if the first encounter of the query passed in a non-representative or poor selectivity value for the column, the CBO would choose a index scan and all subsequnt executions of the query are doomed because they pass in a value for which a FTS would be faster (or vice versa)
"the query will either always index scan or full scan. the bind variable you use will tell it which one is most appropriate"
ok, this is where i still dont get it. You say that "the bind variable you use will tell it whether to index scan or full scan". Doesnt this imply that it needs to look at the actual value of the bind variable and hard parse it every time? And doesnt that then defeat the purpose of using bind variables? What am I missing?
Most systems nowadays are not pure OLTP or pure DSS in the original meaning of the words. So, the query plan would have to change depending on the actual value of the bind variable being passed in. How does Oracle handle this? Or does it randomly decide to hard parse a query every now and then?
June 17, 2004 - 7:52 am UTC
and I'm saying, in your transactional application -- it either always would full scan or always would index scan at that point in the code. the binds, the first binds for that unique piece of sql, would tell it which to do. that is the logic, that is the thought behind this. The inputs at this point in the code will be more or less consistent.
say you have a table of records to process.
1,000,000,000 records.
25 codes.
99% of the records are "processed=Y"
the other 1% of the records are spread over the remaining 24 codes.
At the point in the application you go to pull unprocessed records, index range scan (you would never use Y at that point in the code, you are using one or more of the other 24)
At the point in the application you go to report on processed records, full scan (you are doing processed=Y, probably not even binding at this point).
At that point in the code -- you are either pure oltp or dss. I understand the "system" as a whole is not, but at that point in the code -- it is.
A reader, June 17, 2004 - 9:47 am UTC
Your points are very well taken and I understand it, but you are thinking too much from a application point of view. i.e. all SQLs are embedded in code and cast in stone.
What I was trying to point out was situations where all the SQLs are coming from a BI-type front-end and ALWAYS use bind variables (i.e. where processed=:b1 in your example, ALWAYS).
In such a case, after the database is started, if the first instance of this query binds in 'Y', the CBO will choose a FTS and rightly so. But most subsequent invocations of this query bind in one of the other 24 codes, CBO will just pick up the cached execution plan and FTS and the queries will perform horribly!
Thanks
June 17, 2004 - 12:21 pm UTC
Now you are in the DW situation though, and in those cases, the use of bind variables must be weighed. And oh, how i WISH all BI front ends "always" used bind variables (with an option not to)
While using partitioning, should you use bind variables?
P, July 19, 2004 - 2:15 pm UTC
partition elimination and joins can occur only if the optimizer knows the filtering predicate in advance. the value of bind variables are not known until its time to execute making the process of partition elimination or joins impossible.
tom what is your opinion about this?
July 19, 2004 - 2:46 pm UTC
hogwash (it is not about opinions either -- this stuff is "factual", nothing opinionated about it)
totally wrong. bzzztt....
ops$tkyte@ORA9IR2> CREATE TABLE t
2 (
3 temp_date date,
4 x int,
5 y int
6 )
7 PARTITION BY RANGE (temp_date)
8 subpartition by hash(x)
9 (
10 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) tablespace users,
11 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) tablespace tools,
12 PARTITION junk VALUES LESS THAN (MAXVALUE)
13 )
14 ;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x varchar2(25);
ops$tkyte@ORA9IR2> delete from plan_table;
4 rows deleted.
ops$tkyte@ORA9IR2> explain plan for
2 select * from t where temp_date = to_date(:x,'dd-mon-yyyy');
Explained.
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 2 | | |
| 1 | PARTITION RANGE SINGLE| | | | | KEY | KEY |
| 2 | PARTITION HASH ALL | | | | | 1 | 1 |
|* 3 | TABLE ACCESS FULL | T | 1 | 35 | 2 | KEY | KEY |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."TEMP_DATE"=TO_DATE(:Z,'dd-mon-yyyy'))
Note: cpu costing is off
16 rows selected.
see the pstart/pstop -- it KNOWS it will partition eliminate, it doesn't know which partitions yet, but it knows it will.
partition elimination works dandy with binds by design.
bind variable in static SQL and Java (VB) code
Sean, July 20, 2004 - 8:18 pm UTC
Hi Tom,
You mentioned in your books and on this site that without using bind variable is one of the biggest problems in application. Here are a few questions:
1. In stored procedure, it seems that I would have bind variable issue when I use dynamic SQL. Most of my stored procedures use static SQL like the following one:
-----------------------------------
create table emp_temp as select * from emp where 1=2;
create or replace procedure p1 (p_deptno in number)
is
begin
for r_emp in ( select * from emp where deptno = p_deptno )
loop
insert into emp_temp (empno, sal)
values(r_emp.empno, r_emp.sal *1.1);
end loop;
end;
/
------------------------------------
I assume that "r_emp.empno" and "r_emp.sal *1.1" are bind variables. Of course, I understand that if I use "select * from emp where depnto = 7900", then 7900 is not bind variable. But it is quite rare we would have such query in the stored procedure.
Question: Do I need to worry about bind variable issue if I have stored procedure using static SQL like the above one?
2. If bind variable issues are only related to dynamic SQL, it is hard for me to understand why this is the big issue since in general we don't use much dynamic SQL in stored procedure, at least in our application.
3. You mentioned that in your book that JDBC use only dynamic SQL, my guess is that bind variable problem are mainly from code in Java or VB. If it is true, I would like to know how to use bind variables in Java code.
4. In page 449 of Export One on One, you have code how to find whether application use bind variables, but I did not
find them on the zip file.
Thank so much for your help.
July 20, 2004 - 9:06 pm UTC
1) static sql in plsql is always 100% perfectly bound, you cannot code it otherwise.
only dynamic sql must be explictly bound by you.
2) because many of the developers out there that were taught VB or Java believe there is no other language, or that all other languages are to be avoided.
and in VB and Java, there is only dynamic sql.
3) Use prepared/callable statements and the "bind" methods available with them.
4) everything should have been there, but:
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1163635055580 <code>
has it.
Bind variable in Java or VB code
Sean, July 20, 2004 - 9:54 pm UTC
Hi Tom,
(1) So actually according to your experience, most of SQL code which do not use bind variables are from VB or Java, not from stored procedures though almost all your examples of bind variables use stored procedure.
(2) If (1) is true, we should focus on fixing bind variable problems in VB or Java instead of stored procedure, hence more examples or doc should be available. By looking at Oracle JDBC guide, it seems that it didn't mention using bind variable. Would you please give us a link or some doc which show how to use bind variable in Java or VB (maybe it is so simple that every VB or Java developer should know bind variable already).
Thanks so much for your help.
July 20, 2004 - 10:07 pm UTC
1) yes on the first part, but not sure what you mean by the second part (other than I use plsql as my demonstration language of choice, yes..)
2) our documentation is not the documenation of the entire api - more about extensions to jdbc that we've added and "oracle'isms"
Sun "owns" the jdbc spec if you will - they document these things.
The java docs document the prepared/callable statement classes, pretty much every book on jdbc programming (we do not have one on jdbc programming, just on what our jdbc extensions are) discusses it.
How to use bind variables?
A reader, August 22, 2004 - 5:59 am UTC
Hi Tom.
I am writting a dynamic sql procedure, which builds inline views. The code below is being developed, Could you please help me how to use bind variables in inline view:
declare
l_sclause LONG;
l_wclause LONG;
l_from_clause LONG;
l_state_code DBMS_SQL.VARCHAR2_TABLE;
l_exec_string LONG;
begin
SELECT DISTINCT state_code BULK COLLECT INTO l_state_code FROM STATE;
l_sclause := 'INSERT /*+ APPEND */ INTO test_1 (client, total_amnt)
SELECT main.client, main.value+';
l_wclause := 'WHERE main.client = ';
l_from_clause := ' FROM test_3 main, ';
FOR i in 1.. l_state_code.count LOOP
l_from_clause := l_from_clause||
' (SELECT client, NVL(estimate,0) estimate FROM test_2 WHERE state_code='||''''||l_state_code(i)||''''||') '||l_state_code(i) ;
l_sclause := l_sclause ||'NVL('||l_state_code(i)||'.estimate,0)+';
l_exec_string := rtrim(l_sclause,'+');
l_exec_string := l_exec_string||l_from_clause;
l_exec_string := l_exec_string||' '||l_wclause||l_state_code(i)||'.client(+)';
P(l_exec_string);
EXECUTE IMMEDIATE l_exec_string;
END LOOP;
COMMIT;
END;
/
________________
output:
----------------
INSERT /*+ APPEND */ INTO test_1 (client, total_amnt)
SELECT
main.client, main.value+NVL(A.estimate,0) FROM test_3 main, (SELECT client,
NVL(estimate,0) estimate FROM test_2 WHERE state_code='A') A WHERE main.client =
A.client(+)
----------------
PL/SQL procedure successfully completed
I was reading chapter 5 of your new book, I can write to use bind variables with execute immediate, can I use bind variables in inline views?
Also, I use your P procedure to display the output (o/p), if o/p is very large, Is there any way I can get the formatted o/p?
Thanks
August 22, 2004 - 8:22 am UTC
they would be no different than bind variables without inline views.
you will be using DBMS_SQL since you do not know the number of bind variables at compile time.
for i in 1 .. l_state_code.count
loop
l_query := l_query ||
' (select ... from test_2 where state_code = :state_'||i|| '.....';
end loop;
dbms_sql.parse that query
for i in 1 .. l_state_code.count
loop
dbms_sql.bind_variable( l_cursor, 'state_'||i, l_state(i) );
end loop;
Here is a procedure "p" i use with dbms_output for lines > 255 characters:
create or replace procedure p ( p_str in varchar2 )
is
l_str long := p_str;
begin
loop
exit when l_str is null;
dbms_output.put_line( substr( l_str, 1, 250 ) );
l_str := substr( l_str, 251 );
end loop;
end;
/
P procedure
A reader, August 23, 2004 - 5:37 am UTC
Hi Tom,
Thanks for your help. As per your book "Expert one-on-one", appendix 'A' page 1154, it says that it takes string upto 32KB. I am hitting this limit, Could you please help, how can I overcome this? I am using Oracle 9.2. Can I use clob?
Regards,
August 23, 2004 - 8:25 am UTC
yes, you can use a clob.
Session Hangs
A reader, August 24, 2004 - 4:54 pm UTC
Hi Tom,
When I changed variable in p procedure to clob from long, my session hangs. Finally , I have to kill the session. Please advise.
Thanks
August 24, 2004 - 7:40 pm UTC
you probably coded an infinite loop....
no clue, no code, no clue.
and if the code is bigger than a screen - make it smaller before posting, I'm not going to read scads of code. try to debug it yourself using conventional techniques (dbms_application_info.set_client_info called in your code to say where you are/what you are doing is useful as this can be queried in another session right out of v$session - -can be very useful)
More info...
A reader, August 25, 2004 - 8:30 am UTC
Hi Tom,
Here is more info...
1 DECLARE
2 X clob;
3 y clob;
4 z clob;
5 BEGIN
6 x := rpad('X',32767,'X');
7 z := x||y;
8 --dbms_output.put_line(z);
9* END;
SQL> /
PL/SQL procedure successfully completed.
___________________
1 DECLARE
2 X clob;
3 y clob;
4 z clob;
5 BEGIN
6 x := rpad('X',32768,'X');
7 z := x||y;
8 --dbms_output.put_line(z);
9* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 6
_______________
Session Hangs
-- Where p is using clob
1 DECLARE
2 X clob;
3 y clob;
4 z clob;
5 BEGIN
6 x := rpad('X',32767,'X');
7 z := x||y;
8 p(z);
9 --dbms_output.put_line(z);
0* END;
/
Thanks
August 25, 2004 - 8:51 am UTC
does not "hang" for me -- however the ora-6502 should not be happening in the second example, since you have the ultimate in concise test cases -- please file that with support.
if you just changed p from varchar2/long to clob, it is as I said, you coded an INFINITE LOOP (hint: does the l_str clob value ever go "null" as the string l_str would have?
create or replace procedure p( p_str in clob )
is
l_str clob := p_str;
begin
loop
exit when l_str is null or dbms_lob.getlength(l_str) = 0;
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
dbms_output.put_line( substr( l_str, 1, 250 ) );
l_str := substr( l_str, 251 );
end loop;
end;
/
Parsing
reader, November 19, 2004 - 3:24 pm UTC
select 'X'
FROM
PS_CP_CONFIG C1 WHERE C1.BUSINESS_UNIT = 'MSTRX' AND C1.INV_ITEM_ID = :1 AND
C1.CONFIG_CODE = :2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1668 0.33 0.29 0 0 0 0
Execute 1668 0.08 0.09 0 0 0 0
Fetch 1668 0.03 0.06 3 5004 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5004 0.44 0.44 3 5004 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 21 (SYSADM)
Rows Row Source Operation
------- ---------------------------------------------------
0 INDEX UNIQUE SCAN (object id 5087)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PS_CP_CONFIG' (UNIQUE)
********************************************************************************
1) As you can see in the TKPROF results, no of parses = no of executes, which is bad. Would using a bind variable in WHERE C1.BUSINESS_UNIT = 'MSTRX' help solve this ?
2) If yes, then what is the problem with the below sql (using only bind variable here ....)
select IBU_GROUP
from
PS_BUS_UNIT_TBL_IN where BUSINESS_UNIT = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1668 0.21 0.33 0 0 0 0
Execute 1668 0.08 0.10 0 0 0 0
Fetch 1668 1.04 1.07 3 11676 6672 1668
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5004 1.33 1.50 3 11676 6672 1668
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 21 (SYSADM)
Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX FAST FULL SCAN (object id 375491)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF 'PSABUS_UNIT_TBL_IN'
(NON-UNIQUE)
********************************************************************************
Why is no. of parses = no. of executes ?
Thanks
November 19, 2004 - 7:51 pm UTC
1) no, it is well bound -- that literal is in fact constant.
what needs be fixed here is the CODE, the programmer is parse/binding/executing/closing. they want to parse bind/execute and bind/execute and bind/execute.
when parse = execute, it is the code's fault.
parsing
reader, November 22, 2004 - 10:58 am UTC
Thanks for this clarification. Now my next question on how to achieve what your are suggesting.
Basically the program does this :
1) Read the staging table to pick up all unprocessed rows.(using just a SQL, not a Pl/Sql cursor)
2) For each row perform a series of validation (like the 2 sqls I have mentioned above)
3) Write the row into the actual Table, if it satifies all validation.
How to achieve parse, bind/execute, bind/execute....... in this scenario ? Even if the code is changed to pl/sql, we would still have
1) Open the cursor to read unprocessed rows from Staging table
2) Fetch row
3) Validate condition1
condition2
condition3 ......
4) write into actual table
I dont see this method achieving the parse, bind/execute, bind/execute .... either. Would this still not be parse, bind, execute, close , parse, bind, execute, close .......
So having described what the program is for, what would be the best method to implement your suggestion.Is there any way of doing it without changing the whole thing to pl/sql ( The programmer writing this program is not familiar with pl/sql)
Thanks
November 22, 2004 - 3:26 pm UTC
programmer needs to read about "prepared statements" to figure out how programmer can parse ONCE and execute over and over.
in plsql, it would just happen.
in your 3gl code -- they have to CODE IT.
without knowing the language, it is truly hard to say more (and if you say "VB", I'll just refer you elsewhere as that particular language refuses to compile and run on any OS i use)
Parsing
reader, November 22, 2004 - 5:06 pm UTC
Thanks Tom.
But I still need to clarify a few things.
First, not sure what prepared statements are . Looked thru this site, but did not find anything (as yet) , that explained this concept. Could you direct me to the right place ? The program is being written using SQR (PeopleSoft)
You can embed SQL in this.
Second, how does it 'just happen' in pl/sql ? Can I have an example ?
Also, in the trace file I see that Parse = 0 for some statements. Wouldn't each statement be parsed atleast once ?
What does this indicate ? I have listed 2 examples below
insert into ps_cm_rcv_cost_inv
values
(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, 1, '100', :14, :15,
0)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 41013 10.74 10.84 269 1970 302428 41013
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 41013 10.74 10.84 269 1970 302428 41013
select max(seq_nbr)
from
ps_transaction_inv where business_unit = :1 and inv_item_id = :2 and
dt_timestamp = :3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 89533 4.46 4.00 0 0 0 0
Fetch 177593 4.13 3.98 71 270014 0 1473
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 267126 8.59 7.98 71 270014 0 1473
Thanks
November 22, 2004 - 5:37 pm UTC
you'll have to ask the makers of SQR if they thought about performance at all... I can show you preparedstatements in java, C, many languages -- but SQR? nope.
a search for preparedstatement on this site returned about 428 or so hits. very first hit </code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1993620575194 <code>talked about it.
parse count of 0 just means you started tracing AFTER they parsed it -- if that insert comes from sqr, sqr definitely supports prepared statements -- for they parsed the insert ONCE and executed it 41,013 times for 41,013 rows (which is another problem, row by slow)....
something way funny on the select max(), select max without a group by always returns at least one and at most one row. those counts look wacky -- 1,473 doesn't seem possible.
Helena Marková, November 23, 2004 - 4:58 am UTC
Peeking bind values..
RD, November 24, 2004 - 5:39 pm UTC
Hi Tom,
I am going through this thread and a few questions come to mind (regarding a few scenerios we are facing at work at the very moment) and they are:
Is this PEEKING of bind variables only a 9i and above feature or 8i( which we are using) as well? I ask because, from what you explained to Vikas, we are facing a scenerio where some queries which were taking just a few seconds are suddenly taking ages now. Seriously. One query which was running in 12 seconds is now running in 24 min's. Can this be a cause?(guess not if it's a new 9i feature).
What else can be done ? and frankly I don't understand why this would be happening? Nothing !! no really nothing has changed in the application because all changes go in through me only. We also don't have the before explain plans for all queries so can't compare. We analyze all tables weekly on sundays and we have an OLTP database.
I am really in need of some help or advice here.
Thanks,
Regards,
RD.
November 24, 2004 - 5:42 pm UTC
9i and above...
did the problem happen immediately after an analyze took place?
YES
RD, November 24, 2004 - 5:47 pm UTC
Hi Tom,
Exactly!!! Problems started on Monday morning.
Regards,
RD.
November 24, 2004 - 5:56 pm UTC
well you see the statement "nothing changed" is not accurate, you analyzed, that is a change.
Suggest you take a look at the autotrace traceonly output for this query AND the tkrprof the query and see where the optimizer has gone very wrong -- look at the CARD= in the autotrace, look at the rows flowing through that step. compare them and see where it is "very very wrong".
Then we can start to isolate the cause.
what is the exact way you gather stats?
Peeking ..
RD, November 24, 2004 - 6:03 pm UTC
Hi Tom,
I analyze the schema like this:-
exec sys.dbms_utility.analyze_schema('swbprod','compute');
Not good???
Thanks,
Regards,
RahulD.
November 24, 2004 - 6:52 pm UTC
that is good -- but in the future, you'll want to add in "dbms_stats.export..." first (save them off so you can restore them if you need)
but -- back to the problem at hand, what do you see in the autotrace/tkprof
Query..
RD, November 25, 2004 - 8:12 pm UTC
Hi Tom,
Hi Tom,
The latest query to become a victim is this one:-
SELECT DISTINCT B.TREE_NODE , Z.RECDESCR
FROM PSTREEDEFN A ,PSTREENODE B ,PS_SCRTY_ACC_GRP C ,PSTREENODE E ,PSROLECLASS X ,PSROLEUSER Y,PSRECDEFN Z
WHERE A.SETID=' '
AND A.TREE_STRCT_ID='ACCESS_GROUP'
AND A.EFF_STATUS='A'
AND A.EFFDT = ( SELECT MAX(D.EFFDT)
FROM PSTREEDEFN D
WHERE D.SETID=' '
AND D.TREE_NAME=A.TREE_NAME
AND D.EFFDT<=TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))
AND Y.ROLEUSER='RJD' AND Y.ROLENAME=X.ROLENAME
AND X.CLASSID = C.CLASSID AND C.TREE_NAME=A.TREE_NAME
AND C.ACCESSIBLE='Y' AND B.SETID=' '
AND B.TREE_NAME=E.TREE_NAME AND B.EFFDT=E.EFFDT
AND B.TREE_NODE_TYPE='R' AND B.TREE_NODE=Z.RECNAME
AND Z.RECTYPE IN (0, 1, 6) AND E.SETID=' '
AND E.TREE_NAME=A.TREE_NAME AND E.EFFDT=A.EFFDT
AND E.TREE_NODE_TYPE='G' AND B.TREE_NODE_NUM BETWEEN E.TREE_NODE_NUM
AND E.TREE_NODE_NUM_END AND C.ACCESS_GROUP=E.TREE_NODE
AND ((NOT EXISTS ( SELECT 'X'
FROM PS_SCRTY_ACC_GRP F
WHERE F.CLASSID=X.CLASSID
AND F.TREE_NAME=A.TREE_NAME
AND F.ACCESSIBLE='N'))
OR (E.TREE_NODE_NUM = ( SELECT MAX(G.TREE_NODE_NUM)
FROM PSTREENODE G ,PS_SCRTY_ACC_GRP H
WHERE G.SETID=' '
AND G.TREE_NAME=A.TREE_NAME
AND G.EFFDT=A.EFFDT and G.TREE_NODE_TYPE='G'
AND B.TREE_NODE_NUM BETWEEN G.TREE_NODE_NUM
AND G.TREE_NODE_NUM_END AND H.CLASSID=X.CLASSID
AND H.TREE_NAME=A.TREE_NAME AND H.ACCESS_GROUP=G.TREE_NODE)))
Till last Sunday it was running in say 12 seconds but suddenly on Monday morning it started taking
2 min's almost and also figured on my STATSPACK report.
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
5,523,922 2 2,761,961.0 35.2 2175643882
SELECT DISTINCT B.TREE_NODE , Z.RECDESCR FROM PSTREEDEFN A ,PSTR
EENODE B ,PS_SCRTY_ACC_GRP C ,PSTREENODE E ,PSROLECLASS X ,PSROL
EUSER Y ,PSRECDEFN Z WHERE A.SETID=' ' AND A.TREE_STRCT_ID='ACCE
SS_GROUP' AND A.EFF_STATUS='A' AND A.EFFDT = ( SELECT MAX(D.EFFD
T) FROM PSTREEDEFN D WHERE D.SETID=' ' AND D.TREE_NAME=A.TREE_NA
BUT the strange thing is when I delete statistics on table "PS_SCRTY_ACC_GRP" the query
run great.( in just 12 seconds ).
The Autotrace, explainplan and timing before dropping stats on "PS_SCRTY_ACC_GRP"
3653 rows selected.
Elapsed: 00:01:03.06
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=1 Bytes=240)
1 0 SORT (UNIQUE) (Cost=14 Card=1 Bytes=240)
2 1 FILTER
3 2 NESTED LOOPS (Cost=11 Card=1 Bytes=240)
4 3 NESTED LOOPS (Cost=10 Card=1 Bytes=203)
5 4 NESTED LOOPS (Cost=10 Card=1 Bytes=185)
6 5 NESTED LOOPS (Cost=9 Card=1 Bytes=161)
7 6 NESTED LOOPS (Cost=7 Card=1 Bytes=121)
8 7 NESTED LOOPS (Cost=4 Card=1 Bytes=81)
9 8 INDEX (FAST FULL SCAN) OF 'PSAPSTREEDEFN'(NON-UNIQUE) (Cost=1 Card=1 Bytes=35)
10 8 INDEX (RANGE SCAN) OF 'PSAPSTREENODE' (NON-UNIQUE) (Cost=3 Card=1224 Bytes=56304)
11 7 TABLE ACCESS (BY INDEX ROWID) OF 'PSTREENODE(Cost=3 Card=1224 Bytes=48960)
12 11 INDEX (RANGE SCAN) OF 'PSGPSTREENODE' (NON-UNIQUE) (Cost=2 Card=1224)
13 6 TABLE ACCESS (FULL) OF 'PS_SCRTY_ACC_GRP' (Cost=2 Card=1261 Bytes=50440)
14 5 INDEX (FAST FULL SCAN) OF 'PS_PSROLECLASS' (UNIQUE) (Cost=1 Card=1151 Bytes=27624)
15 4 INDEX (UNIQUE SCAN) OF 'PS_PSROLEUSER' (UNIQUE)
16 3 TABLE ACCESS (BY INDEX ROWID) OF 'PSRECDEFN' (Cost=1 Card=13049 Bytes=482813)
17 16 INDEX (UNIQUE SCAN) OF 'PS_PSRECDEFN' (UNIQUE)
18 2 SORT (AGGREGATE)
19 18 INDEX (RANGE SCAN) OF 'PS_PSTREEDEFN' (UNIQUE) (Cost=1 Card=1 Bytes=23)
20 2 TABLE ACCESS (BY INDEX ROWID) OF 'PS_SCRTY_ACC_GRP' (Cost=3 Card=1 Bytes=24)
21 20 INDEX (RANGE SCAN) OF 'PS_SCRTY_ACC_GRP' (UNIQUE) (Cost=2 Card=1)
22 2 SORT (AGGREGATE)
23 22 NESTED LOOPS (Cost=2 Card=1 Bytes=85)
24 23 TABLE ACCESS (BY INDEX ROWID) OF 'PSTREENODE' (Cost=2 Card=1 Bytes=46)
25 24 INDEX (RANGE SCAN) OF 'PSFPSTREENODE' (NON-UNIQUE) (Cost=1 Card=1)
26 23 INDEX (UNIQUE SCAN) OF 'PS_SCRTY_ACC_GRP' (UNIQUE)
Statistics
----------------------------------------------------------
14 recursive calls
705000 db block gets
2040354 consistent gets
16 physical reads
0 redo size
201731 bytes sent via SQL*Net to client
31537 bytes received via SQL*Net from client
246 SQL*Net roundtrips to/from client
40 sorts (memory)
0 sorts (disk)
3653 rows processed
After dropping statistics.
3653 rows selected.
Elapsed: 00:00:01.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=242)
1 0 SORT (UNIQUE) (Cost=13 Card=1 Bytes=242)
2 1 FILTER
3 2 NESTED LOOPS (Cost=10 Card=1 Bytes=242)
4 3 NESTED LOOPS (Cost=9 Card=1 Bytes=205)
5 4 NESTED LOOPS (Cost=6 Card=1 Bytes=165)
6 5 NESTED LOOPS (Cost=4 Card=1 Bytes=119)
7 6 NESTED LOOPS (Cost=4 Card=12 Bytes=1212)
8 7 NESTED LOOPS (Cost=3 Card=1 Bytes=77)
9 8 INDEX (FAST FULL SCAN) OF 'PSAPSTREEDEFN'(NON-UNIQUE) (Cost=1 Card=1 Bytes=35)
10 8 TABLE ACCESS (BY INDEX ROWID) OF 'PS_SCRTY_ACC_GRP' (Cost=2 Card=9 Bytes=378)
11 10 INDEX (RANGE SCAN) OF 'PSBSCRTY_ACC_GRP'(NON-UNIQUE) (Cost=1 Card=9)
12 7 INDEX (FAST FULL SCAN) OF 'PS_PSROLECLASS' (UNIQUE) (Cost=1 d=1151Bytes=27624)
13 6 INDEX (UNIQUE SCAN) OF 'PS_PSROLEUSER' (UNIQUE)
14 5 TABLE ACCESS (BY INDEX ROWID) OF 'PSTREENODE' (Cost=2 Card=1224 Bytes=56304)
15 14 INDEX (RANGE SCAN) OF 'PSCPSTREENODE' (NON-UNIQUE) (Cost=1 Card=1224)
16 4 TABLE ACCESS (BY INDEX ROWID) OF 'PSTREENODE' (Cost=3 Card=1224 Bytes=48960)
17 16 INDEX (RANGE SCAN) OF 'PSGPSTREENODE' (NON-UNIQUE) (Cost=2 Card=1224)
18 3 TABLE ACCESS (BY INDEX ROWID) OF 'PSRECDEFN' (Cost=1 Card=13049 Bytes=482813)
19 18 INDEX (UNIQUE SCAN) OF 'PS_PSRECDEFN' (UNIQUE)
20 2 SORT (AGGREGATE)
21 20 INDEX (RANGE SCAN) OF 'PS_PSTREEDEFN' (UNIQUE) (Cost=1 Card=1 Bytes=23)
22 2 TABLE ACCESS (BY INDEX ROWID) OF 'PS_SCRTY_ACC_GRP' (Cost=2 Card=1 Bytes=30)
23 22 INDEX (RANGE SCAN) OF 'PSBSCRTY_ACC_GRP' (NON-UNIQUE) (Cost=1 Card=1)
24 2 SORT (AGGREGATE)
25 24 NESTED LOOPS (Cost=2 Card=1 Bytes=86)
26 25 TABLE ACCESS (BY INDEX ROWID) OF 'PSTREENODE' (Cost=2 Card=1 Bytes=46)
27 26 INDEX (RANGE SCAN) OF 'PSFPSTREENODE' (NON-UNIQUE) (Cost=1 Card=1)
28 25 INDEX (UNIQUE SCAN) OF 'PS_SCRTY_ACC_GRP' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
5024 db block gets
40557 consistent gets
12 physical reads
0 redo size
201731 bytes sent via SQL*Net to client
31511 bytes received via SQL*Net from client
246 SQL*Net roundtrips to/from client
40 sorts (memory)
0 sorts (disk)
3653 rows processed
See the consistent gets too, way low.I was particularily watching this.
Table rows
PSTREEDEFN 146
PSTREENODE 22036
PS_SCRTY_ACC_GRP 1261
PSROLECLASS 1151
PSROLEUSER 1519
PSRECDEFN 35240
This has to have some explaination that I'm sure only you are capable of providing. No one else seems to even come close to understatding this.
Thanks in advance,
Regards,
RahulD.
November 25, 2004 - 8:21 pm UTC
please re-read what I asked for -- totally different from what was supplied.
Query..
RD, November 25, 2004 - 8:30 pm UTC
Hi Tom,
This is what I got from autotrace. Is that not what you asked for? Or
may be I misunderstood.
Regards,
RahulD.
November 25, 2004 - 8:37 pm UTC
<quote>
Suggest you take a look at the autotrace traceonly output for this query AND the
tkrprof the query and see where the optimizer has gone very wrong -- look at the
CARD= in the autotrace, look at the rows flowing through that step. compare
them and see where it is "very very wrong".
Then we can start to isolate the cause.
</quote>
Same problem here too RD, you're not alone
Kay, November 28, 2004 - 4:15 pm UTC
Hi Tom,
I have been following this thread and have experianced the same problems as RD and am eagerly waiting for some guidence on how to go about analyzing this parlicular problem which has given me much grief( and made me look bad because I could'ent provide any explaination to why it happens). Being a Junior/Intermediate DBA dosen't help much either.
Best of luck to all in same boat as US.
So long,
Eugene.
November 28, 2004 - 7:06 pm UTC
yes you are -- in that neither of you has compared what tkprof got (row count wise) with what autotrace traceonly explain thought would happen, presented that -- along with the parts of the predicate that would be associated with that.
for example in yours I see:
61 INDEX FAST FULL SCAN (object id 34559)
vs
INDEX (FAST FULL SCAN) OF 'PSAPSTREEDEFN'(NON-UNIQUE) (Cost=1 Card=1 Bytes=35)
Ok, soooo -- why so far off? and given that you and only you know the query here and the indexing scheme and the predicate -- you and only you can answer this.
that is what I would start doing. looking at the differences between "what was assumed" and what really happened and start asking "why"?
are the stats up to date.
maybe I needed a histogram.
things like that.
Autotrace and TKPROF
RD, November 28, 2004 - 5:13 pm UTC
Hi Tom,
Hope I got it right this time around,
From Autotrace:-
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=1 Bytes=240)
1 0 SORT (UNIQUE) (Cost=14 Card=1 Bytes=240)
2 1 FILTER
3 2 NESTED LOOPS (Cost=11 Card=1 Bytes=240)
4 3 NESTED LOOPS (Cost=10 Card=1 Bytes=203)
5 4 NESTED LOOPS (Cost=10 Card=1 Bytes=185)
6 5 NESTED LOOPS (Cost=9 Card=1 Bytes=161)
7 6 NESTED LOOPS (Cost=7 Card=1 Bytes=121)
8 7 NESTED LOOPS (Cost=4 Card=1 Bytes=81)
9 8 INDEX (FAST FULL SCAN) OF 'PSAPSTREEDEFN'(NON-UNIQUE) (Cost=1 ard=1Bytes=35)
10 8 INDEX (RANGE SCAN) OF 'PSAPSTREENODE' (NON-UNIQUE) (Cost=3 Card=1224 =56304)
11 7 TABLE ACCESS (BY INDEX ROWID) OF 'PSTREENODE(Cost=3 Card=1224 Bytes=48960)
12 11 INDEX (RANGE SCAN) OF 'PSGPSTREENODE' (NON-UNIQUE) (Cost=2 Card=1224)
13 6 TABLE ACCESS (FULL) OF 'PS_SCRTY_ACC_GRP' (Cost=2 Card=1261 Bytes=50440)
14 5 INDEX (FAST FULL SCAN) OF 'PS_PSROLECLASS' (UNIQUE) (Cost=1 Card=1151 Bytes=27624)
15 4 INDEX (UNIQUE SCAN) OF 'PS_PSROLEUSER' (UNIQUE)
16 3 TABLE ACCESS (BY INDEX ROWID) OF 'PSRECDEFN' (Cost=1 Card=13049 Bytes=482813)
17 16 INDEX (UNIQUE SCAN) OF 'PS_PSRECDEFN' (UNIQUE)
18 2 SORT (AGGREGATE)
19 18 INDEX (RANGE SCAN) OF 'PS_PSTREEDEFN' (UNIQUE) (Cost=1 Card=1 Bytes=23)
20 2 TABLE ACCESS (BY INDEX ROWID) OF 'PS_SCRTY_ACC_GRP' (Cost=3 Card=1 Bytes=24)
21 20 INDEX (RANGE SCAN) OF 'PS_SCRTY_ACC_GRP' (UNIQUE) (Cost=2 Card=1)
22 2 SORT (AGGREGATE)
23 22 NESTED LOOPS (Cost=2 Card=1 Bytes=85)
24 23 TABLE ACCESS (BY INDEX ROWID) OF 'PSTREENODE' (Cost=2 Card=1 Bytes=46)
25 24 INDEX (RANGE SCAN) OF 'PSFPSTREENODE' (NON-UNIQUE) (Cost=1 Card=1)
26 23 INDEX (UNIQUE SCAN) OF 'PS_SCRTY_ACC_GRP' (UNIQUE)
Statistics
----------------------------------------------------------
14 recursive calls
705000 db block gets
2040354 consistent gets
16 physical reads
0 redo size
201731 bytes sent via SQL*Net to client
31537 bytes received via SQL*Net from client
246 SQL*Net roundtrips to/from client
40 sorts (memory)
0 sorts (disk)
3653 rows processed
From TKPROF :-
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.39 0.54 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 245 64.88 66.35 199 2040351 705000 3653
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 247 65.27 66.89 199 2040351 705000 3653
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 521
Rows Row Source Operation
------- ---------------------------------------------------
3653 SORT UNIQUE
4986 FILTER
4987 NESTED LOOPS
5364 NESTED LOOPS
188552 NESTED LOOPS
162042 NESTED LOOPS
14209 NESTED LOOPS
769 NESTED LOOPS
61 INDEX FAST FULL SCAN (object id 34559)
828 INDEX RANGE SCAN (object id 34578)
14976 TABLE ACCESS BY INDEX ROWID PSTREENODE
16727 INDEX RANGE SCAN (object id 34583)
176249 TABLE ACCESS FULL PS_SCRTY_ACC_GRP
350592 INDEX FAST FULL SCAN (object id 35215)
193914 INDEX UNIQUE SCAN (object id 35186)
10349 TABLE ACCESS BY INDEX ROWID PSRECDEFN
10354 INDEX UNIQUE SCAN (object id 34383)
0 SORT AGGREGATE
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID PSTREENODE
0 INDEX RANGE SCAN (object id 34582)
0 INDEX UNIQUE SCAN (object id 35245)
42 TABLE ACCESS BY INDEX ROWID PS_SCRTY_ACC_GRP
85 INDEX RANGE SCAN (object id 35245)
82 SORT AGGREGATE
41 INDEX RANGE SCAN (object id 34558)
Thanks yet again in advance,
RD.
Bind Variables
HM, March 18, 2005 - 2:28 pm UTC
Tom,
I am not sure if this relevant to this thread, but here's the issue:
sqlstmt:='select * from emp where employee_name=:the_name';
execute immediate sqlstmt using 'jdoe';
does not appear to work (assuming that tables and data exist).
This works if the bind variable is an integer for a different column ofcourse.
Am I doing something wrong?
March 18, 2005 - 2:47 pm UTC
what do you expect it to do exactly?
how do you know that a select that cannot give you any output whatsoever based on the way you are using it "doesn't work"
now,
execute immediate sqlstmt into var1, var2, ... varN using 'jdoe';
that might work (assuming you have "one" jdoe)
unless you have a real need to use dynamic sql (this example doesn't show that), I strongly recommend you don't use dynamic sql.
Bind Variables
A reader, March 18, 2005 - 3:52 pm UTC
Tom,
I simplified the query quite a bit. There are several if/then/else statements that form a more complex query. However, I did notice that the query is parsed every time only because, the username changes, while the other parameters stay fairly static, so I was hoping to use a bind variable for the Username, such that it would not be parsed repeatedly. I noticed that the bind varible does not appear to work for a character variable, but I am not convinced. I could give you more detail if this is not clear.
March 18, 2005 - 8:07 pm UTC
well, strings are the simpliest of bind variables and they totally 100% work
so yes, you would need to provide a concise, small, short example to demonstrate what you mean.
Bind variable
Yogesh, April 05, 2005 - 9:28 am UTC
IÂ’m facing performance issue in one of the procedure. Procedure has one main cursor, which accepts 2 parameters sdate and edate. Following is the tkprof output for cursor query, when sdate = 01-FEB-2005 and edate = 2-FEB-2005
SELECT T1.* ,TRUNC(T3.IDATE,'mon') FROM T1, T3 WHERE T1.TDATE >= :b1 AND T1.TDATE <= :b2 AND T1.CNO = T3.CNO AND T3.PTYPE != 'IP' AND NOT EXISTS (SELECT 'X' FROM T4 WHERE T1.CNO = T4.CNO)
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 9716 347.98 346.35 2 666599 3 9715
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9718 347.98 346.35 2 666599 3 9715
But, If I hardcode the values in procedure I get entirely different results i.e. when sdate = 01-FEB-2005 and edate = 15-FEB-2005
SELECT T1.* ,TRUNC(T3.IDATE,'mon') FROM T1, T3 WHERE T1.TDATE >= '01-FEB-2005' AND T1.TDATE <= '15-FEB-2005' AND T1.CNO = T3.CNO AND T3.PTYPE != 'IP' AND NOT EXISTS (SELECT 'X' FROM T4 WHERE T1.CNO = T4.CNO)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 62987 66.61 65.27 0 2898142 3 62986
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 62989 66.65 65.31 0 2898142 3 62986
In the above explanation, where 1000000 rows are inserted and I guess every time it has parsed the statement. But in this case, we have to parse the statement only once. Can you please explain why so much of difference?
April 05, 2005 - 12:08 pm UTC
I'm missing something here, are you saying "when we bind we get a different answer"?
if so, need to see the client code that is binding.
I would suggest a 10046 level 12 trace to VERIFY the binds.
10046 Trace
Yogesh, April 05, 2005 - 12:25 pm UTC
I did used 10046 trace. when I'm creating my cursor with following query
cursor c1 is
SELECT T1.* ,TRUNC(T3.IDATE,'mon') FROM T1, T3 WHERE T1.TDATE >= sdate AND T1.TDATE <= edate AND T1.CNO = T3.CNO AND T3.PTYPE != 'IP' AND NOT EXISTS
(SELECT 'X' FROM T4 WHERE T1.CNO = T4.CNO)
I got following in raw trace ....
SELECT T1.* ,TRUNC(T3.IDATE,'mon') FROM T1, T3 WHERE T1.TDATE >= :b1 AND T1.TDATE <= :b2 AND T1.CNO = T3.CNO AND T3.PTYPE != 'IP' AND NOT EXISTS (SELECT 'X' FROM T4 WHERE T1.CNO = T4.CNO)
when I used following to create cursor
cursor c1 is
SELECT T1.* ,TRUNC(T3.IDATE,'mon') FROM T1, T3 WHERE T1.TDATE >= '01-FEB-2005' AND T1.TDATE <= '15-FEB-2005' AND T1.CNO = T3.CNO AND T3.PTYPE != 'IP' AND NOT EXISTS (SELECT 'X' FROM T4 WHERE T1.CNO = T4.CNO)
I got following in raw trace
SELECT T1.* ,TRUNC(T3.IDATE,'mon') FROM T1, T3 WHERE T1.TDATE >= '01-FEB-2005' AND T1.TDATE <= '15-FEB-2005' AND T1.CNO = T3.CNO AND T3.PTYPE != 'IP' AND NOT EXISTS (SELECT 'X' FROM T4 WHERE T1.CNO = T4.CNO)
TKPROF for first approach
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 9716 347.98 346.35 2 666599 3 9715
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9718 347.98 346.35 2 666599 3 9715
for second approach
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 62987 66.61 65.27 0 2898142 3 62986
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 62989 66.65 65.31 0 2898142 3 62986
Sorry I was not very clear in last post
April 05, 2005 - 12:48 pm UTC
describe t1 for us. most probably a "we compare dates to strings...."
ops$tkyte@ORA9IR2> create table t ( x varchar2(20) );
Table created.
ops$tkyte@ORA9IR2> insert into t values ( '20-feb-2005' );
1 row created.
ops$tkyte@ORA9IR2> insert into t values ( '20-feb-2004' );
1 row created.
ops$tkyte@ORA9IR2> select * from t where x between '01-feb-2005' and '28-feb-2005';
X
--------------------
20-feb-2005
20-feb-2004
ops$tkyte@ORA9IR2> select * from t where x between to_date('01-feb-2005') and to_date('28-feb-2005');
X
--------------------
20-feb-2005
Bind variable
Yogesh, April 05, 2005 - 12:27 pm UTC
I'm using oracle 8.0.4.
Yogesh, April 05, 2005 - 1:16 pm UTC
COLUMN_NAME DATA_TYPE
CNO NUMBER
ANO NUMBER
ONO CHAR
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
TDATE DATE
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
TOTAL NUMBER
TCODE CHAR
CSNO VARCHAR2
Column data type is DATE
April 05, 2005 - 6:42 pm UTC
show us the binds from the trace file and are the plans different or the same.
Plans
Yogesh, April 06, 2005 - 9:22 am UTC
Procedure call (cursor with static values)
begin
p1;
end;
/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 62987 66.61 65.27 0 2898142 3 62986
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 62989 66.65 65.31 0 2898142 3 62986
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18 (USER1) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 FILTER
0 NESTED LOOPS
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T2'
0 PARTITION (CONCATENATED)
0 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF 'T1'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'T1_S1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'T3'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'T3_PRIM' (UNIQUE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'T4_PRIM' (UNIQUE)
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Procedure call
begin
p1 ('01-FEB-2005','02-FEB-2005');
end;
/
Procedure p1 (startdate date, enddate date);
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 9709 354.48 529.72 20383 666578 3 9708
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9711 354.48 529.72 20383 666578 3 9708
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 18 (USER1) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
12941 FILTER
12941 NESTED LOOPS
109867 NESTED LOOPS
1721 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T2'
0 PARTITION (CONCATENATED)
109867 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF 'T1'
81649721 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'T1_S1' (NON-UNIQUE)
18653 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'T3'
109867 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'T3_PRIM' (UNIQUE)
12515 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'T4_PRIM' (UNIQUE)
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Procedure call
begin
p1 (to_date('01-FEB-2005'),to_date('02-FEB-2005'));
end;
/
Procedure p1 (startdate date, enddate date);
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 9709 347.97 348.00 0 666578 3 9708
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9711 347.97 348.00 0 666578 3 9708
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 18 (USER1) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
12941 FILTER
12941 NESTED LOOPS
109867 NESTED LOOPS
1721 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T2'
0 PARTITION (CONCATENATED)
109867 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF 'T1'
81649721 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'T1_S1' (NON-UNIQUE)
18653 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'T3'
109867 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'T3_PRIM' (UNIQUE)
12515 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'T4_PRIM' (UNIQUE)
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
binds from the trace file
SELECT T1.* ,TRUNC(T3.IDATE,'mon') FROM T1, T3 WHERE T1.TDATE >= :b1 AND
T1.TDATE <= :b2 AND T1.CNO = T3.CNO AND T3.PTYPE != 'IP' AND NOT EXISTS
(SELECT 'X' FROM T4 WHERE T1.CNO = T4.CNO)
Are you looking for this or something else?
April 06, 2005 - 1:18 pm UTC
let's see it in cut and paste, just plsql
alter session set sql_trace=true;
begin
for x in ( select 1 .... )
loop
null;
end loop;
for x in ( select 2 .... )
loop
null;
end loop;
end;
/
exit;
Yogesh, April 06, 2005 - 1:49 pm UTC
alter session set sql_trace=true;
BEGIN
FOR x IN ( SELECT 1 FROM DUAL)
LOOP
NULL;
END LOOP;
FOR x IN ( SELECT 2 FROM DUAL )
LOOP
NULL;
END LOOP;
END;
/
exit;
And paste trace file after that?
I'm sorry, but I could not understand what exactly you want me to do....
April 06, 2005 - 2:23 pm UTC
i want to see YOUR queries in there.
select 1
and
select 2
your two queries, I want to *see* everything.
Yogesh, April 06, 2005 - 2:33 pm UTC
cursor c1 is
SELECT T1.* ,TRUNC(T3.IDATE,'mon') FROM T1, T3 WHERE T1.TDATE >= sdate AND T1.TDATE <= edate AND T1.CNO = T3.CNO AND T3.PTYPE != 'IP' AND NOT EXISTS
(SELECT 'X' FROM T4 WHERE T1.CNO = T4.CNO)
cursor c1 is
SELECT T1.* ,TRUNC(T3.IDATE,'mon') FROM T1, T3 WHERE T1.TDATE >= '01-FEB-2005' AND T1.TDATE <= '15-FEB-2005' AND T1.CNO = T3.CNO AND T3.PTYPE != 'IP' AND
NOT EXISTS (SELECT 'X' FROM T4 WHERE T1.CNO = T4.CNO)
April 06, 2005 - 2:43 pm UTC
please code the entire example as I asked... ok, here, just run this and paste the output AND the tkprof results with the plans:
desc t1
alter session set sql_trace=true;
set serveroutput on
decalre
sdate date := to_date( '01-feb-2005', 'dd-mon-yyyy' );
edate date := to_date( '15-feb-2005', 'dd-mon-yyyy' );
cnt1 number := 0;
cnt2 number := 0;
BEGIN
FOR x IN ( SELECT T1.* ,TRUNC(T3.IDATE,'mon') FROM T1, T3 WHERE T1.TDATE >= sdate AND
T1.TDATE <= edate AND T1.CNO = T3.CNO AND T3.PTYPE != 'IP' AND NOT EXISTS
(SELECT 'X' FROM T4 WHERE T1.CNO = T4.CNO))
LOOP
cnt1 := cnt1+1;
END LOOP;
FOR x IN ( SELECT T1.* ,TRUNC(T3.IDATE,'mon') FROM T1, T3 WHERE T1.TDATE >= '01-FEB-2005'
AND T1.TDATE <= '15-FEB-2005' AND T1.CNO = T3.CNO AND T3.PTYPE != 'IP' AND
NOT EXISTS (SELECT 'X' FROM T4 WHERE T1.CNO = T4.CNO) )
LOOP
cnt2 := cnt2+1;
END LOOP;
dbms_output.put_line( cnt1 || ', ' || cnt2 );
END;
/
exit;
TKPROF OUTPUT
Yogesh, April 07, 2005 - 9:11 am UTC
DESC T1
Name Null? Type
------------- -------- ----
CNO NOT NULL NUMBER(38)
ANO NOT NULL NUMBER(3)
ONO NOT NULL CHAR(4)
TDATE NOT NULL DATE
TOTAL NUMBER(9,2)
TCODE NOT NULL CHAR(1)
DNO NUMBER(3)
CSNO VARCHAR2(8)
Starting First cursor 07-APR-2005 12:03:21
Finished First cursor 07-APR-2005 13:19:29
Finished Second cursor 07-APR-2005 13:22:56
62975, 62975
PL/SQL procedure successfully completed.
Elapsed: 01:19:35.36
tkprof ora_199326_USER1.trc ora_199326_USER1.out explain=/ sys=no
TKPROF: Release 8.0.4.3.0 - Production on Thu Apr 7 13:24:35 2005
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Trace file: ora_199326_USER1.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set sql_trace=true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 18 (USER1)
********************************************************************************
alter session set timed_statistics =true
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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18 (USER1)
********************************************************************************
alter session set max_dump_file_size = unlimited
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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 18 (USER1)
********************************************************************************
begin dbms_output.enable(2000); end;
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 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 18 (USER1)
********************************************************************************
declare
sdate date := to_date( '01-feb-2005', 'dd-mon-yyyy' );
edate date := to_date( '15-feb-2005', 'dd-mon-yyyy' );
cnt1 number := 0;
cnt2 number := 0;
BEGIN
dbms_output.put_line( 'Starting First cursor ' || To_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') );
FOR x IN
(
SELECT T1.* ,TRUNC(T3.IDATE,'mon') FROM T1, T3 WHERE T1.TDATE >= sdate AND T1.TDATE <= edate AND T1.CNO = T3.CNO AND T3.PTYPE != 'IP' AND NOT EXISTS
(SELECT 'X' FROM T4 WHERE T1.CNO = T4.CNO)
)
LOOP
cnt1 := cnt1+1;
END LOOP;
dbms_output.put_line( 'Finished First cursor ' || To_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') );
FOR x IN
(
SELECT T1.* ,TRUNC(T3.IDATE,'mon') FROM T1, T3 WHERE T1.TDATE >= '01-FEB-2005'AND T1.TDATE <= '15-FEB-2005' AND T1.CNO = T3.CNO AND T3.PTYPE != 'IP' AND NOT EXISTS (SELECT 'X' FROM T4 WHERE T1.CNO = T4.CNO)
)
LOOP
cnt2 := cnt2+1;
END LOOP;
dbms_output.put_line( 'Finished Second cursor ' || To_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') );
dbms_output.put_line( cnt1 || ', ' || cnt2 );
END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 19.62 22.40 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 19.63 22.41 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 18 (USER1)
********************************************************************************
SELECT T1.* ,TRUNC(T3.IDATE,'mon') FROM T1, T3 WHERE T1.TDATE >= :b1 AND T1.TDATE <= :b2 AND T1.CNO = T3.CNO AND T3.PTYPE != 'IP' AND NOT EXISTS (SELECT 'X' FROM T4 WHERE T1.CNO = T4.CNO)
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 62976 4263.14 4556.93 33720 6313882 3 62975
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 62978 4263.14 4556.93 33720 6313882 3 62975
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 18 (USER1) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 FILTER
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS (FULL) OF 'T2'
0 PARTITION (CONCATENATED)
0 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF 'T1'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'T1_S1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'T3'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'T3_PRIM' (UNIQUE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'T4_PRIM' (UNIQUE)
********************************************************************************
SELECT T1.* ,TRUNC(T3.IDATE,'mon') FROM T1, T3 WHERE T1.TDATE >= '01-FEB-2005' AND T1.TDATE <= '15-FEB-2005' AND T1.CNO = T3.CNO AND T3.PTYPE != 'IP' AND NOT EXISTS (SELECT 'X' FROM T4 WHERE T1.CNO = T4.CNO)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 62976 73.38 195.96 14578 2898109 3 62975
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 62978 73.42 196.00 14578 2898109 3 62975
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18 (USER1) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 FILTER
0 NESTED LOOPS
0 HASH JOIN
0 TABLE ACCESS (FULL) OF 'T2'
0 PARTITION (CONCATENATED)
0 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF 'T1'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'T1_S1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'T3'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'T3_PRIM' (UNIQUE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'T4_PRIM' (UNIQUE)
********************************************************************************
begin dbms_output.get_lines(:lines, :numlines); end;
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 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 18 (USER1)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.01 0.01 0 0 0 0
Execute 6 19.63 22.40 0 0 0 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 19.64 22.41 0 0 0 3
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.03 0.03 0 0 0 0
Execute 2 0.01 0.01 0 0 0 0
Fetch 125952 4336.52 4752.89 48298 9211991 6 125950
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 125956 4336.56 4752.93 48298 9211991 6 125950
Misses in library cache during parse: 1
8 user SQL statements in session.
0 internal SQL statements in session.
8 SQL statements in session.
2 statements EXPLAINed in this session.
********************************************************************************
Trace file: ora_199326_USER1.trc
Trace file compatibility: 7.03.02
Sort options: default
1 session in tracefile.
8 user SQL statements in trace file.
0 internal SQL statements in trace file.
8 SQL statements in trace file.
8 unique SQL statements in trace file.
2 SQL statements EXPLAINed using schema:
USER1.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
126067 lines in trace file.
April 07, 2005 - 11:02 am UTC
well, you didn't type "exit"
and you used explain=
I want the Row Source, those two are using *different plans* -- not explain=
I know 8.0.6 had this, if you run stuff in plus, then EXIT, then run tkprof, it'll show you the Row Source Operation with the *real* plan not an explain plan which "lies" sometimes. It'll show the rows and all.
but why did they get the *same answer* this time?? I thought this was all about "wrong data"?
Raw output after EXIT
Yogesh, April 07, 2005 - 1:07 pm UTC
When I was trying to figure out the delay, I used date range of 2 days in first query. ThatÂ’s why number of rows was different.
Final Output, after EXIT
Starting First cursor07-APR-2005 16:00:54
Finished First cursor07-APR-2005 17:30:29
Finished Second cursor07-APR-2005 17:33:52
62975, 62975
Cursor with sdate and edate
1. STAT #2 id=1 cnt=85216 pid=0 pos=0 obj=0 op='FILTER '
2. STAT #2 id=2 cnt=85216 pid=1 pos=1 obj=0 op='NESTED LOOPS '
3. STAT #2 id=3 cnt=790359 pid=2 pos=1 obj=0 op='NESTED LOOPS '
4. STAT #2 id=4 cnt=1721 pid=3 pos=1 obj=256190 op='TABLE ACCESS FULL T2 '
5. STAT #2 id=5 cnt=0 pid=3 pos=2 obj=0 op='PARTITION CONCATENATED '
6. STAT #2 id=6 cnt=790359 pid=5 pos=1 obj=36035 op='TABLE ACCESS BY LOCAL INDEX ROWID T1 '
7. STAT #2 id=7 cnt=1088718395 pid=6 pos=1 obj=251242 op='INDEX RANGE SCAN '
8. STAT #2 id=8 cnt=122668 pid=2 pos=2 obj=2086 op='TABLE ACCESS BY INDEX ROWID T3 '
9. STAT #2 id=9 cnt=790359 pid=8 pos=1 obj=198949 op='INDEX UNIQUE SCAN '
10. STAT #2 id=10 cnt=82197 pid=1 pos=2 obj=224461 op='INDEX RANGE SCAN '
Cursor with static values
11. STAT #3 id=1 cnt=85216 pid=0 pos=0 obj=0 op='FILTER '
12. STAT #3 id=2 cnt=85216 pid=1 pos=1 obj=0 op='NESTED LOOPS '
13. STAT #3 id=3 cnt=1331885 pid=2 pos=1 obj=0 op='HASH JOIN '
14. STAT #3 id=4 cnt=1721 pid=3 pos=1 obj=256190 op='TABLE ACCESS FULL T2 '
15. STAT #3 id=5 cnt=0 pid=3 pos=2 obj=0 op='PARTITION CONCATENATED '
16. STAT #3 id=6 cnt=840567 pid=5 pos=1 obj=36035 op='TABLE ACCESS BY LOCAL INDEX ROWID T1 '
17. STAT #3 id=7 cnt=840568 pid=6 pos=1 obj=251242 op='INDEX RANGE SCAN '
18. STAT #3 id=8 cnt=122668 pid=2 pos=2 obj=2086 op='TABLE ACCESS BY INDEX ROWID T3 '
19. STAT #3 id=9 cnt=790359 pid=8 pos=1 obj=198949 op='INDEX UNIQUE SCAN '
20. STAT #3 id=10 cnt=82212 pid=1 pos=2 obj=224461 op='INDEX RANGE SCAN '
Differences in plan
3. STAT #2 id=3 cnt=790359 pid=2 pos=1 obj=0 op='NESTED LOOPS '
13. STAT #3 id=3 cnt=1331885 pid=2 pos=1 obj=0 op='HASH JOIN '
7. STAT #2 id=7 cnt=1088718395 pid=6 pos=1 obj=251242 op='INDEX RANGE SCAN '
17. STAT #3 id=7 cnt=840568 pid=6 pos=1 obj=251242 op='INDEX RANGE SCAN '
April 07, 2005 - 1:32 pm UTC
the cbo is using a different plan when you use binds, it has less information.
What this means is -- in generaly the plan with the binds is "best"
but if you know the dates should "always be close together, the amount of data small" and since you have 8.0 without bind variable peeking (so the optimizer is "blind"), you might have to hint (I'd try first_rows)
(please -- in the future, apples and apples, I seriously thought the issue was "binds return X and constants return Y" that is where I was going with the date/string thing. you were comparing the incomparable!)
8.0 optimizer
Yogesh, April 08, 2005 - 4:41 am UTC
"Since you have 8.0 without bind variable peeking"
So, can we call it as limitation of optimizer in 8.0?
In which version onwards this issue is taken care by optimizer?
"in the future, apples and apples", are you referring to following?
begin
p1 ('01-FEB-2005','02-FEB-2005');
end;
April 08, 2005 - 7:27 am UTC
in 9i the database peeks at bind variables. search this site for "bind variable peeking" to read about it.
I was referring to the fact that you had us looking at:
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 9716 347.98 346.35 2 666599 3 9715
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9718 347.98 346.35 2 666599 3 9715
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 62987 66.61 65.27 0 2898142 3 62986
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 62989 66.65 65.31 0 2898142 3 62986
trying to figure out "what was wrong". It was very confusing to see outputs with different row counts. I thought we were looking at "when I bind I get answer 1, when I don't bind I get answer 2"
FIRST_ROWS
Yogesh, April 08, 2005 - 8:53 am UTC
I tried /*+ first_rows*/ .. But no difference. Query took 1.17 hrs for execution. Plan remain same (one with bind variables) .
What is the solution for this problem?
Upgrade to new version ? or be happy with the current performace?
April 08, 2005 - 8:57 am UTC
with such old software, hint it I guess. You seem happier with the NESTED LOOPS plan, so hint the bound query to use the indexe/nested loop path you desire.
I would be excessively happy if you upgraded, based on many questions you've had in the past, I think you would be excessively happy as well.
And after you do, remove the hints.
USE_HASH
Yogesh, April 08, 2005 - 9:43 am UTC
I totally agree with you ... I'm using 10 year old technology ...
One more question to add.... following plan looks good to me
11. STAT #3 id=1 cnt=85216 pid=0 pos=0 obj=0 op='FILTER '
12. STAT #3 id=2 cnt=85216 pid=1 pos=1 obj=0 op='NESTED LOOPS '
13. STAT #3 id=3 cnt=1331885 pid=2 pos=1 obj=0 op='HASH JOIN '
14. STAT #3 id=4 cnt=1721 pid=3 pos=1 obj=256190 op='TABLE ACCESS FULL T2 '
15. STAT #3 id=5 cnt=0 pid=3 pos=2 obj=0 op='PARTITION CONCATENATED '
16. STAT #3 id=6 cnt=840567 pid=5 pos=1 obj=36035 op='TABLE ACCESS BY LOCAL
INDEX ROWID T1 '
17. STAT #3 id=7 cnt=840568 pid=6 pos=1 obj=251242 op='INDEX RANGE SCAN '
18. STAT #3 id=8 cnt=122668 pid=2 pos=2 obj=2086 op='TABLE ACCESS BY INDEX
ROWID T3 '
19. STAT #3 id=9 cnt=790359 pid=8 pos=1 obj=198949 op='INDEX UNIQUE SCAN '
20. STAT #3 id=10 cnt=82212 pid=1 pos=2 obj=224461 op='INDEX RANGE SCAN '
But if you see line 13, where it is using the hash join, obj=0. How do I determine which table I should be using with USE_HASH hint?
April 08, 2005 - 9:57 am UTC
it is a hash join of the two tables
the plan should be readable as normal in the tkprof.
bind variable peeking question
Menon, February 05, 2006 - 1:31 am UTC
February 06, 2006 - 12:32 am UTC
yes, functions for example can really "hide" bind variable peeking in a fashion.
depends on the release how far into a function (builtin function) the optimizer can peek.
interesting...
Menon, February 06, 2006 - 12:51 pm UTC
"yes, functions for example can really "hide" bind variable peeking in a fashion.
depends on the release how far into a function (builtin function) the optimizer
can peek. "
Are there any other cases (apart from when we use functions) like this? By above I presume you mean when we use functions in where clause (e.g. dt > trunc(sysdate))...
It seems to me that if your query uses normal columns (not vitual ones), then a bind variable peeking would always be done during hard parse...So, perhaps we can state that in a typical database system (OLTP or D/W), most of the hard parses would undergo bind variable peeking... Is there any article you are aware of that discusses this issue in detail?
February 07, 2006 - 1:06 am UTC
yes, any sort of function...
We are in the process of writing such an article "right here" :)
run this in 9ir2 and 10gr2:
drop table t;
create table t
as
select 99 id, a.* from all_objects a;
update t set id = 1 where rownum = 1;
create index t_idx on t(id);
begin
dbms_stats.gather_table_stats
( user, 'T',
method_opt => 'for all indexed columns size 254',
cascade => true );
end;
/
variable str varchar2(25)
variable num number
exec :str := '1'; :num := 1;
alter session set sql_trace=true;
select * from t where id = to_number(:str);
select * from t where id = :num;
9i tkprof:
select * from t where id = to_number(:str)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL T
********************************************************************************
select * from t where id = :num
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID OBJ#(33502)
1 INDEX RANGE SCAN OBJ#(33503) (object id 33503)
10g tkprof:
select * from t where id = to_number(:str)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=90 us)
1 INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=69 us)(object id 56200)
********************************************************************************
select * from t where id = :num
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=89 us)
1 INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=66 us)(object id 56200)
a paper on CBO
Menon, February 06, 2006 - 2:14 pm UTC
when does bind variable peeking occur?
Menon, February 06, 2006 - 7:24 pm UTC
The above url pdf tells us when bind variable peeking occurs:
"There are two cases where the optimizer would peek at the actual bindings of a bind variable and where the actual bindings therefore could make a difference for what plan would get generated.
Range predicates. Example:
sales_date between :1 and :2 and price > :3.
Equality predicates when the column has histograms. Example:
order_status = :4
assuming that order_status has histograms.
In contrast, a condition like order_id = :1 will not trigger bind peeking assuming that order_id does not have histograms. (It may, for instance, be a primary key column, in which case histograms are not beneficial.)"
So, there could be lots of queries actually, where bind variable peeking is not done.
Also at </code>
http://www.oaktable.net/getChallenge.jsp;jsessionid=F8889A54FBA1FC1F0DB2DBCEC0E3EBDD?id=66, <code>I found the following
question:
Q: 9.2 does bind variable peeking. Is there a hint to force the optimizer to peek or not peek?
Answer: No hint to stop it, but there is the hint CURSOR_SHARING_EXACT which may have some effect. It's expected use is to stop literals being converted to bind variables Note - peeking seems to occur only on the first parse; after which the path is fixed - UNLESS some column in the WHERE clause has a histogram defined on it; in which case the statement is hard parsed on every run
The last part about a statement always getting hard parsed in case of a column containing histograms seems to be inaccurate?
February 07, 2006 - 1:25 am UTC
that answer is wrong. histograms do not cause a hard parse every time, cursor sharing exact would not really come into play.
I'll email Mogens about this...
thanx for the example...
Menon, February 07, 2006 - 11:38 am UTC
"yes, any sort of function...
We are in the process of writing such an article "right here" :)"...
Thanx!:-)
I believed you when you talked about functions - it did make sense. But thanx for the proof and the "just-in-time" article!;)
What I am I want to know is how common is the situation where bind variable peeking occurs. I am getting somewhat contradictory (to me) messages:
1. You point out the "function" example, which, one can argue, is not that common in queries -> meaning bind variable peeking is the norm).
2. Jonathan also points out in his book that bind variable peeking is almost always the case (dont have the book with me right now to quote)
3. From the paper that I posted above(official Oracle paper;), I get the message that there are only two cases where bind variable peeking DOES occur.
1. range predicates
2. predicates, where you have equality on a column that has histograms.
The above seems to suggest that bind variable peeking is in fact not very common?
Just wanted to know what your thoughts (or anyone else who cares to comment) are...
February 08, 2006 - 1:28 am UTC
the answer is as always "it depends"
if you don't use functions - very common
if you always use functions - not as common (varies by version in fact)
but, taking the official paper in mind, chew on this:
drop table t;
create table t
as
select 'x' str, a.*
from all_objects a;
update t set str='a' where rownum = 1;
create index t_idx on t(str);
exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all indexed columns size 254', cascade=>true );
variable s varchar2(5)
alter session set sql_trace=true;
set autotrace traceonly statistics
exec :s := 'a%';
select * from t parse_with_a where str like :s;
exec :s := 'x%';
select * from t parse_with_x where str like :s;
set autotrace off
select * from t parse_with_a where str like :s
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=73 us)
1 INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=53 us)(object id 56237)
********************************************************************************
select * from t parse_with_x where str like :s
Rows Row Source Operation
------- ---------------------------------------------------
49792 TABLE ACCESS FULL T (cr=3973 pr=0 pw=0 time=249145 us)
is LIKE equality or range?
:)
The answer from Mogens
Menon, February 07, 2006 - 11:40 am UTC
"that answer is wrong. histograms do not cause a hard parse every time, cursor
sharing exact would not really come into play.
I'll email Mogens about this... "
Perhaps he meant cursor sharing "similar" which, I understand, could result in many queries getting hard parsed (still not always)
hmmm
Menon, February 08, 2006 - 12:50 pm UTC
"is LIKE equality or range?"...
I think Oracle treats like as "range" - so this does not really give a counter example to the official doc.
February 09, 2006 - 4:36 am UTC
but it is not a range, it is a like...
so, what operators are not equality or range then.
use 10g, turn on 10053 trace - it says when bind peeking is or is not done.
I would say "like is not a range" personally, I think of range as - well, a range.
is "where x like 'A%_bc_X%' a "range". Sure, it can use an index in a range scan - but so can equality. So, why say equality, why not just say range....
Su Baba, February 27, 2006 - 1:07 am UTC
I downloaded your AllAboutBinds.ppt. I'd like to educate developers on the virtues of using bind variables. Is it ok if I use a portion of your Powerpoint presentation to do my presentation? Thanks.
February 27, 2006 - 7:04 am UTC
absolutely, I only post them so you can do that.
How to check current values?
Michal, March 21, 2006 - 6:29 am UTC
Hi,
I can't find anywhere how to check values that users assigned to bind variables when they were executing their queries. I would like to make some statistics on what values are most common for a given query (to help me understand how users are using the system/data, not due to any business requirements). Unfortunately there is no debug code to do that in the application/stored procedures and any change to the code takes time to be accepted by the customer, so I would much prefer to do that on the DBA level. Any hints how to do that?
Thanks a lot,
Great
Michal, March 23, 2006 - 8:03 am UTC
I wish it was so easy in 9i as it is in 10g but at least I know how to do that. Thanks!
Have read many useful things here about peeking on bind variables...
Dmytro, April 26, 2006 - 5:36 am UTC
...still one more question - is bind variable peeking still applicable for table-type variables, for example:
SELECT det_id
BULK COLLECT INTO g_details
FROM details
WHERE det_numb IN (SELECT *
FROM TABLE(CAST(l_det_accessible AS tt_strings)));
l_det_accessible and g_details here are table-type variables and the first of them, will have around 0-50 records (details table has nearly 300,000 records). So, will Oracle count number of rows in this table-type variable and create a plan based on this value (using index) or just use some constant like 8168 and do a FTS?
Search in AskTom
An Oracle Fan, April 26, 2006 - 11:39 am UTC
Hi Tom,
Your responses on this page are excellent as usual. A bit of a problem:
I wanted to see all posts regarding use of bind variables in a VC++ MFC application that queries an Oracle database. I typed VC++ in the search box. The search ended up querying {VC} with 750 results. I tried enclosing VC++ in double quotes. It did search {VC++} this time, but I got same(?) 750 results. The top four entries (score 100) were sans the search term.
Quirks of Oracle Text?
Specifying Bind Variables in Partitions
Steve Booth, April 27, 2006 - 10:41 am UTC
I can see where using Bind Variables could be very helpful. However, when I'm working with SQL on a Partitioned Table, I've found that specifying the partition where the data resides results in a much faster query execution. For instance:
SELECT *
FROM T_Data PARTITION(D_20060102)
WHERE MyDate = TO_DATE(:1, 'YYYYMMDD')
AND Fld2 = :2;
:1 = 20060102
:2 = Whatever
Because of the specification of the partition name, wouldn't each SQL statement be considered a separate statement having to be "Hard-Parsed"?
Thoughts and/or suggestions?
Thanks in Advance.
April 27, 2006 - 3:22 pm UTC
it would be (a hard parse) for each unique sql.
however, you give no clues here even about what you partitioned by.
I can only guess:
- you have data that is skewed very differently in each partition
- the access path that works for partition 1 is not what you like for partition 2
- you need a different plan for each partition
I would almost rather NOT use a bind for the partition key, rather than name the partition - that would achieve the same goal.
Which in your case is "a plan possible for each partition, using the local partition stats".
Richard Z. Tan, May 01, 2006 - 3:27 pm UTC
Hi Tom,
Thank you very much for your help.
Due to text template with multiple bind variables in there,
I try to use bind variable like the following,
Select ‘abc…’||:x||’def…’||:y||’ghi…’||:z||’jkl…’ from dual;
If I use double pipe in various forms, it does not work at all, but if I use concat() function to replace all double pipe, it works perfectly, but the template looks cumbersome if there are many bind variables in one single template.
Is there a very clean way to define a series of bind variables in the sql select statement, not sql criteria portion?
Thanks for help.
Richard Tan
May 02, 2006 - 3:26 am UTC
if you use "double pipe" and it does not work then the only conclusion I can come to is....
You didn't use double pipes correctly yet.
whether the binds are in the select or the where or whatever - doesn't matter, it is all the same to us.
ops$tkyte@ORA10GR2> variable a varchar2(10)
ops$tkyte@ORA10GR2> variable b varchar2(10)
ops$tkyte@ORA10GR2> variable c varchar2(10)
ops$tkyte@ORA10GR2> exec :a := 'Hello'; :b := 'World'; :c := 'ABC'
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> select '---' || :a || '---' || :b || '---' || :c from dual;
'---'||:A||'---'||:B||'---'||:C
-------------------------------------------------------------------------------
---Hello---World---ABC
Bind Variables with Partition Names
Steve Booth, May 02, 2006 - 12:55 pm UTC
Thanks for the response. The data is, as the partition name implies, partitioned by date. Each partition is a single date. This makes for the removal of obsolete data pretty easy, we just drop the partition and the local indices go away as well.
The data itself is very consistent on a partition by partition basis. In this application, approximately 75 SQL statements are used to perform all of the calculations necessary in order to determine the correct values for a single day.
Yet another ignorant question: Since we have to go through a "Hard Parse" process for the Partition specification, would it still make sense to use bind variables?
Thanks again.
May 02, 2006 - 4:00 pm UTC
the last question doesn't make "sense" - if you use binds - that means "no hard parse". If you don't it does.
can you give me an example of where using binds is not good and using the partition name is? (back to the original issue here)
Specifying Partitions and Bind Variables
Steve Booth, May 03, 2006 - 1:38 pm UTC
I'm sorry that I haven't been clear. Let me try to explain:
By using this SQL
SELECT *
FROM T_Data PARTITION(D_20060102)
WHERE MyDate = TO_DATE('20060102', 'YYYYMMDD')
AND Fld2 = 1234;
I get much better responsiveness because I'm starting out by restricting the processing to look only in the "D_20060102" Partition. In this table, each partition contains a single day of data. The SQL variable that changes on this statement is the date. Sometimes I need: 20060102, sometimes I need: 20051231 etc.
BUT because I am specifying this value as part of the Partition Name, my SQL statement is going to be "Hard-Parsed" each time it is called.
If I specify the SQL as:
SELECT *
FROM T_Data
WHERE MyDate = TO_DATE(:1, 'YYYYMMDD')
AND Fld2 = 1234;
:1 = '20060102'
I would avoid the "Hard Parse" step, but the resultant response time isn't as good. Several years ago on an 8i system, by specifying the "PARTITION()" the data was returned in about 1/4 of the time (elapsed). The actual SQL that was being used was (obviously) much more complicated. This SQL was meant to demonstrate the point (and failed). There are approximately 45,000 data records per day and 500 days in the table.
The followup question that I was attempting to ask is that since I have to specify the PARTITION after the table name in any case, would it make still make sense to use bind variables?
Once again, I hope that I've clarified what I meant.
Thanks again for your time.
May 03, 2006 - 2:42 pm UTC
IF myDate is the partition key
THEN
WHERE MyDate = TO_DATE(:1, 'YYYYMMDD') also restricts the processing to look at
a SINGLE PARTITION
END IF.
I do not believe you need to specify the partition name, I would like to see a demonstrable case - just use tkprof for us and show us
About your follow-up answer
Richard Tan, May 04, 2006 - 3:17 pm UTC
Hi Tom,
Thank you very much for your follow-up a couple of blocks up.
All templates are stored in the database, so I have to select and use dbms_sql, number of bind variables are not given before runtime, I use your example, please see anything wrong.
declare
v_html_text varchar2(4000) := '---' || :a || '---' || :b || '---' || :c ;
v_cursor number;
v_status number;
v_html_text_output varchar2(4000);
begin
v_html_text := 'select '||v_html_text ||' from dual';
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(c => v_cursor,
statement => v_html_text,
language_flag => dbms_sql.native);
dbms_sql.bind_variable(c => v_cursor,
name => ':a',
value => 'aaa',
out_value_size => 4000);
dbms_sql.bind_variable(c => v_cursor,
name => ':b',
value => 'bbb',
out_value_size => 4000);
dbms_sql.bind_variable(c => v_cursor,
name => ':c',
value => 'ccc',
out_value_size => 4000);
dbms_sql.define_column(c => v_cursor,
position => 1,
column => v_html_text_output,
column_size => 4000);
v_status := dbms_sql.execute(c => v_cursor);
while (dbms_sql.fetch_rows(c => v_cursor) > 0)
loop
dbms_sql.column_value(c => v_cursor,
position => 1,
value => v_html_text_output);
end loop;
dbms_sql.close_cursor(c => v_cursor);
dbms_output.put_line('v_html_text_output = '|| substr(v_html_text_output,1,250));
dbms_output.put_line('v_html_text_output = '|| substr(v_html_text_output,251,500));
dbms_output.put_line('v_html_text_output = '|| substr(v_html_text_output,501,750));
dbms_output.put_line('v_html_text_output = '|| substr(v_html_text_output,751,1000));
end;
/
Thank you very much for help.
May 04, 2006 - 5:11 pm UTC
v_html_text varchar2(4000) := '---' || :a || '---' || :b || '---' || :c ;
that line does not make sense.
why are :a, :b, and :c not IN THE STRING????
ops$tkyte@ORA9IR2> declare
2 v_html_text varchar2(4000) := ' ''---'' || :a || ''---'' || :b || ''---'' || :c' ;
3 v_cursor number;
4 v_status number;
5 v_html_text_output varchar2(4000);
6 begin
7 v_html_text := 'select '||v_html_text ||' from dual';
8 v_cursor := dbms_sql.open_cursor;
9 dbms_sql.parse(c => v_cursor,
10 statement => v_html_text,
11 language_flag => dbms_sql.native);
12 dbms_sql.bind_variable(c => v_cursor,
13 name => ':a',
14 value => 'aaa',
15 out_value_size => 4000);
16 dbms_sql.bind_variable(c => v_cursor,
17 name => ':b',
18 value => 'bbb',
19 out_value_size => 4000);
20 dbms_sql.bind_variable(c => v_cursor,
21 name => ':c',
22 value => 'ccc',
23 out_value_size => 4000);
24 dbms_sql.define_column(c => v_cursor,
25 position => 1,
26 column => v_html_text_output,
27 column_size => 4000);
28 v_status := dbms_sql.execute(c => v_cursor);
29 while (dbms_sql.fetch_rows(c => v_cursor) > 0)
30 loop
31 dbms_sql.column_value(c => v_cursor,
32 position => 1,
33 value => v_html_text_output);
34 end loop;
35 dbms_sql.close_cursor(c => v_cursor);
36 dbms_output.put_line('v_html_text_output = '||
37 substr(v_html_text_output,1,250));
38 dbms_output.put_line('v_html_text_output = '||
39 substr(v_html_text_output,251,500));
40 dbms_output.put_line('v_html_text_output = '||
41 substr(v_html_text_output,501,750));
42 dbms_output.put_line('v_html_text_output = '||
43 substr(v_html_text_output,751,1000));
44 end;
45 /
v_html_text_output = ---aaa---bbb---ccc
v_html_text_output =
v_html_text_output =
v_html_text_output =
PL/SQL procedure successfully completed.
Difference between Bind Variable and Reference variable
Mahmood Lebbai, June 27, 2006 - 2:27 pm UTC
Tom,
Plan A:
Variable yr1 number;
Insert into t values(:yr1);
exec :yr1:=2006;
Plan B:
Insert into t values (&yr1);
I understand, Plan A would result in Soft parse (as you mentioned in your Oracle Expert one-on-one) but can I use the Plan B? Will that guarantee a soft parse? If it is not, then what is the difference between them?
Thanks.
June 27, 2006 - 2:49 pm UTC
&yr1 is a substitution variable, processed by the client. It results in the sql statment:
insert into t values ( 2006 )
being submited to the database - use binds.
Bind variables
a reader, July 18, 2006 - 8:21 am UTC
I have gone through this thread, just wanted to confirm
my understandings.
I am using PL/SQL in an OLTP system.
1)
Which SQL is better ?
SELECT c1 FROM t1 WHERE c1='A' and c2=:var2
OR
SELECT c1 FROM t1 WHERE c1= :var1 and c2=:var2
2)
Which SQL is better
SELECT c1 FROM t2 WHERE C2 IN ('A','B')
OR
SELECT c1 FROM t2 WHERE C2 IN (:VAR1,:VAR2)
Thanks
July 18, 2006 - 8:48 am UTC
1) if the sql is always "c1='A'", then "c1='A'" is the only choice you want to consider. You bind only that which VARIES from sql statement execution to execution.
2) see #1, if 'A', 'B' are ALWAYS 'A', 'B', there is quite simply no point in binding and reasons for NOT binding them
reason to not bind if 'a','b' are constant: they are constant, they do not change, it has meaning to have 'a','b' in the sql text, it is obvious they are constant. next person to read the code will SEE 'a','b' and say "ah, they are constant"
the optimizer gets to see 'a', 'b' and can make use of that fact (that they are 'a', 'b') when optimizing the query.
fine, but I was thinknig it from a different angle
a reader, July 19, 2006 - 4:19 am UTC
Ok, fine, understood,
But my point of view was with respect to "good develpment practice/principles".
Consider a large project where several developers
are writing queries without know what the other developer
is writing. For e.g.
Developer A writes SELECT * FROM t1 WHERE c1 = 'A'
Developer B writes SELECT * FROM t1 WHERE c1 = 'B'
Developer C writes SELECT * FROM t1 WHERE c1 = 'C'
Now each developer's query will have a hard parse.
Yes C='A' is constant, but its constant with respect to
that indivisual developer's code only not, with respect to the whole project code. But if all developers follow a simple rule to use bind variables everywhere, we will
have less hard parses.
Do you agree to make it a standard rule for the whole
project?
July 19, 2006 - 9:13 am UTC
I doubt that would happen and it would be limited by the number of developers hence I sort of doubt it would become a problem.
Reason also -
developer a writes
select * from t1 where c1 = :x
developer b writes
Select *
From t1
Where c1 = :x;
developer C writes
select * from t1 where t1.c1 = :x;
developer D writes
SELECT A, B, C, D
FROM T1
WHERE C1 = :X;
they are all using binds, they are all different sql.
No, OVERBINDING is bad - bind only that which varies.
How over binding is bad
a reader, July 19, 2006 - 9:59 am UTC
Ok, the SQLs are all using binds, they are actualy same
but different for the parser, ok? Can this problem (being different for parser) be avoided/minimized by following
some coding conventions/standards? Can you suggest some of such coding conventions/standards.
You said, overbinding is not good, how? please ellaborate
it, I can hardly think of it, even in Datawarehouses, because I think that binding always "HELPS" or "DOES NOT HELP" but it never "HARMS"
July 20, 2006 - 7:42 am UTC
if you use plsql to encapsulate all of your sql - you'll centralize your code and ensure that the sql is "consistent" :)
say you have the query:
for x in ( select * from user_objects where object_type = 'TABLE' )
loop
Tell me - what does that query "say". It says "I'm processing tables", it is very clear, not ambigous, self documenting, useful.
Now:
for x in ( select * from user_objects where object_type = pkg.global )
loop
what does that say? it says "i'm processing something, but you don't know what, you can guess, you might be right, you might be wrong"
the literal adds value for us humans.
It can also add value for the optimizer (although it can do bind peeking)
A reader, July 19, 2006 - 10:14 am UTC
Do a quick search for Bind Variable Peeking to see the answer to this.
harms of overbinding
a reader, July 20, 2006 - 11:47 am UTC
Ok, so can we say that the harms of over binding are only
1- Readability of the code is not good.
2- The query is hard parsed once, and due to bind variable peeking, the plan it generates may not be good for the other values of the variable.
Are there any other harms you want to list.
July 22, 2006 - 4:53 pm UTC
you missed the important one:
"It can also add value for the optimizer"
the optimizer gets the hard coded values - very relevant.
By using the "are only" terminology, it almost sounds like you are down playing this readability issue. You shouldn't.
overbinding harm list
a reader, July 21, 2006 - 9:19 am UTC
Any body like to add to the above list
TKPROF OUTPUT WHEN USING BIND VARIABLES
Seema, September 12, 2006 - 5:00 pm UTC
Hi Tom,
I tried comparing the tkprof output using your example with following 3 cases:
set timing on
alter session set sql_trace=true;
begin
for i in 1 .. 100000
loop
insert /* STATIC SQL */ into t values ( i );
end loop;
end;
/
begin
for i in 1 .. 100000
loop
execute immediate 'insert /* DYNAMIC SQL USING BIND VARIABLES*/ into t values ( :i )'
using i;
end loop;
end;
/
-- If you really want to see the effect of not using bind variables use this:
begin
for i in 1 .. 100000
loop
execute immediate 'insert /* DYNAMIC SQL NOT USING BIND VARIABLES*/ into t values('||i||')';
end loop;
end;
/
disconnect
But I get a LOT of extra things in the tkprof output as following:
_________________
_________________
FOR THE 1st CASE:
_________________
_________________
********************************************************************************
begin
for i in 1 .. 100000
loop
insert /* STATIC SQL */ into t values ( i );
end loop;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.16 0.16 0 0 0 0
Execute 1 0.65 6.34 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.81 6.51 0 0 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61
********************************************************************************
select user#
from
sys.user$ where name = 'OUTLN'
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 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID USER$
1 INDEX UNIQUE SCAN I_USER1 (object id 44)
********************************************************************************
INSERT INTO T
VALUES
( :B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.03 0 0 0 0
Execute 100000 11.08 15.10 0 328 105275 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100001 11.12 15.14 0 328 105275 100000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)
********************************************************************************
_________________
_________________
FOR THE 2nd CASE:
_________________
_________________
********************************************************************************
begin
for i in 1 .. 100000
loop
execute immediate
'insert /* DYNAMIC SQL USING BIND VARIABLES*/ into t values ( :i )'
using i;
end loop;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.05 0.04 0 0 0 0
Execute 1 9.73 21.36 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 9.78 21.41 0 0 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61
********************************************************************************
insert /* DYNAMIC SQL USING BIND VARIABLES*/ into t
values
( :i )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 100000 10.94 3.89 0 0 0 0
Execute 100000 12.69 18.22 0 100326 304972 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200000 23.64 22.11 0 100326 304972 100000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)
********************************************************************************
_________________
_________________
FOR THE 3rd CASE:
_________________
_________________
********************************************************************************
begin
for i in 1 .. 100000
loop
execute immediate
'insert /* DYNAMIC SQL NOT USING BIND VARIABLES*/ into t values('||i||')';
end loop;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 17.30 31.79 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 17.30 31.79 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 61
********************************************************************************
insert /* DYNAMIC SQL NOT USING BIND VARIABLES*/ into t
values
(1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 1 0.00 0.00 0 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.01 0 1 3 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)
********************************************************************************
insert /* DYNAMIC SQL NOT USING BIND VARIABLES*/ into t
values
(2)
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 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 3 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)
********************************************************************************
insert /* DYNAMIC SQL NOT USING BIND VARIABLES*/ into t
values
(3)
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 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 3 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)
********************************************************************************
insert /* DYNAMIC SQL NOT USING BIND VARIABLES*/ into t
values
(4)
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 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 1 3 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)
********************************************************************************
insert /* DYNAMIC SQL NOT USING BIND VARIABLES*/ into t
values
(5)
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 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 3 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)
********************************************************************************
insert /* DYNAMIC SQL NOT USING BIND VARIABLES*/ into t
values
(6)
_________________________________________
_________________________________________
AND THIS GOES ON AND ON .................
.........................................
.........................................
.........................................
.........................................
.........................................
.....................................TILL
_________________________________________
_________________________________________
********************************************************************************
insert /* DYNAMIC SQL NOT USING BIND VARIABLES*/ into t
values
(100000)
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 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 1 3 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)
********************************************************************************
select pos#,intcol#,col#,spare1,bo#,spare2
from
icol$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 22 0.00 0.00 0 44 0 17
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 29 0.00 0.00 0 44 0 17
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
********************************************************************************
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1,
spare2
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null
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 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 4 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select node,owner,name
from
syn$ where obj#=: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 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by
grantee#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 11 0.01 0.00 0 11 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 0.01 0.00 0 11 0 8
Misses in library cache during parse: 2
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.37 0.39 0 0 0 0
Execute 7 27.72 59.55 0 0 0 6
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 28.10 59.95 0 0 0 6
Misses in library cache during parse: 3
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 200064 148.35 138.78 0 5 0 0
Execute 300101 40.00 54.36 0 201008 715286 300006
Fetch 273 0.09 0.06 0 582 0 191
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 500438 188.45 193.22 0 201595 715286 300197
Misses in library cache during parse: 100029
200008 user SQL statements in session.
63 internal SQL statements in session.
200071 SQL statements in session.
********************************************************************************
Trace file: bpsrep2_ora_1592.trc
Trace file compatibility: 9.02.00
Sort options: default
1 session in tracefile.
200008 user SQL statements in trace file.
63 internal SQL statements in trace file.
200071 SQL statements in trace file.
100026 unique SQL statements in trace file.
1300870 lines in trace file.
_________________________________________
_________________________________________
how do I read and get final conclusions (like the way you demonstarted) from all this for the 3 types of statements that I ran.
September 13, 2006 - 7:02 am UTC
look at the overall totals.
compare them.
that is the "summary of the work performed by each"
tkprof for comparing bind variables performance benefit
Seema, September 13, 2006 - 6:01 pm UTC
Ok Tom, I picked the following results for the 1st case(STATIC SQL)& 2nd case(DYNAMIC SQL USING BIND VARIABLES.
But about the 3rd case (DYNAMIC SQL NOT USING BIND VARIABLES) I did not find no summary in the tkprof output. The tfprof output is already pasted in my previous post. Please advise.
********************************************************************************
INSERT INTO T
VALUES
( :B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.03 0 0 0 0
Execute 100000 11.08 15.10 0 328 105275 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100001 11.12 15.14 0 328 105275 100000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)
********************************************************************************
********************************************************************************
insert /* DYNAMIC SQL USING BIND VARIABLES*/ into t
values
( :i )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 100000 10.94 3.89 0 0 0 0
Execute 100000 12.69 18.22 0 100326 304972 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200000 23.64 22.11 0 100326 304972 100000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (recursive depth: 1)
********************************************************************************
September 14, 2006 - 9:04 am UTC
my advice is constant and consistent:
USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS USE BINDS
tkprof shows cpu time used
tkprof shows elapsed time
tkprof shows IO's performed.
is it not *obvious* from the above which uses
o less cpu
o less elapsed time
o less IO's
what tkprof does not show is the latching involved - and the second one (with 100,000 parses) does TONS more latching than the first meaning when you go multi-user, the second one you have will use MUCH MORE cpu than it currently does (more users = more latch contention = more cpu used = less scalable = not nearly as much work gets done)
just use binds.
Helpful, but need some clarification
A reader, December 20, 2006 - 10:51 am UTC
We have an application server that sends various updates/select/inserts to the database. It uses bind variables.
One particular update statement, which uses bind variables is performing a FTS. It performs a basic update on T1 against 3 columns where a particular column, COL1 = :V1.
T1 has 3million+ rows and COL1=0 for 99.999% of the rows. The row that gets updated is the row where COL!=0, but some number that is substituted for the bind variable.
I'm trying to understand if the optimizer uses the sql with the bind variable to determine is execution path or will it generate an execution path based upon a fixed value for COL1?
The bind variable version is very expensive and performs a FTS, however if I run the update with a specific value for COL1 then the explain plan shows that it uses the index.
December 20, 2006 - 1:18 pm UTC
it will bind peek the first time around - I'd look into "implicit datatype conversion" first and foremost.
you can use a 10053 trace (search for that number on this site) to verify bind peeking is happening.
Look out for implicit conversions though! (eg: col1 is a varchar2 in the database, programmer binds a number, the real predicate would be "to_number(col1) = :x"
Help please
Loïc, March 07, 2007 - 1:44 pm UTC
Hi Tom,
I work on a project on which certain "design patterns" are used to write SQL queries.
Among them stands the following:
"Write a generic SQL query to handle every case so that maintenance is very simple."
select ...
from my_data
where col1 = :1 and
(:2 is null or col2 = :3);
The last line has two parameters which are binded at runtime with the same value:
input of a user in a search screen for example.
If the user inputs nothing, then the query works and we have:
select ...
from my_data
where col1 = :1 and
(null is null or col2 = null);
which only consists of filtering on col1.
If the user inputs a value, it becomes:
select ...
from my_data
where col1 = :1 and
('value' is null or col2 = 'value');
and the filtering is also made on col2.
Following is a testcase to see this design pattern in action:
SET ECHO OFF
SET SERVEROUTPUT ON SIZE 1000000
SET FEEDBACK OFF
column "Plan Table" format a255
prompt Cleaning...
DROP TABLE CASH_COMPENSATION;
DROP TABLE compensation;
prompt Creating tables...
CREATE TABLE compensation
(
compensation_id NUMBER not NULL primary KEY,
status VARCHAR2(4)
);
create table CASH_COMPENSATION
(
CASH_COMPENSATION_ID NUMBER not NULL PRIMARY KEY,
TRX_ID NUMBER,
ORIGIN VARCHAR2(5) not NULL
);
alter table CASH_COMPENSATION
add constraint FK_CASH_COM_FK_CASH_C_COMPENSA foreign key (CASH_COMPENSATION_ID)
references COMPENSATION (COMPENSATION_ID);
prompt Inserting data into tables...
DECLARE
num_rows NUMBER;
BEGIN
FOR i IN 1..50000 LOOP
BEGIN
INSERT INTO compensation (compensation_id, status) VALUES (i, decode(MOD(i,5), 0, 'SETT', 'COMP') );
IF MOD(i,10) != 9 THEN
INSERT INTO cash_COMPENSATION (CASH_COMPENSATION_ID, trx_id, origin) VALUES
(i,decode(MOD(i,2), 0, i*3, NULL),decode(MOD(i,4), 0, 'INCO', 'OUTGO'));
END IF;
END;
END LOOP;
COMMIT;
SELECT COUNT(*) INTO num_rows FROM compensation;
dbms_output.put_line('Inserted '||num_rows||' rows into COMPENSATION');
SELECT COUNT(*) INTO num_rows FROM cash_compensation;
dbms_output.put_line('Inserted '||num_rows||' rows into CASH_COMPENSATION');
END;
/
prompt Gathering statistics on tables...
EXEC dbms_stats.gather_table_stats( ownname => USER, tabname => 'COMPENSATION', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE );
EXEC dbms_stats.gather_table_stats( ownname => USER, tabname => 'CASH_COMPENSATION', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE );
truncate table plan_table;
explain plan FOR
select COMP.COMPENSATION_ID,
CASHCOMP.TRX_ID,
CASHCOMP.ORIGIN
from COMPENSATION COMP,
CASH_COMPENSATION CASHCOMP
where COMP.COMPENSATION_ID = CASHCOMP.CASH_COMPENSATION_ID(+)
and (:1 is null or :2 = COMP.COMPENSATION_ID);
select plan_table_output "Plan Table" from table(dbms_xplan.display('plan_table',null,'serial'));
ROLLBACK;
explain plan FOR
select COMP.COMPENSATION_ID,
CASHCOMP.TRX_ID,
CASHCOMP.ORIGIN
from COMPENSATION COMP,
CASH_COMPENSATION CASHCOMP
where COMP.COMPENSATION_ID = CASHCOMP.CASH_COMPENSATION_ID(+)
and :2 = COMP.COMPENSATION_ID;
select plan_table_output "Plan Table" from table(dbms_xplan.display('plan_table',null,'serial'));
ROLLBACK;
Output:
Cleaning...
Creating tables...
Inserting data into tables...
Inserted 50000 rows into COMPENSATION
Inserted 45000 rows into CASH_COMPENSATION
Gathering statistics on tables...
Plan Table
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2501 | 45018 | 6579 (61)|
| 1 | NESTED LOOPS OUTER | | 2501 | 45018 | 6579 (61)|
|* 2 | INDEX FULL SCAN | SYS_C0015604931 | 2501 | 12505 | 955 (91)|
| 3 | TABLE ACCESS BY INDEX ROWID| CASH_COMPENSATION | 1 | 13 | 3 (67)|
|* 4 | INDEX RANGE SCAN | SYS_C0015604934 | 1 | | |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:Z IS NULL OR "COMP"."COMPENSATION_ID"=TO_NUMBER(:Z))
4 - access("COMP"."COMPENSATION_ID"="CASHCOMP"."CASH_COMPENSATION_ID"(+))
Plan Table
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 5 (60)|
| 1 | NESTED LOOPS OUTER | | 1 | 18 | 5 (60)|
|* 2 | INDEX UNIQUE SCAN | SYS_C0015604931 | 1 | 5 | 3 (67)|
| 3 | TABLE ACCESS BY INDEX ROWID| CASH_COMPENSATION | 1 | 13 | 3 (67)|
|* 4 | INDEX UNIQUE SCAN | SYS_C0015604934 | 1 | | |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COMP"."COMPENSATION_ID"=TO_NUMBER(:Z))
4 - access("CASHCOMP"."CASH_COMPENSATION_ID"(+)=TO_NUMBER(:Z))
filter("COMP"."COMPENSATION_ID"="CASHCOMP"."CASH_COMPENSATION_ID"(+))
As you can see, the first and second execution plan (and thus the costs) are different ^_^;
However, my question is: how can I explain in simple words such behaviour to developers?
I have said at least to write two different queries to separate the two cases (input / no input)
but I can't find a simple explanation to tell them: although the col2 (or compensation_id) is indexed
and althgouh the clause ":1 is null" is independent of the tables/columns... Oracle doesn't use
your index (or at least in a poor way: INDEX FULL SCAN).
Regards,
Loïc
March 07, 2007 - 2:05 pm UTC
allow me to expand on this:
"Write a generic SQL query to handle every case so that maintenance is very simple."
it should be:
"Write a generic SQL query to handle every case so that maintenance is pretty hard since the query will be ugly and complicated with lots of functions and of course performance will be at its very worst. But it will be generic."
http://jonathanlewis.wordpress.com/2007/02/14/conditional-sql-2/
How to bind Varchar2 variables?
Maverick, March 08, 2007 - 5:20 pm UTC
Tom, How to put bind variable in Execute immediate for Varchar2 ?
for eg:-
declare
v_desc varchar2(20):='john';
v_sql varchar2(500):='';
v_desc1 varchar2(20):='';
v_id1 integer:=0;
begin
v_sql:='select empno,ename from emp where upper(ename) like ''%'||upper(v_desc)||'%'' and rownum=1';
dbms_output.put_line(v_sql);
Execute immediate v_sql into v_id1,v_desc1 ;
dbms_output.put_line(v_id1||' ,'||v_desc1);
end; How can I bind upper(v_desc) instead of concatenating in this string?
Thanks,
March 08, 2007 - 8:44 pm UTC
scott%ORA9IR2> declare
2 v_desc varchar2(20):='king';
3 v_sql varchar2(500):='';
4 v_desc1 varchar2(20):='';
5 v_id1 integer:=0;
6 begin
7 v_sql:='select empno,ename from emp where upper(ename) like :x and rownum=1';
8 dbms_output.put_line(v_sql);
9 Execute immediate v_sql into v_id1,v_desc1 using '%'||upper(v_desc)||'%';
10 dbms_output.put_line(v_id1||' ,'||v_desc1);
11 end;
12 /
select empno,ename from emp where upper(ename) like :x and rownum=1
7839 ,KING
PL/SQL procedure successfully completed.
Thanks for solution
Maverick, March 08, 2007 - 8:52 pm UTC
Thanks so much Tom. Why didn't I think of that :-(. I tried all combinations inside quotes to no avail ...
Thanks again.
Is there anyway to force Oracle to always peek for a certain query?
Brandon, May 01, 2007 - 7:19 pm UTC
I understand bind variable peeking is good in most OLTP situations, but it seems to me that many/most database applications these days are OTLP/DW hybrids rather than truly dedicated OLTP-only systems. I work mostly with the Baan ERP application and I think it falls under the hybrid category. Baan uses bind variables for all queries and performs very well about 99.9% of the time, but there are a few troublesome queries ever since upgrading to 9i & 10g with the introduction of bind variable peeking where a query is executed once for a single row, and then the exact same query is executed later for a large range of rows. In these cases, the latter execution gets stuck with an inappropriate explain plan (often a MERGE JOIN CARTESIAN/BUFFER SORT) and performance is terrible. I understand this is really a defect of the application - it should differentiate between these two queries rather than sharing a cursor for both of them, but it seems to me that it would be very helpful for a lot of customers if Oracle could provide a work around because many applications seem to suffer from this problem and it isn't always possible/feasible for us to fix the application code. It would be great if Oracle could provide a method of forcing the CBO to always peek at the bind variables for a specific query. One way to do this would be with a hint, e.g. ALWAYS_PEEK, which could then be applied to the problematic query with a stored outline. Is there any undocumented hint or other method of doing this currently? If not, is there any chance of adding it as an enhancement in future versions?
I've already opened an SR about this too and am awaiting response.
Thanks,
Brandon
May 01, 2007 - 9:13 pm UTC
there is not currently, and not as far as I know into the future.
Trigger :NEW.Value :OLD.Value
A reader, May 02, 2007 - 3:41 am UTC
Hi Tom,
We have to develop an Audit Trail for exceptional transcations on important transaction tables. We can not use the built-in audit features because of our requirements. So, we want to develop our own generic procedure to store the values in Audit Trail Table. That procedure will be called in all the triggers (before insert or update on delete for each row) on the selected transaction Tables. Our objective is to call the same procedure in all the tables and so that the signature of the procedure is same. So we want to pass the :OLD.columns or :NEW.columns to that procedure dynamically. But it is not possible to resolve the value of :OLD / :NEW dynamically. What could be the best method to implement it?
May 02, 2007 - 8:20 am UTC
Alberto Dell'Era, May 02, 2007 - 7:14 am UTC
Enhancement request to force bind variable re-peeking
Brandon, May 02, 2007 - 7:09 pm UTC
Yes, it seems we (Alberto, Albert and I) are all in agreement, and I also found a couple old bug/enhancement request notes on metalink regarding the same issue: 2463736 & 4041455, but it looks like nothing has been done on them in years.
I submitted my enhancement request in SR 6299151.992 yesterday. Maybe if we all submit ERs, something might get done?
Specifying partitions in queries
DW, May 03, 2007 - 1:55 pm UTC
I have a question related to Steve Booth's questions in this thread from May 2-3, 2006.
My data is partitioned by month, and my query needs to find data in the "current" partition, something like:
SELECT *
FROM T_Data
WHERE MyDate >= TRUNC(SYSDATE,'MONTH')
AND Fld2 = 1234;
I have tried "forcing" it to the current partition (looking for PARTITION RANGE (SINGLE) in the execution plan) by using "where mydate between trunc(sysdate,'MONTH') and sysdate", but I always get PARTITION RANGE (ITERATOR) or PARTITION RANGE (ALL) unless I specifically identify the partition, as in:
SELECT *
FROM T_Data PARTITION (M_200705)
WHERE MyDate >= TRUNC(SYSDATE,'MONTH')
AND Fld2 = 1234;
Consistent gets and physical reads are always better with the specified partition. I need to do this in an overnight job, and I would rather not have to resort to dynamic SQL. Is there anything else I can try?
May 03, 2007 - 10:59 pm UTC
give me example to work with.
but basically, you are saying:
where mydate >= :bind_variable and fld2=1234;
because sysdate is an ever moving target, it's value depends on when you submit the query. It'll NEVER be partition range single - because it could be ALL partitions!!!
show us the row source operations from tkprofs with and without the partition(pname)
Using a Dynamic Comment to Force Hard Parse
Brandon, May 04, 2007 - 1:20 pm UTC
Tom,
I found another thread where someone was asking for similar functionality as discussed above to force certain queries to always be hard parsed and have their bind variables peeked, and you suggested a workaround of using dynamic SQL to create the query with the return value of the systimestamp function in the comment/hint section:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7038986332061#30856337401937 This is a great idea, but in my situation I can't use dynamic SQL. Is there anyway to execute a function in the comment area of static query? I thought I saw some special syntax for doing this before, but now I can't find it.
Thanks,
Brandon
May 04, 2007 - 3:55 pm UTC
this is a horrible idea.
remember I also wrote there:
but, it'll kill your system if that is done more than "once in a long while"
why can't you use dynamic sql?
Will only be done once in a long while
Brandon, May 04, 2007 - 4:08 pm UTC
Yes, I caught that part too, but the query I'm working with is only executed a few times a day so I'm not too concerned. It won't be flooding my shared pool and the trade off of extra hard parsing for the improved explain will be well worth it. I can't use dynamic SQL because this is a 3rd-party app (Baan ERP) and I don't have source code to their database driver that sends the queries to Oracle. All I can do is inject some text into the comment/hint section of the query. I just figured out that this will work:
alter session set nls_date_format='DD-MON-YY hh24:mi:ss';
select /*+ &_DATE */ sysdate from dual;
But, I think that will only work in SQL*Plus.
May 08, 2007 - 9:48 am UTC
if you cannot do dynamic sql, I'm not seeing how you can dynamically change the comment.
How are binds handled where the condition does not involve a column?
Ray DeBruyn, May 17, 2007 - 3:08 pm UTC
Hi Tom,
I have many PLSQL Web Toolkit pages having cursors declared with where clauses like:
...
AND ( p_name IS NULL
OR p_name IS NOT NULL AND name = p_name)...
The plan should be quite different depending on whether p_name is null or not null. Does the plan get set based on the first parse (say with a not null value and then always always assume a not null value in future executions)?
I may have 3 or 4 such parameters used in a single cursor. Do you see any issue in doing it this way?
May 18, 2007 - 3:52 pm UTC
Usage of Bind Variable for a Frequently used Procedure
RAM, June 06, 2007 - 12:18 pm UTC
Hi tom ,
We have a procedure which is used to take a report and runs every day, the procedure used gets different arguments and because of this we are having a hard parse every execute
To be more clear
we are having hard pares (7327 ) times of
BEGIN get_login_name('<value>'); END; The procedure would be called in the below fashion
BEGIN get_login_name('annehammond@greenhithe.freeserve.co.uk'); END;
BEGIN get_login_name('davenlaura@sandpiper43.freeserve.co.uk'); END;
BEGIN get_login_name('james.ballingall@partnershipsuk.org.uk'); END;
BEGIN get_login_name('jenny.marriott@english-heritage.org.uk'); END;
BEGIN get_login_name('annamilldown@tithefarme.freeserve.co.uk'); END;
BEGIN get_login_name('maxine.bernard@jobcentreplus.gsi.gov.uk'); END; Is there a way we can use bind variables here
ie to convert the above to something like
BEGIN get_login_name(':b'); END;so that the hard parses are avoided
Thanks
June 06, 2007 - 9:19 pm UTC
sure, just bind it???
begin get_login_name( :b ); end;
(no quotes, just like sql)
It seems you already know what to do - so do it?
Binds
RAM, June 07, 2007 - 6:24 am UTC
Hi tom ,
Thanks for the response , was just cross checking whether i am going in the right direction.
Thanks again
Bind variable and sequence.nextval
Sandro, July 18, 2007 - 11:45 am UTC
create sequence s;
create table t1 (c1 number, c2 varchar2(10), c3 varchar2(10), c4 varchar2(10), c5 varchar2(10));
create table t2 (c1 number, c2 varchar2(10), c3 varchar2(10), c4 varchar2(10), c5 varchar2(10));
insert into t1 values (1, 'a', 'b', 'c', 'd');
commit;
select * from t1;
C1 C2 C3 C4 C5
---------- ---------- ---------- ---------- ----------
1 a b c d
declare
t_trc_file varchar2(256) := 'alter session set tracefile_identifier=' || chr(39) || user || to_char(sysdate, 'YYYYDDMMHH24MISS') || chr(39);
t_trc_stat varchar2(256) := 'alter session set timed_statistics=true';
t_trc_size varchar2(256) := 'alter session set max_dump_file_size=unlimited';
t_trc_sqlSTART varchar2(256) := 'alter session set events ' || chr(39) || '10046 trace name context forever, level 12' || chr(39);
t_trc_sqlSTOP varchar2(256) := 'alter session set events ' || chr(39) || '10046 trace name context OFF'|| chr(39);
begin
execute immediate t_trc_file;
execute immediate t_trc_stat;
execute immediate t_trc_size;
execute immediate t_trc_sqlSTART; -- START TRACE
for i in 1 .. 10000 loop
insert into t2
select s.nextval
,c2
,c3
,c4
,c5
from t1;
end loop;
execute immediate t_trc_sqlSTOP; -- START TRACE
end;
/
tkprof C:\oracle\admin\orcl9\udump\orcl9_ora_3188_test20071807173427.trc C:\oracle\admin\orcl9\udump\orcl9_ora_3188_test20071807173427.txt explain=test/test table=test.plan_table sys=no sort=prsela,fchela,exeela
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 1.31 3.51 0 70026 10827 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 1.31 3.51 0 70026 10827 10000
question:
why
insert into t2
select s.nextval
,c2
,c3
,c4
,c5
from t1;
Use correctly bind and pars once?
I thought has to do...
declare
n integer;
begin
select s.nextval into n from dual;
insert into t2
select n
,c2
,c3
,c4
,c5
from t1;
...for correct use of bind variables.
July 18, 2007 - 12:55 pm UTC
you thought wrong?
I don't know why you would think that - sequences are sort of like a function, the value is passed into the sql statement.
How it get parsed that many times.
Karthick, January 17, 2008 - 5:17 am UTC
How your dynamic insert that used bind variable got parsed that many times. For me it get parsed only once.
BEGIN
FOR i IN 1 .. 100000
LOOP
INSERT /* STATIC SQL */ INTO t VALUES ( i );
END LOOP;
END;
/
BEGIN
FOR i IN 1 .. 100000
LOOP
EXECUTE IMMEDIATE 'insert /* DYNAMIC SQL */ into t values ( :i )' USING i;
END LOOP;
END;
/
INSERT INTO T VALUES ( :B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100000 13.76 19.72 0 169 304722 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100001 13.76 19.72 0 169 304722 100000
insert /* DYNAMIC SQL */ into t values ( :i )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100000 14.25 14.38 0 171 304712 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100001 14.25 14.38 0 171 304712 100000
Also another thing i noted was when rewriting my static insert it removed the comment any idea why.
iam using
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
January 17, 2008 - 11:16 am UTC
you are using 10g
I was using 8i or 9i
10g introduce a native dynamic sql feature that would cache the last statement, so in 9i and before
execute immediate 'insert into t (x) values (:x)';
would be:
parse insert...
bind insert ...
execute insert...
in 10g it would be:
if ( last_statement_executed_here <> insert ... )
then
parse insert....
last_statement_executed_here = insert....
end if;
bind insert...
execute insert....
that is why everything on this site has a version and a timestamp associated with it so you can tell what release I was using and how long ago we did it.
Can we avoid soft parse also??
Karthick, January 17, 2008 - 5:40 am UTC
Ok i know what is soft and hard parsing. I learned from you (your site and your book) only.
I was reading this followup
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7832114438832#627719400346168840 and the below comment got me confused. So i came back here to read it again.
insert /* DYNAMIC SQL */ into t values ( :i )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 100000 4.56 4.40 0 0 0 0
Execute 100000 10.97 11.04 0 100168 302764 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200000 15.53 15.44 0 100168 302764 100000
look at that -- terrible. parse counts like crazy. way over parsed. at least we
bound -- but the price we paid for parsing is extremely high. They were soft parses but
a parse is a parse and something to be avoided when possible.A bind variable has been used. And yet the statement has parsed 100000 times. And you say they are soft parse meaning it gose for syntax and sematic check alone.
I was in an assumption that any query that is submitted gose throught those two stages. A query cannot get executed without that two checks. Am i wrong here.
What you mean by avoide a soft parse.
January 17, 2008 - 11:18 am UTC
syntax, semantic alone - you say that like they are "free", "cheap" or "easy"... they are none of the above.
to avoid a soft parse is to - well - avoid a soft parse, to not reparse a sql statment, to just bind and execute it over and over.
Karthick Pattabiraman, January 18, 2008 - 5:19 am UTC
Thanks for the pseudocode it was helpfull.
BEGIN
FOR i IN 1 .. 100000
LOOP
INSERT /* STATIC SQL */ INTO t VALUES ( i );
END LOOP;
END;
/
Will the pseudocode for static sql will be like this ?
parese insert
for i in 1..1000
loop
bind insert
execute
end loop
January 19, 2008 - 10:34 pm UTC
it is even better than that for a stored procedure, but for the anonymous block, sure, that is it - parse once, execute many.
karthick, January 20, 2008 - 2:01 am UTC
Can you give the pseudocode for stored procedures?
January 20, 2008 - 7:55 am UTC
a stored procedure might not parse it on a given call, they cache it
for a stored procedure, it might just be:
loop
bind it
execute it
end loop
if it was already called once before in your session
How to hard parse to avoid the peek for other bind values
Tom, January 29, 2008 - 11:35 am UTC
I believe I am experiencing the side effect of the peeking.
You wrote:
what I'm saying is -- ok, you have a piece of code. It does a poplist, populates some list box.
that piece of code will either ALWAYS index range scan (most likely) or NEVER. The design of your
interactive OLTP application is such that it is ALWAYS going to use inputs that permit an efficient
index range scan -- or, it is not. It is the very nature of your application here.
We have a job that runs every night and loops through a cursor using the below query:
SELECT
DISTINCT COL1
, COL2
FROM X
WHERE COL3 = :b1
Plan
SELECT STATEMENT ALL_ROWS Cost: 105,871 Bytes: 147,437,130 Cardinality: 6,410,310
2 HASH UNIQUE Cost: 105,871 Bytes: 147,437,130 Cardinality: 6,410,310
1 TABLE ACCESS FULL TABLE OWNER.X Cost: 61,665 Bytes: 147,437,130 Cardinality: 6,410,310
There is a non-unique index ind1 on COL3:
SELECT
DISTINCT COL1
, COL2
FROM X
WHERE COL3 = 99
Plan
SELECT STATEMENT ALL_ROWS Cost: 5 Bytes: 23 Cardinality: 1
3 HASH UNIQUE Cost: 5 Bytes: 23 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE OWNER.X Cost: 4 Bytes: 23 Cardinality: 1
1 INDEX RANGE SCAN INDEX OWNER.ind1 Cost: 3 Cardinality: 1
On some days it uses the index and the process is complete in about 10 minutes. On other days it runs all day long and causes production support problems.
After reviewing all of the post on this page I believe we may be having a peeking problem. I am not sure what would make oracle not want to use the index for some of the COL3 values other than peeking. We have millions of rows in the table.
Table X is truncated and repopulated in the step before this process executes. We analyze the table before the process begins:
dbms_stats.gather_table_stats(ownname => schema_i
,tabname => table_name_i
,degree => degree_i
,cascade => TRUE);
I believe that on some mornings the first value of COL3 makes up a high percentage of table X and oracle therefore does a full table scan. The problem is that it continues to do a full table scan for all of the other values causing it to run all day instead of 10 minutes.
I have listed the COL3 data profile:
COL3 COUNT(*)
23371 12771172
23372 607864
23376 3021962
23377 1412095
24641 146147
25808 530440
23375 31767560
25809 7807889
22734 9069642
17343 6741786
23373 2902454
24640 90895
I show that when Oracle peeks and the COL3 is 23371 it does a full table scan but it continues to do a full table scan for all of the other COL3 values also.
Is there an easy way to get oracle to hard parse for each COL3 value of the cursor or do I need to rewrite the code in a different manner?
January 30, 2008 - 9:20 am UTC
since col3 has so few distinct values, you do not need to bind it, just use a literal instead
After more research into the code.
Tom, January 29, 2008 - 12:23 pm UTC
It looks like the SQL cursor is in a procedure that is called once for each COL3 value. Would this be less of a peeking problem and maybe related to cursor sharing issue instead?
January 30, 2008 - 9:31 am UTC
it is classic bind peeking - the cursor is hard parsed once and the plan chosen by the first value. So instead of:
create procedure foo( p_col3 in number )
as
cursor c is select * from t where col3 = p_col3;
begin
open c;
loop
fetch c into ....;
exit when c%notfound;
end loop;
close c;
end;
do this
create procedure foo( p_col3 in number )
as
c sys_refcursor;
begin
open c for 'select * from t where col3 = ' || p_col3;
loop
fetch c into ....;
exit when c%notfound;
end loop;
close c;
end;
as long as col3 has few values, as you indidcated it did above.
Root Cause
Tom, January 30, 2008 - 7:10 am UTC
This appears to be a case of developers believing that they can join oracle tables better than oracle. This code has a driving cursor fetching about 3000 records. For each of the 3000 records it is executing the SQL statement:
SELECT
DISTINCT COL1
, COL2
FROM X
WHERE COL3 = :b1
even though there are only 12 distinct COL3 values in table X. Therefore they are executing this query 2988 times unnecessarily. They could have added this to the driving query and let oracle filter them out and we would not be having this conversation. I have been told that I am not allowed to redesign all of the code therefore I need to get their lookup query to run faster for each of the 3000 COL3 bind values.
In the case when the first execution value is in the table and has a large representation Oracle correctly does a full table scan. Problem is that for each of the queries that follow oracle finds the execution plan in the SGA and executes the full table scan again. To force Oracle to hard parse each of the 3000 executions I am considering using an OPEN-FOR ref cursor and turning the SQL into dynamic SQL with the COL3 bind value concatenated. I believe that this will force a hard parse and allow oracle to full table scan on COL3 values that have a large number of rows and use the index for the other values. I would like to rewrite the entire module but can¿t.
This may no longer belong to this section. I do believe it would be of use for people who are suffering the same problem and am interested in your take on the issue.
Thank you.
January 30, 2008 - 10:26 am UTC
what a bunch of hooey. I hate "developers" some days (I am a developer by the way... One that understands what databases can and cannot do. For former is a long long list, the latter is pretty short)
Problem Solved
Tom, January 31, 2008 - 1:30 pm UTC
Thank you for your time. The fix went into production last night and the batch job runs in just a couple of minutes now :) vs 24 hours a day.
Bind variables in dynamic SQL
Gary Wicke, February 07, 2008 - 2:41 pm UTC
Hi Tom
I have a piece of PL/SQL code that is returning an error and I can't see the issue. I have a table NET_SALES_JE_PROGRAMS that contains the columns PROGRAM (VARCHAR2) and DOC_TYPE (VARCHAR2).
I have another table that contains similar column names but represent the combination of PROGRAM and DOC_TYPE that I want to DELETE from the first table. Since the DOC_TYPE in the second table could be a '%', saying that ALL DOC_TYPEs for that PROGRAM should be deleted, I am using the LIKE function on the concatenation of PROGRAM and DOC_TYPE.
Here is my PL/SQL code:
DECLARE
l_program owb_rtt.net_sales_program_exclude.program%TYPE;
l_doc_type owb_rtt.net_sales_program_exclude.doc_type%TYPE;
BEGIN
FOR xrow in (select program, doc_type from OWB_RTT.NET_SALES_PROGRAM_EXCLUDE where STATUS_CD = 'ACTIVE')
LOOP
l_program := xrow.program;
l_doc_type := xrow.doc_type;
execute immediate 'delete from OWB_RTT.NET_SALES_JE_PROGRAMS where PROGRAM||DOC_TYPE like '':prog||:doctype''' USING l_program, l_doc_type;
dbms_output.put_line('Program: ' || l_program || ' Doc Type: ' || l_doc_type);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN dbms_output.put_line('Error: ' || SQLCODE || ' Error msg: ' || SQLERRM);
END;
/
I am getting the following error message returned from my exception: Error: -1006 Error msg: ORA-01006: bind variable does not exist
I'm assuming it's the way I'm trying to use the combination of the cursor and the dynamic SQL but I can't seem to find the trick.
Many thanks for pointing me in the right direction.
/gary
February 07, 2008 - 3:49 pm UTC
you do not quote binds
your delete is literally:
delete from x.y where c1||c2 like ':prog||:doctype'
you are comparing c1||c2 to the LITERAL STRING :prog||:doctype
Just because there is a colon in a string doesn't make it a bind.
execute immediate '
delete from x.y
where c1||c2 like :prog||:doctype
' using l_program, l_doc_type;
and for the love of everything DELETE THAT EXCEPTION HANDLER NOW and never ever do that again.
when others, not followed by RAISE or RAISE_APPLICATION_ERROR is a bug 99.99999999999% of the time you see it. Here, it most definitely is a bug.
read this
https://www.oracle.com/technetwork/issue-archive/2007/07-jul/o47asktom-092692.html
A reader, February 25, 2008 - 4:47 pm UTC
Hey Tom:
We have hard time on our database now. we tuned the query, and then put them on live. But some time when we checked the plan on live, the query is using the different plan with what we were tuning it. and the gets is very high. I guess it should come from bind peeking. (we are using "force" in cursor sharing. ) when the query first came into the system, with the value it had, pLan A might be better for it. so in the following, it will not hard parse. it will keep using plan A, although it may not be the optimal plan for the other values.
Then when this happens, how can we change the plan for the queries. how can we change the plan already cached in the system?
Is there a good method to overcome bind peeking?
Thank you very much
February 25, 2008 - 4:58 pm UTC
... we tuned the query, .... (we are using "force" in cursor
sharing. ) ....
you get the irony in that statement. If you tuned, you would not be using cursor sharing =force, you would have already fixed the BUG, the obvious BUG in the code... So, you have not tuned anything yet. You haven't even started (since 99% of the gains to be had from tuning start from looking at the application - not automagical switches and things in the database)
http://asktom.oracle.com/Misc/sqltracetrue-part-two.html
A reader, February 26, 2008 - 2:35 pm UTC
Hey Tom:
We have OLTP system. I read some discussion in here, so for OLTP, it is better to cursor_sharing= force at first to help the application. And after developers fix their bugs in the code, (using their own bind variable in the right way), we can change the cursor_sharing= Exact. is this what you mean?
and what is the difference between the "Exact" and the "Similar". this is my understanding, not sure if it is correct: in exact mode, optimizor will soft parse all the queries has the similar structure but different literals, no matter there is a histogram on the table, index or not. but in similar mode, optimizor will consider the existing plan and histogram to make decision if it needs to hard parse or not. any other difference? like the number of soft parse...
this is from you blog:
"Is it possible to force Oracle somehow to peek at the bind variables of a SQL without the cost of doing a hard parse?
cursor_sharing=similar with literals will do that actually - at the huge expense of a soft parse."
will "similar" cost more soft parse than force?
And next question comes. Will the frequent gethering histogram will affect the database performance?
and another question: we are now using "force". when we saw a query in system, we had 39 sessions having the same queries, and this query is on the top of the activity in grid control. the plan is not the optimal plan. This should come from the bind peeking.So how can we get the query hard parsed again. I remember you said the autotrace will alway hard parse the query when generating execution plan, and also the sql_trace=true hard parse the query in the first time too. If I run the query with autotrace or sql_trace=true at this time, will the plan cached in the shared pool for this query be changed? will the queries which were using bad plan will swith to using the new parsed plan?
One bind variable
A reader, March 05, 2008 - 11:00 am UTC
Hi Tom
How can I get the same result using one bind variable instead of two bind variable
SELECT DECODE(:TYP,'NULL',NULL,:TYP) FROM DUAL
Thanks
March 06, 2008 - 7:37 am UTC
select decode( x, 'null', null, x ) from (select :typ x from dual);
but why would you use sql to do that. if you are in a 3gl (java, c, plsql whatever) JUST WRITE CODE - do not bother the server for something so trivial.
SELECT NULLIF(:TYP,'NULL') FROM DUAL
A reader, March 06, 2008 - 8:58 am UTC
values of bind variables when explain plan has been created
Nenad Noveljic, March 27, 2008 - 4:01 am UTC
Is it possible to find out from some dictionary views, what values of bind variables has been used to create the current explain plan?
March 27, 2008 - 10:57 am UTC
10gr2....
ops$tkyte%ORA10GR2> create table t ( x int );
Table created.
ops$tkyte%ORA10GR2> insert into t select 5 from all_objects;
49884 rows created.
ops$tkyte%ORA10GR2> insert into t values ( 99 );
1 row created.
ops$tkyte%ORA10GR2> create index t_idx on t(x);
Index created.
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all indexed columns' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> variable n number;
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :n := 99;
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t where x = :n;
X
----------
99
ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from t where x = :n
Plan hash value: 2946670127
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INDEX RANGE SCAN| T_IDX |
----------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :N (NUMBER): 99
18 rows selected.
bind peeking in 11g
A reader, April 04, 2008 - 4:35 pm UTC
Hey Tom:
I read your article in the Oracle Megazine about the bind peeking, and you said the 11g has the new feature of "intelligent cursor sharing" which enables the optimizer to generate different plan for diffrent bind vavriable. and I tested the test case you provide in 11g, I found some interesting thing:
create table ttt
as
select case when rownum = 1 then 1 else 99 end id, a.*
from all_objects a
/
alter table ttt modify object_id null;
create index ttt_idx on ttt(id);
begin
dbms_stats.gather_table_stats
( user, 'TTT',
method_opt=> 'for all indexed columns',cascade=>TRUE);
end;
/
SQL> variable id number
SQL> exec :id := 99
SQL> select * from ttt where id = :id;
.........
67965 rows selected.
SQL> select sql_id,sql_text,to_char(LAST_ACTIVE_TIME,'DD_MM_YYYY HH24:MI'),CHILD_NUMBER,FIRST_LOAD_TIME
2 from v$sql
3 where upper(sql_text) like '% * FROM TTT%';
SQL_ID
-------------
SQL_TEXT
TO_CHAR(LAST_ACT CHILD_NUMBER FIRST_LOAD_TIME
---------------- ------------ -------------------
90z9b4d8swhua
select sql_id,sql_text,to_char(LAST_ACTIVE_TIME,'DD_MM_YYYY HH24:MI'),CHILD_NUMBER,FIRST_LOAD_TIME from v$sql where upper(sql_text) like '% * FROM TTT%'
04_04_2008 16:54 0 2008-04-04/16:52:43
gx8dupra9bzqj
select * from ttt where id = :id
04_04_2008 16:54 0 2008-04-04/16:53:28
SQL> exec :id := 1
PL/SQL procedure successfully completed.
SQL> select * from ttt where id = :id;
...
1 rows selected.
SQL>
SQL> select sql_id,sql_text,to_char(LAST_ACTIVE_TIME,'DD_MM_YYYY HH24:MI'),CHILD_NUMBER,FIRST_LOAD_TIME
2 from v$sql
3 where upper(sql_text) like '% * FROM TTT%';
SQL_ID
-------------
SQL_TEXT
TO_CHAR(LAST_ACT CHILD_NUMBER FIRST_LOAD_TIME
---------------- ------------ -------------------
90z9b4d8swhua
select sql_id,sql_text,to_char(LAST_ACTIVE_TIME,'DD_MM_YYYY HH24:MI'),CHILD_NUMBER,FIRST_LOAD_TIME from v$sql where upper(sql_text) like '% * FROM TTT%'
04_04_2008 16:55 0 2008-04-04/16:52:43
gx8dupra9bzqj
select * from ttt where id = :id
04_04_2008 16:54 0 2008-04-04/16:53:28
????????????????????? and through display the cursor, the id=1 is using the same plan with 99. why the last active time is not changed?
****** I executed it again
SQL> select * from ttt where id = :id
2 ;
SQL> select sql_id,sql_text,to_char(LAST_ACTIVE_TIME,'DD_MM_YYYY HH24:MI'),CHILD_NUMBER,FIRST_LOAD_TIME
2 from v$sql
3 where upper(sql_text) like '% * FROM TTT%';
SQL_ID
-------------
SQL_TEXT
TO_CHAR(LAST_ACT CHILD_NUMBER FIRST_LOAD_TIME
---------------- ------------ -------------------
b7h8dsvsx89y5
select * from ttt where id = :id
04_04_2008 16:57 0 2008-04-04/16:57:06
90z9b4d8swhua
select sql_id,sql_text,to_char(LAST_ACTIVE_TIME,'DD_MM_YYYY HH24:MI'),CHILD_NUMBER,FIRST_LOAD_TIME from v$sql where upper(sql_text) like '% * FROM TTT%'
04_04_2008 16:57 0 2008-04-04/16:52:43
gx8dupra9bzqj
select * from ttt where id = :id
04_04_2008 16:54 0 2008-04-04/16:53:28
???????????????why this time there is another sql_id with the same sql_text, and if you check the plan, you will find they are the same plan "full table scan".
*************and I execute it again
SQL> select * from ttt where id = :id;
SQL> select sql_id,sql_text,to_char(LAST_ACTIVE_TIME,'DD_MM_YYYY HH24:MI'),CHILD_NUMBER,FIRST_LOAD_TIME
2 from v$sql
3 where upper(sql_text) like '% * FROM TTT%';
SQL_ID
-------------
SQL_TEXT
TO_CHAR(LAST_ACT CHILD_NUMBER FIRST_LOAD_TIME
---------------- ------------ -------------------
b7h8dsvsx89y5
select * from ttt where id = :id
04_04_2008 16:57 0 2008-04-04/16:57:06
90z9b4d8swhua
select sql_id,sql_text,to_char(LAST_ACTIVE_TIME,'DD_MM_YYYY HH24:MI'),CHILD_NUMBER,FIRST_LOAD_TIME from v$sql where upper(sql_text) like '% * FROM TTT%'
04_04_2008 17:14 0 2008-04-04/16:52:43
gx8dupra9bzqj
select * from ttt where id = :id
04_04_2008 17:14 0 2008-04-04/16:53:28
gx8dupra9bzqj
select * from ttt where id = :id
04_04_2008 17:14 1 2008-04-04/16:53:28
*************and here comes another one, I am totally confused, what is this one from?
????????? and when I set autotrace traceonly, I found
SQL> set autotrace traceonly
SQL> select * from ttt where id = :id;
Execution Plan
----------------------------------------------------------
Plan hash value: 774701505
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33983 | 3451K| 289 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TTT | 33983 | 3451K| 289 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=TO_NUMBER(:ID))
but in 10gr2, the plan will show using index. since autotrace generate plan from hard parse. why here in 11g, the autotrace show the real plan? is that correct in 11g, autotrace gives the plan from v$sql table instead of hard parse?
notes:
the value 99 only been used once in the very beginning, all the following query are binded to 1;
Thank you very much!!!
April 07, 2008 - 8:32 am UTC
ops$tkyte%ORA11GR1> alter system flush shared_pool;
System altered.
ops$tkyte%ORA11GR1> create table ttt
2 as
3 select case when rownum = 1 then 1 else 99 end id, a.*
4 from all_objects a where rownum <= 20000
5 /
Table created.
ops$tkyte%ORA11GR1> alter table ttt modify object_id null;
Table altered.
ops$tkyte%ORA11GR1> create index ttt_idx on ttt(id);
Index created.
ops$tkyte%ORA11GR1> begin
2 dbms_stats.gather_table_stats
3 ( user, 'TTT',
4 method_opt=> 'for all indexed columns',cascade=>TRUE);
5 end;
6 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR1> SET SERVEROUTPUT OFF
ops$tkyte%ORA11GR1> variable id number
ops$tkyte%ORA11GR1> exec :id := 99
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR1> select * from ttt where id = :id;
.....
19999 rows selected.
<b>rather than use autotrace, we'll use dbms_xplan directly, here I
call display cursor, this shows the result of the LAST EXECUTED SQL
in my session - using the not documented +peeked_binds, we can see
what bind values were used to optimize this query...</b>
ops$tkyte%ORA11GR1> select * from table(dbms_xplan.display_cursor(null,null,'typical +peeked_binds'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID gx8dupra9bzqj, child number 0
-------------------------------------
select * from ttt where id = :id
Plan hash value: 774701505
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 41 (100)|
|* 1 | TABLE ACCESS FULL| TTT | 19999 | 820K| 41 (0)| 00:00:01
------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :ID (NUMBER): 99
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:ID)
23 rows selected.
<b>we'll also look in v$sql at the new columns "is_bind_sensitive" (for 11g and adaptive cursor
sharing, is the query subject to changes in plans due to binds changing over time....) and
"is_bind_aware" (if we are sensitive to binds, have we become aware yet of a performance issue
with regards to the bind values).
Right now, our query is bind sensitive - the optimizer knows to look for problems with the plan over
time since different binds would lead to different plans. We are not aware yet of any problems
since we've only just run the query once...</b>
ops$tkyte%ORA11GR1> select sql_id, is_bind_sensitive, is_bind_aware from v$sql where sql_text='select * from ttt where id = :id';
SQL_ID I I
------------- - -
gx8dupra9bzqj Y N
<b> now, chang the binds....</b>
ops$tkyte%ORA11GR1> exec :id := 1;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR1> select * from ttt where id = :id;
ID OBJECT_ID OBJECT_NAME
---------- ---------- ------------------------------
OWNER OBJECT_TYPE
------------------------------ -------------------
1 20 ICOL$
SYS TABLE
ops$tkyte%ORA11GR1> select * from table(dbms_xplan.display_cursor(null,null,'typical +peeked_binds'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID gx8dupra9bzqj, child number 0
-------------------------------------
select * from ttt where id = :id
Plan hash value: 774701505
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 41 (100)|
|* 1 | TABLE ACCESS FULL| TTT | 19999 | 820K| 41 (0)| 00:00:01
------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :ID (NUMBER): 99
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:ID)
23 rows selected.
<b>same plan - same child number - same everything - but the database did notice a problem, when it ran
this query, it was expecting LOTS of rows, it got one. So, the next time we run it....</b>
ops$tkyte%ORA11GR1> select * from ttt where id = :id;
ID OBJECT_ID OBJECT_NAME
---------- ---------- ------------------------------
OWNER OBJECT_TYPE
------------------------------ -------------------
1 20 ICOL$
SYS TABLE
ops$tkyte%ORA11GR1> select * from table(dbms_xplan.display_cursor(null,null,'typical +peeked_binds'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID gx8dupra9bzqj, child number 1
-------------------------------------
select * from ttt where id = :id
Plan hash value: 240739660
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (1
| 1 | TABLE ACCESS BY INDEX ROWID| TTT | 1 | 42 | 2
|* 2 | INDEX RANGE SCAN | TTT_IDX | 1 | | 1
------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :ID (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:ID)
24 rows selected.
<b>it automagically did the hard parse for us and got an optimal plan for this bind value, peeking at
v$sql again</b>
ops$tkyte%ORA11GR1> select sql_id, is_bind_sensitive, is_bind_aware from v$sql where sql_text='select * from ttt where id = :id';
SQL_ID I I
------------- - -
gx8dupra9bzqj Y N
gx8dupra9bzqj Y Y
<b>we see the plan is now BIND AWARE - there are two of them - the first one will not be used again, since we
became aware of a problem with binds - for example, even though we parsed with 99 once, we'll do it again to
get the bind aware plan:</b>
ops$tkyte%ORA11GR1> exec :id := 99
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR1> set termout off
ops$tkyte%ORA11GR1> select * from ttt where id = :id;
....
19999 rows selected.
ops$tkyte%ORA11GR1> select * from table(dbms_xplan.display_cursor(null,null,'typical +peeked_binds'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID gx8dupra9bzqj, child number 2
-------------------------------------
select * from ttt where id = :id
Plan hash value: 774701505
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 41 (100)|
|* 1 | TABLE ACCESS FULL| TTT | 19999 | 820K| 41 (0)| 00:00:01
------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :ID (NUMBER): 99
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:ID)
23 rows selected.
ops$tkyte%ORA11GR1> select sql_id, is_bind_sensitive, is_bind_aware from v$sql where sql_text='select * from ttt where id = :id';
SQL_ID I I
------------- - -
gx8dupra9bzqj Y N
gx8dupra9bzqj Y Y
gx8dupra9bzqj Y Y
ops$tkyte%ORA11GR1> spool off
bind peeking in 11g
A reader, April 07, 2008 - 9:05 am UTC
This is a great explanation!!! i will try it by myself.
THANK YOU VERY MUCH TOM!!!
vaibhav, June 02, 2008 - 5:19 am UTC
PROCEDURE sps_find_transactions(p_service_id IN mhs_transactions.service_id%TYPE,
p_interaction_id IN VARCHAR2,
p_originating_party_id IN mhs_transactions.originating_party_id%TYPE,
p_destination_party_id IN mhs_transactions.destination_party_id%TYPE,
p_originating_system_id IN mhs_transactions.originating_system_id%TYPE,
p_destination_system_id IN mhs_transactions.destination_system_id%TYPE,
p_manual_retry_attempts IN mhs_transactions.manual_retry_attempts%TYPE,
p_manual_attempts_flag IN NUMBER,
p_error_code IN mhs_errors.error_code%TYPE,
p_total_attempts IN mhs_sched_processing.total_attempts%TYPE,
p_total_attempts_flag IN NUMBER,
p_from_timestamp IN mhs_transactions.persistence_timestamp%TYPE,
p_to_timestamp IN mhs_transactions.persistence_timestamp%TYPE,
p_status_type IN mhs_transaction_status.status_type%TYPE,
p_order_by_col IN VARCHAR2,
p_order_type IN VARCHAR2,
p_batchsize IN NUMBER,
o_count_flag OUT NUMBER,
o_cur_find_transactions OUT SYS_REFCURSOR)
AS
/************************************************************************************************************
This procedure will open a sys refcursor that will return records from the tables MHS_TRANSACTIONS,
MHS_TRANSACTION_STATUS and MHS_MESSAGE_ID_MAP based on the input parameters.
************************************************************************************************************/
v_sql VARCHAR2(4000);
v_result_count NUMBER := 0;
v_transaction_id mhs_transactions.transaction_id%TYPE;
v_interaction_id mhs_transactions.interaction_id%TYPE;
v_status_type mhs_transaction_status.status_type%TYPE;
v_message_id_type mhs_message_id_map.message_id_type%TYPE;
v_message_id mhs_message_id_map.message_id%TYPE;
v_is_ref mhs_message_id_map.is_ref%TYPE;
v_originating_party_id mhs_transactions.originating_party_id%TYPE;
v_destination_party_id mhs_transactions.destination_party_id%TYPE;
v_originating_system_id mhs_transactions.originating_system_id%TYPE;
v_destination_system_id mhs_transactions.destination_system_id%TYPE;
v_persistence_timestamp mhs_transactions.persistence_timestamp%TYPE;
v_message_timestamp mhs_transactions.message_timestamp%TYPE;
BEGIN
o_count_flag := 0;
v_sql := 'SELECT DISTINCT mt.transaction_id, interaction_id, status_type, message_id_type, message_id, is_ref, message_timestamp, '||CHR(10)||
'originating_party_id, destination_party_id, originating_system_id, destination_system_id, persistence_timestamp '||CHR(10)||
'FROM mhs_message_id_map mmip, mhs_errors me, mhs_sched_processing msp, mhs_transactions mt, '||CHR(10);
/* If p_status_type is null, then status_type should be the latest status from the view MHS_TRANSACTION_STATUS_V.
Else, status_type should be the input p_status_type.
Include appropriate table names in the FROM clause */
IF (p_status_type IS NOT NULL) THEN
v_sql := v_sql || 'mhs_transaction_status mts '||CHR(10);
ELSE
v_sql := v_sql || 'mhs_transaction_status_v mtsv, mhs_transaction_status mts '||CHR(10);
END IF;
v_sql := v_sql || 'WHERE mt.transaction_id = msp.transaction_id '||CHR(10)||
'AND msp.transaction_id = mmip.transaction_id '||CHR(10)||
'AND mmip.transaction_id = me.transaction_id '||CHR(10)||
'AND me.transaction_id = mts.transaction_id '||CHR(10);
/* If p_service_id is null, then do not include it in the WHERE clause. */
IF (p_service_id IS NOT NULL) AND (UPPER(p_service_id) NOT IN ('ALL', 'ANY')) THEN
v_sql := v_sql || 'AND service_id = :p_service_id '||CHR(10);
ELSE
v_sql := v_sql || 'AND NVL(service_id, ''1'') = (CASE WHEN 1=2 THEN :p_service_id ELSE NVL(service_id, ''1'') END) '||CHR(10);
END IF;
/* If p_interaction_id is null, then do not include it in the WHERE clause.
Else, call the function REPLACE_FUNC to separate out the comma-separated multiple Interaction IDs*/
IF (p_interaction_id IS NOT NULL) AND (UPPER(p_interaction_id) NOT IN ('ALL', 'ANY')) THEN
v_sql := v_sql || 'AND interaction_id IN ' || replace_func(p_interaction_id);
v_sql := v_sql || CHR(10);
END IF;
v_sql := v_sql || 'AND NVL(originating_party_id, ''1'') = NVL(:p_originating_party_id, NVL(originating_party_id, ''1'')) '||CHR(10)||
'AND NVL(destination_party_id, ''1'') = NVL(:p_destination_party_id, NVL(destination_party_id, ''1'')) '||CHR(10)||
'AND NVL(originating_system_id, ''1'') = NVL(:p_originating_system_id, NVL(originating_system_id, ''1'')) '||CHR(10)||
'AND NVL(destination_system_id, ''1'') = NVL(:p_destination_system_id, NVL(destination_system_id, ''1'')) '||CHR(10);
IF (p_manual_retry_attempts IS NOT NULL) THEN
IF (p_manual_attempts_flag = 1) THEN
v_sql := v_sql || 'AND manual_retry_attempts >= :p_manual_retry_attempts '||CHR(10);
ELSE
v_sql := v_sql || 'AND manual_retry_attempts = :p_manual_retry_attempts '||CHR(10);
END IF;
END IF;
/* If p_to_timestamp is null, then default is sysdate */
IF (p_to_timestamp IS NOT NULL) THEN
v_sql := v_sql || 'AND persistence_timestamp BETWEEN ''' || p_from_timestamp || ''' AND ''' || p_to_timestamp || ''' '||CHR(10);
ELSE
v_sql := v_sql || 'AND persistence_timestamp BETWEEN ''' || p_from_timestamp || ''' AND systimestamp '||CHR(10);
END IF;
IF (p_total_attempts IS NOT NULL) THEN
IF (p_total_attempts_flag = 1) THEN
v_sql := v_sql || 'AND total_attempts >= :p_total_attempts '||CHR(10);
ELSE
v_sql := v_sql || 'AND total_attempts = :p_total_attempts '||CHR(10);
END IF;
END IF;
/* If p_status_type is null, then status_type should be the latest status from the view MHS_TRANSACTION_STATUS_V.
Hence, an euqui-join condition is added to the WHERE clause.
Else, status_type should be the input p_status_type. Call the function REPLACE_FUNC to separate
out the comma-separated multiple Status Types */
IF (p_status_type IS NOT NULL) THEN
v_sql := v_sql || 'AND status_type IN ' || replace_func(p_status_type);
v_sql := v_sql || CHR(10);
ELSE
v_sql := v_sql || CHR(10)||'AND msp.transaction_id = mtsv.transaction_id '||CHR(10)||
'AND status_type = current_status '||CHR(10);
END IF;
v_sql := v_sql || 'AND error_code = NVL(:p_error_code, error_code) '||CHR(10)||
'ORDER BY mt.' || p_order_by_col || ' ' || p_order_type;
OPEN o_cur_find_transactions FOR v_sql USING p_service_id, p_originating_party_id, p_destination_party_id,
p_originating_system_id, p_destination_system_id, p_manual_retry_attempts,
p_total_attempts, p_error_code;
/* Count the number of records fetched to decide whether to set the O_COUNT_FLAG or not*/
LOOP
FETCH o_cur_find_transactions INTO v_transaction_id, v_interaction_id, v_status_type, v_message_id_type,
v_message_id, v_is_ref, v_message_timestamp, v_originating_party_id, v_destination_party_id,
v_originating_system_id, v_destination_system_id, v_persistence_timestamp;
EXIT WHEN o_cur_find_transactions%notfound;
v_result_count := v_result_count + 1;
END LOOP;
IF (v_result_count > p_batchsize) THEN
o_count_flag := 1;
END IF;
/* Filter out p_batchzise number of records */
OPEN o_cur_find_transactions FOR ' SELECT * FROM ( ' || v_sql || ' ) WHERE ROWNUM <= :p_batchsize'
USING p_service_id, p_originating_party_id, p_destination_party_id,
p_originating_system_id, p_destination_system_id, p_manual_retry_attempts,
p_total_attempts, p_error_code, p_batchsize;
END sps_find_transactions;
how do i make this procedure use bind variables in all cases...i mean there are 2input variables that are timestamp...for every different dates passed to thsi proc, a hard parse occurs...how do i avoid this
June 02, 2008 - 11:23 am UTC
thanks
Vaibhav, June 04, 2008 - 2:21 am UTC
Hi,
I went through the link and realised that all i had to do is take date values as string and use to_timestamp so that i can use bind variables....your comments and answers are always wonderfull...
v_sql := 'SELECT me.error_code, '||
'COUNT (CASE when me.error_timestamp BETWEEN (:p_error_date - 3/24) AND (:p_error_date - 3/24) + NUMTODSINTERVAL(15, 'MINUTE') '||
'THEN 1 ELSE NULL END) in1, '||
'COUNT (CASE when me.error_timestamp BETWEEN (:p_error_date - 3/24) + NUMTODSINTERVAL(15, 'MINUTE') AND (:p_error_date - 3/24) + NUMTODSINTERVAL(30, 'MINUTE') '||
'THEN 1 ELSE NULL END) in2, '||
'COUNT (CASE when me.error_timestamp BETWEEN (:p_error_date - 3/24) + NUMTODSINTERVAL(30, 'MINUTE') AND (:p_error_date - 3/24) + NUMTODSINTERVAL(45, 'MINUTE') '||
'THEN 1 ELSE NULL END) in3, '||
'COUNT (CASE when me.error_timestamp BETWEEN (:p_error_date - 3/24) + NUMTODSINTERVAL(45, 'MINUTE') AND (:p_error_date - 3/24) + NUMTODSINTERVAL(60, 'MINUTE') '||
'THEN 1 ELSE NULL END) in4, '||
'COUNT (CASE when me.error_timestamp BETWEEN (:p_error_date - 3/24) + NUMTODSINTERVAL(60, 'MINUTE') AND (:p_error_date - 3/24) + NUMTODSINTERVAL(75, 'MINUTE') '||
'THEN 1 ELSE NULL END) in5, '||
'SUM (COUNT(1)) OVER (PARTITION BY error_code ORDER BY error_code) total_count '||
'FROM mhs_errors me '||
'AND me.error_timestamp BETWEEN :p_error_date - 3/24 AND :p_error_date '||
GROUP BY me.error_code ';
Open a_cursor for v_sql using p_error_date, p_error_date, p_error_date, p_error_date, p_error_date, p_error_date,
p_error_date, p_error_date, p_error_date, p_error_date, p_error_date, p_error_date;
1) is the above way the right way to use bind variable in this context...i mean will i have to repeat the same variable name for their corresponding bind variables...
2) also i have written a dynamic query that take smany inputs...but the problem is one input takes multiple values in the format 'a,b,c,d'...i use a function at my end to split it as ('a','b','c','d')...
IF (p_interaction_id IS NOT NULL) THEN
v_sql := v_sql || 'AND interaction_id IN '|| replace_func(p_interaction_id);
END IF;
IF (p_manual_attempts_flag = 1) THEN
v_sql := v_sql || 'AND manual_retry_attempts >= :p_manual_retry_attempts '||CHR(10);
END IF;
how do i make use of bind variable here, i have 14 paarmeters for thsi proc and all are using bind variables expect this one...because everytime a different value is passed for this parameter, hard parse occurs...how do i avoid this parsing everytime...i face a similiar prob in another proc as well where the input parameter is comma separated...
in fact, i will always be doing a hard parse for the procedures where the input parameter would be comma separated...
is there a way out...
Tom...please help...
Thanks a million again for patiently answering all my questions...
Btw, i have started reading Expert oracle by design and i m really enjoying it...chap 2 that explains how to understand AUTOTRACE report is just awesome...simply brilliant
Vaibhav, June 05, 2008 - 12:07 am UTC
hi...
Thanks for that quick reply...
i was using static sql in the error count query but then the requirement changed and even that proc would now take a comma separated input parameter...as a result of which i thought of using dynamic query to make use of bind variables...i will go through the link that you gave and i am sure my solution will be right there...
once i know how to make use of bind variables for such comma separated situations, i am sure i can use that in many procs...
thanks again for all the information and help...
and yea, i was just thinking if i could use a TYPE and store the comma separated parameter in the plsql table using using instr and substr and then just say something like
WHERE interaction_id IN (SELECT * FROM TABLE(id_tab AS id_type))interaction);
i think the above sql would always be static...and i would just avoid using a bind variable for that input parameter interaction_id
what do u suggest...would this be a performance issue...
and yea...a bad news...an online store charged me for Expert one on one oracle but they refunded it in my account and dropped me a mail saying "OUT OF STCOK"...atleast i have Expert Oracle by Design...i wont give up though...will definitely find one...hehe
Thanks...
Marius NITU, June 09, 2008 - 10:55 am UTC
Hi Tom,
We are dynamically generating in PL/SQL and executing a statement like this one (Oracle 9.2.0.5.0)
Insert into <GlobalTemporaryTable> Select * from (
Select M.* From(
Select ( :bind1, :bind2, ..., :bind50 From Dual), ComplexView M
Where M.col1 = :bind1
And M.col2 like :bind40
And M.col15 = 0
And M.col25 = :bind53
)
)
ComplexView is a view based on two others view and these ones are in turn based on other views. The trick behind selecting bind variable from dual allows the construction of dynamic queries in PL/SQL using execute immediate, in the case where the number and order of bind variables is unknown at run time.
We are experimenting somme poor execution plans with this type of query and I suppose that the mecanism of bind variable peeking could be the cause of wrong cardinalities shown by explain plan in our case.
What do you think about this Select Bind_variable from dual trick ?
PS. We have notice also that our problem seems to vanish if we are truncating the GTT but I think that this is more due to hazard.
Bind in PHP
Francesco, June 11, 2008 - 8:56 am UTC
Hi Tom,
I am executing SQL statements from a PHP script and I am binding all variable data. Suppose I issue a statement that contains e.g.:
substr(myfield, 1, 6)
should I also transform the "1" and "6" constants into bind variables, such as:
substr(myfield, :start, :len)
or would this not be needed to achieve the parse once execute many goal?
Thanks in advance
Francesco
June 11, 2008 - 9:45 am UTC
you only bind that which VARIES.
I presume it will always be 1 and 6 - therefore, you would not consider binding 1 and 6 - in fact, it would be "bad" to bind 1 & 6 if they are constant, we'd no longer know at parse time how wide that column would be if you selected it.
ops$tkyte%ORA10GR2> select substr( username, 1, 6 ), substr( username, :x, :y ) from all_users where rownum =1;
SUBSTR SUBSTR(USERNAME,:X,:Y)
------ ------------------------------
SYS SYS
ops$tkyte%ORA10GR2>
you bind values that can change from execution to execution of a sql statement, anything that stays constant over every execution should just be "in there" as a literal
Bind variable & Peeking
Nags, June 16, 2008 - 5:10 pm UTC
We have been having performance issue in production for last whole week, and this started right after we upgraded from 10.2.0.2 to 10.2.0.4. The performance issue seems to be around bind variable and peeking and for some strange reason right after the upgrade. The database has been working fine for last 6-8 months and this query is the most common query in our application. I need some help:
If I modify the query from "and P.HOSPITALID = :3" to "and P.HOSPITALID LIKE :3" will it always use bind peeking and do re-parsing? Will it help?
select
TRUNC(SYSDATE - A.DISCHARGE) AS AGE,
(TRUNC(A.DISCHARGE) - TRUNC(A.ADMIT)) AS LOS,
... more columns
count(*) over() as totalrecords
from LCLEpisode A, LCLPatientMast B, PolicyResources F, CodingQueueResource G, LCLMember D,
EpisodeTypes C, ServiceNames E, PolicyEntity P ,FlexlistMetadata FMD
where E.HOSPITALID(+) = A.HOSPITALID
and E.SERVICECODE(+) = A.SERVICECODE
and C.HOSPITALID(+) = A.HOSPITALID
and C.ET_CODE = A.PTYPE
and A.HOSPITALID = G.HOSPITALID
and A.CHARTNO = G.CHARTNO
and A.EPISODENO = G.EPISODENO
and D.HOSPITALID(+) = G.HOSPITALID
and D.USERID(+) = G.RESPONSIBLECODER
and B.HOSPITALID = G.HOSPITALID
and B.CHARTNO = G.CHARTNO
and G.CODINGQUEUERESOURCEID = decode( replace(translate(trim(F.RESOURCEID),'0123456789','00000000000'),'0',null), null, to_number(trim(F.RESOURCEID)) )
and G.CODINGSTATUS = :1
and G.RESPONSIBLECODER = :2
and f.HOSPITALID = P.HOSPITALID
and F.POLICYID = P.POLICYID
and G.HOSPITALID=FMD.HOSPITALID
and FMD.NAME like 'daysBeforeExpiration'
and G.FLEXLISTID=FMD.FLEXLSTID
and P.HOSPITALID = :3
and P.ENTITYID = :4
order by DISCHARGE asc
June 17, 2008 - 9:40 am UTC
...
If I modify the query from "and P.HOSPITALID = :3" to "and P.HOSPITALID LIKE
:3" will it always use bind peeking and do re-parsing?
....
no, it will generate a single plan like any other predicate would, it would peek once, build plan and then reuse it.
....The
performance issue seems to be around bind variable and peeking....
what makes you say that - give me some background, why do you suspect bind peeking is the "issue" here.
Bind variable & Peeking
Nags, June 17, 2008 - 1:46 pm UTC
This is how it has been happening for the last 10 days.
Every day in the morning, we find that we have performance issue (CPU 100%) and this query runs terrible (>1hr to complete). When we look at all the queries issued for that SQLID we find that, except for a few, rest all execute fine (<5 sec).
So, we collect statistics for one of the tables (a very small table) in the query, so that a new plan gets generated, the next time this query is issued by the application. We do see that a new PLAN HAS VALUE is generated and after that the query works just fine. It stays like this for some time till a different variation of the query gets issued by the application, and the whole problem repeats itself.
We also think that somehow histograms got collected right after we upgraded (I have no proof except the symptoms we had). And, to fix the issue we had run statistic collection which added histograms to other tables, which further added to our problems.
Yesterday night we specifically collected statistics again for all the tables referred in this query and made sure histograms do not get generated. We did not have a single instance today, where we had performance issue.
I researched and I found that several people recommended not to use bind variables and histograms.
We do have data that is very highly skewed.
June 17, 2008 - 3:00 pm UTC
so, you are fixed then, that is what you seem to be saying?
Bind variable & Peeking
Nags, June 17, 2008 - 3:27 pm UTC
Yes, it seems like the statistics collection yesterday, has indeed fixed the issue.
Would you have recommended a different approach?
June 18, 2008 - 12:04 pm UTC
bind peeking becomes an issue in a transactional environment (9i-10gr2) when you
a) have skewed data
b) have statistics that tell the optimizer 'skewed data'
c) when a query is parsed with one set of binds - the plan is only good for those values - and HORRIBLE for others (most others typically)
You were a case of that - when first parsed, that query was parsed with some values that caused an efficient plan for THOSE VALUES but a horrible plan in general. Had the query been parsed first with a different set of values, you get a plan that is good enough for all. The bind peeking makes the plan flip flop unpredictably.
So, by removing the (b) bit above - you end up getting a single, consistent plan - one that works for you. Yes, this is one of the perfectly valid approaches (in 10gr2 and before, having histograms in a transactional system is probably not something you want in general, you would gather them very selectively - intentionally, not as a rule of thumb)
In 11gr1 with adaptive cursor sharing, this all changes - there the optimizer will recognize "whoops, that isn't a good plan, lets get a better one next time" and self correct.
Bind variable & Peeking
Nags, June 18, 2008 - 6:56 pm UTC
What we found out today, was that the Histograms were being collected by the automatic statistics collection mechanism of oracle 10g. We are looking at either
- preventing the automatic job from collecting histograms
- or collecting statistics on most affected tables manually and then locking the statistics
Both actions have some caveats. Which one would you recommend?
I am leaning towards controlling the automatic job.
Thank you for your suggestions.
June 19, 2008 - 9:57 am UTC
I would lead toward not using the automatic job, creating my own 'automatic' job with my 'own' set of statistics to gather.
Bind Variable in DDL command
Robert, July 29, 2008 - 5:22 pm UTC
Hi Tom,
I was trying to use bind variable in the name of the partition to create a table using create as select, but I got ORA-00933.
The name of the partition is always 'PARTYYYYMM':
declare
vpart CHAR(7);
curs INTEGER;
begin
select 'PART'||to_char(add_months(sysdate, -1), 'yyyymm') into vpart from dual;
curs := dbms_sql.open_cursor;
dbms_sql.parse(curs, 'create table SNIPER_rsm as
select a.id_month,a.id_cust, from
tab_part partition (:vpart) a,
tab2 c
where
a.id_month = to_date(''01/06/2008'',''dd/mm/yyyy'')
and a.id_type_prod in (1,4,11,531,532)',dbms_sql.v7);
dbms_sql.close_cursor(curs);
end;
/
August 01, 2008 - 10:45 am UTC
you cannot bind identifiers....
think about it, the plan HAS to be different if you change an identifier, you cannot share the sql if the identifier it different....
you know, if you have monthly partitions, all you need is:
begin
execute immediate '
create table sniper_rsm
as
select a.id_month, a.id_cust, ...
from tab_part a, tab2 c
where a.partition_key >= trunc(add_months(sysdate,-1),'mm')
and a.partition_key < trunc(sysdate)
and a.id_month = ...
';
end;
you don't want or need the partition name, just use the right range and we'll pick the right partition.
also, do not
select f(sysdate) into var from dual;
just
var := f(sysdate);
in the future, selecting from dual is not correct there, just wastes cpu cycles.
Bind Variables
Bob, August 13, 2008 - 7:01 pm UTC
Hi Tom,
I'm an Oracle DBA (on Oracle 9.2) and I'm trying to help a Java developer out. I have noticed that we have a lot of hard parsing in our database and I tracked it down to a particular report. Unfortunately, the output from this report is generated via dynamic SQL from JAVA (as opposed to having the business logic and coding from the database - historical issues). The problem we have is that we could have multiple values selected from a dropdown from the front end. These in turn are put into a WHERE clause
e.g. select col1, col2 from x where x.y IN(1,2,3);
where 1,2,3 are coming from the front end. How do we use bind variables in Java so we can accommodate these values in the IN clause?
One other thing, is it possible to do something clever in the database so that we can limit a maximum number of users to concurrently run reports. At the moment, users are hogging the system by running these CPU intensive reports. I know I can create a PROFILE and then assign a user to that profile. But I don't want their connection to completely bomb out - but rather that they receive a user friendly message to try later...
Cheers,
Bob
Bind variables
Bob, August 21, 2008 - 8:50 am UTC
That's bloody fanatastic! Thank you :-)
Bind variables
Bob, August 21, 2008 - 8:50 am UTC
That's bloody fantastic! Thank you :-)
bitmap index bind variable
YAN, September 02, 2008 - 2:55 pm UTC
Hey Tom:
I know for b-tree index, bind peeking happens all the time, no matter it is user bind or system bind. what about bitmap index? I have an example in our system.
we have bitmap index on each of the columns of table axe. when I run this query from command line, it is very fast, very low gets and pick up the indexes.
SELECT /*+ first_rows (100) */ axe.ad_id
FROM ax_results_enhanced axe
WHERE axe.year between '1880'
and '2009'
and axe.price between '2000'
and '2500'
and axe.latitude between '29.775122222222'
and '30.930677777778'
and axe.longitude between '81.066122222222'
and '82.221677777778'
and axe.show_image = 'Y'
and rownum <= 500
order by to_number (decode (axe.search_order, 20, 1, 50, 1, 75, 1, 90, 2, 99, 3, axe.search_order)) asc, to_number (decode (axe.price, 0, null, axe.price)) desc nulls last
But when it runs from the sessions, it picked up the full table scan with huge gets. In our system, the cursor_sharing is set to "FORCE". So I wonder if it is because the query like following run at the first time and caused the system to pick up full table scan and though that is the best plan for all. But actually it is not.
for the following query, all the predicates in the where clause are not selective, almost including all the possible range. and even when I run from command line, it picks up the full table scan.
SELECT /*+ first_rows (100) */ axe.ad_id
FROM ax_results_enhanced axe
WHERE axe.year between '1880'
and '2009'
and axe.price between 0
and 999999
and axe.latitude between '0'
and '100'
and axe.longitude between '0'
and '180'
and axe.show_image = 'Y'
and rownum <= 500
order by to_number (decode (axe.search_order, 20, 1, 50, 1, 75, 1, 90, 2, 99, 3, axe.search_order))
asc, to_number (decode (axe.price, 0, null, axe.price)) desc nulls last
if it is bind peeking, what should we do? I heard the 11g has conquered the bind peeking issue, it will automatically realize "this is not a good plan, let us pick another plan".
Is this true? And I know Oracle has offered 11g for a long time. My question is :
Is it mature or stable enough to be production version?
Thank you very much!
September 02, 2008 - 4:18 pm UTC
... In our system, the cursor_sharing is set to "FORCE". So I wonder if it
is because the query like following run at the first time and caused the system
to pick up full table scan and though that is the best plan for all. ...
absolutely - before 11g - absolutely. In 11g there is adaptive cursor sharing which will change this behavior - but in 9i and 10g, this can definitely be happening.
...
and rownum <= 500
order by to_number (decode (axe.search_order, 20, 1, 50, 1, 75, 1, 90, 2, 99,
3, axe.search_order))
asc, to_number (decode (axe.price, 0, null, axe.price)) desc nulls last
.....
are you sure you meant to do that??? you get 500 random rows, and then sort them?? Are you sure you meant to do that? You know, given the same set of rows - that query could return two entirely different results for two different users - using the same inputs...
...
Is it mature or stable enough to be production version?
.....
Actually, if you have to use cursor_sharing=force, you should be asking that of your developed code. To have to use that setting means you have some really insecure code (sql injectable) - a massive bug, not only is not binding properly a performance, memory and scalability issue - but it opens your application and thus database to easy attack.
...
WHERE axe.year between '1880'
and '2009'
.....
please don't tell me you put numbers into strings :( why would you do that?
...
and axe.price between 0
and 999999
and axe.latitude between '0'
and '100'
.....
you have some numbers as numbers and other obvious numbers in .... strings.
and you know, if you did store those as strings and not fixed width, always padded strings, you lose....
ops$tkyte%ORA10GR2> create table t ( x varchar2(20) );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( '0' );
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( '99' );
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( '100' );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t where x between '0' and '100';
X
--------------------
0
100
you basically get strings that start with '0' or '1' that way - never 2, 3, 4, ... and so on. did you really mean to do this???!?!?
or is the data in the table really a NUMBER and you are comparing a STRING to a NUMBER, in which case there are implicit conversions happening - probably obviating the use of an index altogether.
show us your create table and your create indexes, then we can discuss further, but there are lots of RED FLAGS here.
bitmap index bind variable
YAN, September 02, 2008 - 5:52 pm UTC
Hey Tom:
sorry, the query should look like this:
SELECT /*+ first_rows (100) */ axe.ad_id
FROM ax_results_enhanced axe
WHERE axe.year between 1880
and 2009
and axe.price between 0
and 999999
and axe.latitude between 0
and 100
and axe.longitude between 0
and 180
and axe.show_image = 'Y'
and rownum <= 500
order by to_number (decode (axe.search_order, 20, 1, 50, 1, 75, 1, 90, 2, 99, 3, axe.search_order))
asc, to_number (decode (axe.price, 0, null, axe.price)) desc nulls last;
we have a tool to pull all the slow queries from system. When it pulled the queries, it adds single quote on all value, no matter it is char or number. I modified the year but forgot the others. The query above is what it really looks like in the database.
And yes! I have to say we have a lot of bad queries. And it is been a long time, it is hard for us to track all the queries with user bind variables, where it should bind, and when it binds. We tried use "exact" for cursor_sharing before, but it ended up with very high load on the system. Finally we changed back. I don't know if there is an possible way to fix this issue. And My thinking was if 11g is smarter than 9i and 10g, we may consider upgrading database directly to 11g. Then, we can keep using "FORCE" without bind peeking. Just concern if 11g is stable enough.
And I will ask the web team about the order by issue. It doesn't make much sense for me too.
September 03, 2008 - 10:29 am UTC
I'd still need to see the CREATE statements here. table and indexes.
this is sort of whacky
to_number (decode (axe.price, 0, null, axe.price)) desc nulls last
if price is a number, that should be:
decode( price, 0, to_number(null), price ) desc nulls last
and since, well, prices are probably never negative (one would think?)
order by price desc nulls last
would do it - zero would be at the bottom of a descending list (followed by anything that was null)
bitmap index bind variable
YAN, September 05, 2008 - 3:40 pm UTC
Hey Tom:
here is the DDL:
CREATE TABLE AX_RESULTS_ENHANCED (
"ARE_ID" NUMBER,
"REGION_CODE" VARCHAR2(2),
"STATE_ID" NUMBER,
"CITY_ID" NUMBER,
"YEAR" NUMBER,
"PRICE" NUMBER,
"SEARCH_ORDER" NUMBER,
"CONSUMER_ID" NUMBER,
"LATITUDE" NUMBER,
"LONGITUDE" NUMBER,
"ZIP_CODE" VARCHAR2(20),
"CERTIFICATION" VARCHAR2(20),
"NEW_USED" VARCHAR2(2),
"LANGUAGE_FLAG" VARCHAR2(1),
"NUM_PAGES" VARCHAR2(50),
"SHOW_IMAGE" VARCHAR2(1),
"BOOK_STYLE" VARCHAR2(50),
"PACKAGE_STYLE" VARCHAR2(50),
"GIFT_SIGN" VARCHAR2(50),
"CONTACT_1" VARCHAR2(50),
"CONTACT_2" VARCHAR2(50),
"EXTERIOR_COLOR" VARCHAR2(50),
"LAST_UPDATE" DATE)
TABLESPACE "AX_DATA" ;
and bitmap indexes on single column:
price
year
latitude
longitude
show_image
so this is a query from drop down, if end users select any of the price range, year, or zip code, the value will win over the default value. If nothing is being selected, default value will be used in the query.
and another question, I changed the query according the last talk:
order by decode (axe.search_order, 20, 1, 50, 1, 75, 1, 90, 2, 99, 3, axe.search_order) asc and axe.price desc nulls last
but I couldn't see any change on the consistent gets and the time. and the plan is the same too. how come? and is that because all the results are cached in the memory? There should be some difference right?
September 05, 2008 - 5:13 pm UTC
yes, likely bind peeking, think about it, if someone does the query with
latitude between 0 and 180 -- 50% of the data...
and so on - it would say initially "wow, ton of stuff", indexes would be a bad idea. especially with all of the bitmap anding it would have to do with really large bitmap results
what do the estimated rows in v$sql_plan look like.
binding
A reader, September 05, 2008 - 7:42 pm UTC
bitmap index bind variable
reader, September 06, 2008 - 4:20 pm UTC
Hey Tom:
sorry, I did not follow you. what do you mean by "estimated rows in v$sql_plan"?
September 08, 2008 - 3:49 pm UTC
v$sql_plan has the rows the optimizer estimated would be returned by a given step in a execution plan, like the PLAN_TABLE does.
bitmap index
reader, September 08, 2008 - 6:25 pm UTC
gotcha, I will check it out later. Now I have some other questions.
for regular b* tree index, if through the equality predicate, the number of rows is limited to very small number, then index on this column is easily to be picked up. is this the same for bitmap index?
I have a query, with the year in it:
SELECT axe.ad_id
FROM ax_results_enhanced axe
WHERE axe.year between 1972
and 1972
and rownum <= 500;
>>>> full scan
31009 consistent gets
SELECT AXe.ad_id
FROM ax_results_enhanced axe
WHERE axe.year between 1992
and 1992
and rownum <= 500;
>>>> full scan
SELECT axE.ad_id
FROM ax_results_enhanced axe
WHERE axe.year between 2008
and 2008
and rownum <= 500;
>>>> full scan
SELECT /*+ index(axe AXRESENH2_YEAR_BITIDX) */ Axe.ad_id
FROM ax_results_enhanced axe
WHERE axe.year between 1972
and 1972
and rownum <= 500;
>>>> pick up year bitmap index, and gets is 49
>>>> I changed the query a little bit every time, like from low case to upper case, so the query will be treated as different query and will not be binded.
select count(*) from ax_results_enhanced axe where year between 1972 and 1972;
14
select count(*) from ax_results_enhanced axe where year between 1992 and 1992;
3096
select count(*) from ax_results_enhanced axe where year between 2008 and 2008;
230834
>>>> sounds like no matter how many rows return, it always pick up full scan. even the year 1972 only return 14 rows.
Why? I am confused.
bitmap index
reader, September 11, 2008 - 3:22 pm UTC
Hey Tom:
Did you see my above questions?
Thank you!
September 16, 2008 - 12:53 pm UTC
no, i didn't. you are "a reader", I have no clue what two you refer to.
I'll assume you meant the one above since I addressed one two up.
One would need more information. Like a test case. If I were to create a table like the one you describe and put in just those three years, it does in fact use an index.
I sort of "presume" AD_ID is *really* ARE_ID (eg: your examples do not 'add up')
ops$tkyte%ORA10GR2> /*
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop TABLE AX_RESULTS_ENHANCED ;
ops$tkyte%ORA10GR2> CREATE TABLE AX_RESULTS_ENHANCED (
ops$tkyte%ORA10GR2> "ARE_ID" NUMBER,
ops$tkyte%ORA10GR2> "REGION_CODE" VARCHAR2(2),
ops$tkyte%ORA10GR2> "STATE_ID" NUMBER,
ops$tkyte%ORA10GR2> "CITY_ID" NUMBER,
ops$tkyte%ORA10GR2> "YEAR" NUMBER,
ops$tkyte%ORA10GR2> "PRICE" NUMBER,
ops$tkyte%ORA10GR2> "SEARCH_ORDER" NUMBER,
ops$tkyte%ORA10GR2> "CONSUMER_ID" NUMBER,
ops$tkyte%ORA10GR2> "LATITUDE" NUMBER,
ops$tkyte%ORA10GR2> "LONGITUDE" NUMBER,
ops$tkyte%ORA10GR2> "ZIP_CODE" VARCHAR2(20),
ops$tkyte%ORA10GR2> "CERTIFICATION" VARCHAR2(20),
ops$tkyte%ORA10GR2> "NEW_USED" VARCHAR2(2),
ops$tkyte%ORA10GR2> "LANGUAGE_FLAG" VARCHAR2(1),
ops$tkyte%ORA10GR2> "NUM_PAGES" VARCHAR2(50),
ops$tkyte%ORA10GR2> "SHOW_IMAGE" VARCHAR2(1),
ops$tkyte%ORA10GR2> "BOOK_STYLE" VARCHAR2(50),
ops$tkyte%ORA10GR2> "PACKAGE_STYLE" VARCHAR2(50),
ops$tkyte%ORA10GR2> "GIFT_SIGN" VARCHAR2(50),
ops$tkyte%ORA10GR2> "CONTACT_1" VARCHAR2(50),
ops$tkyte%ORA10GR2> "CONTACT_2" VARCHAR2(50),
ops$tkyte%ORA10GR2> "EXTERIOR_COLOR" VARCHAR2(50),
ops$tkyte%ORA10GR2> "LAST_UPDATE" DATE)
ops$tkyte%ORA10GR2> /
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec gen_data( 'AX_RESULTS_ENHANCED', 14+ 3096 + 230834 );
ops$tkyte%ORA10GR2> update AX_RESULTS_ENHANCED set year = case when rownum <= 14 then 1972 when rownum <= 14+3096 then 1992 else 2008 end;
ops$tkyte%ORA10GR2> create bitmap index bm_idx on AX_RESULTS_ENHANCED(year);
ops$tkyte%ORA10GR2> */
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'AX_RESULTS_ENHANCED', method_opt => 'for all indexed columns' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> SELECT axe.are_id
2 FROM ax_results_enhanced axe
3 WHERE axe.year between 1972
4 and 1972
5 and rownum <= 500;
Execution Plan
----------------------------------------------------------
Plan hash value: 3845896283
------------------------------------------------------------------------
| Id | Operation | Name | Rows | By
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
|* 1 | COUNT STOPKEY | | |
| 2 | TABLE ACCESS BY INDEX ROWID | AX_RESULTS_ENHANCED | 1 |
| 3 | BITMAP CONVERSION TO ROWIDS| | |
|* 4 | BITMAP INDEX SINGLE VALUE | BM_IDX | |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=500)
4 - access("AXE"."YEAR"=1972)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT AXe.are_id
2 FROM ax_results_enhanced axe
3 WHERE axe.year between 1992
4 and 1992
5 and rownum <= 500;
Execution Plan
----------------------------------------------------------
Plan hash value: 3845896283
------------------------------------------------------------------------
| Id | Operation | Name | Rows | By
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 13
|* 1 | COUNT STOPKEY | | |
| 2 | TABLE ACCESS BY INDEX ROWID | AX_RESULTS_ENHANCED | 501 | 13
| 3 | BITMAP CONVERSION TO ROWIDS| | |
|* 4 | BITMAP INDEX SINGLE VALUE | BM_IDX | |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=500)
4 - access("AXE"."YEAR"=1992)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT axE.are_id
2 FROM ax_results_enhanced axe
3 WHERE axe.year between 2008
4 and 2008
5 and rownum <= 500;
Execution Plan
----------------------------------------------------------
Plan hash value: 1922096437
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 13000 | 16
|* 1 | COUNT STOPKEY | | | |
|* 2 | TABLE ACCESS FULL| AX_RESULTS_ENHANCED | 501 | 13026 | 16
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=500)
2 - filter("AXE"."YEAR"=2008)
ops$tkyte%ORA10GR2> set autotrace off
bind variables as input to a procedure
VLS, October 29, 2008 - 10:05 am UTC
Hi Tom,
I do understand the importance of bind variables and follow this practice in recommending to implement bind variables. While investigating a performance issue and executing your procedure to get the queries that are using literals, I could see many database procedures that are executed through out the day and using literals. For example,
SQL_TEXT
----------------------------------------------------------------------
Executions : 4223
BEGIN E_VALUES('12-MAY-2000','MP'); END;
This procedure has been executed 4223 times with differnt literals. How can it be modified to make use of Bind Variables ?
Would appreciate if you can help me out in implementing bind in this case.
Regards
VLS
October 29, 2008 - 1:37 pm UTC
the same way
select * from emp where empno = 1;
select * from emp where empno = 2;
..... and so on
would be - the APPLICATION WOULD USE BINDS.
and since I don't have a clue in the world what language you are using - I cannot point you to an existing code snippet, but basically - look in your code where you do bind, there is the code snippet you need.
peeking issue
A reader, August 15, 2010 - 1:57 pm UTC
When execution plan for a sql statement in a pl/sql package/procedure changes "out of the blue" because of bind variable peeking, what is the best way to force oracle to hard parse the statement hoping that optimizer would pick a more desirable plan? we don't want to flush shared pool or restart database instance for this one sql statement. This particular sql statement refers more than 10 views and tables. we are on RDBMS 10.2.0.4.0 Thanks.
August 19, 2010 - 12:57 am UTC
In 10g, your choices are limited
either use the one plan that is chosen
or
do not bind that particular value - if the bind variable contains a sufficiently small enough set of distinct values, don't bind THAT one variable. then you'll have a plan per value.
Forced re-parse?
Tony Killen, October 15, 2010 - 12:44 pm UTC
Hi Tom,
I have a similar production issue on 10.2.0.3. I have a SQL statement which occasionally is invalidated (not sure how or why) and re-parsed with a really "bad" value for the bind, resulting in terrible performance for that query. I too want to intentionally invalidate the query and get it re-parsed with a "good" initial bind value.
This is a report and only two values for the bind are used. It executes about twice an hour so there is plenty of time to invalidate and re-parse manually. A long-term fix (after testing and release cycles...) is perhaps to use outlines or hints in the report to choose the favourable plan. Short-term though, I need a method to invalidate and re-parse.
I was thinking 1) add a column to a small table used in the query, 2) mark the column as unused.
What do you think of this approach?
[assumes brace position ready for reply]
October 15, 2010 - 2:12 pm UTC
... This is a report and only two values for the bind are used ...
you should probably not bind that particular value then...
see support Note 457309.1
Forced re-parse?
Tony Killen, October 19, 2010 - 7:03 am UTC
Superb answer as always.
Another good reason to go to 10.2.0.4!
Many, many thanks.
Identifying bind value references
Martin, December 07, 2010 - 5:37 am UTC
Hi Tom,
Does Oracle provide any way of obtaining the names of the bind variable references in a given dynamic SQL string? And, yep, before you say "why don't you know the set already?", this is a bit of "generic" code and the app simply isn't written that way (although, it should be).
In other words, given a string such as
SELECT * FROM t WHERE col1=:ABC and col2=:DEF
I need an array of ABC and DEF etc. etc.
I can identify to an extent using regexes and other string manipulation, but I was wondering if Oracle provided anything to do it?
One issue that's tricky to solve manually is the situation where a literal in the string has text that looks like a bind reference, but isn't, i.e.
SELECT COUNT(*) FROM t WHERE col1=':ABC'
Thanks for any pointers you could give me.
Why so much parsing even when using bind variables
Devinder, December 07, 2010 - 10:57 am UTC
Ok another unexplained behavior by oracle,
I ran an AWR report and saw this
BEGIN INQUIRY.getRoutingBookByCompIDAndName(:1 , :2 , :3 ); END; executions 830,608 parsing 830594
I cant explain why is it doing a parsing when the pl/sql procedure is being called with bind variables..
December 07, 2010 - 11:56 am UTC
using bind variables will not decrease parsing - it can only turn a hard parse problem into a soft parse problem. The number of hard parses will decrease - but the total number of parse calls will remain constant (or even go up as you can actually do more work now! you run faster)
The only way to decrease parse calls is to convince developers that everything they've been taught about programming against the database is wrong. You do NOT want to close cursors "as soon as possible" - you want to cache some of them open so as to not have to open them over and over and over .................. and over again and again...
It is being parses 830,594 times because you told us to parse it that many times. When you call prepareStatement - that is just telling us to parse.
A reader, December 08, 2010 - 12:03 pm UTC
Hi Tom,
"You do NOT want to close cursors "as soon as possible" - you want to cache some of them open so as to not have to open them over and over and over .................. and over again and again... "
will setting session_cache_cursor help in this situation
Thanks
December 08, 2010 - 12:23 pm UTC
.. will setting session_cache_cursor help in this situation
..
that simply turns a soft parse into a softer soft parse. It will not remove a parse.
Bind variables-Soft parse
Prasad, May 12, 2011 - 1:00 pm UTC
Hi Tom,
I understand that using bind variables will help in parsing once and executing many time though the values are different in where clause. In the sequence of parsing(syntax,symantic,sharedpool check) if found in sharedpool it will directly goes to exeuction(soft parse) right? If that is true, how oracle knows what is the row source generation? because row source generations is depens on the where cluase(optimization) value and becuase of soft parsae it is directly going to execution (per concepts doc pg 144 11g2r2). I really appreciate your time and help.
In other words how using bind variables can avoid optimization where it is mandate for rowsource generation, for different values in a where caluse.
Thanks,
Prasad Mynumpati.
A reader, May 13, 2011 - 10:09 am UTC
Dear Tom,
Just one question that I need to clarify in this context.
If you have
select * from t where x in (?, ?, ?)
and then you have
select * from t where x in (?, ?, ?,?,?,?)
Will it use the same explain plan?
I think no because it is not the same query
Thanks in advance
May 13, 2011 - 12:59 pm UTC
those are two different queries, the differ in length.
They will each be hard parsed and each will generate it's own plan.
They may end up using the same plan - the same attack approach for getting the data - but they will each develop that plan independently
bind variables
A reader, June 01, 2011 - 10:10 pm UTC
Forgive me if this has been asked elsewhere. My understanding is that these two queries
SELECT TAX_ID FROM EMP WHERE EMP_ID = 1
and
SELECT TAX_ID FROM EMP WHERE EMP_ID = 2
would each be hard parsed (assuming Oracle 11GR2 here).
If so, then the obvious question is, why is the database engine not smart enough to recognize those as identical queries and re-use the same plan, when clearly the only difference is a constant value?
In complex cases where it's not 'obvious' they're the same, I can maybe see the reason.
June 02, 2011 - 8:57 am UTC
create table emp ( emp_id number, tax_id number );
create index on emp(emp_id);
insert into emp values ( 1, 1 );
insert into emp select 2, rownum from all_objects;
commit;
Now, tell me - should emp_id = 1 and emp_id = 2 necessarily use the same plan? One of them retrieves 1 row from thousands. The other retrieves thousands minus 1 from thousands.
the database, using cursor_sharing=force, can 'auto-bind' if you desire, but the side effects of that are horrible.
In short, the developer (who after all is paid to be knowledgable of the tools they use right?) makes the decision as to whether SQL should be shared (in oltp - almost certainly, in data warehouse - probably not) and programs accordingly.
Sometimes the (smart, intelligent, aware) developer, knowing the data is horribly skewed, such as the case of many status flags (think of a table of processed and un-processed records for example) PURPOSELY does not use a bind variable - to give the optimizer more information, to allow it to come up with the best plan for the given inputs.
PLS-00049: Bad Bind Variable :X
Kev, September 15, 2011 - 2:50 am UTC
Hi Tom,
I have been running the following query successfully on the example database on 11g:
select * from hr.employees where employee_id > :x;
As expected, it works without any problems.
I now put that query inside a stored procedure after creating a holding table for the expentant result set.
The holding table:
create table test as (select * from hr.employees);
The stored procedure:
create or replace procedure hr_test
is
begin
insert into test
select * from hr.employees where employee_id > :x;
end;
/
The query compiles with errors telling me the following:
PLS-00049: bad bind variable 'X';
This is for me somewhat perplexing and would certainly like a little help on what I could have possible done (or not done). I haven't found anything so far that has been useful enough to help!
Once more, many thanks for your help!
Kev
September 15, 2011 - 7:48 am UTC
you need to read the plsql manual and learn the language a bit :)
http://www.oracle.com/pls/db112/portal.all_books#index-PL%2F you do not use bind variables that way in plsql, it would look like this:
create or replace procedure hr_test( p_emp_id in number )
is
begin
insert into test
select * from hr.employees where employee_id > P_EMP_ID;
end;
/
and then you would call it:
begin hr_test( 55 ); end;
/
So very right....!
Kev, September 16, 2011 - 12:44 am UTC
Thank you for that Tom! There really is a big difference between administration and development isn't there!
Regards,
Kev
Bind variables value of a particular session
Rakesh, October 07, 2011 - 7:42 am UTC
Hi Tom,
Is there anyway to find out the value of the bind variables used by the particular session apart from generating trace?
I can find the bind variables values using sql_id in v$sql_bind_capture. But how to find out the values of particular session when multiple sessions are coming with same sql id's
Regards
Rakesh
using bind variables
Rajeshwaran, Jeyabal, February 26, 2019 - 1:11 pm UTC
Team,
could you please help us to understand, why this doesn't work for bind variables? but do work for string concatenations.
demo@ORA12C> drop table t purge;
Table dropped.
demo@ORA12C> create table t
2 partition by range ( created )
3 interval ( numtoyminterval(1,'month') )
4 ( partition p_2000 values less than
5 ( to_date('01-Jan-2001','dd-mon-yyyy') )
6 )
7 as
8 select object_id, owner, object_type, status,
9 to_date('01-Jan-2016','dd-mon-yyyy') + mod(rownum,1100) as created
10 from all_objects ;
Table created.
demo@ORA12C> declare
2 l_value varchar2(100);
3 l_date date;
4 l_sql long;
5 function foo( p_tabnm in varchar2,p_partnm in varchar2)
6 return varchar2
7 as
8 l_data varchar2(100);
9 begin
10 select high_value into l_data
11 from user_tab_partitions
12 where table_name = p_tabnm
13 and partition_name = p_partnm;
14 return l_data;
15 end;
16 begin
17 l_value := foo('T','SYS_P21408');
18 l_sql := ' select '||l_value||' from dual ';
19 execute immediate l_sql into l_date;
20 dbms_output.put_line ( ' l_date = '||l_date);
21 end;
22 /
l_date = 01-FEB-2016
PL/SQL procedure successfully completed.
demo@ORA12C> declare
2 l_value varchar2(100);
3 l_date date;
4 l_sql long;
5 function foo( p_tabnm in varchar2,p_partnm in varchar2)
6 return varchar2
7 as
8 l_data varchar2(100);
9 begin
10 select high_value into l_data
11 from user_tab_partitions
12 where table_name = p_tabnm
13 and partition_name = p_partnm;
14 return l_data;
15 end;
16 begin
17 l_value := foo('T','SYS_P21408');
18 l_sql := ' select :x from dual ';
19 execute immediate l_sql into l_date using l_value;
20 dbms_output.put_line ( ' l_date = '||l_date);
21 end;
22 /
declare
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 19
demo@ORA12C>
February 26, 2019 - 1:45 pm UTC
Because the high_value of date-range partitioned tables includes TO_DATE.
So when you bind it, you're binding the VALUE 'TO_DATE ( ... )'. Not the FUNCTION to_date. You can't bind a function call. Only it's parameters.
Whereas string concatenation means you have the SQL statement
select to_date ( ... ) from dual;
thanks.
Rajeshwaran, Jeyabal, February 26, 2019 - 3:17 pm UTC
You can't bind a function call. Only it's parameters.
thanks. that helps.
PLSQL and variables
Angelo, November 24, 2021 - 8:04 am UTC
We should know which the variables in PLSQL have a life linked to the block.
November 24, 2021 - 2:10 pm UTC
Not sure what you're asking here