(一) 安装:
- 用 SYS 身份登录数据库,执行脚本:%ORACLE_HOME% \RDBMS\ADMIN\profload.sql
- 创建一个用于存放信息的 schema, 并授予权限:
CREATE USER profiler IDENTIFIED BY profiler DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS; GRANT CONNECT, CREATE TABLE, CREATE SEQUENCE TO profiler;
- 用 profiler 身份登录数据库,执行脚本:%ORACLE_HOME% \RDBMS\ADMIN\profload.sql 来创建表和序列,并授予相应权限给 public 角色:
GRANT SELECT ON profiler.plsql_profiler_runnumber TO PUBLIC; GRANT SELECT, INSERT, UPDATE, DELETE ON profiler.plsql_profiler_runs TO PUBLIC; GRANT SELECT, INSERT, UPDATE, DELETE ON profiler.plsql_profiler_units TO PUBLIC; GRANT SELECT, INSERT, UPDATE, DELETE ON profiler.plsql_profiler_data TO PUBLIC;
- 用 SYS 身份登录数据库,为表和序列创建 public 同义词
CREATE PUBLIC SYNONYM plsq_profiler_runnumber FOR profiler.plsql_profiler_runnumber; CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs; CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units; CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data;
(二) 使用 DBMS_PROFILER 收集信息:
begin -- Call the procedure dbms_profiler.start_profiler('profiler'); FOR i IN 1..10000 LOOP EXECUTE IMMEDIATE 'INSERT INTO t VALUES('|| TO_CHAR(i) ||')'; END LOOP; dbms_profiler.stop_profiler; end; 可以通过下面SQL查询RUNID: SELECT runid, run_owner, run_date, run_comment FROM plsql_profiler_runs WHERE run_comment='profiler';
(三) 使用 prof.zip 格式化报表:
SQL> column RUNID format 99 SQL> column RUN_COMMENT format a30 SQL> start "C:\Documents and Settings\Dragon Luo\桌面\prof\profiler.sql" RUNID RUN_DATE RUN_COMMENT ----- ----------------------- ------------------------------ 1 20-7月 -12 14:42:42 ora Usage: sqlplus apps/<pwd> SQL> START profiler.sql <runid> 输入 1 的值: 1 生成报表文件profiler_1.html 报表样式见:C:\Documents and Settings\Dragon Luo\桌面\prof\profiler_7.html
附:PROF.ZIP包 http://files.cnblogs.com/oradragon/PROF.zip