top social plugins for wordpress

  Tracefile analyzer tools: 
 Tom Kyte's Show Space 
 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


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.

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 see:
 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 from: 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;

for safer approach see:

Oracle Inventory

$OH/oui/bin/runInstaller -attachHome ORACLE_HOME_NAME="ORA12c" ORACLE_HOME="/u01/app/oracle/product/12.1.0"  -ignoreSysPrereqs

Flashback query vs Total recal   Creating Database link old style
create database link  "SASHREPO"

Documentation on v$ and DBA views:  Shell Vars Finally a reference for those shell variables that are a problem to search for in google $!, $*.$@ etc