分类:
2012-05-18 10:09:29
原文地址:oracle SQL语句跟踪详解 作者:云中的二舅
本文主要内容为oracle SQL语句跟踪详解,废话不多说,马上进入正题。
对于跟踪的sql语句,生成的trace 文件放在udump 下
SQL> showparameter sql
NAME TYPE VALUE
----------------------------------------------- ------------------------------
plsql_ccflags string
plsql_code_type string INTERPRETED
plsql_compiler_flags string INTERPRETED, NON_DEBUG
plsql_debug boolean FALSE
plsql_native_library_dir string
plsql_native_library_subdir_count integer 0
plsql_optimize_level integer 2
plsql_v2_compatibility boolean FALSE
plsql_warnings string DISABLE:ALL
sql92_security boolean FALSE
sql_trace boolean FALSE
sql_version string NATIVE
sqltune_category string DEFAULT
SQL>alter session set sql_trace=true ;
通过以下语句可以查到生成的trc文件
SQL> selectusername,addr,spid from v$process
2 where addr=( select paddr from v$session
3 where sid=( selectdistinct sid from v$mystat));
USERNAME ADDR SPID
----------------------- ------------
oracle 2AE1D48C 13954
SQL> showparameter dump
NAME TYPE VALUE
----------------------------------------------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/admin/ora1/bdump
core_dump_dest string /u01/app/oracle/admin/ora1/cdump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /u01/app/oracle/admin/ora1/udump
[oracle@rac1 ~]$ ls-lth /u01/app/oracle/admin/ora1/udump/
total 156K
-rw-r----- 1 oracleoinstall 87K May 26 17:29 ora1_ora_13954.trc
用tkprof分析,跟踪文件
[oracle@rac1 ~]$tkprof
Usage: tkproftracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ][sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain='option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQLstatements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERTstatements.
sys=no TKPROF does not list SQL statementsrun as user SYS.
record=filename Record non-recursive statements found in thetrace file.
waits=yes|no Record summary for any wait events foundin the trace file.
sort=option Set of zero or more of the following sortoptions:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing更多Oracle知识请看http://www.cuug.com/
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
好了,就暂且介绍到这里,希望能带给读者帮助。