oracle autotrace 笔记

最新推荐文章于 2024-08-01 14:10:47 发布

bjchangxiao

最新推荐文章于 2024-08-01 14:10:47 发布

阅读量230

点赞数

本文链接：https://blog.youkuaiyun.com/bjchangxiao/article/details/79553751

版权

--puzzle登录
[oracle@oel ~]$ sqlplus puzzle/puzzle

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 16 21:45:25 2018

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--报错
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL>
--sys登录，执行脚本，创建角色，赋予puzzle角色
[oracle@oel ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 16 21:46:27 2018

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

SQL> @?/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL>
SQL> set echo off
SQL>
--授权
SQL> grant plustrace to puzzle;

Grant succeeded.

SQL>
--重新登录puzzle
[oracle@oel ~]$ sqlplus puzzle/puzzle

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 16 21:47:31 2018

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--设置autotrace
SQL> set autotrace on
SQL> explain plan for select * from employees where employee_id=205;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id | Operation   | Name      | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |   987 | 63168 |     6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES |   987 | 63168 |     6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("EMPLOYEE_ID"=205)

13 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

---------------------------------------------------------------------------------------------
| Id | Operation     | Name    | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |     | 8168 | 16336 | 29   (0)| 00:00:01 |
|   1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
50 recursive calls
22 db block gets
564 consistent gets
   0 physical reads
   0 redo size
       1289 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   5 sorts (memory)
   0 sorts (disk)
13 rows processed

SQL> explain plan for select * from employees where employee_id=201;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2126936620

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

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

2 - access("EMPLOYEE_ID"=201)

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

Statistics
----------------------------------------------------------
26 recursive calls
12 db block gets
189 consistent gets
   0 physical reads
   0 redo size
       1451 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   1 sorts (memory)
   0 sorts (disk)
14 rows processed

SQL>