Using DBMS_MONITOR:
SQL> show user
USER is "SYS"
SQL> select sid,serial# from v$session where username='HR';
SID SERIAL#
---------- ----------
49 25
SQL> exec dbms_monitor.session_trace_enable(session_id=>49,-
> serial_num=>25,waits=>true,binds=>true);
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> show user
USER is "HR"
SQL> drop index EMP_NAME_IX;
Index dropped.
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> create index EMP_NAME_IX on employees(first_name,last_name);
Index created.
SQL> select employee_id,first_name,last_name from employees where first_name ='Peter';
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
152 Peter Hall
150 Peter Tucker
144 Peter Vargas
SQL> select sid,serial# from v$session where username='HR';
SID SERIAL#
---------- ----------
49 25
SQL> show user
USER is "SYS"
SQL> exec dbms_monitor.session_trace_disable(session_id=>49,serial_num=>25);
PL/SQL procedure successfully completed.
SQL> !
[oracle@collabn1 trace]$ vi orcl_ora_22841_AFTER_INDEX.trc
[oracle@collabn1 trace]$ tkprof orcl_ora_22841_AFTER_INDEX.trc out3.txt
TKPROF: Release 11.2.0.1.0 - Development on Sat Aug 15 17:04:34 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
[oracle@collabn1 trace]$ vi out3.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)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.00 0.00
db file scattered read 1 0.00 0.00
SQL*Net message from client 2 22.40 22.40
********************************************************************************
Parsing user id: 85 (recursive depth: 1)
********************************************************************************
SQL ID: 7f05xuvxx6rdw
Plan Hash: 1708097244
create index EMP_NAME_IX on employees(first_name,last_name)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.01 7 9 52 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 7 10 52 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85
Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX BUILD NON UNIQUE EMP_NAME_IX (cr=22 pr=8 pw=0 time=0 us)(object id 0)
107 SORT CREATE INDEX (cr=7 pr=0 pw=0 time=212 us)
107 TABLE ACCESS FULL EMPLOYEES (cr=7 pr=0 pw=0 time=0 us cost=3 size=1605 card=107)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 7 0.00 0.00
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 4.14 4.14
********************************************************************************
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 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 8 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 8 0 6
Misses in library cache during parse: 2
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)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
No comments:
Post a Comment