Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

How to identify SQL performing poorly on an APEX application?

with 3 comments

Oracle Application Express (APEX) is a great tool to rapidly develop applications on top of an Oracle database. While developing an internal application we noticed that some pages were slow, meaning taking a few seconds to refresh. Suspecting there was some poorly performing SQL behind those pages, we tried to generate a SQL Trace so we could review the generated SQL. Well, there is no out-of-the-box instrumentation to turn SQL Trace ON from an APEX page… Thus our challenge became: How can we identify suspected SQL performing poorly, when such SQL is generated by an APEX page?

Using ASH

Active Session History (ASH) requires an Oracle Diagnostics Pack License. If your site has such a License, and you need to identify poorly performing SQL generated by APEX, you may want to use find_apex.sql script below. It asks for an application user and for the APEX session (a list is provided in both cases). It outputs a list of poorly performing SQL indicating the APEX page of origin, the SQL_ID and the SQL text. With the SQL_ID you can use some other tool in order to gather additional diagnostics details, including the Execution Plan. You may want to use for that: planx.sql, sqlmon.sql or sqlash.sql. Note that find_apex.sql script also references sqld360.sql, but this new tool is not yet available, so use one of the other 3 suggestions for the time being (or SQLHC/SQLT).

To find poorly performing SQL, script find_apex.sql uses ASH instead of SQL Trace. If the action on a page takes more than a second, then most probably ASH will capture the poorly performing SQL delaying the page.


-- File name: find_apex.sql
-- Purpose: Finds APEX poorly performing SQL for a given application user and session
-- Author: Carlos Sierra
-- Version: 2014/09/03
-- Usage: Inputs APEX application user and session id, and outputs list of poorly
-- performing SQL statements for further investigation with other tools.
-- Example: @find_apex.sql
-- Notes: Developed and tested on
-- Requires an Oracle Diagnostics Pack License since ASH data is accessed.
-- To further investigate poorly performing SQL use sqld360.sql
-- (or planx.sql or sqlmon.sql or sqlash.sql).
ACC confirm_license PROMPT 'Confirm with "Y" that your site has an Oracle Diagnostics Pack License: '
IF NOT '&&confirm_license.' = 'Y' THEN
RAISE_APPLICATION_ERROR(-20000, 'You must have an Oracle Diagnostics Pack License in order to use this script.');
COL seconds FOR 999,990;
COL appl_user FOR A30;
COL min_sample_time FOR A25;
COL max_sample_time FOR A25;
COL apex_session_id FOR A25;
COL page FOR A4;
COL sql_text FOR A80;
SELECT COUNT(*) seconds,
SUBSTR(client_id, 1, INSTR(client_id, ':') - 1) appl_user,
MIN(sample_time) min_sample_time,
MAX(sample_time) max_sample_time
FROM gv$active_session_history
WHERE module LIKE '%/APEX:APP %'
SUBSTR(client_id, 1, INSTR(client_id, ':') - 1)
HAVING SUBSTR(client_id, 1, INSTR(client_id, ':') - 1) IS NOT NULL
1 DESC, 2
ACC appl_user PROMPT 'Enter application user: ';
SELECT MIN(sample_time) min_sample_time,
MAX(sample_time) max_sample_time,
SUBSTR(client_id, INSTR(client_id, ':') + 1) apex_session_id,
COUNT(*) seconds
FROM gv$active_session_history
WHERE module LIKE '%/APEX:APP %'
AND SUBSTR(client_id, 1, INSTR(client_id, ':') - 1) = TRIM('&&appl_user.')
SUBSTR(client_id, INSTR(client_id, ':') + 1)
ACC apex_session_id PROMPT 'Enter APEX session ID: ';
SELECT COUNT(*) seconds,
SUBSTR(h.module, INSTR(h.module, ':', 1, 2) + 1) page,
SUBSTR(s.sql_text, 1, 80) sql_text
FROM gv$active_session_history h,
gv$sql s
WHERE h.module LIKE '%/APEX:APP %'
AND SUBSTR(h.client_id, 1, INSTR(h.client_id, ':') - 1) = TRIM('&&appl_user.')
AND SUBSTR(h.client_id, INSTR(h.client_id, ':') + 1) = TRIM('&&apex_session_id.')
AND s.sql_id = h.sql_id
AND s.inst_id = h.inst_id
AND s.child_number = h.sql_child_number
SUBSTR(h.module, INSTR(h.module, ':', 1, 2) + 1),
SUBSTR(s.sql_text, 1, 80)
1 DESC, 2, 3
PRO Use sqld360.sql (or planx.sql or sqlmon.sql or sqlash.sql) on SQL_ID of interest


This script as well as some others are now available on GitHub.


Written by Carlos Sierra

September 4, 2014 at 5:29 pm

3 Responses

Subscribe to comments with RSS.

  1. Hey Carlos, just wanted to point out that you can trace an APEX page by adding &p_trace=YES at the end of the URL. That will still come in handy when the Diagnostics Pack is not licensed.


    September 4, 2014 at 6:57 pm

    • Thanks Jorge!

      Carlos Sierra

      September 4, 2014 at 6:59 pm

    • Yes Jorge. If SQL Trace captures the SQL performing poorly, and retrieval of the trace file from server is possible, I’d rather go with SQL Trace. In the other hand, if SQL Trace does not capture such SQL, and the site has a license for the Diagnostics Pack, then mining ASH is a good alternative. APEX rocks!
      What I like about this ASH based method, is that you can diagnose after the fact, so it does not need to request the user to redo whatever action performed poorly. This could be of benefit in those cases where the poor performance is intermittent, or hard to reproduce at will.

      Carlos Sierra

      September 5, 2014 at 8:45 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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

%d bloggers like this: