使用Oracle的擴(kuò)展SQL跟蹤數(shù)據(jù)的方法
2024-08-29 13:42:11
供稿：網(wǎng)友
使用擴(kuò)展SQL跟蹤數(shù)據(jù)來了解是什么在耗費(fèi)這么長的時(shí)間。
假如有一天你開車去上班，但最后還是沒能及時(shí)參加一個(gè)重要會議。你無法將你的革命性的想法呈現(xiàn)給客戶，所以他們也不會采用。你的拖拖拉拉使你感到沮喪，你發(fā)誓決不再犯同樣的錯(cuò)誤。那么，為了不再發(fā)生類似情況，你怎么判定問題的原因呢？按照下面這個(gè)列表進(jìn)行檢查怎么樣？
檢查汽車外表是否有缺陷，因?yàn)橥獗碛腥毕輹蛊嚨淖罡咚俣冉档?%或更多。
檢查車輪定位，因?yàn)橥鈨A角、后傾角或前束角不合適都會導(dǎo)致汽車的操縱不靈活并且耗費(fèi)時(shí)間。
檢查發(fā)動機(jī)，以確保達(dá)到額定馬力的99%或更高。假如不是這樣，則要考慮重裝或更換發(fā)動機(jī)。
不，你可能不會采用這種檢查方法；那樣太可笑了。你可能會以完全不同的方式來判定問題之所在，可能只是問你自己一個(gè)簡單的問題：什么事情讓我花了這么長時(shí)間？
從這個(gè)角度出發(fā)，問題就迎刃而解了。假如開車需要40分鐘，而你在會議開始前20分鐘才動身，那么下次就要提前30分鐘動身。假如因?yàn)榻煌〒矶吕速M(fèi)了20分鐘，那么，下次要么再早一些動身，換條路線，要么更仔細(xì)地查看早7點(diǎn)的路況報(bào)告。假如是你迷了路，結(jié)果浪費(fèi)了20分鐘去兜圈子，那么下次你大概就要事先看看地圖。如此等等。
我感到希奇的是，那些擅長解決日常性能優(yōu)化問題的數(shù)據(jù)庫專業(yè)人員在工作中卻使用完全不同的方法來解決數(shù)據(jù)庫性能問題。許多數(shù)據(jù)庫"調(diào)優(yōu)人員"從來不問，"是什么讓這個(gè)程序運(yùn)行了這么長時(shí)間？"相反，他們會參考檢查內(nèi)容清單，并試圖阻止錯(cuò)誤發(fā)生：
檢查所有Oracle塊請求是否都由數(shù)據(jù)庫緩存提供服務(wù)
檢查是否有全表掃描
檢查所有排序是否都在內(nèi)存中進(jìn)行
檢查重做日志是否與其他所有數(shù)據(jù)庫文件進(jìn)行了適當(dāng)?shù)母綦x
等等。
對于某些工作來說，使用檢查內(nèi)容清單也許很好。但是對于判定性能問題這樣的工作，試圖確定理論上可能會出錯(cuò)的每一件事，從而對這個(gè)問題進(jìn)行處理的做法的效率會很低。更有效的方法就是找到這個(gè)簡單問題的答案：
是什么花了這么長時(shí)間？
用于優(yōu)化Oracle程序的好的策略就如同日常生活中用到的策略。就像這樣：
1. 使用專門的儀器來測定程序的性能，從而監(jiān)視運(yùn)行速度慢的程序。
2. 為運(yùn)行慢的程序創(chuàng)建資源描述，把程序的響應(yīng)時(shí)間細(xì)分為幾種有用的類型。
3. 通過首先處理響應(yīng)時(shí)間最長的部分來縮短程序的響應(yīng)時(shí)間。
當(dāng)你了解了若干技術(shù)細(xì)節(jié)之后，這個(gè)方法就非常簡單了。假如你真的這樣做，那么每次你都能獲得一個(gè)有用的方法，久而久之，你將能在進(jìn)行性能改進(jìn)之前預(yù)知其結(jié)果。
跟蹤
假如你有用于收集程序中每個(gè)執(zhí)行步驟的時(shí)間統(tǒng)計(jì)信息的高級工具，那就用吧。但只收集匯總數(shù)據(jù)（如通過對系統(tǒng)全局區(qū)[SGA]或其基礎(chǔ)共享存儲段采樣獲得的數(shù)據(jù)）的工具對于某些類型的問題就不適合。
使用昂貴的監(jiān)控工具時(shí)最常見的匯總錯(cuò)誤是它們會跨整個(gè)Oracle數(shù)據(jù)庫實(shí)例來匯總某一給定時(shí)間間隔內(nèi)資源的使用情況。但是，運(yùn)行速度慢的程序?qū)嶋H上可能不受資源爭用問題的影響，而這個(gè)問題卻完全控制著系統(tǒng)中一些不太重要的程序的性能。
即便是那些在Oracle數(shù)據(jù)庫會話級上匯總信息的工具在診斷一些重要的問題類型時(shí)也存在著缺陷。例如，假設(shè)一個(gè)程序運(yùn)行10分鐘，調(diào)用了10000次Oracle SQL*Net message from client 這一"等待事件"，會話等待該事件的總用時(shí)為8.3分鐘。這意味著會話對SQL*Net message from client事件的等待時(shí)間平均為3秒。但是單從匯總數(shù)據(jù)看，你無法知道這10000次調(diào)用是否每次都用3秒，還是這些調(diào)用中也許有一個(gè)用了5分鐘，而其余9999次調(diào)用每次只用0.02秒。這兩種情況需要進(jìn)行完全不同的處理。
在這種情況下最能為你提供幫助的診斷數(shù)據(jù)是Oracle的擴(kuò)展SQL跟蹤數(shù)據(jù)。擴(kuò)展SQL跟蹤文件按時(shí)間順序顯示了Oracle數(shù)據(jù)庫內(nèi)核在指定時(shí)間內(nèi)所完成工作的逐條記錄。收集擴(kuò)展SQL跟蹤數(shù)據(jù)幾乎是免費(fèi)的。最大的花銷是存儲每一個(gè)需要引起注重的跟蹤文件所需磁盤空間（很少超過幾兆字節(jié)）的費(fèi)用。
跟蹤自己的代碼。假如能訪問程序的源代碼，則打開其擴(kuò)展SQL跟蹤就非常輕易。首先必須確保會話的TIMED_STATISTICS和MAX_DUMP_ FILE_SIZE參數(shù)設(shè)置正確：
Code: [Copy to clipboard]
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
假如沒有設(shè)置TIMED_STATISTICS=TRUE，則數(shù)據(jù)庫內(nèi)核將把0值而不是真正的持續(xù)時(shí)間發(fā)送到跟蹤文件中。假如對MAX_DUMP_ FILE_SIZE嚴(yán)加限制，則會在跟蹤文件中生成下面這樣的消息，而不是你想要的時(shí)間數(shù)據(jù)：
*** DUMP FILE SIZE IS LIMITED TO 1048576 BYTES ***
接下來是激活跟蹤。有幾種方法可以采用。過去的方法是使用ALTER SESSION命令，如下所示：
Code: [Copy to clipboard]
alter session set events '10046 trace name context forever, level 12'
/* code to be traced goes here */
alter session set events '10046 trace name context off'
更好的方法是使用DBMS_SUPPORT包來激活擴(kuò)展SQL跟蹤：
Code: [Copy to clipboard]
dbms_support.start_trace(waits=>true, binds=>true)
/* code to be traced goes here */
dbms_support.stop_trace()
請注重DBMS_SUPPORT 沒有文檔說明，可能也不是數(shù)據(jù)庫默認(rèn)安裝的一部分。要了解DBMS_SUPPORT的信息，請參考MetaLink ( metalink.oracle.com)。
跟蹤別人的代碼。假如你想跟蹤沒有讀/寫權(quán)限的代碼，則激活擴(kuò)展SQL跟蹤就有點(diǎn)麻煩了。但也不會難很多。你首先要獲得你想跟蹤的會話的V$SESSION.SID和V$SESSION.SERIAL#值。然后使用下面的過程調(diào)用，可以設(shè)置所選會話的TIMED_STATISTICS和MAX_DUMP_FILE_SIZE參數(shù)：
Code: [Copy to clipboard]
dbms_system.set_bool_param_in_session(
sid => 42,
serial# => 1215,
parnam => 'timed_statistics',
bval => true)
dbms_system.set_int_param_in_session(
sid => 42,
serial# => 1215,
parnam => 'max_dump_file_size',
intval => 2147483647)
（對于Oracle8 8.1.6以前的版本，你可以用ALTER SYSTEM命令處理這些參數(shù)。）
接下來要激活跟蹤。有幾種方法可以采用，包括下面兩個(gè)：
方法一是使用DBMS_SUPPORT：
Code: [Copy to clipboard]
dbms_support.start_trace_in_session(
sid => 42,
serial# => 1215,
waits => true,
binds => true)
/* code to be traced executes during this time window */
dbms_support.stop_trace_in_session(
sid => 42,
serial => 1215)
若想激活擴(kuò)展SQL跟蹤，請不要使用名為SET_SQL_TRACE_IN_SESSION的DBMS_SUPPORT過程。該過程不答應(yīng)在跟蹤文件中指定等待和綁定的數(shù)據(jù)。
第二種方法更為精致，但在Oracle數(shù)據(jù)庫10g之前的版本中并不支持這種方法。 DBMS_MONITOR包的引入解決了許多復(fù)雜診斷數(shù)據(jù)收集問題，這些問題是由連接共享和多線程操作所引起的。你可以在Oracle數(shù)據(jù)庫10g中指定要跟蹤的服務(wù)、模塊或行動，而不指定要跟蹤的Oracle數(shù)據(jù)庫會話：
Code: [Copy to clipboard]
dbms_monitor.serv_mod_act_trace_enable(
service_name => 'APPS1',
module_name => 'PAYROLL',
action_name => 'PYUGEN',
waits => true,
binds => true,
instance_name => null)
/* code to be traced executes during this time window */
dbms_monitor.serv_mod_act_trace_disable(
service_name => 'APPS1',
module_name => 'PAYROLL',
action_name => 'PYUGEN')
利用DBMS_MONITOR包，Oracle可為要跟蹤的特定的業(yè)務(wù)操作提供完全支持激活或停止診斷數(shù)據(jù)收集的方法。
測試擴(kuò)展SQL跟蹤。試一試吧。查看第一個(gè)跟蹤文件只需使用一個(gè)簡單的SQL*Plus會話，就如同下面這樣：
Code: [Copy to clipboard]
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='Hello';
/* only in Oracle Database 8.1.7and later */
alter session set events '10046 trace name context forever, level 12';
select 'Howdy, it is 'sysdate from dual;
exit;
然后在由USER_DUMP_DEST實(shí)例參數(shù)的值命名的目錄中尋找文件名中包含字符串"Hello"的最新寫入的.trc文件。用你最喜歡的文本編輯器打開它。 閱讀Oracle MetaLink注釋39817.1或（Optimizing Oracle Performance，《優(yōu)化Oracle性能》）一書，以便大概了解原始跟蹤文件中有些什么。一定要運(yùn)行跟蹤文件上的tkPRof，并研究其輸出，但也不要由于有了tkprof就不再看原始的跟蹤文件。跟蹤文件中還有許多tkprof沒有向你展示的內(nèi)容。
假如你不僅需要一個(gè)由簡單的SELECT from DUAL 生成的跟蹤文件，還需要一個(gè)更感愛好的跟蹤文件，那么需要跟蹤下面這條SQL語句：
Code: [Copy to clipboard]
select object_type, owner, object_name from dba_objects;
由此得到的跟蹤數(shù)據(jù)會讓你感到很滿足，因?yàn)镺racle數(shù)據(jù)庫內(nèi)核替你完成了驚人的工作量。
創(chuàng)建資源描述
了正確而具體的診斷數(shù)據(jù)之后，你需要以摘要的形式對其進(jìn)行查看，這有助于你以最快的速度做出響應(yīng)。至少是從20世紀(jì)70年代開始，計(jì)算機(jī)程序員使用的摘要格式就是資源描述。資源描述只是一張表，它將所用時(shí)間分解為若干有用的子集，并按各子集所用時(shí)間降序排列。下面是一個(gè)資源描述的例子：
Code: [Copy to clipboard]
Response Time Component Duration
-------------------------- ----------
Freeway at <50% speed limit 28.3m 59%
Finding a parking spot 7.2m 15%
Waiting at traffic lights 5.2m 11%
Freeway at ≥50% speed limit 4.0m 8%
Other 3.1m 6%
-------------------------- ----------
Total 47.8m 100%
這個(gè)資源描述說明買一輛速度更快的車不會使你能夠更快地到達(dá)工作地點(diǎn)。
要從跟蹤文件創(chuàng)建資源描述，有兩種方法可以采用。
自己動手。《Optimizing Oracle Performance》一書中有所說明。
使用別人的工具。Oracle的tkprof和trcanalyzer（跟蹤分析器）工具可為你完成一部分工作，但不是全部。
對數(shù)據(jù)做出響應(yīng)
有了具體的診斷數(shù)據(jù)及其要點(diǎn)，就要決定對所看到的東西如何做出響應(yīng)。對資源描述做出響應(yīng)的經(jīng)驗(yàn)做法非常可靠且相當(dāng)簡單：首先減少花費(fèi)時(shí)間最長的部分，方法是減少調(diào)用它的次數(shù)。
這種方法幾乎總是正確的。理解減少給定組件的調(diào)用次數(shù)的方法，需要對不同等待事件名稱的含義有所了解。例如，當(dāng)被跟蹤的Oracle會話等待"buffer busy waits"這個(gè)等待事件時(shí)，該會話會向跟蹤文件發(fā)送會生成足夠多的信息，并顯示正在等待哪一個(gè)緩沖區(qū)以及為什么要等待。當(dāng)一個(gè)會話等待SQL*Net message from client事件時(shí)，跟蹤文件中生成的數(shù)據(jù)的位置會告訴你執(zhí)行過的數(shù)據(jù)庫調(diào)用哪個(gè)是多余的。
在Oracle9i第2版中，有350多個(gè)不同的等待事件。在Oracle數(shù)據(jù)庫10g中，幾乎有700個(gè)等待事件。但不必?fù)?dān)心：你根本不必知道它們都是什么意思。你只需知道你的重要程序花費(fèi)大部分時(shí)間所等待的那些事件是什么意思。
看看你能做些什么
有了合適的診斷數(shù)據(jù)，你就能迅速解決相應(yīng)的問題，或者證實(shí)這些問題不值得解決。
下面給出診斷數(shù)據(jù)能夠解決的一部分問題清單：
整個(gè)系統(tǒng)的問題以及個(gè)別用戶（業(yè)務(wù)）操作的具體問題
查詢錯(cuò)誤，包括寫得不好的SQL語句、有問題的索引以及數(shù)據(jù)密度問題
A應(yīng)用程序錯(cuò)誤，包括解析過度、不使用數(shù)組運(yùn)算等等在內(nèi)的應(yīng)用程序
串行化錯(cuò)誤，包括不必要的頻繁發(fā)生或費(fèi)時(shí)的鎖定、鎖存或存儲緩沖區(qū)活動
網(wǎng)絡(luò)錯(cuò)誤，如選擇的協(xié)議不當(dāng)、網(wǎng)絡(luò)設(shè)備有問題
磁盤輸入/輸出錯(cuò)誤，如高速緩存大小不適當(dāng)、負(fù)載不平衡以及配置不當(dāng)
容量不足，如交換、分頁和CPU占用過多
使用Oracle的擴(kuò)展SQL跟蹤數(shù)據(jù)以及提出"什么如此費(fèi)時(shí)？"這種問題的方法能帶來的最好結(jié)果是在開始診斷和解決問題之前你將不必再猜測性能問題會是什么。
