slide1
Download
Skip this Video
Download Presentation
All About Binds

Loading in 2 Seconds...

play fullscreen
1 / 26

All About Binds - PowerPoint PPT Presentation


  • 462 Views
  • Uploaded on

All About Binds Thomas Kyte It’s All About Binds 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?

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'All About Binds' - johana


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1
All About Binds

Thomas Kyte

slide2
It’s

All About Binds

agenda
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?
  • I’m binding, but it isn’t sharing – what’s 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?
performance
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

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

Bind02.sql

scalability
Scalability
  • But it runs fast enough and I’ll buy more memory
  • Does it really?
    • Run bind03.sql

ops$tkyte@ORA10GR1> select 11/10000 from dual;

11/10000

----------

.0011

latch algorithm
Latch Algorithm

Loop

for I in 1 .. 1

loop

try to get latch

if got latch, return

if I = 1 then misses=misses+1

end loop

INCREMENT WAIT COUNT

sleep

Add WAIT TIME

End loop;

more multi user
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

security
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||''' scope=memory';

end;

/

security10
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||''' scope=memory';

end;

/

begin

set_udump('C:\ORA4\admin\ora4\udump2'' scope=memory

utl_file_dir=''*'' scope=spfile

user_dump_dest=''C:\ORA4\admin\ora4\udump2');

end;

security11
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||''' scope=memory';

else

raise_application_error(-20000,'Sorry, but for safety

reasons this procedure does not allow "=" in the parameter

value');

end if;

end;

do i always want to bind
Do I always want to bind?
  • Always say “Never say Never”
  • Never say “Always”
  • You do not want to
    • Over Bind
    • Always Bind
    • Why….
do i always want to bind13
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

do i always want to bind14
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
do i always want to bind15
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
bind variable peeking
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

bind variable peeking17
Bind Variable Peeking
  • Autotrace/Explain plan caveat with binds in general
    • Autotrace “lies” (explain plan “lies”)
      • Well, not really. They just don’t have the facts
    • Is that the only time we cannot trust them completely?
      • No, bvp02…

bvp02.sql

bind variable peeking18
Bind Variable Peeking
  • What can you do when those assumptions don’t hold true for you in a specific case?
    • Don’t bind that query, that is a possibility.
      • Do the math…
    • Don’t 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 “don’t use histograms” in a system that uses binds.
i m binding but it isn t sharing
I’m binding, but it isn’t 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_TRACE=TRUE
  • And Look deeper at “bind mismatches”
  • Desc v$sql_shared_cursor

tune.sql

Bindmis.sql

cursor sharing
Cursor Sharing
  • So the developers don't bind is cursor_sharing = force/similar appropriate system wide?

No

cursor sharing21
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, doesn’t have the facts
    • Behavior Changes
      • Don’t know column widths anymore
      • Don’t know scale/precision anymore

cs01.sql

force similar
Force/Similar
  • Let’s 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!)
force similar23
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 v$sql_shared_cursor)
    • Consider the bind variable peeking implications
      • Cold start, first query is id=99
      • Cold start, first query is id=1
      • Bouncing the database is my tuning tool?
force similar24
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 it’s 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

force similar25
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

slide26
Questions

and

Answers