oracle 使用序列效率,oracle使用profiler分析语句执行效率

1、前言

Profiler是ORACLE PL/SQL 的一个调试优化跟踪方案，使用profiler可以跟语句执行过程中消耗的时间等。在日常开发过程中，我们可能会常常遇到一些sql语句、存储过程在使用过程中，消耗过多的资源，为了细化了某条具体的语句，我们可以使用profiler。这样大大的减少了我们寻找问题语句的时间，提高效率，在优化的过程中，profiler起到一个很好的效果

2、profiler的一些包的介绍

--proftab.sql介绍

在当前用户建表及序列等，具体如下；

plsql_profiler_runs  - profiler运行信息

plsql_profiler_units - profiler每个单元信息

plsql_profiler_data  - profiler每个单元的详细数据

plsql_profiler_runnumber 用来生成profiler唯一运行编号的序列

--profload.sql介绍

创建数据库服务包，包括启动、停止等，具体如下：

sys.dbms_profiler包

此包包括以下主要的函数过程

start_profiler           --启动函数

stop_profiler            --停止函数

pause_profiler

resume_profiler

flush_data

internal_version_check

get_version

rollup_unit

rollup_run

--具体创建过程

SQL> @$ORACLE_HOME/rdbms/admin/proftab.sql;

drop table plsql_profiler_data cascade constraints

ERROR at line 1:

ORA-00942: table or view does not exist

drop table plsql_profiler_units cascade constraints

*

ERROR at line 1:

ORA-00942: table or view does not exist

drop table plsql_profiler_runs cascade constraints

*

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> @$ORACLE_HOME/rdbms/admin/profload.sql;

Package created.

Grant succeeded.

Synonym created.

Library created.

Package body created.

Testing for correct installation

SYS.DBMS_PROFILER successfully loaded.

PL/SQL procedure successfully completed.

3、创建我们需要的测试对象

在本存储过程中，我们只进行两条语句的分析处理，具体如下：

SQL> CREATE OR REPLACE PROCEDURE mytest_proc

2  is

3    v_counts1 number;

4    v_counts2 number;

5    v_counts3 number;

6

7  begin

8    select count(*) into v_counts1 from dba_tables;

9    select count(*) into v_counts2 from dba_objects;

10    select count(*) into v_counts3 from dba_segments;

11  end;

12  /

4、开始测试profiler的使用

--针对上面建立的存储过程，对里面的语句进行分析

SQL> begin

2  DBMS_PROFILER.START_PROFILER('my profiler test');

3  mytest_proc ;

4  DBMS_PROFILER.STOP_PROFILER;

5  end;

6  /

PL/SQL procedure successfully completed.

--查询刚才分析出来的结果：

SQL> select d.line#,

2         u.unit_name,

3         d.total_time,

4         d.total_occur,

5         d.max_time,

6         s.text

7    from sys.plsql_profiler_data d, sys.all_source s, sys.plsql_profiler_units u

8   where u.runid = d.runid

9     and d.unit_number = u.unit_number

10     and d.total_occur <> 0

11     and s.type(+) = u.unit_type

12     and s.owner(+) = u.unit_owner

13     and s.name(+) = u.unit_name

14     and d.line# = nvl(s.line, d.line#) and u.unit_name = 'MYTEST_PROC'

15   order by u.unit_number, d.line#

16  ;

LINE# UNIT_NAME       TOTAL_TIME TOTAL_OCCUR   MAX_TIME TEXT

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

1 MYTEST_PROC           2006           1       2006 PROCEDURE mytest_proc

8 MYTEST_PROC      412905914           1  412905914   select count(*) into v_counts1 from dba_tables;

9 MYTEST_PROC      102775576           1  102775576   select count(*) into v_counts2 from dba_objects;

10 MYTEST_PROC      222756652           1  222756652   select count(*) into v_counts3 from dba_segments;

12 MYTEST_PROC           5015           1       5015 end;

字段说明：

unit_time     --表示执行的过程、语句、函数等

total_time    --总共运行的时间，单位非常小，万亿分之一秒

total_occur   --执行的次数，用总时间除次数，可得到每次运行的时间

max_time      --代码运行的最最长时间，如有游标循环等

runid         --运行号，这个是自动产生的

Avg_time      --平均运行时间

min_time      --最小运行时间