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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

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


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]


My Cloud Certifications:

AWS Certified Solutions Architect Associate

Oracle Cloud Infrastructure 2018 Certified Architect Associate.

Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.

Oracle Database Cloud Administrator Certified Professional.

Oracle Database Cloud Service Operations Certified Associate.

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: