Submit a ticketCall us

Cloud Workloads: Meet Your New Hybrid IT Reality
Have you found yourself in that evolving, hybrid IT grey area and wondering if cloud workloads are now part of your purview? And if so, will monitoring cloud workloads require a new set of dedicated cloud monitoring tools? Your answers: yes, they should be, and no, they don’t.

Find out how SolarWinds® Server & Application Monitor (SAM) can help you monitor your cloud workloads side by side with your on-premises workloads. Register Now.

Home > Success Center > Database Performance Analyzer (DPA) > Use DBMS APPLICATION INFO for more detailed wait analysis

Use DBMS APPLICATION INFO for more detailed wait analysis

Table of contents
No headers
Created by Interspire Import, last modified by MindTouch on Jun 23, 2016

Views: 76 Votes: 0 Revisions: 11

 

DPA may show waits associated with a PL/SQL call and not the individual SQL statements or steps that comprise the code. For example, DPA may show wait time associated with SQL that looks similar to:

BEGIN CONCAP.QUICKPOLL ('ORA102_TESTBOX', TRUE); END;

In this case, the QUICKPOLL procedure is large and contains many SQL statements, so how can we use DPA to drill into more details? You can use the DBMS_APPLICATION_INFO package supplied by an Oracle database.  This only works if you have access to the code for the PL/SQL package. In this example of the QUICKPOLL procedure, the modified code is similar to the following:

PROCEDURE QUICKPOLL (p_db_name        IN VARCHAR2,
    p_report        IN BOOLEAN DEFAULT FALSE)
 AS
    current_module VARCHAR2(48);
   current_action VARCHAR2(32);
 BEGIN
   -- save the current settings for module and action to reset them when done
   DBMS_APPLICATION_INFO.READ_MODULE(current_module, current_action);
    -- run the startup code
    DBMS_APPLICATION_INFO.SET_MODULE ('QUICKPOLL', 'STARTUP');
    STARTUP (p_db_name);
 
    -- Gather monitoring data
    DBMS_APPLICATION_INFO.SET_MODULE ('QUICKPOLL', 'MONITOR');
    MONITOR (p_db_name);
        
    -- Publish the data
    DBMS_APPLICATION_INFO.SET_MODULE ('QUICKPOLL', 'PUBLISH');
    PUBLISH (p_db_name);
 
    -- cleanup monitoring data
    DBMS_APPLICATION_INFO.SET_MODULE ('QUICKPOLL', 'CLEANUP');
    CLEANUP (p_db_name);
   -- reset the module and action to the previous value
   DBMS_APPLICATION_INFO.ST_MODULE(current_module, current_action);
 END QUICKPOLL;

When the QUICKPOLL code executes, the MODULE column will be populated with "QUICKPOLL" and the ACTION column will contain "STARTUP", "MONITOR", "PUBLISH" or "CLEANUP". DPA collects this information and associates wait time with each module or action and allows you to see which portion of the PL/SQL code to focus on. In the background, the MODULE and ACTION columns from V$SESSION are populated with the information, and DPA collects it. In the following screenshot, the MONITOR action is shown to accumulates the most wait time. If the QUICKPOLL procedure requires tuning, this section of code should be the top priority.

dbms_application_info(1).jpg

Last modified

Tags

Classifications

Public