Procedure 性能检测与调整方法

 

Procedure 性能检测与调整

 

这几天在写几个监视Oracle 性能的脚本,有个刚开始写Plsql 的同事问有否监控pl.sql code 的方法。

推荐了一下dbms_profile package 。如何使用请看下面内容，希望对大家有点帮助。

 

 

从Oracle8.1 开始Oracle 已经提供了Package dbms_profile 来监视Procedure/Function/Package 的脚本了。

 

过程基本如下：

 

1：检查是否已经有dbms_profile 这个Package。

    SELECT COUNT (*)

      FROM dba_objects

     WHERE object_name = 'DBMS_PROFILER'

 

   如果有结果返回说明已经创建了这个Package，就不需要再创建跳过第二步。

 

2：创建DBMS_PROFILE package

   Sys:  创建Package

     $ORACLE_HOME/rdbms/admin/proftab.sql

 

3: 创建Profile tables

 

   Sys： 创建 Table

     $ORACLE_HOME/rdbms/admin/proftab.sql

 

    SELECT   *

    FROM dba_objects

       WHERE object_name LIKE '%PROFILE%' AND object_type IN

                                                        ('TABLE', 'SEQUENCE')

    ORDER BY last_ddl_time DESC

 

    SYS   PLSQL_PROFILER_DATA

    SYS   PLSQL_PROFILER_UNITS

    SYS   PLSQL_PROFILER_RUNS

    SYS   PLSQL_PROFILER_RUNNUMBER  ----就这四个Table.

 

 

 

4: 如何使用？

 

    DECLARE

       err   NUMBER;

    BEGIN

       err :=DBMS_PROFILER.start_profiler (TO_CHAR (SYSDATE, 'YYYY-MM-DD 

            HH24:MI:SS'));

              -- You need to test procedure

       err := DBMS_PROFILER.stop_profiler;

    END;

 

5：产看test_procedure_name 每句SQL/PLSQL 的执行时间。

 

    

SELECT   p.unit_name, p.occured, p.tot_time, p.line# line,

         SUBSTR (s.text, 1, 75) text

    FROM (SELECT u.unit_name, d.total_occur occured, u.unit_type TYPE,

                 (d.total_time / 1000000000) tot_time, d.line#

            FROM SYS.plsql_profiler_units u, SYS.plsql_profiler_data d

           WHERE d.runid = u.runid

             AND d.unit_number = u.unit_number

             AND d.total_occur > 0) p,     

         SYS.user_source s

   WHERE p.unit_name = s.NAME(+)

     AND p.line# = s.line(+)

     AND p.TYPE = s.TYPE(+)

     AND p.unit_name = 'COLL_TEST'

ORDER BY p.unit_name, p.line#;

 

6：测试用例：

 

创建procedure:

CREATE OR REPLACE PROCEDURE coll_test

IS

   CURSOR big_emp_curs

   IS

      SELECT ename     FROM big_emp;

 

   cnt     NUMBER;

   ename   VARCHAR2 (30);

BEGIN

   FOR be_r IN big_emp_curs

   LOOP

      ename := be_r.ename;

   END LOOP;

 

   SELECT COUNT (*)   INTO cnt   FROM big_emp   WHERE ename = 'SCOTT';

 

   SELECT COUNT (*)   INTO cnt   FROM big_emp;

   EXECUTE IMMEDIATE 'CREATE INDEX idx_big_emp ON big_emp(ename)';

 

   SELECT COUNT (*)   INTO cnt   FROM big_emp   WHERE ename = 'SCOTT';

   ename := 'DUMMY';

END coll_test;

 

 

收集procedure coll_test 运行时间。

DECLARE

   err   NUMBER;

BEGIN

   err :=

      DBMS_PROFILER.start_profiler (TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS')

       );

   coll_test;                                    -- You need to test procedure

   err := DBMS_PROFILER.stop_profiler;

END;

 

 

查看收集结果：

SELECT   p.unit_name, p.occured, p.tot_time, p.line# line,

         SUBSTR (s.text, 1, 75) text

    FROM (SELECT u.unit_name, d.total_occur occured, u.unit_type TYPE,

                 (d.total_time / 1000000000) tot_time, d.line#

            FROM SYS.plsql_profiler_units u, SYS.plsql_profiler_data d

           WHERE d.runid = u.runid

             AND d.unit_number = u.unit_number

             AND d.total_occur > 0) p,      -- change 28 to the selected runid

         SYS.user_source s

   WHERE p.unit_name = s.NAME(+)

     AND p.line# = s.line(+)

     AND p.TYPE = s.TYPE(+)

     AND p.unit_name = 'COLL_TEST'

ORDER BY p.unit_name, p.line#;

 

 

UNIT_NAME       OCCURED    TOT_TIME       LINE TEXT

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

COLL_TEST             1     .000042          1  PROCEDURE coll_test

COLL_TEST             1     .169419          5        SELECT ename     FROM big_emp;

COLL_TEST          4733    1.988260         10     FOR be_r IN big_emp_curs

COLL_TEST        473074     .347261         12        ename := be_r.ename;

COLL_TEST             1     .143458         15     SELECT COUNT (*)   INTO cnt   FROM big_emp   WHERE ename = 'SCOTT';

COLL_TEST             1     .117456         17     SELECT COUNT (*)   INTO cnt   FROM big_emp;

COLL_TEST             1     .123682         20     SELECT COUNT (*)   INTO cnt   FROM big_emp   WHERE ename = 'SCOTT';

COLL_TEST             1     .000006         21     ename := 'DUMMY';

COLL_TEST             1     .000055         22  END coll_test;     

 

 

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

转载于:http://blog.itpub.net/34596/viewspace-592107/