使用dbms_profiler包辅助pl/sql优化

d

使用dbms_profiler包辅助pl/sql优化

SQL> conn / as sysdba
Connected.

首先就是创建以下对象：
SQL> @$ORACLE_HOME/rdbms/admin/profload.sql

Package created.

Grant succeeded.

Synonym created.

Library created.

Package body created.

Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/proftab

Table created.

Comment created.

Table created.

Comment created.

Table created.

Comment created.

Sequence created.

创建一个测试用的存储过程（注意我这里是在test用户下创建的这个存储过程）

SQL> create or replace procedure p_test as
2
3
4 begin
5 insert into tt values(1);
6 commit;
7
8 insert into tt(a) select 2 from dual connect by rownum<=10000;
9 commit;
10 end;
11 /

Procedure created

然后这里调用存储过程，并使用dbms_profiler包来收集运行时的详细情况。
SQL>
SQL> set serveroutput on;
SQL> declare
2 run_id number;
3 begin
4 run_id := dbms_profiler.start_profiler(to_char(sysdate,
5 'DD-MM-YYYY HH24:MI:SS'));
6 test.p_test;
7 /* Clear data from memory and store it in profiler tables.*/
8 dbms_profiler.flush_data;
9 dbms_profiler.stop_profiler;
10 end;
11 /

PL/SQL procedure successfully completed.

SQL> SET LINESIZE 200
SQL> SET TRIMOUT ON
SQL>
SQL> COLUMN runid FORMAT 99999
SQL> COLUMN run_comment FORMAT A50
SQL> SELECT runid,
2 run_date,
3 run_comment,
4 run_total_time
5 FROM plsql_profiler_runs
6 ORDER BY runid;

RUNID RUN_DATE RUN_COMMENT RUN_TOTAL_TIME
------ --------- -------------------------------------------------- --------------
1 28-AUG-07 28-08-2007 17:38:17 801432000

SQL>

这里输出存储过程的每一条语句的执行情况。
SQL> COLUMN runid FORMAT 99999
SQL> COLUMN unit_number FORMAT 99999
SQL> COLUMN unit_type FORMAT A20
SQL> COLUMN unit_owner FORMAT A20
SQL> COLUMN TEXT FORMAT A30
SQL> SELECT u.unit_number,
2 u.unit_name,
3 d.line#,
4 d.total_time,
5 d.min_time,
6 d.max_time,
7 s.text
8 FROM plsql_profiler_units u, plsql_profiler_data d, all_source s
9 where u.runid = 1
10 and u.runid = d.runid
11 and u.unit_number = d.unit_number
12 and s.owner = u.unit_owner
13 and s.type = u.unit_type
14 and s.name = u.unit_name
15 and s.line = d.line#
16 and unit_name='P_TEST'
17 ORDER BY u.unit_number, d.line#;

UNIT_NUMBER UNIT_NAME LINE# TOTAL_TIME MIN_TIME MAX_TIME TEXT
----------- -------------------------------- ---------- ---------- ---------- ---------- ------------------------------
3 P_TEST 1 252000 252000 252000 procedure p_test as
3 P_TEST 4 0 0 0 begin
3 P_TEST 5 11915000 11915000 11915000 insert into tt values(1);
3 P_TEST 6 3489000 3489000 3489000 commit;
3 P_TEST 8 74055000 74055000 74055000 insert into tt(a) select 2 fro
m dual connect by rownum<=1000
0;

3 P_TEST 9 9963000 9963000 9963000 commit;
3 P_TEST 10 20000 20000 20000 end;

7 rows selected.

SQL>

dbms_profiler可以借助我们在调优存储过程的过程中起到辅助的作用，
但是需要注意的是，通常存储过程都是因为慢才调，但是在使用这个包之前是
必须执行存储过程。否则收集不到这些统计信息的。

当然如果你要删除这个包。可以参照建包里面的内容，里面说明了删除这个包的过程。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/104152/viewspace-140025/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/104152/viewspace-140025/