获取执行计划的常用方法
scott@orclpdb1:orclcdb> set autot
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
scott@orclpdb1:orclcdb> set autot on
scott@orclpdb1:orclcdb> set autot trace
scott@orclpdb1:orclcdb> set autot trace exp
scott@orclpdb1:orclcdb> set autot trace stat
scott@orclpdb1:orclcdb> set autot off
[root@MaxwellDBA ~]# su - oracle
Last login: Tue Oct 18 19:46:20 CST 2022 on pts/0
[oracle@MaxwellDBA ~]$ sqlplus sys/sys as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 19 07:43:48 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
sys@cdb$root:orclcdb> ALTER SESSION SET CONTAINER=ORCLPDB1;
Session altered.
sys@cdb$root:orclcdb> conn SCOTT/TIGER@ORCLPDB1;
Connected.
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> set autot
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
scott@orclpdb1:orclcdb> set lines 200 pages 200
scott@orclpdb1:orclcdb> set autot on
scott@orclpdb1:orclcdb> select count(*) from emp;
COUNT(*)
----------
14
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2937609675
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
36 recursive calls
0 db block gets
56 consistent gets
6 physical reads
0 redo size
550 bytes sent via SQL*Net to client
386 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
scott@orclpdb1:orclcdb> set autot trace
scott@orclpdb1:orclcdb> select count(*) from emp;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2937609675
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
550 bytes sent via SQL*Net to client
386 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
scott@orclpdb1:orclcdb> set autot trace exp
scott@orclpdb1:orclcdb> select count(*) from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2937609675
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------
scott@orclpdb1:orclcdb> set autot trace stat
scott@orclpdb1:orclcdb> select count(*) from emp;
1 row selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
550 bytes sent via SQL*Net to client
386 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
scott@orclpdb1:orclcdb> set autot off
scott@orclpdb1:orclcdb> select count(*) from emp;
COUNT(*)
----------
14
1 row selected.
scott@orclpdb1:orclcdb>
- recursive calls(递归调用次数,当一个SQL第一次执行时,是属于硬解析,recursive calls值大于0, 接下来的执行则属于软解析,recursive calls的值会等于0. Note: 若SQL语句中有自定义函数,recursive calls永远会大于0,循环递归调用多少次自定义函数,则recursive calls就会等于调用的次数)
- db block gets (表示数据块发生变化的个数,通常只有DML以及延迟块清除都会发生变化,调用CLOB函数也可能会发生变化, 查询语句不会发生变化,默认为0)
- consistent gets(逻辑读,单位是块。逻辑读越少,性能越好。应当在SQL优化时尽量减少逻辑读。Note:逻辑读并不是衡量sql快慢的唯一标准,需要结合I/O综合判断)
如何通过逻辑读判断一个sql语句有很大的优化空间?
SQL中的逻辑读远大于所有表的段大小之和(设想各个表都走全表扫描,表关联方式是HASH JOIN就存在很大的优化空间)
- physical reads (从磁盘读取数据块,若表数据被缓存在buffer cache中,physical reads为0)
- redo size (产生了多少字节的重做日志,通常只有DML以及延迟块清除都会产生redo size,查询语句不产生redo size)
- bytes sent via SQL*Net to client (从数据库向客户端发送字节数据)
- bytes received via SQL*Net from client(数据库从客户端接收字节数据)
- SQL*Net roundtrips to/from client (数据库与客户端的交互次数,可以通过设置arraysize减少交互次数)
- sorts (memory) 内存排序
- sorts (disk) 磁盘排序
- rows processed(多少行数据被处理(返回多少行数据),可以通过被处理的数据行数,判断SQL语句走的是HASH JOIN 还是嵌套循环,若rows processed很大,一般走的是HASH JOIN , 若rows processed 很小,一般走的是嵌套循环。)
使用EXPLAIN PLAN FOR 查看执行计划
使用explain plan for 查看执行计划,用法如下:
explain plan for SQL sentence;
select * from table(dbms_xplan.display);
explain plan for select ename,deptno
from emp
where deptno in (select deptno from dept where LOC='CHICAGO');
select * from table(dbms_xplan.display);
scott@orclpdb1:orclcdb> explain plan for select ename,deptno
2 from emp
3 where deptno in (select deptno from dept where LOC='CHICAGO');
Explained.
scott@orclpdb1:orclcdb> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 100 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 5 | 100 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 126 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("LOC"='CHICAGO')
4 - access("DEPTNO"="DEPTNO")
filter("DEPTNO"="DEPTNO")
19 rows selected.
scott@orclpdb1:orclcdb>
查看高级(ADVANCED)执行计划,用法如下。
explain plan for SQL语句
select * from table(dbms_xplan.display(NULL,NULL,'advanced -projection'));
scott@orclpdb1:orclcdb> explain plan for select ename,deptno
2 from emp
3 where deptno in (select deptno from dept where LOC='CHICAGO');
Explained.
scott@orclpdb1:orclcdb> select * from table(dbms_xplan.display(NULL,NULL,'advanced -projection'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 100 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 5 | 100 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 126 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / DEPT@SEL$2
3 - SEL$5DA710D3 / DEPT@SEL$2
5 - SEL$5DA710D3 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_MERGE(@"SEL$5DA710D3" "EMP"@"SEL$1")
LEADING(@"SEL$5DA710D3" "DEPT"@"SEL$2" "EMP"@"SEL$1")
FULL(@"SEL$5DA710D3" "EMP"@"SEL$1")
INDEX(@"SEL$5DA710D3" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
UNNEST(@"SEL$2")
OUTLINE_LEAF(@"SEL$5DA710D3")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("LOC"='CHICAGO')
4 - access("DEPTNO"="DEPTNO")
filter("DEPTNO"="DEPTNO")
Query Block Registry:
---------------------
<q o="2"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMP]]></t><s><![CDATA[SEL$1]]
></s></h></f></q>
<q o="2"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[DEPT]]></t><s><![CDATA[SEL$2]
]></s></h></f></q>
<q o="19" f="y" h="y"><n><![CDATA[SEL$5DA710D3]]></n><p><![CDATA[SEL$1]]></p><i>
<o><t>SQ</t><v><![CDATA[SEL$2]]></v></o></i><f><h><t><![CDATA[EMP]]></t><s><![CD
ATA[SEL$1]]></s></h><h><t><![CDATA[DEPT]]></t><s><![CDATA[SEL$2]]></s></h></f></
q>
60 rows selected.
scott@orclpdb1:orclcdb>
查看带有A-TIME的执行计划
查看带有A-TIME的执行计划的用法如下:
alter session set statistics_level=all;
或者 SQL中添加hint/*+ gather_plan_statistics */
运行完SQL后,获取带有A-TIME的执行计划。
select * from table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select /*+ gather_plan_statistics full(test) */ count(*) from test where owner='SYS';
COUNT(*)
----------
52430
1 row selected.
scott@orclpdb1:orclcdb> select * from table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fswg73p1zmvqu, child number 0
-------------------------------------
select /*+ gather_plan_statistics full(test) */ count(*) from test
where owner='SYS'
Plan hash value: 1950795681
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 1428 | 1427 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 1428 | 1427 |
|* 2 | TABLE ACCESS FULL| TEST | 1 | 52430 | 52430 |00:00:00.01 | 1428 | 1427 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
20 rows selected.
scott@orclpdb1:orclcdb>
- Starts(这个语句执行的次数)
- E-Rows(优化器估算的行数,普通执行计划中的Rows)
- A-Rows(真实的行数)
- A-Time(累加的总时间)
- Buffers(累加的逻辑读)
- Reads(累加的物理读)
PLAN_TABLE是一个会话级的临时表,里面的执行计划并不是SQL真实的执行计划,它只是优化器估算出来的。 真实的执行计划不应该是估算的,应该是真正执行过的。SQL执行过的执行计划存在于共享池中,具体存在于数据字典V$SQL_PLAN中,带有A-TIME的执行计划来自于V$SQL_PLAN,是真实的执行计划。而通过AUTOTRACE或EXPLAIN PLAN FOR 获取的执行计划只是优化器估算获得的执行计划。
原因在于: AUTOTRACE获取的执行计划来自于PLAN_TABLE,而非来自共享池中的V$SQL_PLAN.
查看正在执行的SQL的执行计划
select * from table(dbms_xplan.display_cursor('sql_id',child_number));
select a.sid,a.event,a.sql_id,a.sql_child_number,b.sql_text
from v$session a,v$sql b
where a.sql_address = b.address
and a.sql_hash_value = b.hash_value
and a.sql_child_number = b.child_number
order by 1 desc;
scott@orclpdb1:orclcdb> create table a as select * from dba_objects;
Table created.
scott@orclpdb1:orclcdb> create table b as select * from dba_objects;
Table created.
scott@orclpdb1:orclcdb> select count(*) from a,b where a.owner= b.owner;
COUNT(*)
----------
2903554802
1 row selected.
scott@orclpdb1:orclcdb> select * from table(dbms_xplan.display_cursor('7q09xbqhyr9zz',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7q09xbqhyr9zz, child number 0
-------------------------------------
select count(*) from a,b where a.owner= b.owner
Plan hash value: 1397777030
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 1396 (100)| |
| 1 | SORT AGGREGATE | | 1 | 10 | | | |
|* 2 | HASH JOIN | | 142M| 1356M| 1224K| 1396 (35)| 00:00:01 |
| 3 | TABLE ACCESS FULL| A | 73522 | 358K| | 398 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL| B | 73523 | 358K| | 398 (1)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OWNER"="B"."OWNER")
21 rows selected.
scott@orclpdb1:orclcdb>
如何通过查看执行计划后建立相应的索引?
scott@orclpdb1:orclcdb> explain plan for select e.ename,e.job,d.dname
2 from emp e, dept d
where e.deptno = d.deptno
4 and e.sal < 2000;
Explained.
scott@orclpdb1:orclcdb> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 272 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 8 | 272 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 8 | 168 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 8 | 168 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
5 - filter("E"."SAL"<2000)
19 rows selected.
scott@orclpdb1:orclcdb>
执行计划分为两部分: Plan hash value 和 Predicate Information之间这部分主要是表的访问路径以及表的连接方式。
scott@orclpdb1:orclcdb> set autot trace
scott@orclpdb1:orclcdb> select /*+ index(test) */ * from test where object_name like 'V_%' and owner='SCOTT';no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3011311971-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 132 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 132 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OWNER | 30 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("OBJECT_NAME" LIKE 'V_%')
2 - access("OWNER"='SCOTT')
Statistics
----------------------------------------------------------
132 recursive calls
0 db block gets
398 consistent gets
30 physical reads
0 redo size
2375 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
72 sorts (memory)
0 sorts (disk)
0 rows processed
TABLE ACCESS BY INDEX ROWID BATCHED 前面有 “*” 号,表示回表再过滤。回表再过滤说明数据没有在索引中过滤干净。当TABLE ACCESS BY INDEX ROWID BATCHED前面有 “*”号时,可以将“*”号下面的过滤条件包含在索引中,这样可以减少回表次数,提升查询性能。
scott@orclpdb1:orclcdb> create index idx_ownername on test(owner,object_name);
Index created.
scott@orclpdb1:orclcdb> select /*+ index(test) */ * from test where object_name like 'V_%' and owner='SCOTT';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3011311971-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 132 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 132 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OWNER | 30 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("OBJECT_NAME" LIKE 'V_%')
2 - access("OWNER"='SCOTT')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
2375 bytes sent via SQL*Net to client
435 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processedscott@orclpdb1:orclcdb>
如果索引返回的数据本身很少,即使 TABLE ACCESS BY INDEX ROWID BATCHED 前面有“*”号,也可以不用理会,因为索引本身返回的数据少,回表也没有多少次,因此可以不用再创建组合索引。