Sometimes Ignite for Oracle and other tools will show waits associated with a PL/SQL call, e.g.
BEGIN CONCAP.QUICKPOLL ('ORA102_TESTBOX', TRUE); END;
In our case, the QUICKPOLL procedure is very large and contains many SQL statements, so how can we use Ignite to drill into more details? One answer is to use the DBMS_APPLICATION_INFO package supplied within an Oracle database. This would only work if you have access to the code for the PL/SQL package, but it is a very powerful. In our example of the QUICKPOLL procedure, we modified the code similar to the following:
PROCEDURE QUICKPOLL (p_db_name IN VARCHAR2,
p_report IN BOOLEAN DEFAULT FALSE)
AS
<parameter_list>
BEGIN
-- 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);
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". Ignite for Oracle will collect this information and associate wait time with each module/action and quickly allow you to see which to focus on. In the background, the MODULE and ACTION columns from V$SESSION will be populated with the information and Ignite for Oracle collects it. In the following screenshot it is obvious the MONITOR action accumulates the most wait time. If the QUICKPOLL procedure requires tuning, this section of code should be the top priority.
.jpg)