Oracle Performance Optimization-Oracle SQL statement tracking

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;

 

For the SQL statement to be tracked, the generated trace file is placed under udump.

 

Use the following statement to find the generated trc File

 

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 156 K

-Rw-r ----- 1 oracleoinstall 87 K May 26 17: 29 ora1_ora_13954.trc

 

 

Analyze and track files with 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

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 of user that parsed the cursor

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12