sql performance tunning 15--怎样阅读执行计划-优快云博客

本文详细解析了SQL执行计划的生成过程及其优化方法，包括如何通过EXPLAIN命令查看执行计划、转换为树图阅读理解，以及如何利用SQL_TRACE获取实际运行时的执行计划。重点讨论了不同会话环境对执行计划的影响，并提供了获取真实执行计划的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

QL> set lines 200;

select ename,dname,grade from emp,dept,salgrade where emp.deptno=dept.deptno and emp.sal between salgrade.losal and salgrade.hisal;

Execution Plan ---------------------------------------------------------- Plan hash value: 721498669
----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 36 | 9 (23)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 36 | 9 (23)| 00:00:01 | | 2 | MERGE JOIN | | 1 | 23 | 8 (25)| 00:00:01 | | 3 | SORT JOIN | | 5 | 50 | 4 (25)| 00:00:01 | | 4 | TABLE ACCESS FULL | SALGRADE | 5 | 50 | 3 (0)| 00:00:01 | |* 5 | FILTER | | | | | | |* 6 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 | | 7 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 | |* 9 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | -----------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
5 - filter("EMP"."SAL"<="SALGRADE"."HISAL") 6 - access("EMP"."SAL">="SALGRADE"."LOSAL") filter("EMP"."SAL">="SALGRADE"."LOSAL") 9 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 30 consistent gets 0 physical reads 0 redo size 721 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk)

14 rows processed

怎么看这个执行计划呢？

可以先把计划转成树图，然后读取这个树图，以从下到上，从左到右的方式去阅读树图

但是实际上，很可能通过EXPLAIN 得出的执行计划是不准确的，通过SQL_TRACE我们可以得到实际运行时的执行计划。会话的环境对执行计划的正确性有很大影响。

测试：

会话1：

QL> create table t2

2 as

3 select * from all_objects;

Table created.

SQL> alter table t2 add constraint t2_pk primary key(object_id);

Table altered.

SQL> begin

2 dbms_stats.gather_table_stats

3 (user,'T2',

4 method_opt => 'for all columns size auto',

5 cascade => TRUE);

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> explain plan for

2 select * from t2

3 where object_id>32000;

Explained.

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

PLAN_TABLE_OUTPUT

Plan hash value: 1513984157

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 20494 | 1861K| 160 (2)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T2 | 20494 | 1861K| 160 (2)| 00:00:02 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

1 - filter("OBJECT_ID">32000)

13 rows selected.

SQL>

会话2：

SQL> alter session set optimizer_index_cost_adj=10;

Session altered.

SQL> delete from plan_table;

0 rows deleted.

SQL> explain plan for

2 select * from t2 where object_id > 32000;

Explained.

SQL> set echo off

SQL> set lines 300

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

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2609044213

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 20494 | 1861K| 50 (2)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 20494 | 1861K| 50 (2)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T2_PK | 20494 | | 4 (0)| 00:00:01 |

-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

---------------------------------------------------

2 - access("OBJECT_ID">32000)

14 rows selected.