tkprof
February 13, 2003 - 10am Central time zone
Reviewer: mo
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
Followup February 13, 2003 - 10am Central time zone:
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
February 13, 2003 - 10am Central time zone
Reviewer: Paul from UK
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
May 21, 2003 - 8am Central time zone
Reviewer: Vikas from INDIA
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
Followup May 21, 2003 - 9am Central time zone:
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
May 21, 2003 - 10am Central time zone
Reviewer: Sagi from India
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.
Followup May 21, 2003 - 11am Central time zone:
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
May 21, 2003 - 10am Central time zone
Reviewer: Raj
Absolutely proven!
May 21, 2003 - 11am Central time zone
Reviewer: Sagi from India
Thanx tom.
As usual you are very great.
Regards,
Sagi.
How come my explain plans are different ?
October 16, 2003 - 11pm Central time zone
Reviewer: Ma$e
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
Followup October 17, 2003 - 9am Central time zone:
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
October 17, 2003 - 12pm Central time zone
Reviewer: Ma$e
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
Followup October 17, 2003 - 12pm Central time zone:
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
October 17, 2003 - 1pm Central time zone
Reviewer: Ma$e
Hi Tom:
Thanks..
Ma$e
Followup October 17, 2003 - 2pm Central time zone:
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
October 17, 2003 - 2pm Central time zone
Reviewer: Meyer from Baltimore, MD
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
Followup October 17, 2003 - 2pm Central time zone:
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
October 17, 2003 - 2pm Central time zone
Reviewer: Meyer from Baltimore, MD
Why was peeking introduced in the first place
October 29, 2003 - 12am Central time zone
Reviewer: Bala from Kuala Lumpur
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?
Followup October 29, 2003 - 6am Central time zone:
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.
November 1, 2003 - 11pm Central time zone
Reviewer: Ma$e
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
Followup November 2, 2003 - 10am Central time zone:
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....
November 3, 2003 - 3am Central time zone
Reviewer: Ma$e
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
Followup November 3, 2003 - 7am Central time zone:
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
December 6, 2003 - 7pm Central time zone
Reviewer: Dillip K. Praharaj from CA USA
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 ?
Followup December 7, 2003 - 8am Central time zone:
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.
and tkprof says....
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
December 7, 2003 - 1pm Central time zone
Reviewer: wonder from China
Thank you , Tom
Bind variable
December 12, 2003 - 8pm Central time zone
Reviewer: Dillip K. Praharaj from CA USA
Thanks Tom.
How about generating the plan from the v$SQL_PLAN ?
Followup December 13, 2003 - 11am Central time zone:
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
January 31, 2004 - 9am Central time zone
Reviewer: A reader
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?
Followup January 31, 2004 - 10am Central time zone:
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
February 18, 2004 - 2am Central time zone
Reviewer: Sagi
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
Followup February 18, 2004 - 8pm Central time zone:
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...
February 19, 2004 - 3am Central time zone
Reviewer: A reader
Thanx.
Ur awesome.
Regards,
Bind in plsql
May 31, 2004 - 4am Central time zone
Reviewer: Raaghid from India
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.
Followup May 31, 2004 - 1pm Central time zone:
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)
May 31, 2004 - 6am Central time zone
Reviewer: Raaghid from India
Other one is running in oracle 9.0.1.1.1 (I mentioned wrongly above)
Your Feedback to Bala
June 3, 2004 - 4pm Central time zone
Reviewer: David
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.
Followup June 3, 2004 - 7pm Central time zone:
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
June 4, 2004 - 7am Central time zone
Reviewer: A P Clarke from London, England
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
Followup June 4, 2004 - 8am Central time zone:
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..
June 4, 2004 - 8am Central time zone
Reviewer: bob from PA
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.
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279
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
June 4, 2004 - 10am Central time zone
Reviewer: A P Clarke from London, England
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
June 15, 2004 - 10pm Central time zone
Reviewer: A reader
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
Followup June 16, 2004 - 12pm Central time zone:
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
June 16, 2004 - 1pm Central time zone
Reviewer: A reader
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
Followup June 16, 2004 - 3pm Central time zone:
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.
June 16, 2004 - 7pm Central time zone
Reviewer: A reader
"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?
Followup June 17, 2004 - 7am Central time zone:
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.
June 17, 2004 - 9am Central time zone
Reviewer: A reader
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
Followup June 17, 2004 - 12pm Central time zone:
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?
July 19, 2004 - 2pm Central time zone
Reviewer: P from GA
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?
Followup July 19, 2004 - 2pm Central time zone:
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
July 20, 2004 - 8pm Central time zone
Reviewer: Sean from NJ, USA
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.
Followup July 20, 2004 - 9pm Central time zone:
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:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1163635055580
has it.
Bind variable in Java or VB code
July 20, 2004 - 9pm Central time zone
Reviewer: Sean from NJ, USA
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.
Followup July 20, 2004 - 10pm Central time zone:
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?
August 22, 2004 - 5am Central time zone
Reviewer: A reader
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
Followup August 22, 2004 - 8am Central time zone:
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
August 23, 2004 - 5am Central time zone
Reviewer: A reader
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,
Followup August 23, 2004 - 8am Central time zone:
yes, you can use a clob.
Session Hangs
August 24, 2004 - 4pm Central time zone
Reviewer: A reader
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
Followup August 24, 2004 - 7pm Central time zone:
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...
August 25, 2004 - 8am Central time zone
Reviewer: A reader
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
Followup August 25, 2004 - 8am Central time zone:
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
November 19, 2004 - 3pm Central time zone
Reviewer: reader from USA
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
Followup November 19, 2004 - 7pm Central time zone:
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
November 22, 2004 - 10am Central time zone
Reviewer: reader from USA
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
Followup November 22, 2004 - 3pm Central time zone:
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
November 22, 2004 - 5pm Central time zone
Reviewer: reader from USA
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
Followup November 22, 2004 - 5pm Central time zone:
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 http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1993620575194 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.
November 23, 2004 - 4am Central time zone
Reviewer: Helena Marková from Bratislava, Slovakia
Peeking bind values..
November 24, 2004 - 5pm Central time zone
Reviewer: RD from NZ
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.
Followup November 24, 2004 - 5pm Central time zone:
9i and above...
did the problem happen immediately after an analyze took place?
YES
November 24, 2004 - 5pm Central time zone
Reviewer: RD from NZ
Hi Tom,
Exactly!!! Problems started on Monday morning.
Regards,
RD.
Followup November 24, 2004 - 5pm Central time zone:
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 ..
November 24, 2004 - 6pm Central time zone
Reviewer: RD from NZ
Hi Tom,
I analyze the schema like this:-
exec sys.dbms_utility.analyze_schema('swbprod','compute');
Not good???
Thanks,
Regards,
RahulD.
Followup November 24, 2004 - 6pm Central time zone:
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..
November 25, 2004 - 8pm Central time zone
Reviewer: RD from NZ
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.
Followup November 25, 2004 - 8pm Central time zone:
please re-read what I asked for -- totally different from what was supplied.
Query..
November 25, 2004 - 8pm Central time zone
Reviewer: RD from NZ
Hi Tom,
This is what I got from autotrace. Is that not what you asked for? Or
may be I misunderstood.
Regards,
RahulD.
Followup November 25, 2004 - 8pm Central time zone:
<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
November 28, 2004 - 4pm Central time zone
Reviewer: Kay from NY
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.
Followup November 28, 2004 - 7pm Central time zone:
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
November 28, 2004 - 5pm Central time zone
Reviewer: RD from NZ
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
March 18, 2005 - 2pm Central time zone
Reviewer: HM from NJ
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?
Followup March 18, 2005 - 2pm Central time zone:
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
March 18, 2005 - 3pm Central time zone
Reviewer: A reader
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.
Followup March 18, 2005 - 8pm Central time zone:
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
April 5, 2005 - 9am Central time zone
Reviewer: Yogesh from Pune, India
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?
Followup April 5, 2005 - 12pm Central time zone:
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
April 5, 2005 - 12pm Central time zone
Reviewer: Yogesh from Pune,India
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
Followup April 5, 2005 - 12pm Central time zone:
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
April 5, 2005 - 12pm Central time zone
Reviewer: Yogesh
I'm using oracle 8.0.4.
April 5, 2005 - 1pm Central time zone
Reviewer: Yogesh
COLUMN_NAME DATA_TYPE
CNO NUMBER
ANO NUMBER
ONO CHAR
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
TDATE DATE
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
TOTAL NUMBER
TCODE CHAR
CSNO VARCHAR2
Column data type is DATE
Followup April 5, 2005 - 6pm Central time zone:
show us the binds from the trace file and are the plans different or the same.
Plans
April 6, 2005 - 9am Central time zone
Reviewer: Yogesh
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?
Followup April 6, 2005 - 1pm Central time zone:
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;
April 6, 2005 - 1pm Central time zone
Reviewer: Yogesh
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....
Followup April 6, 2005 - 2pm Central time zone:
i want to see YOUR queries in there.
select 1
and
select 2
your two queries, I want to *see* everything.
April 6, 2005 - 2pm Central time zone
Reviewer: Yogesh
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)
Followup April 6, 2005 - 2pm Central time zone:
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
April 7, 2005 - 9am Central time zone
Reviewer: Yogesh
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.
Followup April 7, 2005 - 11am Central time zone:
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
April 7, 2005 - 1pm Central time zone
Reviewer: Yogesh
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 '
Followup April 7, 2005 - 1pm Central time zone:
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
April 8, 2005 - 4am Central time zone
Reviewer: Yogesh
"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;
Followup April 8, 2005 - 7am Central time zone:
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
April 8, 2005 - 8am Central time zone
Reviewer: Yogesh
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?
Followup April 8, 2005 - 8am Central time zone:
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
April 8, 2005 - 9am Central time zone
Reviewer: Yogesh
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?
Followup April 8, 2005 - 9am Central time zone:
it is a hash join of the two tables
the plan should be readable as normal in the tkprof.
bind variable peeking question
February 5, 2006 - 1am Central time zone
Reviewer: Menon
Are there situations (discarding the bugs such as the one mentioned at http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:19398056075583#48426602526853
when bind variable peeking does not happen? Or for all bind variables - the very first time, the
peeking happens and the plan is the result of the actual value in the bind variables?
Thanx!
Followup February 6, 2006 - 12am Central time zone:
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...
February 6, 2006 - 12pm Central time zone
Reviewer: Menon
"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?
Followup February 7, 2006 - 1am Central time zone:
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
February 6, 2006 - 2pm Central time zone
Reviewer: Menon
http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_general_cbo_migration_10gr2_0405.pdf
contains some info on when bind variable peeking happens and when it does not take place.
when does bind variable peeking occur?
February 6, 2006 - 7pm Central time zone
Reviewer: Menon
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 http://www.oaktable.net/getChallenge.jsp;jsessionid=F8889A54FBA1FC1F0DB2DBCEC0E3EBDD?id=66, 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?
Followup February 7, 2006 - 1am Central time zone:
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...
February 7, 2006 - 11am Central time zone
Reviewer: Menon
"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...
Followup February 8, 2006 - 1am Central time zone:
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
February 7, 2006 - 11am Central time zone
Reviewer: Menon
"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
February 8, 2006 - 12pm Central time zone
Reviewer: Menon
"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.
Followup February 9, 2006 - 4am Central time zone:
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....
February 27, 2006 - 1am Central time zone
Reviewer: Su Baba from Ca, USA
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.
Followup February 27, 2006 - 7am Central time zone:
absolutely, I only post them so you can do that.
How to check current values?
March 21, 2006 - 6am Central time zone
Reviewer: Michal from Poland
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
March 23, 2006 - 8am Central time zone
Reviewer: Michal from Poland
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...
April 26, 2006 - 5am Central time zone
Reviewer: Dmytro from Kyiv, Ukraine
...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
April 26, 2006 - 11am Central time zone
Reviewer: An Oracle Fan
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
April 27, 2006 - 10am Central time zone
Reviewer: Steve Booth from Pewaukee, WI USA
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.
Followup April 27, 2006 - 3pm Central time zone:
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".
May 1, 2006 - 3pm Central time zone
Reviewer: Richard Z. Tan from Fairfax, Virginia
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
Followup May 2, 2006 - 3am Central time zone:
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
May 2, 2006 - 12pm Central time zone
Reviewer: Steve Booth from Pewaukee, WI USA
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.
Followup May 2, 2006 - 4pm Central time zone:
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
May 3, 2006 - 1pm Central time zone
Reviewer: Steve Booth from Pewaukee, WI USA
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.
Followup May 3, 2006 - 2pm Central time zone:
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
May 4, 2006 - 3pm Central time zone
Reviewer: Richard Tan from Fairfax, Virginia, U.S.A.
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.
Followup May 4, 2006 - 5pm Central time zone:
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
June 27, 2006 - 2pm Central time zone
Reviewer: Mahmood Lebbai from Bothell,WA USA
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.
Followup June 27, 2006 - 2pm Central time zone:
&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
July 18, 2006 - 8am Central time zone
Reviewer: a reader
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
Followup July 18, 2006 - 8am Central time zone:
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
July 19, 2006 - 4am Central time zone
Reviewer: a reader
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?
Followup July 19, 2006 - 9am Central time zone:
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
July 19, 2006 - 9am Central time zone
Reviewer: a reader
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"
Followup July 20, 2006 - 7am Central time zone:
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)
July 19, 2006 - 10am Central time zone
Reviewer: A reader
Do a quick search for Bind Variable Peeking to see the answer to this.
harms of overbinding
July 20, 2006 - 11am Central time zone
Reviewer: a reader
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.
Followup July 22, 2006 - 4pm Central time zone:
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
July 21, 2006 - 9am Central time zone
Reviewer: a reader
Any body like to add to the above list
TKPROF OUTPUT WHEN USING BIND VARIABLES
September 12, 2006 - 5pm Central time zone
Reviewer: Seema from USA
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.
Followup September 13, 2006 - 7am Central time zone:
look at the overall totals.
compare them.
that is the "summary of the work performed by each"
tkprof for comparing bind variables performance benefit
September 13, 2006 - 6pm Central time zone
Reviewer: Seema from USA
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)
********************************************************************************
Followup September 14, 2006 - 9am Central time zone:
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
December 20, 2006 - 10am Central time zone
Reviewer: A reader
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.
Followup December 20, 2006 - 1pm Central time zone:
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
March 7, 2007 - 1pm Central time zone
Reviewer: Loïc from Paris
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
Followup March 7, 2007 - 2pm Central time zone:
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?
March 8, 2007 - 5pm Central time zone
Reviewer: Maverick
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,
Followup March 8, 2007 - 8pm Central time zone:
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
March 8, 2007 - 8pm Central time zone
Reviewer: Maverick
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?
May 1, 2007 - 7pm Central time zone
Reviewer: Brandon from Phoenix, AZ
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
Followup May 1, 2007 - 9pm Central time zone:
there is not currently, and not as far as I know into the future.
Trigger :NEW.Value :OLD.Value
May 2, 2007 - 3am Central time zone
Reviewer: A reader
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 2, 2007 - 7am Central time zone
Reviewer: Alberto Dell'Era from Milan, Italy
It seems that Brandon, Albert Nelson and me are asking for the same thing:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:122537200346913471#1228410003469
80212
Enhancement request to force bind variable re-peeking
May 2, 2007 - 7pm Central time zone
Reviewer: Brandon from Phoenix, AZ
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
May 3, 2007 - 1pm Central time zone
Reviewer: DW from Dallas TX USA
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?
Followup May 3, 2007 - 10pm Central time zone:
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
May 4, 2007 - 1pm Central time zone
Reviewer: Brandon from Phoenix, AZ
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
Followup May 4, 2007 - 3pm Central time zone:
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
May 4, 2007 - 4pm Central time zone
Reviewer: Brandon from Phoenix, AZ
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.
Followup May 8, 2007 - 9am Central time zone:
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?
May 17, 2007 - 3pm Central time zone
Reviewer: Ray DeBruyn from Ottawa, Ontario, Canada
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?
Usage of Bind Variable for a Frequently used Procedure
June 6, 2007 - 12pm Central time zone
Reviewer: RAM from UK
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
Followup June 6, 2007 - 9pm Central time zone:
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
June 7, 2007 - 6am Central time zone
Reviewer: RAM from UK
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
July 18, 2007 - 11am Central time zone
Reviewer: Sandro from Italy
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.
Followup July 18, 2007 - 12pm Central time zone:
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.
January 17, 2008 - 5am Central time zone
Reviewer: Karthick from India
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
Followup January 17, 2008 - 11am Central time zone:
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??
January 17, 2008 - 5am Central time zone
Reviewer: Karthick from India
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.
Followup January 17, 2008 - 11am Central time zone:
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.
January 18, 2008 - 5am Central time zone
Reviewer: Karthick Pattabiraman
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
Followup January 19, 2008 - 10pm Central time zone:
it is even better than that for a stored procedure, but for the anonymous block, sure, that is it - parse once, execute many.
January 20, 2008 - 2am Central time zone
Reviewer: karthick from India
Can you give the pseudocode for stored procedures?
Followup January 20, 2008 - 7am Central time zone:
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
January 29, 2008 - 11am Central time zone
Reviewer: Tom from Leesburg
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?
Followup January 30, 2008 - 9am Central time zone:
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.
January 29, 2008 - 12pm Central time zone
Reviewer: Tom from Leesburg, VA
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?
Followup January 30, 2008 - 9am Central time zone:
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
January 30, 2008 - 7am Central time zone
Reviewer: Tom from Leesburg, VA
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.
Followup January 30, 2008 - 10am Central time zone:
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
January 31, 2008 - 1pm Central time zone
Reviewer: Tom from Leesburg, VA
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
February 7, 2008 - 2pm Central time zone
Reviewer: Gary Wicke from Indianapolis, IN US of A
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
Followup February 7, 2008 - 3pm Central time zone:
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
http://www.oracle.com/technology/oramag/oracle/07-jul/o47asktom.html
February 25, 2008 - 4pm Central time zone
Reviewer: A reader from IL
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
Followup February 25, 2008 - 4pm Central time zone:
... 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://tkyte.blogspot.com/2007/09/sqltracetrue-part-two.html
February 26, 2008 - 2pm Central time zone
Reviewer: A reader from IL
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
March 5, 2008 - 11am Central time zone
Reviewer: A reader
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
Followup March 6, 2008 - 7am Central time zone:
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
March 6, 2008 - 8am Central time zone
Reviewer: A reader
values of bind variables when explain plan has been created
March 27, 2008 - 4am Central time zone
Reviewer: Nenad Noveljic from Switzerland
Is it possible to find out from some dictionary views, what values of bind variables has been used
to create the current explain plan?
Followup March 27, 2008 - 10am Central time zone:
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
April 4, 2008 - 4pm Central time zone
Reviewer: A reader
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!!!
Followup April 7, 2008 - 8am Central time zone:
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.
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...
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.
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...
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
now, chang the binds....
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.
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....
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.
it automagically did the hard parse for us and got an optimal plan for this bind value, peeking
at
v$sql again
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
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:
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
April 7, 2008 - 9am Central time zone
Reviewer: A reader
This is a great explanation!!! i will try it by myself.
THANK YOU VERY MUCH TOM!!!
June 2, 2008 - 5am Central time zone
Reviewer: vaibhav from India
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
thanks
June 4, 2008 - 2am Central time zone
Reviewer: Vaibhav from India
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
June 5, 2008 - 12am Central time zone
Reviewer: Vaibhav from India
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...
June 9, 2008 - 10am Central time zone
Reviewer: Marius NITU from France
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
June 11, 2008 - 8am Central time zone
Reviewer: Francesco from Italy
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
Followup June 11, 2008 - 9am Central time zone:
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
June 16, 2008 - 5pm Central time zone
Reviewer: Nags from Boston
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
Followup June 17, 2008 - 9am Central time zone:
...
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
June 17, 2008 - 1pm Central time zone
Reviewer: Nags from Boston
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.
Followup June 17, 2008 - 3pm Central time zone:
so, you are fixed then, that is what you seem to be saying?
Bind variable & Peeking
June 17, 2008 - 3pm Central time zone
Reviewer: Nags from Boston
Yes, it seems like the statistics collection yesterday, has indeed fixed the issue.
Would you have recommended a different approach?
Followup June 18, 2008 - 12pm Central time zone:
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
June 18, 2008 - 6pm Central time zone
Reviewer: Nags from Boston
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.
Followup June 19, 2008 - 9am Central time zone:
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
July 29, 2008 - 5pm Central time zone
Reviewer: Robert from japan
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;
/
Followup August 1, 2008 - 10am Central time zone:
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
August 13, 2008 - 7pm Central time zone
Reviewer: Bob from London, UK
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
August 21, 2008 - 8am Central time zone
Reviewer: Bob from London, UK
That's bloody fanatastic! Thank you :-)
Bind variables
August 21, 2008 - 8am Central time zone
Reviewer: Bob from London, UK
That's bloody fantastic! Thank you :-)
bitmap index bind variable
September 2, 2008 - 2pm Central time zone
Reviewer: YAN from USA
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!
Followup September 2, 2008 - 4pm Central time zone:
... 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
September 2, 2008 - 5pm Central time zone
Reviewer: YAN from USA
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.
Followup September 3, 2008 - 10am Central time zone:
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
September 5, 2008 - 3pm Central time zone
Reviewer: YAN
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?
Followup September 5, 2008 - 5pm Central time zone:
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
September 5, 2008 - 7pm Central time zone
Reviewer: A reader
bitmap index bind variable
September 6, 2008 - 4pm Central time zone
Reviewer: reader
Hey Tom:
sorry, I did not follow you. what do you mean by "estimated rows in v$sql_plan"?
Followup September 8, 2008 - 3pm Central time zone:
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
September 8, 2008 - 6pm Central time zone
Reviewer: reader
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
September 11, 2008 - 3pm Central time zone
Reviewer: reader
Hey Tom:
Did you see my above questions?
Thank you!
Followup September 16, 2008 - 12pm Central time zone:
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
October 29, 2008 - 10am Central time zone
Reviewer: VLS from Bombay, India
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
Followup October 29, 2008 - 1pm Central time zone:
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.
|