The tool was devised by Craig from ORAPUB which is excellent to monitor what happens in session real time.
1.First I need to down OSM tool kit from ORAPUB. First we need to run the following scripts to create some pre-requisite tables under sys/system schema.
a)osmprep.sql
-- ******************************************************
-- * Copyright Notice : (c)1998-2014,2015 OraPub, Inc.
-- * Filename : osmprep.sql
-- * Author : Craig A. Shallahamer
-- * Original : 17-AUG-98
-- * Last Modified : 23-Dec-2014
-- * Description : OSM preperation script
-- * Usage : start osmprep.sql
-- ******************************************************
prompt
prompt OraPub System Monitor - Interactive (OSM-I) installation script.
prompt
prompt (c)1998,1999,2000,2001,2002,2003,2004,2005,2006,2007,2008 by OraPub, Inc.
prompt (c)2009,2010,2011,2012,2013,2014,2015 by OraPub, Inc.
prompt
prompt There is absolutely no guarantee with this software. You may
prompt use this software at your own risk, not OraPub's risk.
prompt No value is implied or stated.
prompt
prompt You may need to run $ORACLE_HOME/rdbms/admin/catblock.sql
prompt
prompt Connect as the user who will be using the OSM.
prompt
prompt Press ENTER to continue.
accept x
prompt
prompt Creating interim tables for delta calculations
prompt
-- Just for rtsysx8.sql
drop table o$sysstat;
create table o$sysstat as select * from v$sysstat where 1=0;
--
drop table system_event_snap;
create table system_event_snap as select * from v$system_event where 1=0;
drop table event_histogram_snap;
create table event_histogram_snap as select * from v$event_histogram where 1=0;
drop table op_sys_time_model_snap;
create table op_sys_time_model_snap as select * from v$sys_time_model where 1=0;
drop table op_os_cpu_snap;
create table op_os_cpu_snap as select stat_name,value from v$osstat where 1=0;
drop table o$system_event_snap;
create table o$system_event_snap as select * from v$system_event where 1=0;
drop table o$sys_time_model;
create table o$sys_time_model as select * from v$sys_time_model where 1=0;
drop table o$rtsysx_sql;
create table o$rtsysx_sql (
sql_id varchar2(13),sql_address raw(8),cpu_time number, elapsed_time number,
sorts number, executions number, parse_calls number, disk_reads number,
buffer_gets number, rows_processed number, sql_text varchar2(1000)
);
create unique index o$rtsysx_sql on o$rtsysx_sql (sql_id);
-- rtsysx8.sql
drop table o$rtsysx_sql8;
create table o$rtsysx_sql8 (
sql_address raw(8),cpu_time number, elapsed_time number,
sorts number, executions number, parse_calls number, disk_reads number,
buffer_gets number, rows_processed number, sql_text varchar2(1000)
);
create unique index o$rtsysx_sql8 on o$rtsysx_sql8 (sql_address);
-- rtcx.sql
drop table o$rtcx_snap;
create table o$rtcx_snap (category varchar2(100),time_cs number, waits number);
-- collection scripts (e.g., rss.sql, ash scripts,) need these two functions
create or replace function get_interval_s_fnc(i_intrvl interval day to second) return number is
begin
return extract(day from i_intrvl) * 86400 +
extract(hour from i_intrvl) * 3600 +
extract(minute from i_intrvl) * 60 +
extract(second from i_intrvl);
exception
when others then begin return null;
end;
end;
/
prompt To categorize wait events for OSM reports, run:
prompt
prompt For pre-10g systems, run event_type_nc.sql
prompt For 10g and beyond, run event_type.sql
prompt
prompt Once you cateogrize the wait events, the installation is complete.
prompt
prompt Menu is osmi.sql
prompt
prompt ENJOY!!
prompt
b)Then I will run another script
-- ********************************************************************
-- * Copyright Notice : (c)2001-2014 OraPub, Inc.
-- * Filename : event_type.sql - For 10g+ databases!!!!!
-- * Author : Craig A. Shallahamer
-- * Original : 11-may-01
-- * Last Update : 19-May-2015 (dealing with I/O events)
-- * Description : event_type.sql - Loads "event type" table
-- * which is used by other scripts.
-- * Usage : start event_type.sql
-- * This is usually run (manually) after osmprep.sql
-- ********************************************************************
prompt
prompt file: event_type.sql for Oracle 10g and beyond...
prompt
prompt About to categorize wait events for OSM reports.
prompt
prompt Press ENTER to re-create the o$event_type table.
accept x
-- Event TYPES are defined as follows:
-- ior - IO read related wait
-- iow - IO write related wait
-- other - "real" but not explicitly categorized
-- idle/bogus - idle events, usually not useful
drop table o$event_type
/
create table o$event_type (
event varchar2(64),
type varchar2(64)
)
/
insert into o$event_type
select name,'other'
from v$event_name
where wait_class in ('Administrative','Application','Cluster','Concurrency',
'Configuration','Other','Scheduler','Queuing','Scheduler')
/
insert into o$event_type
select name,'bogus'
from v$event_name
where wait_class in ('Idle','Network')
/
insert into o$event_type
select name,'ior'
from v$event_name
where wait_class in ('Commit','System I/O','User I/O')
and name like '%read%'
/
insert into o$event_type
select name,'iow'
from v$event_name
where wait_class in ('Commit','System I/O','User I/O')
and name like '%write%'
/
insert into o$event_type
select name, 'other' from v$event_name
minus
select event, 'other' from o$event_type
/
-----------------------------------------------------------------------------------------
----- DO NOT REMOVE THE BELOW LINES as they make adjustments to the broad inserts above.
-----------------------------------------------------------------------------------------
-- select 'update o$event_type set type=''iow'' where event = '''||name||''';' from v$event_name where name like '%I/O%';
-- The below is not perfect cleary, but probably good enough... you still must think...
update o$event_type set type='iow' where event = 'Parameter File I/O';
update o$event_type set type='iow' where event = 'Disk file operations I/O';
update o$event_type set type='iow' where event = 'Disk file I/O Calibration';
update o$event_type set type='iow' where event = 'Datapump dump file I/O';
update o$event_type set type='iow' where event = 'dbms_file_transfer I/O';
update o$event_type set type='iow' where event = 'DG Broker configuration file I/O';
update o$event_type set type='iow' where event = 'Log archive I/O';
--update o$event_type set type='iow' where event = 'RMAN backup & recovery I/O';
update o$event_type set type='iow' where event like 'RMAN backup % recovery I/O';
update o$event_type set type='iow' where event = 'Standby redo I/O';
update o$event_type set type='iow' where event = 'RMAN Disk slave I/O';
update o$event_type set type='iow' where event = 'RMAN Tape slave I/O';
update o$event_type set type='iow' where event = 'DBWR slave I/O';
update o$event_type set type='iow' where event = 'LGWR slave I/O';
update o$event_type set type='iow' where event = 'Archiver slave I/O';
update o$event_type set type='iow' where event = 'db file async I/O submit';
update o$event_type set type='iow' where event = 'concurrent I/O completion';
update o$event_type set type='iow' where event = 'Archive Manager file transfer I/O';
--update o$event_type set type='iow' where event = 'resmgr:large I/O queued';
--update o$event_type set type='iow' where event = 'resmgr:small I/O queued';
update o$event_type set type='iow' where event = 'utl_file I/O';
update o$event_type set type='iow' where event = 'TEXT: File System I/O';
update o$event_type set type='iow' where event = 'ASM Fixed Package I/O';
update o$event_type set type='iow' where event = 'ASM Staleness File I/O';
update o$event_type set type='iow' where event = 'Data Guard: RFS disk I/O';
update o$event_type set type='iow' where event = 'free buffer waits';
update o$event_type set type='other' where event like 'latch%';
update o$event_type set type='other' where event like 'enq%';
update o$event_type set type='other' where event like 'cursor%';
update o$event_type set type='iow' where event like 'log%sync%';
update o$event_type set type='iow' where event like 'log%write%';
update o$event_type set type='iow' where event like 'db%write%';
update o$event_type set type='iow' where event like 'log%file%switch%';
update o$event_type set type='iow' where event like 'LGWR%';
update o$event_type set type='ior' where event like 'log%read%';
update o$event_type set type='ior' where event like 'db%read%';
update o$event_type set type='iow' where event like 'i/o%slave%wait';
update o$event_type set type='iow' where event = 'write complete waits';
update o$event_type set type='bogus' where event = 'wait for unread message on broadcast channel';
update o$event_type set type='bogus' where event = 'wait for unread message on multiple broadcast channels';
--update o$event_type set type='bogus' where event like 'db%file%async%submit%';
-- update o$event_type set type='bogus' where event like 'DLM%cross%inst%call%completion';
update o$event_type set type='bogus' where event like 'rdbms ipc reply';
-- for 12c
update o$event_type set type='bogus' where event like 'LGWR worker group idle';
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
commit;
col name format a50 trunc
col wait_class format a20
col type format a20
prompt
prompt OraPub Categorization Summary
prompt ------------------------------------------------
select distinct type,
count(*)
from o$event_type
group by type
order by type
/
select count(*) from o$event_type
/
prompt
prompt Oracle Categorization Summary
prompt ------------------------------------------------
select distinct wait_class, count(*) from v$event_name group by wait_class order by wait_class
/
select count(*) from v$event_name
/
2.Now the following script can be saved under /home/oracle/rss.sql
-- ******************************************************
-- * Copyright Notice : (c)2014 OraPub, Inc.
-- * Filename : rss.sql - Realtime Session Sampler
-- * Author : Craig A. Shallahamer
-- * Original : 07-Jun-2014
-- * Last Modified : 15-Jul-2014
-- * Description : Collect and display session activity in realtime
-- * Usage : @rss sid_low sid_high serial#_low serial#_high cpu|wait|% event%|% delay_s
-- * @rss 0 999999 0 999999 % % 1
-- * @rss 0 999999 0 999999 cpu % 1
-- * @rss 0 999999 0 999999 wait % 1
-- * @rss 0 999999 0 999999 wait db%file%seq% 1
-- * @rss 328 328 1 1 % % 0.25
-- ******************************************************
set echo off verify off heading off
--set echo on verify on heading on
def sidLow=&1
def sidHigh=&2
def serLow=&3
def serHigh=&4
def state=&5
def wePartial=&6
def delay=&7
prompt OraPub Realtime Session Sampler - collection and display
prompt
prompt Every &delay second(s), session ID from &sidLow to &sidHigh and serial# from &serLow to &serHigh
prompt in a &state state will be sampled and displayed.
prompt When waiting, only display when wait event is like &wePartial
prompt
prompt Output will be written to the /tmp/rss_sql.txt file.
prompt To stream output in realtime, in another window do: tail -f /tmp/rss_sql.txt
prompt
accept x prompt "To begin sampling press ENTER. Otherwise break out now."
create or replace directory temp_dir as '/tmp';
prompt To stop sampling, break out (e.g., CNTRL-C)
prompt
prompt Sampling started...
set serveroutput on
begin
declare
type cv_typ is ref cursor;
cv cv_typ;
sql_to_run_v varchar2(999);
fHandle UTL_FILE.FILE_TYPE;
cntr_v number;
next_sample_time_ts_var timestamp;
current_timestamp_v timestamp;
sid_v number;
serial#_v number;
username_v varchar2(30);
state_v varchar2(19);
event_v varchar2(64);
wecat_v varchar2(64);
sql_id_v varchar2(13);
p1_v number;
p2_v number;
p3_v number;
begin
dbms_session.set_identifier('osm:i rss.sql');
fHandle := UTL_FILE.FOPEN('TEMP_DIR', 'rss_sql.txt', 'W');
UTL_FILE.PUT_LINE(fHandle, 'Starting sampling...');
utl_file.fflush(fHandle);
cntr_v := 0;
while 1 = 1
loop
cntr_v := cntr_v + 1;
select current_timestamp + interval '0 00:00:&delay' day to second
into next_sample_time_ts_var
from dual;
begin
open cv for
select
current_timestamp,
sid,
serial#,
decode(username,'','-',username),
decode(state,'WAITING','WAIT','CPU '),
nvl(sql_id,'-'),
decode(state,'WAITING',decode(o.type,'bogus','idle',o.type),'-'),
decode(state,'WAITING',vs.event,'-') ,
decode(state,'WAITING',p1,''),
decode(state,'WAITING',p2,''),
decode(state,'WAITING',p3,'')
from v$session vs,
o$event_type o
where sid between &sidLow and &sidHigh
and serial# between &serLow and &serHigh
and decode(state,'WAITING','WAIT','CPU') like upper('&state%')
and vs.event like '&wePartial%'
and vs.event = o.event(+)
and o.type != 'bogus';
loop
fetch cv into
current_timestamp_v,
sid_v,
serial#_v,
username_v,
state_v,
sql_id_v,
wecat_v,
event_v,
p1_v, p2_v, p3_v
;
exit when cv%NOTFOUND;
UTL_FILE.PUT_LINE(fHandle, lpad(trim(cntr_v),3,' ')||' '|| to_char(current_timestamp_v,'HH24:MI:SS:FF3')||' '||lpad(trim( sid_v ),5,' ')||lpad(trim(serial#_v),6,' ')||' '||lpad(trim(username_v),10,' ')||' '||state_v||' '||rpad(trim(sql_id_v),20,' ')||' '||rpad(trim(wecat_v),5,' ')||' '||rpad(trim(event_v),30,' ')||' ['||p1_v||','||p2_v||','||p3_v||']');
utl_file.fflush(fHandle);
end loop;
EXCEPTION when others then
cntr_v := cntr_v + 1;
end;
dbms_lock.sleep( greatest( get_interval_s_fnc( next_sample_time_ts_var - current_timestamp ),0));
end loop;
UTL_FILE.PUT_LINE(fHandle, 'Ending sampling...');
UTL_FILE.FCLOSE(fHandle);
end;
end;
/
3.Now execute the script to take sames of either individual user or many users
Situation: I want to sample a single session (sid:10 serial:50) once every five seconds.
SQL>@rss.sql 10 10 50 50 % % 5.0
Situation: I want to essentially stream a single session's (sid:10 serial:50) activity.
SQL>@rss.sql 10 10 50 50 % % 0.125
Situation: I want to see what sessions are waiting for an row level lock while sampling once every second.
SQL>@rss.sql 0 99999 0 99999 wait enq%tx%row% 1.0
Situation: I want to see which sessions are consuming CPU, while sampling once every half second.
SQL>@rss.sql 0 99999 0 99999 cpu % 0.50
Situation:I want to same all sessions of my database
SQL> @rss.sql 0 99999 0 99999 % % 0.125
Reference
http://blog.orapub.com/20140811/watch-oracle-db-session-activity-with-the-real-time-session-sampler.html
