使用 DBMS_PROFILER 定位 PL/SQL 瓶頸代碼

      本文描述中涉及到的相關參考
          綁定變數及其優缺點
          Oracle 硬解析與軟解析
          Oracle 綁定變數窺探
          SQL Tuning Advisor(STA) 到底做了什麼?
          使用SQL tuning advisor(STA)自動最佳化SQL
  
1、配置PROFILER及示範環境

--示範環境sys@USBO> select * from v$version where rownum<2;BANNER------------------------------------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Productiona、使用需要進行plsql剖析的schema執行指令碼proftab.sql(也可以使用一個賬戶建立然後建立同義字並授權給public)--首次使用時執行，會建立相應的表格儲存體profiler資訊，即plsql_profiler_runs,plsql_profiler_units,plsql_profiler_datasys@USBO> conn scott/tiger;Connected.scott@USBO> @?/rdbms/admin/proftab.sqlb、使用sysdba帳戶安裝包DBMS_PROFILER，執行指令碼profload.sql scott@USBO> conn / as sysdbaConnected.sys@USBO> @?/rdbms/admin/profload.sqlc、如果需要，建立plan_table,執行指令碼utlxplan.sqlsys@USBO> @?/rdbms/admin/utlxplan.sqlsys@USBO> GRANT ALL ON sys.plan_table TO public;sys@USBO> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;sys@USBO> conn scott/tiger;Connected.--建立示範表scott@USBO> create table t1(id number,val number);--建立一個基於字面量的過程scott@USBO> create or replace procedure literals  2  is  3   v_num number;  4  begin  5   for i in 1..100000 loop  6     v_num := dbms_random.random;                 7     execute immediate   8      'insert into t1 values ('||v_num||','||v_num||')';  9   end loop; 10   end; 11  /Procedure created.

a、啟動profiler，調用過程start_profilerscott@USBO> execute dbms_profiler.start_profiler('literals');b、執行你需要剖析的代碼(包，過程，匿名塊等)scott@USBO> exec literals;c、停止profiler，調用過程stop_profilerscott@USBO> execute dbms_profiler.stop_profiler;d、查看profiler報告scott@USBO> @chk_profileEnter value for input_comment_name: literalsEnter value for input_sp_name: literalsTEXT                                                    TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME------------------------------------------------------- ----------- ---------- -------- --------procedure literals                                                1         .0       .0       .0procedure literals                                                3         .0       .0       .0procedure literals                                                0         .0       .0       .0 for i in 1..100000 loop                                     100001         .2       .0       .0   v_num := dbms_random.random;                              100000         .8       .0       .0   execute immediate                                         100000       49.9       .0       .0 end;                                                             1         .0       .0       .0procedure literals                                                2         .0       .0       .0--上面的結果可以看出整個過程中execute immediate耗用49s中，也即是說，如果能夠降低該行代碼時間，則整個效能會大幅提升

--這個方法實際也沒有太多的變化，只不過將需要剖析的代碼和啟用profiler與停止profiler封裝到一個sql中--下面建立一個使用綁定變數的樣本來進行剖析scott@USBO> create or replace procedure binds  2  is  3   v_num number;  4  begin  5   for i in 1..100000 loop  6     v_num := dbms_random.random;  7     insert into t1 values (v_num,v_num);  8   end loop;  9  end; 10  /Procedure created.--直接調用call_profiler.sql(該代碼封裝了啟動profiler,停止profiler)scott@USBO> @call_profilerProfiler startedPL/SQL procedure successfully completed.Profiler stoppedProfiler flushedrunid:4--Author : Leshami--Blog   : http://blog.csdn.net/leshami--查看profiler報告scott@USBO> @evaluate_profiler_resultsEnter value for runid: 4Enter value for name: bindsEnter value for owner: scott      Line      Occur        sec Text---------- ---------- ---------- ------------------------------------------------------------         1          0          0 procedure binds         2                       is         3                        v_num number;         4                       begin         5     100001       .182  for i in 1..100000 loop         6     100000       .498    v_num := dbms_random.random;         7     100000      3.756    insert into t1 values (v_num,v_num);         8                        end loop;         9          1          0 end;9 rows selected.Code% coverage--------------            80--從上面的報告可知，當改用使用綁定變數後，原來執行insert語句的時間由49.9s下降到3.756s--對於這個案例僅僅是示範定位瓶頸代碼，並改用綁定變數以提高效能，對於其他情形，具體的如何修改瓶頸代碼應具體分析

a、chk_profile.sql--file_name: chk_profile.sqlset linesize 190column text format a100 wrapcolumn total_time format 99999.9column min_time format 99999.9column max_time format 99999.9select s.text ,       p.total_occur ,       p.total_time/1000000000 total_time,       p.min_time/1000000000 min_time,       p.max_time/1000000000 max_timefrom plsql_profiler_data p, user_source s, plsql_profiler_runs rwhere p.line# = s.lineand   p.runid = r.runidand   r.run_comment = '&input_comment_name'and   s.name =upper('&input_sp_name');b、call_profiler--file_name:call_profiler.sqlSET HEAD OFFSET PAGES 0SELECT DECODE (DBMS_PROFILER.start_profiler, '0', 'Profiler started', 'Profiler error') FROM DUAL;-------you can put you plsql code in below block------------begin    binds;end;/---------------------------------------------------------------SELECT DECODE (DBMS_PROFILER.stop_profiler, '0', 'Profiler stopped', 'Profiler error') FROM DUAL;SELECT DECODE (DBMS_PROFILER.flush_data, '0', 'Profiler flushed', 'Profiler error') FROM DUAL;SELECT 'runid:' || plsql_profiler_runnumber.CURRVAL FROM DUAL;SET HEAD ONSET PAGES 200c、evaluate_profiler_results.sql --file_name:evaluate_profiler_results.sql undef runidundef ownerundef nameset verify offcol text format a60 wrapSELECT s.line "Line"     , p.total_occur "Occur"     , p.total_time "sec"     , s.text "Text"FROM   all_source s     , (SELECT u.unit_owner             , u.unit_name             , u.unit_type             , d.line#             , d.total_occur             , round(d.total_time / 1000000000,3) total_time        FROM   plsql_profiler_data d, plsql_profiler_units u        WHERE  u.runid = &&runid AND u.runid = d.runid AND u.unit_number = d.unit_number) pWHERE      s.owner = p.unit_owner(+)       AND s.name = p.unit_name(+)       AND s.TYPE = p.unit_type(+)       AND s.line = p.line#(+)       AND s.name = UPPER ( '&&name' )       AND s.owner = UPPER ( '&&owner' )ORDER BY s.line;SELECT exec.cnt / total.cnt * 100 "Code% coverage"FROM   (SELECT COUNT ( 1 ) cnt        FROM   plsql_profiler_data d, plsql_profiler_units u        WHERE      d.runid = &&runid               AND u.runid = d.runid               AND u.unit_number = d.unit_number               AND u.unit_name = UPPER ( '&&name' )               AND u.unit_owner = UPPER ( '&&owner' )) total     , (SELECT COUNT ( 1 ) cnt        FROM   plsql_profiler_data d, plsql_profiler_units u        WHERE      d.runid = &&runid               AND u.runid = d.runid               AND u.unit_number = d.unit_number               AND u.unit_name = UPPER ( '&&name' )               AND u.unit_owner = UPPER ( '&&owner' )               AND d.total_occur > 0) exec;undef runidundef ownerundef name