locate pl/sql bottleneck

U:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on 星期四 7月 3 13:55:55 2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL> conn sys/oracle as sysdba
已连接。
SQL> create user plsqltuning identified by plsqltuning
  2  /

用户已创建。

SQL> grant create session,dba to plsqltuning
  2  /

授权成功。

#######创建dbms_profiler包#######
SQL> @d:\oracle\product\10.1.0\db_1\rdbms\admin\profload.sql

程序包已创建。

授权成功。

同义词已创建。

库已创建。

程序包体已创建。

Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.

PL/SQL 过程已成功完成。
SQL> conn plsqltuning/plsqltuning
已连接。
SQL> @d:\oracle\product\10.1.0\db_1\rdbms\admin\proftab.sql
drop table plsql_profiler_data cascade constraints
           *
第 1 行出现错误:
ORA-00942: ???????

drop table plsql_profiler_units cascade constraints
           *
第 1 行出现错误:
ORA-00942: ???????

drop table plsql_profiler_runs cascade constraints
           *
第 1 行出现错误:
ORA-00942: ???????

drop sequence plsql_profiler_runnumber
              *
第 1 行出现错误:
ORA-02289: ?????

 

表已创建。

注释已创建。

表已创建。

注释已创建。

表已创建。

注释已创建。

序列已创建。

SQL> conn sys/oracle as sysdba
已连接。
SQL> grant select on scott.emp to plsqltuning
  2  /

授权成功。
SQL> grant execute on dbms_lock to plsqltuning
  2  /

授权成功。

SQL> conn plsqltuning/plsqltuning
已连接。
SQL> CREATE OR REPLACE PROCEDURE profreset IS
  2
  3  BEGIN
  4    DELETE FROM plsql_profiler_data;
  5    DELETE FROM plsql_profiler_units;
  6    DELETE FROM plsql_profiler_runs;
  7    COMMIT;
  8  END profreset;
  9  /

过程已创建。
SQL> create or replace procedure test_profiler is
  2    emp_rec scott.emp%rowtype;
  3    cursor emp_cur is
  4      select * from scott.emp;
  5  begin
  6    for i in 1 .. 1000 loop
  7      dbms_output.put_line(i * 1000);
  8      sys.dbms_lock.sleep(seconds => 0.01);
  9    end loop;
 10    for emp_rec in emp_cur loop
 11      dbms_output.put_line(emp_rec.ename);
 12    end loop;
 13  end;
 14  /

过程已创建。

SQL> exec profreset

PL/SQL 过程已成功完成。

SQL> exec dbms_profiler.start_profiler('test_profiler');

PL/SQL 过程已成功完成。

SQL> exec test_profiler;

PL/SQL 过程已成功完成。

SQL> exec dbms_profiler.stop_profiler;

PL/SQL 过程已成功完成。

########get result####################################
SQL> column line format 9999
SQL> column occur format 9999
SQL> column sec format 9999.9999
SQL> column text format a60
SQL> select s.line        Line,
  2         p.total_occur Occur,
  3         p.total_time  Sec,
  4         s.text        Text
  5    from all_source s,
  6         (select u.unit_owner,
  7                 u.unit_name,
  8                 u.unit_type,
  9                 d.line#,
 10                 d.total_occur,
 11                 d.total_time / 100000000 total_time
 12            from plsql_profiler_data d, plsql_profiler_units u
 13           where u.runid = &runid
 14             and u.runid = d.runid
 15             and u.unit_number = d.unit_number) p
 16   where s.owner = p.unit_owner(+)
 17     and s.name = p.unit_name(+)
 18     and s.line = p.line#(+)
 19     and s.name = upper('&name')
 20     and s.owner = upper('&owner')
 21   order by s.line;
输入 runid 的值:  2
原值   13:          where u.runid = &runid
新值   13:          where u.runid = 2
输入 name 的值:  test_profiler
原值   19:    and s.name = upper('&name')
新值   19:    and s.name = upper('test_profiler')
输入 owner 的值:  plsqltuning
原值   20:    and s.owner = upper('&owner')
新值   20:    and s.owner = upper('plsqltuning')

 LINE OCCUR        SEC TEXT
----- ----- ---------- ----------------------------------------------------------
    1     1      .2021 procedure test_profiler is
    2                    emp_rec scott.emp%rowtype;
    3     0      .0074   cursor emp_cur is
    4     1      .7324     select * from scott.emp;
    5                  begin
    6  1001     2.5108   for i in 1 .. 1000 loop
    7  1000    34.7284     dbms_output.put_line(i * 1000);
    8  1000     4.9940     sys.dbms_lock.sleep(seconds => 0.01);
    9                    end loop;
   10     3     2.0521   for emp_rec in emp_cur loop
   11    14      .0712     dbms_output.put_line(emp_rec.ename);
   12                    end loop;
   13     1      .0381 end;

已选择13行。