• 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, WAYBILL_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_WAYBILL_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_bard1_1_10g 20,980 6 11.8 1.0 434 0 711 8.8 A01_RECORD_D01 /dev/rv_bard1_2_10g 20,911 6 11.5 1.0 697 0 736 8.6 A01_RECORD_D01 /dev/rv_bard1_3_10g 20,977 6 11.8 1.0 693 0 758 9.4 A01_RECORD_D01 /dev/rv_bard1_4_10g 20,995 6 11.5 1.0 474 0 761 11.5 A01_RECORD_D01 /dev/rv_bard1_5_10g 20,943 6 11.7 1.0 414 0 683 10.3 A01_RECORD_D01 /dev/rv_bard1_6_10g 20,537 6 12.0 1.0 357 0 679 12.8 A01_RECORD_D01 /dev/rv_bard1_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. 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 ###########
  • 45. 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
  • 46. 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
  • 47. 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
  • 48. 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
  • 49. 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,没有出现表空间不足
  • 50. 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
  • 51. 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
  • 52. 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
  • 53. 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
  • 54. Segments by Logical Reads Tablespace Subobject Obj. Logical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- EXP5 EXP_LOG_D0 TT_THREE_PART_CHECK TABLE 116,607,536 22.70 EXP5 BAR_RECORD TT_BAR_RECORD ORD_200911 TABLE 110,294,544 21.47 EXP5 BAR_RECORD IDX_TT_BAR_RECORD_3 ORD_200911 INDEX 21,493,440 4.18 EXP5 BAR_RECORD IDX_TT_BAR_RECORD_3 ORD_200908 INDEX 17,538,896 3.41 EXP5 BAR_RECORD IDX_TT_BAR_RECORD_3 ORD_200909 INDEX 16,489,584 3.21
  • 55. Segments by Physical Reads Tablespace Subobject Obj. Physical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- EXP5 BAR_RECORD TT_BAR_RECORD ORD_200911 TABLE 332,386 20.96 EXP5 BAR_WAYBIL IDX_TT_BAR_WAYBILL_T W_T_200911 INDEX 235,490 14.85 EXP5 BAR_RECORD IDX_TT_BAR_RECORD_1 ORD_200911 INDEX 220,283 13.89 EXP5 BAR_WAYBIL TT_BAR_WAYBILL_TRACE W_T_200911 TABLE 90,958 5.74 EXP5 BAR_WAYBIL IDX_TT_BAR_WAYBILL_T W_T_200910 INDEX 66,708 4.21
  • 56. (本页无文本内容)
  • 57. (本页无文本内容)
  • 58. (本页无文本内容)
  • 59. (本页无文本内容)
  • 60. (本页无文本内容)
  • 61. (本页无文本内容)
  • 62. (本页无文本内容)
  • 63. (本页无文本内容)
  • 64. (本页无文本内容)
  • 65. 感谢您对华章培训网的支持!http://www. hztraining.com欢迎到Oraclefans网交流技术http://www. oraclefans.cn