如何使用oracle提供的SQL_TRACE來(lái)跟蹤sql的執行情況

　　Sql性能非常差的時(shí)候,oracle提供了SQL_TRACE來(lái)跟蹤sql的執行情況。

　　注：分析sql的方式比較多，還有根據優(yōu)化器、sql執行計劃來(lái)分析。

　　SQL_TRACE能夠將sql執行的過(guò)程輸出到一個(gè)trace文件里面。

　　首先設置自己定義的trace文件的標識方便查找。

　　alter session set tracefile_identifier='mytest';

　　然后對當前會(huì )話(huà)啟動(dòng)SQL_TRACE，最好不要一直打開(kāi)該開(kāi)關(guān)，代價(jià)比較大。

　　alter session set sql_trace=true;

　　然后我們執行一條sql語(yǔ)句。

　　最后關(guān)閉該開(kāi)關(guān)的狀態(tài)。

　　alter session set sql_trace=false;

　　我們可以從目錄%ORACLE_BASE%/diag/rdbms/orcl/orcl/trace(11g版本的路徑，如果是10g的應該不一樣)中

　　找到自己定義的trace文件。

　　原始的trace文件的可讀性不高，我們一般使用oracle自帶的工具，tkprof來(lái)處理這個(gè)trace文件。我們可以查看tkprof的幫助。

　　tkprof orcl_ora_3820_mytest.trc out.txt

　　我們來(lái)看剛才生成的trace文件，頭部信息描述了tkprof 的版本以及報告中一些列的含義，對于任何一條sql語(yǔ)句，都應該包含Parse—sql分析階段，Execute—sql執行階段，Fetch—數據提取階段，橫向的列如圖所示，包含消耗cpu時(shí)間0.00秒，操作總耗時(shí)0.04秒，物理讀取了0個(gè)數據塊，沒(méi)有發(fā)生current方式的讀取(一般在update會(huì )發(fā)生)，一共提取記錄1條。

　　Misses in library cache during parse: 0表示這是一次軟分析(關(guān)于硬分析和軟分析下面會(huì )接著(zhù)談到)

　　Optimizer mode: ALL_ROWS表示oracle的優(yōu)化器模式為ALL_ROWS。這也就是前面提到的另外的分析方式優(yōu)化器。

　　下面是sql執行的具體計劃，可以看到執行計劃選擇的是全表掃描。

　　經(jīng)過(guò)處理以后的trace文件的確比較容易看明白，它有助于我們分析sql的性能問(wèn)題。

　　下面我通過(guò)一個(gè)trace實(shí)例來(lái)解釋一下，為什么OLTP系統中需要變量綁定機制。

　　當用戶(hù)和數據庫建立連接，并發(fā)送一條sql語(yǔ)句以后，oracle會(huì )對該sql進(jìn)行hash函數運算(hash算法提供了一種快速存取數據的方法，它用一種算法建立鍵值與真實(shí)值之間的對應關(guān)系,每一個(gè)真實(shí)值只能有一個(gè)鍵值，但是一個(gè)鍵值可以對應多個(gè)真實(shí)值，以方便存取)，得到一個(gè)hash值，然后到共享池中尋找是否有匹配的hash值的sql存在，如果有，就直接使用該sql的執行計劃去執行sql。如果沒(méi)有，oracle就會(huì )認為這是一條新的sql語(yǔ)句，然后按照語(yǔ)法分析，語(yǔ)義分析，生成執行計劃，執行sql這些步驟來(lái)執行最終把結果返回給用戶(hù)。這些步驟也被成為硬分析，可以想象，如果減少硬分析，能夠大大降低數據庫花費在sql解析上的資源開(kāi)銷(xiāo)。

　　我們先執行一條sql 1000次，比較綁定變量和不綁定變量的差異。得到結果以后，要計算實(shí)際的消耗，我們需要把OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS以及OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS的時(shí)間累計起來(lái)，前者表示數據字典表的相關(guān)的信息，包含權限控制等，后者表示sql所衍生出的遞歸sql語(yǔ)句的信息?？梢钥吹浇壎ㄗ兞康?，整條語(yǔ)句執行時(shí)間為0.22+0.02=0.24秒，CPU時(shí)間0.18+0.03=0.21秒，分析次數3次，執行次數1003次。而不綁定變量的時(shí)候，整條語(yǔ)句執行時(shí)間為0.28+1.29=1.57秒，CPU時(shí)間0.31+1.26=1.57秒，分析次數1002次，執行次數1003次?？梢?jiàn)綁定變量的確能夠帶來(lái)更低的開(kāi)銷(xiāo)。(如何設計數據庫中使用綁定變量也是和系統息息相關(guān)的，很多數據庫問(wèn)題都是在設計以后就已經(jīng)存在的)

　　應用級調優(yōu)分析:

　　就通常所說(shuō)的三層架構來(lái)說(shuō)，中間件這一層能夠起到一個(gè)緩沖池的作用，如果并發(fā)用戶(hù)數到3000這個(gè)數量級的時(shí)候，中間件能夠控制不是所有的用戶(hù)都能直接連接到數據庫，當然這里的程序會(huì )快速響應用戶(hù)請求，保證緩沖池的隊列等待不會(huì )很久。

　　對應用這一級別的調優(yōu)，主要集中在app程序，中間件的監控，集群配置等方面。如果是發(fā)現應用級別的問(wèn)題，首先要分析是配置問(wèn)題，還是程序本身的問(wèn)題。如果并發(fā)用戶(hù)數很大，中間件的線(xiàn)程池最大值配置過(guò)小，會(huì )導致在請求隊列堆積，表現就是線(xiàn)程監控視圖中，請求的隊列堆積比較多，一般可以調整線(xiàn)程池最大值來(lái)解決。我們來(lái)看看weblogic的監控視圖。

　　考慮到如果為每一個(gè)請求都創(chuàng )建一個(gè)新線(xiàn)程來(lái)處理的話(huà)，那么我們難以在系統中實(shí)現足夠數量的線(xiàn)程。不受限制的創(chuàng )建線(xiàn)程可能耗盡系統資源，因此引入了線(xiàn)程池。線(xiàn)程池的思想是在進(jìn)程開(kāi)始時(shí)創(chuàng )建一定數量的線(xiàn)程并將它們置入一個(gè)池(pool)中，線(xiàn)程在這個(gè)池中等待工作。當服務(wù)器接收到一個(gè)請求時(shí)，它就從池中喚醒一個(gè)線(xiàn)程(如果有可用的線(xiàn)程)，由它來(lái)處理請求。一旦線(xiàn)程服務(wù)完畢，它就返回線(xiàn)程池等待后面的工作。

　　線(xiàn)程池利用已存在的線(xiàn)程服務(wù)請求要比等待創(chuàng )建一個(gè)線(xiàn)程要快，并且線(xiàn)程池限制了線(xiàn)程的數量。

　　如果懷疑是程序的問(wèn)題，我們一般可以通過(guò)java自帶的工具來(lái)幫助分析，工具很多。這里我主要提到一個(gè)jdk1.6以后附帶的jvisualvm。

　　我們打開(kāi)jdk1.6，找到并運行jvisualvm.exe。

　　我們發(fā)現應用程序分為本地，遠程兩部分。本地包含本地運行的java進(jìn)程，遠程能夠通過(guò)配置連接到遠程服務(wù)器上的java進(jìn)程。我們先啟動(dòng)一個(gè)tomcat?？梢钥吹奖镜貞贸绦蛞呀?jīng)打開(kāi)了一個(gè)帶有tomcat以及進(jìn)程標識id的菜單。雙擊打開(kāi)。這里我們一般關(guān)心2個(gè)視圖。監視、線(xiàn)程。

　　其中監視視圖比較關(guān)心垃圾回收活動(dòng)(顧名思義，回收那些在程序里面不再使用到的內存空間)，堆內存變化。如果在壓力測試過(guò)程中，堆內存變化是一個(gè)逐漸上漲的趨勢，并且經(jīng)過(guò)多次手動(dòng)gc回收，還是保持這個(gè)趨勢，說(shuō)明內存泄漏的可能性很大。如果猜測有內存泄漏，可以通過(guò)分析java的heap dump。JVM (java虛擬機)記錄下問(wèn)題發(fā)生時(shí)系統的運行狀態(tài)并將其存儲在轉儲(dump)文件中。Heap dump就是這樣一種文件形式。

　　線(xiàn)程視圖比較關(guān)心線(xiàn)程的當前執行狀態(tài)，這里可以生成另一種轉儲文件 Java dump。Java dump，也叫做 Thread dump，是 JVM 故障診斷中最重要的轉儲文件之一。JVM 的許多問(wèn)題都可以使用這個(gè)文件進(jìn)行診斷，其中比較典型的包括線(xiàn)程阻塞，CPU 使用率過(guò)高，JVM Crash，堆內存不足，和類(lèi)裝載等問(wèn)題。其中線(xiàn)程阻塞更加常見(jiàn)。

原文轉自：http://blog.csdn.net/xuyubotest/article/details/8158241