優化儲存過程的2種方法(DBMS_PROFILER和10046事件)

優化儲存過程的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檔案，從上向下根據相應時間，進行逐一分析