Follow: Linkedin, Twitter, Youtube, Mastodon, dev.to

This was first published on https://blog.dbi-services.com/exploring-oracle-se-a-ee-performance-statistics-with-orachrome-lighty (2014-05-12)
Republishing here for new followers. The content is related to the the versions available at the publication date

Exploring Oracle SE & EE performance statistics with Orachrome Lighty

    At dbi services, we really like Lighty for Oracle, a tool which helps exploring performance statistics whether they originate from AWR (available only in Enterprise Edition with Diagnostic Pack option) or from Statspack (available via Standard Edition). As a matter of fact, we like it so much that we have became Orachrome Partner.

    If you want to try it out for yourselves, there is a free trial here. If you have Statspack installed (see spdoc.txt in ORACLE_HOME/rdbms/admin to install it), you can immediately use Lighty.

    Let me show you how to install it. First, you have to define a connection (shortcut: Ctrl-A):

    CaptureLighty001.PNG

    It is a jdbc connection without the need to install anything else. You just need the listener host, port, and service:

    CaptureLighty002.PNG

    As you are on Oracel Standard Edition, you don’t have Diagnostic Pack nor tuning Pack. But you have installed Statspack, and you will install L-ASH very soon:

    CaptureLighty003.PNG

    Now, you can install L-ASH as documented. But the goal of this post is to show what you can get it immediately. Go to the menu: Reports > DB Statistics > Past Activity (Statspack) You can select a day and see the snapshots. The color bar immediately shows when the activity was high:

    CaptureLighty004.PNG

    Then you can choose the graphical equivalent for my preferred Statspack sections: Top Events:

    CaptureLighty005.PNG

    Top Segments (change the Statspack level to 7 for that):

    CaptureLighty007.PNG

    And of course Top SQL Statements:

    CaptureLighty006.PNG

    For each statement (click on its sql_id) you will have everything: SQL text, execution plan, statistics. If you like the tool, it’s time to go further and install L-ASH so that you can mine from the Activity Viewer tab, which is the most valuable feature of Lighty for Oracle.

     


Follow: Linkedin, Twitter, Youtube, Mastodon, dev.to