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 using dbms_monitor

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: