优化存储过程的2种方法(DBMS_PROFILER和10046事件)

第一种方法 ：DBMS_PROFILER

11.2.0.3 rac on redhat

1、sys install the DBMS_PROFILER package

sys@RACDOU> @?/rdbms/admin/profload.sql

....

SYS.DBMS_PROFILER successfully loaded.

2、test doudou

doudou@RACDOU>  @?/rdbms/admin/proftab.sql

....

3、doudou create test table

doudou@RACDOU> create table t1 (col1 varchar2(30), col2 varchar2(30));

Table created.

4、create test different procedures

create or replace procedure literals

is

vNumber number;

begin

for i in 1..100000 loop

vNumber := dbms_random.random;              

execute immediate

'insert into t1 values ('||vNumber||','||vNumber||')';

end loop;

end;

/

create or replace procedure binds

is

vNumber number;

begin

for i in 1..100000 loop

vNumber := dbms_random.random;

insert into t1 values (vNumber,vNumber);

end loop;

end;

/

5、dbms_profiler information collection

execute dbms_profiler.stop_profiler;

exec literals;

execute dbms_profiler.stop_profiler;

execute dbms_profiler.stop_profiler;

exec binds;

execute dbms_profiler.stop_profiler;

6、summary of all dbms_profiler and query runid

set lines 10000

column run_owner format a30

column run_comment format a10

select runid,

run_owner,

run_date,

run_total_time/1000000000 run_total_time,

run_comment

from plsql_profiler_runs;

7、 details of dbms_profiler results

column text format a55

column total_time format 99.9

column min_time format 99.9

column max_time format 99.9

select s.text ,

        p.total_occur ,

        p.total_time/1000000000 total_time,

        p.min_time/1000000000 min_time,

        p.max_time/1000000000 max_time

from plsql_profiler_data p, user_source s, plsql_profiler_runs r

where p.line# = s.line

and   p.runid = r.runid

and   r.run_comment = 'literals'

and   s.name ='LITERALS';

select s.text ,

         p.total_occur ,

         p.total_time/1000000000 total_time,

        p.min_time/1000000000 min_time,

        p.max_time/1000000000 max_time

from plsql_profiler_data p, user_source s, plsql_profiler_runs r

where p.line# = s.line

and   p.runid = r.runid

and   r.run_comment = 'binds'

and   s.name ='BINDS';

Transshipment:

http://oracleflash.com/44/Write-fast-and-efficient-PLSQL---DBMS_PROFILER.html

TEXT                                                    TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME

------------------------------------------------------- ----------- ---------- -------- --------

procedure literals                                                1         .0       .0       .0

procedure literals                                                2         .0       .0       .0

procedure literals                                                3         .0       .0       .0

procedure literals                                                0         .0       .0       .0

 for i in 1..100000 loop                                     100001         .4       .0       .0

   vNumber := dbms_random.random;                            100000        1.1       .0       .0

    execute immediate                                        100000       67.0       .0       .1

  end;                                                            1         .0       .0       .0

procedure literals                                                2         .0       .0       .0

procedure literals                                                2         .0       .0       .0

TEXT                                                    TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME

------------------------------------------------------- ----------- ---------- -------- --------

procedure binds                                                   1         .0       .0       .0

procedure binds                                                   2         .0       .0       .0

procedure binds                                                   3         .0       .0       .0

procedure binds                                                   0         .0       .0       .0

 for i in 1..100000 loop                                     100001         .4       .0       .0

  vNumber := dbms_random.random;                             100000        1.0       .0       .0

   insert into t1 values (vNumber,vNumber);                  100000        7.3       .0       .0

 end;                                                             1         .0       .0       .0

procedure binds                                                   2         .0       .0       .0

procedure binds                                                   2         .0       .0       .0

第二种方法 ： 10046事件

开启10046跟踪

alter session set events '10046 trace name context forever,level 12 ';

运行存储过程

关闭10046跟踪

alter session set events '10046 trace name context off';

tkprof doudou_ora_31349.trc /home/oracle/proc_10046.txt sys=no sort=prsela,exeela,fchela

分析proc_10046.txt文件，从上向下根据相应时间，进行逐一分析