How to trace query in Oracle for performance tuning

1. check location for trace file
SQL> show parameter user_dump_dest
NAME                                 TYPE        VALUE
———————————— ———– ——————————
user_dump_dest                       string      c:\app\administrator\diag\rdbms\ora11\ora11\trace

2. set environment in a session
SQL> alter session set TIMED_STATISTICS = true;
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = ‘hpsupport_trace_id’;
SQL> alter session set SQL_TRACE = true;

3. execute queries
SQL> SELECT m1.”UNIQUE_KEY”,m1.”FILE_NAME”,m1.”NAME”,m1.”COMPONENT”
FROM APPROVALM1 m1 JOIN APPROVALA1 a1
ON (((m1.”FILE_NAME” = a1.”FILE_NAME”)
OR (m1.”FILE_NAME” IS NULL AND a1.”FILE_NAME” IS NULL)) AND ((m1.”COMPONENT” = a1.”COMPONENT”)
OR (m1.”COMPONENT” IS NULL AND a1.”COMPONENT” IS NULL)) AND ((m1.”UNIQUE_KEY” = a1.”UNIQUE_KEY”)
OR (m1.”UNIQUE_KEY” IS NULL AND a1.”UNIQUE_KEY” IS NULL)) AND ((m1.”NAME” = a1.”NAME”)
OR (m1.”NAME” IS NULL AND a1.”NAME” IS NULL)))
WHERE ((m1.”APPROVAL_STATUS”=’pending’ and
(m1.”APPROVALS_RECORD” IS NULL or m1.”APPROVALS_RECORD”=’t’)
and (m1.”FILE_NAME”=’cm3r’ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’,’Application’,’Field Support (North America)’,’Hardware’,’Network’,’Service Manager’) or
m1.”FILE_NAME”=’cm3t’ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’,’Application’,’Field Support (North America)’,’Hardware’,’Network’,’Service Manager’) or
m1.”FILE_NAME”=’ocm1′ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’) or m1.”FILE_NAME”=’incidents’
and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’) or m1.”FILE_NAME”=’svcCartItem’ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’))))
ORDER BY m1.”UNIQUE_KEY” ASC,m1.”FILE_NAME” ASC,m1.”NAME” ASC,m1.”COMPONENT” ASC

4. close trace and run TKPROF 
SQL> alter session set SQL_TRACE = false;

file is created in
c:\app\administrator\diag\rdbms\ora11\ora11\trace\ora11_ora_4448_hpsupport_trace_id.trc
c:\app\administrator\diag\rdbms\ora11\ora11\trace\ora11_ora_4448_hpsupport_trace_id.trm
CMD> cd c:\app\administrator\diag\rdbms\ora11\ora11\trace

generate result file
CMD> tkprof  ora11_ora_4448_hpsupport_trace_id.trc result1.txt

5. verify results
cmd> notepad result1.txt
example)
********************************************************************************

 SELECT m1.”UNIQUE_KEY”,m1.”FILE_NAME”,m1.”NAME”,m1.”COMPONENT”
FROM APPROVALM1 m1 JOIN APPROVALA1 a1
ON (((m1.”FILE_NAME” = a1.”FILE_NAME”)
OR (m1.”FILE_NAME” IS NULL AND a1.”FILE_NAME” IS NULL)) AND ((m1.”COMPONENT” = a1.”COMPONENT”)
OR (m1.”COMPONENT” IS NULL AND a1.”COMPONENT” IS NULL)) AND ((m1.”UNIQUE_KEY” = a1.”UNIQUE_KEY”)
OR (m1.”UNIQUE_KEY” IS NULL AND a1.”UNIQUE_KEY” IS NULL)) AND ((m1.”NAME” = a1.”NAME”)
OR (m1.”NAME” IS NULL AND a1.”NAME” IS NULL)))
WHERE ((m1.”APPROVAL_STATUS”=’pending’ and
(m1.”APPROVALS_RECORD” IS NULL or m1.”APPROVALS_RECORD”=’t’)
and (m1.”FILE_NAME”=’cm3r’ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’,’Application’,’Field Support (North America)’,’Hardware’,’Network’,’Service Manager’) or
m1.”FILE_NAME”=’cm3t’ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’,’Application’,’Field Support (North America)’,’Hardware’,’Network’,’Service Manager’) or
m1.”FILE_NAME”=’ocm1′ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’) or m1.”FILE_NAME”=’incidents’
and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’) or m1.”FILE_NAME”=’svcCartItem’ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’))))
ORDER BY m1.”UNIQUE_KEY” ASC,m1.”FILE_NAME” ASC,m1.”NAME” ASC,m1.”COMPONENT” ASC

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.09       0.10          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.04       0.19        198       1960          0           8
——- ——  ——– ———- ———- ———- ———-  ———-
total        4      0.14       0.30        198       1960          0           8

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

Rows     Row Source Operation
——-  —————————————————
8  SORT ORDER BY (cr=1960 pr=198 pw=0 time=0 us cost=695 size=2030 card=10)
8   CONCATENATION  (cr=1960 pr=198 pw=0 time=224 us)
0    NESTED LOOPS  (cr=190 pr=187 pw=0 time=0 us)
0     NESTED LOOPS  (cr=190 pr=187 pw=0 time=0 us cost=68 size=203 card=1)
0      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=187 pw=0 time=0 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=149 card=1)
0    NESTED LOOPS  (cr=201 pr=5 pw=0 time=0 us)
0     NESTED LOOPS  (cr=201 pr=5 pw=0 time=0 us cost=70 size=203 card=1)
8      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=63 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=11 pr=5 pw=0 time=0 us cost=1 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=149 card=1)
0    NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us)
0     NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us cost=68 size=203 card=1)
0      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=0 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=149 card=1)
0    NESTED LOOPS  (cr=201 pr=0 pw=0 time=0 us)
0     NESTED LOOPS  (cr=201 pr=0 pw=0 time=0 us cost=70 size=203 card=1)
8      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=7 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=11 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=149 card=1)
0    NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us)
0     NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us cost=68 size=203 card=1)
0      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=0 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=149 card=1)
0    NESTED LOOPS  (cr=201 pr=0 pw=0 time=0 us)
0     NESTED LOOPS  (cr=201 pr=0 pw=0 time=0 us cost=70 size=203 card=1)
8      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=14 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=11 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=149 card=1)
0    NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us)
0     NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us cost=68 size=203 card=1)
0      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=0 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=149 card=1)
0    NESTED LOOPS  (cr=201 pr=0 pw=0 time=0 us)
0     NESTED LOOPS  (cr=201 pr=0 pw=0 time=0 us cost=71 size=203 card=1)
8      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=7 us cost=69 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=11 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=149 card=1)
0    NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us)
0     NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us cost=68 size=203 card=1)
0      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=0 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=149 card=1)
8    NESTED LOOPS  (cr=206 pr=6 pw=0 time=210 us)
8     NESTED LOOPS  (cr=201 pr=0 pw=0 time=133 us cost=71 size=203 card=1)
8      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=28 us cost=69 size=54 card=1)
8      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=11 pr=0 pw=0 time=0 us cost=1 size=0 card=3)(object id 74630)
8     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=5 pr=6 pw=0 time=0 us cost=2 size=149 card=1)

********************************************************************************

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s