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;

Procedure 效能檢測與調整方法

相關文章