All%20About%20Binds

1

• All About Binds

Thomas Kyte
2
Its

• All About Binds

3
Agenda

• Performance
• Is it just about sharing SQL (or is this really a
  parsing talk in disguise)
• Scalability
• Security
• Do I always want to bind?
• What is bind variable peeking?
• Is it good or evil in disguise or a bit of both?
• Im binding, but it isnt sharing whats up
  with that?
• So the developers don't bind is cursor_sharing
  force/similar appropriate system wide?
• What is the real difference between
  cursor_sharing force/similar and which should
  we use under what circumstances?

4
Performance

• What is involved in all Parses
• The conventional parse - syntax
• Semantic check
• What about a hard parse
• Optimization (can you spell C.P.U)
• Row Source Generation
• And then we can finally execute it
• Soft Parse is lighter weight
• But it is called a shared pool, not your pool
• Shared data structures have to be protected
• Optimization can be avoided
• Row Source Generation can be avoided

Bind01.sql
5
Performance

• Wonder if it might affect memory utilization?
• Strange that count() is so low for that first
  query isnt it.
• Unfortunate that sum(sharable_mem) is so high
  (and remember, it really is 10 times that amount)

Bind02.sql
6
Scalability

• But it runs fast enough and Ill buy more memory
• Does it really?
• Run bind03.sql

opstkyte_at_ORA10GR1gt select 11/10000 from dual


11/10000 ---------- .0011
7
Latch Algorithm
Loop for I in 1 .. 1 loop try to get
latch if got latch, return if I 1 then
missesmisses1 end loop INCREMENT WAIT
COUNT sleep Add WAIT TIME End loop
8
More multi-user
USERS NOBIND_CPU PARSE_MANY_CPU
PARSE_ONCE_CPU ------- ---------- --------------
-------------- 1.00 .27 .07
.03 2.00 .72 .21
.09 3.00 1.46 .38
.13 4.00 2.59 .67
.27 5.00 3.20 .85
.34 6.00 4.20 1.01
.40 7.00 4.79 1.20
.51 8.00 5.74 1.44
.53 9.00 6.27 1.60
.64 10.00 7.16 1.76
.72
9
Security

• Google sql injection
• Funny thing happened during my last column

create or replace procedure set_udump (p_udump in
varchar2) as begin execute immediate 'alter
system set user_dump_dest
'''p_udump''' scopememory' end /
10
Security

• Google sql injection
• Funny thing happened during my last column

create or replace procedure set_udump (p_udump in
varchar2) as begin execute immediate 'alter
system set user_dump_dest
'''p_udump''' scopememory' end / begin
set_udump('C\ORA4\admin\ora4\udump2''
scopememory utl_file_dir''''
scopespfile user_dump_dest''C
\ORA4\admin\ora4\udump2') end
11
Security

• Google sql injection
• Funny thing happened during my last column

create or replace procedure set_udump (p_udump in
varchar2) as begin if ( p_udump NOT LIKE ''
) then execute immediate 'alter system set
user_dump_dest '''p_udump'''
scopememory' else raise_application_error(-
20000,'Sorry, but for safety reasons this
procedure does not allow "" in the parameter
value') end if end
12
Do I always want to bind?

• Always say Never say Never
• Never say Always
• You do not want to
• Over Bind
• Always Bind
• Why.

13
Do I always want to bind?

• Over Binding
• Compulsive disorder to eradicate all literals in
  SQL
• Brought on by taking good advice to an illogical
  extreme
• Do we need to bind those?
• Might it be a bad thing to bind those?

Begin for x in ( select object_name
from user_objects where
object_type in ( TABLE, INDEX )) loop

14
Do I always want to bind?

• Always Binding
• Data warehouse no way.
• When you run queries per second, yes.
• When you run queries that take seconds, maybe,
  maybe no.
• Consider the frequency of the query
• 5,000 users running reports. Bind
• 50 users data mining. No Bind
• OLTP. Bind
• End of month report. Maybe No Bind.
• Common Sense, it is all about math

15
Do I always want to bind?

• Always Binding
• But remember SQL Injection!
• That password screen, binds
• Typical queries, binds
• Only the queries that need the advantage of
  literals during optimization!
• And those have to be looked at over and over
• user dump dest, it seemed so simple

16
Bind Variable Peeking

• It is good or pure evil in disguise (neither of
  course)
• Introduced in 9i Release 1
• Makes the first hard parse of
• Optimize as if you submitted
• What are the assumptions then by the implementer
  of this feature.

Select from emp where empno X
Select from emp where empno 1234
bvp01.sql
17
Bind Variable Peeking

• Autotrace/Explain plan caveat with binds in
  general
• Autotrace lies (explain plan lies)
• Well, not really. They just dont have the facts
• Is that the only time we cannot trust them
  completely?
• No, bvp02

bvp02.sql
18
Bind Variable Peeking

• What can you do when those assumptions dont hold
  true for you in a specific case?
• Dont bind that query, that is a possibility.
• Do the math
• Dont use histograms
• Get the general plan
• Consistent Plan, but typically not the best
  plan for all
• Use your domain knowledge
• Input dates within the last month use this
  query, else use that query
• Codes less than 50 use this query, else use
  that query
• Status values of A, M and N . Else.
• Cursor_sharing similar
• You can disable it but that is like dont use
  histograms in a system that uses binds.

19
Im binding, but it isnt sharing

• Many things can do that
• Any environmental variables that affect the
  optimizer
• Or security (this is why PLSQL rules)
• Bind Type mismatch
• Language
• PLSQL compiler switches
• For example, lets tune with SQL_TRACETRUE
• And Look deeper at bind mismatches
• Desc vsql_shared_cursor

tune.sql Bindmis.sql
20
Cursor Sharing

• So the developers don't bind is cursor_sharing
  force/similar appropriate system wide?

No
21
Cursor Sharing

• Negatively Impacts Well Written Applications
• They run slower even if plans do not change
• We just did bind variable peeking, so we know
  about
• Over binding (this is over binding defined)
• Always binding (this is always binding defined)
• Possible plan changes
• Optimizer has less information, doesnt have the
  facts
• Behavior Changes
• Dont know column widths anymore
• Dont know scale/precision anymore

cs01.sql
22
Force/Similar

• Lets take a look at
• What is the real difference between
  cursor_sharing
• Force
• Similar
• Which should we use under what circumstances?
• (neither! Both represent a bug in the developed
  code!)

23
Force/Similar

• Force is just that
• All literals, without any regard to anything,
  will be replaced with binds
• There will be probably one plan generated (all
  things considered the same! Remember
  vsql_shared_cursor)
• Consider the bind variable peeking implications
• Cold start, first query is id99
• Cold start, first query is id1
• Bouncing the database is my tuning tool?

24
Force/Similar

• Similar
• When replacing the bind with a literal (reversed
  purposely) could change the plan
• Multiple child cursors will be developed
• Each can have its own unique plan
• Optimization will use the best plan
• Is this better than force?
• Depends
• More child cursors
• Longer code path
• But is does solve a little more of the problem.

similar.sql
25
Force/Similar

• In Short, just say

No To setting at the system level, this is an
application level bug workaround until we get it
fixed for real tool
26
Questions
and
Answers