oracle性能调优之--Oracle sql语句跟踪

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 integer0

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 sessionset sql_trace=true ；

对于跟踪的sql语句，生成的trace 文件放在udump 下

通过以下语句可以查到生成的trc文件

SQL> selectusername,addr,spid from v$process

2where addr=( select paddr from v$session

3 where sid=( selectdistinct sid from v$mystat));

USERNAME ADDRSPID

----------------------- ------------

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:

prscntnumber of times parse was called

prscpucpu time parsing

prselaelapsed time parsing

prsdsknumber of disk reads during parse

prsqrynumber of buffers for consistent read during parse

prscunumber of buffers for current read during parse

prsmisnumber of misses in library cache during parse

execntnumber of execute was called

execpucpu time spent executing

exeelaelapsed time executing

exedsknumber of disk reads during execute

exeqrynumber of buffers for consistent read during execute

execunumber of buffers for current read during execute

exerownumber of rows processed during execute

exemisnumber of library cache misses during execute

fchcntnumber of times fetch was called

fchcpucpu time spent fetching

fchelaelapsed time fetching

fchdsknumber of disk reads during fetch

fchqrynumber of buffers for consistent read during fetch

fchcunumber of buffers for current read during fetch

fchrownumber of rows fetched

useriduserid of user that parsed the cursor