Oracle性能优化之普通用户使用dbms_xplan包需要有的权限

普通用户使用dbms_xplan包查看执行计划需要对v$sql、v$sql_plan、v$session及v$sql_plan_statistics_all这四个视图同时具有select权限。

         如果普通用户没有以上权限，执行SQL>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));会报错如下：

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

User has no SELECT privilege on V$SESSION

为普通用户scott授予如上四个视图select权限：

SQL>grant select on v_$sql to scott;

SQL>grant select on v_$sql_plan to scott;

SQL>grant select on v_$sql_plan_statictics_all to scott;

SQL>grant select on v_$session to scott;

再次查询执行计划：

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------SQL_ID  fv0v1agrdrjkv, child number 1

-----------------------------------------------------------------------------------------------------------------------

select * from table(dbms_xplan.display_cursor(null,null,'advanced'))

 

Plan hash value: 3713220770

 

---------------------------------------------------------------------------------------------------------------------------------

|Id|Operatio|Name|Rows|Bytes|Cost(%CPU)|Time  ---------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT |   |   |   |    29 (100)|  |    | 1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |     2 |     4 |    29   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - SEL$4A78348A / KOKBF$@SEL$E112F6F0

 

Outline Data

-------------

 

  /*+

 

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

      DB_VERSION('11.2.0.1')

      ALL_ROWS

      FORCE_XML_QUERY_REWRITE

      XML_DML_RWT_STMT

      XMLINDEX_REWRITE

      XMLINDEX_REWRITE_IN_SELECT

      NO_COST_XML_QUERY_REWRITE

      OUTLINE_LEAF(@"SEL$4A78348A")

 

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------

      MERGE(@"SEL$E112F6F0")

      OUTLINE(@"SEL$1")

      OUTLINE(@"SEL$E112F6F0")

      FULL(@"SEL$4A78348A" "KOKBF$"@"SEL$E112F6F0")

      END_OUTLINE_DATA

  */

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - VALUE(A0)[300]

 

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------

 

Note

-----

   - cardinality feedback used for this statement

 

49 rows selected.

注意：授予普通用户dba权限也得授予如上四个视图的权限才可以普通用户查看执行计划。