随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的主要问题之一，系统优化中一个很重要的方面就是SQL语句的优化。
大量的数据指出劣质的SQL语句和优质的SQL语句之间的速度差别可以达到上百倍，对一个系统不是简单的能实现功能就可以，而是要写出高质量的SQL语句提高系统的可用性。
案例分析
步骤1：以用户 sys 名登录,查询用户 hr 下表 departments 与表 employees 连接的数据，其代码如下：
SQL> conn sys as sysdba
输入口令:
已连接。
SQL> select e.last_name,d.department_name
2 from hr.employees e,hr.departments d
3 where e.department_id=d.department_id;
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Whalen Administration
Fay Marketing
Hartstein Marketing
Tobias Purchasing
Colmenares Purchasing
.......
已选择 106 行。
步骤2：开启追踪，其代码如下：
SQL> show autotrace
autotrace OFF
SQL> set autotrace on
步骤3：显示语句的执行计划和统计信息，其代码如下：
SQL> select e.last_name,d.department_name
2 from hr.employees e,hr.departments d
3 where e.department_id=d.department_id;
LAST_NAME DEPARTMENT_NAME
------------------------- ------------------------------
Whalen Administration
.......
已选择 106 行。
执行计划
----------------------------------------------------------
Plan hash value: 1473400139
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 106 | 2862 | 5 (20)| 00:00:01 |
| 1 | MERGE JOIN | | 106 | 2862 | 5 (20)| 00:00:01 |
| 2 |TABLE ACCESS BY INDEX ROWID| DEPARTMENTS|27 |432 | 2 (0)| 00:00:01 |
| 3 |INDEX FULL SCAN | DEPT_ID_PK| 27 | | 1 (0)| 00:00:01 |
|* 4 |SORT JOIN | | 107 | 1177 | 3 (34)| 00:00:01 |
| 5 | VIEW | index$_join$_001 | 107 | 1177 | 2 (0)| 00:00:01 |
|* 6 | HASH JOIN | | | | | |
| 7 |INDEX FAST FULL SCAN | EMP_DEPARTMENT_IX |107 |1177 |1 (0)| 00:00:01 |
| 8 |INDEX FAST FULL SCAN| EMP_NAME_IX | 107 | 1177 | 1 (0)| 00:00:01
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
6 - access(ROWID=ROWID)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
3340 bytes sent via SQL*Net to client
556 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
106 rows processed
步骤4：关闭trace自动跟踪功能，其代码如下：
SQL> set autotrace off
SQL> show autotrace
autotrace OFF
步骤5：使用explain plan命令获取执行计划，默认存放在plan_table表中，其代码如下：
SQL> explain plan set statement_id='test_project'
2 for
3 select e.last_name,d.department_name
4 from hr.employees e,hr.departments d
5 where e.department_id=d.department_id;
已解释。
步骤6：执行成功后,可以使用如下命令显示plan_table表中的所有执行计划了，其代码如下：
SQL> select id,operation,options,object_name,statement_id
2 from plan_table;
ID OPERATION OPTIONS OBJECT_NAME STATEMENT_ID
---------- -------------------- -------------------- -------------------- --
0 SELECT STATEMENT test_project
1 MERGE JOIN test_project
2 TABLE ACCESS BY INDEX ROWID DEPARTMENTS test_project
3 INDEX FULL SCAN DEPT_ID_PK test_project
4 SORT JOIN test_project
5 VIEW index$_join$_001 test_project
6 HASH JOIN test_project
7 INDEX FAST FULL SCAN EMP_DEPARTMENT_IX test_project
8 INDEX FAST FULL SCAN EMP_NAME_IX test_project
已选择 9 行。
步骤7：当以上的explain plan命令执行后，就可使用如下命令利用dbms_xplan软件包中display函数显示explain plan命令的输出结果。
SQL select plan_table_output from table dbms_xplan display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value :1473400139
|Id |Operation |Name |Rows |Bytes |Cost %CPU |Time |
---------------------------------------------------------------------
|0 |SELECT STATEMENT | |106 |2862 |5(20) | 00:00:01 |
|1 |MERGE JOIN | |106 |2862 |5(20) | 00:00:01 |
|2 |TABLE ACCESS BY INDEX ROWID|DEPARTMENTS |27 |432 |2(0) |00:00:01 |
|3 |INDEX FULL SCAN |DEPT_ID_PK |27 | |1(0) | 00：00：01 |
|* 4 |SORT JOIN | |107 |1177 |3（34） | 00 ：00 ：01 ||5 |VIEW |index$_join$_001|107 |1177 |2(0) | 00 :00 :01 |
|* 6 |HASHJOIN | | | | | |
|7 |INDEX FAST FULL SCAN |EMP_DEPARTMENT_IX|107 |1177 |1(0) | 00:00:01 |
|8 |INDEX FAST FULL SCAN |EMP_NAME_IX |107 |1177 |1(0) | 00:00:01 |
Predicate Information (identifiedbyoperationid):
---------------------------------------------------
4 - access ("E" "DEPARTMENT_ID"="D" "DEPARTMENT_ID")
filter ("E" "DEPARTMENT_ID"="D" "DEPARTMENT_ID")
6 - access (ROWID=ROWID)
已选择 22 行
步骤8：为了方便DBA工作,Oracle还提供了一个名为utlxpls.sql的脚本文件，他的功能就是显示最后解释的SQL语句的执行计划。
这个脚本文件存放在$ORACLE_HOME\RDBMS\ADMIN\文件夹中，可以使用如下命令执行这一个脚本，效果与之前完全一样。
QL @D \SOFTWARE\ORACLE19C\RDBMS\ADMIN\UTLXPLS.SQL
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
value: Plan hash1473400139
----------------------------------------------------------------------
|Id|Operation |Name |Rows |Bytes |Cost (%CPU) |Time|
----------------------------------------------------------------------
|0|SELECT STATEMENT | |106 |2862 |5(20) |00:00:01||1|MERGE JOIN | |106 |2862 |5(20) |00:00:01||2|TABLE ACCESS BY INDEX ROWID |DEPARTMENTS |27 |432 |2(0) |00:00:01||3|INDEX FULL SCAN |DEPT_ID_PK |27 | |1(0)|00:00:01|
|*4|SORT JOIN | |107 |1177 |3(34) |00:00:01|
303
|5|VIEW |index$_join$_001 |107 |1177 |2(0) |00:00:01|
|*6|HASH JOIN | | | | |||7|INDEX FAST FULL SCAN |EMP_DEPARTMENT_IX |107 |1177 |1(0)|00:00:01
|8|INDEX FAST FULL SCAN |EMP_NAME_IX |107 |1177 |1(0) |00:00:01|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access ("E" "DEPARTMENT_ID"="D" "DEPARTMENT_ID")
filter ("E" "DEPARTMENT_ID"="D" "DEPARTMENT_ID")
6 - access (ROWID=ROWID)
已选择 22 行
其实打开这个文件会发现这个脚本使用的方法其实也是调用了dbms_xplan软件包中的display（）函数。
在utlxpls.sql文件中调用display函数时还用了几个参数,完整的display（）函数调用格式如下：
select plan_table_output from
table(dbms_xplan.display('plan_table',null,'serial'));
其中:
plan_table:存放SQL与执行计划的表;
serial:不显示并行操作的信息。
步骤9：利用数据字典v$SQL获取了上面SQL语句的dsql_id，其代码如下：
注意：在查询的过程中使用where语句来限制显示输出的结果，以方便阅读。
SQL> select sql_id,sql_text
2 from v$sql
3 where sql_text like'%select e.lastname,%';
SQL_ID
----------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------
3ygg74tbfngnj
select SQL_ID,SQL_TEXT from v$SQL where SQL_TEXT like'%select e.lastname,%';
由查询结果可以发现，sql_id为3ygg74tbfngnj。
步骤10：利用sql_id调用dbms_xplay软件包中的display（）函数以显示刚刚执行过的SQL语句的执行计划。
SQL﹥colplan_table_output for a100
SQL﹥setlinesize200
304
SQL﹥selectplan_table_output
2 from table (dbms_xplan display_cursor ('3ygg74tbfngnj'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------
SQL_ID 3ygg74tbfngnj child number 0
--------------------------------------------------------
selectSQL_ID, SQL_TEXT from v$SQL where SQL_TEXT like '%selecte lastname % ';
Plan hash value 903671040
--------------------------------------------------------
Id|Operation |Name |Rows |Bytes |Cost( %CPU )|
--------------------------------------------------------
-1412069179|SELECTST ATEMENT| | | |1 (100) |
PLAN_TABLE_OUTPUT
--------------------------------------------------------
Id|Operation |Name |Rows |Bytes |Cost (%CPU) |
--------------------------------------------------------
*1|FIXED TABLE FULL|X$ KGLCURSOR_CHILD|1 | 523|0 (0) |
--------------------------------------------------------
Predicate Information (identif ied by operation id);
--------------------------------------------------------
1 - filter ("KGLNAOBJ"IS NOT NULL AND"KGLNAOBJ"LIKE '%selecte lastname % 'AND"INST_ID"
=USERENV 'INSTANCE'
已选择20行
由上面的结果了解，这两个表在连接时，最大的子表employees，居然使用的是全表扫描(fixed table full)。
参考书籍
《Oracle数据库系统管理与运维-微课视频版》
ISBN：9787302566106
作者：张立杰 主编 陈恒 陶永鹏 副主编
定价：69.9元
本书从Oracle数据库系统、管理以及运维的角度深入浅出地加以介绍，对于Oracle初学者以及Oracle相关从业人员都是适用的。本书的所有例题以及实验都是在最新的Oracle 19c版本下运行实验的。
全书共15个章节，包括Oracle系统、管理以及运维相关知识，内容包括oracle体系结构、数据库实例、存储结构、数据字典与动态性能视图、Oracle监控、数据库备份、数据库恢复、闪回技术以及数据库优化等；包括20个完整的项目案例。书中案例侧重应用性、趣味性强、分布合理、通俗易懂，使读者能够快速掌握Oracle系统、管理以及运维方面的的基础知识、方法、工具以及技巧。
张立杰
张立杰，硕士，副教授。研究方向为数据库、大数据。在各级学术期刊上发表论文10余篇，其中EI检索论文3篇，SCI检索论文1篇，计算机中文核心论文2篇。主持并完成省级项目3项；大连外国语大学校级教改项目4项、科研项目1项、校级开放课程一门。主编相关教材3部。从事Oracle专业教学十多年，为企事业员工，校企合作组织进行多次Oracle培训与讲座；受聘于Oracle Academy进行全球教师培训。
本书附有教学视频、课件、教学大纲、电子教案、教学日历、习题答案、案例源码等配套资源，可以作为大学计算机及相关专业的教材，也可以作为Oracle技术的培训教材。
扫码优惠购书
精彩推荐
Oracle数据库系统 | SQL语句执行计划 Oracle数据库系统 | 性能优化概述 Oracle数据库系统 | Oracle并发与一致性 Oracle数据库系统 | Oracle备份实例 Oracle数据库系统管理与运维 | 数据库进程 Oracle数据库系统管理与运维 | Oracle体系结构 鸿蒙开发实例｜构建轻量级智能穿戴设备用户界面 CCF CSP-J/S第一轮认证必考知识点：回溯算法 CCF CSP-J/S第一轮认证必考知识点：二值图像的最大连通块 CCF CSP-J/S第一轮认证必考知识点：哥德巴赫猜想 CCF CSP-J/S第一轮认证考纲详解 Python 韩信点兵思政案例（含优惠码） 机器学习案例︱人脸识别和人脸检测（附视频） Python ︱爬取天气预报信息（附视频） 《机器学习》实验指导书（附实验参考+代码） Python爬虫综合实战 │ 创建云起书院爬虫（附代码） Python爬虫实战 │ Email提醒（附代码） Python深度学习 │一文掌握卷积神经网络
文章转载自清华计算机学堂，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
