• 1. Oracle数据库性能优化实务 第16讲:解读AWR报告主讲人:白鳝华章培训网、[www.hztraining.com]华章培训网版权所有
  • 2. 本章的目的掌握如何解读AWR报告 掌握在AWR报告中的一些主要小节的作用 掌握AWR报告中的一些主要等待事件的含义 掌握AWR报告中的一些主要统计数据的含义 掌握如何在AWR报告中分析TOP SQL 掌握如何使用AWR报告中的建议
  • 3. 解读AWR报告的作用AWR报告是Oracle 数据库性能和运行状态的最全面的展示 AWR报告对于数据库整体性能分析十分关键 学会解读AWR报告是一个高级DBA的必备功课
  • 4. 解读AWR报告所需的基础知识Oracle Wait Interface 时间模型 Oracle Concepts 中相关的基础知识
  • 5. 解读AWR报告的技巧孰能生巧 基于基线比对 类似系统比对
  • 6. AWR报告头WORKLOAD REPOSITORY report for DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ XXOSS 1544217329 xxoss2 2 10.2.0.4.0 YES rac2 Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 13257 16-Nov-09 19:00:08 297 5.9 End Snap: 13258 16-Nov-09 20:00:00 296 6.0 Elapsed: 59.88 (mins) DB Time: 698.54 (mins)关注点: SESSIONS:采样时点的会话数 CURS/SESS:采样时点的每个会话打开的CURSOR的数量 DB TIME:数据库用于用户会话调用的时间,可反映系统负载情况
  • 7. CACHE SIZECache Sizes ~~~~~~~~~ Begin End ---------- ---------- Buffer Cache: 24,432M 24,432M Std Block Size: 8K Shared Pool Size: 4,096M 4,096M Log Buffer: 14,268K
  • 8. 分析SGA RESIZEalter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; col component format a40 truncate; select component,oper_type,oper_mode,start_time,end_time,trunc(target_size/1024/1024) target from v$sga_resize_ops;
  • 9. 例子COMPONENT OPER_TYPE OPER_MODE START_TIME END_TIME ----------------------- ------------- --------- ------------------- ------------------- DEFAULT buffer cache GROW DEFERRED 2009-08-25 11:52:19 2009-08-25 11:52:19 streams pool SHRINK DEFERRED 2009-08-25 11:52:19 2009-08-25 11:52:19 DEFAULT buffer cache SHRINK IMMEDIATE 2009-08-25 11:53:41 2009-08-25 11:53:41 streams pool GROW IMMEDIATE 2009-08-25 11:53:41 2009-08-25 11:53:41 DEFAULT buffer cache SHRINK IMMEDIATE 2009-08-25 11:53:42 2009-08-25 11:53:42 streams pool GROW IMMEDIATE 2009-08-25 11:53:42 2009-08-25 11:53:42 streams pool GROW IMMEDIATE 2009-08-25 11:53:42 2009-08-25 11:53:42 DEFAULT buffer cache SHRINK IMMEDIATE 2009-08-25 11:53:42 2009-08-25 11:53:42 streams pool GROW IMMEDIATE 2009-08-25 11:54:16 2009-08-25 11:54:16 DEFAULT buffer cache SHRINK IMMEDIATE 2009-08-25 11:54:16 2009-08-25 11:54:16 DEFAULT buffer cache GROW DEFERRED 2009-08-25 11:57:49 2009-08-25 11:58:04 streams pool SHRINK DEFERRED 2009-08-25 11:57:49 2009-08-25 11:58:04 DEFAULT buffer cache GROW DEFERRED 2009-08-25 12:00:17 2009-08-25 12:00:17 streams pool SHRINK DEFERRED 2009-08-25 12:00:17 2009-08-25 12:00:17 streams pool SHRINK DEFERRED 2009-08-25 12:03:17 2009-08-25 12:03:17 DEFAULT buffer cache GROW DEFERRED 2009-08-25 12:03:17 2009-08-25 12:03:17 streams pool SHRINK DEFERRED 2009-08-25 12:07:21 2009-08-25 12:07:21 DEFAULT buffer cache GROW DEFERRED 2009-08-25 12:07:21 2009-08-25 12:07:21
  • 10. Load Profile~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 4,055,059.42 33,250.38 Logical reads: 143,003.19 1,172.59 Block changes: 23,643.28 193.87 Physical reads: 441.32 3.62 Physical writes: 749.27 6.14 User calls: 1,957.58 16.05 Parses: 602.77 4.94 Hard parses: 0.03 0.00 Sorts: 259.33 2.13 Logons: 0.22 0.00 Executes: 2,517.38 20.64 Transactions: 121.96 % Blocks changed per Read: 16.53 Recursive Call %: 54.68 Rollback per transaction %: 0.85 Rows per Sort: 409.87
  • 11. Instance Efficiency Percentages (Target 100%)Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.87 Redo NoWait %: 99.98 Buffer Hit %: 99.71 In-memory Sort %: 99.99 Library Hit %: 100.02 Soft Parse %: 100.00 Execute to Parse %: 76.06 Latch Hit %: 98.98 Parse CPU to Parse Elapsd %: 2.92 % Non-Parse CPU: 99.97
  • 12. Shared Pool Statistics Shared Pool Statistics Begin End ------ ------ Memory Usage %: 51.07 51.16 % SQL with executions>1: 97.06 96.56 % Memory for SQL w/exec>1: 95.47 95.04
  • 13. 感谢您对华章培训网的支持!http://www. hztraining.com
  • 14. Top 5 Timed EventsTop 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- db file sequential read 1,507,667 15,697 10 37.5 User I/O CPU time 12,203 29.1 log file sync 508,580 6,230 12 14.9 Commit Streams capture: waiting for s 1,308 1,996 1526 4.8 Configurat log file parallel write 523,167 1,029 2 2.5 System I/O -------------------------------------------------------------
  • 15. Global Cache Load ProfileGlobal Cache Load Profile ~~~~~~~~~~~~~~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Global Cache blocks received: 274.27 2.25 Global Cache blocks served: 144.48 1.18 GCS/GES messages received: 1,175.37 9.64 GCS/GES messages sent: 1,128.87 9.26 DBWR Fusion writes: 12.70 0.10 Estd Interconnect traffic (KB) 3,800.04
  • 16. Global Cache Efficiency PercentagesGlobal Cache Efficiency Percentages (Target local+remote 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer access - local cache %: 99.51 Buffer access - remote cache %: 0.19 Buffer access - disk %: 0.29
  • 17. Global Cache and Enqueue Services - Workload CharacteristicsGlobal Cache and Enqueue Services - Workload Characteristics ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Avg global enqueue get time (ms): 0.7 Avg global cache cr block receive time (ms): 0.7 Avg global cache current block receive time (ms): 0.7 Avg global cache cr block build time (ms): 0.0 Avg global cache cr block send time (ms): 0.0 Global cache log flushes for cr blocks served %: 39.5 Avg global cache cr block flush time (ms): 4.1 Avg global cache current block pin time (ms): 0.8 Avg global cache current block send time (ms): 0.0 Global cache log flushes for current blocks served %: 1.5 Avg global cache current block flush time (ms): 22.5
  • 18. 重要指标解释Ave global cache get time (ms):一个GLOBAL CACHE的获取时间,一般来说应该小于5毫秒,如果这个指标超过20毫秒,对系统的性能影响将十分大。 Ave global cache convert time (ms):这个指标是对一个DATA BUFFER的访问权限转换所需要的平均时间,这个指标一般来说应该在4毫秒左右,超过20毫秒说明RAC的性能可能存在问题。 Ave time to process CR block request (ms):这个指标统计一个CR BLOCK请求的时间,包含生成CR BLOCK、刷新CR BLOCK和发送CR BLOCK的时间,在Oracle 10G的AWR报告里,这个指标被分解为三个部分(Avg global cache cr block build time、 Avg global cache cr block flush time、Avg global cache cr block send time),正常范围是在0.1毫秒到1毫秒,超过10毫秒,对系统的性能有较大的影响。 Ave receive time for CR block (ms):一个CR BLOCK从发起请求到收到的时间,一般来说这个指标在0.3毫秒-4毫秒之间,这个指标超过12毫秒,我们就需要关注 Ave time to process current block request (ms): 处理一个CURRENT状态的BLOCK请求的处理时间。一般情况下指标在3毫秒左右,大于20毫秒的时候就需要关注了。 Ave receive time for current block (ms):一个CURRENT的BLOCK从发起请求到收到的时间。这个指标一般情况下在8毫秒左右,负载较轻的系统可能会小于1毫秒,负载较重的系统,这个指标可能偏高,不过一般情况下,这个指标应该小于30毫秒。
  • 19. Global Cache and Enqueue Services - Messaging StatisticsGlobal Cache and Enqueue Services - Messaging Statistics ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Avg message sent queue time (ms): 0.0 Avg message sent queue time on ksxp (ms): 0.5 Avg message received queue time (ms): 0.0 Avg GCS message process time (ms): 0.0 Avg GES message process time (ms): 0.0 % of direct sent messages: 49.59 % of indirect sent messages: 31.96 % of flow controlled messages: 18.45
  • 20. 重要指标解释Avg message sent queue time (ms):一个非直接传输的逻辑消息在队列中的平均等待时间,正常情况下,这个值应该在0.1毫秒左右,并且不会超过1毫秒 Avg message sent queue time on ksxp (ms):在IPC层面上发送一个消息到收到ACK回应的时间差,这个指标可以看出IPC的性能。正常情况下这个指标也应该小于1毫秒 Avg message received queue time (ms):消息收到进入队列到开始处理的时间,一般来说这个时间越长说明等待处理的消息越多,一般来说这个值应该在0.1毫秒左右,甚至更低 Avg GCS message process time (ms):平均处理一个BUFFER相关的GCS请求的时间,一般这个指标小于0.5毫秒。 Ave GES message process time (ms):平均处理一个锁操作消息的时间,一般来说这个指标小于0.5毫秒。
  • 21. Time Model StatisticsStatistic Name Time (s) % of DB Time ------------------------------------------ ------------------ ------------ sql execute elapsed time 31,563.4 75.3 DB CPU 12,203.1 29.1 PL/SQL execution elapsed time 2,816.9 6.7 parse time elapsed 68.5 .2 connection management call elapsed time 12.5 .0 hard parse elapsed time 2.6 .0 repeated bind elapsed time 1.1 .0 sequence load elapsed time 0.4 .0 hard parse (sharing criteria) elapsed time 0.2 .0 failed parse elapsed time 0.0 .0 PL/SQL compilation elapsed time 0.0 .0 DB time 41,912.2 N/A background elapsed time 4,886.0 N/A background cpu time 1,428.3 N/A -------------------------------------------------------------
  • 22. Wait Class Avg %Time Total Wait wait Waits Wait Class Waits -outs Time (s) (ms) /txn -------------------- ---------------- ------ ---------------- ------- --------- User I/O 1,586,311 .0 16,229 10 3.6 Commit 508,580 .0 6,230 12 1.2 System I/O 1,305,402 .0 2,228 2 3.0 Configuration 4,594 20.4 2,095 456 0.0 Cluster 1,891,580 .0 1,815 1 4.3 Network 6,344,952 .0 1,495 0 14.5 Other 1,183,921 50.9 1,422 1 2.7 Concurrency 200,260 .1 269 1 0.5 Application 13,858 3.1 224 16 0.0 -------------------------------------------------------------
  • 23. Wait Event Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn ---------------------------- -------------- ------ ----------- ------- --------- db file sequential read 1,507,667 .0 15,697 10 3.4 log file sync 508,580 .0 6,230 12 1.2 Streams capture: waiting for 1,308 70.6 1,996 1526 0.0 log file parallel write 523,167 .0 1,029 2 1.2 gc buffer busy 508,039 .0 858 2 1.2 db file parallel write 589,632 .0 787 1 1.3 enq: SR - contention 17,440 7.5 666 38 0.0 ARCH wait on SENDREQ 29,696 .0 525 18 0.1 read by other session 56,607 .0 515 9 0.1 SQL*Net more data to dblink 947,828 .0 503 1 2.2 gcs log flush sync 231,547 30.6 473 2 0.5 SQL*Net more data from clien 224,985 .0 459 2 0.5 Log archive I/O 33,555 .0 247 7 0.1
  • 24. Background Wait Events Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn ---------------------------- -------------- ------ ----------- ------- --------- log file parallel write 523,215 .0 1,029 2 1.2 db file parallel write 589,632 .0 787 1 1.3 events in waitclass Other 1,090,687 54.3 551 1 2.5 ARCH wait on SENDREQ 29,696 .0 525 18 0.1 Log archive I/O 33,555 .0 247 7 0.1 db file sequential read 55,862 .0 111 2 0.1 log file sequential read 16,081 .0 91 6 0.0 control file sequential read 52,528 .0 18 0 0.1 control file parallel write 6,487 .0 10 2 0.0 log file switch completion 184 .0 7 39 0.0 gc cr multi block request 929 .0 6 6 0.0
  • 25. Operating System Statistics Statistic Total -------------------------------- -------------------- NUM_LCPUS 0 NUM_VCPUS 0 AVG_BUSY_TIME 58,026 AVG_IDLE_TIME 301,217 AVG_IOWAIT_TIME 47,980 AVG_SYS_TIME 8,480 AVG_USER_TIME 49,421 BUSY_TIME 1,395,195 IDLE_TIME 7,232,055 IOWAIT_TIME 1,154,217 SYS_TIME 206,241 USER_TIME 1,188,954 LOAD 0 OS_CPU_WAIT_TIME 1,383,500 RSRC_MGR_CPU_WAIT_TIME 0 PHYSICAL_MEMORY_BYTES 75,161,927,680 NUM_CPUS 24 NUM_CPU_CORES 12
  • 26. Service Statistics Physical Logical Service Name DB Time (s) DB CPU (s) Reads Reads -------------------------------- ------------ ------------ ---------- ---------- xxoss 24,556.7 4,034.8 1,192,835 ########## SYS$USERS 17,375.3 8,156.9 311,706 ########## STRMADMIN.HD_APPLY_QU 0.0 0.0 0 0 STRMADMIN.HQ_CAPTURE_QU 0.0 0.0 0 0 STRMADMIN.HZ_APPLY_QU 0.0 0.0 0 0 SYS$BACKGROUND 0.0 0.0 80,379 ########## sfossXDB 0.0 0.0 0 0
  • 27. Service Wait Class Stats Service Name ---------------------------------------------------------------- User I/O User I/O Concurcy Concurcy Admin Admin Network Network Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time --------- --------- --------- --------- --------- --------- --------- --------- xxoss 1199125 1324820 181621 9019 0 0 5340871 46510 SYS$USERS 305061 283435 12506 17079 0 0 973311 50491 SYS$BACKGROUND 82124 14528 6133 775 0 0 29706 52540
  • 28. SQL ordered by Elapsed Time Elapsed CPU Elap per % Total Time (s) Time (s) Executions Exec (s) DB Time SQL Id ---------- ---------- ------------ ---------- ------- ------------- 5,582 882 210,618 0.0 13.3 g82tjz7g9cjcj Module: JDBC Thin Client insert into AO1_RECORD (A01_TM, NOOBILL_NO, A_CODE, OP_CODE, O P_INFO, B_CODE, A_OPR_CODE, OBJ_TYPE_CODE, C_CODE, Y_FLG, STAY_CODE, PIECE_QTY, A_UPLOAD_TYPE_CODE, QTY,AMT, ACC _CODE, O_INFO, O_NAME, ZONE_CODE, E_STRING, B_SN, SCH
  • 29. Instance Activity StatsStatistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- CPU used by this session 1,030,040 286.7 2.4 CPU used when call started 707,330 196.9 1.6 CR blocks created 213,786 59.5 0.5 Cached Commit SCN referenced 6,562 1.8 0.0 Commit SCN cached 1 0.0 0.0 DB time 12,024,042 3,346.9 27.4 DBWR checkpoint buffers written 2,569,456 715.2 5.9 DBWR checkpoints 148 0.0 0.0 DBWR fusion writes 45,614 12.7 0.1 DBWR object drop buffers written 0 0.0 0.0 DBWR revisited being-written buf 8 0.0 0.0 DBWR thread checkpoint buffers w 1,802,168 501.6 4.1
  • 30. 感谢您对华章培训网的支持!http://www. hztraining.com
  • 31. Instance Activity Stats - Absolute ValuesStatistic Begin Value End Value -------------------------------- --------------- --------------- session cursor cache count 2,671,257 2,677,927 opened cursors current 1,748 1,774 workarea memory allocated 87,511,556 87,513,485 logons current 297 296
  • 32. Instance Activity Stats - Thread ActivityStatistic Total per Hour -------------------------------- ------------------ --------- log switches (derived) 148 148.31
  • 33. Tablespace IO StatsTablespace ------------------------------ Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ A01_RECORD_I01 247,141 69 13.5 1.0 469,818 131 34,207 1.9 A01_NOOBILL_I01 235,772 66 10.6 1.0 480,994 134 15,979 1.8 A01_RECORD_D01 332,709 93 10.3 1.0 6,142 2 12,137 8.2 STREAMS_TBS 71,790 20 2.0 1.0 35,517 10 259 0.1
  • 34. File IO Stats Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ A01_RECORD_D01 /dev/rv_XXXd1_1_10g 20,980 6 11.8 1.0 434 0 711 8.8 A01_RECORD_D01 /dev/rv_XXXd1_2_10g 20,911 6 11.5 1.0 697 0 736 8.6 A01_RECORD_D01 /dev/rv_XXXd1_3_10g 20,977 6 11.8 1.0 693 0 758 9.4 A01_RECORD_D01 /dev/rv_XXXd1_4_10g 20,995 6 11.5 1.0 474 0 761 11.5 A01_RECORD_D01 /dev/rv_XXXd1_5_10g 20,943 6 11.7 1.0 414 0 683 10.3 A01_RECORD_D01 /dev/rv_XXXd1_6_10g 20,537 6 12.0 1.0 357 0 679 12.8 A01_RECORD_D01 /dev/rv_XXXd1_7_10g
  • 35. Buffer Pool Statistics Free Writ Buffer Number of Pool Buffer Physical Physical Buff Comp Busy P Buffers Hit% Gets Reads Writes Wait Wait Waits --- ---------- ---- -------------- ------------ ----------- ---- ---- ---------- D 2,930,313 100 513,126,610 1,509,704 2,618,337 0 0 679,515
  • 36. Instance Recovery Stats Targt Estd Log File Log Ckpt Log Ckpt MTTR MTTR Recovery Actual Target Size Timeout Interval (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks - ----- ----- ---------- --------- --------- ---------- --------- ------------ B 0 73 154681 1365065 2027520 2027520 2757386 N/A E 0 96 170933 1845384 2027520 2027520 2797272 N/A
  • 37. Buffer Pool Advisory Est Phys Size for Size Buffers for Read Estimated P Est (M) Factor Estimate Factor Physical Reads --- -------- ------ ---------------- ------ ------------------ D 2,432 .1 291,688 4.4 1,884,522,143 D 4,864 .2 583,376 2.9 1,258,101,527 D 7,296 .3 875,064 2.2 968,644,064 D 9,728 .4 1,166,752 1.9 808,378,549 D 12,160 .5 1,458,440 1.6 703,726,860 D 14,592 .6 1,750,128 1.5 625,478,384 D 17,024 .7 2,041,816 1.3 563,387,061 D 19,456 .8 2,333,504 1.2 511,992,776 D 21,888 .9 2,625,192 1.1 468,874,071 D 24,320 1.0 2,916,880 1.0 432,162,668 D 24,432 1.0 2,930,313 1.0 430,595,785 D 26,752 1.1 3,208,568 0.9 400,871,822 D 29,184 1.2 3,500,256 0.9 373,393,743 D 31,616 1.3 3,791,944 0.8 349,076,484 D 34,048 1.4 4,083,632 0.8 327,693,238 D 36,480 1.5 4,375,320 0.7 309,308,005
  • 38. 图形化展示
  • 39. PGA Aggr Summary PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ------------------ -------------------------- 98.7 123,633 1,566
  • 40. 重要的警告 Warning: pga_aggregate_target was set too low for current workload, as this value was exceeded during this interval. Use the PGA Advisory view to help identify a different value for pga_aggregate_target.
  • 41. PGA Aggr Target Stats %PGA %Auto %Man PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K) - ---------- ---------- ---------- ---------- ------ ------ ------ ---------- B 10,240 640 17,078.9 1.6 .0 100.0 .0 1,048,570 E 10,240 640 17,371.4 3.0 .0 100.0 .0 1,048,570AUTO PGA TARGET:自动估算的PGA大小,一般小于PGA参数 PGA Mem Alloc:实际分配的PGA空间的最大值 W/A PGA Used :当前手工分配和自动分配PGA的大小
  • 42. PGA Aggr Target Histogram Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- -------------- ------------ ------------ 2K 4K 1,093,488 1,093,488 0 0 64K 128K 5,970 5,970 0 0 128K 256K 1,904 1,904 0 0 256K 512K 4,280 4,276 4 0 512K 1024K 144,239 144,127 112 0 1M 2M 1,253 1,251 2 0 2M 4M 557 485 72 0 4M 8M 520 477 43 0 8M 16M 450 440 10 0 16M 32M 130 130 0 0 32M 64M 38 20 18 0 64M 128M 6 6 0 0
  • 43. PGA Memory Advisory Estd Extra Estd PGA Estd PGA PGA Target Size W/A MB W/A MB Read/ Cache Overalloc Est (MB) Factr Processed Written to Disk Hit % Count ---------- ------- ---------------- ---------------- -------- ---------- 1,280 0.1 43,285,852.4 1,443,733.3 97.0 98,662 2,560 0.3 43,285,852.4 1,258,915.1 97.0 90,179 5,120 0.5 43,285,852.4 1,014,195.2 98.0 73,208 7,680 0.8 43,285,852.4 796,843.8 98.0 57,494 10,240 1.0 43,285,852.4 138,262.0 100.0 44,628 12,288 1.2 43,285,852.4 6,783.8 100.0 31,891 14,336 1.4 43,285,852.4 4,854.6 100.0 18,269 16,384 1.6 43,285,852.4 928.5 100.0 1,286 18,432 1.8 43,285,852.4 0.0 100.0 0 20,480 2.0 43,285,852.4 0.0 100.0 0 30,720 3.0 43,285,852.4 0.0 100.0 0 40,960 4.0 43,285,852.4 0.0 100.0 0 61,440 6.0 43,285,852.4 0.0 100.0 0 81,920 8.0 43,285,852.4 0.0 100.0 0
  • 44. 感谢您对华章培训网的支持!http://www. hztraining.com
  • 45. Shared Pool Advisory Est LC Est LC Est LC Est LC Shared SP Est LC Time Time Load Load Est LC Pool Size Size Est LC Saved Saved Time Time Mem Size(M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 1,616 .4 454 23,673 ####### 1.0 28,611 1.1 ########### 2,032 .5 621 32,217 ####### 1.0 26,129 1.0 ########### 2,448 .6 690 35,989 ####### 1.0 26,126 1.0 ########### 2,864 .7 719 37,590 ####### 1.0 26,125 1.0 ########### 3,280 .8 719 37,590 ####### 1.0 26,125 1.0 ########### 3,696 .9 719 37,590 ####### 1.0 26,125 1.0 ########### 4,112 1.0 719 37,590 ####### 1.0 26,125 1.0 ########### 4,528 1.1 719 37,590 ####### 1.0 26,125 1.0 ########### 4,944 1.2 719 37,590 ####### 1.0 26,125 1.0 ########### 5,360 1.3 719 37,590 ####### 1.0 26,125 1.0 ########### 5,776 1.4 719 37,590 ####### 1.0 26,125 1.0 ########### 6,192 1.5 719 37,590 ####### 1.0 26,125 1.0 ########### 6,608 1.6 719 37,590 ####### 1.0 26,125 1.0 ########### 7,024 1.7 719 37,590 ####### 1.0 26,125 1.0 ########### 7,440 1.8 719 37,590 ####### 1.0 26,125 1.0 ########### 7,856 1.9 719 37,590 ####### 1.0 26,125 1.0 ########### 8,272 2.0 719 37,590 ####### 1.0 26,125 1.0 ###########
  • 46. SGA Target Advisory SGA Target SGA Size Est DB Est Physical Size (M) Factor Time (s) Reads ---------- ---------- ------------ ---------------- 10,240 0.3 20,531,735 1,751,654,422 20,480 0.5 13,249,694 784,349,867 30,720 0.8 11,542,685 558,078,537 40,960 1.0 10,582,822 430,582,931 51,200 1.3 9,909,755 341,236,973 61,440 1.5 9,909,758 341,236,973 71,680 1.8 9,909,760 341,236,973 81,920 2.0 9,909,762 341,236,973
  • 47. Streams Pool Advisory Size for Size Est Spill Est Spill Est Unspill Est Unspill Est (MB) Factor Count Time (s) Count Time (s) ---------- --------- ----------- ----------- ----------- ----------- 8,592 0.7 379,134 234 52,560,918 19,676 9,824 0.8 377,917 234 52,560,913 19,676 11,056 0.9 377,917 234 52,560,913 19,676 12,288 1.0 377,915 234 52,560,912 19,676 13,520 1.1 377,915 234 52,560,912 19,676 14,752 1.2 375,402 233 52,560,912 19,676 15,984 1.3 375,403 233 52,560,912 19,676 17,216 1.4 375,401 233 52,560,912 19,676 18,448 1.5 375,399 233 52,560,911 19,676 19,680 1.6 375,400 233 52,560,911 19,676 20,912 1.7 375,402 233 52,560,911 19,676 22,144 1.8 375,402 233 52,560,911 19,676 23,376 1.9 375,404 233 52,560,911 19,676 24,608 2.0 375,406 233 52,560,911 19,676 25,840 2.1 375,407 233 52,560,911 19,676
  • 48. Buffer Wait Statistics Class Waits Total Wait Time (s) Avg Time (ms) ------------------ ----------- ------------------- -------------- data block 674,864 1,279 2 1st level bmb 2,187 4 2 undo block 744 3 4 undo header 1,093 3 3 2nd level bmb 121 0 1 segment header 85 0 2
  • 49. Enqueue Activity Enqueue Type (Request Reason) ------------------------------------------------------------------------------ Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------- SR-Synchronized Replication 15,961 15,963 0 13,701 683 49.86 TX-Transaction (row lock contention) 127 127 0 127 158 1,247.67 TX-Transaction (index contention) 21,431 21,356 0 11,015 108 9.84 TM-DML 1,362,608 1,362,566 0 50 67 1,342.06 FB-Format Block 7,469 7,469 0 3,711 13 3.46 TT-Tablespace 34,244 34,244 0 20,819 13 .61 CF-Controlfile Transaction 35,792 35,719 73 1,651 10 6.19
  • 50. 常见锁类型CF – 控制文件(Control File) CI - Cross Instance DR - 分布式事务恢复(Distributed recovery ) HW – 高水位(High Watermark) PR - 进程启动(Process Startup) PS - 并行SLAVER进程同步( Parallel Slave Synchronization) SQ - 序列号(Sequence) ST - 空间管理事务(Space Transaction) TM - DML TS – 临时段管理(Temporary Segment) TT - 临时表(Temporary table) TX – 事务 UL – 用户定义锁(DBMS_LOCK) US – 回滚段(Undo Segment)
  • 51. Undo Segment Summary Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/ TS# Blocks (K) Transactions Len (s) Concurcy TR (mins) OOS eS/eR/eU ---- ---------- --------------- -------- -------- --------- ----- -------------- 5 1,132.5 652,633 383 49 120/120 0/0 0/0/0/0/0/0 ------------------------------------------------------------- UNDO 表空间号为5 UNDO表空间约大小为9000M 采样周期内总共有652663个事务 最大查询长度为383秒 最大并行事务数49个 没有ORA-15555,没有出现表空间不足
  • 52. 相关指标含义uS - unexpired Stolen uR - unexpired Released uU - unexpired reUsed eS - expired Stolen eR - expired Released eU - expired reUsed
  • 53. Undo Segment Stats Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/ End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU ------------ ----------- ------------ ------- ------- ------- ----- ------------ 16-Nov 20:06 134,621 67,639 90 41 120 0/0 0/0/0/0/0/0 16-Nov 19:56 123,591 67,881 77 35 120 0/0 0/0/0/0/0/0 16-Nov 19:46 223,815 101,757 111 49 120 0/0 0/0/0/0/0/0 16-Nov 19:36 210,711 117,319 383 47 120 0/0 0/0/0/0/0/0 16-Nov 19:26 188,301 110,568 67 44 120 0/0 0/0/0/0/0/0 16-Nov 19:16 158,269 110,266 87 44 120 0/0 0/0/0/0/0/0 16-Nov 19:06 93,215 77,203 141 40 120 0/0 0/0/0/0/0/0
  • 54. Latch Activity Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ cache buffer handles 1,984,758 0.0 0.0 0 0 N/A cache buffers chains 1,146,663,981 1.2 0.0 2 7,892,807 0.0 cache buffers lru chain 9,956,531 0.5 0.0 0 8,927,384 0.2 cache table scan latch 0 N/A N/A 0 298 0.0 channel handle pool latc 8,071 0.0 N/A 0 0 N/A channel operations paren 1,324,502 0.6 3.4 41 0 N/A checkpoint queue latch 43,921,031 0.0 0.0 0 2,694,814 0.0 client/application info 4,831 0.0 N/A 0 0 N/A commit callback allocati 21,230 0.0 N/A 0 0 N/A
  • 55. db cache相关的闩锁cache buffer handles 3,304,426 0.0 0.0 0 0 N/A cache buffers chains 1,947,850,767 3.1 0.0 11 37,821,316 0.0 cache buffers lru chain 6,997,185 0.4 0.0 0 38,587,335 0.4 cache table scan latch 0 N/A N/A 0 813,898 0.0 checkpoint queue latch 4,562,432 0.0 0.0 0 2,364,920 0.0
  • 56. 共享池相关的闩锁library cache 42,338,349 0.3 0.0 0 10,118 0.2 library cache load lock 282 0.0 N/A 0 0 N/A library cache lock 6,764,130 0.1 0.0 0 0 N/A library cache lock alloc 40,028 0.0 N/A 0 0 N/A library cache pin 24,725,599 0.1 0.0 0 0 N/A library cache pin alloca 5,011 0.0 N/A 0 0 N/A row cache objects 17,337,305 0.0 0.0 0 233 0.0 shared pool 9,363,198 0.2 0.0 0 0 N/A shared pool simulator 21,898,357 0.0 0.0 0 0 N/A
  • 57. REDO相关的闩锁redo allocation 3,906,645 0.5 0.0 0 18,728,200 1.0 redo copy 0 N/A N/A 0 18,789,369 0.3 redo writing 3,932,722 0.0 0.0 0 0 N/A
  • 58. Latch Sleep Breakdown Latch Name ---------------------------------------- Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3 -------------- ----------- ----------- ---------- -------- -------- -------- cache buffers chains 1,146,663,981 14,194,293 60,121 ########## 0 0 0 logminer work area 93,443,790 1,887,188 179 1,887,036 0 0 0 messages 8,834,029 360,556 87 360,470 0 0 0 active checkpoint queue latch 2,361,392 346,535 93 346,442 0 0 0 shared pool 38,038,776 256,539 1,228 255,340 0 0 0 redo writing 4,174,021 185,338 67 185,271 0 0 0
  • 59. Latch Miss Sources NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- cache buffers chains kcbgtcr: kslbegin excl 0 267,267 249,097 cache buffers chains kcbrls: kslbegin 0 19,553 40,589 cache buffers chains kclcls_3 0 11,562 5,435 cache buffers chains kcbzwb 0 6,743 10,608 cache buffers chains kcbgtcr: fast path 0 1,569 302 cache buffers chains kcbget: pin buffer 0 1,222 367 cache buffers chains kcbbxsv 0 526 548 cache buffers chains kcbgcur: kslbegin 0 287 310 cache buffers chains kcbchg: kslbegin: bufs not 0 264 1,048 cache buffers chains kcbnlc 0 230 242 cache buffers chains kcbget: exchange rls 0 67 20 cache buffers chains kcbget: exchange 0 54 45 cache buffers chains kcbzgb: scan from tail. no 0 47 0 cache buffers chains kcbgtcr: kslbegin shared 0 43 24 cache buffers chains kcbgkcbcr 0 27 22
  • 60. 感谢您对华章培训网的支持!http://www. hztraining.com
  • 61. Segments by Logical Reads Tablespace Subobject Obj. Logical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- rds6 rds_LOG_D0 st_THREE_PART_CHECK TABLE 116,607,536 22.70 rds6 BAR_RECORD st_BAR_RECORD ORD_200911 TABLE 110,294,544 21.47 rds6 BAR_RECORD IDX_st_BAR_RECORD_3 ORD_200911 INDEX 21,493,440 4.18 rds6 BAR_RECORD IDX_st_BAR_RECORD_3 ORD_200908 INDEX 17,538,896 3.41 rds6 BAR_RECORD IDX_st_BAR_RECORD_3 ORD_200909 INDEX 16,489,584 3.21 -------------------------------------------------------------
  • 62. Segments by Physical Reads Tablespace Subobject Obj. Physical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- rds6 XXX_RECORD st_XXX_RECORD ORD_200911 TABLE 332,386 20.96 rds6 XXX_NOOBIL IDX_st_XXX_NOOBILL_T W_T_200911 INDEX 235,490 14.85 rds6 XXX_RECORD IDX_st_XXX_RECORD_1 ORD_200911 INDEX 220,283 13.89 rds6 XXX_NOOBIL st_XXX_NOOBILL_TRACE W_T_200911 TABLE 90,958 5.74 rds6 XXX_NOOBIL IDX_st_XXX_NOOBILL_T W_T_200910 INDEX 66,708 4.21
  • 63. Segments by Row Lock Waits Row Tablespace Subobject Obj. Lock % of Owner Name Object Name Name Type Waits Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- rds6 BAR_RECORD IPK_st_BAR_RECORD ORD_200911 INDEX 6,221 29.63 rds6 BAR_NOOBIL IPK_st_BAR_NOOBILL_T W_T_200911 INDEX 6,181 29.44 rds6 BAR_RECORD IDX_st_BAR_RECORD_3 ORD_200911 INDEX 1,329 6.33 rds6 BAR_RECORD IDX_st_BAR_RECORD_4 ORD_200911 INDEX 1,279 6.09 rds6 BAR_NOOBIL IDX_st_BAR_NOOBILL_T W_T_200911 INDEX 736 3.51
  • 64. Segments by ITL Waits Tablespace Subobject Obj. ITL % of Owner Name Object Name Name Type Waits Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- rds6 BAR_RECORD IDX_st_BAR_RECORD_3 ORD_200911 INDEX 22 28.95 rds6 BILLING_IN IDX_TL_VISIT_PARAM_T LOG_200911 INDEX 10 13.16 rds6 BILLING_IN IDX_TL_VISIT_LOG_TM_ LOG_200911 INDEX 9 11.84 rds6 BAR_RECORD IPK_st_BAR_RECORD ORD_200911 INDEX 6 7.89 rds6 BAR_RECORD IDX_st_BAR_RECORD_4 ORD_200911 INDEX 4 5.26
  • 65. Segments by Buffer Busy Waits Buffer Tablespace Subobject Obj. Busy % of Owner Name Object Name Name Type Waits Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- rds6 rds_LOG_D0 st_BAR_EXPORT_INT TABLE 60,726 53.34 rds6 BAR_RECORD IPK_st_BAR_RECORD ORD_200911 INDEX 29,010 25.48 rds6 BAR_NOOBIL IPK_st_BAR_NOOBILL_T W_T_200911 INDEX 13,698 12.03 rds6 rds_LOG_I0 IPK_st_BAR_EXPORT_IN INDEX 1,439 1.26 rds6 rds6_BASE_ IDX_st_BAR_EXPORT_IN INDEX 1,113 .98
  • 66. Segments by Global Cache Buffer Busy Tablespace Subobject Obj. Buffer % of Owner Name Object Name Name Type Busy Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- rds6 rds_LOG_D0 st_THREE_PART_CHECK TABLE 456,198 90.76 rds6 rds_LOG_I0 IPK_st_3PCHECK_INPUT INDEX 26,044 5.18 rds6 BAR_RECORD st_BAR_RECORD ORD_200911 TABLE 4,730 .94 rds6 BAR_NOOBIL st_BAR_NOOBILL_TRACE W_T_200911 TABLE 3,523 .70 rds6 BAR_RECORD IDX_st_BAR_RECORD_3 ORD_200911 INDEX 2,444 .49
  • 67. Segments by CR Blocks Received CR Tablespace Subobject Obj. Blocks Owner Name Object Name Name Type Received %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- rds6 rds_LOG_D0 st_THREE_PART_CHECK TABLE 526,620 78.27 rds6 rds_LOG_I0 IPK_st_3PCHECK_INPUT INDEX 32,588 4.84 rds6 rds6_WAYBI IDX_st_NOOBILL_1 5_W_200911 INDEX 27,128 4.03 rds6 rds6_BASE_ IDX_EXPIRED_OPTE_TYP INDEX 20,440 3.04 rds6 BAR_NOOBIL IDX_st_BAR_NOOBILL_T W_T_200911 INDEX 12,437 1.85
  • 68. Segments by Current Blocks Received Current Tablespace Subobject Obj. Blocks Owner Name Object Name Name Type Received %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- rds6 rds_LOG_D0 st_THREE_PART_CHECK TABLE 92,545 29.62 rds6 rds_LOG_I0 IDX_BAR_EXPORT_THIRD INDEX 34,067 10.90 rds6 rds6_WAYBI st_NOOBILL 5_W_200911 TABLE 18,665 5.97 rds6 rds6_WAYBI IDX_st_NOOBILL_1 5_W_200911 INDEX 13,487 4.32 rds6 BAR_NOOBIL IDX_st_BAR_NOOBILL_T W_T_200911 INDEX 12,314 3.94
  • 69. Dictionary Cache Stats Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_awr_control 62 3.2 0 N/A 0 1 dc_database_links 30,931 0.0 0 N/A 0 5 dc_files 30,118 0.0 0 N/A 0 407 dc_global_oids 2,176,488 0.0 0 N/A 0 56 dc_histogram_data 489 19.4 0 N/A 0 14,040 dc_histogram_defs 1,741 2.8 0 N/A 0 6,491 dc_object_grants 20 0.0 0 N/A 0 245 dc_object_ids 2,233,169 0.0 0 N/A 0 1,788 dc_objects 876 0.1 0 N/A 6 2,811 dc_profiles 373 0.0 0 N/A 0 1 dc_rollback_segments 111,095 0.0 0 N/A 0 2,115 dc_segments 930 1.9 0 N/A 98 2,098 dc_sequences 2,503 0.7 0 N/A 2,503 8 dc_table_scns 14 0.0 0 N/A 0 7 dc_tablespace_quotas 96 18.8 0 N/A 0 10 dc_tablespaces 305,234 0.0 0 N/A 0 75 dc_usernames 9,272,854 0.0 0 N/A 0 53 dc_users 2,529,561 0.0 0 N/A 0 83 outstanding_alerts 842 97.1 0 N/A 0 139
  • 70. Dictionary Cache Stats (RAC) GES GES GES Cache Requests Conflicts Releases ------------------------- ------------ ------------ ------------ dc_awr_control 2 2 0 dc_histogram_defs 48 726 0 dc_object_ids 12 0 0 dc_objects 15 2 0 dc_segments 195 16 0 dc_sequences 5,006 16 0 dc_tablespace_quotas 192 17 0 outstanding_alerts 1,660 682 0
  • 71. Library Cache Activity Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 1,022 0.0 243,522 0.0 0 0 INDEX 12 0.0 32 0.0 0 0 SQL AREA 42,583 0.3 9,129,495 -0.0 8 3 TABLE/PROCEDURE 1,444 0.0 8,218,364 0.0 35 0 TRIGGER 16 0.0 287,201 0.0 0 0
  • 72. Library Cache Activity(RAC) GES Lock GES Pin GES Pin GES Inval GES Invali- Namespace Requests Requests Releases Requests dations --------------- ------------ ------------ ------------ ----------- ----------- INDEX 32 8 0 10 0 TABLE/PROCEDURE 56,632 43 80 359 0
  • 73. Process Memory Summary Hist Avg Std Dev Max Max Alloc Used Alloc Alloc Alloc Alloc Num Num Category (MB) (MB) (MB) (MB) (MB) (MB) Proc Alloc - -------- --------- --------- -------- -------- ------- ------- ------ ------ B Other 16,862.4 N/A 56.0 625.0 7,980 7,980 301 301 Freeable 192.3 .0 1.2 2.1 22 N/A 166 166 SQL 17.8 12.6 .1 .7 11 185 270 175 PL/SQL 5.6 3.4 .0 .0 0 1 299 298 E Other 16,963.5 N/A 56.5 628.6 8,011 8,011 300 300 Freeable 381.1 .0 2.3 11.1 100 N/A 164 164 SQL 21.3 16.0 .1 .7 11 185 269 180 PL/SQL 5.6 3.2 .0 .0 0 1 298 297
  • 74. SGA Memory Summary End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ ------------------- ------------------- Database Buffers 25,618,808,832 Fixed Size 2,163,592 Redo Buffers 14,610,432 Variable Size 17,314,090,104 ------------------- sum 42,949,672,960
  • 75. SGA breakdown differencePool Name Begin MB End MB % Diff ------ ------------------------------ -------------- -------------- ------- java free memory 64.0 64.0 0.00 large PX msg pool 1.0 1.0 0.00 large free memory 63.0 63.0 0.00 shared CCursor 74.8 75.0 0.30 shared db_block_hash_buckets 180.0 180.0 0.00 shared free memory 2,004.3 2,000.4 -0.20 shared gcs resources 577.5 577.5 0.00 shared gcs shadows 345.8 345.8 0.00 shared library cache 44.0 44.1 0.19 shared sql area 485.1 486.1 0.21 stream free memory 6,377.3 6,472.9 1.50 stream internal row lcr 521.4 521.4 0.00 stream koicnfadt adt attr 3,190.8 3,190.9 0.00 stream kol raw 4,806.0 4,807.1 0.02 stream kol vstring 916.7 916.8 0.01 stream kolcalm coll 4,316.8 4,316.9 0.00 stream kolccst obj 21,283.4 21,284.2 0.00 stream kolcpcon kolcol 452.6 452.6 0.01 stream kwqbdaspl:spilledovermsgs 1,164.3 1,201.1 3.16 buffer_cache 24,432.0 24,432.0 0.00 fixed_sga 2.1 2.1 0.00 log_buffer 13.9 13.9 0.00
  • 76. 感谢您对华章培训网的支持!http://www. hztraining.com
  • 77. Streams CPU/IO Usage Session Type CPU Time User I/O Time Sys I/O Time ------------------------- -------------- -------------- -------------- Propagation Sender 1,700,517,370 0 0 QMON Slaves 455,432,626 110,901,002 0 STREAMS Apply Server 237,862,575 911,531,701 0 Logminer Preparer 136,651,770 0 0 STREAMS Capture 105,340,039 54,746,071 645,476 Propagation Receiver 77,120,432 179,280 0 Logminer Builder 74,150,255 231,457 0 STREAMS Apply Reader 36,365,934 0 0 Logminer Reader 36,092,209 5,801 2,498,479 STREAMS Apply Coordinator 1,209,698 0 0 QMON Coordinator 185,017 0 0
  • 78. Streams Capture Captured Enqueued Pct Pct Pct Pct Per Per Lag RuleEval Enqueue RedoWait Pause Capture Name Second Second Change Time Time Time Time ------------ -------- -------- -------- -------- -------- -------- -------- HQ_CAPTURE 1,218 134 1,128 0 1 1 15
  • 79. Streams Apply Applied Pct Pct Pct Pct Applied Dequeue Apply Lag Apply Name TPS DB WDEP WCMT RBK MPS TPM TPM Change ------------ -------- ---- ---- ---- --- -------- -------- -------- -------- HD_APPLY 1 1 2 68 0 74 0 2 -6 HZ_APPLY 1 1 3 76 0 96 0 4 0
  • 80. Buffered Queues Incoming Outgoing Spilled Diff Queue Schema and Name per second per second per second Pct Spilled --------------------- ----------- ----------- ----------- ------------ STRMADMIN.HQ_CAPTURE_ 506 535 303 0 STRMADMIN.HD_APPLY_QU 74 75 0 0 STRMADMIN.HZ_APPLY_QU 96 97 0 0
  • 81. Buffered Subscribers Incoming Outgoing Spilled Subscriber Name per second per second per second -------------------- ----------- ----------- ----------- PROXY: HZSFOSS 506 535 303 HD_APPLY -1,179 -1,179 1 HD_APPLY 74 75 0 HZ_APPLY 96 97 0 PROXY: HDSFOSS 506 504 0 HZ_APPLY 1,349 1,350 -1
  • 82. Rule Set Fast SQL CPU Elapsed Ruleset Name Evals Evals Execs Time Time ------------------------- -------- -------- -------- -------- -------- STRMADMIN.RULESET$_597 424 0 0 3 8 STRMADMIN.RULESET$_283 348 0 0 0 7 SYS.ALERT_QUE_R 25 0 0 0 0
  • 83. Resource Limit Stats Current Maximum Initial Resource Name Utilization Utilization Allocation Limit ------------------------------ ------------ ------------ ---------- ---------- gcs_shadows 3,051,489 3,308,137 3737495 3737495
  • 84. init.ora Parameters End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- _job_queue_interval 1 _optim_peek_user_binds TRUE aq_tm_processes 1 archive_lag_target 600 audit_file_dest /u01/app/oracle/admin/sfoss/adump audit_trail DB background_dump_dest /u01/app/oracle/admin/sfoss/bdump cluster_database TRUE
  • 85. Global Enqueue StatisticsStatistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ acks for commit broadcast(actual) 599,225 166.8 1.4 acks for commit broadcast(logical 751,919 209.3 1.7 broadcast msgs on commit(actual) 514,166 143.1 1.2 broadcast msgs on commit(logical) 524,549 146.0 1.2 broadcast msgs on commit(wasted) 49,261 13.7 0.1 broadcast on commit wait time(ms) 42,905 11.9 0.1 broadcast on commit waits 300,456 83.6 0.7 dynamically allocated gcs resourc 0 0.0 0.0 dynamically allocated gcs shadows 0 0.0 0.0 false posts waiting for scn acks 0 0.0 0.0 flow control messages received 0 0.0 0.0 flow control messages sent 0 0.0 0.0 gcs assume cvt 0 0.0 0.0 gcs assume no cvt 121,542 33.8 0.3 gcs ast xid 98 0.0 0.0 gcs blocked converts 229,399 63.9 0.5 gcs blocked cr converts 731,270 203.6 1.7 gcs compatible basts 1,012 0.3 0.0
  • 86. Global Enqueue Statistics(2)gcs compatible cr basts (global) 59,336 16.5 0.1 gcs compatible cr basts (local) 113,455 31.6 0.3 gcs cr basts to PIs 0 0.0 0.0 gcs cr serve without current lock 0 0.0 0.0 gcs dbwr flush pi msgs 42,810 11.9 0.1 gcs dbwr write request msgs 44,591 12.4 0.1 gcs error msgs 0 0.0 0.0 gcs forward cr to pinged instance 0 0.0 0.0 gcs immediate (compatible) conver 75,932 21.1 0.2 gcs immediate (null) converts 281,325 78.3 0.6 gcs immediate cr (compatible) con 7 0.0 0.0 gcs immediate cr (null) converts 866,696 241.2 2.0 gcs indirect ast 574,091 159.8 1.3 gcs lms flush pi msgs 0 0.0 0.0 gcs lms write request msgs 71,342 19.9 0.2 gcs msgs process time(ms) 76,292 21.2 0.2 gcs msgs received 4,114,442 1,145.3 9.4 gcs out-of-order msgs 0 0.0 0.0 gcs pings refused 58 0.0 0.0 gcs pkey conflicts retry 0 0.0 0.0 gcs queued converts 19 0.0 0.0
  • 87. Global Enqueue Statistics(3)gcs recovery claim msgs 0 0.0 0.0 gcs refuse xid 140 0.0 0.0 gcs regular cr 0 0.0 0.0 gcs retry convert request 0 0.0 0.0 gcs side channel msgs actual 69,537 19.4 0.2 gcs side channel msgs logical 1,345,029 374.4 3.1 gcs stale cr 78 0.0 0.0 gcs undo cr 1,380 0.4 0.0 gcs write notification msgs 6,708 1.9 0.0 gcs writes refused 242 0.1 0.0 ges msgs process time(ms) 1,598 0.4 0.0 ges msgs received 108,124 30.1 0.2 global posts dropped 0 0.0 0.0 global posts queue time 29 0.0 0.0 global posts queued 49 0.0 0.0 global posts requested 49 0.0 0.0 global posts sent 49 0.0 0.0 implicit batch messages received 313,733 87.3 0.7 implicit batch messages sent 194,770 54.2 0.4 lmd msg send time(ms) 0 0.0 0.0 lms(s) msg send time(ms) 0 0.0 0.0
  • 88. Global Enqueue Statistics(4)Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ messages flow controlled 523,478 145.7 1.2 messages queue sent actual 1,189,024 331.0 2.7 messages queue sent logical 1,486,058 413.6 3.4 messages received actual 3,170,143 882.4 7.2 messages received logical 4,222,576 1,175.4 9.6 messages sent directly 1,406,582 391.5 3.2 messages sent indirectly 906,491 252.3 2.1 messages sent not implicit batche 994,328 276.8 2.3 messages sent pbatched 1,365,724 380.2 3.1 msgs causing lmd to send msgs 23,773 6.6 0.1 msgs causing lms(s) to send msgs 189,122 52.6 0.4 msgs received queue time (ms) 74,534 20.7 0.2 msgs received queued 4,222,368 1,175.3 9.6 msgs sent queue time (ms) 47,409 13.2 0.1 msgs sent queue time on ksxp (ms) 1,725,042 480.2 3.9 msgs sent queued 1,478,914 411.7 3.4 msgs sent queued on ksxp 3,236,792 901.0 7.4 process batch messages received 651,171 181.3 1.5 process batch messages sent 837,131 233.0 1.9
  • 89. Global CR Served StatsStatistic Total ------------------------------ ------------------ CR Block Requests 279,160 CURRENT Block Requests 6,044 Data Block Requests 279,160 Undo Block Requests 1,380 TX Block Requests 4,059 Current Results 281,708 Private results 648 Zero Results 2,837 Disk Read Results 0 Fail Results 0 Fairness Down Converts 18,736 Fairness Clears 0 Free GC Elements 0 Flushes 112,414 Flushes Queued 0 Flush Queue Full 0 Flush Max Time (us) 0 Light Works 8,241 Errors 0
  • 90. Global CURRENT Served StatsStatistic Total % <1ms % <10ms % <100ms % <1s % <10s ---------- ------------ -------- -------- -------- -------- -------- Pins 234,673 98.67 0.01 1.29 0.04 0.00 Flushes 3,469 49.58 23.03 20.55 6.83 0.00 Writes 45,592 48.43 37.08 10.96 2.19 1.34
  • 91. Global Cache Transfer Stats CR Current ----------------------------- ----------------------------- Inst Block Blocks % % % Blocks % % % No Class Received Immed Busy Congst Received Immed Busy Congst ---- ----------- -------- ------ ------ ------ -------- ------ ------ ------ 1 data block 662,909 83.3 16.7 .0 308,744 98.9 1.1 .0 1 undo header 7,790 94.7 5.3 .0 266 99.2 .8 .0 1 Others 1,202 99.5 .5 .0 3,457 99.1 .9 .0 1 undo block 953 100.0 .0 .0 0 N/A N/A N/A
  • 92. 感谢您对华章培训网的支持!http://www. hztraining.com
  • 93. 11.2新增内容-LOAD PROFILELoad Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~ --------------- --------------- ---------- ---------- DB Time(s): 0.4 4.1 0.03 0.00 DB CPU(s): 0.1 0.5 0.00 0.00 Redo size: 297,199.0 3,064,279.2 Logical reads: 2,640.6 27,225.5 Block changes: 2,939.6 30,308.4 Physical reads: 1.9 19.7 Physical writes: 28.5 294.0 User calls: 138.3 1,426.2 Parses: 4.3 44.5 Hard parses: 0.7 7.2 W/A MB processed: 0.3 2.8 Logons: 0.1 0.5 Executes: 12.1 124.4 Rollbacks: 0.0 0.0 Transactions: 0.1
  • 94. 11.2新增内容-HOST CPUHost CPU (CPUs: 2 Cores: 2 Sockets: 1) ~~~~~~~~ Load Average Begin End %User %System %WIO %Idle --------- --------- --------- --------- --------- --------- 0.16 0.26 4.5 6.9 26.8 86.3
  • 95. 11.2新增内容-HOST CPUHost CPU (CPUs: 2 Cores: 2 Sockets: 1) ~~~~~~~~ Load Average Begin End %User %System %WIO %Idle --------- --------- --------- --------- --------- --------- 0.16 0.26 4.5 6.9 26.8 86.3
  • 96. 11.2新增内容-Instance CPUInstance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 4.4 % of busy CPU for Instance: 31.9 %DB time waiting for CPU - Resource Mgr: 0.0
  • 97. 11.2新增内容-Memory StatisticsMemory Statistics ~~~~~~~~~~~~~~~~~ Begin End Host Mem (MB): 2,002.5 2,002.5 SGA use (MB): 488.0 488.0 PGA use (MB): 175.7 196.6 % Host Mem used for SGA+PGA: 33.14 34.19
  • 98. 11.2新增内容-Operating System StatisticsSnap Time Load %busy %user %sys %idle %iowait --------------- -------- -------- -------- -------- -------- -------- 05-Jul 14:00:16 0.2 N/A N/A N/A N/A N/A 05-Jul 14:01:40 0.1 10.3 3.7 4.1 89.7 14.6 05-Jul 14:12:17 0.3 14.1 4.6 7.2 85.9 28.2
  • 99. 11.2新增内容-Operating System StatisticsSnap Time Load %busy %user %sys %idle %iowait --------------- -------- -------- -------- -------- -------- -------- 05-Jul 14:00:16 0.2 N/A N/A N/A N/A N/A 05-Jul 14:01:40 0.1 10.3 3.7 4.1 89.7 14.6 05-Jul 14:12:17 0.3 14.1 4.6 7.2 85.9 28.2
  • 100. 11.2新增内容-Wait Event Histogram % of Waits ----------------------------------------------- Total Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ASM file metadata operatio 371 86.8 2.2 3.8 3.0 .5 1.1 2.4 .3 CGS wait for IPC msg 5659 99.9 .1 .0 CSS initialization 10 50.0 40.0 10.0 CSS operation: action 12 8.3 83.3 8.3 CSS operation: query 30 83.3 13.3 3.3 DFS lock handle 110 43.6 29.1 15.5 2.7 .9 8.2 Data file init write 8 25.0 75.0 Disk file operations I/O 136 81.6 8.1 2.9 .7 2.2 .7 2.2 1.5 IPC send completion sync 20 65.0 25.0 5.0 5.0 KJC: Wait for msg sends to 100 98.0 2.0 LGWR wait for redo copy 7 85.7 14.3 PX Deq: Signal ACK EXT 3 66.7 33.3 PX Deq: Signal ACK RSG 3 100.0 PX Deq: Slave Session Stat 8 75.0 12.5 12.5 PX Deq: reap credit 300 100.0 PX qref latch 74 98.6 1.4
  • 101. 11.2新增内容-Wait Event Histogram % of Waits ----------------------------------------------- Total Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ASM file metadata operatio 371 86.8 2.2 3.8 3.0 .5 1.1 2.4 .3 CGS wait for IPC msg 5659 99.9 .1 .0 CSS initialization 10 50.0 40.0 10.0 CSS operation: action 12 8.3 83.3 8.3 CSS operation: query 30 83.3 13.3 3.3 DFS lock handle 110 43.6 29.1 15.5 2.7 .9 8.2 Data file init write 8 25.0 75.0 Disk file operations I/O 136 81.6 8.1 2.9 .7 2.2 .7 2.2 1.5 IPC send completion sync 20 65.0 25.0 5.0 5.0 KJC: Wait for msg sends to 100 98.0 2.0 LGWR wait for redo copy 7 85.7 14.3 PX Deq: Signal ACK EXT 3 66.7 33.3 PX Deq: Signal ACK RSG 3 100.0 PX Deq: Slave Session Stat 8 75.0 12.5 12.5 PX Deq: reap credit 300 100.0 PX qref latch 74 98.6 1.4
  • 102. 11.2新增内容-Wait Event Histogram Detail % of Total Waits ----------------------------------------------- Waits 64ms Event to 2s <32ms <64ms <1/8s <1/4s <1/2s <1s <2s >=2s -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ASM file metadata operatio 9 97.3 1.1 .3 .3 .5 .3 .3 DFS lock handle 9 91.8 5.5 1.8 .9 Data file init write 6 25.0 37.5 25.0 12.5 Disk file operations I/O 5 96.3 .7 .7 .7 1.5 IPC send completion sync 1 95.0 5.0 PX Deq: Slave Session Stat 1 87.5 12.5 buffer busy waits 2 50.0 20.0 30.0 control file parallel writ 35 90.4 2.3 1.3 1.3 1.3 1.3 1.5 .8 control file sequential re 216 90.4 1.3 1.1 1.1 1.6 2.1 1.9 .5 db file parallel write 279 54.2 8.8 8.3 9.0 10.0 6.1 3.4 .2 db file scattered read 5 71.4 4.8 4.8 9.5 4.8 4.8 db file sequential read 165 85.5 5.2 3.3 1.7 1.6 1.5 .6 .7 enq: CF - contention 1 95.5 4.5 enq: CO - master slave det 2 99.1 .4 .4 enq: HW - contention 1 96.2 3.8 enq: WF - contention 1 94.4 5.6
  • 103. 11.2新增内容-Wait Event Histogram Detail(2) % of Total Waits ----------------------------------------------- Waits 4s Event to 2m <2s <4s <8s <16s <32s < 1m < 2m >=2m -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ASM file metadata operatio 1 99.7 .3 buffer busy waits 3 70.0 20.0 10.0 control file parallel writ 3 99.2 .5 .3 control file sequential re 11 99.5 .3 .1 db file parallel write 1 99.8 .2 db file scattered read 1 95.2 4.8 db file sequential read 8 99.3 .4 .2 .1 log buffer space 17 81.7 16.1 1.1 1.1 log file parallel write 19 94.8 4.7 .3 .3 log file switch (checkpoin 1 80.0 20.0 log file sync 5 75.0 20.0 5.0
  • 104. 11.2新增内容-Wait Event Histogram Detail(3) Wait Event Histogram Detail (4 min to 1 hr)DB/Inst: TESTDB/testdb_2
  • 105. 11.2新增内容-TOP SQL的改进 Elapsed Elapsed Time Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id ---------------- -------------- ------------- ------ ------ ------ ------------- 83.2 2 41.62 28.7 7.7 5.0 0ufjjw976htrx Module: SQL*Plus select * from test_obj for update
  • 106. 11.2新增内容-TOP SQL的改进(2) SQL ordered by Cluster Wait Time DB/Inst: TESTDB/testdb_2 Snaps: 1029-1031 -> %Total - Cluster Time as a percentage of Total Cluster Wait Time -> %Clu - Cluster Time as a percentage of Elapsed Time -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Only SQL with Cluster Wait Time > .005 seconds is reported -> Total Cluster Wait Time (s): 7 -> Captured SQL account for 72.8% of Total Cluster Elapsed Wait Time (s) Executions %Total Time(s) %Clu %CPU %IO SQL Id -------------- ------------ ------ ---------- ------ ------ ------ ------------- VALUES (NULL, NULL, :B5 , NULL, NULL, 0, :B4 , :B3 , :B2 , NULL, NULL, NULL, :B
  • 107. 11.2新增内容-TOP SQL的改进(3) SQL ordered by Cluster Wait Time DB/Inst: TESTDB/testdb_2 Snaps: 1029-1031 -> %Total - Cluster Time as a percentage of Total Cluster Wait Time -> %Clu - Cluster Time as a percentage of Elapsed Time -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Only SQL with Cluster Wait Time > .005 seconds is reported -> Total Cluster Wait Time (s): 7 -> Captured SQL account for 72.8% of Total Cluster Elapsed Wait Time (s) Executions %Total Time(s) %Clu %CPU %IO SQL Id -------------- ------------ ------ ---------- ------ ------ ------ ------------- VALUES (NULL, NULL, :B5 , NULL, NULL, 0, :B4 , :B3 , :B2 , NULL, NULL, NULL, :B
  • 108. 11.2新增内容-TOP SQL的改进(4) SQL ordered by User I/O Wait Time DB/Inst: TESTDB/testdb_2 Snaps: 1029-1031 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> %Total - User I/O Time as a percentage of Total User I/O Wait time -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Captured SQL account for 92.3% of Total User I/O Wait Time (s): -> Captured PL/SQL account for 51.1% of Total User I/O Wait Time (s): User I/O UIO per Elapsed Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id ---------- ------------ ---------- ------ ---------- ------ ------ ------------- 38.5 1 38.51 39.3 42.3 .8 91.1 9ctt1scmwbmbg begin dbsnmp.bsln_internal.maintain_thresholds; end;
  • 109. 11.2新增内容-Instance Activity Stats增强 Statistic Begin Value End Value -------------------------------- --------------- --------------- session pga memory 144,425,216 200,214,952 session pga memory max 171,508,640 300,453,608 session cursor cache count 814 1,423 session uga memory 8,604,812,632 30,083,539,744 opened cursors current 52 56 logons current 48 52 session uga memory max 118,134,608 241,292,672
  • 110. 11.2新增内容-IOStat by Function summary Reads: Reqs Data Writes: Reqs Data Waits: Avg Function Name Data per sec per sec Data per sec per sec Count Tm(ms) --------------- ------- ------- ------- ------- ------- ------- ------- ------- LGWR 3M 0.3 .004156 428M 1.8 .593013 672 239.6 DBWR 1M 0.1 .001385 162M 11.5 .224458 45 2.4 Others 34M 3.0 .047108 21M 1.0 .029096 2498 79.0 Buffer Cache Re 11M 1.6 .015241 0M 0.0 0M 1182 79.9 TOTAL: 49M 5.0 .067891 611M 14.3 .846568 4397 103.0
  • 111. 11.2新增内容-IOStat by Filetype summary Reads: Reqs Data Writes: Reqs Data Small Large Filetype Name Data per sec per sec Data per sec per sec Read Read --------------- ------- ------- ------- ------- ------- ------- ------- ------- Log File 0M 0.0 0M 427M 1.7 .591628 0.8 N/A Data File 12M 1.7 .016626 161M 11.5 .223072 78.6 190.0 Control File 37M 3.3 .051265 12M 1.1 .016626 71.5 N/A Temp File 0M 0.0 0M 0M 0.0 0M N/A N/A TOTAL: 49M 5.0 .067891 600M 14.3 .831327 73.5 190.0
  • 112. 11.2新增内容-IOStat by Function/Filetype Reads: Reqs Data Writes: Reqs Data Waits: Avg Data per sec per sec Data per sec per sec Count Tm(ms) ------- ------- ------- ------- ------- ------- ------- ------- LGWR 3M 0.3 .004156 427M 1.8 .591628 252 16.6 LGWR (Log File) 0M 0.0 0M 426M 1.7 .590242 40 0.8 LGWR (Control File) 3M 0.3 .004156 1M 0.2 .001385 212 19.6 DBWR 1M 0.1 .001385 162M 11.5 .224458 45 2.4 DBWR (Data File) 0M 0.0 0M 162M 11.5 .224458 0 N/A DBWR (Control File) 1M 0.1 .001385 0M 0.0 0M 45 2.4 Others (Control File) 34M 2.9 .047108 11M 1.0 .015241 2118 78.2 Others (Data File) 1M 0.0 .001385 10M 0.0 .013855 34 32.9 Buffer Cache Reads 11M 1.6 .015241 0M 0.0 0M 1166 80.9 Buffer Cache Reads (Data File) 11M 1.6 .015241 0M 0.0 0M 1166 80.9 TOTAL: 50M 5.0 .069277 610M 14.3 .845183 3615 73.4
  • 113. 11.2新增-Segments by UnOptimized Reads Tablespace Subobject Obj. UnOptimized Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- SCOTT USERS IDX_TESTOBJ INDEX 298 24.85 SYS SYSTEM HISTGRM$ TABLE 250 20.85 SCOTT USERS TEST_OBJ TABLE 114 9.51 SYS SYSTEM IDL_UB1$ TABLE 79 6.59 SYS SYSAUX WRH$_SQL_PLAN TABLE 45 3.75
  • 114. 11.2新增-内存管理Memory Resize Operations Summary DB/Inst: TESTDB/testdb_2 Snaps: 1029-1031 No data exists for this section of the report. ------------------------------------------------------------- Memory Resize Ops DB/Inst: TESTDB/testdb_2 Snaps: 1029-1031 No data exists for this section of the report. -------------------------------------------------------------
  • 115. 11.2新增-共享服务器Shared Servers Activity DB/Inst: TESTDB/testdb_2 Snaps: 1029-1031 -> Values represent averages for all samples Avg Total Avg Active Avg Total Avg Active Avg Total Avg Active Connections Connections Shared Srvrs Shared Srvrs Dispatchers Dispatchers ------------ ------------ ------------ ------------ ------------ ------------ 0 0 1 0 1 0 ------------------------------------------------------------- Shared Servers Rates DB/Inst: TESTDB/testdb_2 Snaps: 1029-1031 Common Disp Common Disp Server Queue Queue Server Server Queue Queue Total Server Per Sec Per Sec Msgs/Sec KB/Sec Total Total Msgs Total(KB) -------- -------- -------- --------- ---------- ---------- ---------- ---------- 0 0 0 0.0 0 0 0 0 ------------------------------------------------------------- Shared Servers Utilization DB/Inst: TESTDB/testdb_2 Snaps: 1029-1031 -> Statistics are combined for all servers -> Incoming and Outgoing Net % are included in %Busy Total Server Incoming Outgoing Time (s) %Busy %Idle Net % Net % -------------- -------- -------- --------- --------- 709 0.0 100.0 0.0 0.0
  • 116. 11.2新增-Interconnect Ping Latency Stats Target 500B Pin Avg Latency Stddev 8K Ping Avg Latency Stddev Instance Count 500B msg 500B msg Count 8K msg 8K msg --------- -------- ----------- ----------- -------- ----------- ----------- 1 56 1.62 1.55 56 1.89 1.76 2 56 .48 .51 56 .44 .48
  • 117. 11.2新增-Interconnect Throughput by Client Send Receive Used By Mbytes/sec Mbytes/sec ---------------- ----------- ----------- Global Cache .00 .02 Parallel Query .00 .00 DB Locks .00 .00 DB Streams .00 .00 Other .00 .00
  • 118. 11.2新增-Interconnect Device StatisticsDevice Name IP Address Public Source --------------- ---------------- ------ ------------------------------- Send Send Send Send Send Buffer Carrier Mbytes/sec Errors Dropped Overrun Lost ----------- -------- -------- -------- -------- Receive Receive Receive Receive Receive Buffer Frame Mbytes/sec Errors Dropped Overrun Errors ----------- -------- -------- -------- -------- eth2:1 169.254.138.214 NO .00 0 0 0 0 .00 0 0 0 0
  • 119. 11.2新增-Dynamic Remastering StatsDynamic Remastering Stats DB/Inst: TESTDB/testdb_2 Snaps: 1029-1031 No data exists for this section of the report.
  • 120. 感谢您对华章培训网的支持!(未完待续) http://www. hztraining.com
  • 121. 感谢您对华章培训网的支持!http://www. hztraining.com欢迎到Oraclefans网交流技术http://www. oraclefans.cn