PLSQL性能调优

<Insert Picture Here>Oracle Direct SeminarPL/SQL 性能调优日本Oracle 股份有限公司<Insert Picture Here>Agenda•序•编程篇•系统设定篇※资料中介绍的测试内容以及测试结果是在一定的测试环境下生成的。

并不能保证在所有的环境中都会有同样的动作。

序•前提•本讲座要求听众有一定的PL/SQL的基本知识•本讲座中的脚本执行环境为Oracle Database 10g Enterprise Edition 10.2.0.3.0※在低版本的环境中有可能不能正确执行•测试环境•OS: Oracle Enterprise Linux R4 update5•计算机：HP DL360•CPU:Xeon 3.0GHz x2•内存:6GB•DB的实例用DBCA做成，全部使用缺省选项，没有进行任何的优化处理<Insert Picture Here>编程篇-数值型数据类型的选择-变量的声明及NOT NULL 的限制-参数选项NOCOPY 的指定-使用RETURNING -系统固有的动态SQL -BULK 处理处理（（批量处理批量处理））-表函数-管道表函数<Insert Picture Here>数值型数据类型的选择PL/SQL 的数值型数据类型浮点小数浮点小数整数整数小数浮点小数数值的类型双精度64bit单精度32bit -231～231-231～2311E-130 ～9.99…9E 125、-1E-130 ～-9.99…9E 125、取值范围○○○○○PL/SQL 的数据类型○○××○表的数据类型BINARY_DOUBLEBINARY_FLOATPLS_INTEGERBINARY_INTEGERNUMBER整数を扱う際はお勧め需要用到整数的时候，推荐使用这两种类型数值型数据类型不同所引起的性能差异declarewk_cnt 数据类型:= 0;beginfor cnt in 0..100000000 loop wk_cnt := wk_cnt + 1;end loop;end;/numberbinary_integer pls_integer指定任意一种0.31220.31531PLS_INTEGERBINARY_INTEGERNUMBER<Insert Picture Here>变量的声明及NOT NULL的限制变量的声明及NOT NULL 的限制declarevalue1 number not null := 0;beginfor cnt in 0 .. 100000000 loop value1 := 1;end loop;end;/declarevalue1 number;beginfor cnt in 0 .. 100000000 loop value1 := 1;end loop;if value1 is not null then null;end if;end;/每次代入值的时候都会对是否NULL进行检查只需要最后对NULL 检查一次0.521不定义不定义NOT NOT NULL定义定义NOT NOT NULL<Insert Picture Here>参数选项NOCOPY的指定PL/SQL的参数说明程序名(参数名调用模式(NOCOPY) 数据类型(DEFAULT 值), ………)•参数最大数量65536•参数模式•IN：只用于输入的参数→缺省选项•OUT：只用于返回值的参数•IN OUT：既可以输入又可以返回值的参数•使用NOCOPY可以传递参数的地址•即使不使用IN，也是传递的参数的地址•参数的长度越大，效果越明显•DEFAULT句：用来参数为空时(NULL)的缺省值NOCOPY的指定与否产生的性能差declaretype test_tbl_type is table of pls_integerindex by pls_integer;test_tbl test_tbl_type;procedure test (arg_cnt in pls_integer,arg_tbl in out nocopy test_tbl_type) isbeginfor cnt_test in test_tbl.first..arg_st looparg_tbl(cnt_test) :=arg_tbl(cnt_test) + arg_cnt;end loop;end;--接右边指定，不指定时的比较0.68671 NOCOPYNOCOPY指定指定NOCOPYNOCOPY不指定不指定beginfor cnt in 0 .. 10000 looptest_tbl(cnt) := cnt;end loop;for cnt in 0 .. 10000 looptest(cnt, test_tbl);end loop;end;/<Insert Picture Here>使用RETURNING是什么？？RETURNING是什么•从DML语句处理对象行的数据中间，返回指定列的数据•INSERT INTO …VALUES (…) RETURNING COL1 INTO :COL1;•UPDATE …SET …RETURNING COL1 INTO :COL1;•DELETE …RETURNING COL1 INTO :COL1;•可以指定多个返回数据•……RETURNING COL1, COL2 INTO :COL1, :COL2;•可以指定返回数据保存到数组等数据类型中•……RETURNING COL1 INTO :COL1_ARRAY;•也适用于处理对象为多行数据的SQL使用RETURNING 的好处•可以减少SQL 的执行次数declareseq number;begininsert into table1 values(seq1.nextval, 'AAA', 'BBB');select seq1.currval into seq from dual;dbms_output.put_line (seq || ‘的值已经取得');end;/declareseq number;begininsert into table1 values(seq1.nextval, 'AAA', 'BBB')returning col1 into seq ;dbms_output.put_line (seq || '的值已经取得');end;/执行2次SQL执行1次SQL上面的代码执行10次的性能比较0.62951RETURNING RETURNING<Insert Picture Here>系统固有的动态SQL什么是动态SQL？•程序实际执行中动态组合而成的SQL•静态SQL：编写程序时就写好的固定的SQL•动态SQL需要与否的关键是看程序执行前除了参数以后的因素是否能够全部定下来•不管是静态的还是动态的SQL都可以使用bind参数•应尽量避免使用动态SQLPL/SQL关联的动态SQL•～Oracle8：DBMS_SQL•由系统包提供•程序编写非常复杂•Oracle8i～：EXECUTE IMMEDIATE、OPEN-FOR •系统固有的动态SQL•作为PL/SQL的一个语句提供•程序的编写非常轻松•性能也优于DBMS_SQLdeclaresource varchar2(30) := 'emp';emp_no emp.empno%type;emp_name emp.ename%type;emp_sal emp.sal%type;sel_cursor integer;ins_stmt integer;row_cnt integer;beginsel_cursor:= dbms_sql.open_cursor;dbms_sql.parse(sel_cursor, 'select empno,ename,sal from '|| source, dbms_sql.native);dbms_sql.define_column(sel_cursor, 1, emp_no);dbms_sql.define_column(sel_cursor, 2, emp_name, 10);dbms_sql.define_column(sel_cursor, 3, emp_sal);ins_stmt:= dbms_sql.open_cursor;dbms_sql.parse(ins_stmt, 'insert into temp values (:ins_empno, :ins_ename, :ins_sal)', dbms_sql.native); 接下页row_cnt:= dbms_sql.execute(sel_cursor);while (dbms_sql.fetch_rows(sel_cursor) > 0) loopdbms_sql.column_value(sel_cursor, 1, emp_no);dbms_sql.column_value(sel_cursor, 2, emp_name);dbms_sql.column_value(sel_cursor, 3, emp_sal);dbms_sql.bind_variable(ins_stmt, 'ins_empno', emp_no);dbms_sql.bind_variable(ins_stmt, 'ins_ename', emp_name);dbms_sql.bind_variable(ins_stmt, 'ins_sal', emp_sal);row_cnt:= dbms_sql.execute(ins_stmt);end loop;dbms_sql.close_cursor(sel_cursor);dbms_sql.close_cursor(ins_stmt);end;/EXECUTE IMMEDIATE文编码例declaresource varchar2(30) := 'emp';type sel_cursor_type is ref cursor;sel_cursor sel_cursor_type;emp_no emp.empno%type;emp_name emp.ename%type;emp_sal emp.sal%type;beginopen sel_cursor for'select empno, ename, sal from ' || source;loopfetch sel_cursor into emp_no, emp_name, emp_sal;exit when sel_cursor%notfound;execute immediate'insert into temp values (:empno, :ename, :sal)' using emp_no, emp_name, emp_sal;end loop;close sel_cursor;end;/动态SQL的性能比较0.65791EXECUTE DBMS_SQL<Insert Picture Here>批量处理））BULK处理处理（（批量处理什么是BULK处理？处理？Oracle8i～•把大量的数据(主要是行)一次性的处理•一次性的进行多行的FETCH, INSERT, etc.•通过集合数据类型进行处理•PL/SQL表•VARRAY•嵌套表（NEST TABLE）FETCH的BULK处理declaretype emp_tbl_type is table ofemp%rowtype index by pls_integer; emp_tbl emp_tbl_type;cursor emp_cur is select * from emp; beginopen emp_cur;loopfetch emp_curinto emp_tbl(emp_cur%rowcount);exit when emp_cur%notfound;end loop;close emp_cur;end;/declaretype emp_tbl_type is table ofemp%rowtype index by pls_integer;emp_tbl emp_tbl_type;cursor emp_cur is select * from emp; beginopen emp_cur;fetch emp_cur bulk collectinto emp_tbl;close emp_cur;end;/处理10万行数据时的性能比较0.1751BULK处理非BULK处理SELECT INTO的BULK处理declaretype emp_tbl_type is table ofemp%rowtype index by pls_integer; emp_tbl emp_tbl_type;cursor emp_cur is select * from emp; beginopen emp_cur;loopfetch emp_curinto emp_tbl(emp_cur%rowcount);exit when emp_cur%notfound;end loop;close emp_cur;end;/declaretype emp_tbl_type is table ofemp%rowtypeindex by pls_integer; emp_tbl emp_tbl_type;beginselect * bulk collect into emp_tblfrom emp;end;/处理10万行数据时的性能比较0.18141BULK处理非BULK处理RETURNING的BULK处理………begininsert into table1(col1, col2)select seq1.nextval, col8 from table2returning col1 bulk collect into col1_tbl;………end;FORALL的BULK处理declaretype emp_tbl_type is table ofemp.empno%type index by pls_integer; emp_tbl emp_tbl_type;beginselect empno bulk collect into emp_tblfrom emp;for i in emp_tbl.first.. emp_stloopupdate emp set sal= sal+ 100where empno= emp_tbl(i);end loop;end;/declaretype emp_tbl_type is table ofemp.empno%type index by pls_integer; emp_tbl emp_tbl_type;beginselect empno bulk collect into emp_tblfrom emp;forall i in emp_tbl.first.. emp_stupdate emp set sal= sal+ 100where empno= emp_tbl(i);end;/处理10万行数据时的性能比较0.44911BULK处非BULK处<Insert Picture Here>表函数什么是表函数什么是表函数？？•返回值是集合(NEST TABLE 、VARRAY)的函数•Oracle8i 以后可以使用•可以在内存里进行程序间的数据交换•从Oracle9i 开始，可以作为SELECT 的表使用Oracle8i ～•用EMP表的EMPNO列和ENAME列构成的表函数的代码示例SQL> create or replace type emp_type as object2 (empno number(5), ename varchar2(10))3 /Type created.SQL> create or replace type emp_tbl_type as table of emp_type 2 /Type created.SQL> create or replace function get_emp_tbl(arg1 number)2 return emp_tbl_type is3 wk_emp_tbl emp_tbl_type;4 begin5 select emp_type(empno, ename)bulk collect into wk_emp_tbl6 from emp where deptno = arg1;7 return wk_emp_tbl;8 end;9 /Function created.指定NEST TABLE 作为返回值创建对象类型的数据类型时，系统会自动的创建一个和对象同名的系统函数。