乐趣区

关于数据库:Oracle-性能调查之AWR一

在 ORACLE 数据库提供的性能监督,问题考察的工具中,AWR 也是一个十分重要的机能。

AWR 和 STATSPACK 相似,都是定时取对于 DB 整体情况的 Snapshot,而后取指定的两个 Snapshot 算出差值,从而失去两个工夫点之间的 DB 情况。

然而,AWR 和 STATSPACK 又存在以下区别:

1.  AWR 是免费的;STATSPACK 是收费的。2.  AWR 只能在 EE 应用;STATSPACK 能够在 SE 应用。3.  AWR 是用 C 语言写的,采纳 DMA(direct memory access)形式间接拜访内存;STATSPACK 是用 Pl/SQL 写的,须要走和一般 SQL 文的一样的 Parse --> Execute --> Fetch 解决形式。4.  AWR 的 SnapShot 的采集,治理,删除是主动的;STATSPACK 是须要用户本人写 Job 实现的。

因为 AWR 外面蕴含的数据是十分丰盛的,所以应用办法也是多种多样的,明天只举一个小例子。

 客户报告:3 月份开始,AWR Report 的“Buffer Hit %”大幅降落。提供材料:2 月份和 3 月份的 AWR Report。请求事项:起因确认

首先,须要确认一下“Buffer Hit %”的计算方法。

 参考 Note:AWR レポートに出力されるバッファ・キャッシュ・ヒット率の値が正しくない (KROWN:142990) (ドキュメント ID 1750026.1)

1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache')

而后,依据下面的计算式,在 AWR Report 外面蕴含的以下 3 个待机的统计值:

physical reads cache
consistent gets from cache
db block gets from cache

应用一点小伎俩,把蕴含在客户提供的 AWR Report 里的待机值取出来。

◆2 月份
    physical reads cache    consistent gets from cache    db block gets from cache    Buffer Hit%    计算值
awrrpt_Node2_11117_11118.html    1,367,229    123,185,553    2,943,455    98.92    98.92
awrrpt_Node2_11118_11119.html    654,533    127,234,595    450,102    99.49    99.49
awrrpt_Node2_11119_11120.html    1,003,773    130,975,087    567,126    99.24    99.24
awrrpt_Node2_11120_11121.html    1,057,474    129,739,916    510,676    99.19    99.19
awrrpt_Node2_11121_11122.html    806,442    139,078,160    551,815    99.42    99.42
awrrpt_Node2_11122_11123.html    235,276    136,269,822    10,762,534    99.84    99.84
awrrpt_Node2_11123_11124.html    1,314,543    125,717,238    611,062    98.96    98.96
awrrpt_Node2_11124_11125.html    206,319    116,914,725    434,141    99.82    99.82
awrrpt_Node2_11125_11126.html    1,777,179    128,968,423    16,465,897    98.78    98.78
awrrpt_Node2_11126_11127.html    326,310    132,073,666    424,109    99.75    99.75
awrrpt_Node2_11127_11128.html    1,474,847    120,674,422    545,747    98.78    98.78
awrrpt_Node2_11128_11129.html    213,332    118,891,545    386,444    99.82    99.82
awrrpt_Node2_11129_11130.html    1,185,440    129,554,468    849,653    99.09    99.09
awrrpt_Node2_11130_11131.html    291,913    123,851,398    442,434    99.77    99.77
awrrpt_Node2_11131_11132.html    1,277,884    121,854,109    504,381    98.96    98.96
awrrpt_Node2_11132_11133.html    258,371    135,959,454    447,235    99.81    99.81
awrrpt_Node2_11133_11134.html    1,209,367    122,064,000    532,350    99.01    99.01
awrrpt_Node2_11134_11135.html    189,544    119,130,960    401,270    99.84    99.84
awrrpt_Node2_11135_11136.html    1,277,111    143,900,150    626,518    99.12    99.12
awrrpt_Node2_11136_11137.html    233,943    131,330,750    745,066    99.82    99.82
awrrpt_Node2_11137_11138.html    1,146,509    133,473,223    484,318    99.14    99.14
awrrpt_Node2_11138_11139.html    163,751    115,964,992    318,123    99.86    99.86
awrrpt_Node2_11139_11140.html    1,183,224    128,098,854    909,259    99.08    99.08
awrrpt_Node2_11140_11141.html    228,650    121,668,141    16,226,344    99.83    99.83
awrrpt_Node2_11141_11142.html    1,243,272    133,804,917    366,931    99.07    99.07
awrrpt_Node2_11142_11143.html    189,297    140,340,156    324,678    99.87    99.87
awrrpt_Node2_11143_11144.html    1,106,725    108,323,111    393,184    98.98    98.98
awrrpt_Node2_11144_11145.html    217,717    117,828,642    361,044    99.82    99.82
awrrpt_Node2_11145_11146.html    1,121,367    109,813,017    443,225    98.98    98.98
awrrpt_Node2_11146_11147.html    365,329    121,099,235    9,842,513    99.72    99.72
awrrpt_Node2_11147_11148.html    177,362    127,510,344    69,653,323    99.91    99.91
awrrpt_Node2_11148_11149.html    122,136    105,458,188    289,046    99.88    99.88
awrrpt_Node2_11149_11150.html    97,822    97,544,757    247,641    99.9    99.9
awrrpt_Node2_11150_11151.html    60,263    88,334,090    202,901    99.93    99.93
awrrpt_Node2_11151_11152.html    55,919    77,791,297    177,600    99.93    99.93
awrrpt_Node2_11152_11153.html    28,153    69,924,517    191,956    99.96    99.96
awrrpt_Node2_11153_11154.html    33,529    70,628,356    1,235,751    99.95    99.95
awrrpt_Node2_11154_11155.html    15,681    63,324,755    122,490    99.98    99.98
awrrpt_Node2_11155_11156.html    20,723    55,369,179    127,362    99.96    99.96
awrrpt_Node2_11156_11157.html    20,181    60,451,827    107,454    99.97    99.97
awrrpt_Node2_11157_11158.html    290,997    63,362,994    1,256,035    99.55    99.55
awrrpt_Node2_11158_11159.html    3,134,999    59,561,257    65,422,469    97.49    97.49
awrrpt_Node2_11159_11160.html    12,603,133    671,968,974    12,634,978    98.16    98.16
awrrpt_Node2_11160_11161.html    7,986,463    72,291,100    78,403,181    94.7    94.7
awrrpt_Node2_11161_11162.html    165,156    77,928,307    199,674    99.79    99.79
awrrpt_Node2_11162_11163.html    143,307    84,143,818    246,087    99.83    99.83
awrrpt_Node2_11163_11164.html    138,990    94,506,704    272,167    99.85    99.85
awrrpt_Node2_11164_11165.html    137,758    99,008,540    295,274    99.86    99.86

◆3 月份
    physical reads cache    consistent gets from cache    db block gets from cache    Buffer Hit%    计算值
awrrpt_Node2_22213_22214.html    18,833,127    87,719,557    11,547,532    81.03    81.03
awrrpt_Node2_22214_22215.html    19,293,314    84,003,101    777,801    77.24    77.24
awrrpt_Node2_22215_22216.html    19,055,550    97,849,548    901,390    80.7    80.7
awrrpt_Node2_22216_22217.html    19,263,835    88,958,016    787,016    78.53    78.53
awrrpt_Node2_22217_22218.html    19,156,451    85,629,731    817,799    77.84    77.84
awrrpt_Node2_22218_22219.html    19,339,294    90,498,771    11,164,125    80.98    80.98
awrrpt_Node2_22219_22220.html    19,394,785    85,818,970    998,442    77.66    77.66
awrrpt_Node2_22220_22221.html    19,298,334    83,376,226    838,372    77.08    77.08
awrrpt_Node2_22221_22222.html    19,334,326    92,402,004    909,433    79.28    79.28
awrrpt_Node2_22222_22223.html    19,590,098    117,188,196    27,331,324    86.44    86.44
awrrpt_Node2_22223_22224.html    19,756,886    79,694,538    783,825    75.45    75.45
awrrpt_Node2_22224_22225.html    20,142,492    69,985,919    637,065    71.48    71.48
awrrpt_Node2_22225_22226.html    19,284,672    68,688,658    1,062,320    72.35    72.35
awrrpt_Node2_22226_22227.html    18,973,492    78,436,829    602,573    75.99    75.99
awrrpt_Node2_22227_22228.html    19,295,820    83,930,250    754,265    77.21    77.21
awrrpt_Node2_22228_22229.html    19,299,273    95,240,240    710,249    79.89    79.89
awrrpt_Node2_22229_22230.html    19,282,703    86,314,357    765,538    77.86    77.86
awrrpt_Node2_22230_22231.html    19,471,142    92,579,836    675,708    79.12    79.12
awrrpt_Node2_22231_22232.html    19,456,272    87,360,635    922,862    77.96    77.96
awrrpt_Node2_22232_22233.html    19,426,444    78,029,665    775,880    75.35    75.35
awrrpt_Node2_22233_22234.html    19,134,299    107,192,096    689,028    82.26    82.26
awrrpt_Node2_22234_22235.html    19,539,297    88,922,077    603,820    78.17    78.17
awrrpt_Node2_22235_22236.html    19,548,117    74,524,524    10,886,459    77.11    77.11
awrrpt_Node2_22236_22237.html    19,571,235    69,991,952    6,898,460    74.55    74.55
awrrpt_Node2_22237_22238.html    19,535,153    68,002,826    669,415    71.55    71.55
awrrpt_Node2_22238_22239.html    19,743,665    69,208,411    676,517    71.75    71.75
awrrpt_Node2_22239_22240.html    19,315,599    70,517,718    603,436    72.84    72.84
awrrpt_Node2_22240_22241.html    19,702,422    85,042,101    730,447    77.03    77.03
awrrpt_Node2_22241_22242.html    29,386,292    78,899,276    780,642    63.12    63.12
awrrpt_Node2_22242_22243.html    20,363,303    76,394,697    5,406,729    75.11    75.11
awrrpt_Node2_22243_22244.html    20,493,153    85,604,990    75,059,277    87.24    87.24
awrrpt_Node2_22244_22245.html    20,606,911    67,653,183    578,796    69.8    69.8
awrrpt_Node2_22245_22246.html    20,644,327    59,512,101    553,342    65.63    65.63
awrrpt_Node2_22246_22247.html    20,236,428    50,540,686    491,729    60.35    60.35
awrrpt_Node2_22247_22248.html    20,401,296    46,971,705    442,071    56.97    56.97
awrrpt_Node2_22248_22249.html    20,507,154    43,251,247    457,559    53.08    53.08
awrrpt_Node2_22249_22250.html    19,866,674    40,809,028    402,312    51.79    51.79
awrrpt_Node2_22250_22251.html    20,011,908    38,691,219    413,919    48.83    48.83
awrrpt_Node2_22251_22252.html    18,834,446    36,228,742    318,118    48.46    48.46
awrrpt_Node2_22252_22253.html    19,089,954    37,053,290    409,520    49.04    49.04
awrrpt_Node2_22253_22254.html    19,515,077    37,761,037    354,648    48.8    48.8
awrrpt_Node2_22254_22255.html    22,171,394    40,008,660    10,777,153    56.34    56.34
awrrpt_Node2_22255_22256.html    25,519,651    586,686,934    68,644,322    96.11    96.11
awrrpt_Node2_22256_22257.html    35,365,336    119,680,111    11,297,447    73    73
awrrpt_Node2_22257_22258.html    22,012,146    58,094,571    68,177,156    82.57    82.57
awrrpt_Node2_22258_22259.html    21,263,614    65,416,659    575,772    67.78    67.78
awrrpt_Node2_22259_22260.html    21,357,383    68,498,396    821,252    69.19    69.19
awrrpt_Node2_22260_22261.html    21,006,076    74,916,494    16,673,221    77.07    77.07

通过比拟 AWR Report 里获得的”Buffer Hit%“和计算出来的”Buffer Hit%“,咱们发现两个值是相等的,这就证实了咱们后面考察到的计算方法是正确的。

而后应用 Excel 做几个简略的曲线图:

◆Buffer Hit% 曲线图

◆physical reads cache 曲线图

◆consistent gets from cache 曲线图

◆db block gets from cache 曲线图

通过下面的曲线图,能够直观的看到以下两个特点:

1.  Buffer Hit% 和 physical reads cache 的变化趋势相同。2.  consistent gets from cache 和 db block gets from cache 的变化趋势雷同。

再联合 Buffer Hit% 的计算式,就能够得出这个问题的起因是 3 月份的”physical reads cache“减少造成的论断了。

Buffer Hit% = 1 – ((‘physical reads cache’) / (‘consistent gets from cache’ + ‘db block gets from cache’)

那么,还有以下两个问题须要明确:

1.  那些解决(SQL 文)造成了物理读。2.  物理读的对象(Segment)是那些。

这两个问题能够通过 AWR Report 里的”SQL ordered by Reads“和”Segments by Physical Reads“进行确认。

因为这部分数据蕴含客户信息,就不在这里展现了。

最初,再罗嗦一句,剖析 AWR Report 和 ASH 数据一样,都要无意识的先确定考察方向,不能胡子眉毛一把抓,没有重点。

当前有好的例子再和大家分享。

2021/03/26 @ Dalian

退出移动版