当前位置: 代码迷 >> 综合 >> Oracle 12中启用Plustrace角色和设置
  详细解决方案

Oracle 12中启用Plustrace角色和设置

热度:17   发布时间:2024-01-15 20:08:02.0

在Oracle 12c中启用Plustrace角色和设置

 

首先进入到Oracle的安装目录下:

\app\SDSC\virtual\product\12.2.0\dbhome_1\rdbms\admin

找到文件:utlxplan.sql

以SYS用户身份,登录数据库系统,执行脚本:

@?\app\SDSC\virtual\product\12.2.0\dbhome_1\rdbms\admin\utlxplan.sql;

为了方便期间,创建一个同义词:

create public synonym plan_table for plan_table;

我们可以把这个表的权限赋给我们想要给的用户,也可以给public这个角色,就可以使用sql*plus进程跟踪。

SQL> grant all on plan_table to public;

创建plustrace的角色,进入到目录 \app\SDSC\virtual\product\12.2.0\dbhome_1\sqlplus\admin中,找到 plustrce.sql文件,并执行:

SQL>@?\app\SDSC\virtual\product\12.2.0\dbhome_1\sqlplus\admin\plustrce.sql;

执行下面的授权:

SQL> grant plustrace to public;

这样,在SQL Plus中进行设置时,就好了。

SQL> set timing on;
SQL> set autotrace on;
SQL> select sum(o.quantity)2  from order_items o, product_information p3  where o.product_id = p.product_id4  and o.product_id < 10001;SUM(O.QUANTITY)
---------------已选择 1 行。已用时间:  00: 00: 00.01执行计划
----------------------------------------------------------
Plan hash value: 3419397814----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |    26 |            |          |
|*  2 |   TABLE ACCESS FULL| ORDER_ITEMS |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - filter("O"."PRODUCT_ID"<10001)统计信息
----------------------------------------------------------0  recursive calls0  db block gets0  consistent gets0  physical reads0  redo size548  bytes sent via SQL*Net to client607  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)1  rows processedSQL>

关于 Autotrace的设置:

- SET AUTOTRACE ON;

- SET AUTOTRACE TRACEONLY;

- SET AUTOTRACE OFF;

- SET AUTOTRACE ON EXPLAIN;

- SET AUTOTRACE ON STATISTICS;

 

(完)