OracleSQL跟蹤技術(shù)-(技術(shù)篇)-TD-V10

1、()Oracle SQL跟蹤技術(shù)在職員工(技術(shù)篇)部 門：培訓(xùn)部日 期：2012.03.23本文檔及其所含信息為內(nèi)部保密材料,由北京九恒星科技股份有限公司擁有,公司專屬知識產(chǎn)權(quán)未經(jīng)書面授權(quán)，不得對外泄露 COPYRIGHT 保留所有權(quán)利課程修訂訂修訂編號修訂作者變更頁碼版本號變更日期課程狀態(tài)備注第一次郭鵬1-74V1.02012-03-26待評審第二次第三次第四次第五次第六次課程介紹紹您將掌握SQL跟蹤技術(shù)術(shù)原理、方法及及過程，熟悉動動態(tài)性能能視圖表表的作用用及用法法，掌握握SQLTrace、TKPROF工具應(yīng)用用，并能能分析各各類ORACLE語句問題題，為實(shí)實(shí)際工作作做到方方法的指指引。課程

2、名稱OracleSQL跟蹤技術(shù)術(shù)培訓(xùn)目標(biāo)標(biāo)實(shí)施、開開發(fā)、測試崗位各級級技術(shù)人員員培訓(xùn)對象象2個(gè)小時(shí)培訓(xùn)課時(shí)時(shí)培訓(xùn)方式式面授課程層級級初、中級級培訓(xùn)內(nèi)容容綱要通過本次次培訓(xùn)，您將學(xué)學(xué)習(xí)到以以下核心心內(nèi)容：數(shù)據(jù)庫連連接體系系結(jié)構(gòu)客戶端、服務(wù)器器SQL跟蹤原理理及過程程方法SQLTrace工具介紹紹、使用用方法及及實(shí)踐TKPROF工具介紹紹、使用用方法及及實(shí)踐目錄錄TABLEOFCONTENTS一、數(shù)據(jù)庫庫連接體體系介紹紹二、SQL跟蹤原理理解析三、Oracle“SQLTarce”工具用用法四、Oracle“TKPROF”工具用用法五、Q &A單擊此處處添加段段落文字字內(nèi)容DB網(wǎng)絡(luò)連接接架構(gòu)多進(jìn)程(

3、單線程) P/T單進(jìn)程多多線程P/T多進(jìn)程多多線程P/T流行單擊此處處添加段段落文字字內(nèi)容三層技術(shù)術(shù)架構(gòu)DatabaseMCVCacheAJAX(AsynchronousJavaScript andXML)JPA(HibernateEntityManager)JSPHTMLEXTServlet ControllerQuartzJBMPRPCJMS業(yè)務(wù)邏輯輯SpringJersey(JAXRS)WEB服務(wù)器單擊此處處添加段段落文字字內(nèi)容DB連接模式式OracleServer對外提供供兩種服服務(wù)的鏈鏈接：共享服務(wù)務(wù)器模式式多個(gè)連接過過來，則則后臺只只有一個(gè)個(gè)進(jìn)程進(jìn)進(jìn)行服務(wù)務(wù)。專有服務(wù)務(wù)器模式式一個(gè)

4、連接過過來，則則后臺生生成一個(gè)個(gè)單獨(dú)的的進(jìn)程進(jìn)進(jìn)行服務(wù)務(wù)。（selectsid,serial#,username, status,program,serverfrom v$sessionwhereusernameisnotnull;）單擊此處處添加段段落文字字內(nèi)容DB連接模式式DB采用專有有服務(wù)器器模式：目錄錄TABLEOFCONTENTS二、SQL跟蹤原理理解析一、數(shù)據(jù)據(jù)庫連接接體系介介紹三、Oracle“SQLTarce”工具用用法四、Oracle“TKPROF”工具用用法五、Q &A單擊此處處添加段段落文字字內(nèi)容任務(wù)假如很多多用戶訪訪問DB服務(wù)器，我如何何知曉：是那個(gè)用用戶訪問問DB服務(wù)

5、器？那個(gè)用戶戶對應(yīng)那那個(gè)事務(wù)務(wù)？那個(gè)事務(wù)務(wù)執(zhí)行很很慢？（如何找到對應(yīng)應(yīng)SQL語句）單擊此處處添加段段落文字字內(nèi)容stat查詢兩臺臺計(jì)算機(jī)機(jī)TCP、UDP連接情況況信息Linux/UNIX下：netstatantpWin下：netstat -ano|findstr TCP工具單擊此處處添加段段落文字字內(nèi)容跟蹤、監(jiān)監(jiān)控客戶戶端連接接db服務(wù)器過過程步驟一：通過客客戶端連連接DB服務(wù)器單擊此處處添加段段落文字字內(nèi)容跟蹤、監(jiān)監(jiān)控客戶戶端連接接db服務(wù)器過過程步驟二：從DB服務(wù)器查查詢數(shù)據(jù)據(jù)庫客戶戶端連接接信息。命令為：netstatantp|more如下圖紅紅框所示示，查看看單擊此處處添加段段落文字字

6、內(nèi)容跟蹤、監(jiān)監(jiān)控客戶戶端連接接db服務(wù)器過過程ps-ef|grep4311ps-ef|grep 4317此時(shí)通過過在DB服務(wù)器端端運(yùn)行netstat命令，我我們找到到與客戶戶端通信信的DB服務(wù)器端端進(jìn)程信息：4311和4317則：單擊此處處添加段段落文字字內(nèi)容跟蹤、監(jiān)監(jiān)控客戶戶端連接接db服務(wù)器過過程步驟三：通過在客戶端端鍵入如如下命令令，查看看客戶端端連接進(jìn)進(jìn)程信息及客戶端進(jìn)程信息：netstat bnetstat-ano單擊此處處添加段段落文字字內(nèi)容工具介紹紹采用工具具：列出當(dāng)前前連接DB的用戶數(shù)數(shù)列出當(dāng)前前DB有多少事事務(wù)查看當(dāng)前前執(zhí)行的的SQL語句DB服務(wù)器進(jìn)進(jìn)程信息息單擊此處處添加段

7、段落文字字內(nèi)容1.v$session數(shù)據(jù)字典典關(guān)注點(diǎn)：SADDR:Session addressSID:唯一標(biāo)識識Session identifierPADDR:擁有這個(gè)個(gè)會話的的進(jìn)程地地址USERNAME:用戶名空空的的標(biāo)識是是oracle的實(shí)例，后臺進(jìn)進(jìn)程的process內(nèi)部sessionPROCESS：操作系統(tǒng)統(tǒng)客戶機(jī)機(jī)進(jìn)程IDMACHINE:客戶端machine name。SQL_ADDRESS:指當(dāng)前執(zhí)執(zhí)行的SQL語句的地地址PREV_SQL_ADDR:找到上一一次已經(jīng)經(jīng)執(zhí)行過過的SQL語句地址址SERVER：servertype(dedicated or shared)OSUSER

8、：客戶端操操作系統(tǒng)統(tǒng)用戶名名PROGRAM:客戶端應(yīng)應(yīng)用程序序STATUS:Statusofthe session:ACTIVE-Session currentlyexecutingSQLINACTIVE-等待操作作KILLED-被標(biāo)注為為刪除selectsaddr,sid,paddr,PROCESS ,username,statusfromv$session;v$session單擊此處處添加段段落文字字內(nèi)容1.v$transaction數(shù)據(jù)字典典關(guān)注點(diǎn)：ADDR:標(biāo)識這個(gè)個(gè)事務(wù)的的唯一地地址SES_ADDR:用戶會話話對象地地址與與session的SADDR掛鉤selectADDR,SES_

9、ADDRfromv$transaction;v$transaction單擊此處處添加段段落文字字內(nèi)容1.v$process數(shù)據(jù)字典典：包含含oracle運(yùn)行的所所有進(jìn)程程信息。常被用用于將oracle或服務(wù)進(jìn)進(jìn)程的操操作系統(tǒng)統(tǒng)進(jìn)程ID與數(shù)據(jù)庫庫session之間建立立聯(lián)系。關(guān)注點(diǎn)：ADDR:進(jìn)程對象象地址SPID:操作系統(tǒng)統(tǒng)進(jìn)程IDPID：Oracle進(jìn)程identifierUSERNAME:操作系統(tǒng)統(tǒng)進(jìn)程的的用戶名名。并非非Oracle用戶名。PROGRAM:進(jìn)程正在在執(zhí)行的的程序，和v$session中的program類似。selectaddr,spidfrom v$process;v$p

10、rocess單擊此處處添加段段落文字字內(nèi)容1.v$sql數(shù)據(jù)字典典關(guān)注點(diǎn)：sql_text： 前1000個(gè)字符addressselectsql_text, addressfromv$sql;v$sql單擊此處處添加段段落文字字內(nèi)容查看當(dāng)前前所有非非系統(tǒng)用用戶的連連接DB信息：spid（操作系系統(tǒng)進(jìn)程程）-連接DB客戶端的的進(jìn)程號號(netstatantp查)machine - DB所在服務(wù)務(wù)器機(jī)機(jī)器名selectp.spid,sid,paddr,gram,osuser,machine,PROCESS ,s.username,statusfrom v$sessions,v$proces

11、sp where p.addr=s.paddr ands.username is notnull;練習(xí)單擊此處處添加段段落文字字內(nèi)容動態(tài)性能能視圖分分析獲取客戶戶端、服務(wù)器器端進(jìn)程程信息，結(jié)合db數(shù)據(jù)字典典，將獲取一系列數(shù)據(jù)庫信息：#單擊此處處添加段段落文字字內(nèi)容跟蹤、監(jiān)監(jiān)控客戶戶端連接接db服務(wù)器過過程步驟四：1.查詢v$transaction數(shù)據(jù)字典典，查看當(dāng)前前存在的的事務(wù)信信息及SES_ADDR如下：selectaddr,ses_addrfromv$transaction;2.根據(jù)v$transaction中SES_ADDR字段，從從v$session查詢?nèi)缦孪滦畔⒓凹皩ο蟮牡腜ADD

12、R：selectsaddr,sid,paddr,username,statusfrom v$session;selectsaddr,sid,paddr,serial#,username,status,prev_sql_addr,prev_hash_value fromv$session;單擊此處處添加段段落文字字內(nèi)容跟蹤、監(jiān)監(jiān)控客戶戶端連接接db服務(wù)器過過程3.根據(jù)v$session中信息及及PADDR，從v$process表中查詢詢ADDR及服務(wù)器端對應(yīng)的的進(jìn)程SPID：selectaddr,spidfrom v$process;單擊此處處添加段段落文字字內(nèi)容跟蹤、監(jiān)監(jiān)控客戶戶端連接接db服

13、務(wù)器過過程4.根據(jù)從v$process表中查詢詢到的SPID，通過如如下查詢詢該DB服務(wù)器運(yùn)行的的進(jìn)程信息息：單擊此處處添加段段落文字字內(nèi)容跟蹤、監(jiān)監(jiān)控客戶戶端連接接db服務(wù)器過過程5.在DB服務(wù)器端端運(yùn)行命命令，如如下所示示：單擊此處處添加段段落文字字內(nèi)容步驟五：從v$session表中獲取取SQL_ADDRESS字段與PREV_SQL_ADDR字段。selectsid,prev_sql_addr,username,status fromv$session;如何知道道該進(jìn)程程執(zhí)行的的SQL語句單擊此處處添加段段落文字字內(nèi)容如何知道道該進(jìn)程程執(zhí)行的的SQL語句步驟六：根據(jù)v$session表中

14、查詢詢到的PREV_SQL_ADDR字段，再再從v$sql表中查詢到如如下信息息。selectsql_text, addressfromv$sqlwhereaddress=3091B2DC;最后就找找到了那那個(gè)用戶戶從什么么客戶端端發(fā)送什什么樣的的語句到到服務(wù)器器端什么么進(jìn)程去去處理的的整個(gè)過過程，從從而獲取取。單擊此處處添加段段落文字字內(nèi)容跟蹤語句句小結(jié)-查找sesssionselectsid,serial#,username,status,machine,osuser, program, logon_timefrom v$sessionwherestatus=upper(INACTIVE)

15、orderbylogon_time asc;selectaddr,ses_addrfromv$transaction;selectsaddr, sid,serial#,username, statusfrom v$session;selectsaddr, sid,serial#,username, status,prev_sql_addr,prev_hash_value fromv$session;-查找活動動用戶客客戶端進(jìn)進(jìn)程號，程序、tranactionselectsid,username,MACHINE,process,programfrom v$transactiont,v$sessi

16、onswheret.ses_addr=s.saddr;selectaddr,pid,spid,program fromv$process;selectsaddr, sid,paddr, username,statusfromv$session;-根據(jù)sid，查找相相應(yīng)inactivesessions對應(yīng)的sql操作selectsql_text, address, hash_valuefrom v$sql q, v$sessionswheres.sid=144and s.prev_sql_addr=q.address;單擊此處處添加段段落文字字內(nèi)容跟蹤語句句小結(jié)-查看當(dāng)前前用戶的的spid:se

17、lectspidfrom v$processp,v$sessionswheres.audsid=userenv(sessionid)ands.paddr=p.addr;selectspidfrom v$processp joinv$session sonp.addr=s.paddrands.audsid=userenv(sessionid);查看當(dāng)前前用戶的的sid和serial#:selectsid,serial#,statusfromv$sessionwhereaudsid=userenv(sessionid);查看當(dāng)前前用戶的的tracefile路徑:selectp.value|t.in

18、stance|_ora_ | ltrim(to_char(p.spid,fm99999) | .trcfrom v$processp,v$sessions,v$parameterp,v$threadtwherep.addr=s.paddr ands.audsid= userenv(sessionid) =user_dump_dest;單擊此處處添加段段落文字字內(nèi)容跟蹤語句句小結(jié)-已知spid，查看當(dāng)當(dāng)前正在在執(zhí)行或或最近一一次執(zhí)行行的語句句:select/*+ ordered*/sql_textfromv$sqltextsqlwhere(sql.hash_value, sql

19、.address)in(selectdecode(sql_hash_value,0,prev_hash_value, sql_hash_value),decode(sql_hash_value, 0, prev_sql_addr, sql_address)from v$sessions where s.paddr=(select addrfromv$processpwherep.spid=to_number(&pid)orderbypieceasc;-查看鎖和和等待:coluser_name formata10colownerformat a10colobject_nameformat a15

20、colsid format999999colserial#format999999colspidformata6select/*+ rule*/lpad( , decode(l.xidusn,0,3,0) | l.oracle_username user_name,o.owner,o.object_name,o.object_type,s.sid,s.serial#,p.spidfrom v$locked_objectl,dba_objectso,v$sessions,v$process pwherel.object_id= o.object_id andl.session_id=s.sida

21、nds.paddr= p.addrorderbyo.object_id,xidusn desc;目錄錄TABLEOFCONTENTS三、Oracle“SQLTarce”工具用用法一、數(shù)據(jù)據(jù)庫連接接體系介介紹二、SQL跟蹤原理理解析四、Oracle“TKPROF”工具用用法五、Q &A單擊此處處添加段段落文字字內(nèi)容Oracle“SQLTrace”介紹SQL_TRACE是Oracle提供的用用于進(jìn)行行SQL跟蹤的手手段，是是強(qiáng)有力力的輔助助診斷工工具.在日常的的數(shù)據(jù)庫庫問題診診斷和解解決中，SQL_TRACE是非常常常用的方方法。利用Oracle的SQLTRACE實(shí)用程序序可以對對指定的的查詢、批

22、處理理進(jìn)程和和整個(gè)系系統(tǒng)做時(shí)時(shí)間統(tǒng)計(jì)計(jì)?？梢砸詭椭椅覀儚氐椎椎卣业降较到y(tǒng)中中可能存存在的瓶瓶頸。SQLTRACE有如下的的功能：SQLTRACE運(yùn)行這個(gè)個(gè)查詢并并輸出一一個(gè)所執(zhí)執(zhí)行的Oracle查詢(或一系列查查詢)的統(tǒng)計(jì)信信息。SQLTRACE幫助開發(fā)發(fā)人員分分析查詢詢的每個(gè)個(gè)部分。用戶跟蹤蹤文件包包括：所有的用用戶發(fā)出出的SQL語句的信信息，包包括：每每條語句句的執(zhí)行行時(shí)間和和占用資資源的數(shù)數(shù)量。因?yàn)楦欅櫸募牡男畔⑹鞘菦]有格格式化的的原始信信息，所所以用TKPROF工具來轉(zhuǎn)轉(zhuǎn)換信息息，使其其易于理理解。單擊此處處添加段段落文字字內(nèi)容Oracle“SQLTrace”介紹通過SQLTra

23、ce可收集的的信息包包括：解析、執(zhí)執(zhí)行、返返回?cái)?shù)據(jù)據(jù)的次數(shù)數(shù)cpu和執(zhí)行命命令的時(shí)時(shí)間物理讀和和邏輯讀讀的次數(shù)數(shù)系統(tǒng)處理理的記錄錄數(shù)SQL查詢執(zhí)行行計(jì)劃狀狀況需要調(diào)整整的語句句符合以以下幾點(diǎn)點(diǎn)：CPU占用過多多Parse,Execute,Fetch花費(fèi)太多多時(shí)間DISK讀取太多多，query/current(SGA)中數(shù)據(jù)塊塊讀取太太少訪問許多多塊，只只返回2行單擊此處處添加段段落文字字內(nèi)容設(shè)置跟蹤蹤參數(shù)SQL_TRACE可以作為為初始化化參數(shù)在在全局啟啟用，也也可以通通過命令令行方式式在具體體session啟用。1設(shè)置實(shí)例例級跟蹤蹤參數(shù)（在全局啟啟用）在全局啟啟用SQL_TRACE會導(dǎo)致所所

24、有進(jìn)程程的活動動被跟蹤蹤，包括括后臺進(jìn)進(jìn)程及所所有用戶戶進(jìn)程，這通常常會導(dǎo)致致比較嚴(yán)嚴(yán)重的性性能問題題，所以以在生產(chǎn)產(chǎn)環(huán)境中中要謹(jǐn)慎慎使用，這個(gè)參參數(shù)在10g之后是動動態(tài)參數(shù)數(shù)，可以以隨時(shí)調(diào)調(diào)整，在在某些診診斷中非非常有效效。設(shè)置init.ora中的TIMED_STATISTICES（用于啟啟動或禁禁止對定定時(shí)統(tǒng)計(jì)計(jì)信息（如CUP時(shí)間、占占用時(shí)間），以及及動態(tài)性性能表中中多種統(tǒng)統(tǒng)計(jì)信息息的收集集功能。 ）MAX_DUP_FILE_SIZE（若為UNLIMITED則意味著著沒有上上限。）USER_DUMP_DEST（設(shè)置跟跟蹤文件件的存儲儲位置。默認(rèn)為為admin/用戶/udump）設(shè)置show

25、 parameterUSER_DUMP_DESTshow parameterMAX_DUMP_FILE_SIZEshow parameterTIMED_STATISTICSshow parameterSQL_TRACEaltersession setMAX_DUP_FILE_SIZE/timed_statistics true;altersystemsetMAX_DUP_FILE_SIZE/timed_statistics false;altersystemset user_dump_dest=newdir;altersystemset sql_trace=true;altersystemse

26、t sql_trace=false;單擊此處處添加段段落文字字內(nèi)容設(shè)置跟蹤蹤參數(shù)2設(shè)置跟蹤蹤參數(shù)（會話級啟用）大多數(shù)時(shí)時(shí)候我們們使用sql_trace跟蹤當(dāng)前前進(jìn)程.通過跟蹤蹤當(dāng)前進(jìn)進(jìn)程可以以發(fā)現(xiàn)當(dāng)當(dāng)前操作作的后臺臺數(shù)據(jù)庫庫遞歸活活動(這在研究究數(shù)據(jù)庫庫新特性性時(shí)尤其其有效)，研究SQL執(zhí)行，發(fā)發(fā)現(xiàn)后臺臺錯誤等等。grantselectany dictionaryto用戶名;設(shè)置會話話級跟蹤蹤參數(shù)如如下：SQL showparameter USER_DUMP_DESTNAMETYPEVALUE-user_dump_deststringC:oracleadminora92udumpSQL-步驟1

27、:設(shè)置SQL_TRACE=FALSESQL showparameter SQL_TRACENAMETYPEVALUE-sql_tracebooleanFALSESQLSQLALTERSESSIONSETSQL_TRACE=TRUE或SQL EXECUTEdbms_session.set_sql_trace(true);單擊此處處添加段段落文字字內(nèi)容啟動SQL跟蹤3啟動會話話級的跟跟蹤由于SQL_TRACE實(shí)用程序序會增加加系統(tǒng)的的開銷，建議用用完后及及時(shí)設(shè)置置為FALSE。如果將整整個(gè)實(shí)例例級都進(jìn)進(jìn)行跟蹤蹤的話，則在修修改SPFILE參數(shù)sql_trace=true。這樣會會使系統(tǒng)統(tǒng)付出更更大

28、的代代價(jià)。SQLaltersession setSQL_TRACE=TRUE;SQL-不用可設(shè)設(shè)置為：SQLaltersession setSQL_TRACE=FALSE;單擊此處處添加段段落文字字內(nèi)容啟動SQL跟蹤4確定被跟跟蹤所產(chǎn)產(chǎn)生文件件的名稱稱1）先查詢詢實(shí)例名名：SQLselect會話的數(shù)數(shù)據(jù)實(shí)例例名:|name實(shí)例名fromv$database;單擊此處處添加段段落文字字內(nèi)容啟動SQL跟蹤4確定被跟跟蹤所產(chǎn)產(chǎn)生文件件的名稱稱2）查詢自自己會話話的SID和SERIAL#：selectsubstr(a.spid,1,9) spid,substr(b.sid,1,5)sid,substr

29、(b.serial#,1,5)ser#,substr(b.machine,1,6)box,substr(b.username,1,8) username,substr(b.osuser,1,8)os_user,substr(gram,1,30) programfrom v$sessionb,v$processawhereb.paddr =a.addr andtype=USERorderbyspid;單擊此處處添加段段落文字字內(nèi)容啟動SQL跟蹤4確定被跟跟蹤所產(chǎn)產(chǎn)生文件件的名稱稱3）啟動會會話級跟跟蹤：SQL showuserUSER is“SYSSQLaltersession set

30、SQL_TRACE=TRUE;Session altered.Elapsed:00:00:00.03SQL跟蹤用戶戶名為SYS；被跟蹤的的文件的的名字可可能是：NSTC_5379.trc單擊此處處添加段段落文字字內(nèi)容啟動SQL跟蹤5開始運(yùn)行行應(yīng)用以以產(chǎn)生跟跟蹤文件件:假設(shè)用戶戶發(fā)出下下面語句句：SQL select*from t;SQL insertinto tvalues(10,地球人);SQL select* fromtwhereid= 10;單擊此處處添加段段落文字字內(nèi)容啟動SQL跟蹤6路徑找產(chǎn)產(chǎn)生跟蹤蹤文件:所產(chǎn)生的的文件為為NSTC_ora_5379.trc則：設(shè)置置會話級級跟蹤為為

31、FALSE：SQLSQL showuserUSER is“sysSQLaltersession setSQL_TRACE=FALSE;Session altered.Elapsed:00:00:00.00單擊此處處添加段段落文字字內(nèi)容跟蹤其他他用戶進(jìn)進(jìn)程在很多時(shí)時(shí)候我們們需要跟跟蹤其他他用戶的的進(jìn)程，而不是是當(dāng)前用用戶，可可以通過過Oracle提供的系系統(tǒng)包DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION來完成SET_SQL_TRACE_IN_SESSION過程序要要提供三三個(gè)參數(shù)數(shù):參數(shù)名稱參數(shù)類型In/Out Default?SIDNUMBERINSERIAL# N

32、UMBERINSQL_TRACE BOOLEANINEXECUTE dbms_system.set_sql_trace_in_session(session_id,serial_id,true);單擊此處處添加段段落文字字內(nèi)容10046事件說明明10046事件是Oracle提供的內(nèi)內(nèi)部事件件，是對對SQL_TRACE的增強(qiáng).10046事件可以以設(shè)置以以下四個(gè)個(gè)級別:1 -啟用標(biāo)準(zhǔn)準(zhǔn)的SQL_TRACE功能,等價(jià)于sql_trace4 -Level1加上綁定定值(bindvalues)8 -Level1+等待事件件跟蹤12- Level 1+Level4 +Level8在全局設(shè)設(shè)置在參數(shù)文文件中

33、增增加:event=10046tracenamecontext forever,level12此設(shè)置對對所有用用戶的所所有進(jìn)程程生效、包括后后臺進(jìn)程程.在會話設(shè)設(shè)置SQL alter sessionset events10046tracenamecontext forever;SQL alter sessionset events10046tracenamecontext forever, level 8;SQL alter sessionset events10046tracenamecontext off;單擊此處處添加段段落文字字內(nèi)容10046事件說明明對其他用用戶session設(shè)置ex

34、ec dbms_system.set_ev(146,4,10046,4,n6user);設(shè)置exec dbms_system.set_ev(146,4,10046,0,n6user);停止以上前兩兩個(gè)參數(shù)數(shù)分別為為sid，serial#selectsid,serial#,usernamefromv$sessionwhereusernameisnot null;單擊此處處添加段段落文字字內(nèi)容示例一通過v$session獲得sid、serial#等信息，確認(rèn)要要跟蹤的的用戶SQL selectp.spid,s.sid,s.serial#,gram,osuser,machine,PROCE

35、SS,s.username,status fromv$session s,v$process pwherep.addr=s.paddrand s.usernameisnot null;2.設(shè)置跟蹤蹤:SQL execdbms_system.set_sql_trace_in_session(145,10, true)PL/SQLprocedure successfullycompleted.可以等候候片刻，跟蹤session執(zhí)行任務(wù)務(wù),捕獲sql操作.3.停止跟蹤蹤:SQL execdbms_system.set_sql_trace_in_session(145,10, false)PL/SQL

36、procedure successfullycompleted.單擊此處處添加段段落文字字內(nèi)容示例二如何跟蹤蹤某應(yīng)用用下發(fā)生生的sql操作細(xì)節(jié)節(jié)1.找出應(yīng)用用的sid和serial#selectsid,serial#,usernamefromv$sessionwherelower(program)like%sqlplus%;2.開始跟蹤蹤SQL execdbms_system.set_sql_trace_in_session(148,57, true)期間做點(diǎn)點(diǎn)關(guān)于這這個(gè)應(yīng)用用的操作作（保證證和數(shù)據(jù)據(jù)庫能有有交互）單擊此處處添加段段落文字字內(nèi)容示例二3、停止跟跟蹤，在在user_dump_de

37、st目錄下會會生成跟跟蹤的trace文件SQL execdbms_system.set_sql_trace_in_session(148,57, false)4、找出并并進(jìn)入user_dump_dest目錄，最最后生成成那個(gè)文文件就是是要用的的trace文件SQL setlines1024SQL showparameter user_dump_dest單擊此處處添加段段落文字字內(nèi)容示例二5、使用tkprof格式化trace文件，sys=no的意思是是不查看看sys用戶的操操作$ tkproforcl_ora_24884.trcreport.txtsys=no6、 查看看格式化化后的文文件rep

38、ort.txt，里頭就就有跟蹤蹤期間發(fā)發(fā)生的所所有存儲儲過程（存儲過過程中執(zhí)執(zhí)行的sql操作也會會逐條顯顯示）和和sql操作，還還有各sql執(zhí)行的統(tǒng)統(tǒng)計(jì)數(shù)據(jù)據(jù)，可以以了解哪哪些sql快，哪些些慢。跟蹤完畢畢。目錄錄TABLEOFCONTENTS四、Oracle“TKPROF”工具用用法一、數(shù)據(jù)據(jù)庫連接接體系介介紹二、SQL跟蹤原理理解析三、Oracle“SQLTarce”工具用用法五、Q &A單擊此處處添加段段落文字字內(nèi)容TKPROF介紹Tkprof是一個(gè)用用于分析析Oracle跟蹤文件件并且產(chǎn)產(chǎn)生一個(gè)個(gè)更加清清晰合理理的輸出出結(jié)果的的可執(zhí)行行工具。如果一一個(gè)系統(tǒng)統(tǒng)的執(zhí)行行效率比比較低，一個(gè)比

39、比較好的的方法是是通過跟跟蹤用戶戶的會話話并且使使用Tkprof工具使用用排序功功能格式式化輸出出，從而而找出有有問題的的SQL語句:系統(tǒng)對SQL語句進(jìn)行行跟蹤后后，在產(chǎn)產(chǎn)生USER_DUMP_DEST路徑產(chǎn)生生了.TRC文件確定結(jié)束束跟蹤后后，可用用TKPROF實(shí)用程序序進(jìn)行分分析；TKPROF將跟蹤文文件作為為輸入文文件，在在經(jīng)過格格式化后后產(chǎn)生輸輸出文件件；由于跟蹤蹤文件是是一系列列的文件件，在使使用TKPROF時(shí)可以對對單個(gè)文文件進(jìn)行行格式化化，也可可以將所所有的跟跟蹤文件件串在一一起在進(jìn)進(jìn)行格式式化；逐一對單單個(gè)文件件進(jìn)行TKPROF，分別產(chǎn)產(chǎn)生相應(yīng)應(yīng)的輸出出文件；單擊此處處添加段段

40、落文字字內(nèi)容TKPROF作用標(biāo)記需要要調(diào)整的的語句使用TKPROF格式化的的文件很很容易看看出需要要調(diào)整的的SQL語句。查查找的依依據(jù)是：*耗費(fèi)大大量的CPU資源*花費(fèi)較較長的時(shí)時(shí)間進(jìn)行行SQL分析、執(zhí)執(zhí)行和獲獲取*從磁盤盤讀大量量的數(shù)據(jù)據(jù)塊，而而從SGA區(qū)讀的數(shù)數(shù)據(jù)很少少*訪問大大量的數(shù)數(shù)據(jù)塊但但只返回回一點(diǎn)數(shù)數(shù)據(jù)一旦找到到這些語語句就可可以用執(zhí)執(zhí)行計(jì)劃劃工具來來進(jìn)一步步?jīng)Q定為為什么這這些語句句性能這么低低了。單擊此處處添加段段落文字字內(nèi)容TKPROF工具介紹紹TKPROF命令語法法:附加參數(shù)數(shù):filename1指定的輸輸入文件件，可以以是多個(gè)個(gè)文件聯(lián)聯(lián)起來。Filename2格式化輸輸出

41、文件件。SORT在輸出文文件前先先排序。如果省省去則按按照實(shí)際際使用的的順序輸輸出到文文件中。排序選選項(xiàng)有以以下多種種：PRSCNT語句解析析的數(shù)目目PRSCPU語句解析析所占用用的CPU時(shí)間PRSELA語句解析析所占用用的時(shí)間間PRSDSK語句解析析期間，從磁盤盤進(jìn)行物物理讀的的數(shù)目TKPROFfilename1,filename2 SORT= opion,optionPRINT= integer AGGREGATE= YES| NO INSERT= filename3 SYS= YES |NO TABLE =schema.table | EXPLAIN=user/password RECO

42、RD=filename單擊此處處添加段段落文字字內(nèi)容TKPROF工具介紹紹附加參數(shù)數(shù)用于指指定期望望的信息息。這些些選項(xiàng)如如下所示示：EXPLAN為跟蹤文文件中的的每條語語句生成成一個(gè)執(zhí)執(zhí)行計(jì)劃劃。產(chǎn)生生的結(jié)果果保存在在指定模模式的PLAN_TABLE中，該模模式在命命令中給給出，即即命令中中必須要要給出用用戶名和和口令。TABLE指定執(zhí)行行計(jì)劃保保存的表表名稱，替換缺缺省的PLAN_TABLE表名。SYS表明是否否格式化化的文件件包括遞遞歸的SQL語句。SORT表明格式式化的文文件信息息排序的的規(guī)則。RECORD指定跟蹤蹤文件中中的SQL語句寫入入哪一個(gè)個(gè)文件。使你能能將SQL語句與附附加的

43、資資源使用用信息分分開。PRINT指定格式式化的跟跟蹤文件件最多可可以寫入入的SQL語句的數(shù)數(shù)量。INSERT執(zhí)行時(shí)創(chuàng)創(chuàng)建一個(gè)個(gè)腳本，用于生生成一個(gè)個(gè)TKPROF_TABLE表。AGGREGATE決定是否否將多個(gè)個(gè)執(zhí)行相相同語句句分析的的結(jié)果合合并。單擊此處處添加段段落文字字內(nèi)容格式后信信息內(nèi)容容解析表式統(tǒng)計(jì)計(jì)信息PARSE將把SQL語句轉(zhuǎn)化化為執(zhí)行行規(guī)劃，其中包包括對安安全授權(quán)權(quán)的檢查查，以及及對存在在表、列列和其他他訪問對對象進(jìn)行行檢查。EXECUTE由ORACLE進(jìn)行語句句執(zhí)行。對于INSERT、UPDATE、DELETE的語句，將對數(shù)數(shù)據(jù)進(jìn)行行修改；對于SELECT語句，將將對所選選行

44、進(jìn)行行確定。FETCH將檢查出出查詢的的返回行行，只有有SELECT語句能夠夠執(zhí)行取取數(shù)據(jù)的的操作。單擊此處處添加段段落文字字內(nèi)容格式后信信息內(nèi)容容解析表統(tǒng)計(jì)信信息：COUNT：語句解析析、執(zhí)行行或取數(shù)數(shù)據(jù)等執(zhí)執(zhí)行的數(shù)數(shù)據(jù)庫調(diào)調(diào)用數(shù)量量；CPU某條語句句進(jìn)行解解析、執(zhí)執(zhí)行和取取數(shù)據(jù)等等數(shù)據(jù)庫庫調(diào)用所所占用地地時(shí)間，單位為為秒；ELAPSED某條語句句進(jìn)行解解析、執(zhí)執(zhí)行和取取數(shù)據(jù)等等處理數(shù)數(shù)據(jù)庫調(diào)調(diào)用花費(fèi)費(fèi)的總的的時(shí)間，單位為為秒；DISK對于所有有語句解解析、執(zhí)執(zhí)行和取取數(shù)據(jù)等等調(diào)用，從磁盤盤數(shù)據(jù)文文件中物物理讀取取的數(shù)據(jù)據(jù)塊數(shù)目目；注意意：這不不是物理理I/O操作的數(shù)數(shù)量，如如果這個(gè)個(gè)值大于

45、于邏輯讀讀的數(shù)量量(disk query +current)，這意味味著數(shù)據(jù)據(jù)塊填充充進(jìn)了臨臨時(shí)表空空間；QHERY以一致模模式從高高速緩存存邏輯讀讀取的塊塊數(shù)量。通常，這類型型的邏輯輯讀用作作查詢。CURRENT在當(dāng)前模模式下從從高速緩緩存邏輯輯讀取的的塊數(shù)量量。這類類邏輯讀讀被INSERT、DELETE、UPDATE等語句所所使用。ROWS SQL語句所處處理行的的總數(shù)目目，它并并不包括括SQL語句的子子查詢所所處理的的行數(shù)。對于INSERT、DELETE、MERGE以及UPDATE等語句來來說，這這是所影影響的行行數(shù)量。單擊此處處添加段段落文字字內(nèi)容格式后信信息內(nèi)容容解析單擊此處處添加段

46、段落文字字內(nèi)容格式后信信息內(nèi)容容解析對于每個(gè)個(gè)行源操操作來說說，提供供如下的的運(yùn)行時(shí)時(shí)統(tǒng)計(jì)：cr是一致性性模式下下邏輯讀讀出的數(shù)數(shù)據(jù)塊數(shù)數(shù)。pr是從磁盤盤物理讀讀出的數(shù)數(shù)據(jù)塊數(shù)數(shù)。pw是物理寫寫入磁盤盤的數(shù)據(jù)據(jù)塊數(shù)。time是以微秒秒表示的的總的消消逝時(shí)間間。注意意根據(jù)統(tǒng)統(tǒng)計(jì)得到到的值不不總是精精確的。實(shí)際上上，為了了減少開開銷，可可能用了了采樣。cost是操作的的評估開開銷。這個(gè)值只只有在Oracle11g才提供。size是操作返返回的預(yù)預(yù)估數(shù)據(jù)據(jù)量(字節(jié)數(shù))。這個(gè)值只只有在Oracle11g才提供。card是操作返返回的預(yù)預(yù)估行數(shù)數(shù)。這個(gè)值只只有在Oracle11g才提供。單擊此處處添加段

47、段落文字字內(nèi)容格式后信信息內(nèi)容容解析輸出文件件的結(jié)尾尾給出了了所有關(guān)關(guān)于跟蹤蹤文件的的信息：跟蹤文件件名稱、版本號號、用于于這個(gè)分分析所使使用的參參數(shù)sort的值。所有會話話數(shù)量與與SQL語句數(shù)量量。顯示組成成跟蹤文文件的行行數(shù)所有SQL語句用去去的時(shí)間間（毫秒秒）。注意：知知道整個(gè)個(gè)跟蹤文文件消耗耗了多少少時(shí)間是是很關(guān)鍵鍵單擊此處處添加段段落文字字內(nèi)容案例解析析例1：$tkprof ora_1234.trctrace.txtsys=no explain=scott/111111該命令將將忽略原原始文件件中的遞遞歸SQL；在格式式化的跟跟蹤文件件中包括括每條SQL語句的執(zhí)執(zhí)行計(jì)劃劃PLAN_T

48、ABLE依賴于用用戶JOE模式。例2：$tkprof ora_1234.trctrace.txtprint=10record=sql.txt該命令將將翻譯跟跟蹤文件件ora_1234.trc并保存頭頭10條SQL語句到輸輸出文件件sql.txt，用于保保存原始始跟蹤文文件的頭頭10條SQL語句。例3：$tkprof ora_1234.trctrace.txtinsert=trace.sqlsort=fchrow該命令將將翻譯跟跟蹤文件件ora_1234.trc并保存到到輸出文文件trace.txt；創(chuàng)建第第二個(gè)文文件trace.sql，用于保保存生成成TKPORF_TABLE表的SQL命令。如

49、如果沒有有排序參參數(shù)fchrow,TKPROF將會按照照執(zhí)行的的順序輸輸出到trace.txt文件中去去。例4:tkprofnstc_ora_4408.trc2.txtAGGREGATE=no合并執(zhí)行行相同的的結(jié)果單擊此處處添加段段落文字字內(nèi)容解讀與分分析格式式化文件件（示例例一）被TKPROF分析的格格式化文文件，如如下所示示：單擊此處處添加段段落文字字內(nèi)容解讀與分分析格式式化文件件（示例一一）分析：跟蹤文件件中發(fā)現(xiàn)現(xiàn)，第三三條語句句被分析析了一次次，執(zhí)行行了五次次，并執(zhí)執(zhí)行了250次獲取操操作。這個(gè)過程程耗費(fèi)了了CPU0.18秒的時(shí)間間，整個(gè)個(gè)語句執(zhí)執(zhí)行花費(fèi)費(fèi)了0.20秒的時(shí)間間。它需需要從磁磁盤讀取取54個(gè)數(shù)據(jù)塊塊，從SGA讀取266個(gè)數(shù)據(jù)塊塊并返回回245行數(shù)據(jù)在最后一一行顯示示所有統(tǒng)統(tǒng)計(jì)信息息。單擊此處處添加段段落文字字內(nèi)容格式后信信息內(nèi)容容解析二二執(zhí)行計(jì)劃執(zhí)行統(tǒng)計(jì)計(jì)行源操作單擊此處處添加段段落文字字內(nèi)容解讀與分分析格式式化文件件（示例二二）分析：在本例中中，表sales有918843行而根據(jù)據(jù)跟蹤文文件僅返返回540318行，這表表示W(wǎng)HERE條件過濾濾了大約約41%的行。此此后的GROUPBY進(jìn)一步減減少結(jié)果果到16348行。單擊此處處添加段段落文字字內(nèi)容解讀與分分析格式式化文件件用編