oracle存储过程dbms_sql,Oracle 优化之存储过程方法（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/

########################################################################################