数据库准备

在阅读本之前请读者安装好 Oracle 数据库并且配置好示例账户 Scott，因为本书均以 Scott 账户进行讲解。推荐读者安装与本书相同版本的数据库进行测试，具有专研精神的读者请安装好 Oracle12c 进行对比实验，这样一来，你将发现 Oracle12c CBO 的一些新特征。本书使用的版本是 Oracle11gR2。

这里我装在Docker上安装了Oracle12c，安装过程参考Docker 安装 Oracle12c

SQL> select * from v$version where rownum=1;
BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> show user
USER is "SYS"
SQL> grant dba to scott;
Grant succeeded.
SQL> alter user scott account unlock;
User altered.
SQL> alter user scott identified by tiger;
User altered. 
SQL> conn scott/tiger
Connected.
SQL> create table test as select * from dba_objects;
Table created.

SCOTT账户执行SQL如下：

grant dba to scott;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
CREATE TABLE test AS SELECT * FROM DBA_OBJECTS;
SELECT * FROM TEST;
grant select,insert,update,delete,all on sys.user$ to scott;
SELECT * FROM sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r;

查看执行计划

具体参见
获取执行计划常用方法

DBeaver Simple Plan View

You can click the rows of the execution plan to see their details (statistics) in the panels below and to the right of the plan.
To reevaluate the plan, click the Reevaluate button ().
To see the source script on which the plan is based, click the View Source button ().

Oracle SQL语句的诊断工具

使用autotrace功能时，oracle启用了两个session。

一个用来执行SQL。另一个用来记录执行计划和输出结果。

set autot[race] trace[only];
--关闭跟踪执行计划和统计信息功能（默认关闭）。
SQL> set autotrace off;
--执行计划和统计信息都显示
SQL> set autotrace on ;
--只显示执行计划和统计信息，不显示sql执行结果。
SQL> set autotrace traceonly;
--只显示执行计划
SQL> set autotrace on explain;
--只显示统计信息
SQL> set autotrace on statistics;

注意：set autotrace on is a SQL*Plus client command. You can’t use it in PL/SQL. 就是说Dbeaver用不了这个工具

SQLPlus 默认可以使用autotrace，如果不行，请参考：

参考：https://docs.oracle.com/database/121/SQPUG/ch_eight.htm#SQPUG539

You can control the report by setting the AUTOTRACE system variable.

To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you. DBA privileges are required to grant the PLUSTRACE role.

1. Creating a PLAN_TABLE

Run the following commands from your SQL*Plus session to create the PLAN_TABLE in the HR schema:

CONNECT HR 
@$ORACLE_HOME/rdbms/admin/utlxplan.sql 
Table created.

1. Creating the PLUSTRACE Role

Run the following commands from your SQL*Plus session to create the PLUSTRACE role and grant it to the DBA:

CONNECT / AS SYSDBA 
@$ORACLE_HOME/sqlplus/admin/plustrce.sql

drop role plustrace;
Role dropped.

create role plustrace;
Role created.

grant plustrace to dba with admin option;
Grant succeeded.

1. Granting the PLUSTRACE Role

Run the following commands from your SQL*Plus session to grant the PLUSTRACE role to the HR user:

CONNECT / AS SYSDBA 
GRANT PLUSTRACE TO HR; 
Grant succeeded.