All About Binds

1. All About Binds Thomas Kyte

2. It’s 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? • 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?

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 isn’t 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 I’ll buy more memory • Does it really? • Run bind03.sql ops$tkyte@ORA10GR1> 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 misses=misses+1 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||''' scope=memory'; 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||''' 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;

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||''' scope=memory'; 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 don’t 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 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.

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

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

22. 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!)

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 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?

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

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