oracle dbms_profiler使用问题

[呆瓜呆呆]

问题描述：在使用dbms_profiler分析执行的pl/sql块的时候PLSQL_PROFILER_RUNS 、PLSQL_PROFILER_DATA、PLSQL_PROFILER_UNITS这三个表中没有完整的数据。
具体操作如下：

--安装DBMS_PROFILER包
CONNECT / AS SYSDBA
@$ORACLE_HOME/rdbms/admin/profload.sql

--创建profile表空间提供给profiler用户
DROP USER profiler CASCADE;
DROP TABLESPACE profile_tbs INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

CREATE TABLESPACE profile_tbs 
DATAFILE '/u01/app/oracle/oradata/normal/profiletbs01.dbf'
SIZE 50M;

--创建profiler用户默认表空间为profile_tbs
CREATE USER profiler
IDENTIFIED BY oracle 
DEFAULT TABLESPACE profile_tbs;

--为profiler用户授予相应的权限
GRANT CONNECT, RESOURCE TO profiler;

--为profiler用户创建共有同义词提供给所有的用户访问
DROP PUBLIC SYNONYM plsql_profiler_runs;
DROP PUBLIC SYNONYM plsql_profiler_units;
DROP PUBLIC SYNONYM plsql_profiler_data;
DROP PUBLIC SYNONYM 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;
CREATE PUBLIC SYNONYM plsql_profiler_runnumber
	FOR profiler.plsql_profiler_runnumber;

--使用profiler用户登录创建工具表，并授予共有访问权限
CONNECT profiler/oracle
@$ORACLE_HOME/rdbms/admin/proftab.sql
GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;
GRANT SELECT, INSERT, DELETE ON plsql_profiler_data TO PUBLIC;
GRANT SELECT, INSERT, DELETE ON plsql_profiler_units TO PUBLIC;
GRANT SELECT, INSERT, DELETE ON plsql_profiler_runs TO PUBLIC;

--做测试DBMS_PROFILER实验
create table tab_test (a int);

CREATE OR REPLACE PROCEDURE sp_test 
AS  
BEGIN  
	FOR I IN 1 .. 100000 LOOP  
		INSERT INTO tab_test VALUES (I);  
	END LOOP;  
	COMMIT;  
END;  
/  

DECLARE
	v_run_number integer;  
BEGIN
	--启动profiler
	v_run_number := DBMS_PROFILER.start_profiler(run_comment=>'sp_test:' || SYSDATE);
	--显示当前跟踪的运行序号(后面查询要用)
	DBMS_OUTPUT.put_line('run_number:' || v_run_number);
	--运行要跟踪的PLSQL
	sp_test;
	--停止profiler
	v_run_number := DBMS_PROFILER.stop_profiler;
END;
/

--以下是我分别查询PLSQL_PROFILER_RUNS 、PLSQL_PROFILER_DATA、PLSQL_PROFILER_UNITS表的数据信息
select runid,run_owner,run_date,run_total_time from plsql_profiler_runs;
     RUNID RUN_OWNER                        RUN_DATE  RUN_TOTAL_TIME
---------- -------------------------------- --------- --------------
         1 CHENHAO                          26-OCT-14

SQL> select * from PLSQL_PROFILER_DATA;
no rows selected

SQL> select * from PLSQL_PROFILER_UNITS;
no rows selected

以上可以看到PLSQL_PROFILER_RUNS 表的RUN_TOTAL_TIME字段没有值、PLSQL_PROFILER_DATA、PLSQL_PROFILER_UNITS表中根本就没有数据
求解决。

[呆瓜呆呆]

引用 1 楼 zlloct 的回复:

[quote=引用 楼主 ch7543658 的回复:]

查看一下包是否存在， desc dbms_profiler ; 实在不行你用sys账户试试 [/quote] 你好！我用了sys用户执行成功了

select runid, RUN_OWNER, RUN_DATE, RUN_TOTAL_TIME from PLSQL_PROFILER_RUNS;

     RUNID RUN_OWNER                        RUN_DATE  RUN_TOTAL_TIME
---------- -------------------------------- --------- --------------
         3 SYS                              27-OCT-14     1.4080E+10
		 
select * from PLSQL_PROFILER_UNITS;

     RUNID UNIT_NUMBER UNIT_TYPE                        UNIT_OWNER                       UNIT_NAME                        UNIT_TIME TOTAL_TIME     SPARE1     SPARE2
---------- ----------- -------------------------------- -------------------------------- -------------------------------- --------- ---------- ---------- ----------
         3           1 PACKAGE BODY                     SYS                              DBMS_PROFILER                    17-SEP-11          0
         3           2 ANONYMOUS BLOCK                  <anonymous>                      <anonymous>                      00-DECEMB          0
         3           3 PACKAGE BODY                     SYS                              DBMS_OUTPUT                      17-SEP-11          0
         3           4 PROCEDURE                        SYS

但是我想我一下，要如何使用其他用户执行呢？

[CT_LXL]

引用 3 楼 ch7543658 的回复:

以下的代码我已成功实践过，你可以参考一下（其中牵涉的两个SQL文件你需要在你对应的ORACLE_HOME下找到并成功执行）：

[oracle@localhost admin]$ sqlplus / as sysdba
 
SQL>@/home/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/profload.sql
 
SQL> desc dbms_profiler;
 
SQL> CREATE USER profiler IDENTIFIED BY oracle;
 
SQL> grant connect,resource to profiler;
 
SQL> CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs;
 
SQL> CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units;
 
SQL> CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data;
 
SQL> CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR profiler.plsql_profiler_runnumber;
 
SQL> conn profiler/oracle
 
SQL> @/home/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/proftab.sql
 
SQL> GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;
 
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_data TO PUBLIC;
 
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_units TO PUBLIC;
 
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_runs TO PUBLIC;
 
SQL> create table tab_test (a int);
 
 
CREATE OR REPLACE PROCEDURE sp_test
AS
BEGIN
   FOR I IN 1 .. 100
   LOOP
      INSERT INTO tab_test
        VALUES   (I);
   END LOOP;
   COMMIT;
END;
/
SQL> set serverout on
 
SQL> DECLARE
   v_run_number   integer;
   v_temp1        integer;
BEGIN
   --启动profiler
   sys.DBMS_PROFILER.start_profiler (run_number => v_run_number);
   --显示当前跟踪的运行序号(后面查询要用)
   DBMS_OUTPUT.put_line ('run_number:' || v_run_number);
   --运行要跟踪的PLSQL
   sp_test;
   --停止profiler
   sys.DBMS_PROFILER.stop_profiler;
END;
/
SQL> select runid,run_owner,run_date,run_total_time from plsql_profiler_runs;
 
 
SQL> select unit_number,unit_type,unit_owner,unit_name,unit_timestamp,total_time from plsql_profiler_units where runid = ‘上面找到的runid’ and unit_name = 'SP_TEST';
 
 
SQL> select runid,unit_number,line#,total_occur,total_time,min_time,max_time from plsql_profiler_data where runid = '上面找到的runid' and unit_number = 2;

[CT_LXL]

引用 楼主 ch7543658 的回复:

查看一下包是否存在， desc dbms_profiler ; 实在不行你用sys账户试试

[呆瓜呆呆]

引用

5楼解答

5楼正解，我用你的代码页执行成功了，并且使用其他用户也能成功的运行，谢谢你的帮助。要是能说明白都我的代码为什么发生没有数据的情况那就完美了。

[wangwei]

看步骤没有问题

[baidu_37135130]

GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC; SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_data TO PUBLIC; SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_units TO PUBLIC; SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_runs TO PUBLIC; 对表赋权限的时候报同义词循环链的错误，百度了一下，查同义词没有对象的数据库对象没有记录 求大神指点！！！！