Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

session_trace_enable tips

Oracle Database Tips by Donald BurlesonSeptember 2, 2015


In the example below, a table is created without any index and without statistics collection.  Then a trace is enabled in a session using the dbms_monitor package. After that, the tkprof utility  will be used to analyze the gathered data, getting the execution plan the query executed.

 

After analyzing and finding the query's full table scan, a proper index will be created and statistics collected. Finally, the query is executed again with a much faster response time. Once complete, the trace in session is disabled using the dbms_monitor package.

Also see my notes on database_trace_enable and session_trace_enable tips

 

<  Code  5.8 - dbms_monitor.sql

SQL>

conn

 pkg@dbms

 

Connected to Oracle 11g Enterprise Edition Release 11.2.0.1.0

Connected as pkg

 

SQL>

show

 user

 

User is "pkg"

 

--Create a test table
create table
   test_dbms_monitor
as
select
   *
from
dba_objects;

--Make the table bigger (run this command some times to increase the table size)
insert into
   test_dbms_monitor
select
   *
from
test_dbms_monitor;

commit;

--Enable trace in session using dbms_monitor
begin
dbms_monitor.session_trace_enable;
end;
/
begin

--Here, replace with your session_id and serial_num if disabling trace on another session

dbms_monitor.session_trace_disable(session_id => 33,serial_num => 85);
end;

 

--Notice how you can specify the session where tracing will be enabled or disabled, it's not necessarily limited to yours, thus, you don't need to make any changes to your applications to be able to trace what they are doing./

 

The trace generated on the directory can be found specified by user_dump_dest parameters like the one below:

 

[ora11g@dbms trace]$ ls -la

/u01/app/oracle/diag/rdbms/dbms/dbms/trace/dbms_ora_3957.trc
-rw-r----- 1 ora11g oinstall 97126 Nov  9 14:46 /u01/app/oracle/diag/rdbms/dbms/dbms/trace/dbms_ora_3957.trc

 

Now let's run a query on the table created and check the generated trace. To check the explain plan, use the tkprof utility. After that, analyze the explain plan of the query executed.

 

--Run a query on table created  
select
   count(*)
from
   test_dbms_monitor t
where
   t.object_id = 3289;

 

Executed in 32,766 seconds

 

Run the tkprof to generate the file that will be analyzed:

 

 [ora11g@dbms trace]$ tkprof dbms_ora_3957.trc dbms_ora_3957.txt explain=pkg/pkg@dbms sort=exeela


Now locate the query and see a full table scan on this table; and then assume that we conclude that an index is needed to improve query performance.

 

select
   count(*)
from
   test_dbms_monitor t
where
   t.object_id = 3289

 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.00       0.00          0          1          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch        5      6.59      34.12     128593     149249          1           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       15      6.59      34.12     128593     149250          1           5

Misses in library cache during parse: 1
Optimizer mode: all_rows
Parsing user id: 46  (pkg)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  sort aggregate (cr=21487 pr=21477 pw=0 time=0 us)
     64   table access full test_dbms_monitor (cr=21487 pr=21477 pw=0 time=11323557 us cost=5965 size=4537 card=349)
Rows     Execution Plan
-------  ---------------------------------------------------
      0  select statement   node: all_rows
      1   sort (aggregate)
     64    table access (full) of 'test_dbms_monitor' (table)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      10        0.00          0.00
  SQL*Net message from client                    10        0.01          0.05
  enq: KO - fast object checkpoint                2        1.01          1.05
  Disk file operations I/O                        1        0.00          0.00
  direct path read                             3849        0.35         26.82
  asynch descriptor resize                        5        0.00          0.00
  db file parallel read                           1        0.16          0.16
********************************************************************************

At this time, the index on column object_id is created and statistics for the table and index are gathered.

 

create index
   pkg.idx_obj_id
on
   pkg.test_dbms_monitor (object_id)
pctfree 10
initrans 2
maxtrans 255 
logging
  storage(
  buffer_pool default
  flash_cache default
  cell_flash_cache default)
  tablespace "tbx_idx";

 

--Gather statistics for the table and indexe
begin
dbms_stats.gather_table_stats(
   ownname => 'pkg',
   tabname => 'test_dbms_monitor',
   estimate_percent => 30,
   degree => 4,
   granularity => 'ALL',
   cascade => TRUE,
   method_opt =>'for all indexed columns');
end;
/

 

Run the query again and look at the time and the plan at the trace file. Note that tkprof will need to be run again. It is also advised to clean up the buffer cache to make sure data is not cached, so the impact of the index that was just built for the query performance should now be greatly improved thanks to the index and statistics collection that was done based on the findings with the dbms_monitor package.

 

alter system flush buffer_cache;

select
   count(*)
from
   test_dbms_monitor t
where
   t.object_id = 3289;

 

Executed in 0,031 seconds

 

Now the new explain plan that is using the index created can be seen and the new outstanding execution time.

 

select
   count(*)
from
   test_dbms_monitor t
where
   t.object_id = 3289

 

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        1      0.00       0.03          4          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.04          4          4          0           1

Misses in library cache during parse: 1
Optimizer mode: all_rows
Parsing user id: 46  (pkg)



Rows     Row Source Operation
-------  ---------------------------------------------------
      1  sort aggregate (cr=4 pr=4 pw=0 time=0 us)
    128   index range scan idx_obj_id (cr=4 pr=4 pw=0 time=508 us cost=3 size=640 card=128)(object id 26166)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  select statement   mode: all_rows
      1   sort (aggregate)
    128    index mode: analyzed (range scan) of 'idx_obj_id' (index)


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                     2        0.00          0.00
  db file sequential read                         4        0.02          0.03
********************************************************************************

 

Lastly, use the dbms_monitor package again to disable the trace in session:

begin
dbms_monitor.session_trace_disable;
end;
/

 
 
 
Inside the DBMS Packages

The DBMS packages form the foundation of Oracle DBA functionality.  Now, Paulo Portugal writes a landmark book Advanced Oracle DBMS Packages:  The Definitive Reference

This is a must-have book complete with a code depot of working examples for all of the major DBMS packages.  Order directly from Rampant and save 30%. 
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster