源码-PL/SQL从入门到精通-第十八章-PL/SQL性能优化建议-Part 1

数据库性能调优，在所参与的项目中，这个词向来都是很底层（高大上）的概念，如今，咱也接触到了。嘿嘿。

--第18章 PL/SQL性能优化建议
--代码18.1 使用Nocopy提升性能
DECLARE
   TYPE test_tbl_type IS TABLE OF PLS_INTEGER
      INDEX BY PLS_INTEGER;                        --定义索引表类型
   test_tbl   test_tbl_type;                       --定义索引表类型的变量
   --定义内嵌子程序，在IN OUT参数中使用NOCOPY提示来按引用传递
   PROCEDURE TEST (arg_cnt IN PLS_INTEGER, arg_tbl IN OUT nocopy test_tbl_type)
   IS
   BEGIN
      FOR cnt_test IN test_tbl.FIRST .. arg_tbl.LAST         --依序循环索引表
      LOOP
         arg_tbl (cnt_test) := arg_tbl (cnt_test) + arg_cnt; --为形式参数表赋值
      END LOOP;
   END;
BEGIN
   FOR cnt IN 0 .. 10000                                      
   LOOP
      test_tbl (cnt) := cnt;      --初始一个较大的索引表
   END LOOP;
   FOR cnt IN 0 .. 10000
   LOOP
      TEST (cnt, test_tbl);       --分10000次调用函数，用来测试性能
   END LOOP;
END;
/


--代码18.2 使用批处理一次性获取所有数据
DECLARE
   TYPE emp_tbl_type IS TABLE OF emp%ROWTYPE
      INDEX BY PLS_INTEGER;        --定义索引表类型
   emp_tbl   emp_tbl_type;         --定义索引表变量
   CURSOR emp_cur
   IS
      SELECT *
        FROM emp;                  --定义打开员工资料的游标
BEGIN
   OPEN emp_cur;                   --打开游标
   FETCH emp_cur
   BULK COLLECT INTO emp_tbl;      --批量提取游标数据
   CLOSE emp_cur;                  --关闭游标
END;
/

--配置PROFILER方案以sysdba权限执行以下语句
create user profiler identified by 12345;
grant connect, resource to profiler;
create public synonym plsql_profiler_runs for profiler.plsql_profiler_runs;
create public synonym plsql_profiler_units for profiler.plsql_profiler_units;
create public synonym plsql_profiler_data for profiler.plsql_profiler_data;
create public synonym plsql_profiler_runnumber for profiler.plsql_profiler_runnumber;

--配置PROFILER表
conn profiler/12345
@?/rdbms/admin/proftab.sql
drop table plsql_profiler_data cascade constraints;

--分配public角色
grant select on plsql_profiler_runnumber to public;
grant select,insert,update,delete on plsql_profiler_data to public;
grant select,insert,update,delete on plsql_profiler_units to public;
grant select,insert,update,delete on plsql_profiler_runs to public;

--代码18.3 创建要被测试的过程
CREATE TABLE pro_tst_table (a INT);        --创建测试表
CREATE OR REPLACE PROCEDURE sp_test        --创建测试过程
AS
BEGIN
   FOR i IN 1 .. 10000
   LOOP
      INSERT INTO pro_tst_table            --向表中插入10000行记录
           VALUES (i);
   END LOOP;
   COMMIT;
END;
/

TRUNCATE TABLE pro_tst_table;

--代码18.4 使用DBMS_profiler来测试包
DECLARE
   v_run_number   integer;            --保存PROFILER的运行号码
BEGIN
   --启动PROFILER
   DBMS_PROFILER.start_profiler (run_number => v_run_number);
   --显示当前跟踪的运行序号(后面查询要用)
   DBMS_OUTPUT.put_line ('run_number:' || v_run_number);
   --运行要跟踪的PLSQL
   sp_test;
   --停止profiler
   DBMS_PROFILER.stop_profiler;
END;
/

--查询profiler获取结果
SELECT runid, run_owner, run_date, run_total_time
  FROM plsql_profiler_runs;


--查询单元信息
SELECT unit_number, unit_type, unit_owner, unit_name, unit_timestamp,
       total_time
  FROM plsql_profiler_units
 WHERE runid = 3 AND unit_name = 'SP_TEST';
 
 
--查询存储过程的统计信息
SELECT runid, unit_number, line#, total_occur, total_time, min_time, max_time
  FROM plsql_profiler_data
 WHERE runid = 3 AND unit_number = 2;