OracleSQL跟踪技术__TD_V10.ppt

(),OracleSQL跟踪技术在职员工(技术篇),部门：培训部日期：2012.03.23,本文档及其所含信息为内部保密材料,未经书面授权，不得对外泄露COPYRIGHT保留所有,课程修订,课程介绍,您将掌握SQL跟踪技术原理、方法及过程，熟悉动态性能视图表的作用及用法，掌握SQLTrace、TKPROF工具应用，并能分析各类ORACLE语句问题，为实际工作做到方法的指引。,课程名称,OracleSQL跟踪技术,培训目标,实施、开发、测试岗位各级技术人员,培训对象,2个小时,培训课时,培训方式,面授,课程层级,初、中级,培训内容纲要,通过本次培训，您将学习到以下核心内容：数据库连接体系结构客户端、服务器SQL跟踪原理及过程方法SQLTrace工具介绍、使用方法及实践TKPROF工具介绍、使用方法及实践,目录,TABLEOFCONTENTS,一、数据库连接体系介绍,二、SQL跟踪原理解析,三、Oracle“SQLTarce”工具用法,四、Oracle“TKPROF”工具用法,五、Q）,单击此处添加段落文字内容,DB连接模式,DB采用专有服务器模式：,目录,TABLEOFCONTENTS,二、SQL跟踪原理解析,一、数据库连接体系介绍,三、Oracle“SQLTarce”工具用法,四、Oracle“TKPROF”工具用法,五、Q,v$session,单击此处添加段落文字内容,1.v$transaction数据字典关注点：ADDR:标识这个事务的唯一地址SES_ADDR:用户会话对象地址与session的SADDR挂钩selectADDR,SES_ADDRfromv$transaction;,v$transaction,单击此处添加段落文字内容,1.v$process数据字典：包含oracle运行的所有进程信息。常被用于将oracle或服务进程的操作系统进程ID与数据库session之间建立联系。关注点：ADDR:进程对象地址SPID:操作系统进程IDPID：Oracle进程identifierUSERNAME:操作系统进程的用户名。并非Oracle用户名。PROGRAM:进程正在执行的程序，和v$session中的program类似。selectaddr,spidfromv$process;,v$process,单击此处添加段落文字内容,1.v$sql数据字典关注点：sql_text：前1000个字符addressselectsql_text,addressfromv$sql;,v$sql,单击此处添加段落文字内容,查看当前所有非系统用户的连接DB信息：spid（操作系统进程）-连接DB客户端的进程号(netstatantp查)machine-DB所在服务器机器名selectp.spid,sid,paddr,gram,osuser,machine,PROCESS,s.username,statusfromv$sessions,v$processpwherep.addr=s.paddrands.usernameisnotnull;,练习,单击此处添加段落文字内容,动态性能视图分析,获取客户端、服务器端进程信息，结合db数据字典，将获取一系列数据库信息：,#,单击此处添加段落文字内容,跟踪、监控客户端连接db服务器过程,步骤四：1.查询v$transaction数据字典，查看当前存在的事务信息及SES_ADDR如下：selectaddr,ses_addrfromv$transaction;,2.根据v$transaction中SES_ADDR字段，从v$session查询如下信息及对象的PADDR：selectsaddr,sid,paddr,username,statusfromv$session;selectsaddr,sid,paddr,serial#,username,status,prev_sql_addr,prev_hash_valuefromv$session;,单击此处添加段落文字内容,跟踪、监控客户端连接db服务器过程,3.根据v$session中信息及PADDR，从v$process表中查询ADDR及服务器端对应的进程SPID：selectaddr,spidfromv$process;,单击此处添加段落文字内容,跟踪、监控客户端连接db服务器过程,4.根据从v$process表中查询到的SPID，通过如下查询该DB服务器运行的进程信息：,单击此处添加段落文字内容,跟踪、监控客户端连接db服务器过程,5.在DB服务器端运行命令，如下所示：,单击此处添加段落文字内容,步骤五：从v$session表中获取SQL_ADDRESS字段与PREV_SQL_ADDR字段。selectsid,prev_sql_addr,username,statusfromv$session;,如何知道该进程执行的SQL语句,单击此处添加段落文字内容,如何知道该进程执行的SQL语句,步骤六：根据v$session表中查询到的PREV_SQL_ADDR字段，再从v$sql表中查询到如下信息。selectsql_text,addressfromv$sqlwhereaddress=3091B2DC;,最后就找到了那个用户从什么客户端发送什么样的语句到服务器端什么进程去处理的整个过程，从而获取。,单击此处添加段落文字内容,跟踪语句小结,-查找sesssionselectsid,serial#,username,status,machine,osuser,program,logon_timefromv$sessionwherestatus=upper(INACTIVE)orderbylogon_timeasc;selectaddr,ses_addrfromv$transaction;selectsaddr,sid,serial#,username,statusfromv$session;selectsaddr,sid,serial#,username,status,prev_sql_addr,prev_hash_valuefromv$session;-查找活动用户客户端进程号，程序、tranactionselectsid,username,MACHINE,process,programfromv$transactiont,v$sessionswheret.ses_addr=s.saddr;selectaddr,pid,spid,programfromv$process;selectsaddr,sid,paddr,username,statusfromv$session;-根据sid，查找相应inactivesessions对应的sql操作selectsql_text,address,hash_valuefromv$sqlq,v$sessionswheres.sid=144ands.prev_sql_addr=q.address;,单击此处添加段落文字内容,跟踪语句小结,-查看当前用户的spid:selectspidfromv$processp,v$sessionswheres.audsid=userenv(sessionid)ands.paddr=p.addr;selectspidfromv$processpjoinv$sessionsonp.addr=s.paddrands.audsid=userenv(sessionid);查看当前用户的sid和serial#:selectsid,serial#,statusfromv$sessionwhereaudsid=userenv(sessionid);查看当前用户的tracefile路径:selectp.value|t.instance|_ora_|ltrim(to_char(p.spid,fm99999)|.trcfromv$processp,v$sessions,v$parameterp,v$threadtwherep.addr=s.paddrands.audsid=userenv(sessionid)=user_dump_dest;,单击此处添加段落文字内容,跟踪语句小结,-已知spid，查看当前正在执行或最近一次执行的语句:select/*+ordered*/sql_textfromv$sqltextsqlwhere(sql.hash_value,sql.address)in(selectdecode(sql_hash_value,0,prev_hash_value,sql_hash_value),decode(sql_hash_value,0,prev_sql_addr,sql_address)fromv$sessionswheres.paddr=(selectaddrfromv$processpwherep.spid=to_number(,目录,TABLEOFCONTENTS,三、Oracle“SQLTarce”工具用法,一、数据库连接体系介绍,二、SQL跟踪原理解析,四、Oracle“TKPROF”工具用法,五、QaltersystemsetMAX_DUP_FILE_SIZE/timed_statisticsfalse;altersystemsetuser_dump_dest=newdir;,altersystemsetsql_trace=true;altersystemsetsql_trace=false;,单击此处添加段落文字内容,设置跟踪参数,2设置跟踪参数（会话级启用）大多数时候我们使用sql_trace跟踪当前进程.通过跟踪当前进程可以发现当前操作的后台数据库递归活动(这在研究数据库新特性时尤其有效)，研究SQL执行，发现后台错误等。grantselectanydictionaryto用户名;设置会话级跟踪参数如下：SQLshowparameterUSER_DUMP_DESTNAMETYPEVALUE-user_dump_deststringC:oracleadminora92udumpSQL-步骤1:设置SQL_TRACE=FALSESQLshowparameterSQL_TRACENAMETYPEVALUE-sql_tracebooleanFALSESQLSQLALTERSESSIONSETSQL_TRACE=TRUE或SQLEXECUTEdbms_session.set_sql_trace(true);,单击此处添加段落文字内容,启动SQL跟踪,3启动会话级的跟踪由于SQL_TRACE实用程序会增加系统的开销，建议用完后及时设置为FALSE。如果将整个实例级都进行跟踪的话，则在修改SPFILE参数sql_trace=true。这样会使系统付出更大的代价。,SQLaltersessionsetSQL_TRACE=TRUE;SQL-不用可设置为：SQLaltersessionsetSQL_TRACE=FALSE;,单击此处添加段落文字内容,启动SQL跟踪,4确定被跟踪所产生文件的名称1）先查询实例名：,SQLselect会话的数据实例名:|name实例名fromv$database;,单击此处添加段落文字内容,启动SQL跟踪,4确定被跟踪所产生文件的名称2）查询自己会话的SID和SERIAL#：,selectsubstr(a.spid,1,9)spid,substr(b.sid,1,5)sid,substr(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)programfromv$sessionb,v$processawhereb.paddr=a.addrandtype=USERorderbyspid;,单击此处添加段落文字内容,启动SQL跟踪,4确定被跟踪所产生文件的名称3）启动会话级跟踪：,SQLshowuserUSERis“SYSSQLaltersessionsetSQL_TRACE=TRUE;Sessionaltered.Elapsed:00:00:00.03SQL跟踪用户名为SYS；被跟踪的文件的名字可能是：NSTC_5379.trc,单击此处添加段落文字内容,启动SQL跟踪,5开始运行应用以产生跟踪文件:假设用户发出下面语句：,SQLselect*fromt;SQLinsertintotvalues(10,地球人);SQLselect*fromtwhereid=10;,单击此处添加段落文字内容,启动SQL跟踪,6路径找产生跟踪文件:所产生的文件为NSTC_ora_5379.trc则：设置会话级跟踪为FALSE：,SQLSQLshowuserUSERis“sysSQLaltersessionsetSQL_TRACE=FALSE;Sessionaltered.Elapsed:00:00:00.00,单击此处添加段落文字内容,跟踪其他用户进程,在很多时候我们需要跟踪其他用户的进程，而不是当前用户，可以通过Oracle提供的系统包DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION来完成SET_SQL_TRACE_IN_SESSION过程序要提供三个参数:,EXECUTEdbms_system.set_sql_trace_in_session(session_id,serial_id,true);,单击此处添加段落文字内容,10046事件说明,10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强.10046事件可以设置以下四个级别:1-启用标准的SQL_TRACE功能,等价于sql_trace4-Level1加上绑定值(bindvalues)8-Level1+等待事件跟踪12-Level1+Level4+Level8在全局设置在参数文件中增加:event=10046tracenamecontextforever,level12此设置对所有用户的所有进程生效、包括后台进程.在会话设置SQLaltersessionsetevents10046tracenamecontextforever;SQLaltersessionsetevents10046tracenamecontextforever,level8;SQLaltersessionsetevents10046tracenamecontextoff;,单击此处添加段落文字内容,10046事件说明,对其他用户session设置execdbms_system.set_ev(146,4,10046,4,n6user);设置execdbms_system.set_ev(146,4,10046,0,n6user);停止以上前两个参数分别为sid，serial#selectsid,serial#,usernamefromv$sessionwhereusernameisnotnull;,单击此处添加段落文字内容,示例一,通过v$session获得sid、serial#等信息，确认要跟踪的用户SQLselectp.spid,s.sid,s.serial#,gram,osuser,machine,PROCESS,s.username,statusfromv$sessions,v$processpwherep.addr=s.paddrands.usernameisnotnull;2.设置跟踪:SQLexecdbms_system.set_sql_trace_in_session(145,10,true)PL/SQLproceduresuccessfullycompleted.可以等候片刻，跟踪session执行任务,捕获sql操作.3.停止跟踪:SQLexecdbms_system.set_sql_trace_in_session(145,10,false)PL/SQLproceduresuccessfullycompleted.,单击此处添加段落文字内容,示例二,如何跟踪某应用下发生的sql操作细节1.找出应用的sid和serial#selectsid,serial#,usernamefromv$sessionwherelower(program)like%sqlplus%;2.开始跟踪SQLexecdbms_system.set_sql_trace_in_session(148,57,true)期间做点关于这个应用的操作（保证和数据库能有交互）,单击此处添加段落文字内容,示例二,3、停止跟踪，在user_dump_dest目录下会生成跟踪的trace文件SQLexecdbms_system.set_sql_trace_in_session(148,57,false)4、找出并进入user_dump_dest目录，最后生成那个文件就是要用的trace文件SQLsetlines1024SQLshowparameteruser_dump_dest,单击此处添加段落文字内容,示例二,5、使用tkprof格式化trace文件，sys=no的意思是不查看sys用户的操作$tkproforcl_ora_24884.trcreport.txtsys=no6、查看格式化后的文件report.txt，里头就有跟踪期间发生的所有存储过程（存储过程中执行的sql操作也会逐条显示）和sql操作，还有各sql执行的统计数据，可以了解哪些sql快，哪些慢。跟踪完毕。,目录,TABLEOFCONTENTS,四、Oracle“TKPROF”工具用法,一、数据库连接体系介绍,二、SQL跟踪原理解析,三、Oracle“SQLTarce”工具用法,五、Qcallcountcpuelapseddiskquerycurrentrows-parse10.080.120000execute10.040.2053111getch00.000.000100-total20.120.3253111,单击此处添加段落文字内容,解读与分析格式化文件,selectlast_name,title,department_idfromemployee;callcountcpuelapseddiskquerycurrentrows-parse10.010.010000execute50.080.090000getch2500.090.105420264245-total2560.180.205420264245parse分析是将