Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Saturday, August 15, 2015

Oracle how to use tkprof


Using TKPROF to view the sql trace file data:



SQL> break on table_name skip 1 on index_name


Current Indexex on EMPLOYEES table:

SQL> select table_name,index_name,column_name from user_ind_columns where table_name='EMPLOYEES';


TABLE_NAME              INDEX_NAME             COLUMN_NAME
------------------------------ ------------------------------ -----------------------------------
EMPLOYEES       EMP_EMAIL_UK             EMAIL
                 EMP_EMP_ID_PK                      EMPLOYEE_ID
                 EMP_DEPARTMENT_IX             DEPARTMENT_ID
                 EMP_JOB_IX                               JOB_ID
                 EMP_MANAGER_IX                    MANAGER_ID
                 EMP_NAME_IX                            LAST_NAME
                                                                      FIRST_NAME


SQL> drop index  EMP_NAME_IX;

Index dropped.

SQL> alter session set tracefile_identifier='TKPROF_TEST';

Session altered.

SQL> set autot off

SQL> alter session set sql_trace=true;

Session altered.

SQL> select employee_id,first_name,last_name from employees where first_name ='Peter';

EMPLOYEE_ID FIRST_NAME        LAST_NAME
----------- -------------------- -------------------------
   144 Peter        Vargas
   150 Peter        Tucker
   152 Peter        Hall

SQL> alter session set sql_trace=false;

Session altered.


SQL> show parameter diag

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest             string     /u01/app/oracle

[oracle@collabn1 ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/

[oracle@collabn1 trace]$ find . -type f -name "*TKPROF_TEST*"
./orcl_ora_8871_TKPROF_TEST.trc
./orcl_ora_8871_TKPROF_TEST.trm



sample trace file content:
[oracle@collabn1 trace]$ vi ./orcl_ora_8871_TKPROF_TEST.trc

trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8871_TKPROF_TEST.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0.2/SHAIKPROD
System name:    Linux
Node name:      collabn1.shaiksameer
Release:        3.8.13-44.1.1.el6uek.x86_64
Version:        #2 SMP Wed Sep 10 06:10:25 PDT 2014
Machine:        x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 41
Unix process pid: 8871, image: oracle@collabn1.shaiksameer (TNS V1-V3)


*** 2015-08-15 16:31:17.838
*** SESSION ID:(49.22) 2015-08-15 16:31:17.838
*** CLIENT ID:() 2015-08-15 16:31:17.838
*** SERVICE NAME:(SYS$USERS) 2015-08-15 16:31:17.838
*** MODULE NAME:(SQL*Plus) 2015-08-15 16:31:17.838
*** ACTION NAME:() 2015-08-15 16:31:17.838

=====================
PARSING IN CURSOR #7 len=32 dep=0 uid=85 oct=42 lid=85 tim=1439670677838634 hv=1569151342 ad='7fa5e865fe40' sqlid='4tk6t8tfsfqbf'
alter session set sql_trace=true
END OF STMT
EXEC #7:c=0,e=37,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1439670677837153

*** 2015-08-15 16:31:44.988
CLOSE #7:c=0,e=8,dep=0,type=0,tim=1439670704988770



[oracle@collabn1 trace]$ tkprof ./orcl_ora_8871_TKPROF_TEST.trc out.txt

TKPROF: Release 11.2.0.1.0 - Development on Sat Aug 15 16:37:54 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


[oracle@collabn1 trace]$ vi out.txt

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

SQL ID: bk0z2yhg4dq6u
Plan Hash: 1445457117
select employee_id,first_name,last_name
from
employees where first_name ='Peter'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          6          8          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          6          8          0           3

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

Rows     Row Source Operation
-------  ---------------------------------------------------
     3  TABLE ACCESS FULL EMPLOYEES (cr=8 pr=6 pw=0 time=0 us cost=3 size=57 card=3)

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

SQL> create index  EMP_NAME_IX on employees(first_name,last_name);

Index created.

SQL> select table_name,index_name,column_name from user_ind_columns where table_name='EMPLOYEES';

TABLE_NAME              INDEX_NAME             COLUMN_NAME
------------------------------ ------------------------------ -----------------------------------
EMPLOYEES              EMP_EMAIL_UK             EMAIL
                 EMP_EMP_ID_PK             EMPLOYEE_ID
                 EMP_DEPARTMENT_IX          DEPARTMENT_ID
                 EMP_JOB_IX             JOB_ID
                 EMP_MANAGER_IX             MANAGER_ID
                 EMP_NAME_IX             LAST_NAME
                                FIRST_NAME


7 rows selected.


[oracle@collabn1 trace]$ find . -type f -name "*AFTER_INDEX*"
./orcl_ora_22841_AFTER_INDEX.trc
./orcl_ora_22841_AFTER_INDEX.trm


[oracle@collabn1 trace]$ tkprof ./orcl_ora_22841_AFTER_INDEX.trc out1.txt

TKPROF: Release 11.2.0.1.0 - Development on Sat Aug 15 16:49:28 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


[oracle@collabn1 trace]$ vi out1.txt


Parsing user id: 85
********************************************************************************

SQL ID: bk0z2yhg4dq6u
Plan Hash: 2077747057
select employee_id,first_name,last_name
from
employees where first_name ='Peter'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           3

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

Rows     Row Source Operation
-------  ---------------------------------------------------
     3  TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=4 pr=0 pw=0 time=0 us cost=2 size=57 card=3)
     3   INDEX RANGE SCAN EMP_NAME_IX (cr=2 pr=0 pw=0 time=12 us cost=1 size=0 card=3)(object id 75140)

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

No comments: