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:
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 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: