Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Live or Dynamic Dashboard & Charts in SQL Developer Web

2 Mins Read

Yesterday I shared a dashboard I published in my Always Free Autonomous Cloud Service.

The first question I got was:

I was just a little surprised that the examples in your blog weren’t live.  After all, that’s the point of the feature.

A fan 🙂

So sure, let’s use the database itself to build some ‘live’ charts, or charts that have frequently changing data.

I like toggling to grids to see the raw data, esp if the label is an actual SQL statement!

The Dashboard

There are 4 actual charts. The layout is very much up to you. I wanted something wide enough that I could read the SQL text on my ‘Slowest SQL’ chart. And I needed similar to get true idea of scale on my User Stats – so both of those got the space that 2 charts would normally take. And my Health Check chart isn’t just two columns wide, it’s also two columns tall!

This is how that looks in ‘design-mode:’

I can move those around or re-size them in any fashion I deem most desirable.

The Reports

I picked some easier to get metrics out of some existing SQLDev reports. I know also back in the day that Tom was quite keen on knowing what your Hard/Soft parse ratios were. Please don’t take these as endorsements by me.

I am merely trying to demonstrate what you can do with the Charts feature, and using some data out of the data dictionary.

Waits

SELECT
    gv$system_event.wait_class             AS wait_class,
    0.6 * SUM(gv$system_event.time_waited) AS time_waited
FROM
    gv$system_event
WHERE
    wait_class != 'Idle'
GROUP BY
    gv$system_event.wait_class
ORDER BY
    1

Choose whichever type of chart you want, or adjust these queries as YOU see fit.

Slowest SQL

SELECT
    DISTINCT sql_text AS SQL,
    CASE
        WHEN cpu_time < 1000     THEN
            '< 1 ms'
        WHEN cpu_time < 1000000  THEN
            to_char(
                round(
                    cpu_time / 1000,
                    1
                )
            )
            || ' ms'
        WHEN cpu_time < 60000000 THEN
            to_char(
                round(
                    cpu_time / 1000000,
                    1
                )
            )
            || ' s'
        ELSE
            to_char(
                round(
                    cpu_time / 60000000,
                    1
                )
            )
            || ' m'
    END      AS cpu_seconds_form,
    cpu_time / 1000 AS cpu_mseconds,
    sql_id
FROM
    gv$sql
WHERE cpu_time <> 0
ORDER BY
    3 DESC
fetch FIRST 15 ROWS ONLY

I have (2) columns in the query used for the chart and the rest that come in handy if you switch to the grid view (cpu_seconds & sql_id).

User Stats

SELECT
    NAME,
    VALUE
FROM
    V$SYSSTAT
WHERE
        CLASS = 1
    AND ( NAME LIKE 'user %' )

Boring query, but interesting data?

Parse Ratios

SELECT 'Soft Parses ' "Ratio"
,round(
((SELECT SUM(VALUE) FROM v$sysstat WHERE name = 'parse count (total)')
- (SELECT SUM(VALUE) FROM v$sysstat WHERE name = 'parse count (hard)'))
/(SELECT SUM(VALUE) FROM v$sysstat WHERE name = 'execute count')
*100,2) "percentage"
FROM dual
UNION
SELECT 'Hard Parses ' "Ratio"
,round(
(SELECT SUM(VALUE) FROM v$sysstat WHERE name = 'parse count (hard)')
/(SELECT SUM(VALUE) FROM v$sysstat WHERE name = 'execute count')
*100,2) "percentage"
FROM dual
UNION
SELECT 'Parse Failure ' "Ratio"
,round(
(SELECT SUM(VALUE) FROM v$sysstat WHERE name = 'parse count (failures)')
/(SELECT SUM(VALUE) FROM v$sysstat WHERE name = 'parse count (total)')
*100,2) "percentage"
FROM dual

That last query courtesy of dilipdba.

To create these charts, you can copy and paste the SQL in, and then go from there. Enjoy!

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

5 Comments

  1. chris Reply

    Well say we have diff client data in the table CLIENT_SALES with a CLIENT_CODE column that defines the data for each client then we have a USERS table with USER_ID and CLIENT_CODE

    So in order that the dashboard only displays data for user logged in we would have

    SELECT sum(cs.sales_total)
    from CLIENT_SALES cs,USERS u
    where u.client_code = cs.client_code
    and upper(u.user_id) = :APP_USER

  2. christopher bell Reply

    These are great !

    is there anyway we can link them to the current logged in user on APEX the :APP_USER ??

Write A Comment