溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
本篇內(nèi)容主要講解“怎么理解Oracle響應(yīng)時(shí)間分析報(bào)告”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“怎么理解Oracle響應(yīng)時(shí)間分析報(bào)告”吧!
Oracle響應(yīng)時(shí)間分析報(bào)告分為系統(tǒng)級(jí)與會(huì)話級(jí),報(bào)告相比awr報(bào)告更加直觀清楚有助于快速分析定位性能問(wèn)題，這里使用OSM工具來(lái)生成這兩種類型的報(bào)告，該工具是由Craig Shallahamer所寫(xiě)。
在數(shù)據(jù)庫(kù)中創(chuàng)建osm用戶并安裝osm腳本所需要使用的對(duì)象
[oracle@db1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 27 15:43:54 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> create user osm identified by "osm" default tablespace sx temporary tablespace temp; User created. SQL> grant connect,resource,dba to osm; Grant succeeded. SQL> conn osm/osm Connected. SQL> exec sys.dbms_lock.sleep(5); PL/SQL procedure successfully completed. SQL> @osmprep.sql OraPub System Monitor - Interactive (OSM-I) installation script. (c)1998,1999,2000,2001,2002,2003,2004,2005,2006,2007,2008 by OraPub, Inc. (c)2009,2010,2011,2012,2013,2014,2015 by OraPub, Inc. There is absolutely no guarantee with this software. You may use this software at your own risk, not OraPub's risk. No value is implied or stated. You may need to run $ORACLE_HOME/rdbms/admin/catblock.sql Connect as the user who will be using the OSM. Press ENTER to continue. ..... To categorize wait events for OSM reports, run: For pre-10g systems, run event_type_nc.sql For 10g and beyond, run event_type.sql Once you cateogrize the wait events, the installation is complete. Menu is osm.sql ENJOY!! SQL> @event_type.sql file: event_type.sql for Oracle 10g and beyond... About to categorize wait events for OSM reports. Press ENTER to re-create the o$event_type table. ..... OraPub Categorization Summary ----------------------------------------------- TYPE COUNT(*) -------------------- ---------- bogus 126 ior 20 iow 59 other 1162 COUNT(*) ---------- 1367 Oracle Categorization Summary ----------------------------------------------- WAIT_CLASS COUNT(*) -------------------- ---------- Administrative 55 Application 17 Cluster 50 Commit 2 Concurrency 33 Configuration 24 Idle 96 Network 35 Other 958 Queueing 9 Scheduler 8 WAIT_CLASS COUNT(*) -------------------- ---------- System I/O 32 User I/O 48 13 rows selected. COUNT(*) ---------- 1367
OSM工具包中的rtsess.sql是用來(lái)生成會(huì)話級(jí)報(bào)告，rtsysx.sql,rtpctx.sql用來(lái)生成實(shí)例級(jí)報(bào)告
實(shí)例級(jí)Oracle響應(yīng)時(shí)間分析報(bào)告是基于rtsysx.sql腳本，它將捕獲指定時(shí)間間隔內(nèi)實(shí)例范圍內(nèi)關(guān)于響應(yīng)時(shí)間方面的詳細(xì)信息。這個(gè)腳本將對(duì)實(shí)例級(jí)統(tǒng)計(jì)信息(v$sysstat,v$sys_time_model)與實(shí)例級(jí)等待事件統(tǒng)計(jì)信息(v$system_event)生成快照。下面的例子在120秒的時(shí)間間隔內(nèi)，腳本每10秒被喚醒一次，從v$session視圖中查詢活動(dòng)的SQL并存儲(chǔ)當(dāng)前正在運(yùn)行的SQL_ID。在報(bào)告生命周期結(jié)束后，其它的統(tǒng)計(jì)住處快照會(huì)生成，計(jì)算出時(shí)間差異并生成報(bào)告。幾乎報(bào)告中的所有信息都可以從Statspack或AWR報(bào)告中進(jìn)行收集。使用rtsysx.sql腳可以生成格式化的輸出可以快速的執(zhí)行Oracle響應(yīng)時(shí)間分析。使用腳本rtsysx.sql腳本生成的報(bào)告包括以下幾個(gè)組成部分:
.第一部分是關(guān)注工作量負(fù)載情況
.第二部分是高級(jí)別的響應(yīng)時(shí)間分類信息
.第三部分是IO與非IO情況
.第四部分是沒(méi)有使用綁定變量的SQL語(yǔ)句
.對(duì)于Oracle 10g及以上版本，第五部分是關(guān)于操作系統(tǒng)CPU利用率
下面執(zhí)行rtsysx.sql腳本，執(zhí)行生命周期是120秒，腳本每10秒被喚醒一次。
SQL> @rtsysx.sql 120 10 OraPub's Response Time Analysis (RTA) interactive system level delta report Initializing response time delta objects... Sleeping and probing active SQL for next 120 seconds... Done sleeping...gathering and storing current values... *** Response Time Ratio and Workload Metrics RT Ratio Ora Trx/s Block Changes/s User Calls/s Execs/s -------- ---------- --------------- ------------ ------------ 0.325 0.54 90.63 27.41 100.00 *** Response Time System Summary (delta - interactive - system level) Tot CPU CPU SP CPU BG CPU Parse CPU Recur Tot Wait IO Wait Other Wait Time Time Time Time Time Ora CPU Time Time Time % % (sec) (sec) (sec) (sec) (sec) Util % (sec) (sec) (sec) IO Wait Other Wait ---------- -------- -------- --------- --------- ------- -------- -------- ---------- ------- ---------- 34 26 8 2 15 0.4 17 15 1 92 8 *** I/O Wait Time Summary w/Event Details (delta - interactive - system level) IO Wait IO WRITE IO READ Time Wait Time Wait Time % IO % IO (sec) (sec) (sec) Write Read -------- --------- ---------- ----- ---- 15 10 5 66 34 Tot Call Avg Call Wait Time Wait Time IO Wait Event R,W % (sec) (ms) Tot Waits ------------------------------------------------------------ --- ----- ----------- ----------- --------- LGWR real time apply sync W 57 8.69 65.83 132 db file sequential read R 31 4.67 4.97 940 LGWR-LNS wait on channel W 5 0.71 1.74 408 *** Other Wait Time (non-I/O) Event Detail (delta - interactive - system level) Tot Call Avg Call Wait Time Wait Time Non IO (other) Wait Event % (sec) (ms) Tot Waits ------------------------------------------------------------ ----- ----------- ----------- --------- gc current block 2-way 26 0.35 0.62 569 gc cr grant 2-way 17 0.23 0.48 484 reliable message 10 0.14 1.21 116 os thread startup 10 0.14 23.33 6 enq: US - contention 6 0.08 0.49 163 library cache pin 6 0.08 0.56 144 library cache lock 4 0.06 0.61 98 gc current grant 2-way 4 0.05 0.45 111 *** SQL Activity Details During Probe Phys Rds Log Rds Tot Time CPU Time Rows Stmt SQL ID Sec/EXE (k) (k) (sec) (sec) Sec/PIO Sec/LIO Runs (k) Sorts Type ---------------- --------- -------- -------- --------- ---------- ------- ------- -------- ------- ------ ----- gz5bfrcjq060u 0.01 0 0 0.3 0.3 ####### 0.001 24 0 23 INSER c77k33u5u7zgc 0.06 0 17 0.1 0.1 ####### 0.000 2 0 2 SELEC 8fb44rrg8a5rh 0.13 0 15 0.1 0.1 ####### 0.000 1 0 2 SELEC 98564h4vavfcm -25.78 -0 -0 -51.6 -0.6 51.552 0.276 2 0 0 inser *** SQL Similar Statements During Delta SQL Statement (shown if first 10 chars) Count ---------------------------------------------------------------------- -------- SELECT NVL 2 *** OS CPU Breakdown During Delta Category Percent ----------------------------------- -------- Idle 96.51 IO Wait 0.44 Nice 0.00 System 0.38 User 2.47 Delta is 123.53 seconds Number of CPU cores is 80
報(bào)告的第一部分:Response Time Ratio and Workload Metrics
報(bào)告的第一部分提供了與Statspack與AWR中Load Profile部分相同的Workload Metrics。這部分信息在比較響應(yīng)時(shí)間快照之間的差異時(shí)非常有用。如果工作負(fù)載減少那么可以預(yù)期響應(yīng)時(shí)間減少。
*** Response Time Ratio and Workload Metrics RT Ratio Ora Trx/s Block Changes/s User Calls/s Execs/s -------- ---------- --------------- ------------ ------------ 0.325 0.54 90.63 27.41 100.00
報(bào)告的第二部分:Response Time System Summary
這部分信息顯示總的CPU時(shí)間為34秒，總的等待時(shí)間為17秒，也可以說(shuō)是在120秒的時(shí)間間隔內(nèi)，Oracle進(jìn)程消耗的CPU時(shí)間只有34秒，消耗的等待時(shí)間只有17秒。而且還可以看到17秒的等待時(shí)間中IO等待時(shí)間為15秒，非IO等待時(shí)間為1秒。在120秒的時(shí)間間隔內(nèi)，Oracle進(jìn)程只消耗了總CPU可用時(shí)間的0.4%，這個(gè)數(shù)據(jù)是使用Oracle進(jìn)程消耗的總CPU時(shí)間除以主機(jī)可用CPU時(shí)間。在120秒時(shí)間間隔的主機(jī)的CPU可用時(shí)間為CPU的內(nèi)核數(shù)量乘以報(bào)告時(shí)間間隔。在這里主機(jī)的CPU內(nèi)核數(shù)量為80，報(bào)告時(shí)間間隔為120秒，所以O(shè)racle所消耗的CPU時(shí)間為34/(120*80)=0.4%。如果主機(jī)上只運(yùn)行該實(shí)例，那么它也提供了操作系統(tǒng)CPU利用率給我們，因此也不用執(zhí)行操作系統(tǒng)命令來(lái)查看CPU利用情況了。
*** Response Time System Summary (delta - interactive - system level) Tot CPU CPU SP CPU BG CPU Parse CPU Recur Tot Wait IO Wait Other Wait Time Time Time Time Time Ora CPU Time Time Time % % (sec) (sec) (sec) (sec) (sec) Util % (sec) (sec) (sec) IO Wait Other Wait ---------- -------- -------- --------- --------- ------- -------- -------- ---------- ------- ---------- 34 26 8 2 15 0.4 17 15 1 92 8
報(bào)告的第三部分:I/O Wait Time Summary w/Event Details
如果IO有問(wèn)題，那么你肯定想知道是讀還是寫(xiě)有問(wèn)題，管理員可以從了解IO負(fù)載類型來(lái)給出相關(guān)的解決方案。比如一個(gè)IO讀問(wèn)題可以通過(guò)將常被訪問(wèn)的數(shù)據(jù)塊保存在Oracle Cache中來(lái)使用IO讀的影響降低到最小，如果一個(gè)IO寫(xiě)問(wèn)題可以通過(guò)配置，比如聯(lián)機(jī)重做日志文件的數(shù)量與大小來(lái)使IO寫(xiě)的影響降低到最小。從報(bào)告中可以看到IO總等待時(shí)間為15秒，其中IO寫(xiě)為10秒,IO讀為5秒。其中LGWR real time apply sync事件平均等待一次的時(shí)間是65.83毫秒，這是因?yàn)榕渲昧薃DG，對(duì)于同城異地容災(zāi)來(lái)說(shuō)這個(gè)等待時(shí)間也還是正常的，db file sequential read事件平均等待一次的時(shí)間為4.97毫秒也是正常的。
*** I/O Wait Time Summary w/Event Details (delta - interactive - system level) IO Wait IO WRITE IO READ Time Wait Time Wait Time % IO % IO (sec) (sec) (sec) Write Read -------- --------- ---------- ----- ---- 15 10 5 66 34 Tot Call Avg Call Wait Time Wait Time IO Wait Event R,W % (sec) (ms) Tot Waits ------------------------------------------------------------ --- ----- ----------- ----------- --------- LGWR real time apply sync W 57 8.69 65.83 132 db file sequential read R 31 4.67 4.97 940 LGWR-LNS wait on channel W 5 0.71 1.74 408
報(bào)告的第四部分:Other Wait Time (non-I/O) Event Detail
這部分顯示了非IO等待事件的匯總與底層相關(guān)的等待事件詳細(xì)信息，因?yàn)榉荌O等待時(shí)間總共才只有1秒，這并不影響性能。所以相關(guān)的等待事件我們也就不用查看了。
*** Other Wait Time (non-I/O) Event Detail (delta - interactive - system level) Tot Call Avg Call Wait Time Wait Time Non IO (other) Wait Event % (sec) (ms) Tot Waits ------------------------------------------------------------ ----- ----------- ----------- --------- gc current block 2-way 26 0.35 0.62 569 gc cr grant 2-way 17 0.23 0.48 484 reliable message 10 0.14 1.21 116 os thread startup 10 0.14 23.33 6 enq: US - contention 6 0.08 0.49 163 library cache pin 6 0.08 0.56 144 library cache lock 4 0.06 0.61 98 gc current grant 2-way 4 0.05 0.45 111
報(bào)告的第五部分:SQL Activity Details During Probe
為了幫助分析應(yīng)用程序，報(bào)告捕獲了直接影響響應(yīng)時(shí)間的SQL語(yǔ)句并顯示了資源消耗情況，以下面的數(shù)據(jù)來(lái)看，在捕獲的SQL語(yǔ)句所消耗的資源都是很少的不會(huì)影響性能，其中語(yǔ)句的物理讀為0，邏輯讀總大小也才32K。
*** SQL Activity Details During Probe Phys Rds Log Rds Tot Time CPU Time Rows Stmt SQL ID Sec/EXE (k) (k) (sec) (sec) Sec/PIO Sec/LIO Runs (k) Sorts Type ---------------- --------- -------- -------- --------- ---------- ------- ------- -------- ------- ------ ----- gz5bfrcjq060u 0.01 0 0 0.3 0.3 ####### 0.001 24 0 23 INSER c77k33u5u7zgc 0.06 0 17 0.1 0.1 ####### 0.000 2 0 2 SELEC 8fb44rrg8a5rh 0.13 0 15 0.1 0.1 ####### 0.000 1 0 2 SELEC 98564h4vavfcm -25.78 -0 -0 -51.6 -0.6 51.552 0.276 2 0 0 inser
報(bào)告的第六部分:SQL Similar Statements During Delta
在執(zhí)行rtsysx.sql腳本所指定的第二個(gè)參數(shù)就與查找類似SQL語(yǔ)句相關(guān)，類似SQL語(yǔ)句是除了where子句中的過(guò)濾與連接條件不同之外其它完全相同的語(yǔ)句。第二個(gè)參數(shù)我們指定的是10，也就是說(shuō)類似語(yǔ)句會(huì)被統(tǒng)計(jì)且統(tǒng)計(jì)數(shù)大于1的語(yǔ)句的前10個(gè)字符才會(huì)被顯示。
*** SQL Similar Statements During Delta SQL Statement (shown if first 10 chars) Count ---------------------------------------------------------------------- -------- SELECT NVL 2
報(bào)告的第七部分:Operating System CPU Utilization
這部分顯示了操作系統(tǒng)使用的詳細(xì)情況。從Oracle 10g開(kāi)始，Oracle捕獲操作系統(tǒng)CPU的使用的詳細(xì)信息并且這些信息可以通過(guò)v$osstat視圖來(lái)查看。
*** OS CPU Breakdown During Delta Category Percent ----------------------------------- -------- Idle 96.51 IO Wait 0.44 Nice 0.00 System 0.38 User 2.47 Delta is 123.53 seconds Number of CPU cores is 80
會(huì)話級(jí)Oracle響應(yīng)時(shí)間分析報(bào)告
執(zhí)行腳本rtsess9.sql來(lái)對(duì)指定會(huì)話1110來(lái)生成會(huì)話級(jí)Oracle響應(yīng)時(shí)間分析報(bào)告，從下面的報(bào)告中可以看到會(huì)話的響應(yīng)時(shí)間為699.29秒，其中隊(duì)列時(shí)間為608.20秒，非計(jì)數(shù)時(shí)間為91.09秒，而隊(duì)列時(shí)間中IO隊(duì)列時(shí)間只有0.3秒，Net+Client隊(duì)列時(shí)間占了607.73秒。這說(shuō)明會(huì)話一直在等待客戶端程序進(jìn)行調(diào)用。
SQL> @rtsess9 1110 =================================================================== Session Level Response Time Profile Oracle session 1110 CPU statistics number is 12 ...... Session level response time details for SID 1110 *** Response Time Summary Response Service Queue Unaccount % CPU % Queue % UAT Time(sec) Time(sec) Time(sec) Time(sec) RT RT RT [rt=st+qt+uat] [st] [qt] [uat] [st/rt] [qt/rt] [uat/rt] -------------- --------- --------- --------- ------- ------- -------- 699.29 0.00 608.20 91.09 0.00 86.97 13.03 *** Queue Time Summary QT QT QT Queue Time(sec) I/O(sec) Net+Client(sec) Other(sec) [qio+qnc+qot] [qio] [qnc] [qot] --------------- -------- --------------- ---------- 608.20 0.03 607.73 0.44 *** Queue Time IO Timing Detail QT QT QT I/O(sec) Write I/O(sec) Read I/O(sec) % Writes Time % Read Time [tio=wio+rio] [wio] [rio] [wio/tio] [rio/tio] ------------- -------------- ------------- ------------- ----------- 0.03 0.03 0.00 99.97 0.00 *** Queue Time IO Event Timing Detail Wait Time Wait Event Name (sec) ---------------------------------------- --------- direct path write 0.01 log file sync 0.02 *** Queue Time Other Event Timing Detail Wait Time Wait Event Name (sec) ---------------------------------------- --------- gc cr block 2-way 0.08 library cache pin 0.01 gc current block congested 0.01 gc current block 2-way 0.31 row cache lock 0.01 events in waitclass Other 0.01 library cache lock 0.01 *** Wait Event Time Not Categorized (for QA) ......
如果應(yīng)用程序用戶與Oracle服務(wù)器進(jìn)程都在等待這是不正常的。如果用戶已經(jīng)執(zhí)行了命令并且正等待命令執(zhí)行結(jié)束，同時(shí)，相關(guān)的Oracle服務(wù)器進(jìn)程正等待從客戶端進(jìn)程接收信息，那么在這兩者之間存在問(wèn)題。那么大概問(wèn)題區(qū)域就是網(wǎng)絡(luò)與客戶端進(jìn)程了。
到此，相信大家對(duì)“怎么理解Oracle響應(yīng)時(shí)間分析報(bào)告”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
