plsql dev中Dynamic Performance Tables not accessible分析解决

相信很多使用plsql dev的朋友多遇到过类此如下面的提示:

Dynamic Performance Tables not accessible, 
Automatic Statistics Disabled for this session 

You can disable statistics in the preference menu,or obtanin select 
priviliges on the v$session,v$sesstat and v$statname tables 

一、产生该提示原因
plsql dev在用户运行过程中,要收集用户统计信息,但是由于你现在登录的用户没有访问v$session,v$sesstat and v$statname视图的权限,所以不能收集当前用户的统计信息,和plsql dev工具中配置的Automatic Statistics相冲突,所以就出现了这个提示,试验验证:

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 10 04:31:57 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@XFF>create user chf identified by xifenfei;

User created.

sys@XFF>grant create session,resource to chf;

Grant succeeded.

sys@XFF>conn chf/xifenfei
Connected.
chf@XFF>select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 rows selected.

chf@XFF>SELECT TABLE_NAME FROM USER_TAB_PRIVS;

no rows selected

创建一个chf用户,授权create session,resource,无v$session,v$sesstat and v$statname视图访问权限,使用plsql dev登录并查询user_tables表(登录时不会提示,只有用户执行了查询或者相关类此操作时候才会提示)
Dynamic Performance Tables not accessible

二、解决问题
根据警告提示,可以有两种方法解决这种警告
1、关闭plsql dev统计功能
在 Tools->Preferences->Options里 把Automatic Statistics前的那个勾子去掉,保存
2、给访问用户授权访问相关视图
授权访问v_$session,v_$sesstat,v_$statname,注意不能直接对v$视图进行授权

chf@XFF>conn / as sysdba
Connected.
sys@XFF>grant select on v_$session to chf;

Grant succeeded.

sys@XFF>grant select on v_$sesstat to chf;

Grant succeeded.

sys@XFF>grant select on v_$statname to chf;

Grant succeeded.

sys@XFF>conn chf/xifenfei
Connected.

chf@XFF>SELECT TABLE_NAME FROM USER_TAB_PRIVS;

TABLE_NAME
------------------------------
V_$SESSION
V_$SESSTAT
V_$STATNAME

三、问题分析
通过上面的解决方法,为什么授权访问v$session,v$sesstat and v$statname视图就可以Statistics用户的信息了呢?请见下面的两张图
图1:通过plsql dev中的tools–>session选项看用户统计信息
通过plsql dev中的session选项看用户统计信息
图2:通过sql语句查询用户统计信息
通过sql语句查询用户统计信息
通过两张图的比较可能会发现,他们的数值有一点点出入,那是因为我先通过tools查询出用户统计信息,再通过sql查询,所以图1中的数据有些选项会比图2小那么一点点,通过对v$session,v$sesstat and v$statname视图分析,发现其实plsql dev就是通过下面sql实现统计功能,也从而进一步说明了,为什么plsql dev收集统计信息需要对v$session,v$sesstat and v$statname视图授于访问权限

SELECT C.NAME, B.STATISTIC#, B.VALUE
  FROM V$SESSION A, V$SESSTAT B, V$STATNAME C
 WHERE A.SID = B.SID
   AND A.AUDSID = USERENV('SESSIONID')
   AND B.STATISTIC# = C.STATISTIC#
   ORDER BY C.STATISTIC#;

深入分析数据库版本相关视图

1、dba_registry视图

SQL> set line 200
SQL> col comp_name for a35 
SQL> col version for a12
SQL> col status for a6
SQL> select comp_name, version, status from dba_registry;

COMP_NAME                           VERSION      STATUS
----------------------------------- ------------ ------
Spatial                             10.2.0.5.0   VALID
Oracle interMedia                   10.2.0.5.0   VALID
OLAP Catalog                        10.2.0.5.0   VALID
Oracle Enterprise Manager           10.2.0.5.0   VALID
Oracle XML Database                 10.2.0.5.0   VALID
Oracle Text                         10.2.0.5.0   VALID
Oracle Expression Filter            10.2.0.5.0   VALID
Oracle Rule Manager                 10.2.0.5.0   VALID
Oracle Workspace Manager            10.2.0.5.0   VALID
Oracle Data Mining                  10.2.0.5.0   VALID
Oracle Database Catalog Views       10.2.0.5.0   VALID
Oracle Database Packages and Types  10.2.0.5.0   VALID
JServer JAVA Virtual Machine        10.2.0.5.0   VALID
Oracle XDK                          10.2.0.5.0   VALID
Oracle Database Java Packages       10.2.0.5.0   VALID
OLAP Analytic Workspace             10.2.0.5.0   VALID
Oracle OLAP API                     10.2.0.5.0   VALID

17 rows selected.

SQL> select dbms_metadata.get_ddl('VIEW','DBA_REGISTRY','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','DBA_REGISTRY','SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_REGISTRY" ("COMP_ID", "COMP_NAME", "VERSION", "STATUS", "MODIFIED", "NAMESPAC
E", "CONTROL", "SCHEMA", "PROCEDURE", "STARTUP", "PARENT_ID", "OTHER_SCHEMAS") A
S
  SELECT r.cid, r.cname, r.version,
       SUBSTR(dbms_registry.status_name(r.status),1,11),
       TO_CHAR(r.modified,'DD-MON-YYYY HH24:MI:SS'),
       r.namespace, i.name, s.name, r.vproc,
       DECODE(bitand(r.flags,1),1,'REQUIRED',NULL), r.pid,
       dbms_registry.schema_list_string(r.cid)
FROM registry$ r, user$ s, user$ i
WHERE r.schema# = s.user# AND r.invoker#=i.user#

SQL> DESC registry$
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 CID                                                   NOT NULL VARCHAR2(30)
 CNAME                                                          VARCHAR2(255)
 SCHEMA#                                               NOT NULL NUMBER
 INVOKER#                                              NOT NULL NUMBER
 VERSION                                                        VARCHAR2(30)
 STATUS                                                NOT NULL NUMBER
 FLAGS                                                 NOT NULL NUMBER
 MODIFIED                                                       DATE
 PID                                                            VARCHAR2(30)
 BANNER                                                         VARCHAR2(80)
 VPROC                                                          VARCHAR2(61)
 DATE_INVALID                                                   DATE
 DATE_VALID                                                     DATE
 DATE_LOADING                                                   DATE
 DATE_LOADED                                                    DATE
 DATE_UPGRADING                                                 DATE
 DATE_UPGRADED                                                  DATE
 DATE_DOWNGRADING                                               DATE
 DATE_DOWNGRADED                                                DATE
 DATE_REMOVING                                                  DATE
 DATE_REMOVED                                                   DATE
 NAMESPACE                                             NOT NULL VARCHAR2(30)
 ORG_VERSION                                                    VARCHAR2(30)
 PRV_VERSION                                                    VARCHAR2(30)

SQL> SELECT BANNER,VERSION,modified,prv_version FROM SYS.registry$;

BANNER                                                                           VERSION      MODIFIED            PRV_VERSION
-------------------------------------------------------------------------------- ------------ ------------------- ----------------
Oracle Database Catalog Views Release 10.2.0.5.0 - 64bi                          10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Database Packages and Types Release 10.2.0.5.0 - Production               10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Workspace Manager Release 10.2.0.5.0 - Production                         10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.3
JServer JAVA Virtual Machine Release 10.2.0.5.0 - Production                     10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle XDK Release 10.2.0.5.0 - Production                                       10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Database Java Packages Release 10.2.0.5.0 - Production                    10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Expression Filter Release 10.2.0.5.0 - Production                         10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Data Mining Release 10.2.0.5.0 - Production                               10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Text Release 10.2.0.5.0 - Production                                      10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle XML Database Release 10.2.0.5.0 - Production                              10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle Rule Manager Release 10.2.0.5.0 - Production                              10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle interMedia Release 10.2.0.5.0 - Production                                10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
OLAP Analytic Workspace Release 10.2.0.5.0 - Production                          10.2.0.5.0   2011-11-03 14:07:34 10.2.0.4.0
Oracle OLAP API Release 10.2.0.5.0 - Production                                  10.2.0.5.0   2011-11-03 14:07:35 10.2.0.4.0
OLAP Catalog Release 10.2.0.5.0 - Production                                     10.2.0.5.0   2011-11-03 14:07:35 10.2.0.4.0
Spatial Release 10.2.0.5.0 - Production                                          10.2.0.5.0   2011-11-03 14:07:35 10.2.0.4.0
Oracle Enterprise Manager Release 10.2.0.5.0 - Production                        10.2.0.5.0   2011-11-02 17:23:47 10.2.0.4.0

17 rows selected.

2、v$version或者PRODUCT_COMPONENT_VERSION视图

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> col product for a35
SQL> col product for a40
SQL> select * from PRODUCT_COMPONENT_VERSION;

PRODUCT                                  VERSION      STATUS
---------------------------------------- ------------ --------------
NLSRTL                                   10.2.0.5.0   Production
Oracle Database 10g Enterprise Edition   10.2.0.5.0   64bi
PL/SQL                                   10.2.0.5.0   Production
TNS for Linux:                           10.2.0.5.0   Production

SQL> set long 1000
SQL> set pages 0
SQL> select dbms_metadata.get_ddl('VIEW','PRODUCT_COMPONENT_VERSION','SYS') FROM DUAL;

CREATE OR REPLACE FORCE VIEW "SYS"."PRODUCT_COMPONENT_VERSION" ("PRODUCT", "VERSION", "STATUS") AS
(select
substr(banner,1, instr(banner,'Version')-1),
substr(banner, instr(banner,'Version')+8,
instr(banner,' - ')-(instr(banner,'Version')+8)),
substr(banner,instr(banner,' - ')+3)
from v$version
where instr(banner,'Version') > 0
and
((instr(banner,'Version') <   instr(banner,'Release')) or
instr(banner,'Release') = 0))
union
(select
substr(banner,1, instr(banner,'Release')-1),
substr(banner, instr(banner,'Release')+8,
instr(banner,' - ')-(instr(banner,'Release')+8)),
substr(banner,instr(banner,' - ')+3)
from v$version
where instr(banner,'Release') > 0
and
instr(banner,'Release') <   instr(banner,' - '))


SQL> COL object_name for a20
SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_objects where object_name='V$VERSION';

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
PUBLIC                         V$VERSION            SYNONYM

SQL> SELECT TABLE_OWNER,TABLE_NAME FROM dba_synonyms a WHERE a.synonym_name='V$VERSION';

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYS                            V_$VERSION

SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_objects where object_name='V_$VERSION';

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
SYS                            V_$VERSION           VIEW

SQL> select dbms_metadata.get_ddl('VIEW','V_$VERSION','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','V_$VERSION','SYS')
-----------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."V_$VERSION" ("BANNER") AS
  select "BANNER" from v$version

SQL> select * from v$fixed_table where name LIKE '%V%VERSION%';

NAME                            OBJECT_ID TYPE   TABLE_NUM
------------------------------ ---------- ----- ----------
GV$VERSION                     4294951314 VIEW       65537
V$VERSION                      4294951045 VIEW       65537

SQL>  COL VIEW_DEFINITION FOR A80
SQL> select * from v$fixed_view_definition where view_name='V$VERSION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ ------------------------------------------------------------------
V$VERSION                      select  BANNER from GV$VERSION where inst_id = USERENV('Instance')

SQL> select * from v$fixed_view_definition where view_name='GV$VERSION';

VIEW_NAME                      VIEW_DEFINITION
------------------------------ -------------------------------------------------------
GV$VERSION                     select inst_id, banner from x$version

SQL> DESC x$version
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------
 ADDR                                                           RAW(8)
 INDX                                                           NUMBER
 INST_ID                                                        NUMBER
 BANNER                                                         VARCHAR2(64)

SQL> SET LINE 200
SQL> SELECT * FROM x$version;

ADDR                   INDX    INST_ID BANNER
---------------- ---------- ---------- ----------------------------------------------------------------
00002AB64240D028          0          1 Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
00002AB64240D028          1          1 PL/SQL Release 10.2.0.5.0 - Production
00002AB64240D028          2          1 CORE     10.2.0.5.0      Production
00002AB64240D028          3          1 TNS for Linux: Version 10.2.0.5.0 - Production
00002AB64240D028          4          1 NLSRTL Version 10.2.0.5.0 - Production

3、查看v$instance视图

SQL> select version from v$instance;

VERSION
------------
10.2.0.5.0

--通过同v$version同样操作,得出如下语句
 SELECT KS.INST_ID,
        KSUXSINS,
        KSUXSSID,
        KSUXSHST,
        KSUXSVER,
        KSUXSTIM,
        DECODE(KSUXSSTS,
               0,
               'STARTED',
               1,
               'MOUNTED',
               2,
               'OPEN',
               3,
               'OPEN MIGRATE',
               'UNKNOWN'),
        DECODE(KSUXSSHR, 0, 'NO', 1, 'YES', 2, NULL),
        KSUXSTHR,
        DECODE(KSUXSARC, 0, 'STOPPED', 1, 'STARTED', 'FAILED'),
        DECODE(KSUXSLSW,
               0,
               NULL,
               2,
               'ARCHIVE LOG',
               3,
               'CLEAR LOG',
               4,
               'CHECKPOINT',
               5,
               'REDO GENERATION'),
        DECODE(KSUXSDBA, 0, 'ALLOWED', 'RESTRICTED'),
        DECODE(KSUXSSHP, 0, 'NO', 'YES'),
        DECODE(KVITVAL,
               0,
               'ACTIVE',
               2147483647,
               'SUSPENDED',
               'INSTANCE RECOVERY'),
        DECODE(KSUXSROL,
               1,
               'PRIMARY_INSTANCE',
               2,
               'SECONDARY_INSTANCE',
               'UNKNOWN'),
        DECODE(QUI_STATE,
               0,
               'NORMAL',
               1,
               'QUIESCING',
               2,
               'QUIESCED',
               'UNKNOWN'),
        DECODE(BITAND(KSUXSDST, 1), 0, 'NO', 1, 'YES', 'NO')
   FROM X$KSUXSINST KS, X$KVIT KV, X$QUIESCE QU
  WHERE KVITTAG = 'kcbwst';

SQL> set line 90
SQL> desc X$KSUXSINST;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- --------------
 ADDR                                                           RAW(8)
 INDX                                                           NUMBER
 INST_ID                                                        NUMBER
 KSUXSINS                                                       NUMBER
 KSUXSSID                                                       VARCHAR2(16)
 KSUXSHST                                                       VARCHAR2(64)
 KSUXSVER                                                       VARCHAR2(17)
 KSUXSTIM                                                       DATE
 KSUXSSTS                                                       NUMBER
 KSUXSSHR                                                       NUMBER
 KSUXSTHR                                                       NUMBER
 KSUXSARC                                                       NUMBER
 KSUXSLSW                                                       NUMBER
 KSUXSDBA                                                       NUMBER
 KSUXSSHP                                                       NUMBER
 KSUXSSCN                                                       VARCHAR2(16)
 KSUXSROL                                                       NUMBER
 KSUXSDST                                                       NUMBER

SQL> SELECT  KSUXSVER FROM SYS.X$KSUXSINST;

KSUXSVER
-----------------
10.2.0.5.0