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:
Post a Comment