DynamicPerformanceTablesnotaccessible Analysis Solution in plsqldev

In plsqldev, DynamicPerformanceTablesnotaccessible analysis solves the following problems:

In plsql dev, the Dynamic Performance Tables not accessible analysis solves this problem. I believe many of my friends who use plsql dev have encountered the following prompts: Dynamic Performance Tables not accessible, automatic Statistics Disabled for this session You can disable statistics in the pr

Dynamic Performance Tables not accessible analysis solution in plsql dev

I believe many of my friends who use plsql dev have encountered the following prompts:

Dynamic Performance Tables not accessible, Automatic Statistics Disabled for this session You can disable statistics in the preference menu, or obtanin selectpriviliges on the v $ session, v $ sesstat and v $ statname tables

1. Cause Of this prompt
Plsql dev collects user statistics during user operation, but since the user you are currently logged on to does not have permission to access the v $ session, v $ sesstat and v $ statname view, therefore, the Statistics of the current user cannot be collected, which is in conflict with the Automatic Statistics configured in plsql dev. Therefore, this prompt is displayed. test and verify:

[Oracle @ xifenfei ~] $ Sqlplus/as sysdbaSQL * Plus: Release 11.2.0.3.0 Production on Thu Nov 10 04:31:57 2011 Copyright (c) 1982,201 1, Oracle. all rights reserved. connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionssys @ XFF> create user chf identified by xifenfei; User created. sys @ XFF> grant create session, resource to chf; Grant succeeded. sys @ XFF> conn chf/xifenfeiConnected. chf @ XFF> select * from session_privs; PRIVILEGE --------------------------------------- -create sessionunlimited tablespacecreate tablecreate clustercreate sequencecreate procedurecreate triggercreate typecreate operatorcreate INDEXTYPE10 rows selected. chf @ XFF> SELECT TABLE_NAME FROM USER_TAB_PRIVS; no rows selected

Create a chf user, authorize create session, resource, no access permission for the v $ session, v $ sesstat and v $ statname view, use plsql dev to log on to and query the user_tables table (no prompt will be prompted during logon. This prompt will be displayed only when the user performs the query or related operations)

Ii. Solving Problems
According to the warning prompt, there are two ways to solve this warning.
1. Disable plsql dev statistics.
In Tools-> Preferences-> Options, remove the marker before the STIC Statistics and save
2. Authorize the user to access related views
Authorize access to v _ $ session, v _ $ sesstat, v _ $ statname. Note that you cannot directly authorize the v $ view.

Chf @ XFF> conn/as sysdbaConnected. 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/xifenfeiConnected. chf @ XFF> SELECT TABLE_NAME FROM USER_TAB_PRIVS; TABLE_NAME------------------------------V _ $ SESSIONV _ $ SESSTATV _ $ STATNAME

Iii. Problem Analysis
Through the above solution, why can I authorize access to the v $ session, v $ sesstat and v $ statname view to Statistics user information? See the following two figures.
: View User statistics using the tools-> session option in plsql dev

: Query user statistics using SQL statements

Comparing the two figures, we may find that their values are slightly different, because I first use tools to query user statistics and then use SQL to query, therefore, some of the data options will be a little bit smaller. Through analysis on the v $ session, v $ sesstat and v $ statname views, we find that plsql dev implements the statistical function through the following SQL, this further explains why plsql dev needs to grant access permissions to the v $ session, v $ sesstat and v $ statname views for collecting statistics.

Select c. NAME, B. STATISTIC #, B. value from v $ session a, V $ sesstat B, V $ STATNAME C WHERE. SID = B. sid and. AUDSID = USERENV ('sessionid') and B. STATISTIC # = C. STATISTIC # order by c. STATISTIC #;