• 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 -------------------------------- ------------ ------------ ---------- ---------- sfoss 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 --------- --------- --------- --------- --------- --------- --------- --------- sfoss 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. 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
  • 31. Instance Activity Stats - Thread ActivityStatistic Total per Hour -------------------------------- ------------------ --------- log switches (derived) 148 148.31
  • 32. 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
  • 33. 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
  • 34. 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
  • 35. 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
  • 36. 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
  • 37. PGA Aggr Summary PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ------------------ -------------------------- 98.7 123,633 1,566
  • 38. 重要的警告 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.
  • 39. 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,570
  • 40. 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
  • 41. 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
  • 42. 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 ###########
  • 43. 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
  • 44. 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
  • 45. 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
  • 46. 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
  • 47. (本页无文本内容)
  • 48. (本页无文本内容)
  • 49. (本页无文本内容)
  • 50. (本页无文本内容)
  • 51. (本页无文本内容)
  • 52. (本页无文本内容)
  • 53. (本页无文本内容)
  • 54. 感谢您对华章培训网的支持!http://www. hztraining.com欢迎到Oraclefans网交流技术http://www. oraclefans.cn