ASH Viewer

    Background (2008)

    For work often had to deal with the performance of Oracle database servers. After the release of Oracle 10g, the task of monitoring and diagnosing performance problems was greatly simplified - using the Active Session History (ASH ), it became possible to find out what happened to the database in the past, what is happening on the server at the moment, and even predict what will happen with the load in the future.

    But, firstly, the migration process to the new version is quite lengthy, and some customers continued to use the old versions. And secondly, visualization tools for the history of active sessions from Oracle, which were available:
    • They required some gestures to install and configure them - this is Oracle Enterprise Manager Console (DB Console) or Oracle Enterprise Manager Grid Control (now Oracle Enterprise Manager Cloud Control). DB Console from my (and not only) practice required to install the use of very strong admin spells. Also, in order to save resources, not everyone wants to install additional services on the database server or deploy a separate monitoring infrastructure on their site;
    • Possessed a number of architectural flaws. For example, in the event of a database server freezing, access to information from the history of active sessions through the Oracle Enterprise Manager Console became impossible, and quickly find out that “something went wrong” (c) was almost impossible;
    • There was no functionality in terms of storing monitoring data and its subsequent analysis on its site.

    Of third-party free tools, similar functionality was implemented in the ASHMON utility by Kyle Hailey. But the functionality was only for viewing, and it worked under one platform - Windows.

    In general, after considering all the options available at that time, a strong-willed decision was made to assemble your “bicycle” from improvised materials.

    Application Requirements:
    • Independence from monitoring tools installed on the customer’s database;
    • The ability to save monitoring results in a local database for subsequent processing;
    • Extensibility. The ability to add functionality if necessary;
    • Cross-platform. The program works both in Unix / Linux and in Windows environments.

    1. Oracle Berkeley DB Java Edition v. 3.3.75. Embedded key value storage. To store history data of active sessions;
    2. JFreeChart v. 1.0.12. Library for creating graphs in Java SE. To solve the problem, the stacked chart came up. The main changes to achieve the desired result - similar to how in Oracle Enterprise Manager - see ChartPanel ;
    3. E-Gantt v.0.5.3. Library for creating Gantt charts in Java Swing;
    4. SwingLabs Swing Component Extensions v. 0.9.5. For more convenient output of tabular data, calendar, etc .;
    5. Joda Time - Java date and time API v.1.6. A great library for working with dates in Java;
    6. Blanco SQL Formatter v. 0.1.1. Formatting sql, pl / sql code;
    7. jEdit Syntax Package v. 2.2.2. For highlighting sql, pl / sql code.

    General information about ASH Viewer:
    ASH Viewer provides a graphical interface for viewing data on active sessions. The program periodically polls the Oracle database server for a list of active sessions, stores information in a local database, after which the data is visualized. For versions prior to version 10g, the database is polled once per second; for Oracle 10g and higher, the request for information from v $ active_session_history and updating the schedule occur every 30 seconds.

    Setup and connection:
    Download the distribution package , unzip to a separate directory, add ojdbc6.jarto the lib directory. Run ASHV.jar or (, correcting the paths to the installed Java on the local computer. Connect using a user with privileges to view tables and rights to execute pl / sql packages necessary for ASH Viewer-a to work here .

    For version 10g and above, it is possible to enable ASH emulation. This mode is suitable for databases for which the paid Oracle Diagnostic Pack option is not connected, or for Oracle in Standard Edition. For 9i, 8i versions, select Standard.

    Top Activity Interface:
    The interface is identical to the Top Activity page of Oracle Enterprise Manager. We select an arbitrary range on the graph and get data on top of active sessions and SQL / PL / SQL statements. It is possible to get detailed information on request - request text, execution plans:
    - Directly from v $ sql_plan (for various plan_hash_value). Made for compatibility with previous versions of Oracle DBMS;
    - DBMS_XPLAN.DISPLAY_CURSOR. From cursor cursor. Execution plans for a specific sql_id;
    - DBMS_XPLAN.DISPLAY_AWR. Execution plans from the workload repository, also by sql_id.
    In the settings, you can set the automatic mode in which the program updates top sql sessions and processes on a given window (by default - 5 minutes of the last database server activity), the number of SQL, PL / SQL statements that select detailed information from the database (query from v $ sql) and the ability to change the scale of the Top Activity graph representation relative to the number of processors (cpu_count database server parameter). Using the selected range, you can get an ASH report (similar to the output that we get through the script) through a DB server API call - DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT

    Detail interface:
    Drill down on CPU Used and each group of wait events. It works similar to the Top Activity interface of Oracle Enterprise Manager, but there is no automatic mode. You can also configure the number of operators by which detailed information is selected from v $ sql, save query execution plans in a local database and the ability to change the scale of the graph representation relative to the number of processors in the system.

    History Interface:
    On this page, you can view the history of active sessions using the collected data from the history of active Oracle sessions. Navigation is done through the Calendar interface. Also in the Calendar interface, you can select a range of days and delete it from the local database to free up disk space (for example, in the case of a long collection of the history of active sessions). The offline mode works, in which you can run ASH Viewer only to view archived data. To get more complete information in archive mode, it is recommended to collect information online in automatic mode, otherwise information on sql queries will be available only in the form of SQL ID / HASH Value.

    • If there is no connection to the database (network failures, problems with the listener, restarting the database), ASH Viewer automatically tries to connect to the Oracle server every 30 seconds to continue collecting information about the history of active sessions;
    • It is possible to specify in the settings to dump data from the top SQL / PL / SQL statements to the OS clipboard.

    Use cases:
    • Ongoing performance monitoring of Oracle DBMS;
    • View the history of active sessions for the previous observation period;
    • Creating reports on the operation of the Oracle database.

    Useful links:
    Project page on . GitHub
    project page . Recording actions on the screen in a gif file - ScreenToGif Utility for load testing databases - Hummerora . Thanks for attention!

    Also popular now: