oracle性能优化交流执行计划简介

1、汉得技术交流性能优化-执行计划简介Date： Version：Author： 常海亮目录目录执行计划介绍查看执行计划执行计划简介谓词介绍常用索引扫描常用连接方式事件介绍如何发现问题一般优化方法实例分析自动调优建议2执行计划介绍3执行计划介绍4执行计划：一条查询语句在ORACLE中的执行过程或访问路径的描述；一条合法的语句在解析执行时，就会产生一条执行计划，可以从视图v$sql_plan查询，一条语句生产的第一个游标的CHILD_NUMBER（即v$sql_plan中的CHILD_NUMBER）为0，同一条sql语句可能因为环境或版本等其他因素不同而产生不同的执行计划，即一条sql可能有多个CH

2、ILD_NUMBER；执行计划中的主要信息：SQL信息结构化执行计划信息：操作、对象、结果集、成本、CPU、时间等信息;查询块名称和对象别名信息;执行计划概要信息：文本描述的执行路径;谓词信息;列结构信息;查看执行计划5PLSQL中F5或EXPLAIN PLANEXPLAIN PLAN FOR SELECT do.OBJECT_ID,do.OBJECT_NAME,do.OBJECT_TYPE FROM dba_objects do WHERE do.OWNER = APPSVIEW;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);SQLPLUS下使用AUTOTR

3、ACE命令SET AUTOTRACE ON;-包含执行计划、统计信息、以及数据输出，需要等SQL执行完SET AUTOTRACE TRACEONLY;-有执行计划、统计信息，不包含结果输出SET AUTOTRACE OFF;-关闭AUTOTRACESET AUTOTRACE ON EXPLAIN;-包含执行计划、数据输出，没有统计信息 SET AUTOTRACE TRACEONLY STAT;-只包含有统计信息 开启AUTOTRACE诊断后，系统中有两个SESSION对应于同一个PROCESS，一个SESSION用于执行查询，另一个用于记录执行计划和输出最终结果；SELECT s.sid,s.

4、serial#,p.spid,p.pid FROM v$session s,v$process p WHERE s.paddr = p.addr AND p.pid = userenv(pid);DBMS_XPLAN.DISPLAY_CURSOR查询执行SQL;获取执行SQL的SQL_ID:SELECT s.sql_id,s.child_number,s.sql_text FROM v$sql s WHERE upper(s.sql_text) LIKE %CUX_OBJECTS% AND s.last_active_time SYSDATE - 1 / 24 / 60;根据SQL_ID获取执

5、行计划： SELECT * FROM TABLE(dbms_xplan.display_cursor(a1wtffgp387ku, 0,typical);-典型的，默认为该值 SELECT * FROM TABLE(dbms_xplan.display_cursor(00ssx97nsp296, 0,advanced allstats );-高级的SELECT * FROM TABLE(dbms_xplan.display_cursor(cqxm1nmtb05u8,0,advanced -outline -alias);详细参考： 查看执行计划6SQL_TRACE设置-开启SQL诊断ALTER

6、 SESSION SET SQL_TRACE=TRUE;-执行SQL-关闭SQL诊断ALTER SESSION SET SQL_TRACE =FALSE; -跟踪其他SESSIONSELECT SID,serial#,username FROM v$session WHERE sid =;exec dbms_system.set_sql_trace_in_session(61, 76, TRUE);exec dbms_system.set_sql_trace_in_session(61, 76, FALSE);10046事件-开启当前Session的10046诊断alter session s

7、et events 10046 trace name context forever , level 12 ;-执行SQL-关闭10046诊断alter session set events 10046 trace name context off ;-其他SESSIONexec dbms_system.set_ev(706 , 23263, 10046, 12 , NULL);exec dbms_system.set_ev(706 , 23263, 10046, 0 , NULL);-获取spidSELECT spid FROM v$process WHERE addr = (SELECT

8、paddr FROM v$session s WHERE s.sid = userenv(sid);-获取TRACE文件路径SELECT p.value FROM v$parameter p WHERE = user_dump_dest;-根据spid查找TRACE文件 ls *36283* EBSDEV_ora_36283.trc-格式化输出TRACE文件tkprof EBSDEV_ora_36283.trc /home/oradev/EBSDEV_ora_36283.txt sys= no explain= apps/apps SORT=exeela执行计划简介7例子：-执行

9、查询SELECT co.object_name ,co.object_name FROM cux_objects co WHERE co.owner = APPSVIEW AND EXISTS (SELECT 1 FROM cux_objects2 co2 WHERE co2.object_type = TRIGGER AND co2.object_name = co.object_name);-获取执行SQL的SQL_ID:SELECT s.sql_id ,s.child_number ,s.sql_text FROM v$sql s WHERE s.sql_text LIKE %CO.ob

10、ject_name%;-根据SQL_ID获取执行计划： SELECT * FROM TABLE(dbms_xplan.display_cursor(7c5pt845ntmgr, 0, advanced);执行计划简介8执行计划头信息：SQL_ID、child number标识一条sql；Plan hash value 解析执行计划放入share pool会记录hash值，下次碰到相同，不执行硬解析；执行计划主体信息：Id 序号，不代表执行顺序，带星号的一般有谓词信息；Operation 当前执行的操作方式；Name 操作对象，表或索引等；Rows 基数，预估当前操作返回的结果集行数，10g前为

11、Cardinality；Bytes 预估执行当前操作返回的字节数；Cost (%CPU) 无单位，当前操作累计成本值，估算成本；Time 预估当前操作累计占用时间；执行顺序：最右最上最先执行，即缩进最多的先执行，缩进相同的，位于上边的先执行；执行计划简介9查询块名称和对象别名信息：SEL$为SELECT缩写，相应的还有DEL$,INS$,UPD$等；可以通过CO2SEL$2作为别名在Hint中使用执行计划概要信息：执行计划主要信息以文本方式展现；可以替换Outline的执行方法，并通过Hint实现；执行计划简介10谓词信息：Access表示谓词条件影响数据的访问路径（索引、全表）Filter

12、谓词条件不会影响访问路径，只进行数据过滤；结构信息：相关列的结构信息；Dynamic sampling动态采样，即该表可能未做过数据分析；Kyes=表示存在一个列参与排序统计数据：recursive calls:存在其他sql调用；db block gets:增、删、改引起的逻辑读；consistent gets:一致性逻辑读；physical reads:物理读；执行计划简介11等待事件信息：10046事件，可以方便的收集过程中的事件信息；谓词介绍 12谓词即SQL语句中的WHERE条件；ACCESS和FILTER：Access表示谓词条件影响数据的访问路径（索引、全表）Filter 谓词条

13、件不会影响访问路径，只进行数据过滤；通过操作序号对应的谓词情况，来分析sql扫描时例子：练习001：谓词Access和Filter测试常用索引扫描13常用索引扫描方式：INDEX UNIQUE SCAN：唯一索引扫描，表存在主键、唯一索引，谓词条件包含唯一索引字段组合；INDEX RANGE SCAN：非唯一索引扫描、唯一索引范围扫描，如=、Between And等；INDEX FULL SCAN：索引全表扫描，按块顺序读取，仅查询索引字段，可使用INDEX_FS指定该索引；INDEX FAST FULL SCAN：索引全表快速扫描，采用多块读，唯一索引表进行COUNT可以使用该方式快速扫描，

14、可使用INDEX_FFS指定该索引；INDEX SKIP SCAN：索引跳跃扫描，索引前导列不在查询条件中时，且前导列取值较少时；数据块扫描方式：TABLE ACCESS BY INDEX ROWID：索引扫描后，通过ROWID直接访问数据记录所在的位置；TABLE ACCESS BY USER ROWID：直接手工指定ROWID进行访问；TABLE ACCESS FULL：不通过索引直接全表扫描，采用多块读；常用连接方式14NESTED LOOPS：迭代循环遍历，小表作为外层驱动表，大表作为内层被驱动表，被驱动表根据驱动表循环遍历；理想条件下，驱动表记录较少，被驱动表连接条件存在索引，唯一索

15、引或者选择性高的非唯一索引；MERGE JOIN：合并排序连接，先对两个表进行排序，根据排序结果两表交替遍历，符合要求的保留，不符合要求的剔除，不适用与、LIKE情况，两个表只访问一次；HASH JOIN：哈希连接，大数据连接常用方式，对小表连接谓词存储散列表，然后扫描大表并探测散列表，找出匹配的行；FILTER：迭代，类似于迭代循环遍历，对外表的每一行都对内表执行一次扫描，但是会维护HASH值，已经存在记录的不再扫描，性能取决于外表关联列值DISTINCT后的结果，最差情况每行都进行一次扫描；FILTER例子：练习002：Filter迭代测试常用连接方式15HINT实现NESTED LOOP

16、S、MERGE JOIN、HASH JOIN：SELECT /*+ use_hash(cch,ccl) */ cch.changed_number,ccl.line_id FROM cux_om_order_change_headers cch ,cux_om_order_change_lines ccl WHERE cch.header_id = ccl.header_id;SELECT /*+ use_merge(cch,ccl) */ cch.changed_number,ccl.line_id FROM cux_om_order_change_headers cch ,cux_om_o

17、rder_change_lines ccl WHERE cch.header_id = ccl.header_id;SELECT /*+ use_nl(cch,ccl) */ cch.changed_number,ccl.line_id FROM cux_om_order_change_headers cch ,cux_om_order_change_lines ccl WHERE cch.header_id = ccl.header_id;常用连接方式16反连接：在使用NOT EXISTS或NOT IN时，会使用到反连接，用ANTI表示，同样可以通过HINT指定反连接方式；/*+ HASH_

18、AJ*/和/*+ MERGE_AJ*/和/*+ NL_AJ*/SELECT cch.changed_number ,cch.status_code FROM cux_om_order_change_headers cch WHERE cch.header_id NOT IN (SELECT /*+ MERGE_AJ*/ ccl.header_id FROM cux_om_order_change_lines ccl);SELECT cch.changed_number ,cch.status_code FROM cux_om_order_change_headers cch WHERE NOT

19、 EXISTS (SELECT /*+ NL_AJ*/ 1 FROM cux_om_order_change_lines ccl WHERE ccl.header_id = cch.header_id);常用连接方式17半连接：在使用EXISTS或IN时，会使用到半连接，用SEMI表示，同样可以通过HINT指定半连接方式；半连接可能会对连接结果集进行必要的去重；/*+ HASH_SJ*/和/*+ MERGE_SJ*/和/*+ NL_SJ*/SELECT cch.changed_number ,cch.status_code FROM cux_om_order_change_headers cc

20、h WHERE EXISTS (SELECT /*+ MERGE_SJ*/ 1 FROM cux_om_order_change_lines ccl WHERE cch.header_id = ccl.header_id);SELECT cch.changed_number ,cch.status_code FROM cux_om_order_change_headers cch WHERE cch.header_id IN (SELECT /*+ MERGE_SJ*/ ccl.header_id FROM cux_om_order_change_lines ccl);常用连接方式18INDE

21、X JOIN：单表上的不同索引之间进行连接，类似于表连接，对象是索引；VIEW：将视图或某段子查询看成一个单独的整体并独立的执行，执行计划中出现VIEW类型；UNION/UNION ALL：UNION ALL仅简单合并，UNION合并后并进行去重，相当于于UNION ALL+SORT UNIQUE：SELECT cch.gcd_order_number FROM cux_om_order_change_headers cch WHERE cch.changed_level = BATCHUNIONSELECT cch.gcd_order_number FROM cux_om_order_cha

22、nge_headers cch WHERE cch.changed_level = ORDER;SORT：出现了SORT不一定进行排序，需要通过列信息的Key值区分：常见的SORT：SORT AGGREGATE、SORT UNIQUE、SORT JOIN、SORT GROUP BY、SORT ORDER BY、BUFFER SORT；SORT AGGREGATE：用于SUM求和时，不一定存在排序；Buffer Sort不一定存在排序：当使用笛卡尔连接时，不一定存在排序，只是将某个表索引扫描的结果存入PGA；SELECT cch.header_id,ccl.changed_type FROM c

23、ux_om_order_change_headers cch ,cux_om_order_change_lines ccl;事件介绍 19事件(EVENT)：事件记录会话每一个环节所在进行的活动，如等待锁，逻辑读、物理读等；通过事件分析会话正在进行的活动来发现可能存在的问题；通过等待事件，了解会话正在做什么：SELECT * FROM v$session_wait sw WHERE sw.sid = userenv(sid);-会话当前等待事件；SELECT * FROM v$session_event se WHERE se.sid = userenv(sid);-会话所有等待事件；sele

24、ct * FROM v$event_name en;-等待事件参数几种常见事件：db file sequential read 顺序读，将存储上可能不连续的数据块读到连续的内存；即访问的数据不在SGA时，会从数据文件读取到SGA，属于单块读；db file scattered read离散读，将存储上连续的数据块离散的读入到多个不连续的内存位置；direct path read通常发生在Oracle直接读数据到进程PGA时，读取不需要经过SGA；direct paht write通常发生在Oracle直接从PGA写数据到数据文件或临时文件，写操作可以绕过SGA；大量直接路径读写可能多数因为磁盘

25、排序导致；如何发现问题20Vmstat命令：用来服务器整体的的CPU使用率，内存使用，虚拟内存交换情况,IO读写情况；Vmstat 2 2 参数表示采样时间间隔和采样次数；r 表示运行队列; b 表示阻塞的进程;swpd 虚拟内存已使用的大小,大于0，表示物理内存不足；Free 空闲的物理内存的大小；Buff 目录，权限的缓存；cache 文件缓存；si和so表示虚拟内存和磁盘交互的读写；bi和bo设备IO读写；us、sy、id分别代表系统、用户、空闲的cpu，一般us+sy+is=100；如何发现问题21Top命令：用来动态显示进程的CPU、内存使用情况；第一行任务队列信息：时间、系统运行天

26、数、当前用户数、系统负载；第二行进程信息：总数、运行中、睡眠、停止、僵尸进程数；第三行CPU信息：用户进程、系统内核、进程调度、空闲、等待等占比；第四行物理内存信息：总数、已使用、空闲、内核缓存占用；第五行虚拟内存信息：总数、已使用、空闲、缓冲区占比；进程信息：进程号、用户、优先级、NICE值（负值优先级高）、虚拟内存占用、物理内存占用、共享内存、进程状态(R运行/S睡眠/T停止/Z僵尸)、CPU占用、物理内存占用、进程CPU时间、命令等信息；通过进程PID=v$process.spid来获取数据库进程和会话；如何发现问题22AWR报告：用来诊断数据库一段时间的性能状态，各类事件统计信息，TO

27、P事件等；服务器上$ORACLE_HOME/rdbms/admin/awrrpt.sql脚本可以生成AWR报表,sqlplus连接Oraclesqlplus apps/apps#备注:执行awrrpt.sql脚本 $ORACLE_HOME/rdbms/admin/awrrpt.sql#备注:输入AWR报表格式,可以选择html或者text格式;Enter value for report_type: html#备注:输出选择要显示快照的天数,选择1,则代表今天零点到现在Enter value for num_days: 5#备注:根据以上展示的SNAP号,输入开始快照Enter value f

28、or begin_snap: 3220#备注:根据以上 展示的SNAP号,输入结束快照Enter value for end_snap: 3230#备注:输出快照路径和文件名,默认命名为awrrpt_1_开始SNAP号_结束SNAP号Enter value for report_name: /home/orasit/awrrpt_1_3220_3230.html#备注:打印完成后断开数据库连接,从指定路径下获取AWR报表文件;SQL exit ;set head off ;set feedback off ;set echo off ;set long 200000;spool D:Mater

29、ialAWRawrrpt_1_3281_3289.html;SELECT output FROM TABLE(dbms_workload_repository.awr_report_html(l_dbid = 3547208355 ,l_inst_num = 1 ,l_bid = 3281 ,l_eid = 3289 ,l_options = NULL);spool off;如何发现问题23AWR报告：如何发现问题24TRACE跟踪：通过在FORM、REQUEST开启跟踪，使用10046事件对会话进行诊断；SESSION执行情况：诊断正在运行SESSION的执行情况：SQL及执行计划和统计信息

30、、等待事件；SELECT r.request_id,r.oracle_session_id,r.oracle_process_id ,s.sid,s.serial#,s.event ,p.spid,p.pid ,q.sql_text,q.sql_id FROM fnd_concurrent_requests r,gv$session s ,gv$process p,gv$sql q WHERE r.oracle_session_id(+) = s.audsid AND p.addr = s.paddr AND s.sql_id = q.sql_id(+);一般优化方法25分析性能瓶颈：分析SQ

31、L语法，是否有明显的性能问题；分析查询表的数据量和目标集；通过执行计划分析每一步的连接方式和成本，是否存在合适的索引；分析统计信息查看逻辑读和物理读情况；通过TRACE分析每一步的成本、时间，分析耗时较长的事件；一般优化方法：排查有明显性能问题的SQL语句；根据业务需求对SQL改写、纯技术上改写、通过HINT指定访问方式；添加必要的索引，选择更优的执行计划；降低表的水位线，碎片整理；调整系统参数，如增加SGA来减少磁盘读等；使用临时表、物化视图、分区表等；优化案例分析26案例分析：自动调优建议27自动调优建议：Oracle提供了一些可以简单调优的工具，如ADDM报告，DBMS_SQLTUNE包等；DECLARE my_task_name VARCHAR2(30); my_sqltext CLOB;BEGIN -创建优化建议，对存在的参数，需要使用变量绑定 my_sqltext := SELECT co1.object_id,co1.object_name,co1.object_type FROM cux_objects1 co1 WHERE co1.owner = :BD1 AND co1.object_name = :BD2 AND EXISTS (SELECT 1 FROM cux_objects2