Download
slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
All About Binds PowerPoint Presentation
Download Presentation
All About Binds

All About Binds

127 Vues Download Presentation
Télécharger la présentation

All About Binds

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

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

  5. sqlplus ops$tkyte%ORA11GR2> create table t ( x int primary key ); Table created. ops$tkyte%ORA11GR2> create table parse_stats 2 as 3 select a.name, b.value, cast( null as number ) run1, cast(null as number) run2 4 from v$statname a, v$mystat b 5 where a.statistic# = b.statistic# 6 and a.name like 'parse%'; Table created.

  6. sqlplus ops$tkyte%ORA11GR2> declare 2 l_cursor sys_refcursor; 3 begin 4 for i in 1 .. 10000 5 loop 6 open l_cursor for 'select /* nobind */ * from t where x = ' || i; 7 close l_cursor; 8 end loop; 9 end; 10 / PL/SQL procedure successfully completed. Elapsed: 00:00:11.04

  7. sqlplus ops$tkyte%ORA11GR2> merge into parse_stats using ( select a.name, b.value new_val 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and a.name like 'parse%' ) s 5 on (parse_stats.name = s.name) when matched then update set run1 = new_val; 6 rows merged. Elapsed: 00:00:00.15 Bind01.sql

  8. sqlplus ops$tkyte%ORA11GR2> declare 2 l_cursor sys_refcursor; 3 begin 4 for i in 1 .. 10000 5 loop 6 open l_cursor for 'select /* bind */ * from t where x = :x' using i; 7 close l_cursor; 8 end loop; 9 end; 10 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.73 Bind01.sql

  9. sqlplus ops$tkyte%ORA11GR2> merge into parse_stats using ( select a.name, b.value new_val 2 from v$statname a, v$mystat b 3 where a.statistic# = b.statistic# 4 and a.name like 'parse%' ) s 5 on (parse_stats.name = s.name) when matched then update set run2 = new_val; 6 rows merged. Elapsed: 00:00:00.01 Bind01.sql

  10. sqlplus ops$tkyte%ORA11GR2> select name, run1, run2, 2 decode( run1, 0, null, 3 to_char(run2/run1*100,'9,999.00') || ' %' ) run2_pct_of_run1 4 from ( 5 select name, run1-value run1, run2-run1 run2 6 from parse_stats 7 ) 8 / NAME RUN1 RUN2 RUN2_PCT_OF ------------------------------ ---------- ---------- ----------- parse time cpu 271 5 1.85 % parse time elapsed 278 9 3.24 % parse count (total) 10078 10018 99.40 % parse count (hard) 10014 3 .03 % parse count (failures) 0 0 parse count (describe) 0 0 6 rows selected. Bind01.sql

  11. 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 many times that amount) Bind02.sql

  12. sqlplus ops$tkyte%ORA11GR2> select count(*), to_char(sum(sharable_mem),'999,999,999') sm, 2 to_char(sum(sharable_mem)/ decode( count(*), 0, to_number(null), count(*) ), '999,999,999' ) per_stmt 3 from v$sql 4 where sql_text like 'select /* nobind */ * from t where x = %'; COUNT(*) SM PER_STMT ---------- ------------ ------------ 57 781,917 13,718 ops$tkyte%ORA11GR2> ops$tkyte%ORA11GR2> select count(*), to_char(sum(sharable_mem),'999,999,999') sm 2 from v$sql 3 where sql_text = 'select /* bind */ * from t where x = :x'; COUNT(*) SM ---------- ------------ 1 13,856

  13. sqlplus ops$tkyte%ORA11GR2> declare 2 l_stmt long := 'select /*+ RULE */ * from t where x in ( 1'; 3 l_rec t%rowtype; 4 l_cursor sys_refcursor; 5 begin 6 for i in 2 .. 1000 7 loop 8 l_stmt := l_stmt || ', ' || i; 9 end loop; 10 l_stmt := l_stmt || ' ) or x in ( 1'; 11 for i in 1002 .. 2000 12 loop 13 l_stmt := l_stmt || ', ' || i; 14 end loop; 15 open l_cursor for l_stmt || ' )'; 16 fetch l_cursor into l_rec; 17 close l_cursor; 18 end; 19 / PL/SQL procedure successfully completed.

  14. sqlplus ops$tkyte%ORA11GR2> select count(*), to_char(sum(sharable_mem),'999,999,999') sm 2 from v$sql 3 where sql_text like 'select /*+ RULE */ * from t where x in ( 1%'; COUNT(*) SM ---------- ------------ 1 2,597,091

  15. Scalability • But it runs fast enough and I’ll buy more memory • Does it really? • Run bind03.sql • Review multiuser.sql • Findings coming right up… ops$tkyte@ORA10GR1> select 11/10000 from dual; 11/10000 ---------- .0011

  16. sqlplus ops$tkyte%ORA11GR2> create table t ( x int primary key ); Table created. ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' ); PL/SQL procedure successfully completed.

  17. sqlplus ops$tkyte%ORA11GR2> create or replace procedure nobind 2 as 3 l_cursor sys_refcursor; 4 begin 5 for i in 1 .. 2000 6 loop 7 open l_cursor for 'select /* nobind */ * from t where x = ' || i; 8 close l_cursor; 9 end loop; 10 end; 11 / Procedure created.

  18. sqlplus ops$tkyte%ORA11GR2> create or replace procedure bind 2 as 3 l_cursor sys_refcursor; 4 begin 5 for i in 1 .. 2000 6 loop 7 open l_cursor for 'select /* bind */ * from t where x = :x' using i; 8 close l_cursor; 9 end loop; 10 end; 11 / Procedure created.

  19. sqlplus ops$tkyte%ORA11GR2> exec runstats_pkg.rs_start PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> exec nobind PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> exec runstats_pkg.rs_middle PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> exec bind PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> exec runstats_pkg.rs_stop(10000) Run1 ran in 68 hsecs Run2 ran in 8 hsecs run 1 ran in 850% of the time

  20. sqlplus Name Run1 Run2 Diff LATCH.kks stats 10,831 33 -10,798 LATCH.shared pool simulator 15,068 63 -15,005 LATCH.row cache objects 48,470 191 -48,279 STAT...session uga memory max 123,452 65,512 -57,940 STAT...session uga memory 65,512 0 -65,512 STAT...session pga memory max 131,072 65,536 -65,536 LATCH.shared pool 87,428 327 -87,101 STAT...session pga memory 65,536 -65,536 -131,072 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 171,431 1,150 -170,281 14,907.04% PL/SQL procedure successfully completed.

  21. 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; /

  22. 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;

  23. 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; inj.sql

  24. sqlplus ops$tkyte%ORA11GR2> create or replace procedure inj( p_date in date ) 2 as 3 l_rec all_users%rowtype; 4 c sys_refcursor; 5 l_query long; 6 begin 7 l_query := ' 8 select * 9 from all_users 10 where created = ''' ||p_date ||''''; 11 12 dbms_output.put_line( l_query ); 13 open c for l_query; 14 15 for i in 1 .. 5 16 loop 17 fetch c into l_rec; 18 exit when c%notfound; 19 dbms_output.put_line( l_rec.username || '.....' ); 20 end loop; 21 close c; 22 end; 23 / Procedure created.

  25. sqlplus ops$tkyte%ORA11GR2> exec inj( sysdate ) select * from all_users where created = '25-OCT-10' PL/SQL procedure successfully completed.

  26. sqlplus ops$tkyte%ORA11GR2> alter session set 2 nls_date_format = 'dd-mon-yyyy"'' or ''a'' = ''a"'; Session altered.

  27. sqlplus ops$tkyte%ORA11GR2> exec inj( sysdate ) select * from all_users where created = '25-oct-2010' or 'a' = 'a' C..... B..... A..... FB_DEMO..... BIG_TABLE..... PL/SQL procedure successfully completed.

  28. Do I always want to bind? • Always say “Never say Never” • Never say “Always” • I always say… • You do not want to • Over Bind • Always Bind • Why….

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

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

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

  32. sqlplus ops$tkyte%ORA11GR2> create table t 2 as 3 select 99 id, a.* 4 from stage a 5 where rownum <= 20000; Table created. ops$tkyte%ORA11GR2> ops$tkyte%ORA11GR2> update t set id = 1 where rownum = 1; 1 row updated. ops$tkyte%ORA11GR2> ops$tkyte%ORA11GR2> create index t_idx on t(id); Index created.

  33. sqlplus ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats 3 ( user, 'T', 4 method_opt=>'for all indexed columns size 254', 5 estimate_percent => 100, 6 cascade=>TRUE ); 7 end; 8 / PL/SQL procedure successfully completed.

  34. sqlplus ops$tkyte%ORA11GR2> set autotrace traceonly explain ops$tkyte%ORA11GR2> select count(object_type) from t where id = 1; Execution Plan ---------------------------------------------------------- Plan hash value: 1789076273 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 14 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 14 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID"=1) ops$tkyte%ORA11GR2> set autotrace off

  35. sqlplus ops$tkyte%ORA11GR2> set autotrace traceonly explain ops$tkyte%ORA11GR2> select count(object_type) from t where id = 99; Execution Plan ---------------------------------------------------------- Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 82 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 14 | | | |* 2 | TABLE ACCESS FULL| T | 19999 | 273K| 82 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"=99) ops$tkyte%ORA11GR2> set autotrace off

  36. sqlplus ops$tkyte%ORA11GR2> variable n number ops$tkyte%ORA11GR2> exec :n := 1 PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> explain plan for 2 select count(object_type) from t n_is_1_first where id = :n; Explained. ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------- Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 82 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 14 | | | |* 2 | TABLE ACCESS FULL| T | 10000 | 136K| 82 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"=TO_NUMBER(:N)) 14 rows selected.

  37. sqlplus ops$tkyte%ORA11GR2> alter session set events '10046 trace name context forever, level 12'; Session altered. ops$tkyte%ORA11GR2> select count(object_type) from t n_is_1_first where id = :n; COUNT(OBJECT_TYPE) ------------------ 1

  38. sqlplus ops$tkyte%ORA11GR2> exec :n := 99 PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> select count(object_type) from t n_is_1_first where id = :n; COUNT(OBJECT_TYPE) ------------------ 19999

  39. sqlplus ops$tkyte%ORA11GR2> select count(object_type) from t n_is_99_first where id = :n; COUNT(OBJECT_TYPE) ------------------ 19999

  40. sqlplus ops$tkyte%ORA11GR2> select count(object_type) from t n_is_99_first where id = :n; COUNT(OBJECT_TYPE) ------------------ 1

  41. sqlplus select count(object_type) from t n_is_1_first where id = :n call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.07 0 0 0 0 Fetch 2 0.00 0.02 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.09 0 3 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 211 (OPS$TKYTE) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=0 us) 1 TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0 time=0 us cost=2 size=14 card=1) 1 INDEX RANGE SCAN T_IDX (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 88040) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 1 SORT (AGGREGATE) 1 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T' (TABLE)

  42. sqlplus select count(object_type) from t n_is_1_first where id = :n call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.01 0 322 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.01 0 322 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 211 (OPS$TKYTE) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=322 pr=0 pw=0 time=0 us) 19999 TABLE ACCESS BY INDEX ROWID T (cr=322 pr=0 pw=0 time=76043 us cost=2 size=14 card=1) 19999 INDEX RANGE SCAN T_IDX (cr=41 pr=0 pw=0 time=25984 us cost=1 size=0 card=1)(object id 88040) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 1 SORT (AGGREGATE) 19999 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T' (TABLE)

  43. sqlplus select count(object_type) from t n_is_99_first where id = :n call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 285 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 285 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 211 (OPS$TKYTE) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=285 pr=0 pw=0 time=0 us) 19999 TABLE ACCESS FULL T (cr=285 pr=0 pw=0 time=24838 us cost=82 size=279986 card=19999) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 1 SORT (AGGREGATE) 19999 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T' (TABLE)

  44. sqlplus select count(object_type) from t n_is_99_first where id = :n call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 285 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 285 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 211 (OPS$TKYTE) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=285 pr=0 pw=0 time=0 us) 1 TABLE ACCESS FULL T (cr=285 pr=0 pw=0 time=0 us cost=82 size=279986 card=19999) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 1 SORT (AGGREGATE) 1 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T' (TABLE)

  45. Bind Variable Peeking • Adaptive Cursor Sharing • New in 11gR1 • Will help solve most bind variable peeking issues • Works currently with equality and range operations (but not “like” predicates) bvp03.sql

  46. sqlplus ops$tkyte%ORA11GR2> create table t 2 as 3 select 99 id, a.* 4 from stage a 5 where rownum <= 20000; Table created. ops$tkyte%ORA11GR2> ops$tkyte%ORA11GR2> update t set id = 1 where rownum = 1; 1 row updated. ops$tkyte%ORA11GR2> ops$tkyte%ORA11GR2> create index t_idx on t(id); Index created.

  47. sqlplus ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats 3 ( user, 'T', 4 method_opt=>'for all indexed columns size 254', 5 estimate_percent => 100, 6 cascade=>TRUE ); 7 end; 8 / PL/SQL procedure successfully completed.

  48. sqlplus ops$tkyte%ORA11GR2> exec :n := 1; PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> select /*BVP03*/ count(subobject_name) from t where id = :n; COUNT(SUBOBJECT_NAME) --------------------- 0 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ----------------------- SQL_ID 4un62u0uttg6u, child number 0 ------------------------------------- select /*BVP03*/ count(subobject_name) from t where id = :n Plan hash value: 1789076273 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 20 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 20 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID"=:N) 20 rows selected.

  49. sqlplus ops$tkyte%ORA11GR2> select child_number, executions, buffer_gets, 2 is_bind_sensitive, is_bind_aware 3 from v$sql 4 where sql_text like 'select /*BVP03*/ %'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE ------------ ---------- ----------- ------------------ --------------- 0 1 3 Y N

  50. sqlplus ops$tkyte%ORA11GR2> exec :n := 99; PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> select /*BVP03*/ count(subobject_name) from t where id = :n; COUNT(SUBOBJECT_NAME) --------------------- 118 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ----------------------------------------- SQL_ID 4un62u0uttg6u, child number 0 ------------------------------------- select /*BVP03*/ count(subobject_name) from t where id = :n Plan hash value: 1789076273 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 20 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 20 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID"=:N) 20 rows selected.