Posted by For the many cases, simply having an EXPLAIN PLAN function will do the job when it comes to tuning SQL. However, from time to time, you want to dig a little deeper and with APEX, that takes a little more care. Because APEX is a metadata-driven low code environment, the query you wrote to build your app is not the same query that APEX runs on your behalf.
For example, lets say I’ve built myself an interactive report with a simple query to the EMMPLOYEES table.
I might have run that query in SQL Workshop and it seems to perform fine, but perhaps I’m not seeing the same performance when the report is running within my app. The more feature-packed the region is (eg faceted search), the more APEX will augment your original query in order to provide the features needed on the page. This is a one of the beauties of a low-code environment – much of the SQL heavy lifting is done for you.
Thus in order to the see the true query that APEX ran, I can switch on debug and re-run the page to let me see the query that APEX ran on my behalf to support things like pagination, filtering, etc etc.
From the debug log, I can then take this query and start to look at how the true query runs in SQL Workshop, and look at its EXPLAIN PLAN output.
But often when I’m trying to tune a piece of SQL, a critical piece of the puzzle is comparing the estimated rows to the actual rows, so that I can see if the optimizer was working with good information. I’d like to be able to use the GATHER_PLAN_STATISTICS hint but in SQL Workshop to get the cursor output from DBMS_XPLAN, I’ll need the SQL_ID or I’ll need to save the entire sheet as a script so that it runs in a single trip to the database. That’s a little cumbersome for me.
To overcome this, I wrote a little procedure APEX_PLAN which takes your SQL as an input, automatically adds the GATHER_PLAN_STATISTICS hint, and runs the SQL in an anonymous block and sends back the plan statistics via DBMS_OUTPUT in the familiar command line style output format.
This lets me easily check the stats and make decisions on how to improve the SQL via optimizer statistics, or optimizer hints, or even recasting the base SQL that APEX used to build the runtime SQL.
If you think this will be useful for your own environments, you can grab the source from my git repo. It’s still very much a work in progress – I’d like to extend it so that you can just drop in pretty much any APEX region SQL source – but that will require bind variable support etc. Watch this space.
Update: The routine now has some rudimentary bind variable support as well, which should make it easier to simply paste in APEX generated SQLs that contain things like pagination binds etc.
Learning is not a spectator sport 
Really love this, Thanks! .. can you describe what are the minimum permissions to run this … I am getting currently this error: User has no SELECT privilege on V$SESSION
I think you’ll need the following
GRANT SELECT ON V_$SESSION
GRANT SELECT ON V_$SQL
GRANT SELECT ON V_$SQL_PLAN
GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL
Hi Connor,
I like the idea of your procedure, to work-around the artificial limitations of the web environment 🙂
But, I strongly dislike the idea that APEX has the “freedom” to take the SQL created by the developer and start to tweak it around for adding its own “spices”, just for making the GUI interface more “bells and whistles” -rich …
It looks to me weird that, instead of letting the core SQL to be completely under the developer’s control,
APEX takes that control in its own hands, and, maybe as a “compensation”, passes the GUI control to the developer,
who, instead of being required to have a strong SQL and PL/SQL knowledge (aka, database knowledge), is now rather required to master the whole orchestra of web development languages, for controlling the small bits-and-bytes
of the purely GUI aspects …
I remember the one-phrase description of APEX, as a tool “easy to use by those who master SQL and PL/SQL” …
but, it looks to me that this is not exactly the case, even for the simplest applications …
It looks like “low code” does in fact mean “more code”, aka, “tune the SQL that APEX tweaked out of the SQL you have already tuned …”
Well … old cows are probably not fitted to wear bells and whistles at their old necks 😦 …
Cheers & Enjoy your upcoming events 🙂
Iudith Mentzel
You of course *do* have the option of doing all the SQL yourself, but think of something like faceted search (eg the AskTom videos page). That page is driven via a single SQL that APEX generated for me, where my original query was basically “select * from videos”.
My view is almost opposite to yours – let the developers write SQL to get the data, and then let the tool continue use to SQL to expand on that for the UI needs. We get the benefit of using SQL to manage the data (rather than clumsy code in the 3GL layer) and yet a lot of the heavy lifting is taken care of.
This should be a one button output in the APEX debug page. For every Customer Query there should be this button and when I click there I should get the explain plan in a modal 🙂
This little tool I wrote *might* be a premonition of future capability 🙂
Thanks for the posts on APEX. My company is starting to use APEX more to extend EBS so this is great info.
QQ: Do you have documentation or a post about converting and configuring a tomcat hosted ORDS to point to a dataguard backed RDBMS?
thanks
jcp
As long as ORDS is pointing to a service name (not an instance) then when that service name migrates to the DG instance during a failover, then new connections will simply move over with it.
If you add a “Safe Harbour” statement instead of a FR-1001 number I’m satisfied 😊
EMMPLOYEES? I think you meant EMPLOYEES. 🙂