Oracle 10g數據庫中如何分析響應時間

在Oracle10g中，以前版本中比較難于獲取的響應時間數據將會變得非常輕易獲取。 在以前看來，為了盡量獲得數據庫的最佳性能，Oracle的DBA們和性能分析專家一直很困難獲得系統以及用戶會話活動的一致的響應時間數據。DBA們面臨的問題一直以來包括兩個方面：第一個方面是準確定位數據庫或者用戶會話究竟在哪里消耗了時間；第二個方面就是確定用戶體驗的客觀性質。 在數據庫中產生所有可能的行為和交互作用，這些任務都不是沒有價值的。Oracle等待接口，在之前的很早的Oracle數據庫版本中開始介紹的，對于那些知道如何使用等待接口的治理員來說這已經成為一個偉大的開始，即使它仍然缺乏告訴DBA系統或者用戶會話是否有效的處理了事務或者查詢這個理想的能力。啟用和鉆研跟蹤文件能夠存儲這個級別上的具體信息，但是對于大多數超負荷工作治理大型數據庫的DBA們，這個鉆研是奢侈的而耗費時間的。 幸運的是，那些將數據庫升級到Oracle10g的DBA們將會發現找到主要的響應時間變得很輕易，可以答應一個非常好的圖表來顯示系統和會話級的響應時間數據。很重要的一點，Oracle的ADDM提供了一個查看響應時間的方法，通過自動分析收集的統計信息，識別問題區域，甚至可以通過Oracle企業治理器網絡控制的圖形界面提供建議。 此外，與我們這里討論相關的是Oracle10g數據庫的歷史數據機制答應DBA們按時查看對響應時間趨勢的分析，這將有助于DBA們確定事務/系統的高峰時期，更好的定位那些拉長批處理周期和ETL作業的進程和SQL語句。 這里主要討論用于系統、會話和SQL級別上那些歷史機制的用途。 系統層的響應時間分析： 先來看看典型的幾個經常問到DBA們的問題： 通常來說，數據庫運行的狀況如何？ 用戶體驗感覺的平均響應時間是多少？ 什么行為是最影響整個響應時間的？ 上述問題在Oracle10g數據庫之前對于DBA們來說是相當不好回答的，但是假如使用了最新的Oracle10g數據庫之后，這些數據信息將會很輕易的被捕捉到。 首先，Oracle10g數據庫運行的狀況如何這個問題可以通過下面的查詢來獲得： select METRIC_NAME，VALUE from SYS.V_$SYSMETRIC where METRIC_NAME IN （'Database CPU Time Ratio'，'Database Wait Time Ratio'） AND INTSIZE_CSEC = （select max（INTSIZE_CSEC） from SYS.V_$SYSMETRIC）； METRIC_NAME VALUE Database Wait Time Ratio 31.3499111 Database CPU Time Ratio 68.6500888Oracle10g數據庫中的V$SYSMETRIC視圖中存在一些非常有用的響應時間數據，其中兩個比較重要的就是Wait Time Ratio 和Database CPU Time Ratio.上面的查詢顯示了數據庫中最新的關于這兩個統計數據的快照，這將有助于幫助我們確定是否數據庫正在經歷著一個比較高的等待百分率和瓶頸。數據庫的CPU Time Ratio是由數據庫中的'database time'的數值除以CPU的數量，'database time'定義為數據庫消耗在用戶級別調用所花費的時間（不包括實例的后臺進程活動所消耗的時間）。比較高的值（90%-95%以上）代表很少等待和瓶頸活動，因為各個系統不同，這個閥值只能作為一個一般的規則來使用。 還可以使用如下的查詢來迅速查看最新一個小時的信息，看看數據庫的總性能如何： select end_time，value from sys.v_$sysmetric_history where metric_name = 'Database CPU Time Ratio' order by 1； END_TIME VALUE 2007-1-24 2 3.21949216 2007-1-24 2 3.01443414 2007-1-24 2 9.75636353 2007-1-24 2 9.28581409 2007-1-24 2 43.3490481 2007-1-24 2 38.8366361 2007-1-24 2 32.0272511 2007-1-24 2 0 2007-1-24 2 22.9580733 2007-1-24 2 33.0615102 2007-1-24 2 43.1294933可以從V$SYSMETRIC_SUMMARY視圖中獲得數據庫整體性能效率的最大、最小和平均值： select CASE METRIC_NAME WHEN 'SQL Service Response Time' then 'SQL Service Response Time （secs）' WHEN 'Response Time Per Txn' then 'Response Time Per Txn （secs）' ELSE METRIC_NAME END METRIC_NAME， CASE METRIC_NAME WHEN 'SQL Service Response Time' then ROUND（（MINVAL / 100），2） WHEN 'Response Time Per Txn' then ROUND（（MINVAL / 100），2） ELSE MINVAL END MININUM， CASE METRIC_NAME WHEN 'SQL Service Response Time' then ROUND（（MAXVAL / 100），2） WHEN 'Response Time Per Txn' then ROUND（（MAXVAL / 100），2） ELSE MAXVAL END MAXIMUM， CASE METRIC_NAME WHEN 'SQL Service Response Time' then ROUND（（AVERAGE / 100），2） WHEN 'Response Time Per Txn' then ROUND（（AVERAGE / 100），2） ELSE AVERAGE END AVERAGE from SYS.V_$SYSMETRIC_SUMMARY where METRIC_NAME in （'CPU Usage Per Sec'， 'CPU Usage Per Txn'， 'Database CPU Time Ratio'， 'Database Wait Time Ratio'， 'Executions Per Sec'， 'Executions Per Txn'， 'Response Time Per Txn'， 'SQL Service Response Time'， 'User Transaction Per Sec'） ORDER BY 1； METRIC_NAME MININUM MAXIMUM AVERAGE CPU Usage Per Sec 0 53.9947577 11.1603280 CPU Usage Per Txn 0 168.731666 24.8848615 Database CPU Time Ratio 0 87.1866295 35.8114730 Database Wait Time Ratio 0 90.7141859 64.1885269 Executions Per Sec 0 540.768348 114.852472 Executions Per Txn 0 1911 279.912779 Response Time Per Txn （secs） 0 3.88 0.66 SQL Service Response Time （secs） 0 0 0 User Transaction Per Sec 0 4.70183486 0.94469007上面的查詢包含了更多的具體的響應時間數據。DBA們還需要收集在系統級別上的用戶通訊的平均響應時間，上面的查詢給出了需要的結果。假如用戶抱怨響應時間太慢，那么DBA就應該查看Response Time Per Txn和SQL Service Response Time數據是否存在數據庫問題。 假如響應時間不在是那么渴求，那么DBA就會想了解究竟是什么類型的用戶活動讓數據庫的響應變得如此的慢，在Oracle10g數據庫之前，這些信息 是比較難獲取的，但是現在就變得非常輕易，執行如下查詢： select case db_stat_name when 'parse time elapsed' then 'soft parse time' else db_stat_name end db_stat_name， case db_stat_name when 'sql execute elapsed time' then time_secs - plsql_time when 'parse time elapsed' then time_secs - hard_parse_time else time_secs end time_secs， case db_stat_name when 'sql execute elapsed time' then round（100 * （time_secs - plsql_time） / db_time，2） when 'parse time elapsed' then round（100 * （time_secs - hard_parse_time） / db_time，2） else round（100 * time_secs / db_time，2） end pct_time from （select stat_name db_stat_name， round（（value / 1000000），3） time_secs from sys.v_$sys_time_model where stat_name not in（'DB time'，'background elapsed time'， 'background cpu time'，'DB CPU'））， （select round（（value / 1000000），3） db_time from sys.v_$sys_time_model where stat_name = 'DB time'）， （select round（（value / 1000000），3） plsql_time from sys.v_$sys_time_model where stat_name = 'PL/SQL execution elapsed time'）， （select round（（value / 1000000），3） hard_parse_time from sys.v_$sys_time_model where stat_name = 'hard parse elapsed time'） order by 2 desc； DB_STAT_NAME TIME_SECS PCT_TIME sql execute elapsed time 65.644 89.7 hard parse elapsed time 26.661 36.43 PL/SQL execution elapsed time 12.766 17.44 PL/SQL compilation elapsed time 6.353 8.68 soft parse time 2.15 2.94 connection management call elapsed time 1.084 1.48 hard parse （sharing criteria） elapsed time 0.448 0.61 repeated bind elapsed time 0.026 0.04 failed parse elapsed time 0.009 0.01 hard parse （bind mismatch） elapsed time 0.002 0 RMAN cpu time （backup/restore） 0 0 inbound PL/SQL rpc elapsed time 0 0 sequence load elapsed time 0 0 Java execution elapsed time 0 0 failed parse （out of shared memory） elapsed time 0 0可以在V$SYS_TIME_MODEL視圖中找到相應的主要花費時間處理的部分，然后就可以根據這些來對數據庫進行相應的調整。 除了活動時間，DBA也還想知道整體的等待時間。在Oracle10g數據庫之前，DBA必須查看單獨的等待事件來找出等待和瓶頸，現在Oracle10g數據庫提供一個等待的概要機制。 select WAIT_CLASS， TOTAL_WAITS， round（100 * （TOTAL_WAITS / SUM_WAITS），2） PCT_WAITS， ROUND（（TIME_WAITED / 100），2） TIME_WAITED_SECS， round（100 * （TIME_WAITED / SUM_TIME），2） PCT_TIME from （select WAIT_CLASS， TOTAL_WAITS， TIME_WAITED from V$SYSTEM_WAIT_CLASS where WAIT_CLASS ！= 'Idle'）， （select sum（TOTAL_WAITS） SUM_WAITS， sum（TIME_WAITED） SUM_TIME from V$SYSTEM_WAIT_CLASS where WAIT_CLASS ！= 'Idle'） order by 5 desc； WAIT_CLASS TOTAL_WAITS PCT_WAITS TIME_WAITED_SECS PCT_TIME User I/O 5748 61.71 67.57 65.79 Other 182 1.95 16.85 16.41 System I/O 2975 31.94 11.27 10.97 Concurrency 114 1.22 6.76 6.58 Commit 61 0.65 0.22 0.21 Network 233 2.5 0.03 0.03 Application 2 0.02 0 0這樣就能非常輕易的找出大部分的整體等待時間。如同響應時間數據一樣，我們可以用下面的查詢來及時回顧最新的一個小時等待類型： select a.sid， b.username， a.wait_class， a.total_waits， round（（a.time_waited / 100），2） time_waited_secs from sys.v_$session_wait_class a， sys.v_$session b where b.sid = a.sid and b.username is not null and a.wait_class ！= 'Idle' order by 5 desc； SID USERNAME WAIT_CLASS TOTAL_WAITS TIME_WAITED_SECS 38 SYS User I/O 22 0.19 48 SYS User I/O 15 0.12 38 SYS Network 21 0.01 48 SYS Network 24 0 38 SYS Application 2 0這個時候，就可以檢查標準的單獨等待事件就如在以前版本的Oracle數據庫中查詢V$SESSION_WAIT和V$SESSION_EVENT視圖。在Oracle10g數據庫中DBA還將可以找出新的等待類型在這兩張視圖中。假如需要找出以前哪個會話登錄并且消耗了大部分的資源，你可以使用下面的查詢，下面的例子是查找午夜12點到5點的數據庫活動，并且包括用戶的I/O等待。 select sess_id， username， program， wait_event， sess_time， round（100 * （sess_time / total_time），2） pct_time_waited from （select a.session_id sess_id， decode（session_type，'background'，session_type，c.username） username， a.program program， b.name wait_event， sum（a.time_waited） sess_time from sys.v_$active_session_history a， sys.v_$event_name b， sys.dba_users c where a.event# = b.event# and a.user_id = c.user_id and sample_time > '22-JAN-07 12：00：00 AM' and sample_time < '22-JAN-07 05：00：00 AM' and b.wait_class = 'User I/O' group by a.session_id， decode（session_type，'background'，session_type，c.username）， a.program， b.name），SQL語句響應時間分析 在Oracle9i數據庫中查看SQL語句的響應時間就變得比較輕易了，現在在Oracle10g中，DBA們擁有更多的工具可以幫助他們跟蹤效率低下的數據庫代碼。以前可以用來查詢的視圖是V$SQLAREA，從Oracle9i開始，這個視圖增加了ELAPSED_TIME和CPU_TIME兩個列，這極大的有助于去確定實際用戶的SQL語句的執行經歷。（假如除以執行的次數列EXECUTIONS，那么將得到平均每次執行這個SQL語句所用的平均時間）在Oracle10g數據庫中，V$SQLAREA視圖中增加了6個新的和等待以及時間相關的列： APPLICATION_WAIT_TIME CONCURRENCY_WAIT_TIME CLUSTER_WAIT_TIME USER_IO_WAIT_TIME PLSQL_EXEC_TIME JAVA_EXEC_TIME這些新的列有助于確定很多信息，例如：一個存儲過程中花費在PL/SQL代碼和標準SQL執行上的時間的對比，以及一個SQL語句經歷的任何具體的用戶I/O等待。例如：下面的SQL語句能幫助找到前5位用戶I/O等待最高的SQL語句： select * from （select sql_text， sql_id， elapsed_time， cpu_time， user_io_wait_time from sys.v_$sqlarea order by 5 desc） where rownum < 6； SQL_TEXT SQL_ID ELAPSED_TIME CPU_TIME USER_IO_WAIT_TIME DECLARE job BINARY_INTEGER ：= ：job； next_date DATE ：= ：mydate； broken BOOLEAN ： 6gvch1xu9ca3g 11077912 747091 8593479 select /*+ index（idl_ub1$ i_idl_ub11） +*/ piece#， length，piece from idl_ub1 $ wher cvn54b7yz0s8u 6455976 220128 6427409 select s.synonym_name object_name， o.object_type from sys.all_synonyms s， s fqmpmkfr6pqyk 11814078 6958760 3189450 select /*+ rule */ bUCket， endpoint， col#， epvalue from histgrm$ where obj#=：1 a db78fxqxwxt7r 2737680 193937 2689611 select /*+ index（idl_ub2$ i_idl_ub21） +*/ piece#， length，piece from idl_ub2$ where 39m4sx9k63ba2 2322664 108100 2307700當然，獲取最消耗時間或者等待時間最長的SQL語句非常不錯，但是同時也需要抓住其要點——在V$ACTIVE_SESSION_HISTORY視圖中又一次出現的SQL語句。通過這個視圖，能夠找出具體什么等待時間延遲了SQL語句執行，連同實際的文件，對象以及阻塞的對象導致等待。 例如：設想已經找到一個非凡的SQL語句，看上去在用戶I/O等待時間方面極端的嚴重，那么可以執行下面的查詢來得到等待時間中各個單獨的等待事件，等待的文件，等待的對象： select event， time_waited， owner， object_name， current_file#， current_block# from sys.v_$active_session_history a， sys.dba_objects b where sql_id = '6gvch1xu9ca3g' and a.current_obj# = b.object_id and time_waited <> 0； EVENT TIME_WAITED OWNER OBJECT_NAME file block db file sequential read 27665 SYSMAN MGMT_METRICS_1HOUR_PK 3 29438 db file sequential read 3985 SYSMAN SEVERITY_PRIMARY_KEY 3 52877當然，也可以通過使用V$ACTIVE_SESSION_HISTORY視圖中的歷史數據的方式來限制一段非凡時間內的沒有優化的SQL語句。問題在于Oracle10g數據庫通過簡化的數據字典視圖把SQL語句的響應時間分析變得非常的簡單，比起以前運用消耗時間的trace方法來說。 總結 DBA們和性能分析專家們治理Oracle10g數據庫的性能時會發現在最新的Oracle旗艦數據庫中已經把許多的響應時間數據做成了動態性能視圖。這些統計信息將有助于迅速找出大型復雜數據庫中的性能瓶頸所在。