Oracle-Database-11g：性能优化less-09PPT.ppt

版权所有 2008，,Oracle。,保留所有权利。,9-,*,Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,确定存在问题的,SQL,语句,课程目标,学完本课后，应能完成下列工作：,描述,SQL,语句处理,描述优化程序的作用,查看,SQL,语句统计信息,确定性能不佳的,SQL,语句,生成和查看执行计划,生成,tkprof,报表,生成优化程序跟踪,SQL,语句处理阶段,关闭,打开,提取,绑定,分析,执行,分析阶段分析阶段：,始终：,检查语法,检查语义和权限,软分析：,在共享池中搜索语句,硬分析：,合并视图定义和子查询,确定执行计划,SQL,存储,SQLAREA,游标上下文区域,SELECT,语句 2,游标上下文区域,SELECT,语句 1,共享池,SELECT,语句 2,SELECT,语句 1,SELECT,语句 1,游标的使用和分析,分析过程：,查找并执行打开的游标。,在会话高速缓存中查找关闭的游标。,搜索散列链（软分析）。,构建游标（硬分析）。,打开的游标,关闭的游标,会话内存(,UGA),共享池(,SGA),游标句柄,散列链,1,2,3,4,仅包含备注的幻灯片,SQL,语句处理阶段：绑定,绑定阶段：,检查语句的绑定变量,为绑定变量分配或重新分配值,下列情况下，绑定变量会影响性能：,通过使用共享游标来减少分析。,对于不同的绑定值，使用不同的执行计划可能带来性能优势。,SQL,语句处理阶段：执行和提取,执行阶段：,执行,SQL,语句,针对数据操纵语言(,DML),语句执行所需的,I/O,和排序,提取阶段：,针对查询检索行,视需要对查询进行排序,使用数组提取机制,处理,DML,语句,数据库,数据文件,控制文件,重做日志文件,UPDATE employees.,用户,进程,SGA,数据库,缓冲区高速缓存,共享池,重做日志,缓冲区,服务器,进程,3,1,4,2,完整注释页,实例,数据库,数据文件,控制文件,用户进程,SGA,数据库,缓冲区高速缓存,共享池,重做日志,缓冲区,服务器,进程,提交处理,LGWR,重做日志文件,Oracle,优化程序的作用,Oracle,查询优化程序确定最高效的执行计划，这是处理任何,SQL,语句过程中最重要的一个步骤。,优化程序将：,评估表达式和条件,使用对象和系统统计信息,确定如何访问数据,确定如何联接表,确定哪条访问路径最有效,完整注释页,确定不良,SQL,不良,SQL,会在没有必要的情况下过多地使用资源。,不良,SQL,具有以下特征：,分析时间长,过多的,I/O（,物理读写）,过多的,CPU,时间,过多的等待,顶级,SQL,报表,按,CPU,时间排序的,SQL,按获取数排序的,SQL,什么是执行计划,执行计划是当优化程序执行,SQL,语句并执行某一操作时所执行的一组步骤。,HJ,SORT,HJ,查看执行计划的方法,EXPLAIN PLAN,SQL,跟踪,自动工作量资料档案库,V$SQL_PLAN,SQL*Plus,AUTOTRACE,使用执行计划,确定当前的执行计划,确定索引的效果,确定访问路径,确认使用索引,确认可使用的执行计划,HJ,SORT,NL,HJ,SORT,HJ,DBMS_XPLAN,程序包：概览,DBMS_XPLAN,程序包提供了一种显示以下内容的输出的简单方法：,EXPLAIN PLAN,命令,自动工作量资料档案库(,AWR),V$SQL_PLAN,固定视图和,V$SQL_PLAN_STATISTICS_ALL,固定视图,DBMS_XPLAN,程序包提供了三种表函数，可用来检索和显示执行计划：,DISPLAY,DISPLAY_AWR,DISPLAY_CURSOR,完整注释页,EXPLAIN,PLAN,命令,生成优化程序执行计划,在,PLAN,表中存储计划,不执行语句本身,HJ,SORT,HJ,EXPLAIN PLAN,Plan,表,EXPLAIN PLAN,命令：示例,EXPLAIN PLAN,SET STATEMENT_ID=demo01 FOR,SELECT e.last_name,d.department_name,FROM hr.employees e,hr.departments d,WHERE e.department_id=d.department_id;,Explained.,注：,EXPLAIN PLAN,命令不实际执行语句。,SET STATEMENT_ID,=,text,EXPLAIN PLAN,INTO,your plan table,FOR,statement,EXPLAIN PLAN,命令：输出,Plan hash value:1343509718,-,|Id|Operation|Name|Rows|Bytes|Cost(%CPU|,-,|0|SELECT STATEMENT|106|2862|6 (17|,|1|MERGE JOIN|106|2862|6 (17|,|2|TABLE ACCESS BY INDEX ROWID|DEPARTMENTS|27|432|2 (0|,|3|INDEX FULL SCAN|DEPT_ID_PK|27|1 (0|,|*4|SORT JOIN|107|1177|4 (25|,|5|TABLE ACCESS FULL|EMPLOYEES|107|1177|3 (0|,-,Predicate Information(identified by operation id):,-,4-access(E.DEPARTMENT_ID=D.DEPARTMENT_ID),filter(E.DEPARTMENT_ID=D.DEPARTMENT_ID),18 rows selected.,SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY();,读取执行计划,0,SELECT STATEMENT,SORT JOIN,1,3,5,MERGE JOIN,对,EMPLOYEES,表执行,FULL TABLE SCAN,对,DEPARTMENTS,表执行,TABLE ACCESS BY INDEX ROWID,INDEX FULL SCAN,DEPT_ID_PK,2,4,使用,V$SQL_PLAN,视图,V$SQL_PLAN,提供了一种方法，用于检查最近执行过的游标的执行计划。,V$SQL_PLAN,中的信息非常类似于,EXPLAIN PLAN,语句的输出：,EXPLAIN PLAN,显示执行相应语句时可以使用的理论计划。,V$SQL_PLAN,包含所用的实际计划。,V$SQL_PLAN,列,注：此处仅列出一部分列。,HASH_VALUE,此游标的父游标的句柄地址,使用此执行计划的子游标编号,具有相同的,PARENT_ID,的操作的处理顺序,操作当前步骤的输出的下一执行步骤的,ID,执行计划中每个步骤所分配的编号,库高速缓存中父语句的散列值,ADDRESS,CHILD_NUMBER,POSITION,PARENT_ID,ID,查询,V$SQL_PLAN,SQL_ID cfz0cdukrfdnu,child number 0,-,SELECT e.last_name,d.department_name,FROM hr.employees e,hr.departments d WHERE,e.department_id=d.department_id,Plan hash value:1343509718,-,|Id|Operation|Name|Rows|Bytes|Cost(%CPU|,-,|0|SELECT STATEMENT|6(100|,|1|MERGE JOIN|106|2862|6 (17|,|2|TABLE ACCESS BY INDEX ROWID|DEPARTMENTS|27|432|2 (0|,|3|INDEX FULL SCAN|DEPT_ID_PK|27|1 (0|,|*4|SORT JOIN|107|1177|4 (25|,|5|TABLE ACCESS FULL|EMPLOYEES|107|1177|3 (0|,-,Predicate Information(identified by operation id):,-,4-access(E.DEPARTMENT_ID=D.DEPARTMENT_ID),filter(EDEPARTMENT_ID=D.DEPARTMENT_ID),24 rows selected.,SELECT PLAN_TABLE_OUTPUT FROM,TABLE(DBMS_XPLAN.DISPLAY_CURSOR(cfz0cdukrfdnu);,V$SQL_PLAN_STATISTICS,视图,V$SQL_PLAN_STATISTICS,提供了实际的执行统计信息。,通过,V$SQL_PLAN_STATISTICS_ALL,，,可并列比较优化程序评估结果。,查询,AWR,SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_AWR454rug2yva18w);,PLAN_TABLE_OUTPUT,-,SQL_ID,454rug2yva18w,-,select/*example*/*from hr.employees natural join hr.departments,Plan hash value:2052257371,-,|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|,-,|0|SELECT STATEMENT|6(100)|,|1|HASH JOIN|11|968|6 (17)|00:00:01|,|2|TABLE ACCESS FULL|DEPARTMENTS|11|220|2 (0)|00:00:01|,|3|TABLE ACCESS FULL|EMPLOYEES|107|7276|3 (0)|00:00:01|,-,完整注释页,SQL*Plus,AUTOTRACE,OFF,TRACEONLY,EXPLAIN,STATISTICS,SHOW AUTOTRACE,SET AUTOTRACE,ON,使用,SQL*Plus,AUTOTRACE,使用,AUTOTRACE,启动跟踪语句：,隐藏语句输出：,仅显示执行计划：,使用列设置控制布局,set autotrace on,set autotrace traceonly,set autotrace traceonly explain,SQL*Plus,AUTOTRACE,：,统计信息,set autotrace traceonly statistics,SELECT*,FROM products;,Statistics,-,1 recursive calls,0 db block gets,9 consistent gets,3 physical reads,0 redo size,15028 bytes sent via SQL*Net to client,556 bytes received via SQL*Net from client,6 SQL*Net roundtrips to/from client,0 sorts(memory),0 sorts(disk),72 rows processed,SQL,跟踪工具,通常在会话级启用,收集按照会话分组的,SQL,语句的会话统计信息,产生可通过,TKPROF,格式化的输出,报表文件,数据库,跟踪文件,TKPROF,服务器进程,完整注释页,使用,SQL,跟踪工具的方法,设置初始化参数。,启用跟踪。,运行应用程序。,禁用跟踪。,关闭会话。,格式化跟踪文件。,解释输出。,报表文件,数据库,跟踪文件,TKPROF,SQL,跟踪,初始化参数,STATISTICS_LEVEL=BASIC|,TYPICAL,|ALL,TIMED_STATISTICS=false|,true,MAX_DUMP_FILE_SIZE=n|,unlimited,DIAGNOSTIC_DEST=directory_path|,$ORACLE_BASE/diag,完整注释页,启用,SQL,跟踪,SQLEXEC dbms_monitor.database_trace_enable();,对于当前会话：,对于任何会话：,对于实例范围的跟踪：,SQLEXEC dbms_monitor.session_trace_enable;,SQLEXECUTE dbms_session.set_sql_trace(true);,SQLEXECUTE dbms_system.set_sql_trace_in_session,2 (,session_id,serial_id,true);,SQLEXEC dbms_monitor.database_trace_disable();,禁用,SQL,跟踪,对于当前会话：,对于任何会话：,对于实例范围的跟踪：,SQLEXEC dbms_monitor.session_trace_disable;,SQLEXECUTE dbms_session.set_sql_trace(false);,SQLEXECUTE dbms_system.set_sql_trace_in_session,2 (,session_id,serialid,false);,格式化跟踪文件,TKPROF,命令示例：,OS tkprof,OS tkprof ora_902.trc run1.txt,OS tkprof ora_902.trc run2.txt sys=no,sort=execpu print=3,OS tkprof,tracefile outputfile,options,TKPROF,命令选项,SORT=option,PRINT=n,EXPLAIN=username/password,INSERT=filename,SYS=NO,AGGREGATE=NO,RECORD=filename,TABLE=schema.tablename,完整注释页,TKPROF,命令的输出,SQL,语句的文本,（语句和递归调用的）跟踪统计分为三个,SQL,处理步骤：,分析,将,SQL,语句转换为执行计划,执行,执行语句,（此步骤修改,INSERT,、,UPDATE,和,DELETE,语句的数据。）,提取,检索查询所返回的行,（提取的执行仅适用于,SELECT,语句。）,TKPROF,命令的输出,有七类跟踪统计信息：,Count,执行过程的次数,CPU,处理的秒数,Elapsed,执行的总秒数,Disk,物理块读取数,Query,一致读取的逻辑缓冲区读取数,Current,当前模式读取的逻辑缓冲区数,Rows,提取或执行操作所处理的行数完整注释页,TKPROF,命令的输出,TKPROF,输出还包括以下内容：,递归,SQL,语句,库高速缓存未命中数,分析用户,ID,执行计划,优化程序模式或提示,行源操作,.,Misses in library cache during parse:1,Optimizer mode:ALL_ROWS,Parsing user id:85 (SH),Rows Row Source Operation,-,1 SORT AGGREGATE(cr=1457 pr=1455 pw=1455 time=0 us),77 TABLE ACCESS FULL CUSTOMERS(cr=1457 pr=1455 pw=1455 time=3338,.,完整注释页,不带索引的,TKPROF,输出：示例,.,select max(cust_credit_limit),from customers where cust_city=Paris,call count cpu elapsed disk query current rows,-,Parse 1 0.00 0.00 0 0 0 0,Execute 1 0.00 0.00 0 0 0 0,Fetch 2 0.03 0.03 1455 1459 0 1,-,total 4 0.04 0.04 1455 1459 0 1,Misses in library cache during parse:1,Optimizer mode:ALL_ROWS,Parsing user id:85 (SH),Rows Row Source Operation,-,1 SORT AGGREGATE(cr=1459 pr=1455 pw=1455 time=0 us),77 TABLE ACCESS FULL CUSTOMERS(cr=1459 pr=1455 pw=1455 time=170 us cost=406,size=1260 card=90),带有索引的,TKPROF,输出：示例,.,select max(cust_credit_limit),from customers where cust_city=Paris,call count cpu elapsed disk query current rows,-,Parse 1 0.00 0.00 0 0 0 0,Execute 1 0.00 0.00 0 0 0 0,Fetch 2 0.00 0.00 77 77 0 1,-,total 4 0.01 0.01 77 77 0 1,Misses in library cache during parse:1,Optimizer mode:ALL_ROWS,Parsing user id:85 (SH),Rows Row Source Operation,-,1 SORT AGGREGATE(cr=77 pr=77 pw=77 time=0 us),77 TABLE ACCESS BY INDEX ROWID CUSTOMERS(cr=77 pr=77 pw=77 time=555 us,cost=85 size=1260 card=90),77 INDEX RANGE SCAN CUST_CUST_CITY_IDX(cr=2 pr=2 pw=2 time=1 us cost=1,size=0 card=90)(object id 75264),生成优化程序跟踪,设置一个事件10053 优化程序跟踪。,执行相关语句。,查找并查看跟踪文件。,SQL ALTER SESSION SET EVENTS,2 10053 trace name context forever,level 1;,SQL select*,2 from hr.employees natural join hr.departments,3 where department_id=10;,小结,在本课中，您应该已经学会如何：,描述,SQL,语句处理,描述优化程序的作用,查看,SQL,语句统计信息,确定性能不佳的,SQL,语句,生成和查看解释计划,生成,tkprof,报表,生成优化程序跟踪,练习9：概览使用执行计划实用程序,本练习包含以下主题：,使用,AUTOTRACE,使用,EXPLAIN PLAN,使用,AWR,使用,DBMS_XPLAN,检索执行计划,使用,tkprof,生成优化程序跟踪,