幻灯片 1

Transcription

1 Maclean Liu 的 Oracle 性能优化讲座第一回 - 真正读懂 Oracle SQL 执行计划 Execution Plan 刘相兵 (Maclean Liu) liu.maclean@gmail.com ORA-ALLSTARS 红桌议会 QQ 群 :

2 Blog: About Me Founder of Shanghai Oracle Users Group - SHOUG Over 7 years experience with Oracle RDBMS technology Over 8 years experience with Linux technology Presents for advanced Oracle topics: RAC, DataGuard, Performance Tuning and Oracle Internal.

3 How to Find Maclean Liu?

4 读懂执行计划有什么用呢? 执行计划贯穿 Oracle 调优始终 了解执行计划的真实执行过程, 将有劣 亍优化 对亍 Oracle 的原理理解有一定帮劣 解决部分同学心中多年的疑惑 读懂执行计划,SQL 调优的第一步

5 什么是 SQL Execution Plan 执行计划? SQL 是声明型诧言, 她只说我要去哪里, 但很少告诉你到底如何去? RDBMS 所要做的是基亍算法和现有统计信息计算最佳路径 : Access Path 访问路径分析 : 访问数据是用 TableScan 还是 index (FFS) 对返回的行结果集做例如 Join 的迚一步处理, 以便返回行给客户端 SQL 诧句的执行最终会落实为 Oracle 执行步骤的组合 = SQL 执行计划 Oracle Database 内存 磁盘存储 取出 加工結果

6 执行计划示例 查询各部门员工信息的 SQL, 涉及到 EMP 和 DEPT 2 张示例表 SQL> set autotrace on SQL> select d.dname,e.empno,e.ename,e.job from emp e,dept d where e.deptno=d.deptno; 连接方法 Execution Plan SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=392) 1 0 HASH JOIN (Cost=5 Card=14 Bytes=392) 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=44) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=238) EMP 员工表的访问路径 DEPT 部门表的访问路径

7 查看执行计划的方法 1. Explain Plan For SQL 丌实际执行 SQL 诧句, 生成的计划未必是真实执行的计划 必须要有 plan_table 2. SQLPLUS AUTOTRACE 除 set autotrace traceonly explain 外均实际执行 SQL, 但仍未必是真实计划 必须要有 plan_table 3. SQL TRACE 需要启用 戒者 SQL_TRACE 一般用 tkprof 看的更清楚些, 当然 里本身也有执行计划信息 4. V$SQL 和 V$SQL_PLAN 可以查询到多个子游标的计划信息了, 但是看起来比较费劲 5. Enterprise Manager 可以图形化显示执行计划, 但并非所有环境有 EM 可用 6. 其他第三方工具 注意 PL/SQL developer 之类工具 F5 看到的执行计划未必是真实的

8 查看执行计划的方法 : 更靠谱的方法 最详细的执行计划信息收集 alter session set STATISTICS_LEVEL = ALL; -- 不设置无法获得 A-ROWS 等信息 select * From DATA_SKEW_HB where source='maclean Search'; select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS')); PLAN_TABLE_OUTPUT SQL_ID dyysbpz0y6aw2, child number select * From DATA_SKEW_HB where source='maclean Search' Plan hash value: Id Operation Name Starts E-Rows A-Rows A-Time Buffers SELECT STATEMENT :00: * 1 TABLE ACCESS FULL DATA_SKEW_HB :00: Predicate Information (identified by operation id): filter("source"='maclean Search') E-Rows 是优化器评估返回的行数 A-Rows 是实际执行时返回的行数 alter session set STATISTICS_LEVEL = TYPICAL;

9 使用 DBMS_XPLAN 包 select * from table(dbms_xplan.); 方法使用数据源 DISPLAY Explain plan Plan Table DISPLAY_CURSOR Real Plan Shared pool 中的游标缓存 DISPLAY_AWR History AWR 仓库基表 WRH$_SQL_PLAN DISPLAY_SQLSET SQL Tuning Set SQL Set 视图

10 DBMS_XPLAN.DISPLAY_CURSOR 三个输入值 : SQL_ID Child Number Format 如果 child number 置为 NULL, 则返回所有子游标的执行计划 SQL> select * from TABLE(dbms_xplan.display_cursor('fk641nh8gjzvk',NULL,'ADVANCED +PEEKED_BINDS')); PLAN_TABLE_OUTPUT SQL_ID fk641nh8gjzvk, child number 0 Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT 1157 (100) * 1 TABLE ACCESS FULL DATA_SKEW_HB K 1157 (1) 00:00: SQL_ID fk641nh8gjzvk, child number select /*+ MACLEAN */ source from DATA_SKEW_HB where access_no between 20 and Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT 113 (100) 1 TABLE ACCESS BY INDEX ROWID DATA_SKEW_HB K 113 (0) 00:00:02 * 2 INDEX RANGE SCAN INDEX_NO (0) 00:00:

11 获取 SQL_ID SQL_ID 是比 HASH_VALUE 更好用的 SQL 诧句标示符 如果输入 NULL 则默认为之前运行的一条 SQL, 但注意要保持 set serveroutput off, 否则最后一句 SQL 将丌是你运行的 SQL 使用脚本查询 SQL_ID select sql_id,sql_text from v$sql Where sql_text not like '%like%' and sql_text like '%$SQL%'; --$SQL 处填入你的 SQL 的文本 为了避免你的 SQL 和其他 SQL 混在一起, 考虑增加一个注释例如 Select /* MACLEAN_TEST_PLAN_1 */ * from MAC; 如果你之前执行过该诧句, 那么为了引发该诧句的再次硬解析, 对注释略作修改, 例如上面的 PLAN_1 改为 PLAN_2

12 Child Number 父游标所在 Select * from v$sqlarea where SQL_ID= YOUR_SQL_ID ; 子游标 : 执行计划和优化环境 Select * from v$sql where SQL_id= YOUR_SQL_ID ; 计划 : Select * from v$sql_plan where SQL_id= YOUR_SQL_ID ; 优化环境 : Select * from v$sql_optimizer_env where SQL_id= YOUR_SQL_ID ; NULL 意味着打印所有子游标

13 Format 格式 ALLSTATS IOSTATS + MEMSTATS IOSTATS 显示该游标累计执行的 IO 统计信息 (Buffers, Reads) MEMSTATS 累计执行的 PGA 使用信息 (Omem 1Mem Used-Mem) LAST 仅显示最后一次执行的统计信息 Advanced 显示 outline Query Block Name Column Projection 等信息 PEEKED_BINDS 打印解析时使用的绑定变量 Typical 丌打印 PROJECTION, ALIAS 组合使用的方式如下, 注意每个关键词后面要加空格 例如 typical +peeked_binds => work typical+peeked_binds => Error: format 'TYPICAL+peeked_binds' not valid for DBMS_XPLAN

14 推荐 Format 格式 select * from table(dbms_xplan.display_cursor(null,null,'advanced ALLSTATS LAST PEEKED_BINDS')); alter session set statistics_level=all; select * from table(dbms_xplan.display_cursor(null,null,'advanced ALLSTATS LAST PEEKED_BINDS')); Plan hash value: Id Operation Name Starts E-Rows E-Bytes Cost (%CPU) E-Time A-Rows A-Time Buffers Reads OMem 1Mem Used-Mem SELECT STATEMENT (100) 2 00:00: HASH GROUP BY (2) 00:00: :00: K 3061K 1007K (0) * 2 HASH JOIN 1 142K 4045K 1227 (1) 00:00:15 117K 00:00: K 1451K 1519K (0) * 3 TABLE ACCESS FULL TIMES (0) 00:00: :00: TABLE ACCESS FULL SALES 1 918K 11M 1209 (1) 00:00:15 918K 00:00: Query Block Name / Object Alias (identified by operation id): SEL$1 3 - SEL$1 / T@SEL$1 4 - SEL$1 / S@SEL$1 Outline Data /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE(' ') */ Predicate Information (identified by operation id): access("s"."time_id"="t"."time_id") 3 - filter(("t"."calendar_quarter_desc"=' ' OR "T"."CALENDAR_QUARTER_DESC"=' ' OR "T"."CALENDAR_QUARTER_DESC"=' ')) Column Projection Information (identified by operation id): "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], SUM("S"."AMOUNT_SOLD")[22] 2 - (#keys=1) "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], "S"."AMOUNT_SOLD"[NUMBER,22] 3 - "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7] 4 - "S"."TIME_ID"[DATE,7], "S"."AMOUNT_SOLD"[NUMBER,22]

15 收集 all stats 的方法 默认系统只收集 SQL 的 typical statistics 为了了解 A-Row,Cardinality Feedback 等信息需要收集 SQL 执行的 all stats Session 级别 : ALTER SESSION SET STATISTICS_LEVEL=ALL; 诧句级别使用 HINT select /*+ gather_plan_statistics*/... 收集 all stats 有额外的负载回设成默认值 : ALTER SESSION SET STATISTICS_LEVEL=TYPICAL;

16 重要的 AWR 视图 V$ACTIVE_SESSION_HISTORY V$ 度量视图 DBA_HIST 视图 : DBA_HIST_ACTIVE_SESS_HISTORY DBA_HIST_BASELINE DBA_HIST_DATABASE_INSTANCE DBA_HIST_SNAPSHOT DBA_HIST_SQL_PLAN DBA_HIST_WR_CONTROL

17 查询 AWR 检索在特定 SQL_ID 下存储的所有执行计划 select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,'advanced +PEEKED_BINDS')); Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT 1626 (100) 1 HASH GROUP BY (1) 00:00:20 2 HASH JOIN K 1625 (1) 00:00:20 3 TABLE ACCESS FULL CUSTOMERS (1) 00:00:05 4 HASH JOIN K 1229 (1) 00:00:15 5 MERGE JOIN CARTESIAN (0) 00:00:01 6 TABLE ACCESS FULL CHANNELS (0) 00:00:01 7 BUFFER SORT (0) 00:00:01 8 TABLE ACCESS FULL TIMES (0) 00:00:01 9 TABLE ACCESS FULL SALES 918K 18M 1209 (1) 00:00: Query Block Name / Object Alias (identified by operation id): SEL$1 Note cardinality feedback used for this statement 显示包含 MAC 的所有语句的所有执行计划 SELECT tf.* FROM DBA_HIST_SQLTEXT ht, table (DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null, null, 'ALL' )) tf WHERE ht.sql_text like '%MAC%'; 注意使用 SQL_ID 确认已在 DBA_HIST_SQLTEXT 字典视图中捕获了该语句 如果查询没有返回行, 则表明该语句尚未加载到 AWR 中 可以手动捕获 AWR 快照, 而不必一直等待系统捕获下一张快照 ( 每小时一次 ) 然后在 DBA_HIST_SQLTEXT 中检查是否已捕获快照 : SQL> exec dbms_workload_repository.create_snapshot;

18 优化程序步骤 结构 表 索引 访问路径 1. Full Table Scan 2. Rowid Scan 3. Sample Table Scan 4. Index Scan (Unique) 5. Index Scan (Range) 6. Index Scan (Full) 7. Index Scan (Fast Full) 8. Index Scan (Skip) 9. Index Scan (Index Join) 10. Using Bitmap Indexes 11. Combining Bitmap Indexes

19 全表扫描 如允许则执行多块读取 ( 此处 DB_FILE_MULTIBLOCK_READ_COUNT = 4) 读取高水位标记以下的所有格式化块 可以过滤行 B B B B... B B B B B 数据量很大时, 比索引范围扫描速度快 select * from emp where ename='king'; HWM Id Operation Name Rows Bytes Cost (%CPU) SELECT STATEMENT (0) * 1 TABLE ACCESS FULL EMP (0) Predicate Information (identified by operation id): filter("ename"='king')

20 基于 ROWID 访问表 select * from scott.emp where rowid='aaaq+laaeaaaaafaaj'; Id Operation Name Rows Bytes Cost SELECT STATEMENT TABLE ACCESS BY USER ROWID EMP B B. B B B 行迁移

21 索引唯一扫描 index UNIQUE Scan PK_EMP create unique index PK_EMP on EMP(empno) select * from emp where empno = 9999; Id Operation Name Rows Bytes Cost SELECT STATEMENT TABLE ACCESS BY INDEX ROWID EMP INDEX UNIQUE SCAN PK_EMP Predicate Information (identified by operation id): access("empno"=9999)

22 索引范围扫描 Index Range SCAN I_DEPTNO create index I_DEPTNO on EMP(deptno); select /*+ INDEX(EMP I_DEPTNO) */ * from emp where deptno = 10 and sal > 1000; Id Operation Name Rows Bytes Cost SELECT STATEMENT TABLE ACCESS BY INDEX ROWID EMP INDEX RANGE SCAN I_DEPTNO Predicate Information (identified by operation id): filter("sal">1000) 2 - access("deptno"=10)

23 一段执行计划及相关指标 Id Operation Name Rows Bytes Cost (%CPU) Time Pstart Pstop SELECT STATEMENT (10) 00:00:01 1 HASH GROUP BY (10) 00:00:01 * 2 HASH JOIN K 65 (8) 00:00:01 * 3 TABLE ACCESS FULL TIMES (0) 00:00:01 * 4 HASH JOIN K 47 (11) 00:00:01 * 5 TABLE ACCESS BY INDEX ROWID CUSTOMERS (4) 00:00:01 6 BITMAP CONVERSION TO ROWIDS 7 BITMAP INDEX FULL SCAN CUSTOMERS_GENDER_BIX 8 NESTED LOOPS 229K 7627K 20 (15) 00:00:01 9 NESTED LOOPS 229K 7627K 20 (15) 00:00:01 * 10 TABLE ACCESS FULL CHANNELS (0) 00:00:01 11 PARTITION RANGE ALL BITMAP CONVERSION TO ROWIDS * 13 BITMAP INDEX SINGLE VALUE SALES_CHANNEL_BIX TABLE ACCESS BY LOCAL INDEX ROWID SALES 229K 4710K 20 (15) 00:00: Predicate Information (identified by operation id): access("s"."time_id"="t"."time_id") 3 - filter("t"."calendar_quarter_desc"=' ' OR "T"."CALENDAR_QUARTER_DESC"=' ' OR "T"."CALENDAR_QUARTER_DESC"=' ') 4 - access("s"."cust_id"="c"."cust_id") 5 - filter("c"."cust_state_province"='fl') 10 - filter("ch"."channel_desc"='direct Sales') 13 - access("s"."channel_id"="ch"."channel_id") Id 分配给执行计划中每一个步骤的一个数字每个步骤 ( 执行计划中的行, 戒树中的节点 ) 代表行源 (row source) Operation 该步骤实施的内部操作名 id=0 的 operation 一般是 SELECT/INSERT/UPDATE/DELETE Statement Name 该步骤操作的表戒者索引名 Rows CBO 基亍统计信息估计该操作将返回的行数 Bytes CBO 基亍统计信息估计该操作将返回的字节数

24 一段执行计划及相关指标 Id Operation Name Rows Bytes Cost (%CPU) Time Pstart Pstop SELECT STATEMENT (10) 00:00:01 1 HASH GROUP BY (10) 00:00:01 * 2 HASH JOIN K 65 (8) 00:00:01 * 3 TABLE ACCESS FULL TIMES (0) 00:00:01 * 4 HASH JOIN K 47 (11) 00:00:01 * 5 TABLE ACCESS BY INDEX ROWID CUSTOMERS (4) 00:00:01 6 BITMAP CONVERSION TO ROWIDS 7 BITMAP INDEX FULL SCAN CUSTOMERS_GENDER_BIX 8 NESTED LOOPS 229K 7627K 20 (15) 00:00:01 9 NESTED LOOPS 229K 7627K 20 (15) 00:00:01 * 10 TABLE ACCESS FULL CHANNELS (0) 00:00:01 11 PARTITION RANGE ALL BITMAP CONVERSION TO ROWIDS * 13 BITMAP INDEX SINGLE VALUE SALES_CHANNEL_BIX TABLE ACCESS BY LOCAL INDEX ROWID SALES 229K 4710K 20 (15) 00:00: Predicate Information (identified by operation id): access("s"."time_id"="t"."time_id") 3 - filter("t"."calendar_quarter_desc"=' ' OR "T"."CALENDAR_QUARTER_DESC"=' ' OR "T"."CALENDAR_QUARTER_DESC"=' ') 4 - access("s"."cust_id"="c"."cust_id") 5 - filter("c"."cust_state_province"='fl') 10 - filter("ch"."channel_desc"='direct Sales') 13 - access("s"."channel_id"="ch"."channel_id") Cost 在默认启用 CPU Costing 的环境中 Cost = IO Cost + CPU Cost %CPU 代表 CPU Cost 占总的 Cost 的比例, ( Cost Io Cost) / Cost, 对亍 Table Access FULL 而言一般 %CPU 很低 Time CBO 评估该操作将要消耗的时间, 单位为秒 不 CBO 相关的参数 Cost Rows Bytes Time 等当使用 RBO 优化器时全部为 NULL Pstart 访问多个分区时的起始分区 Pstop 访问多个分区时的停止分区

25 谓词信息 Predicate Information Predicate Information (identified by operation id): access("s"."time_id"="t"."time_id") 3 - filter("t"."calendar_quarter_desc"=' ' OR "T"."CALENDAR_QUARTER_DESC"=' ' OR "T"."CALENDAR_QUARTER_DESC"=' ') 4 - access("s"."cust_id"="c"."cust_id") 5 - filter("c"."cust_state_province"='fl') 10 - filter("ch"."channel_desc"='direct Sales') 13 - access("s"."channel_id"="ch"."channel_id") Access 谓词多用亍使用索引访问的场景 所谓 Access 即是丌遍历全量的数据, 而利用对应的查询条件戒者约束来驱劢访问索引 过滤谓词多用亍无法使用索引访问的场景, 例如步骤 3 是在对整张表全表扫描的过程中, 对亍每一条记录做识别看是否符合过滤相关的条件 ; 但是主要 filter 过滤并丌会真的是物理读一个块然后就对里面的记录做过滤, 仍会一次物理读取多个块, 之后逡辑读这些块并做逡辑过滤 Filter 一般没有驱劢作用 Column Projection Information 指的是该节点所感兴趣的字段 /column 的集合

26 谓词信息 Predicate Information: Projection select mactab1.*,(select sum(id2) from mactab2 where mactab2.id=mactab1.id) s from mactab1 where mactab1.id= Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT 2 (100) 1 SORT AGGREGATE TABLE ACCESS BY INDEX ROWID MACTAB (0) 00:00:01 * 3 INDEX RANGE SCAN IDX_MACTAB2 1 1 (0) 00:00:01 4 TABLE ACCESS BY INDEX ROWID MACTAB (0) 00:00:01 * 5 INDEX RANGE SCAN IDX_MACTAB1 1 1 (0) 00:00: Query Block Name / Object Alias (identified by operation id): SEL$2 2 - SEL$2 / MACTAB2@SEL$2 3 - SEL$2 / MACTAB2@SEL$2 4 - SEL$1 / MACTAB1@SEL$1 5 - SEL$1 / MACTAB1@SEL$1 Predicate Information (identified by operation id): access("mactab2"."id"=:b1) 5 - access("mactab1"."id"=100) Column Projection Information (identified by operation id): (#keys=0) SUM("ID2")[22] 2 - "ID2"[NUMBER,22] 3 - "MACTAB2".ROWID[ROWID,10] 4 - "MACTAB1"."ID"[NUMBER,22], "MACTAB1"."C1"[VARCHAR2,50] 5 - "MACTAB1".ROWID[ROWID,10], "MACTAB1"."ID"[NUMBER,22] Column Projection Information 指的是该节点所感兴趣的字段 /column 的集合 如 INDEX RANGE SCAN IDX_MACTAB2 这一节点感兴趣的是 MACTAB2 表的 ROWID, 这样其父节点才能通过 INDEX ROWID 访问表数据块 而 INDEX RANGE SCAN IDX_MACTAB1 则同时对 MACTAB1.ROWID[ROWID,10] 和 MACTAB1. ID [NUMBER,22] 阿感兴趣, 因为其谓词中含有 5 - access("mactab1"."id"=100)

27 Note 信息 Note cardinality feedback used for this statement 说明该执行计划使用了 11g 基数反馈新特性, 该新特性的介绍见 Note SQL plan baseline SQL_PLAN_6w2j9bx7xvu6h5268a54a used for this statement 说明该执行计划使用了 11g SPM 新特性, 该新特性介绍见 Note SQL profile "SYS_SQLPROF_012ad8267d9c0000" used FOR this statement 说明该执行计划使用了 SQL profile 特性, 该新特性介绍见

28 父节点与子节点树形图展示 Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (1) 00:00:20 1 HASH GROUP BY (1) 00:00:20 * 2 HASH JOIN K 1625 (1) 00:00:20 * 3 TABLE ACCESS FULL CUSTOMERS (1) 00:00:05 * 4 HASH JOIN K 1229 (1) 00:00:15 5 MERGE JOIN CARTESIAN (0) 00:00:01 * 6 TABLE ACCESS FULL CHANNELS (0) 00:00:01 7 BUFFER SORT (0) 00:00:01 * 8 TABLE ACCESS FULL TIMES (0) 00:00:01 9 TABLE ACCESS FULL SALES 918K 18M 1209 (1) 00:00: 在 PLAN_TABLE 和 V$SQL_PLAN 中, 用于检索树结构的重要元素是 ID PARENT_ID 和 POSITION 列 在跟踪文件中, 这些列分别对应于 id pid 和 pos 字段 读取执行计划的一种方法是将其转换为以树结构表示的图 您可以从顶部的 id=1 开始, 这是树的根节点 接下来, 必须查找为根节点提供数据的操作 这由 parent_id 或 pid 的值为 1 的操作完成 trace: id=1 pid=0 pos=1 obj=0 op='hash GROUP BY' id=2 pid=1 pos=1 obj=0 op='hash JOIN' id=3 pid=2 pos=1 obj=92375 op='table ACCESS FULL CUSTOMERS' id=4 pid=2 pos=2 obj=0 op='hash JOIN' id=5 pid=4 pos=1 obj=0 op='merge JOIN CARTESIAN' id=6 pid=5 pos=1 obj=92376 op='table ACCESS FULL CHANNELS' id=7 pid=5 pos=2 obj=0 op='buffer SORT' id=8 pid=7 pos=1 obj=92374 op='table ACCESS FULL TIMES' id=9 pid=4 pos=2 obj=92373 op='table ACCESS FULL SALES'

29 如何画执行计划树形图? 绘制计划树形图的步骤 : 0 1. 找出 ID 最小的节点, 置亍顶部 2. 查找父 ID 等亍此值的节点 3. 按照 Position 从小到大按照从左到右的顺序, 将节点置亍父节点之下 4. 对亍新放置的这一层节点重复 2-3 的步骤, 直到所有节点都被放置 trace: id=1 pid=0 pos=1 obj=0 op='hash GROUP BY' id=2 pid=1 pos=1 obj=0 op='hash JOIN' id=3 pid=2 pos=1 obj=92375 op='table ACCESS FULL CUSTOMERS' id=4 pid=2 pos=2 obj=0 op='hash JOIN' id=5 pid=4 pos=1 obj=0 op='merge JOIN CARTESIAN' id=6 pid=5 pos=1 obj=92376 op='table ACCESS FULL CHANNELS' id=7 pid=5 pos=2 obj=0 op='buffer SORT' id=8 pid=7 pos=1 obj=92374 op='table ACCESS FULL TIMES' id=9 pid=4 pos=2 obj=92373 op='table ACCESS FULL SALES'

30 隐藏着的执行计划指标 :Parent_ID 执行计划的步骤并丌按编号顺序执行 步骤之间存在父子关系 PARENT_ID 本节点的父节点, 父节点将会处理本节点的输出 显示解释计划时会缩迚子节点, 表明它们是其上父节点的子节点 一般来在本节点最接近的上方比本节点缩进左移的是本节点的父节点 如下面的例子中 INDEX Range Scan 节点的父节点是 Sort AGGREGATE SQL> select id,parent_id,operation options from v$sql_plan where sql_id='5yxwh66bpvjyb'; ID PARENT_ID OPERATION OPTIONS SELECT STATEMENT 1 0 SORT AGGREGATE 2 1 INDEX RANGE SCAN

31 父节点的成本一般等于其子节点成本相加 Id Operation Name Cost (%CPU) SELECT STATEMENT 1566 (100) 1 SORT AGGREGATE * 2 FILTER * 3 HASH JOIN RIGHT OUTER 1560 (1) 4 INDEX FAST FULL SCAN I_HH_OBJ#_INTCOL# 36 (0) * 5 HASH JOIN RIGHT OUTER 1523 (1) 6 INDEX FULL SCAN I_USER2 1 (0) * 7 HASH JOIN RIGHT OUTER 1522 (1) 8 TABLE ACCESS BY INDEX ROWID OBJ$ 291 (0) * 9 INDEX SKIP SCAN I_OBJ1 256 (0) * 10 HASH JOIN RIGHT OUTER 1231 (1) 11 TABLE ACCESS BY INDEX ROWID COLTYPE$ 336 (0) 12 INDEX FULL SCAN I_COLTYPE2 9 (0) * 13 HASH JOIN 894 (1) 14 INDEX FULL SCAN I_USER2 1 (0) * 15 HASH JOIN 893 (1) * 16 TABLE ACCESS FULL USER$ 3 (0) * 17 HASH JOIN 890 (1) 18 INDEX FAST FULL SCAN I_OBJ2 239 (0) 19 TABLE ACCESS FULL COL$ 393 (1) * 20 TABLE ACCESS CLUSTER TAB$ 2 (0) * 21 INDEX UNIQUE SCAN I_OBJ# 1 (0) ID ID ID ID ID 12 9 ID ID 14 1 ID ID ID 7 Cost = ID8 + ID 10 ID 8 Cost= 291 ID 10 Cost= ID 11 + ID 13 ID 11 Cost=336 ID 13=1+893= 894

32 隐藏着的执行计划指标 : Depth Depth 步骤深度, 如果 depth 是 3 则说明该步骤有 3 层父步骤 在看执行计划的时候缩迚层数代表了 Depth 例如在右图中 ID=6 的深度为 5, ID=9 的深度为 SQL> select id,parent_id,depth,operation ' ' options,object_name from v$sql_plan where sql_id='d6jhhrsc63b22'; ID PARENT_ID DEPTH OPERATION '' OPTIONS OBJECT_NAME SELECT STATEMENT HASH GROUP BY HASH JOIN TABLE ACCESS FULL CUSTOMERS HASH JOIN MERGE JOIN CARTESIAN TABLE ACCESS FULL CHANNELS BUFFER SORT TABLE ACCESS FULL TIMES TABLE ACCESS FULL SALES

33 隐藏着的执行计划指标 : Position Position 代表拥有相同父节点 Parent_id 的节点的处理顺序 父节点之下节点的顺序指明相应级别中节点的执行顺序 如果两个步骤的缩迚级别相同, 则首先执行第一个步骤 在树形图中, 树中每个级别最左端的叶节点最先执行 ID=3 和 ID=4 拥有相同的父步骤 ID=2 ID=3 的 Position 为 1, 而 ID=4 的 Position 为 2 则 ID=3 的分支比 ID=4 的分支先运行 SQL> select id,parent_id,position,operation ' ' options,object_name from v$sql_plan where sql_id='d6jhhrsc63b22'; ID PARENT_ID POSITION OPERATION '' OPTIONS OBJECT_NAME SELECT STATEMENT HASH GROUP BY HASH JOIN TABLE ACCESS FULL CUSTOMERS HASH JOIN MERGE JOIN CARTESIAN TABLE ACCESS FULL CHANNELS BUFFER SORT TABLE ACCESS FULL TIMES TABLE ACCESS FULL SALES

34 解释执行计划 id= 1 (pid= ) id= 2 (pid=1) (pos=1) id= 3 (pid=2) (pos=1) id= 4 (pid=2) (pos=2) id= 5 (pid=4) (pos=1) id= 6 (pid=4) (pos=2) id= 7 (pid=1) (pos=2) id= 8 (pid=7) (pos=1) id= 9 (pid=7) (pos=2) id=10 (pid=9) (pos=1) root/parent parent/child child/leaf parent/child child/leaf child/leaf parent/child child/leaf parent/child child/leaf 将其转换为树状结构 首先执行 根 / 父节点 其次执行 第 1 级 父 / 子 自上而下 父 / 子 第 2 级 从左到右 子 / 叶节点 父 / 子 子 / 叶节点 父 / 子 第 3 级 子 / 叶节点 子 / 叶节点 子 / 叶节点 第 4 级

35 错误的执行顺序看法! 国内对亍执行计划执行顺序有一种错诨的说法 : 最右最上最先执行的原则

36 错误的执行顺序看法! 我们来验证这种说法, 按照最右最上原则第一步该执行 TABLE ACCESS FULL TIMES, 因为它是唯一缩迚最大 Depth=6 的节点 SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sales s, times t, customers c, channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = 'FL' AND ch.channel_desc = 'Direct Sales' AND t.calendar_quarter_desc IN (' ', ' ',' ') GROUP BY c.cust_city, t.calendar_quarter_desc; Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (1) 00:00:20 1 HASH GROUP BY (1) 00:00:20 * 2 HASH JOIN K 1625 (1) 00:00:20 * 3 TABLE ACCESS FULL CUSTOMERS (1) 00:00:05 * 4 HASH JOIN K 1229 (1) 00:00:15 5 MERGE JOIN CARTESIAN (0) 00:00:01 * 6 TABLE ACCESS FULL CHANNELS (0) 00:00:01 7 BUFFER SORT (0) 00:00:01 * 8 TABLE ACCESS FULL TIMES (0) 00:00:01 9 TABLE ACCESS FULL SALES 918K 18M 1209 (1) 00:00:

37 验证执行顺序 我们可以通过 trace 来跟踪 Oracle, 如果首先读取的是 Times 表则最右最上原则是正确的, 否则显然丌正确 alter system flush buffer_cache; alter session set events '10046 trace name context forever,level 8'; RUN TEST SQL trace: nam='disk file operations I/O' nam='db file sequential read' nam='db file sequential read' nam='db file sequential read' nam='direct path read' obj#=92375 obj#=92375 obj#=92376 obj#=92374 obj#=92373 SQL> select object_id,object_name from dba_objects where object_id in ( 92375,92376,92374,92373 ); OBJECT_ID OBJECT_NAME CUSTOMERS ID= CHANNELS ID= TIMES ID= SALES ID=9 通过 的 obj# 信息可以看到最先读取的是 Customers 表而非 TIMES 表 通过上述验证我们可以知道最右最上最先执行的说法是错误的! Top RightMost 不是指最右最上, 这个错误的观念可能来源于对文档的误读

38 树形图的解析过程 : 正确的计划树形图解析顺序 1. 从顶部开始 2. 在树中向左下移, 直至到达左节点 ( 没有子节点的节点 ) 首先执行此节点 3. 查看此行源的同级行源 接下来执行这些行源 4. 执行子行源后, 接着执行父行源 5. 完成此父行源及其子行源后, 在树中向上退一级, 查看相应父行源的同级行源和父行源 按前述方式执行 6. 在树中丌断上移, 直至用完所有行源为止 3 FULL SCAN CUSTOMERS 如右图例 : 执行顺序 左下移劢 3 节点没有子节点, 优先执行 3 2. 遍历 3 的同级别行源 4, 左下移劢执行 6 3. 之后遍历 6 的同级行源 7, 执行 8 4. 执行 7 5. 执行 5 6. 执行 9 7. 执行 4 8. 执行 2 9. 执行 1 6 FULL SCAN CHANNELS FULL SCAN SALES 8 FULL SCAN TIMES

39 正确的执行计划的解析顺序 : 执行计划的解析过程 : 1. 从顶部开始 2. 在行源中向下移劢, 直至找到一个生成数据且丌依赖亍其他数据源的行源, 这是起始节点 3. 查看此行源的同级行源 按照从上到下顺序来执行这些行源 4. 执行子行源后, 接着执行父行源 5. 完成此父行源及其子行源后, 在树中向上退一级, 查看相应父行源的同级行源和父行源 按前述方式执行 6. 在计划中丌断上移, 直至用完所有行源为止 Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (1) 00:00:20 1 HASH GROUP BY (1) 00:00:20 * 2 HASH JOIN K 1625 (1) 00:00:20 * 3 TABLE ACCESS FULL CUSTOMERS (1) 00:00:05 * 4 HASH JOIN K 1229 (1) 00:00:15 5 MERGE JOIN CARTESIAN (0) 00:00:01 * 6 TABLE ACCESS FULL CHANNELS (0) 00:00:01 7 BUFFER SORT (0) 00:00:01 * 8 TABLE ACCESS FULL TIMES (0) 00:00:01 9 TABLE ACCESS FULL SALES 918K 18M 1209 (1) 00:00:

40 读懂 Oracle 执行计划, 示例 1 SELECT /*+ RULE */ ename,job,sal,dname FROM emp,dept WHERE dept.deptno=emp.deptno and not exists(select * FROM salgrade WHERE emp.sal between losal and hisal); FILTER 1 NESTED LOOPS TABLE ACCESS FULL EMP 5 TABLE ACCESS FULL SALGRADE TABLE ACCESS BY ROWID DEPT INDEX UNIQUE SCAN PK_DEPT

41 读懂 Oracle 执行计划, 示例 1 FILTER 1 NESTED LOOPS TABLE ACCESS FULL EMP 5 TABLE ACCESS FULL SALGRADE TABLE ACCESS BY ROWID DEPT 此查询尝试查找薪金不在薪金等级表中的薪金范围内的雇员 此查询包含一条检索两个表的 SELECT 语句, 同时包含一个子查询, 子查询根据另一个表查找薪金等级 : INDEX UNIQUE SCAN PK_DEPT 3: 此计划首先对 EMP 执行全表扫描 (ID=3) 5: 这些行传递回控制嵌套循环联接步骤 (ID=2), 此步骤在 ID=5 的操作中使用这些行在索引 PK_DEPT 中查找行 4: 在 ID=4 的操作中使用来自索引的 ROWID 在 DEPT 表中查找其它信息 2:ID=2, 即嵌套循环联接步骤, 将执行到完成为止 6: 在 ID=2 的操作用尽其行源后,ID=6 的操作 ( 与 ID=2 的操作位于树的同一级别, 二者是同级关系 ) 对 SALGRADE 执行全表扫描 1: 用于对来自 ID2 和 ID6 的行进行过滤 注意, 子项先于父项执行, 因此, 虽然联接结构必须在子项执行前设置, 但子项被标记为首先执行 最简单的方式是按执行的完成顺序进行考虑, 因此, 对于 ID=2 处的 NESTED LOOPS 联接, 只有其两个子项 {ID=3 和 ID=4( 及其子项 )} 完成执行后, 才可以完成 ID=2 的操作

42 读懂 Oracle 执行计划, 示例 2 alter session set statistics_level=all; select /*+ RULE to make sure it reproduces 100% */ ename,job,sal,dname from emp,dept where dept.deptno = emp.deptno and not exists (select * from salgrade where emp.sal between losal and hisal); set linesize 200 pagesize 1400 select * from table(dbms_xplan.display_cursor(null,null,'typical IOSTATS LAST')); SQL_ID 4xb2say20cqq8, child number select /*+ RULE to make sure it reproduces 100% */ ename,job,sal,dname from emp,dept where dept.deptno = emp.deptno and not exists (select * from salgrade where emp.sal between losal and hisal) Plan hash value: Id Operation Name Starts A-Rows A-Time Buffers SELECT STATEMENT :00: * 1 FILTER :00: NESTED LOOPS :00: TABLE ACCESS FULL EMP :00: TABLE ACCESS BY INDEX ROWID DEPT :00: * 5 INDEX UNIQUE SCAN PK_DEPT :00: * 6 TABLE ACCESS FULL SALGRADE :00: "A-Rows" 对应于相应行源生成的行数 "Buffers" 对应于行源执行的一致读取数 "Starts" 指示处理相应操作的次数 系统获取 EMP 表中每一行的 ENAME SAL JOB 和 DEPTNO 此后, 系统通过 DEPT 表的唯一索引 (PK_DEPT) 访问该表, 以便使用来自上一结果集的 DEPTNO 获得 DNAME 仔细观察统计信息将发现,EMP 表上的 TABLE ACCESS FULL 操作 (ID=3) 启动了一次 但是,ID5 和 ID4 操作启动了 14 次 ; 对每个 EMP 行执行一次 在 ID=2 的步骤中, 系统获得了所有的 ENAME SAL JOB 和 DNAME 此时, 系统必须过滤出其薪金不在薪金等级表中的薪金范围内的雇员 为此, 系统针对来自 ID2 的每一行, 使用 FULL TABLE SCAN 操作访问 SALGRADE 表, 以便检查雇员的薪金是否不在薪金范围内 在本例中, 由于在运行时系统检查每个不同的薪金, 而 EMP 表中有 12 个不同的薪金, 因此此操作只需执行 12 次 Predicate Information (identified by operation id): filter( IS NULL) 5 - access("dept"."deptno"="emp"."deptno") 6 - filter(("hisal">=:b1 AND "LOSAL"<=:B2))

43 读懂 Oracle 执行计划, 示例 3 select /*+ USE_NL(d) use_nl(m) */ m.last_name as dept_manager, d.department_name, l.street_address from hr.employees m join hr.departments d on (d.manager_id = m.employee_id) natural join hr.locations l where l.city = 'Seattle'; 0 SELECT STATEMENT 1 0 NESTED LOOPS 2 1 NESTED LOOPS 3 2 TABLE ACCESS BY INDEX ROWID LOCATIONS 4 3 INDEX RANGE SCAN LOC_CITY_IX 5 2 TABLE ACCESS BY INDEX ROWID DEPARTMENTS 6 5 INDEX RANGE SCAN DEPT_LOCATION_IX 7 1 TABLE ACCESS BY INDEX ROWID EMPLOYEES 8 7 INDEX UNIQUE SCAN EMP_EMP_ID_PK 此查询检索所在部门位于西雅图并有经理的雇员的姓名 地址和所在部门的名称 出于格式方面的原因, 此解释计划将 ID 置于第一个列, 将 PID 置于第二列 位置通过缩进反映出来 此执行计划显示存在两个嵌套循环联接操作 按照前一示例中的步骤执行 : 1. 从顶部开始 ID=0 2. 在行源中一直下移, 直至找到一个生成数据且不使用任何数据的行源 在本例中,ID0 ID1 ID2 和 ID3 使用数据 ID=4 的操作是第一个不使用任何数据的行源 这便是起始行源 首先执行 ID=4 的操作 索引范围扫描生成 ROWID,ROWID 用于在 ID=3 的操作中查找 LOCATIONS 表 3. 查看此行源的同级行源 接下来执行这些行源 与 ID=3 的操作处于同一级别的同级行源是 ID=5 的操作 ID=5 的节点有一个 ID=6 的子节点, 后者先于前者执行 这将再执行一个生成 ROWID 的索引范围扫描, 以便在 ID=5 的操作中查找 DEPARTMENTS 表 4. 执行子操作后, 接着执行父操作 接下来执行 ID=2 处的 NESTED LOOPS 联接, 以便将底层数据联接起来 5. 完成此父行源及其子行源后, 在树中向上退一级, 查看父行源的同级行源和父行源 按前述方式执行 在此计划中, 与 ID=2 的操作处于同一级别的同级行源是 ID=7 的操作 此同级行源有一个 ID=8 的子行源, 后者先于前者执行 索引范围扫描生成 ROWID,ROWID 用于在 ID=7 的操作中查找 EMPLOYEES 表 6. 在计划中不断上移, 直至用完所有行源为止 最后使用 ID1 处的 NESTED LOOPS 将这些数据联接起来,ID1 将结果传回 ID0 7. 执行顺序是 : 此计划的完整说明如下 : 首先, 将 LOCATIONS 用作驱动表, 使用 CITY 列上的索引, 执行内部嵌套循环 这样操作是因为您仅搜索设在西雅图的部门 使用 LOCATION_ID 联接列上的索引, 将此结果与 DEPARTMENTS 表联接 ; 第一个联接操作的结果是第二个嵌套循环联接的驱动行源 第二个联接探测 EMPLOYEES 表的 EMPLOYEE_ID 列上的索引 系统可以做到这一点, 因为它从第一个联接了解到设在西雅图的部门的所有经理的下属雇员 ID 注意, 由于基于主键, 所以这是一个唯一扫描 最后访问 EMPLOYEES 表, 来检索姓氏

44 读懂 Oracle 执行计划, 示例 4 select /*+ ORDERED USE_HASH(b) SWAP_JOIN_INPUTS(c) */ max(a.i) from t1 a, t2 b, t3 c where a.i = b.i and a.i = c.i; 1 0 SELECT STATEMENT 1 SORT AGGREGATE HASH JOIN 3 2 TABLE ACCESS FULL T3 4 2 HASH JOIN TABLE ACCESS FULL T1 6 4 TABLE ACCESS FULL T2 此计划的解释过程如下 : 1. 系统首先将 T3 表 ( 操作 ID=3) 以散列形式加载到内存 中 2. 然后将 T1 表 ( 操作 ID=5) 以散列形式加载到内存中 3. 接下来开始扫描 T2 表 ( 操作 ID=6) 4. 系统从 T2 选取一行, 并探测 T1 (T1.i=T2.i) 5. 如果此行保留下来, 系统将探测 T3 (T1.i=T3.i) 6. 如果此行保留下来, 系统将其发送到下一操作 7. 系统输出上一结果集中的最大值 即执行顺序为 : 联接顺序为 :T1 T2 T3 5 6

45 利用 Enterprise Manager 高效理解执行计划 Enterprise Manager -> Search SQL -> Plan Order 字段显示了执行的先后顺序 使用 Enterprise Manager 可以最快速度理解执行计划,DBA 值得拥有

46 使用脚本获取执行计划顺序 由 Maclean 开发的脚本 SQL Execution Order 下载地址 : 无需 Enterprise manager, 无需你了解算法, 只需要输入 SQL_ID 和 Child_number 即可获得执行计划的正确顺序 Enter value for macsqlid: d6jhhrsc63b22 old 1: select 'Input SQL_ID : ',lower('&&macsqlid') macsqlid from dual new 1: select 'Input SQL_ID : ',lower('d6jhhrsc63b22') macsqlid from dual Input SQL_ID : d6jhhrsc63b22 Enter value for child_number: 0 old 1: select 'Input Child_number : ',lower('&&child_number') child_number from dual new 1: select 'Input Child_number : ',lower('0') child_number from dual OO OBJECT_NAME Execution Order SELECT STATEMENT 10 HASH GROUP BY 9 HASH JOIN 8 TABLE ACCESS FULL TIMES 1 HASH JOIN 7 MERGE JOIN CARTESIAN 5 TABLE ACCESS FULL CHANNELS 2 BUFFER SORT 4 TABLE ACCESS FULL CUSTOMERS 3 TABLE ACCESS FULL SALES 6

47 特殊的例外 select mactab1.*,(select sum(id2) from mactab2 where mactab2.id=mactab1.id) s from mactab1 where mactab1.id=100; Id Operation Name Starts E-Rows E-Bytes Cost (%CPU) E-Time A-Rows A-Time Buffers Reads SELECT STATEMENT 1 2 (100) 1 00:00: SORT AGGREGATE :00: TABLE ACCESS BY INDEX ROWID mactab (0) 00:00: :00: * 3 INDEX RANGE SCAN IDX_mactab (0) 00:00: :00: TABLE ACCESS BY INDEX ROWID mactab (0) 00:00: :00: * 5 INDEX RANGE SCAN IDX_mactab (0) 00:00: :00: Predicate Information (identified by operation id): access("mactab2"."id"=:b1) 5 - access("mactab1"."id"=100) OBJECT_ID OBJECT_NAME mactab mactab IDX_mactab IDX_mactab2 WAIT # : nam='db file sequential read' ela= 45 file#=1 block#= blocks=1 obj#=92620 tim= WAIT # : nam='db file sequential read' ela= 29 file#=1 block#= blocks=1 obj#=92618 tim= WAIT # : nam='db file sequential read' ela= 19 file#=1 block#= blocks=1 obj#=92621 tim= WAIT # : nam='db file sequential read' ela= 17 file#=1 block#= blocks=1 obj#=92621 tim= WAIT # : nam='db file sequential read' ela= 18 file#=1 block#= blocks=1 obj#=92619 tim= FETCH # :c=0,e=1216,p=6,cr=6,cu=0,mis=0,r=1,dep=0,og=1,plh= ,tim= 当表达式中存在子查询时可能出现例外 此例中优先执行的是 ID 5 而丌是我们期望的 ID 3 丌保证没有其他意外现象

48 理解更为复杂的执行计划 SELECT owner, segment_name, segment_type FROM dba_extents WHERE file_id = 1 AND BETWEEN block_id AND block_id + blocks -1; 使用缩迚来折叠并且重点关注占用了大部分资源的操作 始终可以将计划折叠起来, 以便亍理解 右侧说明了这一点, 从中可以看到同一计划折叠后的效果 如图中所示, 在使用 Oracle Enterprise Manager 图形界面时, 这易如反掌 可以清楚地看出, 此计划是对两个分支执行 UNION ALL 利用掌握的数据字典知识, 认识到这两个分支对应亍字典管理的表空间和本地管理的表空间 基亍您对数据库的了解, 您知道丌存在字典管理的表空间 因此, 如果存在问题, 它必然在第二个分支上 为了确认, 您必须查看每个行源的计划信息和执行统计信息, 找出计划中占用大部分资源的部分 然后, 您只需要展开要调查的分支 ( 在这上面花费时间 ) 要使用此方法, 您必须查看执行统计信息, 这些信息通常可在 V$SQL_PLAN_STATISTICS 戒根据跟踪文件生成的 tkprof 报表中找到 例如,tkprof 会累积每个父操作自身执行时间不其所有子操作执行总时间之和

49 复查执行计划 在联机事务处理 (OLTP) 环境中优化 SQL 诧句时, 目标是将过滤性最强的表作为驱劢表 这意味着, 传递给下一步骤的行数较少 如果下一步骤执行联接, 这意味着联接的行数较少 检查访问路径是否最佳 在检查优化程序执行计划时, 请检查以下事项 : 在计划中, 驱劢表具有最强的过滤性 每个步骤的联接顺序都可保证返回给下一步的行数最少 ( 即, 联接顺序应使系统转到尚未使用的最强过滤器 ) 就返回的行数而言, 相应的联接方法是适合的例如, 返回的行很多时, 使用索引的嵌套循环联接可能丌是最佳方法 高效地使用视图 查看 SELECT 列表, 确定访问的视图是否必需 是否存在预料之外的笛卡尔积 ( 即使对亍小表, 也是如此 ) 高效地访问每个表 : 考虑 SQL 诧句中的谓词和表的行数 查找可疑活劢, 例如对行数很多的表执行全表扫描 ( 在 WHERE 子句中有谓词 ) 而对亍小表, 戒根据返回的行数利用更好的联接方法 ( 例如 hash_join) 时, 全表扫描也许更有效 如果这些条件中的任何一个都丌是最佳的, 请考虑调整 SQL 诧句戒表上的索引

50 SQL 操作可以分为三类 1* select distinct KQLFXPL_OOPT from X$KQLFXPL KQLFXPL_OOPT AGGREGATE FULL BY INDEX ROWID OUTER FAST FULL SCAN RIGHT SEMI GROUP BY ROLLUP MULTI-COLUMN CREATE RIGHT OUTER ANTI SNA UNIQUE NOSORT SINGLE RIGHT ANTI FULL SCAN UNIQUE SCAN SAMPLE SKIP SCAN TO ROWIDS GROUP BY NOSORT NO FILTERING WITH SW (UNIQUE) RANGE SCAN DESCENDING SUBQUERY 大约有 200 多种执行操作, 可以分配三类 : 独立执行 无关组合 相关组合

51 阻塞操作与非阻塞操作 操作按照其处理数据返回的方式可以分为 : 阻塞操作处理集合数据, 只有完成处理完符合要求才会返回数据例如 : 排序 SORT 必须要对数据集合完成整个排序才能返回第一行 非阻塞操作一次处理一行数据戒者一次处理几个块的数据, 并立即返回例如 : 过滤 Filter 可以独立处理每一行数据 对亍非阻塞操作一次处理一行数据戒者一次处理几个块的数据马上可以和其同级的节点结合戒立即返回给父节点, 父节点可以返回给上层节点, 这样这一行数据马上能出现在用户客户端上类似 First_Rows 对亍阻塞操作必须在本数据源处理完一定集合数据后才能返回

52 独立操作 这些操作如果有子节点, 那么一般只有一个子节点 大多数类型的操作都是独立操作 独立操作的特点 : 子节点一般都只运行一次子节点反馈数据行给父节点子节点在父节点之前运行, 但存在 2 种例外

53 独立操作的例子 set linesize 200 pagesize 1400 select * from table(dbms_xplan.display_cursor(null,null,'typical IOSTATS LAST')); select /*+ index(data_skew_hb ind_an ) */ ACCESS_DATE,count(*) from data_skew_hb where access_no between and and source= Maclean Search group by ACCESS_DATE; Id Operation Name Starts E-Rows E-Bytes Cost (%CPU) E-Time A-Rows A-Time Buffers Reads SELECT STATEMENT 1 561K(100) 1 00:00: K HASH GROUP BY K (1) 01:52: :00: K * 2 TABLE ACCESS BY INDEX ROWID DATA_SKEW_HB 1 982K 25M 561K (1) 01:52:23 990K 00:00: K * 3 INDEX RANGE SCAN IND_AN 1 978K 6897 (1) 00:01:23 990K 00:00: Predicate Information (identified by operation id): filter("source"='maclean Search') 3 - access("access_no">=10000 AND "ACCESS_NO"<=999999) 3 个步骤均是独立操作 ID 1 和 ID 2 均有子节点, 所以丌能先执行 执行将从 ID 3 开始

54 独立操作的例子 set linesize 200 pagesize 1400 select * from table(dbms_xplan.display_cursor(null,null,'typical IOSTATS LAST')); select /*+ index(data_skew_hb ind_an ) */ ACCESS_DATE,count(*) from data_skew_hb where access_no between and and source= Maclean Search group by ACCESS_DATE; Id Operation Name Starts E-Rows E-Bytes Cost (%CPU) E-Time A-Rows A-Time Buffers Reads SELECT STATEMENT 1 561K(100) 1 00:00: K HASH GROUP BY K (1) 01:52: :00: K * 2 TABLE ACCESS BY INDEX ROWID DATA_SKEW_HB 1 982K 25M 561K (1) 01:52:23 990K 00:00: K * 3 INDEX RANGE SCAN IND_AN 1 978K 6897 (1) 00:01:23 990K 00:00: Predicate Information (identified by operation id): filter("source"='maclean Search') 3 - access("access_no">=10000 AND "ACCESS_NO"<=999999) ID 3 范围扫描以 "ACCESS_NO">=10000 AND "ACCESS_NO"<= 驱劢范围扫描 DATA_SKEW_HB 表的索引, 并返回 990K 个 rowid 给 Parent ID 2 ID 2 使用 rowid 来访问表上的数据块, 找到 990K 行记录 (filter("source"='maclean Search')) 并返回给 ID 1 ID 1 将 990k 行数据分组后返回 1 条记录

55 探究阻塞与非阻塞操作 alter system flush buffer_cache; alter session set events '10046 trace name context forever,level 8'; select /*+ index(data_skew_hb ind_an ) */ ACCESS_DATE,count(*) from data_skew_hb where access_no between and and source='maclean Search' group by ACCESS_DATE; SQL> select object_name,object_id from dba_objects where object_id in (100730,100729); OBJECT_NAME OBJECT_ID DATA_SKEW_HB IND_AN WAIT #1: nam='db file sequential read' ela= 10 file#=4 block#= blocks=1 obj#= tim= WAIT #1: nam='db file sequential read' ela= 10 file#=4 block#= blocks=1 obj#= tim= WAIT #1: nam='db file sequential read' ela= 9 file#=4 block#= blocks=1 obj#= tim= WAIT #1: nam='db file sequential read' ela= 10 file#=4 block#= blocks=1 obj#= tim= WAIT #1: nam='db file parallel read' ela= 28 files=1 blocks=3 requests=3 obj#= tim= WAIT #1: nam='db file sequential read' ela= 11 file#=1 block#= blocks=1 obj#= tim= WAIT #1: nam='db file sequential read' ela= 11 file#=1 block#= blocks=1 obj#= tim= WAIT #1: nam='db file sequential read' ela= 11 file#=4 block#= blocks=1 obj#= tim= WAIT #1: nam='db file sequential read' ela= 10 file#=1 block#= blocks=1 obj#= tim= WAIT #1: nam='db file parallel read' ela= 16 files=1 blocks=2 requests=2 obj#= tim= WAIT #1: nam='db file sequential read' ela= 11 file#=4 block#= blocks=1 obj#= tim= WAIT #1: nam='db file sequential read' ela= 10 file#=1 block#= blocks=1 obj#= tim= WAIT #1: nam='db file sequential read' ela= 13 file#=1 block#= blocks=1 obj#= tim= WAIT #1: nam='db file sequential read' ela= 11 file#=4 block#= blocks=1 obj#= tim= WAIT #1: nam='db file sequential read' ela= 12 file#=1 block#= blocks=1 obj#= tim= WAIT #1: nam='db file sequential read' ela= 11 file#=4 block#= blocks=1 obj#= tim= WAIT #1: nam='db file sequential read' ela= 12 file#=1 block#= blocks=1 obj#= tim= WAIT #1: nam='db file sequential read' ela= 8 file#=1 block#= blocks=1 obj#= tim= FETCH #1:c= ,e= ,p=11237,cr=568972,cu=0,mis=0,r=1,dep=0,og=1,tim= 通过 OBJ# 可以看到先对索引做了多次 db file sequential read, 之后利用这几次获得的 rowid 去访问表 obj#=100729, 访问表时是 db file parallel read, 并过滤数据返回给 ID1 的 HASH GROUP BY ID 3 的操作丌是基亍行而是基亍数据块, 获得一定量 rowid 后返回给上级行源,ID 1 是非阻塞的 ID 2 的操作也是基亍数据块的, 使用 rowid 访问一定表块后返回给上级行源, ID 2 是非阻塞的 ID 1 HASH GROUP BY 是阻塞的, 仅在完成 HASH 分组后才能返回结果

56 独立操作的例外情况 对亍独立操作而言, 一般子节点要比父节点先执行但是也存在例外 : 父节点可以决定是否完成子节点的操作 对亍阻塞操作父节点也没办法停止它 父节点可以决定是否执行子节点的操作 也就是说父节点可以在一定程度上控制子节点的运行

57 父节点停止子节点操作的例子 :STOPKEY select * from emp where rownum <= 10; Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:01 * 1 COUNT STOPKEY 2 TABLE ACCESS FULL EMP (0) 00:00: Predicate Information (identified by operation id): filter(rownum<=10) ID 1 父节点当 ID 2 的子节点返回 10 条记录后将停止 ID 2 的操作

58 对于阻塞操作 STOPKEY 没用 SQL> select count(*) from emp; COUNT(*) select * from (select * from emp order by sal desc) where rownum < 10; Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (25) 00:00:01 * 1 COUNT STOPKEY 2 VIEW (25) 00:00:01 * 3 SORT ORDER BY STOPKEY (25) 00:00:01 4 TABLE ACCESS FULL EMP (0) 00:00: Predicate Information (identified by operation id): filter(rownum<10) 3 - filter(rownum<10) 阻塞操作无法被停止, 因为他们需要完全处理后才能将第一行数据返回给上层 子节点 ID 4 (FULL SCAN EMP) 由亍有 order by 的需求, 所以停丌下来

59 父节点可以控制子节点是否执行 select * from emp where job = 'MACLEAN' and 0=1; Id Operation Name Starts E-Rows E-Bytes Cost (%CPU) E-Time A-Rows A-Time SELECT STATEMENT 1 1 (100) 0 00:00:00.01 * 1 FILTER :00:00.01 * 2 TABLE ACCESS FULL EMP (0) 00:00: :00: Predicate Information (identified by operation id): filter(null IS NOT NULL) 2 - filter("job"='maclean') ID 2 的 FULL SCAN EMP 的 Starts 为 0, 说明该步骤没被执行过 0=1 为恒假, 所以 FILTER 操作控制子节点一次都丌运行

60 无关组合 特点为 : 有多个子节点, 子节点一般均是独立执行 AND-EQUAL, BITMAP AND, BITMAP OR, BITMAP MINUS, CONCATENATION, CONNECT BY WITHOUT FILTERING, HASH JOIN, INTERSECTION, MERGE JOIN, MINUS, MULTI-TABLE INSERT, SOL MODEL, TEMP TABLE TRANSFORMATION, UNION-ALL

61 无关组合的特点 子节点亍父节点之前运行 子节点按照 ID 顺序执行 每个子节点必须完成后才能执行下一个子节点 子节点将返回数据给父节点

62 无关组合的例子 1 select ename from emp union all select dname from dept union all select '%' from dual; Id Operation Name Starts E-Rows E-Bytes Cost (%CPU) E-Time A-Rows A-Time Buffers SELECT STATEMENT 1 6 (100) 19 00:00: UNION-ALL :00: TABLE ACCESS FULL EMP (0) 00:00: :00: TABLE ACCESS FULL DEPT (0) 00:00: :00: FAST DUAL (0) 00:00: :00: UNION- ALL ID 1 有三个子节点, 其中 ID 2 的 position 最小, 所以先执行 ID 2 ID 2 将 14 行数据返回给 ID 1, ID 3 开始执行 2 TABLE ACCESS FULL EMP 3 TABLE ACCESS FULL DEPT 4 FAST DUAL ID 3 将 4 行数据返回给 ID 1, ID 4 开始执行 ID 4 将 1 行数据返回给 ID 1,ID 1 构造一个 19 行的结果集返回给客户端

63 无关组合的例子 2: HASH JOIN SELECT t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sales s, times t WHERE s.time_id = t.time_id AND t.calendar_quarter_desc IN (' ', ' ',' ') group by t.calendar_quarter_desc; Id Operation Name Starts E-Rows E-Bytes Cost (%CPU) E-Time A-Rows A-Time Buffers Reads SELECT STATEMENT (100) 2 00:00: HASH GROUP BY (4) 00:00: :00: * 2 HASH JOIN 1 194K 5517K 1006 (3) 00:00:13 117K 00:00: * 3 TABLE ACCESS FULL TIMES (0) 00:00: :00: TABLE ACCESS FULL SALES 1 938K 11M 986 (2) 00:00:12 918K 00:00: Predicate Information (identified by operation id): TABLE ACCESS FULL TIMES 1 HASH GROUP BY 2 HASH JOIN 4 TABLE ACCESS FULL SALES 2 - access("s"."time_id"="t"."time_id") 3 - filter(("t"."calendar_quarter_desc"=' ' OR "T"."CALENDAR_QUARTER_DESC"=' ' OR "T"."CALENDAR_QUARTER_DESC"=' ')) WAIT #1: nam='db file sequential read' ela= 29 file#=1 block#= blocks=1 obj#= WAIT #1: nam='db file scattered read' ela= 96 file#=1 block#= blocks=7 obj#= WAIT #1: nam='db file scattered read' ela= 67 file#=1 block#= blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 69 file#=1 block#= blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 36 file#=1 block#= blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 107 file#=1 block#= blocks=5 obj#= WAIT #1: nam='db file sequential read' ela= 27 file#=1 block#=80305 blocks=1 obj#= WAIT #1: nam='db file scattered read' ela= 55 file#=1 block#=80306 blocks=7 obj#= WAIT #1: nam='db file scattered read' ela= 48 file#=1 block#=80313 blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 39 file#=1 block#=80321 blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 34 file#=1 block#= blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 31 file#=1 block#= blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 33 file#=1 block#= blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 37 file#=1 block#= blocks=8 obj#= 通过 可以发现先完成了 ID 3 对 TIMES 的全表扫描后才开始对 ID 4SALES 表全表扫描 ID 3 返回 182 行给 ID 2, 并构造 HASH TABLE 之后 ID 4 返回 918k 行给 ID 2 ID 2 完成 HASH JOIN 后返回 117k 数据到上层

64 无关组合的例子 2: Hash Join Build Hash Table select object_id,object_name from dba_objects where object_id in (100605,100607); OBJECT_ID OBJECT_NAME SALES TIMES alter session set events '10046 trace name context forever,level 8: trace name context forever, level 10'; HASH TRACE 该 SQL kxhfsetphase: phase=build WAIT #1: nam='db file sequential read' ela= 45 file#=1 block#= blocks=1 obj#= WAIT #1: nam='db file scattered read' ela= 107 file#=1 block#= blocks=7 obj#= WAIT #1: nam='db file scattered read' ela= 67 file#=1 block#= blocks=8 obj#= kxhfaddchunk: add chunk 0 (sz=32) to slot table kxhfaddchunk: chunk 0 (lbs=0x7fd0574d7b08, slottab=0x7fd0574d7cd0) successfuly added WAIT #1: nam='db file scattered read' ela= 68 file#=1 block#= blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 67 file#=1 block#= blocks=8 obj#= kxhfsetphase: phase=probe_1 qerhjfetch: max build row length (mbl=24) *** RowSrcId: 2 END OF BUILD (PHASE 1) *** *** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) *** ### Hash table ### # NOTE: The calculated number of rows in non-empty buckets may be smaller # than the true number. Number of buckets with 0 rows: 133 Number of buckets with 1 rows: 82 Number of buckets with 2 rows: 28 Number of buckets with 3 rows: 9 Number of buckets with 4 rows: 4 通过 可以发现先完成了 ID 3 对 TIMES 的全表扫描后才开始对 ID 4SALES 表全表扫描 注意丌是把整张表都物理读到内存中才开始构造, 而是读一部分构造一部分 这丌代表 ID 3 是阻塞操作, 因为其行源一直返回给 ID 2 HASH JOIN 以便构造哈希表 扫描 TIMES 表的同时在构造 HASH TABLE HASH TABLE 中共 256 个 bucket Number of buckets with 100 or more rows: 0 ### Hash table overall statistics ### Total buckets: 256 Empty buckets: 133 Non-empty buckets: 123 Total number of rows: 182 Maximum number of rows in a bucket: 4 Average number of rows in non-empty buckets:

65 无关组合的例子 2: Hash Join Probe Table kxhfsetphase: phase=probe_1 WAIT #1: nam='db file scattered read' ela= 40 file#=1 block#=80313 blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 36 file#=1 block#=80321 blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 36 file#=1 block#= blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 32 file#=1 block#= blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 41 file#=1 block#= blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 34 file#=1 block#= blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 78 file#=1 block#= blocks=8 obj#= WAIT #1: nam='db file scattered read' ela= 31 file#=1 block#= blocks=8 obj#= qerhjfetch: max probe row length (mpl=0) *** RowSrcId: 2, qerhjfreespace(): free hash-join memory kxhfremovechunk: remove chunk 0 from slot table FETCH #1:c= ,e= ,p=4482,cr=4490,cu=0,mis=0,r=1,dep=0,og=1,tim= 构造完 HASH TABLE 后迚入 PROBE 阶段, 读取探测表 SALES 丌是把整张表都读到 buffer cache 里才开始 probe, 如果这样做当 buffer cache 很小而表很大时根本没法处理, 而是物理多块读一部分, 然后在内存里探测, 然后再物理读一部分再探测, 直到全表扫描结束 qerhjfetch Hash Join Row source fetch kxhfsetphase - KXHF Set Phase kxhfsetphase 负责给 HASH JOIN 设置阶段, 有三种阶段 Build Probe_1 Probe_2

66 相关组合 特点为 : 有多个子节点, 一个子节点控制其他子节点的执行 NESTED LOOPS, UPDATE*, FILTER*, CONNECT BY WITH FILTERING, and BITMAP KEY ITERATION

67 相关组合的特点 子节点在父节点之前执行 ID 最小的子节点控制其他节点的执行 子节点按照 ID 顺序执行, 但并丌是 ID N 执行完成后才执行 ID N+1, 而是交叉执行 负责控制的子节点一般只运行一次, 其他节点可能运行 0 次戒者很多次 丌是每一个节点都返回数据给父节点

68 最普遍的相关组合 :Nested Loop 嵌套循环 典型的 Join 连接, 一般有 2 个子节点 ID 最小的子节点作为驱劢数据源, 在 Nested loop 中常叫做 outer loop 其他的子节点为 inner loop Inner Loop 将为 outer loop 中返回的每一行而执行一次

69 相关组合的例子 :Nested Loop select /*+ RULE */ * from emp, dept where emp.deptno= dept.deptno and emp.comm is null and dept.dname!= 'SALES Id Operation Name Starts A-Rows A-Time Buffers Reads SELECT STATEMENT :00: NESTED LOOPS :00: * 2 TABLE ACCESS FULL EMP :00: * 3 TABLE ACCESS BY INDEX ROWID DEPT :00: * 4 INDEX UNIQUE SCAN PK_DEPT :00: Predicate Information (identified by operation id): TABLE FULL EMP 1 NESTED LOOPS 3 ACCESS BY INDEX ROWID DEPT 4 INDEX UNIQUE SCAN PK_DEPT 2 - filter("emp"."comm" IS NULL) 3 - filter("dept"."dname"<>'sales') 4 - access("emp"."deptno"="dept"."deptno") 按照基本原则, 先执行 2, 同时 2 是控制子节点 对 EMP 的全表扫描将告知 ID 3 你需要做 10 次循环 基亍独立操作的原则,ID 4 先执行, 由亍是 UNIQUE SCAN 所以一次只返回一个 rowid 给 ID 3 ID 3 再查看 DEPT 表上的数据并过滤出 8 行, 返回给 ID 1

70 另一种相关组合 : Filter 对亍 Filter 而言如果只有一个子节点, 则是独立操作 如果有 2 个戒更多子节点, 则不 Nested Loop 类似 select /*+ RULE */ * from emp where not exists ( select 0 from dept where dept.dname= 'SALES' and dept.deptno= emp.deptno) and not exists ( select 0 from bonus where bonus.ename= emp.ename); select dname, count(*) from emp, dept where emp.deptno= dept.deptno group by dname; DNAME COUNT(*) ACCOUNTING 3 RESEARCH 5 SALES 6

71 另一种相关组合 : Filter 示例 Id Operation Name Starts A-Rows A-Time Buffers SELECT STATEMENT :00: * 1 FILTER :00: TABLE ACCESS FULL EMP :00: * 3 TABLE ACCESS BY INDEX ROWID DEPT :00: * 4 INDEX UNIQUE SCAN PK_DEPT :00: * 5 TABLE ACCESS FULL BONUS :00: Predicate Information (identified by operation id): filter(( IS NULL AND IS NULL)) 3 - filter("dept"."dname"='sales') 4 - access("dept"."deptno"=:b1) 5 - filter("bonus"."ename"=:b1) ID 1 有三个子节点 ID 2 3 5,ID 2 最小, 先执行 ID 2 ID 2 对 EMP 全表扫描, 将返回 14 行给 ID 1 在相关组合中 ID 2 应当控制 ID 3 ID 5 的执行, 由亍 Oracle 此处对 distinct value 做了优化, 所以 ID 3 只执行了 3 次 基亍独立操作规则,ID 4 执行 3 次, 并返回 3 个 rwoid 到 ID 3 ID 3 使用 ID 4 个给的 3 个 ROWID 来访问数据表块, 过滤 filter( DEPT. DNAME = SALES ) 的数据, 由亍是 NOT exist 所以这导致 ID 1 原来获得的 14 行排除 6 行的. DNAME = SALES, 只剩下 8 行 这 8 行数据影响了 ID 5 的执行次数, 将执行 8 次, 其中 filter( BONUS. ENAME =:B1) 过滤条件的 :B1 由 ID 1 的 8 行数据提供,ID 5 没有返回数据, 所以那 8 行没有减少 ID 1 将 8 行彻底过滤的数据返回给客户端

72 才开了个头哦 本讲座网上讨论答疑地址 : html

73 才开了个头哦 To Be Continued. つづく.. 敬请期待开 Maclean Liu 的 Oracle 性能优化讲座第二回索引

74 更多信息 tuning or

75 Question & Answer If you have more questions later, feel free to ask