newbie : BIND VARIABLES
cava123
We have to use "bind variables" : What does bind variables means exactly
? relation ship with performance ? and how I can see if it used or not ?
(V$SQLAREA,V$SQL,V$SESSION, ...)
I have also execute an ALTER SESSION SET AUTOTRACE TRACEONLY EXPLAN PLAN
for .... and get 'HASH JOIN' : What does it mean HASH JOIN and HASH
TABLES ?
Thanks
Jim Kennedy
field1:=hvField1)
Without bind variables you are forcing Oracle to serielize on a latch and
you are causing earch and every statement to be reparsed.(high CPU) It will
mean each sql statement will take longer. Once the statement is parced, is
you use bind variables, then it does not need to be reparsed and the
execution plan does not need to be redone.
Jim
"cava123" <cav...@noos.fr> wrote in message
news:3D0CB6CC...@noos.fr...
cava123
> Bind variables are place holders in DML (eg select .. from mytable where
> field1:=hvField1)
>>Only in PL/SQL code ? Which V$ vues give the information if the code use bind
variables or not ? (EXPLAIN PLAN can help to detect if variables are bound or
not -what does HASH JOIN mean- ?
Vladimir M. Zakharychev
variables to bind variables in your static SQL automatically. With SQL,
your host application has to supply actual values for bind variables if
you use them.
NOT using bind variables where appropriate (and it's almost everywhere)
is a major performance and scalability inhibitor. Without them, all your
SQL is unique to Oracle, and each statement is hard-parsed and is kept
in the library cache as new unique SQL, which quickly fills up the SGA with
unique non-shareable statements and introduces library cache latch
contention among other bad things. There are cases when you would want
not to use bind variables (to allow CBO to see actual values so that it can
come up with optimal plan for your particular data distribution), but these
are actually rare.
Tom Kyte's book (Expert one-on-one: Oracle) lists a method for detecting
SQL statements with literals, which allows to spot such statements quickly
and reliably.
In 8i and later, there is CURSOR_SHARING parameter, which automatically
replaces literals with bind variables when set to EXACT (or SIMILAR in 9i),
but it has a number of bugs and caveats associated with it, so it's not a
silver bullet, though it is particularly useful with dynamic SQL. You may want
to look at it, but I would discourage setting it at instance level and only set
it to EXACT in session when it is really needed. Ideally, you should explicitly
use bind variables where they are appropriate and not rely on some database
setting to do it for you automatically.
--
Vladimir Zakharychev (b...@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"cava123" <cav...@noos.fr> wrote in message news:3D0D1F4C...@noos.fr...
Vladimir M. Zakharychev
EXACT (which turns the feature off).
--
Vladimir Zakharychev (b...@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Vladimir M. Zakharychev" <b...@dpsp-yes.com> wrote in message news:aekkc1$454$1...@babylon.agtel.net...
Galen Boyer
> Hi all, We have to use "bind variables" : What does bind variables
> means exactly ?
You'll have to read a bunch to get it exactly, but try to think about
what Oracle has to do when you submit SQL. It has to go through a bunch
of if/then question steps.
Is this SQL syntactically correct?
Do the objects exist?
Does the user submitting the SQL have the permissions on all the
objects?
What plan should I use to perform the data lookup?
Once Oracle answers all of these questions, it will hash the submitted
SQL statement, and store that hash key and the SQL in the SQL shared
area.
Next time you submit the same SQL, it will be able to hash it, query its
system tables to see if the hash exists and then use that versions plans
instead. (Question for you to ponder is when does the hashing of the
SQL statement and subsequent querying of its system tables occur?)
> relation ship with performance ?
HUGE is the operative word.
> and how I can see if it used or not ? (V$SQLAREA,V$SQL,V$SESSION,
Good places to start.
> ...) I have also execute an ALTER SESSION SET AUTOTRACE TRACEONLY
> EXPLAN PLAN for .... and get 'HASH JOIN' : What does it mean HASH JOIN
TableA and TableB are joined together. To you, the statement was
select * from tableA, tableB where tableA.some_field = tableB.some_field
To Oracle, this meant, go get TableA and then go get TableB. Then,
iterate through the results finding where these columns equal, maybe
presorting them so the iteration can assume the order. Hey, wait a
minute! How bout on reading of TableA, I perform an algorithm on
tableA.some_field and store the result of that somewhere along with a
pointer back to the retrieved data? Then, I could perform the same
algoritm on TableB.some_field and use this result for the join. Yeah,
thats the ticket... (I'm sure I got some of the fundamentals of this
wrong, and I deliberately was non-technical, but it should suffice for
your understanding)
> and HASH TABLES ? Thanks
Whew! Never used these. Maybe partioning by hash?
--
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.