该文介绍了如何在Oracle12c的HR数据库中分析和优化SQL查询语句。通过对比不同查询方法的执行计划，展示了如何利用执行计划的统计信息判断最优查询，并使用SQLDeveloper的优化指导工具进行优化。实验中比较了四种查询方式，得出使用部门ID过滤的查询3为最优选择。
实验1：SQL语句的执行计划分析与优化指导
实验目的
分析SQL执行计划，执行SQL语句的优化指导。理解分析SQL语句的执行计划的重要作用。
实验数据库和用户
数据库是pdborcl，用户是sys和hr
实验内容
- 对Oracle12c中的HR人力资源管理系统中的表进行查询与分析。
- 设计自己的查询语句，并作相应的分析，查询语句不能太简单。执行两个比较复杂的返回相同查询结果数据集的SQL语句，通过分析SQL语句各自的执行计划，判断哪个SQL语句是最优的。最后将你认为最优的SQL语句通过sqldeveloper的优化指导工具进行优化指导，看看该工具有没有给出优化建议。
参考
- 用户hr默认没有统计权限，打开统计信息功能autotrace时要报错，必须要向用户hr授予 v_KaTeX parse error: Expected group after '_' at position 11: sesstat, v_̲statname 和 v_$session 的选择权限。
$ sqlplus sys/123@localhost/pdborcl as sysdba
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
create role plustrace;
GRANT SELECT ON v_$sesstat TO plustrace;
GRANT SELECT ON v_$statname TO plustrace;
GRANT SELECT ON v_$mystat TO plustrace;
GRANT plustrace TO dba WITH ADMIN OPTION;
GRANT plustrace TO hr;
GRANT SELECT ON v_$sql TO hr;
GRANT SELECT ON v_$sql_plan TO hr;
GRANT SELECT ON v_$sql_plan_statistics_all TO hr;
GRANT SELECT ON v_$session TO hr;
GRANT SELECT ON v_$parameter TO hr;
- 教材中的查询语句：查询两个部门(‘IT’和’Sales’)的部门总人数和平均工资，两个查询的结果是一样的。但效率不相同。
查询1：
$sqlplus hr/123@localhost/pdborcl
set autotrace on
SELECT d.department_name,count(e.job_id)as "部门总人数",
avg(e.salary)as "平均工资"
from hr.departments d,hr.employees e
where d.department_id = e.department_id
and d.department_name in ('IT','Sales')
6 GROUP BY d.department_name;
DEPARTMENT_NAME 部门总人数 平均工资
------------------------------ ---------- ----------
IT 5 5760
Sales 34 8955.88235
执行计划
----------------------------------------------------------
Plan hash value: 3808327043
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 1 | 23 |
5 (20)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 23 |
5 (20)| 00:00:01 |
| 2 | NESTED LOOPS | | 19 | 437 |
4 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 20 | 437 |
4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 |
3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | |
0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 70 |
1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("D"."DEPARTMENT_NAME"='IT' OR "D"."DEPARTMENT_NAME"='Sales')
5 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
Note
-----
- this is an adaptive plan
统计信息
----------------------------------------------------------
180 recursive calls
0 db block gets
332 consistent gets
2 physical reads
0 redo size
815 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
2 rows processed
分析：该查询语句通过员工表employees和部门表departments来查询部门的总人数和平均工资，并按照部门名’IT’和’Sales’进行分组查询。总人数直接使用了count(员工表id)得到员工人数，平均工资使用avg(员工表salary)算出。使用了多表联查从部门中找出了目标部门然后再通过其department_id在员工表中找出该部门所有的员工。
set autotrace on
SELECT d.department_name,count(e.job_id)as "部门总人数",
avg(e.salary)as "平均工资"
FROM hr.departments d,hr.employees e
WHERE d.department_id = e.department_id
GROUP BY d.department_name
6 HAVING d.department_name in ('IT','Sales');
DEPARTMENT_NAME 部门总人数 平均工资
------------------------------ ---------- ----------
IT 5 5760
Sales 34 8955.88235
执行计划
----------------------------------------------------------
Plan hash value: 2128232041
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | 1 | 23 | 7 (2
9)| 00:00:01 |
|* 1 | FILTER | | | |
| |
| 2 | HASH GROUP BY | | 1 | 23 | 7 (2
9)| 00:00:01 |
| 3 | MERGE JOIN | | 106 | 2438 | 6 (1
7)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (
0)| 00:00:01 |
| 5 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (
0)| 00:00:01 |
|* 6 | SORT JOIN | | 107 | 749 | 4 (2
5)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMPLOYEES | 107 | 749 | 3 (
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("D"."DEPARTMENT_NAME"='IT' OR "D"."DEPARTMENT_NAME"='Sales')
6 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
11 consistent gets
5 physical reads
0 redo size
815 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
分析：该查询语句同样是通过员工表employees和部门表departments来查询部门的总人数和平均工资，并按照部门名’IT’和’Sales’进行分组查询。判断部门ID和员工ID是否对应，由HANVING确认部门名字是IT和sales来查询部门总人数和平 均工资。
由于使用了WHERE和HAVING进行了两次过滤，结果更加精准，所以该查询语句比第一条查询语句进一步优化。
查询3
set autotrace on
SELECT d.department_name,count(e.job_id) as "部门总人数",avg(e.salary) as "平均工资"
FROM hr.departments d,hr.employees e
WHERE e.department_id=d.department_id and d.department_id in
(SELECT department_id from hr.departments WHERE department_name in ('IT','Sales'))
5 group by d.department_name;
DEPARTMENT_NAME 部门总人数 平均工资
------------------------------ ---------- ----------
IT 5 5760
Sales 34 8955.88235
执行计划
----------------------------------------------------------
Plan hash value: 3808327043
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 1 | 23 |
5 (20)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 23 |
5 (20)| 00:00:01 |
| 2 | NESTED LOOPS | | 19 | 437 |
4 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 20 | 437 |
4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 |
3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | |
0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 70 |
1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("DEPARTMENT_NAME"='IT' OR "DEPARTMENT_NAME"='Sales')
5 - access("E"."DEPARTMENT_ID"="DEPARTMENT_ID")
Note
-----
- this is an adaptive plan
统计信息
----------------------------------------------------------
15 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
815 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
分析：该查询语句在查询1的基础上进行了优化，将查询的条件部门名’IT’和’Sales’换成对应部门的id进行查询，使用部门名查询对应的部门id作为子查询而得到的结果作为外层查询条件，这样来查询结果的准确性更高，查询效率也更高。
查询4：
set autotrace on
SELECT hr.departments.department_name,count(hr.employees.job_id)as "部门总人数",avg(hr.employees.salary)as "平均工资"
FROM hr.employees left join hr.departments on hr.employees.department_id=hr.departments.department_id
WHERE hr.departments.department_name in ('IT','Sales')
GROUP BY hr.departments.department_name; 2 3 4
DEPARTMENT_NAME 部门总人数 平均工资
------------------------------ ---------- ----------
IT 5 5760
Sales 34 8955.88235
执行计划
----------------------------------------------------------
Plan hash value: 3808327043
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 1 | 23 |
5 (20)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 23 |
5 (20)| 00:00:01 |
| 2 | NESTED LOOPS | | 19 | 437 |
4 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 20 | 437 |
4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 |
3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | |
0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 70 |
1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("DEPARTMENTS"."DEPARTMENT_NAME"='IT' OR
"DEPARTMENTS"."DEPARTMENT_NAME"='Sales')
5 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")
Note
-----
- this is an adaptive plan
统计信息
----------------------------------------------------------
164 recursive calls
0 db block gets
223 consistent gets
11 physical reads
0 redo size
797 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
2 rows processed
分析：在查询1的基础上，增加了左外连接查询，不必包含hr.departments表的全部信息，似乎没有什么优化之处
综上分析，查询3是最优查询sql语句
要运行 SQL 优化指导, 用户 ‘HR’ 需要系统权限 ‘ADVISOR’, ‘ADMINISTER SQL TUNING SET’, ‘SELECT ANY DICTIONARY’
521
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
