SQL graphs (with some Analytics)

I’ve been learning about Analytic queries. It is really a cool thing, and I hope it makes it into the SQL standard and/or other databases. After trying this out I realized it would be really cool if I could graph things in SQL. As a result I came up with this to allow me to use bar graphing in the text SQL result to make things more apparent. Now, obviously you can use sorting to put the big things at the top, but this was fun and sometimes I want to know what is going on, but I want it in another order (such as the “Session Count” example).

So, here are a couple examples:

Session Count

This is a query that looks at sessions per schema on one our databases. I use this because the application creates additional connections when it gets busy, so this tells me who is doing a lot of work and/or who is waiting a lot and creating more sessions to get more done (yeah, I’ve talked to development about that.)

select username, sess_count,
       -- tot_sess_count, pct_of_tot,
       lpad('|',pct_of_tot,'|') as pct_of_tot_bar_graph
from
(
select du.username, count(distinct s.sid) sess_count,
        sum(count(distinct s.sid)) over () tot_sess_count,
        round((count(distinct s.sid) / sum(count(distinct s.sid)) over ())*100,0) pct_of_tot
from gv$session s, dba_users du
where du.username like 'MY%'
and du.username != 'MY_REPORTING'
and du.username = s.username(+)
group by du.username
order by du.username
)
/
USERNAME    SESS_COUNT PCT_OF_TOT_BAR_GRAPH
----------- ---------- ---------------------------------
MY_ATLT             20 |||||||
MY_BVRT             21 |||||||
MY_CHIC             27 |||||||||
MY_CHLM             20 |||||||
MY_DENV             67 ||||||||||||||||||||||
MY_DETR             22 |||||||
MY_MANA             19 ||||||
MY_NPLS             21 |||||||
MY_PHIL             20 |||||||
MY_PITT             20 |||||||
MY_SJOS             23 ||||||||
MY_SLTL             19 ||||||

ASH Examination

This is something you’ll probably want to just sort, but I thought it was a cool place to use it.

select wait_class, wait_class_id,
       sum_waited,
       round((sum_waited / sum(sum_waited) over ())*100,2) pct_of_time_waited,
       lpad('|',round((sum_waited / sum(sum_waited) over ())*100,0),'|') pct_time_waited_bar_graph
from
(
select ash.wait_class, ash.wait_class_id,
        sum(ash.time_waited) sum_waited,
        avg(ash.time_waited) avg_waited,
        count(1) total_waits
from dba_hist_active_sess_history ash, dba_hist_snapshot snap
where ash.snap_id = snap.snap_id
and snap.begin_interval_time > sysdate-1
group by ash.wait_class, ash.wait_class_id
)
order by wait_class
/
WAIT_CLASS      WAIT_CLASS_ID          SUM_WAITED PCT_OF_TIME_WAITED PCT_TIME_WAITED_BAR_GRAPH
--------------- ------------- ------------------- ------------------ --------------------------------------------------
Administrative     4166625743            16861640                .01
Application        4217450380           370791000                 .2
Cluster            3871361733         68891497232              37.07 |||||||||||||||||||||||||||||||||||||
Commit             3386400367         41434201160               22.3 ||||||||||||||||||||||
Concurrency        3875070507         24621300840              13.25 |||||||||||||
Configuration      3290255840           133785952                .07
Idle               2723168908             3170984                  0
Network            2000153315            96558368                .05
Other              1893977003         11256082552               6.06 ||||||
System I/O         4108307767         19066757232              10.26 ||||||||||
User I/O           1740759767         19940617360              10.73 |||||||||||

So, try it out if it looks useful, since it is quite simple to do. In fact, I’d love to reproduce the OEM Performance Page graph in SQL to make it easy to see the same data in a query.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s