ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle数据库中使用DBMS_PROFILER进行调优

Oracle数据库中使用DBMS_PROFILER进行调优

原创 Linux操作系统 作者:syzxlyx_cu 时间:2009-09-28 11:03:47 0 删除 编辑

Oracle数据库中使用DBMS_PROFILER进行调优

在进行数据库调优时,代码的性能瓶颈至关重要,Oracle数据库提供的DBMS_PROFILER包可以特别方便的发现瓶颈的所在之处。


DBMS_PROFILER在使用之前可能需要安装,具体安装步骤如下:

◆1:conn sys/oracle as sysdba
执行 @$ORACLE_HOME/rdbms/admin/profload.sql
◆2:创建一个用于存放跟踪信息的schema
CREATE USER profiler IDENTIFIED BY profiler DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT connect 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;
grant resource to profiler;
◆3:使用刚刚创建的profiler用户执行如下脚本或代码:
conn profiler/profiler
@$ORACLE_HOME/rdbms/admin/proftab.sql
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;

 

安装成功后测试:

执行
create table a (a varhcar (200));
BEGIN
DBMS_PROFILER.START_PROFILER('123');
FOR I IN 1 .. 1000 LOOP
INSERT INTO A VALUES (I || '');
END LOOP;
COMMIT;
DBMS_PROFILER.STOP_PROFILER();
END;
/
执行后使用下面的代码检查:

SELECT C.LINE#, C.TOTAL_OCCUR, C.TOTAL_TIME, C.MIN_TIME, C.MAX_TIME
FROM PLSQL_PROFILER_RUNS A, PLSQL_PROFILER_UNITS B, PLSQL_PROFILER_DATA C
WHERE A.RUN_COMMENT = '123'
AND B.UNIT_OWNER = ''
AND A.RUNID = B.RUNID
AND A.RUNID = C.RUNID
AND B.UNIT_NUMBER = C.UNIT_NUMBER;

注意:每次DBMS_PROFILER.START_PROFILER的输入参数需要改变,否则便不能分别运行后的运行结果了。

除此之外B.UNIT_OWNER =''中的约束值如果是在package里面需要是包名,如果是procedure则是procedure的名字。实在在不知道什么名字时可以在PLSQL_PROFILER_UNITS中查一下。
     LINE# TOTAL_OCCUR TOTAL_TIME   MIN_TIME   MAX_TIME
---------- ----------- ---------- ---------- ----------
         1           0          0          0          0
         2           0          0          0          0
         3        1001    1842000       1000      35000
         4        1000   38527000      32000    3717000
         6           1     250000     250000     250000
         7           1       7000       7000       7000
         8           0          0          0          0

7 rows selected.
create or replace procedure TEST_PROFILER AS
BEGIN
DBMS_PROFILER.START_PROFILER('123');
FOR I IN 1 .. 1000 LOOP
INSERT INTO A VALUES (I || '');
END LOOP;
COMMIT;
DBMS_PROFILER.STOP_PROFILER();
END;
col text format a40
SELECT C.LINE#, D.TEXT,C.TOTAL_OCCUR, C.TOTAL_TIME, C.MIN_TIME, C.MAX_TIME
FROM PLSQL_PROFILER_RUNS A, PLSQL_PROFILER_UNITS B, PLSQL_PROFILER_DATA C,user_source D
WHERE A.RUN_COMMENT = '123'
AND B.UNIT_OWNER = 'PROFILER'
AND A.RUNID = B.RUNID
AND A.RUNID = C.RUNID
AND B.UNIT_NUMBER = C.UNIT_NUMBER
and d.name=B.UNIT_NAME
and d.line=c.line#
and B.UNIT_NAME='TEST_PROFILER';


    LINE# TEXT                                     TOTAL_OCCUR TOTAL_TIME   MIN_TIME   MAX_TIME
---------- ---------------------------------------- ----------- ---------- ---------- ----------
         1 procedure TESTPRROFILER AS                         0          0          0          0
         3 DBMS_PROFILER.START_PROFILER('123');               0          0          0          0
         4 FOR I IN 1 .. 1000 LOOP                         1001    1810000       1000      17000
         5 INSERT INTO A VALUES (I || '');                 1000   36421000      32000     897000
         7 COMMIT;                                            1     224000     224000     224000
         8 DBMS_PROFILER.STOP_PROFILER();                     1       7000       7000       7000
         9 END;                                               0          0          0          0
conn lt/lt
begin
DBMS_PROFILER.START_PROFILER('123');
inserttab2(1000,100);
DBMS_PROFILER.STOP_PROFILER();
end;
/
col text format a50
SELECT C.LINE#, D.TEXT,C.TOTAL_OCCUR, C.TOTAL_TIME, C.MIN_TIME, C.MAX_TIME
FROM PLSQL_PROFILER_RUNS A, PLSQL_PROFILER_UNITS B, PLSQL_PROFILER_DATA C,user_source D
WHERE A.RUN_COMMENT = '123'
AND B.UNIT_OWNER = 'LT'
AND A.RUNID = B.RUNID
AND A.RUNID = C.RUNID
AND B.UNIT_NUMBER = C.UNIT_NUMBER
and d.name=B.UNIT_NAME
and d.line=c.line#
and B.UNIT_NAME='INSERTTAB2';

     LINE# TEXT                                               TOTAL_OCCUR TOTAL_TIME   MIN_TIME   MAX_TIME
---------- -------------------------------------------------- ----------- ---------- ---------- ----------
         1 procedure inserttab2(rowc NUMBER,colc NUMBER)                0       4000       4000       4000
         3 buf varchar(24000):='insert into tcol'||to_char(co           1      28000      28000      28000
           lc)||' select level,';
         5  for i in 1..colc-1                                        100     178000       1000      11000
         7    buf:=buf||'mod(level+'||to_char(i)||',1e10),';           99     253000       1000      18000
         9  buf:=buf||'mod(level+'||to_char(colc)||',1e10) fr           1       3000       3000       3000
           om dual connect by level<='||to_char(rowc);
        10  execute immediate buf;                                      1  107750000  107750000  107750000
        11 end;                                                         1       7000       7000       7000
begin
DBMS_PROFILER.START_PROFILER('123');
comp_join2(3,2);
DBMS_PROFILER.STOP_PROFILER();
end;
/
col text format a50
SELECT C.LINE#, D.TEXT,C.TOTAL_OCCUR, C.TOTAL_TIME, C.MIN_TIME, C.MAX_TIME
FROM PLSQL_PROFILER_RUNS A, PLSQL_PROFILER_UNITS B, PLSQL_PROFILER_DATA C,user_source D
WHERE A.RUN_COMMENT = '123'
AND B.UNIT_OWNER = 'LT'
AND A.RUNID = B.RUNID
AND A.RUNID = C.RUNID
AND B.UNIT_NUMBER = C.UNIT_NUMBER
and d.name=B.UNIT_NAME
and d.line=c.line#
and B.UNIT_NAME='COMP_JOIN2';
     LINE# TEXT                                               TOTAL_OCCUR TOTAL_TIME   MIN_TIME   MAX_TIME
---------- -------------------------------------------------- ----------- ---------- ---------- ----------
         1 procedure                                                    0      14000      14000      14000
         3 buf varchar(100):='';                                        1       3000       3000       3000
         9 start_time := DBMS_UTILITY.get_time;                         1     520000     520000     520000
        10 execute immediate 'truncate table tcol50';                   1    2462000    2462000    2462000
        11 execute immediate 'drop index tcol50_idx';                   1    3016000    3016000    3016000
        12 execute immediate 'drop table tcoll';                        1     729000     729000     729000
        13 execute immediate 'drop table tcolr';                        1     770000     770000     770000
        14 inserttab2(10000*tm,50);                                     1      68000      68000      68000
        15 commit;                                                      1    2597000    2597000    2597000
        16 execute immediate 'create table tcoll as select x1           1    7254000    7254000    7254000
           ,v1,v2,v3,v4,v5,v6,v7,v8,v9,v10,v11,v12,v13,v14,v1
           5,v16,v17,v18,v19,v20,v21,v22,v23,v24,v25 from tco
           l50';
        17 execute immediate 'create table tcolr as select x1           1    5343000    5343000    5343000
           ,v25,v26,v27,v28,v29,v30,v31,v32,v33,v34,v35,v36,v
           37,v38,v39,v40,v41,v42,v43,v44,v45,v46,v47,v48,v49
           ,v50 from tcol50';
        18 execute immediate 'create index tcoll_idx on tcoll           1    5400000    5400000    5400000
           (x1)';
        19 execute immediate 'create index tcolr_idx on tcolr           1    4491000    4491000    4491000
           (x1)';
        20 execute immediate 'create index tcol50_idx on tcol           1    3840000    3840000    3840000
           50(x1)';
        21 end_time := DBMS_UTILITY.get_time;                           1       8000       8000       8000
        22 DBMS_OUTPUT.PUT_LINE('init '||to_char(end_time-sta           1     585000     585000     585000
           rt_time));
        23 for i in 1..tm2                                              3       6000       2000       2000
        25 start_time := DBMS_UTILITY.get_time;                         2       4000       2000       2000
        26 select sum(v1)||sum(v20)||sum(v30)||sum(v40)||sum(           2   39127000   17665000   21462000
           v50) into buf from tcol50;
        27 end_time := DBMS_UTILITY.get_time;                           2      16000       8000       8000
        28 DBMS_OUTPUT.PUT_LINE('single '||to_char(end_time-s           2      39000      19000      20000
           tart_time));
        29 start_time := DBMS_UTILITY.get_time;                         2       4000       2000       2000
        30 select sum(v1)||sum(v20)||sum(v30)||sum(v40)||sum(           2  170020000   73297000   96723000
           v50) into buf from tcoll a,tcolr b where a.x1=b.x1
           ;
        31 end_time := DBMS_UTILITY.get_time;                           2      16000       8000       8000
        32 DBMS_OUTPUT.PUT_LINE('two join '||to_char(end_time           2      38000      19000      19000
           -start_time));
        33 start_time := DBMS_UTILITY.get_time;                         2       3000       1000       2000
        34 select /*+ rule */ sum(v1)||sum(v20)||sum(v30)||su           2  190600000   87969000  102631000
           m(v40)||sum(v50) into buf from tcoll a,tcolr b whe
           re a.x1=b.x1;
        35 end_time := DBMS_UTILITY.get_time;                           2      17000       8000       9000
        36 DBMS_OUTPUT.PUT_LINE('two join '||to_char(end_time           2      38000      19000      19000
           -start_time));
        38 end;                                                         1       4000       4000       4000
oracle 10.2.0.1的输出与11g不同,时间后3个数不全为0

 

     LINE# TEXT                                               TOTAL_OCCUR TOTAL_TIME   MIN_TIME   MAX_TIME
---------- -------------------------------------------------- ----------- ---------- ---------- ----------
         1 procedure inserttab2(rowc NUMBER,colc NUMBER)                0       4677       4677       4677
         3 buf varchar(24000):='insert into tcol'||to_char(co           1      10540      10540      10540
           lc)||' select level,';

         5 for i in 1..colc-1                                         100      77774        712       5506
         7    buf:=buf||'level+'||to_char(i)||',';                     99     165151       1285       8028
         9 buf:=buf||'level+'||to_char(colc)||' from dual con           1       3703       3703       3703
           nect by level<='||to_char(rowc);

        10 execute immediate buf;                                       1   59824304   59824304   59824304
        11 end;                                                         1       3144       3144       3144
dbms_profiler用来测试PL/SQL代码非常有用,比如找出哪一段代码比较耗时,也可以用来比较不同算法之间的差异。
1.配置dbms_profiler的运行环境
SQL> conn / as sysdba
已连接。
SQL> @?\rdbms\admin\profload.sql
程序包已创建。

授权成功。

同义词已创建。

库已创建。

程序包主体已创建。
Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.
PL/SQL 过程已成功完成。
SQL> @?\rdbms\admin\proftab.sql
drop table plsql_profiler_data cascade constraints
           *
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在

drop table plsql_profiler_units cascade constraints
           *
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在

drop table plsql_profiler_runs cascade constraints
           *
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在

drop sequence plsql_profiler_runnumber
              *
ERROR 位于第 1 行:
ORA-02289: 序列(号)不存在

表已创建。

注释已创建。

表已创建。

注释已创建。

表已创建。

注释已创建。

序列已创建。
SQL> @?\plsql\demo\profrep.sql
视图已建立。

视图已建立。

视图已建立。

视图已建立。

程序包已创建。
没有错误。
程序包主体已创建。
没有错误。
SQL>
2.使用dbms_profiler
e.g.:
begin
    dbms_profiler.start_profiler( 'mod' );
    for i in 1 .. 500000
    loop
        cnt := cnt + 1;
        if ( mod(cnt,1000) = 0 )
        then
            commit;
        end if;
    end loop;
    dbms_profiler.stop_profiler;
end;
/
begin
    dbms_profiler.start_profiler( 'no mod' );
    for i in 1 .. 500000
    loop
        cnt := cnt + 1;
        if ( cnt = 1000 )
        then
            commit;
            cnt := 0;
        end if;
    end loop;
    dbms_profiler.stop_profiler;
end;
/
然后执行:
SQL> conn scott/tiger@db01
已连接。
SQL> exec no_mod
PL/SQL 过程已成功完成。
SQL> exec do_mod
PL/SQL 过程已成功完成。
3.生成Report
Oracle自带的profsum.sql比较耗时,可以使用tom修改过的profsum.sql脚本来生成Report。
Oracle自带的profsum.sql脚本产生的Report比Tom的profsum.sql脚本所产生的Report详细,具体的结果请查看示例。
每次执行profsum.sql脚本之前,需要把旧的数据删除:
truncate table plsql_profiler_data;
truncate table plsql_profiler_units;
truncate table plsql_profiler_runs;
Oracle自带的profsum.sql比较耗时


profsum.sql脚本


Rem
Rem $Header: profsum.sql 13-apr-98.19:17:28 astocks Exp $
Rem
Rem profsum.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      profsum.sql
Rem
Rem    DESCRIPTION
Rem      Example of usage of profiler data. A miscellany of potentially
Rem      useful adhoc queries, and calls to the prof_report_utilities
Rem      package.
Rem
Rem    NOTES
Rem      You must connect before running this script.  Calls to the long
Rem      reports can be commented out.
Rem
/* Script. to dump lots of reports from ordts long performance run */
set echo off
set linesize 5000
set trimspool on
set serveroutput on
set termout off
column owner format a11
column unit_name format a14
column text format a21 word_wrapped
column runid format 999999
column secs  format 99999.99
column hsecs format 99999.99
column grand_total  format 9999.99
column run_comment format a11 word_wrapped
column line# format 99999
column pct format 9999.9
column unit_owner format a11
spool profsum.out
/* Clean out rollup results, and recreate */
update plsql_profiler_units set total_time = 0;
execute prof_report_utilities.rollup_all_runs;
/* Total time */
select
    to_char(grand_total/1000000000, '999999.99') as grand_total
  from plsql_profiler_grand_total;
/* Total time spent on each run */
select
    runid, substr(run_comment,1, 30) as run_comment,
    run_total_time/1000000000 as seconds
  from plsql_profiler_runs
  where run_total_time > 0
  order by runid asc;

/* Percentage of time in each module, for each run separately */
select
    p1.runid,
    substr(p2.run_comment, 1, 20) as run_comment,
    p1.unit_owner,
    decode(p1.unit_name, '', '', substr(p1.unit_name,1, 20)) as unit_name,
    TO_CHAR(p1.total_time/1000000000, '99999.99') as seconds,
    TO_CHAR(100*p1.total_time/p2.run_total_time, '999.9') as percentage
  from plsql_profiler_units p1, plsql_profiler_runs p2
  where
    p1.runid=p2.runid and
    p1.total_time > 0 and p2.run_total_time > 0 and 
    (p1.total_time/p2.run_total_time)  >= .01
  order by p1.runid asc, p1.total_time desc;
/* Percentage of time in each module, summarized across runs */
select p1.unit_owner,
    decode(p1.unit_name, '', '', substr(p1.unit_name,1, 25)) as unit_name,
    TO_CHAR(p1.total_time/1000000000, '99999.99') as seconds,
    TO_CHAR(100*p1.total_time/p2.grand_total, '99999.99') as percentage
  from
    plsql_profiler_units_cross_run p1,
    plsql_profiler_grand_total p2
  order by p1.total_time DESC;

Rem Analyze min/max/average time anomalies -
Rem
Rem This report finds lines where the minimum time for the query is either
Rem greater than the average time (which indicates a data collection
Rem problem), or significantly less than the average (which indicates high
Rem variablility in the timing of a single line). It can take a long time to
Rem run, so it should only be used if the data is suspected to have problems.
Rem Normally, this query should produce no output.
Rem
select
    p1.runid, p2.unit_owner, substr(p2.unit_name,1,25),
    to_char(p1.line#,'9999') as line,
    p1.total_occur,
    to_char(p1.total_time/1000,'9999999.99') as microS,
    to_char(p1.total_time/(1000*p1.total_occur),'9999999.99') as "Ave Micro",
    to_char(p1.min_time/1000,'9999999.99') as min_time,
    to_char(p1.max_time/1000,'999999999.99') as max_time,
    to_char(p1.max_time/p1.min_time,'999999.99') as "Max/min",
    to_char(p1.total_time/(p1.total_occur*p1.min_time),'99999.99')as "Ave/min",
    p3.text
  from
    plsql_profiler_data p1,
    plsql_profiler_units p2,
    all_source p3
  where
    ((p1.total_time > 1000*(p1.total_occur*p1.min_time)) OR
     (p1.total_time < (p1.total_occur*p1.min_time))) AND
    p1.runID=p2.runID and p2.unit_number = p1.unit_number AND
    ((p3.type='PACKAGE BODY') OR (p3.type = 'PROCEDURE')) and
    p3.line = p1.line# and
    (p3.owner = p2.unit_owner)   AND
    (p3.name=p2.unit_name)
  order by "Ave/min" asc;
/* Lines taking more than 1% of the total time, each run separate */
select
    p1.runid as runid,
    to_char(p1.total_time/1000000000, '99999.9') as seconds,
    substr(p2.unit_owner, 1, 20) as owner,
    decode(p2.unit_name, '', '', substr(p2.unit_name,1, 20)) as unit_name,
    p1.line#,
    p3.text
  from
    plsql_profiler_data p1,
    plsql_profiler_units p2,
   all_source p3, plsql_profiler_grand_total p4
  where
    (p1.total_time >= p4.grand_total/100) AND
    p1.runID = p2.runid and
    p2.unit_number=p1.unit_number and
    p3.type='PACKAGE BODY' and
    p3.owner = p2.unit_owner and
    p3.line = p1.line# and
    p3.name=p2.unit_name
  order by p1.total_time desc;
/* Most popular lines (more than 1%), summarize across all runs */
select
    to_char(p1.total_time/1000000000, '99999.9') as seconds,
    substr(p1.unit_owner, 1, 20) as unit_owner,
    decode(p1.unit_name, '', '', substr(p1.unit_name,1, 20)) as unit_name,
    p1.line#,
    p3.text
  from
    plsql_profiler_lines_cross_run p1,
    all_source p3,
    plsql_profiler_grand_total p4
  where
    (p1.total_time >= p4.grand_total/100) AND
    ((p3.type='PACKAGE BODY') OR (p3.type = 'PROCEDURE')) and
    (p3.line = p1.line#) and
    (p3.owner = p1.unit_owner) AND
    (p3.name = p1.unit_name)
  order by p1.total_time desc;
Rem Get coverage information
execute prof_report_utilities.rollup_all_runs;
Rem Number of lines actually executed in different units (by unit_name)
select
    p1.unit_owner, p1.unit_name,
    count(p1.line#) as lines_executed
  from plsql_profiler_lines_cross_run p1
  where
    (p1.unit_type = 'PACKAGE BODY' OR
    p1.unit_type = 'TYPE BODY')  AND
    p1.total_occur != 0
  group by p1.unit_owner, p1.unit_name;

Rem Total number of lines in different units ( by unit_name)
select
    p1.unit_owner, p1.unit_name,
    count(p1.line#) as lines_present
  from plsql_profiler_lines_cross_run p1
  where
    (p1.unit_type = 'PACKAGE BODY' OR
    p1.unit_type = 'TYPE BODY')
    group by p1.unit_owner, p1.unit_name;

Rem Number of lines actually executed for all units
select
    count(p1.line#) as lines_executed
  from plsql_profiler_lines_cross_run p1
  where
    (p1.unit_type = 'PACKAGE BODY' OR
     p1.unit_type = 'TYPE BODY')  AND
    p1.total_occur > 0;


Rem Total number of lines in all units
select
    count(p1.line#) as lines_present
  from plsql_profiler_lines_cross_run p1
  where
    (p1.unit_type = 'PACKAGE BODY' OR
     p1.unit_type = 'TYPE BODY')  ;
/* Full reports */
execute prof_report_utilities.Print_Detailed_Report;
execute prof_report_utilities.Print_Summarized_Report;
spool off
set termout on
edit profsum.out
set linesize 131
om的profsum.sql脚本

 

set echo off
set linesize 5000
set trimspool on
set serveroutput on
set termout off
column owner format a11
column unit_name format a14
column text format a21 word_wrapped
column runid format 999999
column secs  format 99999.99
column hsecs format 99999.99
column grand_total  format 9999.99
column run_comment format a11 word_wrapped
column line# format 99999
column pct format 9999.9
column unit_owner format a11
spool profsum.out
/* Clean out rollup results, and recreate */
update plsql_profiler_units set total_time = 0;
execute prof_report_utilities.rollup_all_runs;
prompt =
prompt =
prompt ====================
prompt Total time
select grand_total/1000000000 as grand_total
  from plsql_profiler_grand_total;
prompt =
prompt =
prompt ====================
prompt Total time spent on each run
select runid,
       substr(run_comment,1, 30) as run_comment,
       run_total_time/1000000000 as secs
  from (select a.runid, sum(a.total_time)  run_total_time, b.run_comment
          from plsql_profiler_units a, plsql_profiler_runs b
         where a.runid = b.runid group by a.runid, b.run_comment )
where run_total_time > 0
order by runid asc;

prompt =
prompt =
prompt ====================
prompt Percentage of time in each module, for each run separately
select p1.runid,
       substr(p2.run_comment, 1, 20) as run_comment,
       p1.unit_owner,
       decode(p1.unit_name, '', '',
                    substr(p1.unit_name,1, 20)) as unit_name,
       p1.total_time/1000000000 as secs,
       TO_CHAR(100*p1.total_time/p2.run_total_time, '999.9') as percentage
  from plsql_profiler_units p1,
       (select a.runid, sum(a.total_time)  run_total_time, b.run_comment
          from plsql_profiler_units a, plsql_profiler_runs b
         where a.runid = b.runid group by a.runid, b.run_comment ) p2
where p1.runid=p2.runid
   and p1.total_time > 0
   and p2.run_total_time > 0
   and  (p1.total_time/p2.run_total_time)  >= .01
order by p1.runid asc, p1.total_time desc;
column secs form. 99999.99
prompt =
prompt =
prompt ====================
prompt Percentage of time in each module, summarized across runs
select p1.unit_owner,
       decode(p1.unit_name, '', '', substr(p1.unit_name,1, 25)) as
unit_name,
       p1.total_time/1000000000 as secs,
       TO_CHAR(100*p1.total_time/p2.grand_total, '99999.99') as percentage
  from plsql_profiler_units_cross_run p1,
       plsql_profiler_grand_total p2
order by p1.total_time DESC;

prompt =
prompt =
prompt ====================
prompt Lines taking more than 1% of the total time, each run separate
select p1.runid as runid,
       p1.total_time/10000000 as Hsecs,
        p1.total_time/p4.grand_total*100 as pct,
       substr(p2.unit_owner, 1, 20) as owner,
       decode(p2.unit_name, '', '', substr(p2.unit_name,1, 20)) as
unit_name,
       p1.line#,
       ( select p3.text
           from all_source p3
          where p3.owner = p2.unit_owner and
                p3.line = p1.line# and
                p3.name=p2.unit_name and
                p3.type not in ( 'PACKAGE', 'TYPE' )) text
  from plsql_profiler_data p1,
       plsql_profiler_units p2,
       plsql_profiler_grand_total p4
where (p1.total_time >= p4.grand_total/100)
   AND p1.runID = p2.runid
   and p2.unit_number=p1.unit_number
order by p1.total_time desc;
prompt =
prompt =
prompt ====================
prompt Most popular lines (more than 1%), summarize across all runs
select p1.total_time/10000000 as hsecs,
        p1.total_time/p4.grand_total*100 as pct,
       substr(p1.unit_owner, 1, 20) as unit_owner,
       decode(p1.unit_name, '', '',
                 substr(p1.unit_name,1, 20)) as unit_name,
       p1.line#,
       ( select p3.text from all_source p3
          where (p3.line = p1.line#) and
                (p3.owner = p1.unit_owner) AND
                (p3.name = p1.unit_name) and
                (p3.type not in ( 'PACKAGE', 'TYPE' ) ) ) text
  from  plsql_profiler_lines_cross_run p1,
        plsql_profiler_grand_total p4
where (p1.total_time >= p4.grand_total/100)
order by p1.total_time desc;
execute prof_report_utilities.rollup_all_runs;
prompt =
prompt =
prompt ====================
prompt  Number of lines actually executed in different units (by unit_name)
select p1.unit_owner,
       p1.unit_name,
       count( decode( p1.total_occur, 0, null, 0))  as lines_executed ,
       count(p1.line#) as lines_present,
       count( decode( p1.total_occur, 0, null, 0))/count(p1.line#) *100
                                       as pct
  from plsql_profiler_lines_cross_run p1
where (p1.unit_type in ( 'PACKAGE BODY', 'TYPE BODY',
                          'PROCEDURE', 'FUNCTION' )  )
group by p1.unit_owner, p1.unit_name;

prompt =
prompt =
prompt ====================
prompt  Number of lines actually executed for all units
select count(p1.line#) as lines_executed
  from plsql_profiler_lines_cross_run p1
where (p1.unit_type in ( 'PACKAGE BODY', 'TYPE BODY',
                          'PROCEDURE', 'FUNCTION' )  )
    AND p1.total_occur > 0;

prompt =
prompt =
prompt ====================
prompt  Total number of lines in all units
select count(p1.line#) as lines_present
  from plsql_profiler_lines_cross_run p1
where (p1.unit_type in ( 'PACKAGE BODY', 'TYPE BODY',
                          'PROCEDURE', 'FUNCTION' )  );
spool off
set termout on
edit profsum.out
set linesize 131
Oracle自带的profsum.sql产生的结果示例

 


已更新2行。

PL/SQL 过程已成功完成。

GRAND_TOTA
----------
   1560.57

  RUNID RUN_COMMENT    SECONDS
------- ----------- ----------
      1 no mod      643.964701
      2 mod          1158.1919

  RUNID RUN_COMMENT UNIT_OWNER  UNIT_NAME      SECONDS   PERCEN
------- ----------- ----------- -------------- --------- ------
      1 no mod      SCOTT       NO_MOD            506.44   78.6
      2 mod         SCOTT       DO_MOD           1054.13   91.0

UNIT_OWNER  UNIT_NAME      SECONDS   PERCENTAG
----------- -------------- --------- ---------
SCOTT       DO_MOD           1054.13     67.55
SCOTT       NO_MOD            506.44     32.45

未选定行

未选定行

SECONDS  UNIT_OWNER  UNIT_NAME       LINE# TEXT
-------- ----------- -------------- ------ ---------------------
   709.7 SCOTT       DO_MOD              8 if ( mod(cnt,1000) =
                                           0 )
   171.4 SCOTT       NO_MOD              8 if ( cnt = 1000 )
   160.3 SCOTT       NO_MOD              7 cnt := cnt + 1;
   158.5 SCOTT       DO_MOD              5 for i in 1 .. 500000
   155.0 SCOTT       NO_MOD              5 for i in 1 .. 500000
   147.3 SCOTT       DO_MOD              7 cnt := cnt + 1;
    38.6 SCOTT       DO_MOD             10 commit;
    19.4 SCOTT       NO_MOD             10 commit;
已选择8行。

PL/SQL 过程已成功完成。

未选定行

未选定行

LINES_EXECUTED
--------------
             0

LINES_PRESENT
-------------
            0
=================================trace info=================================
===========================Results for run #1 made on 24-7月 -06 17:04:39 =========================
(no mod) Run total time:    643.96 seconds
Unit #1: SCOTT.NO_MOD - Total time:    506.44 seconds
1                                                procedure no_mod as
2           0   0                                    cnt number := 0;
3                                                begin
4           0   0                                    dbms_profiler.start_profiler( 'no mod' );
5     500,001   154.96648  .00030993                 for i in 1 .. 500000
6                                                    loop
7     500,000   160.32336  .00032064                     cnt := cnt + 1;
8     500,000   171.38172  .00034276                     if ( cnt = 1000 )
9                                                        then
10         500   19.431397  .03886279                         commit;
11         500   .33597007  .00067194                         cnt := 0;
12                                                        end if;
13                                                    end loop;
14           1   .0034496  .0034496                   dbms_profiler.stop_profiler;
15                                                end;
===========================Results for run #2 made on 24-7月 -06 17:04:50 =========================
(mod) Run total time:   1158.19 seconds
Unit #1: SCOTT.DO_MOD - Total time:   1054.13 seconds
1                                                procedure do_mod as
2           0   0                                    cnt number := 0;
3                                                begin
4           0   0                                    dbms_profiler.start_profiler( 'mod' );
5     500,001   158.52169  .00031704                 for i in 1 .. 500000
6                                                    loop
7     500,000   147.34272  .00029468                     cnt := cnt + 1;
8     500,000   709.65247  .00141930                     if ( mod(cnt,1000) = 0 )
9                                                        then
10         500   38.609054  .07721810                         commit;
11                                                        end if;
12                                                    end loop;
13           1   .00322387  .00322387                 dbms_profiler.stop_profiler;
14                                                end;
============================================================================
PL/SQL 过程已成功完成。
================== Profiler report - all runs rolled up ===================
Unit SCOTT.DO_MOD:
1                                                procedure do_mod as
2           0   0                                    cnt number := 0;
3                                                begin
4           0   0                                    dbms_profiler.start_profiler( 'mod' );
5     500,001   158.52169  .00031704                 for i in 1 .. 500000
6                                                    loop
7     500,000   147.34272  .00029468                     cnt := cnt + 1;
8     500,000   709.65247  .00141930                     if ( mod(cnt,1000) = 0 )
9                                                        then
10         500   38.609054  .07721810                         commit;
11                                                        end if;
12                                                    end loop;
13           1   .00322387  .00322387                 dbms_profiler.stop_profiler;
14                                                end;
Unit SCOTT.NO_MOD:
1                                                procedure no_mod as
2           0   0                                    cnt number := 0;
3                                                begin
4           0   0                                    dbms_profiler.start_profiler( 'no mod' );
5     500,001   154.96648  .00030993                 for i in 1 .. 500000
6                                                    loop
7     500,000   160.32336  .00032064                     cnt := cnt + 1;
8     500,000   171.38172  .00034276                     if ( cnt = 1000 )
9                                                        then
10         500   19.431397  .03886279                         commit;
11         500   .33597007  .00067194                         cnt := 0;
12                                                        end if;
13                                                    end loop;
14           1   .0034496  .0034496                   dbms_profiler.stop_profiler;
15                                                end;
============================================================================
PL/SQL 过程已成功完成。
  用Tom的profsum.sql脚本产生的结果示例

 


已更新2行。

PL/SQL 过程已成功完成。
=
=
====================
Total time
GRAND_TOTAL
-----------
    1560.57
=
=
====================
Total time spent on each run
  RUNID RUN_COMMENT      SECS
------- ----------- ---------
      1 no mod         506.44
      2 mod           1054.13
=
=
====================
Percentage of time in each module, for each run separately
  RUNID RUN_COMMENT UNIT_OWNER  UNIT_NAME           SECS PERCEN
------- ----------- ----------- -------------- --------- ------
      1 no mod      SCOTT       NO_MOD            506.44  100.0
      2 mod         SCOTT       DO_MOD           1054.13  100.0
=
=
====================
Percentage of time in each module, summarized across runs
UNIT_OWNER  UNIT_NAME           SECS PERCENTAG
----------- -------------- --------- ---------
SCOTT       DO_MOD           1054.13     67.55
SCOTT       NO_MOD            506.44     32.45
=
=
====================
Lines taking more than 1% of the total time, each run separate
  RUNID     HSECS     PCT OWNER       UNIT_NAME       LINE# TEXT
------- --------- ------- ----------- -------------- ------ ---------------------
      2  70965.25    45.5 SCOTT       DO_MOD              8 if ( mod(cnt,1000) =
                                                            0 )
      1  17138.17    11.0 SCOTT       NO_MOD              8 if ( cnt = 1000 )
      1  16032.34    10.3 SCOTT       NO_MOD              7 cnt := cnt + 1;
      2  15852.17    10.2 SCOTT       DO_MOD              5 for i in 1 .. 500000
      1  15496.65     9.9 SCOTT       NO_MOD              5 for i in 1 .. 500000
      2  14734.27     9.4 SCOTT       DO_MOD              7 cnt := cnt + 1;
      2   3860.91     2.5 SCOTT       DO_MOD             10 commit;
      1   1943.14     1.2 SCOTT       NO_MOD             10 commit;
已选择8行。
=
=
====================
Most popular lines (more than 1%), summarize across all runs
    HSECS     PCT UNIT_OWNER  UNIT_NAME       LINE# TEXT
--------- ------- ----------- -------------- ------ ---------------------
70965.25    45.5 SCOTT       DO_MOD              8 if ( mod(cnt,1000) =
                                                    0 )
17138.17    11.0 SCOTT       NO_MOD              8 if ( cnt = 1000 )
16032.34    10.3 SCOTT       NO_MOD              7 cnt := cnt + 1;
15852.17    10.2 SCOTT       DO_MOD              5 for i in 1 .. 500000
15496.65     9.9 SCOTT       NO_MOD              5 for i in 1 .. 500000
14734.27     9.4 SCOTT       DO_MOD              7 cnt := cnt + 1;
  3860.91     2.5 SCOTT       DO_MOD             10 commit;
  1943.14     1.2 SCOTT       NO_MOD             10 commit;
已选择8行。

PL/SQL 过程已成功完成。
=
=
====================
Number of lines actually executed in different units (by unit_name)
UNIT_OWNER  UNIT_NAME      LINES_EXECUTED LINES_PRESENT     PCT
----------- -------------- -------------- ------------- -------
SCOTT       DO_MOD                      5             7    71.4
SCOTT       NO_MOD                      6             8    75.0
=
=
====================
Number of lines actually executed for all units
LINES_EXECUTED
--------------
            11
=
=
====================
Total number of lines in all units
LINES_PRESENT
-------------
           15

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

上一篇: TVD$XTAT的用法
下一篇: oracle统计
请登录后发表评论 登录
全部评论

注册时间:2009-09-28

  • 博文量
    125
  • 访问量
    124038