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

好了，就暂且介绍到这里，希望能带给读者帮助。