Slide1 l.jpg
Advertisement
This presentation is the property of its rightful owner.
1 / 26

All About Binds PowerPoint PPT Presentation

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?

Download Presentation

All About Binds

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 l.jpg

All About Binds

Thomas Kyte


Slide2 l.jpg

It’s

All About Binds


Agenda l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

Scalability

  • But it runs fast enough and I’ll buy more memory

  • Does it really?

    • Run bind03.sql

[email protected]> select 11/10000 from dual;

11/10000

----------

.0011


Latch algorithm l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

Cursor Sharing

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

No


Cursor sharing21 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

Questions

and

Answers


  • Login