And my simple & quick Oracle database monitoring script …

Posted by FatDBA on December 9, 2021

Hi All,

Sometimes when you have to support a performance problem and have to do the firefight, every second counts and you don’t want to use any of those fancy tools to see what’s going on in the database at the moment, and want something handy and quick, nothing is as friendly as querying dynamic views. What about using a small quick script to provide you what. Specially the darling views V$SQLAREA and V$SESSION where the first gives you all sorts of SQL level information and later provides me session information.

Recently while working on a shot assignment where I have to settle the performance and stablize system’s performance for a network inventory application, I quickly penned one script, which can be called as ‘Poor man’s database monitoring script‘ 🙂 to provide me a quick understanding of the database system on what all comes and stays and waits at any given point in time.

Github link : https://github.com/fatdba/scripts.git

The script is pretty basic but very handy, quick and gives you all sort of details, and I’ve played with the LAST_CALL_ET from V$SESSION to get the overall time (RUNNING_SINCE) the SQL is there in the database, rest all you can add, alter as per your need. So this is what it is …

--------------------------------------------------------------------------------------------------------------------
-- File name:   prashantpoormanscript.sql
-- Version:     V1.1 (12-08-2021) Simple View
-- Purpose:     This script can be used on any Oracle DB to know what all running and for how long and waiting
--              Also provides details on SQL and SESSION level. 
-- Author:      Prashant Dixit The Fatdba www.fatdba.com
--------------------------------------------------------------------------------------------------------------------
set linesize 400 pagesize 400
select 
x.inst_id
,x.sid
,x.serial#
,x.username
,x.sql_id
,plan_hash_value
,sqlarea.DISK_READS
,sqlarea.BUFFER_GETS
,sqlarea.ROWS_PROCESSED
,x.event
,x.osuser
,x.status
,x.BLOCKING_SESSION_STATUS
,x.BLOCKING_INSTANCE
,x.BLOCKING_SESSION
,x.process
,x.machine
,x.program
,x.module
,x.action
,TO_CHAR(x.LOGON_TIME, 'MM-DD-YYYY HH24:MI:SS') logontime
,x.LAST_CALL_ET
,x.SECONDS_IN_WAIT
,x.state
,sql_text,
ltrim(to_char(floor(x.LAST_CALL_ET/3600), '09')) || ':'
 || ltrim(to_char(floor(mod(x.LAST_CALL_ET, 3600)/60), '09')) || ':'
 || ltrim(to_char(mod(x.LAST_CALL_ET, 60), '09'))    RUNNING_SINCE
from   gv$sqlarea sqlarea
,gv$session x
where  x.sql_hash_value = sqlarea.hash_value
and    x.sql_address    = sqlarea.address
and    sql_text not like '%select x.inst_id,x.sid ,x.serial# ,x.username ,x.sql_id ,plan_hash_value ,sqlarea.DISK_READS%'
and    x.status='ACTIVE'
and x.USERNAME is not null
and x.SQL_ADDRESS    = sqlarea.ADDRESS
and x.SQL_HASH_VALUE = sqlarea.HASH_VALUE
order by RUNNING_SINCE desc;

This is how the output looks like. I have highlighted the RUNNING_SINCE column in red, just to show that the SQL was there in the database running for 3 minutes and 8 seconds and still active waiting on db file sequential reads and was called through SQL Developer, along it gets you the SQLID and PHV for all active SQLs along with other session and SQL level details that will help you to form the right approach for any of those slow sluggish SQLs.

There are hundreds of use cases for this one, specially when the application or product team doesn’t know what all gets triggered in the database in the form of SQLs whenver any of the APIs touched. That’s when you can take help of such handy SQL scripts to check what all runs on the database. You can also write a simple shell script and call it via OS utilities like watch to see it live showing you database workload .. You can write a simple shell something like this …

--------------------------------------------------------------------------------------------------------------------
-- File name:   prashantpoormanscript.sh
-- Version:     V1.1 (12-08-2021) Shell script View
-- Purpose:     This script can be used on any Oracle DB to know what all running and for how long and waiting
--              Also provides details on SQL and SESSION level. 
-- Author:      Prashant Dixit The Fatdba www.fatdba.com
--------------------------------------------------------------------------------------------------------------------
#!/bin/ksh
sqlplus /nolog << EOF
CONNECT username/Password@connection_string
set linesize 400
set pagesize 400
col ACTION for a22
col USERNAME for a9
col SQL_ID for a16
col EVENT for a20
col OSUSER for a10
col PROCESS for a8
col MACHINE for a15
col OSUSER for a8
col PROGRAM for a15
col module for a20
select x.inst_id,x.sid
,x.serial#
,x.username
,x.sql_id
,plan_hash_value as PHV
,sqlarea.DISK_READS
,sqlarea.BUFFER_GETS
,sqlarea.ROWS_PROCESSED
,x.event
,x.osuser
,x.status
,x.BLOCKING_SESSION_STATUS
,x.BLOCKING_INSTANCE
,x.BLOCKING_SESSION
,x.process
,x.machine
,x.program
,x.module
,x.action
,TO_CHAR(x.LOGON_TIME, 'MM-DD-YYYY HH24:MI:SS') logontime
,x.LAST_CALL_ET
,x.SECONDS_IN_WAIT
,x.state
,sql_text,
ltrim(to_char(floor(x.LAST_CALL_ET/3600), '09')) || ':'
 || ltrim(to_char(floor(mod(x.LAST_CALL_ET, 3600)/60), '09')) || ':'
 || ltrim(to_char(mod(x.LAST_CALL_ET, 60), '09'))    RUNNING_SINCE
from   gv\$sqlarea sqlarea
,gv\$session x
where  x.sql_hash_value = sqlarea.hash_value
and    x.sql_address    = sqlarea.address
and    sql_text not like '%select x.inst_id,x.sid ,x.serial# ,x.username ,x.sql_id ,plan_hash_value%'
and    x.status='ACTIVE'
and x.USERNAME is not null
and x.SQL_ADDRESS    = sqlarea.ADDRESS
and x.SQL_HASH_VALUE = sqlarea.HASH_VALUE
order by RUNNING_SINCE desc;

SPOOL OFF
EXIT;
EOF

Once you put all that in shell script, call that via watch utility and you’re set.
Example : call the shell every 2 seconds to refresh the output

[Fatdba@Ontacan-test7-dbmonkey mytools]$ watch -n 2 sh prashantpoormanscript.sh

But if you want a fancier representation of the code, here you go … I did some formatting and make it more easy to read but I still love the core one, unformatted and simple.

--------------------------------------------------------------------------------------------------------------------
-- File name:   prashantpoormanscript1.sql
-- Version:     V1.1 (12-08-2021) Fancy Version
-- Purpose:     This script can be used on any Oracle DB to know what all running and for how long and waiting
--              Also provides details on SQL and SESSION level. 
-- Author:      Prashant Dixit The Fatdba www.fatdba.com
--------------------------------------------------------------------------------------------------------------------
set linesize 400
set pagesize 400
col ACTION for a22
col USERNAME for a9
col SQL_ID for a16
col EVENT for a20
col OSUSER for a10
col PROCESS for a8
col MACHINE for a15
col OSUSER for a8
col PROGRAM for a15
col module for a20
col BLOCKING_INSTANCE for a20
select 
'InstID .............................................: '||x.inst_id,
'SID ................................................: '||x.sid,
'Serial .............................................: '||x.serial#,
'Username ...........................................: '||x.username,
'SQLID ..............................................: '||x.sql_id,
'PHV ................................................: '||plan_hash_value,
'DISK_READS .........................................: '||sqlarea.DISK_READS,
'BUFFER_GETS ........................................: '||sqlarea.BUFFER_GETS,
'ROWS_PROCESSED ..... ...............................: '||sqlarea.ROWS_PROCESSED,
'Event  .............................................: '||x.event,
'OSUser .............................................: '||x.osuser,
'Status .............................................: '||x.status,
'BLOCKING_SESSION_STATUS ............................: '||x.BLOCKING_SESSION_STATUS,
'BLOCKING_INSTANCE ..................................: '||x.BLOCKING_INSTANCE,
'BLOCKING_SESSION ...................................: '||x.BLOCKING_SESSION,
'PROCESS ............................................: '||x.process,
'MACHINE ............................................: '||x.machine,
'PROGRAM ............................................: '||x.program,
'MODULE .............................................: '||x.module,
'ACTION .............................................: '||x.action,
'LOGONTIME ..........................................: '||TO_CHAR(x.LOGON_TIME, 'MM-DD-YYYY HH24:MI:SS') logontime,
'LAST_CALL_ET .......................................: '||x.LAST_CALL_ET,
'SECONDS_IN_WAIT ....................................: '||x.SECONDS_IN_WAIT,
'STATE ..............................................: '||x.state,
'RUNNING_SINCE ......................................: '||ltrim(to_char(floor(x.LAST_CALL_ET/3600), '09')) || ':' || ltrim(to_char(floor(mod(x.LAST_CALL_ET, 3600)/60), '09')) || ':' || ltrim(to_char(mod(x.LAST_CALL_ET, 60), '09')) RUNNING_SINCE,
'SQLTEXT ............................................: '||sql_text
from   gv$sqlarea sqlarea
,gv$session x
where  x.sql_hash_value = sqlarea.hash_value
and    x.sql_address    = sqlarea.address
and    sql_text not like '%select x.inst_id,x.sid ,x.serial# ,x.username ,x.sql_id ,plan_hash_value%'
and    x.status='ACTIVE'
and    sql_text not like '%select :"SYS_B_00"||x.inst_id, :"SYS_B_01"||x.sid, :"SYS_B_02"||x.serial#,%'
and x.USERNAME is not null
and x.SQL_ADDRESS    = sqlarea.ADDRESS
and x.SQL_HASH_VALUE = sqlarea.HASH_VALUE
order by RUNNING_SINCE desc;

And the output will look something like this, neat and clean ..

Hope It Helped!
Prashant Dixit

This entry was posted on December 9, 2021 at 8:36 AM and is filed under Advanced, troubleshooting. Tagged: oracle, performance, troubleshooting. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

6 Responses to “And my simple & quick Oracle database monitoring script …”

Sriram Sanka said

December 9, 2021 at 9:10 AM
On the “Fancier Part “, you can use Tom`s “Print_Table” function

Reply
- FatDBA said
  
  December 9, 2021 at 9:55 AM
  Thanks mate, I need to check that…
  
  Reply
Mayank said

December 9, 2021 at 9:26 AM
Awsome ,i have to do this performance firefighting everyday and yes every seconds count , thanks for sharing

Reply
- FatDBA said
  
  December 9, 2021 at 9:55 AM
  Yeah my friend!
  Thanks you liked the post and the script.
  
  Reply
Rolf Maag said

December 10, 2021 at 4:39 PM
You know about v$active_session_history, dont you

Reply
- FatDBA said
  
  December 10, 2021 at 7:56 PM
  Yes, I know that and have used in few of my other scripts and tools that I have written so far.
  
  I just want to keep it simple using V$SESSION and V$SQLAREA …
  
  Reply

Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Likes