Dynamic WHERE Clause for APEX Interactive Reports and Grids
Absolute Technologies began converting our applications’ technical platform from Oracle Forms and Reports to Oracle Applications Express (APEX) several years ago. In doing so, we developed a proprietary platform for APEX that further accelerates application development and support, as well as providing built in reporting tools for Oracle EBS. We call it ProView.
APEX provided significant benefits overall, but we experienced issues in one key area: performance of Interactive Reports.
Here’s how we solved it!
Problem Analysis
Run Time Environment
We started testing the APEX run time queries. APEX wraps your original SQL statement, the inner query, in the SQL for interactive APEX column selection, filtering, and row counting page set management, known as the outer query. It then applies bind variables and runs the query. We tested these wrapped queries using SQL*Plus and SQL Developer. We learned that unlike SQL*Plus and SQL Developer, APEX does not take advantage of bind variable peeking and adaptive cursor sharing, both of which enable the optimizer to find more efficient query plans. Thus, a query might look efficient enough in the SQL development environment but be less efficient when run from within APEX in an Interactive Report or Grid.
Without bind variable peeking, the optimizer cannot evaluate the selectivity of the variables, which is important when determining the best plan for the SQL statement. If you use literal values instead of bind variables the optimizer is effective.
User Friendly Page Design
Another issue is that our Interactive Report pages are designed to support several End User searching and filtering use cases tailored to the business application and data. They include a report filtering parameter region with numerous APEX Page Items that are incorporated in the base query as bind variables. Among the filter Page Items, some correlate with table indexes, but to provide flexible pre-configured user analysis, we usually have several non-indexed Page Items. If the user does not need a Page Item for their analysis, they leave it NULL, in which case it should have no filtering effect. The where clause predicates for these Page Items are coded using NVL, DECODE, or CASE. Users would typically only use a few of the filter parameters, some indexed and some not so. Nevertheless, all the Page Item bind values needed to be evaluated but the NULL ones would have been discarded if bind peeking were enabled.
These Page Item filters work with any of the End User’s interactive APEX IR and IG filters, which APEX adds to the outer query. The user can click Actions > Filter, and create unlimited Column and Row filters, which APEX ANDs together in the WHERE clause of the outer query. The End User can toggle each of these filters on or off.
Performance Solution
We decided that the where clause predicates would perform better if we could create them in APEX dynamically at query time, using only the Page Item bind variables the user wanted. For APEX Interactive Reports and Grids, the developer cannot do that. There would be SQL Injection risks. Reports that are driven by a Function that returns the query cannot be the basis for IRs and IGs.
For example, assume we have a View named SODA_FND_USER_FUN_CON_V. The page we want to improve is page 20, and has a query of the form:
- Create a copy of SODA_FND_USER_FUN_CON_V named SODA_FND_USER_FUN_CON_PV. This optional step supports transitions where some queries can use the original _V view and supply predicates, and others can use the new _PV view for better performance.
- In page 20:
- Change the APEX IR or IG region’s base query from using the _V view to use SODA_FND_USER_FUN_CON_PV.
- Remove or comment out the WHERE clause series of predicate expressions from the query.
- Develop a dynamic WHERE clause (DWC) Function, SODA_FND_USER_FUN_CON_PF. It processes the Page Items it finds in page 20, returning the resulting WHERE clause. The code uses the APEX V Function, as in v(‘P20_NAME_A’), to reference to a page item’s value. It finds the non-null Page Items, and builds the predicate with literal values from the V Function rather than bind variables. This requires transcribing the logic removed from the query into the function.
- The VPD Function knows that by default, APEX Page Items are of the form ‘P’<page ID number>_<item name>. If the Page Item names are consistent between pages and differ only in the <page ID number>, the same function can support many pages that have queries on the same Table or View.
- Finally, create a Virtual Private Database (VPD) policy to associate the _PV view and _PF function objects: SODA_FND_USER_FUN_CON_PY. The VPD policy invokes the dynamic WHERE clause function.
At query execution time, the optimizer needs a hard parse for every APEX query on our SODA_FND_USER_FUN_CON_PV view. Since the dynamic predicate uses actual literal values, it can determine the optimum query plan.
Example Dynamic Where Clause
We use a conditional display for developers and support staff so they can see the content of the Dynamic Where Clause when they run the page as an end user.
Conclusion
APEX Interactive Reports and Grids without bind peeking can approach the performance seen in the development environment using SQL*Plus and SQL Developer where bind peeking is available.
by Skip Straus
Vice President and APEX Product Manager
Absolute Technologies, Inc.
From Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-05
11.1.3 Bind Variable Peeking
In bind variable peeking (also known as bind peeking), the optimizer looks at the value in a bind variable when the database performs a hard parse of a statement.
When a query uses literals, the optimizer can use the literal values to find the best plan. However, when a query uses bind variables, the optimizer must select the best plan without the presence of literals in the SQL text. This task can be extremely difficult. By peeking at bind values the optimizer can determine the selectivity of a WHERE clause condition as if literals had been used, thereby improving the plan.
…
When choosing a plan, the optimizer only peeks at the bind value during the hard parse. This plan may not be optimal for all possible values.