转- oracle10g-如何提高pl/sql程序的性能

oracle10g-如何提高pl/sql程序的性能(转)

oracle10g-如何提高pl/sql程序的性能(1) 转自：http://blog.chinaunix.net/u/25176/showart_1089339.html PLSQL_OPTIMIZE_LEVEL Property Description Parameter type Integer Default value DE>2DE> Modifiable DE>ALTER SESSIONDE>, DE>ALTER SYSTEMDE> Range of values DE>0DE> to DE>2DE>   DE>PLSQL_OPTIMIZE_LEVELDE> specifies the optimization level that will be used to compile PL/SQL library units. The higher the setting of this parameter, the more effort the compiler makes to optimize PL/SQL library units. Values: ·  DE>0DE> Maintains the evaluation order and hence the pattern of side effects, exceptions, and package initializations of Oracle9i and earlier releases. Also removes the new semantic identity of DE>BINARY_INTEGERDE> and DE>PLS_INTEGERDE> and restores the earlier rules for the evaluation of integer expressions. Although code will run somewhat faster than it did in Oracle9i, use of level 0 will forfeit most of the performance gains of PL/SQL in Oracle Database 10g. ·  DE>1DE> Applies a wide range of optimizations to PL/SQL programs including the elimination of unnecessary computations and exceptions, but generally does not move source code out of its original source order. ·  DE>2DE> Applies a wide range of modern optimization techniques beyond those of level 1 including changes which may move source code relatively far from its original location. Generally, setting this parameter to DE>2DE> pays off in better execution performance. If, however, the compiler runs slowly on a particular source module or if optimization does not make sense for some reason (for example, during rapid turnaround development), then setting this parameter to DE>1DE> will result in almost as good a compilation with less use of compile-time resources.         必须说明的是，以下内容是属于学习笔记的部分，虽然PDF的E文也看得懂，但是还是觉得翻译为自己的国文看起来更加舒畅一些。         此处原文见<<Oracle Database PL/SQL User’s Guide and Reference>>的第十一章节的内容"Tuning PL/SQL Applications for Performance"－－调整Pl/sql应用程序的性能。         一，和PL/SQL编译相关的初始化参数         自然这里的初始化参数指的就是init文件中的参数。                PLSQL_CCFLAGS         主要用于条件编译中                PLSQL_CODE_TYPE    可以选择的值是NATIVE还是INTERPRETED                PLSQL_DEBUG              true/false，true的时候，代码别编译为Interpreted类型以便调试。。                PLSQL_NATIVE_LIBRARY_DIR        全编译时候存放库的路径                PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT    这和上一个是配对使用的，也是用于全编译。                PLSQL_OPTIMIZE_LEVEL   特别介绍以下，不过oracle建议我们一般不要修改。                －－－－－－－－－－－－－－－－－－－－－                     原文不晦涩，也就不翻译了。                     PLSQL_OPTIMIZE_LEVEL specifies the optimization level that will be used to compile PL/SQL library units. The higher the setting of this parameter, the more effort the compiler makes to optimize PL/SQL library units. Values: ■ 0（主要为了向下兼容） Maintains the evaluation order and hence the pattern of side effects, exceptions, and package initializations of Oracle9i and earlier releases. Also removes the new semantic identity of BINARY_INTEGER and PLS_INTEGER and restores the earlier rules for the evaluation of integer expressions. Although code will run somewhat faster than it did in Oracle9i, use of level 0 will forfeit most of the performance gains of PL/SQL in Oracle Database 10g. ■ 1（一般化，不过一般不做特殊优化） Applies a wide range of optimizations to PL/SQL programs including the elimination of unnecessary computations and exceptions, but generally does not move source code out of its original source order. ■ 2（和1类似，但是可能会修改你的源码，在生成的库中，代码的顺序会变化） Applies a wide range of modern optimization techniques beyond those of level 1 including changes which may move source code relatively far from its original location. Generally, setting this parameter to 2 pays off in better execution performance. If, however, the compiler runs slowly on a particular source module or if optimization does not make sense for some reason (for example, during rapid turnaround development), then setting this parameter to 1 will result in almost as good a compilation with less use of compile-time resources. The value of this parameter is stored persistently with the library unit. 最后一句话：在生成的库中总是会存储这个参数的值。                －－－－－－－－－－－－－－－－－－－－－                PLSQL_WARNINGS        属于编译的参数，控制可以输出的参数，可以基于session和system的级别修改                NLS_LENGTH_SEMANTICS.  这个参数比较玄妙，这是用于控制类型为char和varchar2的列使用字节还是                                                       字来存储相关的数据，默认是用单字节(byte),也可以是character，不影响已经存                                                       在的数据，不影响SYSTEM和sys用户的对象。         关于这些参数的定义可以见<<oracle database reference>>,非常的全，至于如何修改它们，则可以参考<<oracle database sql reference>>,这两部书都是特大部头。         二，如何优化（或者是优化的原理是什么）         有一点很关键的是，10g之后oracle会重新排列代码（相应的plsql_optimize_level＝2)（应该来说，通常重新排列之后的算法是更好的，当然也许会存在更不好的，这个以后再说）。         需要注意的是，你可以自己按照需要调整优化级别参数。         对于已经编译好的对象，可以通过视图ALL_PLSQL_OBJECT_SETTINGS来知道优化的级别以及相关编译信息。        其次，就是选择优化的时机。如果在进行优化前，先考虑一些基本的也许会更好一些。        三、关于避免PLSQL性能问题的指导方针        3.1 避免CPU过载              A.使SQL语句尽可能的高效 o  是否有合适的索引，是否正确的使用了索引 o  是否有最新的统计数据，常常使用DBMS_STATS包来重新统计。 o  使用EXPLAIN PLAN语句或者TKPROF工具分析语句的性能 o  如果需要，就重写sql语句。例如可以使用强制提示 o 考虑用FORALL替代普通的循环来执行INSERT等语句；如果需要轮询查询结果，考虑用BULK  COLLECT语句               B. 使函数更有效率                  如果在SQL查询中使用函数，那么考虑建立一个基于函数的索引；尽可能的把需要使用函数的行的数量                  降低。（由于很多的内容稍后会有比较详细的描述，所以这里不再赘述了)              C. 优化循环              D  尽可能地使用内建的字符串函数来处理字符串，例如搜索等等。              E  对于条件表达式，应该把消耗最小的排在前面，这样有的时候后面复杂的测试可能可以避免。              F  尽量减少数据类型的转换              G  用PLS_INTEGER进行整数运算              H  用BINARY_FLOAT 和BINARY_DOUBLE进行浮点运算        3.2 避免内存过载              A  定义VARCHAR2变量类型的时候，不妨大方一些，这反而可以编译溢出。              B   把相关的过程放在一个包裹中（PACKAGE），可以减少不必要的I/O .              C   把常常用到的包裹钉在共享内存池中,Pin packages in the shared memory pool..。可以用                    dbms_shared_pool 包裹来处理，具体参考<<ORACLE DATABASE PL/SQL PACKAGES AND Types                    reference>>              D  避免编译中出现的警告。有警告提示，则应该按照提示去改正。                     四，记录并跟踪PL/SQL程序          4.1  使用记录API:包裹dbms_profiler.这个包括能够提供收集并保存运行时统计数据的功能。最终的数据被保存在数据库的表格中。          4.2  使用跟踪API：包裹DBMS_TRACE.当让要讲究跟踪的策略。对于这点，我觉得使用PL/SQL DEVELOPER很方便。        五，减少DML语句循环的消耗、在查询中使用BULK SQL语句         PL/SQL引擎把FORALL中的DML语句成批的发送给SQL引擎。SQL引擎通过BULK collect会批量的返回数据。这两种方式都会大大的减少两个引擎之间的通讯成本和管理成本。         SQL语句中PL/SQL变量的绑定有三个途径：         1：内部绑定：通过INSERT 或者UPDATE语句来完成。         2：外部绑定：通过INSERT,UPDATE,DELETE 中的RETURNING语句来完成。         3：定义       ：通过SELECT或者FETCH语句来完成。         此外的一种就是大量绑定（BULK BINDING)          5.1  使用FORALL 语句                5.1.1  FORALL是如何作用到ROLLBACKS（回滚)                          在异常之前的语句都可以提交，异常和异常之后的无法提交。                5.1.2  使用%bulk_rowcount属性来计算FORALL影响到的行                                                5.1.3  使用%BULK_EXCEPTIONS属性来处理FORALL异常                我们需要一个例子，可以从例子中得到许多有益的总结：(其中的employees的格式和数据来源于hr）                                                  DECLARE     TYPE empid_tab IS TABLE OF employees.employee_id%TYPE;     emp_sr empid_tab;     -- create an exception handler for ORA-24381     errors NUMBER;     dml_errors EXCEPTION;     PRAGMA EXCEPTION_INIT(dml_errors, -24381); BEGIN     SELECT employee_id BULK COLLECT INTO emp_sr FROM emp_temp     WHERE hire_date < to_date('1994-12-30','yyyy-mm-dd');     -- add '_SR' to the job_id of the most senior employees     FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS     UPDATE emp_temp SET job_id = job_id || '_SR'     WHERE emp_sr(i) = emp_temp.employee_id;     -- If any errors occurred during the FORALL SAVE EXCEPTIONS,     -- a single exception is raised when the statement completes. EXCEPTION     WHEN dml_errors THEN -- Now we figure out what failed and why.     FOR I IN emp_sr.FIRST..emp_sr.LAST loop     if sql%bulk_rowcount(i)<>0 then          dbms_output.put_line('The  row '||i||' has '||sql%bulk_rowcount(i)||' Updated !');     else       dbms_output.put_line('The  row '||i||' has  no rows Updated !');     end if;     end loop;     errors := SQL%BULK_EXCEPTIONS.COUNT;     DBMS_OUTPUT.PUT_LINE('Number of statements that failed: ' || errors);     FOR i IN 1..errors LOOP               DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '||         'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);         DBMS_OUTPUT.PUT_LINE('Error message is ' ||         SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));     END LOOP;   rollback; END;  The  row 1 has 1 Updated ! The  row 2 has 1 Updated ! The  row 3 has 1 Updated ! The  row 4 has 1 Updated ! The  row 5 has 1 Updated ! The  row 6 has 1 Updated ! The  row 7 has  no rows Updated ! The  row 8 has 1 Updated ! The  row 9 has 1 Updated ! The  row 10 has 1 Updated ! The  row 11 has 1 Updated ! The  row 12 has 1 Updated ! The  row 13 has  no rows Updated ! Number of statements that failed: 2 Error #1 occurred during iteration #7 Error message is ORA-12899: value too large for column  (actual: , maximum: ) Error #2 occurred during iteration #13 Error message is ORA-12899: value too large for column  (actual: , maximum: )                          从例子可以看出，sql%bulk_rowcount表示的是每一个sql语句影响到多少行。元素个数等于循环数.                                             sql%bulk_exceptions则存储了有错误发生的行的信息，实际的bulk_exceptions的元素                                            个数等于错误个数。          5.2  用BULK COLLECT 从查询结果中提取数据到集合中                 关于这个没有什么特别好说明。就是一个returning语句稍微介绍一下：                 DELETE FROM emp_temp WHERE department_id = 30  RETURNING employee_id, last_name BULK                        COLLECT INTO enums, names;                 其次就是可以使用limit语句，以免内存过载.                 第三，FORALL和bulk collect into 可以结合起来，但是SELECT ...BULK COLLECT除外，也就是说只能                            用returning.                          六，编写精壮计算程序          用pls_integer和binary_integer做整数运算，用BINARY_FLOAT和BINARY_DOUBLE做浮点运算。                   七，使用execute immediate 和cursor变量来调整性能          尽量不要用dbms_sql来执行动态sql了（个人任务这个包过时的可能性比较大），现在用execute immedaite能   够很好的执行各种动态的sql，关于这个可以参考我的另外一篇文章:动态执行sql的几个方式               DBMS_SQL的速度和紧凑性上都比execute immediate来的差。                  八,在过程调用中使用NOCOPY编译提示指令         IN/OUT的过程传参，一般情况是按值来进行的，这就是说在调用子过程前，需要一个中间变量，然后把值复制给这个中间变量，临了还要拷贝回来，如果参数的数据许多且结构复杂，那么可能就需要消耗许多的时间。         使用NOCOPY指令，这样可以强制调用过程的时候使用地址传递参数。这样的好处是快，但是也有个坏处，如果子程序异常结束了，那么参数也可能发生了改变。          例子：          DECLARE     TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE;     emp_tab EmpTabTyp := EmpTabTyp(NULL); -- initialize     t1 NUMBER;     t2 NUMBER;     t3 NUMBER;     PROCEDURE get_time (t OUT NUMBER) IS     BEGIN t := DBMS_UTILITY.get_time; END;     PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS     BEGIN NULL; END;     PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS     BEGIN NULL; END; BEGIN     SELECT * INTO emp_tab(1) FROM employees WHERE employee_id = 100;     emp_tab.EXTEND(49999, 1); -- copy element 1 into 2..50000     get_time(t1);     do_nothing1(emp_tab); -- pass IN OUT parameter     get_time(t2);     do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter     get_time(t3);     DBMS_OUTPUT.PUT_LINE('Call Duration (secs)');     DBMS_OUTPUT.PUT_LINE('--------------------');     DBMS_OUTPUT.PUT_LINE('Just IN OUT: ' || TO_CHAR((t2 - t1)/100.0));     DBMS_OUTPUT.PUT_LINE('With NOCOPY: ' || TO_CHAR((t3 - t2))/100.0); END;                  限制性（比较常见的）：           通过DBLINK调用的过程，或者是外部过程例如DLL之类的，那么这个HINT无效。           实参需要一个隐式数据转换的