AllAboutBinds by rocker12

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?

• 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? Copyright Kyte Inc. 2005

Performance • Would you compile a subroutine, run it and then throw away the object code for every set of inputs? –

Hard Parse

• So, why do you do that to SQL… • Would you compile a subroutine every time to run it, regardless of the inputs? –

Soft Parse

• So, why do you do that in SQL…. Copyright Kyte Inc. 2005

Performance • What is involved in a Parse – – –

The “conventional” parse – syntax, semantic check Optimization (can you spell C.P.U…) Row Source Generation

• And then we can finally execute it • Conventional parse is fairly lightweight – –

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 Copyright Kyte Inc. 2005

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 Copyright Kyte Inc. 2005

Scalability • But it runs fast enough and I’ll buy more memory ops$tkyte@ORA10GR1> select 11/10000 from dual; 11/10000 ---------.0011

• Does it really? – – –

Run bind03.sql Review multiuser.sql Findings coming right up…

MULTI_USER.SQL test • PowerEdge 2600 • 2 Xeon 3.0GHZ CPU, 512 Cache –

Really appear as 4 1.3GHz CPUs to the OS (hyper threading)

• 2 Gig Ram • You would expect if you are CPU bound using 1 of 4 CPUs (just parsing…) doubling the workload would double throughput • Up to 4 times seems reasonable. • Not a chance…. Copyright Kyte Inc. 2005

MULTI_USER.SQL test Users Minutes Increase 1 5.93 2 9.48 3.55 3 13.85 4.37 4 19.20 5.35 5 24.87 5.67 6 30.22 5.35 7 35.37 5.15 8 40.75 5.38 9 45.50 4.75 10 51.92 6.42 Copyright Kyte Inc. 2005

We basically observed no real return for each additional CPU You cannot solve a hard parse problem with CPU

MULTI_USER.SQL test Users Minutes 1 5.93 2 9.48 3 13.85 4 19.20 5 24.87 6 30.22 7 35.37 8 40.75 9 45.50 10 51.92 Copyright Kyte Inc. 2005

Increase CPU in CPU Increase Time used 351 3.55 1,095 744 4.37 2,309 1,214 5.35 4,163 1,854 5.67 5,092 929 5.35 6,210 1,118 5.15 7,290 1,080 5.38 8,444 1,154 4.75 9,406 962 6.42 10,890 1,484

The linear growth in CPU usage would have been nice… If the elapsed time hadn’t gone up Also, 351 – single user. 744 more for the second user! And we had a free CPU

MULTI_USER.SQL test Top 5 Timed Events (SINGLE USER) ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Call Time -------------------------------------------- ------------ ----------- --------CPU time 351 92.93 class slave wait 2 10 2.64 Queue Monitor Task Wait 2 10 2.64 latch: shared pool 455 4 .97 control file parallel write 115 2 .62 Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->3+ -------------------------- -------------- ----------- ----------- -----------shared pool 6,041,642 5,093 455 4730/272/90/1 library cache 4,980,141 1,987 15 1972/15/0/0

CPU munchers

52 does not even begin to describe the waiting & spinning that is really happening

Top 5 Timed Events (TWO USERS) ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Call Time -------------------------------------------- ------------ ----------- --------CPU time 1,095 93.26 latch: shared pool 133,949 52 4.46 class slave wait 2 10 .85 Queue Monitor Task Wait 2 10 .85 control file parallel write 184 3 .28 Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->3+ -------------------------- -------------- ----------- ----------- -----------shared pool 11,298,214 932,219 133,949 798500/133501/206/12 row cache objects 10,531,820 890,177 1 890176/1/0/0 library cache 9,595,471 488,299 10,173 478134/10157/8/0

MULTI_USER.SQL test • Change one line of code: open l_cursor for 'select * from t t' || p_job || ' where x = ' || i; open l_cursor for 'select * from t t' || p_job || ' where x = :x' using i;

• That is, we fixed the bug in the code…. • Well, part of the bug…

MULTI_USER.SQL test Not as good as it could be Users 1 2 3 4 5 6 7 8 9 10

No Bind Bind Pct of Minutes Minutes Savings Runtime 5.93 0.53 5.40 8.94 9.48 0.78 8.70 8.23 13.85 0.95 12.90 6.86 19.20 1.22 17.98 6.35 24.87 1.45 23.42 5.83 30.22 1.72 28.50 5.69 35.37 2.07 33.30 5.85 40.75 2.38 38.37 5.84 45.50 2.67 42.83 5.87 51.92 2.95 48.97 5.68

We have a massive soft parsing problem now But everything is relative, isnâ&#x20AC;&#x2122;t it. An almost 20x speed up would be fairly impressive But, this could run light speed â&#x20AC;&#x201C; there is still a big hit (look at users 1,2,3,4)

Security â&#x20AC;˘ Google sql injection â&#x20AC;˘ 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; Copyright Kyte Inc. 2005

Security â&#x20AC;˘ Google sql injection â&#x20AC;˘ 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; Copyright Kyte Inc. 2005

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 bind? • Over Binding – –

Compulsive disorder to eradicate all literals in SQL Brought on by taking good advice to an illogical extreme

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

Do we need to bind those? Might it be a bad thing to bind those?

Do I always want to bind? • Over Binding – –

– –

Literals in Static SQL are perfectly OK Problem is neither VB nor Java do Static SQL • JSQL is considered “dead” • So, it becomes very hard to tell what is truly variable Bind only that which varies Literals for everything else

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 o 5,000 users running reports. Bind o 50 users data mining. No Bind o OLTP. Bind o End of month report. Maybe No Bind. o Common Sense, it is all about math

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 • It is good or pure evil in disguise (neither of course) • Introduced in 9i Release 1 • Makes the first hard parse of: Select * from emp where empno = :X;

• Optimize as if you submitted: Select * from emp where empno = 1234;

• What are the assumptions then by the implementer of this feature. bvp01.sql Copyright Kyte Inc. 2005

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…

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….

–

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 • 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 Copyright Kyte Inc. 2005

Cursor Sharing â&#x20AC;˘ So the developers don't bind is cursor_sharing = force/similar appropriate system wide?

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

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/Similar • Force is just that – – –

All literals, without any regard to anything, will be replaced with binds There will be precisely 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/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.

Force/Similar • In Short, just say

To setting at the system level, this is an application level bug “workaround until we get it fixed for real” tool Copyright Kyte Inc. 2005