If you run Oracle Standard Edition or haven’t licenced Diagnostics Pack for Enterprise Edition, then you don’t have AWR and ASH Data available. This is when Statspack, the predecessor of AWR, comes in handy to keep a history of database performance metrics.

But although Oracle still deliver Statspack with their recent DB releases (yes, even in 12c it’s not dead!), there are few tools that support it. But wait – Oracle SQL Developer has a nice reporting feature built in, so why not build custom statspack reports for this great free tool?

This motivated me to start a series on leveraging SQL Developer Reports for DBA tasks, starting with visualizing logical I/O history.

First things first: I like my SQL Developer menus in English. Unfortunately, you cannot choose your preferred language from the preferences menu, so you have to add an entry to “<SQLDev Program Dir>/sqldeveloper/bin/sqldeveloper.conf”:

# Set language to English
AddVMOption -Duser.language=en

Okay, let’s fire up sqldev, go to the “Reports” tab an create a new user report:

and set the initial properties for the report – choose “Chart” as report type:

There are many, many ways to query Statspack data and to visualize performance metrics. Let me show you my way – your mileage may vary:

-- Pre-select snapshot detail using a WITH-clause,
-- particularly the time difference between snaps in seconds
WITH snaps AS(
     SELECT sn.snap_id
          , sn.snap_time
          , LAG( sn.snap_id, 1 ) OVER( ORDER BY sn.snap_id ) AS prev_snap_id
          , ( LAG( sn.snap_time, 1 ) OVER( ORDER BY sn.snap_id ) - sn.snap_time ) *24*3600 AS time_diff
          , sn.instance_number
          , sn.dbid
       FROM stats$snapshot sn
)
-- These are the columns SQL Developer needs for a graph:
-- X-axis, series name, Y-axis
SELECT TO_CHAR( snap_time, 'dd.mm. hh24:mi' ) time
     , stat_name
     , value_diff  * blksz / 1024 / 1024
       / DECODE( time_diff, 0, NULL, time_diff ) "MB/s"
  FROM
     (
     SELECT sn.snap_id
          -- the LAG distance has to match the number of metrics collected
          -- e.g., 2 metrics ==> LAG( x, 2 )
          , ( LAG( ss.value, 2 ) OVER( ORDER BY ss.snap_id, ss.name ) - ss.value ) AS value_diff
          , sn.time_diff
          , sn.snap_time
          , ss.name stat_name
          , ( select to_number(value) from v$parameter where name='db_block_size' ) blksz
       FROM stats$sysstat ss
          , snaps sn
      WHERE ss.name IN ('db block gets', 'consistent gets') --, 'session logical reads')
        AND sn.snap_id = ss.snap_id
        AND ss.dbid = sn.dbid
        -- use instance_number to be RAC-aware.
        -- restrict the selection to the current instance.
        AND ss.instance_number = sn.instance_number
        AND sn.instance_number = ( SELECT instance_number FROM v$instance )
     )
ORDER BY snap_time, stat_name DESC

Now the SQL is entered, choose an appropriate graph type. In our case, the sum of “db block gets” and “consistent gets” equals “session logical reads” – thus, a stacked area graph shows us three metrics by selecting only two.

Let’s give our report a self-explanatory title:

I don’t like the default colour, especially when it comes to series that differ by orders of magnitude. So let’s choose a brighter colour for the “db block gets” column:

A title for the Y-Axis would also be nice. Furthermore, when the instance is restarted, many metrics are reset to zero, resulting in huge negative differences ruining our graph. You could either mitigate this with SQL or, as shown here, by setting the minimum Y-Axis value to 0 (zero).

Finally, let’s hit “Apply” an check out how our graph looks!

Looks good already, doesn’t it? Actually, this is real world data and quite typical for an OLTP system with many reads and little changes. If just the tiny area of the “db block gets” would show up better… But hey, there’s an option in the report properties that helps! Change the scale to “logarithmic” and see for yourself:

The logarithmic scale works only when the minimum value is set automatically, so check that box back again.

Not too bad, either. Now you can easily check the values of that little metric by hovering with your mouse cursor over the area.

But wait – there’s more to those graphs: What if we want to plot a value that’s not part of the sum but also a component of logical I/O? Can we combine a stacked graph with a line for that separate series? Yes, we can (sorry, couldn’t resist the pun)!

Here’s our SQL, now with an additional metric:

WITH snaps AS(
     SELECT sn.snap_id
          , sn.snap_time
          , LAG( sn.snap_id, 1 ) OVER( ORDER BY sn.snap_id ) AS prev_snap_id
          , ( LAG( sn.snap_time, 1 ) OVER( ORDER BY sn.snap_id ) - sn.snap_time ) *24*3600 AS time_diff
          , sn.instance_number
          , sn.dbid
       FROM stats$snapshot sn
)
SELECT TO_CHAR( snap_time, 'dd.mm. hh24:mi' ) time
     , stat_name
     , value_diff  * blksz / 1024 / 1024
       / DECODE( time_diff, 0, NULL, time_diff ) "MB/s"
  FROM
     (
     SELECT sn.snap_id
          , ( LAG( ss.value, 3 ) OVER( ORDER BY ss.snap_id, ss.name ) - ss.value ) AS value_diff
          , sn.time_diff
          , sn.snap_time
          , ss.name stat_name
          , ( select to_number(value) from v$parameter where name='db_block_size' ) blksz
       FROM stats$sysstat ss
          , snaps sn
      WHERE ss.name IN ('db block changes', 'db block gets', 'consistent gets')
        AND sn.snap_id = ss.snap_id
        AND ss.dbid = sn.dbid
        AND ss.instance_number = sn.instance_number
        AND sn.instance_number = ( SELECT instance_number FROM v$instance )
     )
ORDER BY snap_time, stat_name DESC

After changing the chart type to “Combination”, open the “Plot Area” properties again and choose colours and graph types for our series. We want to plot “db block changes” as a line and the other series as an area:

And this is how it looks in the end:

Unfortunately, the graph library of SQL Developer 4.0.3 draws the yellow area with gaps inbetween. So far I haven’t found a way to change that; if you know a solution, let me know in the comments, please.

At this point you might consider saving this report. The saved XML file can then be transferred to another computer / SQL Developer installation or shared publicly as I do in my SQL Developer Repository on GitHub. So, in case you don’t want to walk through this article, feel free to download this report from there.

Hope this helps,

Uwe