Oracle PL/SQL 效能分析工具 profiler 說明

 

一.說明

Oracle提供了profiler工具包，利用該工具包可以查看PL/SQL執行過程中各模組的效能。可以從MOS上下載該指令碼：

Implementing andUsing the PL/SQL Profiler [ID 243755.1]

 

            也可以從我的CSDN上下載：

            http://download.csdn.net/detail/tianlesoftware/4051100

When there is asignificant gap between user elapsed time and SQL processing elapsed time, andthere is PL/SQL code involved, the PL/SQL Profiler becomes a very useful tool.It helps to identify the lines of PL/SQL code which are taking longer toprocess. 

--當user elapsed time 和 SQL processing elapsedtime 有很大差別，且涉及到PL/SQL 代碼時，就可以使用PL/SQL Profiler 工具，其可以指明行級PL/SQL 的時間。

 

For example, ifa transaction which uses PL/SQL Libraries (packages, procedures, functions ortriggers) executes in one hour of user elapsed time, and reviewing the resultsof the Trace Analyzer or TKPROF only 10 minutes of the elapsed time can beexplained with SQL commands being executed, then, by using the PL/SQL Profiler,a line-by-line of the executed PL/SQL application code is reported, includingthe total execution time for each line of code, and how many times each ofthese lines was executed.

--比如執行了一個PL/SQL 庫（包，過程，函數或者觸發器），userelapsed time 用了1個小時，但是通過trace 顯示僅用了10分鐘，這時就可以用PL/SQL Profiler，其可以報告每行代碼執行的時間。

 

The actualPL/SQL Profiler is provided with the core RDBMS code, and it is well documentedon the Supplied PL/SQL Packages and Types Reference manual, under the packagename DBMS_PROFILER. This Note is about implementing and using the PL/SQLProfiler on any 9i or higher database, in order to debug the performance of anyPL/SQL application Library. The main script provided in this Note (profiler.sql)generates a comprehensive HTML report on the performance data extracted by theDBMS_PROFILER package.

--實際上，PL/SQL Profiler 僅提供了核心的RDBMS 代碼，其在DBMS_PROFILER包裡也說明。

 

PL/SQL Profiler 包含如下3個指令碼：

profiler.sql - Reporting PL/SQL Profilerdata generated by DBMS_PROFILER (main script)

profgsrc.sql - Get source code for PL/SQLLibrary (package, procedure, function or trigger)

proftab.sql - Create tables for the PL/SQLprofiler

 

 

二.使用前的準備工作

 

2.1 If used for the first time, determineif DBMS_PROFILER is installed by doing a describe on that package

--如果是第一次使用，需要檢查DBMS_PROFILER 包是否安裝。

#sqlplus APPS/<pwd> --注意使用者
SQL> DESC DBMS_PROFILER;

 

2.2 If DBMS_PROFILER is not installed,connect as SYS into SQL*Plus on database server, and execute command below tocreate the missing package:

--如果DBMS_PROFILER沒有安裝，使用SYS 用來執行如下命令來安裝該包。

#sqlplus SYS/<pwd> --注意使用者
SQL> START ?/rdbms/admin/profload.sql;

 

2.3  If used for the first time, andonce DBMS_PROFILER is installed, connect as application user into SQL*Plus, andcreate the repository tables PLSQL_PROFILER_RUNS, PLSQL_PROFILER_UNITS andPLSQL_PROFILER_DATA (proftab.sql is provided within PROF.zip, and is alsoavailable under $ORACLE_HOME/rdbms/admin)

--如果是第一次使用，且DBMS_PROFILER 已經安裝，那麼執行proftab.sql 指令碼，建立存放統計資料的表：PLSQL_PROFILER_RUNS,PLSQL_PROFILER_UNITS and PLSQL_PROFILER_DATA。 該指令碼在PROF 包裡有，$ORACLE_HOME/rdbms/admin目錄下也有。

#sqlplus APPS/<pwd>
SQL> START proftab.sql;

 

或：

SQL> start ?/rdbms/admin/proftab.sql

drop table plsql_profiler_data cascadeconstraints

          *

ERROR at line 1:

ORA-00942: table or view does not exist

 

drop table plsql_profiler_units cascadeconstraints

          *

ERROR at line 1:

ORA-00942: table or view does not exist

 

drop table plsql_profiler_runs cascadeconstraints

          *

ERROR at line 1:

ORA-00942: table or view does not exist

 

drop sequence plsql_profiler_runnumber

              *

ERROR at line 1:

ORA-02289: sequence does not exist

 

Table created.

Comment created.

 

Table created.

Comment created.

 

Table created.

Comment created.

 

Sequence created.

 

SQL>

 

 

2.4 Since main script on this Note(profiler.sql) reports on data generated by package DBMS_PROFILER, be sure toprofile your PL/SQL Library prior to try reporting the results. To profile aPL/SQL Library (package, procedure, function or trigger), include in its bodythe two calls to actually start and complete the profiling.

--因為主指令碼（profiler.sql）顯示的資料是用DBMS_PROFILER包產生的，所以顯示結果之前要先profile PL/SQL 代碼。

 

Use the example below on any PL/SQL Libraryto profile.

 

BEGIN
DBMS_PROFILER.START_PROFILER('any comment to identify this execution');
...
DBMS_PROFILER.STOP_PROFILER;
EXCEPTION -- this line may exist in your code
...
END;
/

 

2.5 In order to modify your PL/SQL Library,find first the script that creates it, make a backup, and insert manually theSTART and STOP calls for the profiler. If unable to find the script thatcreates your package, procedure, function or trigger, use the provided scriptprofgsrc.sql executing with PL/SQL Library name as inline parameter:

--為了修改PL/SQL庫，首先需要找到指令碼，備份後，然後手工的添加START 和 STOP,如果不能找到原代碼，可以使用profgsrc.sql 指令碼來擷取原代碼：

 

#sqlplus APPS/<pwd>
SQL> START profgsrc.sql <PL/SQL Library name>;

 

SQL> start ?/rdbms/admin/profgsrc.sql  logon_audit;

Usage:

sqlplus apps/<pwd>

SQL> START profgsrc.sql <PL/SQLLibrary name>

 

NAME                           TYPE

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

LOGON_AUDIT                    TRIGGER

 

Generating spool file for TRIGGERLOGON_AUDIT

 

我這裡將指令碼copy到了$ORACLE_HOME/rdbms/admin下，執行之後其會在sqlplus 的目前的目錄下，產生一個同名的sql 指令碼，指令碼就是我們對象的的SQL 代碼。

 

 

2.6 Script profgsrc.sql extracts fromUSER_SOURCE the actual source code for the requested PL/SQL Library. Itgenerates a text spool file as a SQL script to regenerate the PL/SQL Library.Make a backup of the spool file before modifying it. Compile your modifiedPL/SQL Library by executing it from SQL*Plus and connecting as your applicationuser.

--profgsrc.sql 從USER_SOURCE中抽取對象的代碼。

 

2.7 Once your compiled PL/SQL Librarycontains the START and STOP profiler procedure calls, execute your Library fromyour application. Every execution generates a new run_id which can then bereported on, by using the profiler.sql script.

--當我們在PL/SQL對象中添加完START 和 STOP profiler 調用後，然後啟動應用，每次執行都會產生一個run_id, 然後使用profiler.sql 工具，結合run_id 就可以擷取對應的報告。

 

產生報告命令

 SQL> START profiler.sql <run_id>

 

Where run_id isthe execution id returned by the DBMS_PROFILER (which must be installed first).

If run_id isunknown, execute without any parameter and the script will display a list tochoose from.

--如果沒有提供任何參數，那麼會自動列出所有的run_id.

 

 

三.使用樣本

測試的預存程序如下：

CREATE OR REPLACE PROCEDURE proc_test

AS

BEGIN

   insert intotest_pro  select * from all_objects;

      commit;

   FOR x IN (select * fromall_users where user_id>90)

   LOOP

      DBMS_OUTPUT.put_line(x.username);

   END LOOP;

END proc_test;

 

 

3.1 樣本1

 

SQL> set serveroutput on

SQL> begin

 2 DBMS_PROFILER.START_PROFILER('Dave Test PL/SQL Profiler');

 3  proc_test;

 4  DBMS_PROFILER.STOP_PROFILER;

 5  end;

 6  /

XS$NULL

DAVE3

DAVE2

DAVE1

XEZF

MGMT_VIEW

SYSMAN

ANQING

DVD

 

PL/SQL procedure successfully completed.

 

--調用PL/SQLprofiler.sql

SQL> @?/rdbms/admin/profiler.sql

 

    RUNID RUN_DATE             RUN_COMMENT

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

        1 08-FEB-12 14:55:38    Dave TestPL/SQL Profiler

        2 08-FEB-12 15:03:11    Dave TestPL/SQL Profiler

 

 

Usage:

sqlplus apps/<pwd>

SQL> START profiler.sql <runid>

 

Enter value for 1: 2

SQL>

--其會自動列出所有RUNID,並啟動，我們這裡選擇上面對應的RUNID，輸入2，斷行符號即可。

 

執行完畢後會在SQLPLUS的目前的目錄下產生一個profiler_2.html的報告，這裡的2是RUNID.

 

 

3.2 樣本2

 

CREATE OR REPLACE PROCEDURE proc_test2

AS

BEGIN

DBMS_PROFILER.START_PROFILER('Dave Test PL/SQL Profiler');

   insert intotest_pro  select * from all_objects;

   commit;

   FOR x IN (select * fromall_users where user_id>90)

   LOOP

      DBMS_OUTPUT.put_line(x.username);

   END LOOP;

DBMS_PROFILER.STOP_PROFILER;

END proc_test2;

 

 

SQL> exec proc_test2

PL/SQL procedure successfully completed.

 

SQL> @?/rdbms/admin/profiler.sql

    RUNID RUN_DATE             RUN_COMMENT

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

        1 08-FEB-12 14:55:38    Dave TestPL/SQL Profiler

        2 08-FEB-12 15:03:11    Dave TestPL/SQL Profiler

        3 08-FEB-12 15:07:26    Dave TestPL/SQL Profiler

        4 08-FEB-12 15:09:40    Dave TestPL/SQL Profiler

--這裡這裡的RUN_COMMENT內容,其在DBMS_PROFILER.START_PROFILER 中指定，主要用來辨別我們的RUNID.

 

Usage:

sqlplus apps/<pwd>

SQL> START profiler.sql <runid>

 

Enter value for 1: 4

SQL>

 

 

3.3 注意事項：

1.     proftab.sql 指令碼需要在PL/SQL對象所在的使用者下執行，否則就會報如下錯誤：

 

SQL> exec proc_test2

BEGIN proc_test2; END;

 

*

ERROR at line 1:

ORA-06528: Error executing PL/SQL profiler

ORA-06512: at"SYS.DBMS_PROFILER", line 123

ORA-06512: at"SYS.DBMS_PROFILER", line 132

ORA-06512: at "DAVE.PROC_TEST2",line 4

ORA-06512: at line 1

 

2.     起初在win7+ oracle 11gR2 下測試，執行沒有報錯，但最終產生的報告沒有資料。後轉移到linux + oracle 10gR2 下測試，正常。

 後經過測試，發現問題，之前的環境是64位的win7+32位的Oracle 11gR2，後把Oracle 換成64位 11gR2 後正常。這個原因可能與版本有關係，因為也不能排除重新安裝解決的某些未知因素。

 

測試結果如下：

 

3. 兩個樣本的區別在於樣本1沒有修改PL/SQL 對象，而是在PL/SQL 代碼塊中start 和stop了DBMS_PROFILER。 而樣本2中是修改了PL/SQL 對象，將DBMS_PROFILER 添加到PL/SQL 對象中。

        官網的資料是先備份PL/SQL對象，然後修改，將DBMS_PROFILER 添加到對象裡，這樣的好處是每次調用時都會統計PROFILER的資訊。當然麻煩的是需要修改對象，而在樣本1中，在PL/SQL 代碼塊中啟動和關閉，就免於修改PL/SQL 對象。

      所以具體採用哪種方式要結合自己的需求，能達到目的即可。

 

 

 

 

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

著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!

Email:             tianlesoftware@gmail.com

Skype:            tianlesoftware

Blog:   http://www.tianlesoftware.com

Weibo:            http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook:http://www.facebook.com/tianlesoftware

 

-------加群需要在備忘說明Oracle資料表空間和資料檔案的關係，否則拒絕申請----

DBA1 群：62697716(滿);   DBA2 群：62697977(滿)  DBA3 群：62697850(滿)  

DBA 超級群：63306533(滿);  DBA4 群：83829929   DBA5群： 142216823

DBA6 群：158654907    DBA7 群：172855474   DBA總群：104207940