Oracle数据库中级培训(执行计划)第6讲
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Oracle数据库中级培训 (执行计划部分)第6讲
上海全成通信技术有限公司 金刚(seniordba@) 金刚 2009-12
目录
PLAN(执行计划 执行计划) 一． SQL EXECUTION PLAN(执行计划) 成本分析入门) 二． SQL COST(成本分析入门 成本分析入门 表分区)设计 三． PARTITION(表分区 设计 表分区 四． INDEX(索引 索引) 索引 并行) 五． PARALLEL(并行 并行 提示) 六． HINT(提示 提示 七． 设计开发误区
步骤一：人工设置 和存储子系统处理能力。 用户下执行) 步骤一：人工设置CPU和存储子系统处理能力。(在sys用户下执行 和存储子系统处理能力 在 用户下执行 begin dbms_stats.set_system_stats('MBRC',12); dbms_stats.set_system_stats('MREADTIM',30); dbms_stats.set_system_stats('SREADTIM',5); dbms_stats.set_system_stats('CPUSPEED',1500); end; /
案例1:关于 案例 关于CPU成本讨论 关于 成本讨论
步骤三： 步骤三：分析执行计划
1)explain plan for select /*+ cpu_costing ordered_predicates */ * from t1 where v1 = 1 and n2 = 18 and n1 = 998 ;
分析如下： 分析如下： 1)类型转换30000次，比较30000+1500+75=31574 2)类型转换1次， 比较30002 3)类型转换30000次，比较30000+1500+1=31501 4)类型转换1次， 比较30002 5)类型转换30000次，比较1502次 结论： 结论： 1.COST(CPU)= 10,456,833/(1500*5000)=1.39 2.优化技巧，避免数据类型转换 优化技巧， 优化技巧
解释： 解释： 执行序列：观察Operation,最里面的最先执行；同一层次，从上到下顺序执行。 Operation,最里面的最先执行 1）执行序列：观察Operation,最里面的最先执行；同一层次，从上到下顺序执行。 因此，执行序列为2 >3->1因此，执行序列为2->3->1->0 多表连接，每次执行是两个表进行连接，连接算法有三种，这里采用hash连接。 hash连接 2) 多表连接，每次执行是两个表进行连接，连接算法有三种，这里采用hash连接。 表的连接算法：嵌套循环，哈希连接， 表的连接算法：嵌套循环，哈希连接，排序归并 Rows,Bytes,Cost含义 含义： 3) Rows,Bytes,Cost含义： Rows表示执行该步骤后返回的记录数 Rows表示执行该步骤后返回的记录数 Bytes表示执行该步骤后返回的字节数 表示执行该步骤后返回的字节数=Rows*AVG_LINE_LENGTH Bytes表示执行该步骤后返回的字节数=Rows*AVG_LINE_LENGTH COST表示执行到该步骤的累计成本 表示执行到该步骤的累计成本。 COST表示执行到该步骤的累计成本。
查看统计信息收集结果： select * from sys.aux_stats$; SNAME PNAME PVAL1 PVAL2 -------------------- -------------------- ---------- -----------------------------SYSSTATS_INFO STATUS COMPLETED SYSSTATS_INFO DSTART 12-07-2009 17:24 SYSSTATS_INFO DSTOP 12-07-2009 17:24 SYSSTATS_INFO FLAGS 1 SYSSTATS_MAIN CPUSPEEDNW 1370.55239 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN SREADTIM 5 SYSSTATS_MAIN MREADTIM 30 SYSSTATS_MAIN CPUSPEED 1500 SYSSTATS_MAIN MBRC 12 SYSSTATS_MAIN MAXTHR 4395008 SYSSTATS_MAIN SLAVETHR 197632 13 rows selected.
案例1:关于 案例 关于CPU成本讨论 关于 成本讨论
3.不同WHERE条件下的CPU_COST分析
1) where v1 = 1 and n2 = 18 and n1 = 998 2) where n1 = 998 and n2 = 18 and v1 = 1 3) where v1 = 1 and n1 = 998 and n2 = 18 4) where n1 = 998 and v1 = 1 and n2 = 18 5) where n2 = 18 and n1 = 998 and v1 = 1 6) where n2 = 18 and v1 = 1 and n1 = 998 7) where v1 = '1‘ and n2 =18 and n1 = 998
案例1:关于 案例 关于CPU成本讨论 关于 成本讨论
也可以通过，注意要在正常负载下执行，否则错误的统计数据会严重误导成 本的计算，最终导致低效的执行计划。 exec dbms_stats.gather_system_stats(‘start’); ********半小时后继续执行下面的语句结束统计信息收集************* exec dbms_stats.gather_system_stats(‘stop’);
案例1:关于 案例 关于CPU成本讨论 关于 成本讨论
序号 过滤谓词 CPU成本 ------------------------------------------------------------------------------------------------------------1 TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998 10,456,833 2 "N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1 7,414,573 3 TO_NUMBER("V1")=1 AND "N1"=998 AND "N2"=18 10,453,130 4 "N1"=998 AND TO_NUMBER("V1")=1 AND "N2"=18 7,414,668 5 "N2"=18 AND "N1"=998 AND TO_NUMBER("V1")=1 7,488,635 6 "N2"=18 AND TO_NUMBER("V1")=1 AND "N1"=998 7,640,558 7 "V1"='1' AND "N2"=18 AND "N1"=998 7,492,333
执行计划解释
SELECT ST_NAME, E.SALARY, J.JOB_TITLE FROM EMPLOYEES E, JOBS J WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000; Execution Plan ---------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 6 | 360 | 6 (17)| 00:00:01 | |* 1 | HASH JOIN | | 6 | 360 | 6 (17)| 00:00:01 | |* 2 | TABLE ACCESS FULL| EMPLOYEES| 6 | 204 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| JOBS | 19 | 494 | 2 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------1 - access("E"."JOB_ID"="J"."JOB_ID") 2 - filter("E"."SALARY">12000)
目录
一．
SQL EXECUTION PLAN(执行计划 执行计划) 执行计划
COST(成本分析入门 成本分析入门) 二． SQL COST(成本分析入门) PARTITION(表分区 表分区)设计 三． PARTITION(表分区)设计 索引) 四． INDEX(索引 索引 并行) 五． PARALLEL(并行 并行 提示) 六． HINT(提示 提示 七． 设计开发误区
பைடு நூலகம்
案例2:关于数据类型的选择 案例 关于数据类型的选择 关于日期数值 日期数值数据类型的选择 日期数值
步骤一： 步骤一：准备表和数据并收集统计数据 create table t2 (d1 date, n1 number(8), v1 varchar2(8));
insert into t2 select d1, to_number(to_char(d1,'yyyymmdd')), to_char(d1,'yyyymmdd') from ( select to_date('19900101','yyyymmdd') + rownum from all_objects where rownum <= 18270 ); exec dbms_stats.gather_table_stats(user,'t2');
案例1:关于 案例 关于CPU成本讨论 关于 成本讨论
步骤二： 步骤二：构建表和数据并收集统计数据
drop table t1; create table t1( v1,n1,n2) as select to_char(mod(rownum,20)), rownum, mod(rownum,20) from all_objects where rownum <= 30000; 在当前用户模式下收集统计信息： begin dbms_stats.gather_table_stats(user,'t1',cascade => true); end; /
配置执行计划
在SYS用户下运行 $ORACLE_HOME/rdbms/admin/xplan.sql 运行$ORACLE_HOME/sqlplus/admin/plustrce.sql 授权 GRANT PLUS_TRACE TO 目标用户
获取执行计划的常用的三种方法
SET AUTOTRACE ON EXPLAIN PLAN FOR SELECT … SELECT * FROM table(dbms_xplan.display); 第三方工具,比方说PL/SQL DEV开发工具等
成本计算公式
COST=( #SRds*sreadtim + #MRds*mreadtim + #CPUCycles / cpuspeed )/sreadtim
案例1:关于 案例 关于CPU成本讨论 关于 成本讨论
1.访问某个数据行的CPU成本中绝大部分来自于查找、 锁存和销毁缓冲块操作。 2.关于CPU成本案例分析
上海全成通信技术有限公司 金刚(seniordba@) 金刚 2009-12
目录
PLAN(执行计划 执行计划) 一． SQL EXECUTION PLAN(执行计划) 成本分析入门) 二． SQL COST(成本分析入门 成本分析入门 表分区)设计 三． PARTITION(表分区 设计 表分区 四． INDEX(索引 索引) 索引 并行) 五． PARALLEL(并行 并行 提示) 六． HINT(提示 提示 七． 设计开发误区
步骤一：人工设置 和存储子系统处理能力。 用户下执行) 步骤一：人工设置CPU和存储子系统处理能力。(在sys用户下执行 和存储子系统处理能力 在 用户下执行 begin dbms_stats.set_system_stats('MBRC',12); dbms_stats.set_system_stats('MREADTIM',30); dbms_stats.set_system_stats('SREADTIM',5); dbms_stats.set_system_stats('CPUSPEED',1500); end; /
案例1:关于 案例 关于CPU成本讨论 关于 成本讨论
步骤三： 步骤三：分析执行计划
1)explain plan for select /*+ cpu_costing ordered_predicates */ * from t1 where v1 = 1 and n2 = 18 and n1 = 998 ;
分析如下： 分析如下： 1)类型转换30000次，比较30000+1500+75=31574 2)类型转换1次， 比较30002 3)类型转换30000次，比较30000+1500+1=31501 4)类型转换1次， 比较30002 5)类型转换30000次，比较1502次 结论： 结论： 1.COST(CPU)= 10,456,833/(1500*5000)=1.39 2.优化技巧，避免数据类型转换 优化技巧， 优化技巧
解释： 解释： 执行序列：观察Operation,最里面的最先执行；同一层次，从上到下顺序执行。 Operation,最里面的最先执行 1）执行序列：观察Operation,最里面的最先执行；同一层次，从上到下顺序执行。 因此，执行序列为2 >3->1因此，执行序列为2->3->1->0 多表连接，每次执行是两个表进行连接，连接算法有三种，这里采用hash连接。 hash连接 2) 多表连接，每次执行是两个表进行连接，连接算法有三种，这里采用hash连接。 表的连接算法：嵌套循环，哈希连接， 表的连接算法：嵌套循环，哈希连接，排序归并 Rows,Bytes,Cost含义 含义： 3) Rows,Bytes,Cost含义： Rows表示执行该步骤后返回的记录数 Rows表示执行该步骤后返回的记录数 Bytes表示执行该步骤后返回的字节数 表示执行该步骤后返回的字节数=Rows*AVG_LINE_LENGTH Bytes表示执行该步骤后返回的字节数=Rows*AVG_LINE_LENGTH COST表示执行到该步骤的累计成本 表示执行到该步骤的累计成本。 COST表示执行到该步骤的累计成本。
查看统计信息收集结果： select * from sys.aux_stats$; SNAME PNAME PVAL1 PVAL2 -------------------- -------------------- ---------- -----------------------------SYSSTATS_INFO STATUS COMPLETED SYSSTATS_INFO DSTART 12-07-2009 17:24 SYSSTATS_INFO DSTOP 12-07-2009 17:24 SYSSTATS_INFO FLAGS 1 SYSSTATS_MAIN CPUSPEEDNW 1370.55239 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN SREADTIM 5 SYSSTATS_MAIN MREADTIM 30 SYSSTATS_MAIN CPUSPEED 1500 SYSSTATS_MAIN MBRC 12 SYSSTATS_MAIN MAXTHR 4395008 SYSSTATS_MAIN SLAVETHR 197632 13 rows selected.
案例1:关于 案例 关于CPU成本讨论 关于 成本讨论
3.不同WHERE条件下的CPU_COST分析
1) where v1 = 1 and n2 = 18 and n1 = 998 2) where n1 = 998 and n2 = 18 and v1 = 1 3) where v1 = 1 and n1 = 998 and n2 = 18 4) where n1 = 998 and v1 = 1 and n2 = 18 5) where n2 = 18 and n1 = 998 and v1 = 1 6) where n2 = 18 and v1 = 1 and n1 = 998 7) where v1 = '1‘ and n2 =18 and n1 = 998
案例1:关于 案例 关于CPU成本讨论 关于 成本讨论
也可以通过，注意要在正常负载下执行，否则错误的统计数据会严重误导成 本的计算，最终导致低效的执行计划。 exec dbms_stats.gather_system_stats(‘start’); ********半小时后继续执行下面的语句结束统计信息收集************* exec dbms_stats.gather_system_stats(‘stop’);
案例1:关于 案例 关于CPU成本讨论 关于 成本讨论
序号 过滤谓词 CPU成本 ------------------------------------------------------------------------------------------------------------1 TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998 10,456,833 2 "N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1 7,414,573 3 TO_NUMBER("V1")=1 AND "N1"=998 AND "N2"=18 10,453,130 4 "N1"=998 AND TO_NUMBER("V1")=1 AND "N2"=18 7,414,668 5 "N2"=18 AND "N1"=998 AND TO_NUMBER("V1")=1 7,488,635 6 "N2"=18 AND TO_NUMBER("V1")=1 AND "N1"=998 7,640,558 7 "V1"='1' AND "N2"=18 AND "N1"=998 7,492,333
执行计划解释
SELECT ST_NAME, E.SALARY, J.JOB_TITLE FROM EMPLOYEES E, JOBS J WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000; Execution Plan ---------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 6 | 360 | 6 (17)| 00:00:01 | |* 1 | HASH JOIN | | 6 | 360 | 6 (17)| 00:00:01 | |* 2 | TABLE ACCESS FULL| EMPLOYEES| 6 | 204 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| JOBS | 19 | 494 | 2 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------1 - access("E"."JOB_ID"="J"."JOB_ID") 2 - filter("E"."SALARY">12000)
目录
一．
SQL EXECUTION PLAN(执行计划 执行计划) 执行计划
COST(成本分析入门 成本分析入门) 二． SQL COST(成本分析入门) PARTITION(表分区 表分区)设计 三． PARTITION(表分区)设计 索引) 四． INDEX(索引 索引 并行) 五． PARALLEL(并行 并行 提示) 六． HINT(提示 提示 七． 设计开发误区
பைடு நூலகம்
案例2:关于数据类型的选择 案例 关于数据类型的选择 关于日期数值 日期数值数据类型的选择 日期数值
步骤一： 步骤一：准备表和数据并收集统计数据 create table t2 (d1 date, n1 number(8), v1 varchar2(8));
insert into t2 select d1, to_number(to_char(d1,'yyyymmdd')), to_char(d1,'yyyymmdd') from ( select to_date('19900101','yyyymmdd') + rownum from all_objects where rownum <= 18270 ); exec dbms_stats.gather_table_stats(user,'t2');
案例1:关于 案例 关于CPU成本讨论 关于 成本讨论
步骤二： 步骤二：构建表和数据并收集统计数据
drop table t1; create table t1( v1,n1,n2) as select to_char(mod(rownum,20)), rownum, mod(rownum,20) from all_objects where rownum <= 30000; 在当前用户模式下收集统计信息： begin dbms_stats.gather_table_stats(user,'t1',cascade => true); end; /
配置执行计划
在SYS用户下运行 $ORACLE_HOME/rdbms/admin/xplan.sql 运行$ORACLE_HOME/sqlplus/admin/plustrce.sql 授权 GRANT PLUS_TRACE TO 目标用户
获取执行计划的常用的三种方法
SET AUTOTRACE ON EXPLAIN PLAN FOR SELECT … SELECT * FROM table(dbms_xplan.display); 第三方工具,比方说PL/SQL DEV开发工具等
成本计算公式
COST=( #SRds*sreadtim + #MRds*mreadtim + #CPUCycles / cpuspeed )/sreadtim
案例1:关于 案例 关于CPU成本讨论 关于 成本讨论
1.访问某个数据行的CPU成本中绝大部分来自于查找、 锁存和销毁缓冲块操作。 2.关于CPU成本案例分析