儲存過程優化（DBMS_HPROF）

今天介紹推薦一個11g以後優化儲存過程的利器“DBMS_HPROF”，官方推薦11g以上可以使用DBMS_HPROF來分析優化儲存過程。具體使用方法如下。資料庫版本（11.2.0.4.0）

1.Unlock Scott

SQL> conn /as sysdba

Connected.

SQL> alter user scott account unlock;

User altered.

SQL> alter user scott identified by oracle;

User altered.

2.Create procedure p1 with user Scott

Create or replace procedure p1 is

N number;

Procedure p2 is

   begin

      select count(*) into n from emp;

   end;

begin

   for j in 1..1000 loop

      p2;

   end loop;

end;

/

3.Create Directory Alias and profiling results in a trace file

SQL> Create or replace directory prof_dir as '/dwtest/dba';

Directory created.

4.grant execute on DBMS_HPROF to scott

SQL> grant execute on DBMS_HPROF to scott;

Grant succeeded.

5.enable Dbms_hprof and generate t.trc

Begin

   Dbms_hprof.start_profiling('PROF_DIR','t.trc');

End;

/

SQL> Begin

  2     Dbms_hprof.start_profiling('PROF_DIR','t.trc');

  3  End;

  4  /

PL/SQL procedure successfully completed.

6.User Scott execu P1

SQL> conn scott/oracle

Connected.

SQL> begin

  2     p1;

  3  end;

  4  /

PL/SQL procedure successfully completed.

7.disable Dbms_hprof

SQL> begin

  2     dbms_hprof.stop_profiling;

  3  end;

  4  /

PL/SQL procedure successfully completed.

8.In order to process the captured information, additional tables and database objects related to the profiling utility need to be created within the SCOTT schema. The Hierarchical Profiler tables can be created by running

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

Table dropped.

Table dropped.

Table dropped.

Sequence dropped.

Table created.

Comment created.

Table created.

Comment created.

Table created.

Comment created.

Sequence created.

9.Display some of the newly created tables

SQL> SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'DBMSHP_%';

TABLE_NAME

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

DBMSHP_RUNS

DBMSHP_PARENT_CHILD_INFO

DBMSHP_FUNCTION_INFO

10.DBMS_HPROF.ANALYZE processes the raw trace file generated by data collection component and produces hierarchial information in hierarchial profiler database tables created by executing the above script. The following SQL illustrates how to run this command in SQL*Plus

SQL> Var runid number;

SQL>

SQL> Begin

  2  :runid:=dbms_hprof.analyze(location=>'PROF_DIR', filename=>'t.trc',run_comment=>'Run one');

  3  end;

  4  /

PL/SQL procedure successfully completed.

SQL> SQL> print :runid;

     RUNID

----------

         1

11.The command line plshprof utility can be used to generate html report. The following command runs the plshprof utility and generates a file (t.html) based on the profile information captured within file t.trc. Notice that the output file name is simply 't' and the report automatically assigns the html extension.

hostname:/dwtest/dba$plshprof -output /dwtest/dba/t /dwtest/dba/t.trc

PLSHPROF: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

[3 symbols processed]

[Report written to '/dwtest/dba/t.html']

hostname:/dwtest/dba$ls -l

total 144

-rw-r--r--    1 oracle   dba            2461 May 30 17:54 t.html

-rw-r--r--    1 oracle   dba             245 May 30 17:48 t.trc

-rw-r--r--    1 oracle   dba            1039 May 30 17:54 t_2c.html

-rw-r--r--    1 oracle   dba            1067 May 30 17:54 t_2f.html

-rw-r--r--    1 oracle   dba             890 May 30 17:54 t_2n.html

-rw-r--r--    1 oracle   dba            1858 May 30 17:54 t_fn.html

-rw-r--r--    1 oracle   dba            2304 May 30 17:54 t_md.html

-rw-r--r--    1 oracle   dba            2298 May 30 17:54 t_mf.html

-rw-r--r--    1 oracle   dba            2296 May 30 17:54 t_ms.html

-rw-r--r--    1 oracle   dba             787 May 30 17:54 t_nsc.html

-rw-r--r--    1 oracle   dba             815 May 30 17:54 t_nsf.html

-rw-r--r--    1 oracle   dba             682 May 30 17:54 t_nsp.html

-rw-r--r--    1 oracle   dba            5098 May 30 17:54 t_pc.html

-rw-r--r--    1 oracle   dba            2055 May 30 17:54 t_tc.html

-rw-r--r--    1 oracle   dba            1937 May 30 17:54 t_td.html

-rw-r--r--    1 oracle   dba            2083 May 30 17:54 t_tf.html

-rw-r--r--    1 oracle   dba            1929 May 30 17:54 t_ts.html

12.Display design sketch

########################################################################################
版權所有，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!【QQ交流群：53993419】
QQ：14040928 E-mail：dbadoudou@163.com
本文連結： http://blog.itpub.net/26442936/viewspace-2109404/
########################################################################################

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-2109404/，如需轉載，請註明出處，否則將追究法律責任。