Oracle 11g 非dba用户 如何使用 set autotrace 功能

Oracle 11g 非dba用户 如何使用 set autotrace 功能

![执行set autotrace traceonly报错]
SQL> set autotrace
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autotrace traceonly
SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色
SP2-0611: 启用 STATISTICS 报告时出错
遇到这种情况该怎么解决呢？
遇到这种情况是因为二点原因：一是跟角色plustrace有关，二是跟用户对于特定视图的权限有关。
我们来看下如何解决，按顺序输入如下代码：
首先我们登陆dba用户，给指定的非dba用户授权

E:\oracle\app\b\product\11.2.0\dbhome_1\sqlplus\admin>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 3月 17 21:34:35 2020
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant select on v_$sesstat to scott;
授权成功。
SQL> grant select on v_$statname to scott;
授权成功。
SQL> grant select on v_$mystat to scott;
授权成功。

授权成功后，输入
SQL> exit--退出dba用户
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

再次登陆所授权的用户，这里我登陆的是bzj普通用户。

E:\oracle\app\b\product\11.2.0\dbhome_1\sqlplus\admin>sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 3月 17 21:37:43 2020
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

连接成功后我们就可以使用set autotrace了。如下代码：

SQL> set autotrace
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autotrace traceonly;--这里我只显示执行计划
SQL> select * from bzj where name='lisi';
执行计划
----------------------------------------------------------
Plan hash value: 2570142085

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

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
ime     |

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

|   0 | SELECT STATEMENT            |           |     1 |    34 |     2   (0)| 0
0:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| BZJ       |     1 |    34 |     2   (0)| 0
0:00:01 |

|*  2 |   INDEX RANGE SCAN          | B_NAME_IX |     1 |       |     1   (0)| 0
0:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME"='lisi')

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        674  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

这里的执行计划看起来有点乱，我们可以美化一下，代码如下：
输入语句：set lines 300;

SQL> set lines 300;
SQL> /

执行计划
----------------------------------------------------------
Plan hash value: 2570142085

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    34 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BZJ       |     1 |    34 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | B_NAME_IX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME"='lisi')

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        674  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

那么这样就好看多了。
感谢大家！