• 1. Oracle数据库性能优化实务 第二讲:Oracle 等待接口 (两课时)主讲人:白鳝华章培训网、[www.hztraining.com]华章培训网版权所有
  • 2. OWI-ORACLE WAIT INTERFACE第2页从Oracle 7开始支持OWI 目的是让开发人员和维护人员能够了解在Oracle的每个模块和OS中消耗了多少时间 通过Oracle提供的视图获取这些信息 不仅仅供DBA使用,也为Oracle RDBMS开发提供帮助
  • 3. OWI分析的主要作用分析系统性能问题的根源 找到对系统性能影响最大的问题所在 找到TOP SQL 诊断系统故障的原因 分析BUG
  • 4. OWI分析的工具OWI核心视图 AWR/STATSPACK报告 ASH报告 HANGANALYZE ORACLE诊断事件DUMP工具 操作系统DEBUG/CALL STACK分析工具
  • 5. OWI-核心视图第5页v$system_event:总体性视图 v$session_event:按照SESSION划分的总体性视图 v$session_wait:明细信息,每三秒钟刷新一次等待时间
  • 6. OWI-核心视图(续)第6页event:名称 total_waits:自从实例启动以来的等待次数 total_timeouts:事件被唤醒的总次数 time_waited:按照cs统计的总的等待时间 average_wait:平均每次等待的等待时间,单位是cs sid: v$session_event, session id number
  • 7. OWI核心视图-V$SESSION_WAITSID:SESSION ID seq#:等待次数统计 event:事件 p[1-3]:等待的详细参数 p[1-3]raw:参数的raw模式 p[1-3]text:参数的名字
  • 8. OWI核心视图-10G加强V$SESSION中增加blocking_session字段,列出资源的持有者 对EVENT进行分类 SELECT e.wait_class#, e.wait_class,sum(s.total_waits), sum(s.time_waited) FROM v$event_name e, v$system_event s WHERE e.name = s.event GROUP BY e.wait_class#,e.wait_class order by e.wait_class# WAIT_CLASS# WAIT_CLASS SUM(S.TOTAL_WAITS) SUM(S.TIME_WAITED) ----------- ------------------------------ ------------------ ------------------ 0 Other 13685 65802 1 Application 283 18824 2 Configuration 76 2040 4 Concurrency 5589 5781 5 Commit 5641 880 6 Idle 1619241 674624809 7 Network 2932 8 8 User I/O 30691 14395 9 System I/O 423003 65607
  • 9. OWI核心视图-10G增强(2)事件分类 IDLE WAIT APPLICATION:行锁、表锁、DDL锁等 Configuration:由于配置导致的 Administrative:特权用户的某些维护操作导致的 Concurrency:由于并发量大引起的 commit: log file sync Network User I/O waits:前台进程,SMON,MMON System I/O Waits:除了SMON,MMON外的后台进程 Scheduler:资源管理引起的 CLUSTER:RAC Othere
  • 10. OWI核心视图-10G增强(3)LATCH等待的细分 SELECT event,p1,p2,p3 FROM v$session_wait WHERE event like 'latch%'; 等待直方图 v$event_histogram v$file_histogram v$temp_histogram
  • 11. OW核心视图-10g 增强(4)v$event_histogram event#,event wait_time_milli wait_countSQL> exec waithistogram(pfilter=>'db file sequential'); event Wait time Wait count Pct_rt db file sequential read 1 23008 055.31 db file sequential read 2 2081 060.31 db file sequential read 4 2752 066.93 db file sequential read 8 4895 078.70 db file sequential read 16 5813 092.67 db file sequential read 32 2660 099.07 db file sequential read 64 355 099.92 db file sequential read 128 20 099.97 db file sequential read 256 11 100.00 db file sequential read 512 2 100.00
  • 12. OWI核心视图-10G增强(5)SQL> exec filehistogram('DATA01'); FILE Wait time Wait count Pct_rt E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATA01..DBF 1.00 2407 091.91 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATA01..DBF 2.00 21 092.71 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATA01..DBF 4.00 20 093.47 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATA01..DBF 8.00 47 095.27 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATA01..DBF 16.00 74 098.09 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATA01..DBF 32.00 26 099.08 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATA01..DBF 64.00 22 099.92 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATA01..DBF 128.00 2 100.00v$file_histogram file# SINGLEBLKRDTIM_MILLI SINGLEBLKRDS
  • 13. OWI核心视图-10G增强(6)v$temp_histogram file# SINGLEBLKRDTIM_MILLI SINGLEBLKRDSSQL> exec temphistogram; TempFILE Wait time Wait count Pct_rt E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF 1.00 15 083.33 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF 2.00 083.33 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF 4.00 1 088.89 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF 8.00 088.89 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF 16.00 1 094.44 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF 32.00 1 100.00
  • 14. OWI核心视图-10G增强(7)v$system_wait_class wait_class# ,wait_class time_waited total_waits SQL> exec SystemWaitClass wait class time waited total_waits Network 12 10782 Commit 1084 7021 Concurrency 8053 6840 User I/O 22057 47060 Other 80259 30754 System I/O 85259 530205 Configuration 102462 586 Application 378234 1656 Idle 825601168 1981056
  • 15. OWI核心视图-10G增强(8)v$session_wait_class SID,SERIAL# wait_class# ,wait_class time_waited total_waits SQL> exec SessionWaitClass(162); sid wait class time waited total_waits 162 Other 2 5 162 User I/O 76 65 162 Concurrency 7472 6127 162 Idle 40194784 80638
  • 16. OWI核心视图-10G增强(9)v$session增加EVENT相关字段 EVENT#,EVENT P1TEXT,P1,P1RAW P2TEXT,P2,P2RAW P3TEXT,P3,P3RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT
  • 17. OWI核心视图-10G增强(10)新增v$session_wait_history视图 SID,SEQ# EVENT#,EVENT P1TEXT,P1 P2TEXT,P2 P3TEXT,P3 WAIT_TIME WAIT_COUNT SQL> EXEC SessionWaitHistory(pSid=>166); sid event wait_time wait_count p1 p2 p3 166 rdbms ipc message 0 1 219 0 0 166 log file parallel write 0 1 1 3 1 166 rdbms ipc message 1 1 219 0 0 166 log file parallel write 0 1 1 2 1 166 log file parallel write 0 1 1 3 1 166 rdbms ipc message 300 1 300 0 0 166 rdbms ipc message 300 1 300 0 0 166 rdbms ipc message 300 1 300 0 0 166 rdbms ipc message 219 1 219 0 0 166 rdbms ipc message 81 1 300 0 0
  • 18. OWI核心视图-10G增强(10)v$event_name中增加事件分类(wait_class) 字段SQL> SELECT name, wait_class FROM v$event_name 2 WHERE wait_class# =10; NAME WAIT_CLASS ------------------------------------------- -------------- resmgr:cpu quantum Scheduler resmgr:become active Scheduler
  • 19. OWI核心视图-10G增强(11)新增v$sys_time_modelSQL>select * from v$sys_time_model STAT_ID STAT_NAME VALUE ---------- ------------------------------------------------- ---------- 3649082374 DB time 6214000783 2748282437 DB CPU 547301843 4157170894 background elapsed time 1975422970 2451517896 background cpu time 644208639 4127043053 sequence load elapsed time 259929 1431595225 parse time elapsed 201181835 372226525 hard parse elapsed time 167194853 2821698184 sql execute elapsed time 5783274366 1990024365 connection management call elapsed time 1900292 1824284809 failed parse elapsed time 1002981 4125607023 failed parse (out of shared memory) elapsed time 0 3138706091 hard parse (sharing criteria) elapsed time 20236586 268357648 hard parse (bind mismatch) elapsed time 436796 2643905994 PL/SQL execution elapsed time 79430155 290749718 inbound PL/SQL rpc elapsed time 0 1311180441 PL/SQL compilation elapsed time 7655235 751169994 Java execution elapsed time 0 1159091985 repeated bind elapsed time 1599552 2411117902 RMAN cpu time (backup/restore) 480000
  • 20. OWI核心视图-10G增强(12)新增v$sess_time_modelSQL> exec sessiontimeModel(170); sid StateName Value 170 hard parse (bind mismatch) elapsed time 0 170 inbound PL/SQL rpc elapsed time 0 170 hard parse elapsed time 0 170 Java execution elapsed time 0 170 repeated bind elapsed time 0 170 PL/SQL compilation elapsed time 0 170 parse time elapsed 0 170 failed parse elapsed time 0 170 connection management call elapsed time 0 170 RMAN cpu time (backup/restore) 0 170 background cpu time 16151575 170 PL/SQL execution elapsed time 0 170 DB CPU 0 170 sql execute elapsed time 0 170 hard parse (sharing criteria) elapsed time 0 170 DB time 0 170 failed parse (out of shared memory) elapsed time 0 170 sequence load elapsed time 0 170 background elapsed time 16819269
  • 21. OWI核心视图-10G增强(13)METRIC:系统自动统计的数据,大多数指标60秒钟采集一次,历史数据可以从*_history视图中获取 V$SYSMETRIC/v$sysmetric_history V$SESSMETRIC V$FILEMETRIC/V$filemetric_history V$EVENTMETRIC V$WAITCLASSMETRIC /_history V$METRICNAME
  • 22. OWI核心视图-10G增强(14)OSSTAT:操作系统统计数据SQL> select * from v$osstat; STAT_NAME VALUE OSSTAT_ID ---------------------------------------- ---------- ---------- NUM_CPUS 2 0 IDLE_TIME 102286118 1 BUSY_TIME 14421985 2 USER_TIME 2592943 3 SYS_TIME 11829042 4 AVG_IDLE_TIME 51133130 7 AVG_BUSY_TIME 7201053 8 AVG_USER_TIME 1287709 9 AVG_SYS_TIME 9.2234E+18 10 RSRC_MGR_CPU_WAIT_TIME 0 14 PHYSICAL_MEMORY_BYTES 3623137280 1008 VM_IN_BYTES 9138503680 1009 VM_OUT_BYTES 198705152 1010
  • 23. OWI核心视图-10G增强(15)ASH:Active Session History V$ACTIVE_SESSION_HISTORY DBA_HIST_ACTIVE_SESS_HISTORY AWR的一部分 从v$session中每秒一次采样,除去IDLE事件,除去非ACTIVE的SESSION 除了存储在AWR中外,1/10的采样可以在视图中看到
  • 24. 课间休息回顾刚才的内容 了解什么是OWI 了解OWI核心视图 了解OWI在10g 中的增强
  • 25. OWI诊断的方法从系统级开始 v$system_event v$session_wait statspack/awr报告 关注存在较大等待时间和次数的事件 注意过滤掉IDLE事件 IDLE事件一般放在STATSPACK报告的事件的尾部 STATS$IDLE_EVENT表 对关键事件进行细致跟踪 v$session_wait statspack/awr报告 建立基线
  • 26. OWI性能分析路线图等待事件/CPU开销系统及SQL情况PL/SQL情况OWI及性能分析SQL执行计划系统调用性能分析的起点GetSessionInfotopsql.sqlprofilerautotrace,10046tusc,truss,pstackawr,ash,statspackALERT LOG等日志Hanganalyze分析
  • 27. 总体等待情况Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- CPU time 10,272 48.34 db file sequential read 996,599 3,549 16.70 enqueue 1,231 3,511 16.52 latch free 31,772 2,725 12.82 db file scattered read 77,117 569 2.68 Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- db file sequential read 996,599 0 3,549 4 33.9 enqueue 1,231 1,199 3,511 2852 0.0 latch free 31,772 7,192 2,725 86 1.1 db file scattered read 77,117 0 569 7 2.6
  • 28. 明细分析select sid,event,wait_time,state ,p1,p2,p3 from v$session_wait where event ='file scattered read'; SID EVENT WAIT_TIME STATE P1 P2 P3 ---- -------------------------- ---------- ------------ ------ -------- ------- 8 db file scattered read -1 WAITED KNOWN 4 4801 8 select sid,event,wait_time,state ,p1,p2,p3 from v$session_wait where sid=8; SID EVENT WAIT_TIME STATE P1 P2 P3 ---- -------------------------- ---------- ------------ ------ -------- ------- 8 db file scattered read -1 WAITED KNOWN 4 4777 8
  • 29. 案例分析故障现象:查询select count(*) from org;发现几乎HANG住 分析思路:局部性故障,使用等待事件分析和HANGANALYZE结合的方法 分析方法:查看V$SESSION_WAIT,做HANGANALYZE分析 *** SESSION ID:(16.4057) 2008-04-04 15:25:43.327 *** 2008-04-04 15:25:43.327 ============== HANG ANALYSIS: ============== Found 59 objects waiting for <66/11/0x5598b3e0/4728/No Wait> Open chains found: Chain 1 : : <16/4057/0x559b23d0/8520/No Wait> Chain 2 : : <66/11/0x5598b3e0/4728/No Wait> -- <19/2751/0x55975850/8051/latch free> 查明原因:DB CACHE的内部死锁
  • 30. 局部变慢或者HANG住的分析方法查看ALERT LOG中是否有报错 查看相关会话信息 通过V$SESSION_WAIT查看等待事件 做ASH报告 检查长时间执行的SQL 通过HANGANALYZE分析查看是否有HANG住现象 查看操作系统资源
  • 31. 获取会话的信息SESSION基本信息 Session统计信息 Session Event信息 GetSessionInfo工具 GetSessionInfo(pSid number,pState boolean default false,pWait boolean default false) GetMySessionInfo(pState boolean default false,pWait boolean default false)
  • 32. GetMySessionInfoSQL> exec GetMySessionInfo; Sid, Serial#, Aud sid : 17 , 798 , 0 DB User / OS User : SYS / jackson?xu Machine - Terminal : WORKGROUP\JACKSONXU - JACKSONXU OS Process Ids : 2832:2928 (Client) 11741 (Server) Client Program Name : sqlplus.exeSQL> exec GetMySessionInfo(pwait=>true); Sid, Serial#, Aud sid : 17 , 798 , 0 DB User / OS User : SYS / jackson?xu Machine - Terminal : WORKGROUP\JACKSONXU - JACKSONXU OS Process Ids : 2832:2928 (Client) 11741 (Server) Client Program Name : sqlplus.exe Wait event ..... event p1 p2 p3 waitTime state SQL*Net message from client 1413697536 00000001 00000000 00008730 WAITED KNO
  • 33. ASH分析10G新特性 ASH分析在现场系统分析中十分重要 可以分析最近几个小时内的系统情况 集成在EM DB CONSOLE中 @?/rdbms/admin/ashrpt.sql
  • 34. ASH分析路线图总体分析对象file#,block#Object NameSQL等待事件SESSION信息执行计划SessionSession信息SESSION信息执行计划
  • 35. 使用AWR报告分析性能问题可以清晰的抓住系统关键问题 具有大量的细节信息 部分内容对DBA要求很高 可以交给专业人员协助分析
  • 36. AWR报告使用方法手工生成报告: awrrpt.sql/awrrpti.sql awrddrpt.sql/awrddrpi.sql:比较报告 awrsqrpt.sql/awrsqrpi.sql:SQL报告 awrextr.sql/awrload.sql:导出/导入AWR数据 awrinfo.sql:AWR的一些基本信息 使用EM DBCONSOLE
  • 37. AWR中的全局性分析Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.75 Redo NoWait %: 100.00 Buffer Hit %: 96.53 In-memory Sort %: 100.00 Library Hit %: 97.59 Soft Parse %: 94.71 Execute to Parse %: 37.18 Latch Hit %: 98.76 Parse CPU to Parse Elapsd %: 20.77 % Non-Parse CPU: 96.92 Shared Pool Statistics Begin End Memory Usage %: 93.49 93.17 % SQL with executions>1: 22.53 18.94 % Memory for SQL w/exec>1: 18.35 15.64 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- CPU time 25,218 43.33 db file sequential read 10,000,912 12,633 21.71 SQL*Net message from dblink 2,027,803 3,166 5.44 global cache cr request 19,272,909 3,136 5.39 db file scattered read 1,358,017 2,496 4.29
  • 38. 使用ADDM分析系统性能问题优点 直接看到结论 不需要很专业的技术能力 适合解决主要问题 缺点 缺乏细节 分析的深度不够 对于次要矛盾的把握能力较弱
  • 39. 什么是ADDM应用和SQL管理存储管理备份恢复管理系统资源管理空间管理ADDM
  • 40. ADDM工作原理SnapshotsADDMADDM resultsMMONIn-memory statisticsAWRSGA60 minutesADDM 结果EM
  • 41. ADDM可以发现的主要问题因公数据库配置方面的问题ParsingI/O i问题Top SQLPL/SQL, Java timeCheckpointing导致的锁和ITL等待(9i未区分)RAC服务方面的问题导致热块的SQL内存设置偏小过度频繁的 login 、logoffstatspack 无法诊断ADDM
  • 42. 在EM中使用ADDM
  • 43. 生成ADDM报告SQL>@?/rdbms/admin/addmrpt 输入分析的BEGIN SNAP,END SNAP
  • 44. AWR结合ADDM使用AWR分析细节 使用ADDM分析主要问题 通过ADDM验证AWR分析结果
  • 45. OWI分析的例子(1) Avg Total Wait wait %of waits Event Waits Timeouts Time (s) (ms) ------------------------- ------------ ---------- ---------- ------ -------- db file scattered read 36,858,021 0 18,429 0.5 85.7 db file sequential read 12,420,031 0 2,484 0.2 9.9IO好像有问题
  • 46. 进一步分析DB CacheDB File Scatter Read操作系统 IO子系统 读取时间均小于1毫秒物理存储从平均每次等待时间来看,IO子系统读取时间均小于1毫秒。说明物理存储和IO子系统并不存在性能问题。
  • 47. 下节预告:操作系统诊断操作系统诊断是优化中诊断的第一步 如何诊断操作系统存在的问题 如何通过操作系统参数调整解决性能问题 虚拟内存管理策略的讨论 配置异步IO优化IO性能
  • 48. 感谢您对华章培训网的支持!http://www. hztraining.com