Identifying the interloper

I'm often looking to identify top consumers - SQL statements that we know will regularly chew through time, CPU, memory etc.  After a while of living with an application, you will get used to seeing certain statements up there however over time, you may miss something that creeps in.

Most sites I visit as a consultant have the DBMS_WORKLOAD_REPOSITORY snapshots taken automatically every 30 or 60 minutes which may be fine for applications that run well and predictably, however finding problematic events (bad SQL, spikes in latency etc) require that snapshot creation be more frequent.  I personally like snapshots to be generated every 10 minutes which is the minimum interval permissible in Oracle 11g and 12c.  I should add that this is the most frequent that automatic snapshots will be taken - there is nothing stopping you from creating them almost as frequently as you like (beware granularity issues should you try this).

Recently I've been getting a lot of requests to identify what the top consuming SQL statements are and to identify new statements that appear as new software is deployed.  You can certainly do this through reading the AWR reports, but the comparison between snapshots requires a little manipulation unless you like scraping data into Excel (or SuperCalc 4 for those in the stone age) so I came up with some SQL to do the hard work for me.

In this particular case, the SQL looks for statements executed the most frequently, specifically by the "executions_delta" that is quite the useful statistic.  You could of course care about any of the measurements - elapsed time, CPU consumed, I/O waits etc - modify the SQL as appropriate.
So, here it is.  It will identify the top 5 ranked SQL statements for snapshots taken in the past ten hours.

  SELECT snap_id,

         MAX (CASE WHEN exec_rank = 1 THEN sql_id ELSE NULL END) AS "First",

         MAX (CASE WHEN exec_rank = 2 THEN sql_id ELSE NULL END) AS "Second",

         MAX (CASE WHEN exec_rank = 3 THEN sql_id ELSE NULL END) AS "Third",

         MAX (CASE WHEN exec_rank = 4 THEN sql_id ELSE NULL END) AS "Fourth",

         MAX (CASE WHEN exec_rank = 5 THEN sql_id ELSE NULL END) AS "Fifth"

    FROM (  SELECT a.snap_id,

                   a.sql_id,

                   a.executions_delta,

                   DENSE_RANK ()

                   OVER (PARTITION BY a.snap_id ORDER BY a.executions_delta DESC)

                      exec_rank

              FROM sys.wrh$_sqlstat a, sys.dba_hist_snapshot d

             WHERE     a.snap_id = d.snap_id

                   AND (    (d.begin_interval_time > SYSDATE - 10 / 24)

                        AND (d.end_interval_time < SYSDATE - 1 / 96))

          ORDER BY snap_id, exec_rank, sql_id)

   WHERE exec_rank < 6 GROUP BY snap_id

That done, you should end up with data that looks like this. Now it should be somewhat simpler to identify what has changed between snapshots.

SNAP_ID	First	Second	Third	Fourth	Fifth
2909	96g93hntrzjtr	3nkd3g3ju5ph1	db78fxqxwxt7r	53saa2zkr6wc3	32hbap2vtmf53
2910	cm5vu20fhtnq1	3c1kubcdjnppq	b2gnxm5z6r51n	95mpkn5xz9001	db78fxqxwxt7r
2911	cm5vu20fhtnq1	61a161nm1ttjj	5ms6rbzdnq16t	3c1kubcdjnppq	089dbukv1aanh
2912	cm5vu20fhtnq1	61a161nm1ttjj	5ms6rbzdnq16t	089dbukv1aanh	csnp95dz2r8ss
2913	cm5vu20fhtnq1	61a161nm1ttjj	089dbukv1aanh	9pvbjqqt222b4	csnp95dz2r8ss
2914	cm5vu20fhtnq1	61a161nm1ttjj	089dbukv1aanh	9pvbjqqt222b4	csnp95dz2r8ss
2915	cm5vu20fhtnq1	61a161nm1ttjj	5ms6rbzdnq16t	089dbukv1aanh	9pvbjqqt222b4
2916	cm5vu20fhtnq1	61a161nm1ttjj	089dbukv1aanh	3c1kubcdjnppq	9pvbjqqt222b4

Welcome - and an introduction

I should perhaps start by declaring that I am not a blogger, nor does the social media aspect of modern internet life come naturally to me.

I am, by profession, an Oracle Database Administrator and have been since 1992 when a kind CEO offered me a job as a "DBA".  Now, I had no idea what a DBA did; my prior experience with Oracle was with an AT&T Istel patient administration system in 1988 at a London hospital.  Of course I couldn't Google it and my only hope was one of the Oracle Forms developers (green screen) and the Oracle Version 6 Administrator Guide which was relatively thin.

Almost 22 years later, I'm still in the field in spite of a very nice offer from IBM for me to specialize in AIX.  Very glad to be here now, what with Exadata, Big Data Appliance, 12c and a host of features too numerous to mention.

I'm convinced that the role of the DBA is not going away any time soon - as naysayers and "thought leaders" have predicted over the past few years.  As more automation comes in, there's more complexity to manage; more products and features to learn, optimize and new roadblocks to overcome.
I recall in version 6 spending a lot of time managing rollback segments.  Now it's all automatic and yet I don't feel my role is any less relevant because I don't have to do that any more.

Of late I am very excited to have joined the Enkitec team where the brilliant minds of many Oracle Aces and Ace Directors have congregated.  I feel smarter just breathing the same air :)

So from time to time I will post something here about situations I've encountered, perhaps some useful SQL or techniques and maybe some of those head-scratching moments one has when faced with either mind boggling situations or just "WTF" moments.

You may also experience the odd photo.  Cat, dog or travel.  Or just bizarre.

For those interested, my photos are available at my Flickr site.

Enjoy!  And avoid those ORA-00600s.