Saturday, 14 February 2009

Oracle Quick Tip: Displaying a Text Graph

The RPAD function can be used to display a text graph in Oracle.

Suppose we wish to display the number of sessions that have been opened by each client machine connected to the database. We can run this simple SQL.
SELECT 
machine,
count(*) sessions
FROM v$session
GROUP BY machine
ORDER BY machine;

MACHINE SESSIONS
-------------------------- -------------
GHYT887 20
MAL001024 18
SCJ0012 15
dev.lishman.com 2
jupiter.lishman.com 1
mars.lishman.com 31
saturn.lishman.com 15
venus.lishman.com 23

8 rows selected
However, we can easily include a text graph to provide a visual representation of the session distribution.
SELECT 
machine,
count(*) sessions,
rpad('*', count(*), '*') session_graph
FROM v$session
GROUP BY machine
ORDER BY machine;

MACHINE SESSIONS SESION_GRAPH
-------------------------- ------------- -------------------------------
GHYT887 20 ********************
MAL001024 18 ******************
SCJ0012 15 ***************
dev.lishman.com 2 **
jupiter.lishman.com 1 *
mars.lishman.com 31 *******************************
saturn.lishman.com 15 ***************
venus.lishman.com 23 ***********************

8 rows selected
RPAD right pads the first expression ('*') to the length specified by the second expression (count(*)) with the character provided by the third expression ('*').

1 comment:

abeacock said...

Nice tip Lish, this is a very cool post which I've delicious'ed for a future time.