Oracle PL/SQL 性能诊断是经常会遇到的问题，所以 Oracle 提供了比较多的诊断工具，其中就包括了 DBMS_PROFILER 包。
DBMS_PROFILER 包中会用到的一些基本构建如下：
- 在调用程式前使用
DBMS_PROFILER.START_PROFILER，启动对该程式的监控； - 在调用程式后使用
DBMS_PROFILER.STOP_PROFILER，结束对该程式的监控 - 在监控过程中，系统会将资料存放至三个表
PLSQL_PROFILER_RUNS运行信息表、PLSQL_PROFILER_UNITS单元信息表、PLSQL_PROFILER_DATA单元详细信息表中 - 可以通过
PROFILER.SQL工具产生HTML形式的性能报表
安装 PROFILER
使用命令行脚本方式
- 登录 SYS 用户
C:\Windows\system32>sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on 星期三 1月 23 15:30:50 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
- 以 SYS 用户创建 DBMS_PROFILER 包
SQL> @?/rdbms/admin/profload.sql 程序包已创建。 授权成功。 同义词已创建。 库已创建。 程序包体已创建。 Testing for correct installation SYS.DBMS_PROFILER successfully loaded. PL/SQL 过程已成功完成。
- 创建表
SQL> @?/rdbms/admin/proftab.sql
drop table plsql_profiler_data cascade constraints
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
drop table plsql_profiler_units cascade constraints
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
drop table plsql_profiler_runs cascade constraints
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
drop sequence plsql_profiler_runnumber
*
第 1 行出现错误:
ORA-02289: 序列不存在
表已创建。
注释已创建。
表已创建。
注释已创建。
表已创建。
注释已创建。
序列已创建。
- 查看 SYS 用户下创建的三张表
SQL> desc plsql_profiler_runs; 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- RUNID NOT NULL NUMBER RELATED_RUN NUMBER RUN_OWNER VARCHAR2(32) RUN_DATE DATE RUN_COMMENT VARCHAR2(2047) RUN_TOTAL_TIME NUMBER RUN_SYSTEM_INFO VARCHAR2(2047) RUN_COMMENT1 VARCHAR2(2047) SPARE1 VARCHAR2(256) SQL> desc plsql_profiler_units; 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- RUNID NOT NULL NUMBER UNIT_NUMBER NOT NULL NUMBER UNIT_TYPE VARCHAR2(32) UNIT_OWNER VARCHAR2(32) UNIT_NAME VARCHAR2(32) UNIT_TIMESTAMP DATE TOTAL_TIME NOT NULL NUMBER SPARE1 NUMBER SPARE2 NUMBER SQL> desc plsql_profiler_data; 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- RUNID NOT NULL NUMBER UNIT_NUMBER NOT NULL NUMBER LINE# NOT NULL NUMBER TOTAL_OCCUR NUMBER TOTAL_TIME NUMBER MIN_TIME NUMBER MAX_TIME NUMBER SPARE1 NUMBER SPARE2 NUMBER SPARE3 NUMBER SPARE4 NUMBER
- 在 SYS 用户下创建公有同义词
SQL> create public synonym plsql_profiler_runs for plsql_profiler_runs; 同义词已创建。 SQL> create public synonym plsql_profiler_units for plsql_profiler_units; 同义词已创建。 SQL> create public synonym plsql_profiler_data for plsql_profiler_data; 同义词已创建。 SQL> create public synonym plsql_profiler_runnumber for plsql_profiler_runnumber ; 同义词已创建。 SQL> select * from dba_synonyms t where t.synonym_name like 'PLSQL%'; OWNER SYNONYM_NAME TABLE_OWNE TABLE_NAME DB_LINK ---------- ------------------------------ ---------- ------------------------------ ---------- PUBLIC PLSQL_PROFILER_DATA SYS PLSQL_PROFILER_DATA PUBLIC PLSQL_PROFILER_RUNNUMBER SYS PLSQL_PROFILER_RUNNUMBER PUBLIC PLSQL_PROFILER_RUNS SYS PLSQL_PROFILER_RUNS PUBLIC PLSQL_PROFILER_UNITS SYS PLSQL_PROFILER_UNITS
- 授权给所有用户
SQL> grant select, insert, update, delete on plsql_profiler_runs to public; 授权成功。 SQL> grant select, insert, update, delete on plsql_profiler_units to public; 授权成功。 SQL> grant select, insert, update, delete on plsql_profiler_data to public; 授权成功。 SQL> grant select on plsql_profiler_runnumber to public; 授权成功。
- 登录 SCOTT 普通用户，访问 PLSQL_PROFILER 相关表
SQL> conn scott/tiger 已连接。 SQL> select * from plsql_profiler_runs; 未选定行 SQL> select * from plsql_profiler_units; 未选定行 SQL> select * from plsql_profiler_data; 未选定行
需要注意的是，普通用户实际上访问的是 SYS 用户下的表，也就是说所有的用户共享 SYS 用户下的表。
使用 PLSQL Developer 工具
-
使用 PLSQL Developer 工具，登录 SCOTT 普通用户
-
创建测试存储过程 P_TEST
CREATE OR REPLACE PROCEDURE P_TEST
AS
BEGIN
FOR X IN (
SELECT * FROM USER_TABLES
) LOOP
DBMS_OUTPUT.PUT_LINE(X.TABLE_NAME||','||X.TABLESPACE_NAME||X.NUM_ROWS);
END LOOP;
END;
-
以 Test 方式执行存储过程
-
点击 Create Profiler report
-
当前用户会出现三张 PLSQL_PROFILER 表和对应的序列
需要注意的是，以这种方式创建的 PLSQL_PROFILER 表，只能在当前用户访问，也就是说如果别的用户需要使用，就需要以同样的方式创建
SQL> conn / as sysdba
已连接。
SQL> create user profiler identified by profier;
用户已创建。
SQL> grant connect, resource to profiler;
授权成功。
SQL> conn profiler/profier;
已连接。
SQL> select * from plsql_profiler_data;
select * from plsql_profiler_data
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
使用案例
上述中，使用 PLSQL Developer 工具在 SCOTT 用户下，创建了 PLSQL_PROFILER 三张表和 P_TEST 存储过程，现在来测试其性能。
-
以 TEST 方式执行存储过程
-
点击 Create Profiler report
-
执行存储过程
-
点击 Profiler 查看执行情况
生成 HTML 格式的报表
profiler.sql 文件参见 metalink 的 Note:243755.1。
- 在cmd 下进入 profiler.sql 脚本目录下
C:\Windows\system32>cd c:\Oracle
- 登录需要生成 HTML 格式报表的数据库用户
c:\Oracle>sqlplus scott/tiger SQL*Plus: Release 11.2.0.3.0 Production on 星期三 1月 23 22:50:32 2019 Copyright (c) 1982, 2011, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
- 执行 profiler.sql 脚本
SQL> set linesize 150;
SQL> set pagesize 1000;
SQL> @profiler.sql
RUNID RUN_DATE RUN_COMMENT
---------- ----------------------- ------------------------------------------------------------------------
1 23-1月 -19 22:46:28 Test Script - 2019/1/23 22:46:28
Usage:
sqlplus apps/<pwd>
SQL> START profiler.sql <runid>
输入 1 的值: 1
- 当前目录下会生成一份名叫 profiler_.html 的 HTML 文件
原创文章，转载请注明出处：http://www.opcoder.cn/article/22/