Check out

for a list of industry tools some free some pay for.

Tracefile analyzer tools:

My notes on how to trace:

Tom Kyte’s Show Space

Notes for JDBC connections over firewalls

ORA-600 decoder ring

Wait Events


ASH Queries

My favorite ASH query is aveactn.sql which shows the load on the database graphically like the performance page in OEM but the output is in ASCII. Here is a blog entry on an improved version

ASH queries at  Github


SQL Execution times from ASH


OEM Performance page in ascii art


 MOATS.sql has some ASH collection as well

  • overview:
  • download


S-ASH is a plsql package that collects v$active_session_history on Oracle 7-11 and versions such as Standard Edition and best of all it runs without an additional Oracle licenses.


Active Session History can be difficult to browse and mine for data,  but with graphics it becomes easy. ASHMON is a graphical program that reads ASH data (or SASH data) and visualizes it. ASHMON also reads data from v$sysstat and v$system_event and visualizes it. ASHMON is  written by me and only reads ASH from the databsase but can use S-ASH. For tools that both collect and read ASH check out  ASHVIEWER and LAB128. LAB128 is pay for but does more than just collect ASH and ASHViewer is free but only collects ASH and or views already collect ASH data.

AWR diff’er

I often have to compare to AWR reports and it can be time consuming, thus I wrote a script that will compare the data called (the u stands for u in utlbstat/utlestat !)

One problem though is AWR reports in HTML. My scripts are for TEXT. In the case of HTML I use to convert them to TEXT.

SGA Direct Memory Attach

I haven’t worked on SGA access stuff in several years but it’s one of those subjects that seems to fascinate people. For a good introduction, read Oracle Insights, Tales from the Oaktable. I wrote chapter 6 on how I got started working on SGA attach and how to get started yourself.

  • SGA Access
    • xksuse.sql
    • xksuse.c

statspack shell script

Before AWR came along, I disliked useing statspack and creating tables and inserting data into my monitored database, so I wrote a script that collected the data I want into flatfiles. That script is called ““

These scripts were originally hosted at

I dug up this script recently to do some monitoring and it’s a work in progress:

The script is a shell script to provide a screen view of Oracle statistics like “top.” I last touch this code about12 years ago, but it still seems like a good idea.

ASH Report



select output
from table(
        (select dbid from v$database),
        1, -- instance id
        sysdate - 2/24, -- start date
        sysdate - 1/24  -- end date
        )) ;

AWR Report

List of AWR views

There are two ways to get AWR reports. The easiest is to connect with sqlplus as sys or system and run ?/rdbms/admin/awrrpt.sql :

$ sqlplus / as sysdba
sqlplus> @?/rdbms/admin/awrrpt.sql

The script will prompt you for input. Choose “TEXT” report – it’s easier to parse with (Clive Bostock  also has a AWR to CSV converter based on text format). If you won’t be parsing AWR reports then the HTML version is easier to read and navigate.  The awrrpt.sql will also prompt for the time period to cover. The time period could be a full day or if there are particular hours that have typical workload, then an hour report would be good. The constraint here is that the script “?/rdbms/admin/awrrpt.sql” is on the database machine. If you are not on the database machine and are connecting with SQL*Plus over SQL*Net, then you can run

    (select dbid from v$database),
    1, -- instance id
    124, -- begin id
    125 -- end id ));

The trick here is that you have to know the snapshot ids, which in this example are 124 and 125. To find out snapshot ids you can query

col begin_interval_time for A30
select snap_id, Begin_interval_time from DBA_HIST_SNAPSHOT
order by begin_interval_time ;

---------- ----------------------------
       538 02-MAR-11 PM
       539 02-MAR-11 PM
       540 02-MAR-11 PM

Check out the AWR formatter plugin for Chrome:

AWR Diff’er

Set long 100000
        (select dbid from v$database),
         1,  -- instance id
        (select dbid from v$database),
         1 , -- instance id

ADDM Report Manually

 Set long 100000
select dbms_advisor.get_task_report(task_name)
from   dba_advisor_tasks
where task_id = (
                  select max(t.task_id)
                  from   dba_advisor_tasks t,
                         dba_advisor_log l
                  where t.task_id = l.task_id and
                        l.status = 'COMPLETED' and
                        l.execution_end < sysdate - 2/24 and -- time period
                        t.advisor_name = 'ADDM');


     -- run as system creates plustrace
     grant plustrace to [user]

     set autotrace off
     set autotrace on   -- explain and statistics

     set autotrace traceonly -- runs query, shows explain and stats but doesn't print query results 

     set autotrace on explain
     set autotrace on statistics

     set autotrace traceonly explain -- doesn't run the query
     set autotrace traceonly statistics -- runs query but no results shown


oradebug setmypid
oradebug event 10046 trace name context forever, level 12;
oradebug unlimit
oradebug tracefile_name

alter session set events '10046 trace name context forever, level 12';
alter session set max_dump_file_size = unlimited;

-- to get session id from from current session

NOTE: oradebug uses ospid (setospid) or pid (setorapid) which come from v$process

More on 10046

 pid,   -- oradebug setorapid
          p.spid ospid -- oradebug setospid
from v$process p, v$session s
where s.sid = &1
and p.addr = s.paddr

Rather than using oradebug, you can use DBMS_MONITOR

dbms_monitor.session_trace_enable(session_id => 127,
serial_num => 29,
waits => TRUE,
binds => FALSE)

select sid, serial#,sql_trace,sql_trace_waits, sql_trace_binds from v$session;

DBMS_MONITOR can use client_id, but client_id has to be turned on and TRCSESS has to be use extract trace information from multiple trace files

dbms_monitor.client_id_trace_enable (client_id, TRUE, FALSE)
Trcsess output=<name> client_id=<name>

On logon trigger can be use to trace a session from the beginning

create or replace trigger sys.set_trace
after logon on database
when (user = 'trace_user')
execute immediate 'alter session set statistics_level=all';
execute immediate 'alter session set max_dump_file_size=unlimited';
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
end set_trace;

Here is a process to trace the Oracle batch from a second process so the trace can be started and stopped without interfering with the batch,

In the batch session, run


      SELECT sys_context('USERENV', 'SID') FROM DUAL;

to get the sessions SID. After that the batch can be kicked off.
In a second session as sysdba, run


          p.spid ospid -- oradebug setospid
from v$process p, v$session s
where s.sid = &1
and p.addr = s.paddr

inputting the sessions SID.
Now, with this return value of OSPID, tracing can be set on and off from another process:


oradebug setospid   [ospid]
oradebug unlimit
oradebug event 10046 trace name context forever, level 12;
-- let batch run for a while
oradebug event 10046 trace name context off;
oradebug tracefile_name

after tracing try to parse the file

Flushing buffer cache

 alter session set events 'immediate trace name flush_cache’;
 alter system flush buffer_cache;


needs level 6 snapshot


@?/rdbms/admin/spcreate.sql  -- Install, Connect as SYSDBA
Exec statspack.snap; -- take snapshots before, after, and during load
@?/rdbms/admin/spreport.sql -- Generate Reports

Average IO sizes from ASH

col event for a25
select event,round(min(p3)) mn,
round(avg(p3)) av,
round(max(p3)) mx,
count(*)  cnt
-- from dba_hist_active_sess_history
from v$active_session_history
where  (event like 'db file%' or event like 'direct %')
and event not like '%parallel%'
group by event
order by event

NOTE: because ASH samples, these I/O sizes are skewed towards the larger end but that is not so bad as often I want to know what the largest read I/O sizes are. The skew is much more of a problem for timings, but for IO sizes this can be good information. The above query on I/O sizes is useful. The below query on I/O times is of questionable use (though I include it here as I have used it)

col event for a25
col event for a25
select event,round(min(time_waited)) mn,
round(avg(time_waited)) av,
round(max(time_waited)) mx,
count(*)  cnt
-- from dba_hist_active_sess_history
from v$active_session_history
where  (event like 'db file%' or event like 'direct %')
and event not like '%parallel%'
group by event
order by event

execution plan from display_cursor

use /* + gather_plan_statistics */  hint go gather statistics and use ALLSTATS as last parameter

select * from table(dbms_xplan.display_cursor(sql_id,child_number,options));
select * from table(dbms_xplan.display_cursor('0zh4k4hzzby20'));
select * from table(dbms_xplan.display_cursor('0zh4k4hzzby20',1));
select * from table(dbms_xplan.display_cursor('0zh4k4hzzby20',null,'ADVANCED'));





execution plan from AWR

select * from table(dbms_xplan.display_awr(sql_id, null,null,'ADVANCED'));

Bind Variables

extracting bind variables is something I’ve meant to look into but not spent much time. See

variable x varchar2(10);
exec :x:='X';
col f_sqlid new_value v_sqlid
col f_sqlcn new_value v_sqlcn
col f_binddata new_value v_binddata
select * from dual where dummy=:x;
PREV_SQL_ID f_sqlid,
from v$session
where sid = ( Select sid from v$mystat where rownum=1 ) ;
select bind_data f_binddata from v$sql
where sql_id = '&v_sqlid'
and child_number = &v_sqlcn
SELECT xmltype(other_xml) AS xmlval
FROM v$sql_plan
sql_id = '&v_sqlid' AND
child_number = &v_sqlcn AND
other_xml IS NOT NULL

Tracing a SQL statement – 11g

 alter session set events 'sql_trace [sql_id]';
alter session set events 'sql_trace [sql_id|sql_id]';
alter session set events 'sql_trace [707wu2umpfas7],wait=true,bind=true';
-- run query
alter session set events 'sql_trace [707wu2umpfas7]'off;

Rollback of a killed process


watch used_urec

-- -----------------------------------------------------------------------------------
-- File Name    :
-- Author       : DR Timothy S Hall
-- Description  : Displays undo information on relevant database sessions.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @session_undo
-- Last Modified: 29/03/2005
-- -----------------------------------------------------------------------------------

COLUMN username FORMAT A15

SELECT s.username,
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND    rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

Pfile or SPfile


SELECT DECODE(value, NULL, ‘PFILE’, ‘SPFILE’) “Init File Type” FROM sys.v_$parameter WHERE name = ‘spfile‘;

Connect strings other than via tnsnames.ora

see: ezconnect

CONNECT username/password@[//]host[:port][/service_name]

or long form

sqlplus dbUser/dbPassword@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=remoteServer)(PORT=1521)))(CONNECT_DATA=(SID=dbSid)))’

Tracking specific SQL in AWR (coloring SQL)

exec dbms_workload_repository.add_colored_sql( sql_id );


Generating Dummy data

idea from Dion Cho

create table cacher(c1 char(2000), c2 char(2000), c3 char(2000)) ;
insert into redo_gen
select ‘x’, ‘x’, ‘x’
from dual
connect by level <= 10000;

Clearing Archives

WARNING:  this is for clearning logs on test databases that will never need recovery:

rman target /

allocate channel for maintenance type disk;
delete force noprompt copy of archivelog all completed before ‘sysdate‘;

SQLPLUS / as sysdba

archive log stop;
archive log start;

archive log all;

Flashback query vs Total recal

  • flashback – uses what ever UNDO you have available, flashback limited to 5 days via Tom Kyte – the farther back in time, the slower the query
  • total recall – saves changes for specified tables, issues with total recall such as performance, space and prohibited operations, total recall lab, flashback examples, PPT presentation, PDF presentation, trivadis PDF review and limits
  • 12c Temporal, examples on 12c , Tom Kyte on 12c
  • also see workspace manager


Creating Database link old style

create database link  "SASHREPO"

Documentation on v$ and DBA views:

  • DBA_HIST views

 Shell Vars

Finally a reference for those shell variables that are a problem to search for in google $!, $*.$@ etc





  2. Mark Entwistle
    May 19th, 2011 at 02:25 | #1

    Great tip on creating AWR reports from sqlplus gui – thanks!

  3. Kyle Hailey
    May 27th, 2011 at 15:06 | #2

    From a user:

    I have been playing around with aveact.sql. I created a load, using a crude load script on my dual core cpu laptop.
    I ran the load script on 3 different sessions concurrently. After a few minutes I ran the aveact.sql script and I got output as below:

    —————- —— ——- —————————— —– —–
    27-MAY 14:53:00 1 .02 2 1 0
    27-MAY 14:54:00 1 .00 2 0 0
    27-MAY 14:55:00 23 .02 2 1 0
    27-MAY 14:57:00 40 .22 + 2 12 1
    27-MAY 14:58:00 60 .28 + 2 17
    27-MAY 14:59:00 60 1.57 ++++++++ 2 94
    27-MAY 15:00:00 59 2.10 ++++++++++2+ 123 3
    27-MAY 15:01:00 57 2.85 ++++++++++2+++++ 169 2
    27-MAY 15:02:00 57 3.00 ++++++++++2++++++ 179 1
    27-MAY 15:03:00 57 2.98 ++++++++++2++++++ 177 2
    27-MAY 15:04:00 56 3.00 ++++++++++2++++++ 178 2
    27-MAY 15:05:00 56 3.07 ++++++++++2++++++ 184 0
    27-MAY 15:06:00 55 2.95 ++++++++++2++++++ 176 1
    27-MAY 15:07:00 54 2.82 ++++++++++2+++++ 169 0
    27-MAY 15:08:00 55 2.98 ++++++++++2++++++ 178 1
    27-MAY 15:09:00 8 .42 ++ 2 25 0

    The thing that is confusing me is the CPU measurement. How can it be so far above the #CPUs?
    Enterprise manager is broken on my laptop so I can’t double check there.
    I was wondering if you may have seen this before?

    It means there is more demand for CPU than there is CPU available.
    It’s like the run queue on UNIX.
    Clearly shows that there is a bottleneck on CPU.
    In 11g OEM you will see CPU time and WAIT for CPU time. Wait for CPU time is demand for CPU minus the actual CPU used. CPU used is measured with OS counters where as demand for CPU, at least below, is measured from ASH by counting sessions who are ready to run on the CPU.

    Best Wishes

  4. Maddy
    July 17th, 2012 at 03:30 | #3

    Sir – I tried to use your [ this script is not available fro download] / for pulling out the difference between two AWR text reports… It generates nothing. Can you please let us know the correct script with clear usage …

  5. July 17th, 2012 at 04:43 | #4

    Hi Maddy, you can send me your awr reports in text format to and I will test them out as I have time.
    – Kyle

You must be logged in to post a comment.