Tuesday, August 23, 2011

What is SQL Trace and TKPROF?


SQL Trace:

The SQL Trace facility and TKPROF lets you accurately assess the efficiency of the SQL statements an application runs.

How to use SQL Trace?

When the SQL Trace facility is enabled, performance statistics for all SQL statements executed in a user session or in the instance are placed into trace files.

Tracing a particular user's session:

1.) First get the SID and serial# of the session:

SQL>conn / as sysdba

SQL>select sid,serial# from v$session where username='SCOTT';

      SID    SERIAL#
---------- ----------
         1      136

2.) Enable tracing for your selected process:

SQL> ALTER SYSTEM SET timed_statistics = true;

Timed statistics means CPU and elapsed times as well as the collection of various statistics in the dynamic performance tables.

SQL> execute dbms_system.set_sql_trace_in_session(1, 136, true);

3.) Ask user to run the sql's:

4.) Look for trace file in USER_DUMP_DEST:
   D:\oracle\product\10.2.0\admin\tsm\udump

TKPROF:

TKPROF accepts as input a trace file produced by the SQL Trace facility, and it produces a formatted output file. TKPROF can also be used to generate execution plans.

After the SQL Trace facility has generated a number of trace files, you can:

Run TKPROF on each individual trace file, producing a number of formatted output files, one for each session.
Concatenate the trace files, and then run TKPROF on the result to produce a formatted output file for the entire instance.

Steps for TKPROF:

1.) Creating a plan_table:

The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL statement. You must make sure such a plan table exists.You can use the SQL script utlxplan.sql to manually create a local PLAN_TABLE in your schema.

@ORACLE_HOME\rdbms\admin\utlxplan.sql
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE;
GRANT SELECT, INSERT, UPDATE, DELETE ON SYS.PLAN_TABLE TO PUBLIC;

2.) Trace files which you obtained from SQL Trace utility now can be readable using TKPROF

TKPROF D:\oracle\product\10.2.0\admin\tsm\udump\tsm_ora_2688 c:\output.txt explain=scott/tiger@service table=sys.plan_table

Output:

select * 
from
 tab

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6      0.04       0.04         21       1884          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.04       0.04         21       1884          0           6

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  NESTED LOOPS OUTER (cr=628 pr=21 pw=0 time=25030 us)
      2   TABLE ACCESS FULL OBJ$ (cr=621 pr=20 pw=0 time=21996 us)
      2   TABLE ACCESS CLUSTER TAB$ (cr=7 pr=1 pw=0 time=3051 us)
      2    INDEX UNIQUE SCAN I_OBJ# (cr=4 pr=1 pw=0 time=3033 us)(object id 3)

Enjoy:-)

9 comments:

Anonymous said...

Yesterday, while I was at work, my cousin stole my apple ipad and
tested to see if it can survive a twenty five foot drop, just so she can be a youtube sensation.
My iPad is now destroyed and she has 83 views.
I know this is completely off topic but I had to share it with someone!


my blog post ... voyance

Anonymous said...

Τrеmendous thingѕ hеre. I am veгу satisfied to ѕee уour аrtіcle.
Thank you so much anԁ I'm having a look forward to contact you. Will you please drop me a mail?

my page devis peinture

Anonymous said...

Right away I am ready to do my breakfast, when having my breakfast
coming over again to read further news.

Stop by my site Tennis

Anonymous said...

With havіn so much ωrіtten сontеnt do
уou ever run into anу pгoblems of plagorism or cοpyright violatіon?

Му blog has a lot οf unique content Ӏ've either authored myself or outsourced but it appears a lot of it is popping it up all over the web without my authorization. Do you know any ways to help prevent content from being ripped off? I'd сегtaіnly appreсiаte it.


Mу homepage ... disque ssd

Anonymous said...

My brother suggested I might like this web site.
He was entirely right. This post truly made my day. You cann't imagine just how much time I had spent for this info! Thanks!

Look into my blog post ... tarot de Marseille

Anonymous said...

Hello Dear, are you truly visiting this web page on
a regular basis, if so after that you will definitely obtain
good know-how.

my site - voyance par telephone

Unknown said...

zhengjx20160428
coach factory outlet
nfl jerseys
true religion jeans
oakley outlet
insanity workout
louis vuitton outlet
kate spade outlet
louis vuitton purses
michael kors outlet
cheap toms
true religion jeans
ray bans
fitflop shoes
louboutin shoes
cheap air jordans
nike air force 1 white
nike air max
nike uk
louis vuitton purses
nike store
oakley sunglasses
ray bans
burberry outlet
montblanc pens
michael kors outlet clearance
lebron 13
adidas originals store
nike outlet
michael kors handbags
ray ban outlet
michael kors outlet clearance
coach outlet store online
louis vuitton bags
michael kors purses
michael kors canada outlet
cheap louis vuitton handbags
cheap toms shoes
coach outlet
ralph lauren

5689 said...

zzzzz2018.8.31
coach outlet
canada goose outlet
air max 90
nike factory outlet
nike huarache
christian louboutin sale
tods shoes
ugg boots on sale 70% off
tory burch handbags
adidas outlet online

Unknown said...

balenciaga
offwhite
adidas nmd
michael kors outlet
yeezy boost
birkin bag
kyrie 4
chrome hearts online
adidas tubular
curry 4