AWR/Statspack SQL Statement graphs available in version 1.1.1.235 of Mumbai

Version 1.1.1.235 of Mumbai is available for download now.

Besides a new AAS (Average Active Session) view which aggregates all wait events and DB CPU times into a single value for AWR and Statspack reports, there are now new graphs for the Top SQL statements from historic Statspack and AWR data. The basic idea is that you have a graph that shows you the information from the “Top SQL by elapased time”, “Top SQL by CPU time”, “Top SQL by buffer gets” and “Top SQL by executions” sections of a textual Statspack/AWR report, but over all snapsnots.

The picture above shows one line for each of the Top SQL statements. In this view the elapsed time over all intervals can be seen, but as visible from the tabs, “CPU time”, “Buffer gets” and “Number of Executions” is also available. When hovering over a line representing one SQL statement, the SQL text, it’s SQL_ID and HASH_VALUE and the values on the x- and y-axis are shown.

This entry was posted in Mumbai. Bookmark the permalink.

10 Responses to AWR/Statspack SQL Statement graphs available in version 1.1.1.235 of Mumbai

  1. orawiss says:

    There is no version for Linux?
    Thank you,
    Wissem

  2. Noons says:

    Hey Marcus. Very interesting tool, having a lot of fun with it.

    Quick question, if I may:
    I keep multiple versions of archived Statspack data in my dev/test database. Basically, I just do an export of the statspack tables from production every 6 months, then import them under a dedicated owner in test. One for each 6 month period. I use these to do long term trend analysis.
    Was just wondering: is there any way to make Mumbai look at these tables as if they were the default statspack ones? I tried making the owner user the logon to Mumbai, but it doesn’t appear to recognize the statspack tables at all.

    Been looking for a tool that will help me analyze this information and just thought Mumbai might do the trick.

    Thanks for any feedback and for maing available such a great tool.

    • Hi!

      Mumbai doesn’t reference the default PERFSTAT schema in its SQL statements, so it runs into the synonyms created by the @spcreate.sql script for all Statspack tables. You could write some scripts that remap the targets of the synonyms to your different schemas and then, both the standard SQL*Plus report scripts and the reports in Mumbai would work on your currently mapped schema.

      Btw, the PERFSTAT schema is actually designed to accept data from multiple instances.I frequently import additonal data from the same or another instance into an existing PERFSTAT schema. You just need to make sure that you first import STATS$DATABASE_INSTANCE, then STATS$SNAPSHOT and then all the other tables, but skipping the fixed tables like STATS$IDLE_EVENT, STATS$LEVEL_DESCRIPTION, etc..

      Hope this helps,
      Marcus

      • Noons says:

        Strange. I’ve got all the public synonyms pointing to the owner of the statspack tables and am logged in as that owner. Yet when I select the statspack tab in Mumbai, I immediately get a “ora942: table or view does not exist” dialogue, even before it displays the screen. And then I get it again every time I click on “Refresh”. I wonder what else I am missing?

  3. Actually, I was missing something. A quick 10046 trace of the Mumbai session shows that I am referencing the PERFSTAT schema in the SQL for filling the instance and snapshot selectors at the top of the Statspack tab (PERFSTAT.STATS$SNAPSHOT and PERFSTAT.STATS$DATABASE_INSTANCE). I’ll fix that very soon.

  4. Noons says:

    Thanks heaps, Marcus. It works perfectly now, with multiple perfstat schemas!
    Just what I needed.

  5. nabil says:

    I want more options with the “customize chart”.
    – Stacked column for “AWR top sql”
    – Stacked area to “wait event”
    ——————————————————–
    Bonjour,
    je souhaite plus d’option avec le bouton “customize chart”.
    – histogramme empilé pour “top sql awr”
    – aires empilées pour “wait event”

    Et d’autres type de graphique comme le permet excel

    Cdt

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