APEX Survey Results: Addressing Performance

Yep, I'm still doing this. A bunch of questions to come, many worth the visit. Just a few weeks between drinks, so to speak. My annual review is a little late, too. Anyway...

Time for the performance questions in my 2015 survey. A favourite topic of mine, and my boss, Penny Cookson, lives for tuning.

Q9. How do you proactively address performance? (tick all that apply)

Tune SQL - I would be surprised if this wasn't the top result. Plenty of SQL used in APEX applications, and why not tune them?

Limit Interpreted PL/SQL - this is good practice in general, but in the world of APEX, this means moving inline code from application into packages. You can get pretty quick wins doing this with any plugins. Read here for details.

Materialised Views - a database construct that can aggregate complex information at regular intervals, to be queried many times with simpler SQL over fewer rows. I've seen interesting examples that obfuscate layers to external information using pipelined functions. Or you could just use it for your menu.

Care using v() - Particularly important when referencing page items, since this function would execute a query on the session state table. While values such as APP_ID come from a persistent package variable, it's still context switching between SQL and PL/SQL, so it would be even better to use bind variable.
where id = (select v('APP_ID') from dual)
I think any use of v() should be questioned. SQL queries should use bind variables (not substitution strings) and packages should be parameterised.

Page Workflow/Design - why refresh the entire page when a partial refresh would do? A well designed application will limit network traffic and the amount of queries necessary to serve the data. Refreshing regions on demand with dynamic actions is one of the most regular things I do. Declarative page modals in APEX 5.x have also made APEX life a lot more comfortable.

Global Page Modularisation - Performance also about the developer not repeating tasks over and over, causing future maintenance headaches.

JSS/CSS File Management - minifying code can reduce network traffic, as can well designed libraries; CDNs; and declarative options. I understand APEX Front End Boost can help.

Care with jQuery selectors - selectors can be abused just like table indexes. I have some commentary on this here.

Region Caching - possibly underutilised, but the ability to cache region on the global page in APEX 5.0 opens options, as do some new APIs.

data-attributes - jQuery related, what I meant by this was to offer more information during report generation, which can help interactivity and reduce AJAX activity. Though read consistency issues should be considered. See a basic example here.

Other - a few people suggested a well designed data model. Touche. Even more got stuck into it in the next question on performance return on investment.

The User Interface attributes are also an area worth being familiar with, particularly in regard to application level file management.

I don't need to worry about performance - yeah, right.

Truncating LISTAGG prior to 12.2

I've been following the LISTAGG problem for a while because I find the function useful.

The problem being this error:
ORA-01489: result of string concatenation is too long

It relates to when the result set is over 4000 characters, eg:

with data as (select rpad('x',400, 'x') str from dual connect by level <=10)
select listagg(str,', ') within group (order by null)
from data

Ten rows of 400 characters equals 4000, add the concatenation operator and you get the error.
If the second parameter was null, this would actually be within the 4000 limit.

There are improvements in 12.2 to handle this overflow.

with data as (select rpad('x',400, 'x') str from dual connect by level <=11)
select listagg(str, ',' on overflow truncate '...' with count) within group (order by null)
from data

The output would end
...*********,...(8985)

But if you're still in 12.1 or earlier, you need other options

One solution is a custom LISTAGG function to return a CLOB.

You could make a decision on the incoming length, which probably isn't always practical, or you can concatenate only the first x rows using a CASE statement

with data as (select rpad('x',400, 'x') str from dual connect by level <=10)
select listagg(case when rownum < 5 then str end
              ,', ') within group (order by null)
            ||'First 5 of '||count(*)||' shown.' as str
from data;

Which ends the output with
...********First 5 of 10 shown.

I suspect this will do for a lot of scenarios. Ordering is up to you.

I don't remember encountering this idea in the context of LISTAGG, but it seems nice and simple.

LiveSQL example:
https://livesql.oracle.com/apex/livesql/file/content_EHQAVVYH6J2XMN5VHTFJYT3Y8.html

Escape Special Characters APEX Demo

A few weeks ago I wrote more detail than expected regarding escaping of special characters.

I thought I'd add a simple demonstration, for reference.

Consider the following query, with variations of escaped column output.

with data as 
  (select q'[G'day,]'||chr(10)
           ||'Scott<strong>loves</strong>'
           ||'<br>APEX<script></script>' as string 
   from dual)
select 
 -- UI default
 string dflt
  -- where no tags expected
 ,apex_escape.html(string) protected
  -- good for most things
 ,apex_escape.html_whitelist(string) whitelisted 
  -- replace line feeds with HTML line break. Could use chr(13)
 ,replace(apex_escape.html_whitelist(string),chr(10),'<br>') protected_custom
 -- UI naughty
 ,string naughty
from data

The major difference being unescaped output looks like
"G'day,
Scott <strong>loves</strong><br>APEX<script></script>"

While escaped output looks like
"G'day,
Scott <strong>loves</strong><br>APEX<script></script>"

It determines how the browser will interpret these tags and display them to the end user.

Note that the first field called "DFLT" is using the default setting, therefore escaping special characters.
All other four columns do no explicitly escape these characters, deferring protection to the SQL.

Colum Attributes across four columns

The output looks like the following. The first line may look similar to any time you attempt to use APEX_ITEM in a classic report without turning this flag off.

Sample output, using template: Value Attribute Pairs - Column

So depending on what you're trying to display, you might need a particular combination of code / settings.

1. Default - default APEX behaviour, no column settings adjusted.
2. Protected - uses apex_escape package to do the same job as declarative attribute
3. Whitelisted - Certain markup tags are allowed, but all others are still escaped
4. Protected custom - often I want to replace line feeds/carriage returns in data with HTML line breaks. This combination facilitates the best of both worlds
5. Naughty - avoid unticking Escape Special Characters attribute without protecting data within SQL. This is enabled Cross Site Scripting (XSS)

The naughtiness can be demonstrated by adding alert("Hello universe") between the <script> tags. The unescaped column will mean the browser will render an alert when the page renders.

This is bad because instead of an alert, it could be some malicious JavaScript.

Further security tips can be found at Recx.
APEX-SERT is also 5.0 ready.

Re-evaluating APEX Authorization Schemes

Authorization schemes in Oracle APEX are used to control access to page, buttons, and all sorts of other components.

In my experience, these are best defined at a privilege level, where the same privilege could be allocated to multiple business roles, but that's for another post.

In this post, I want to mention a cool API function called apex_authorization.reset_cache, which helps control the behaviour of these authorization schemes.

Preface

While googling something else I stumbled across an interesting function called apex_util.reset_authorizations, only to find it was deprecated in 5.1, replaced with the same (but renamed) function in another package.

APEX_UTIL was getting big, and even though the team was trying to manage it, 5.1 was the first time I noticed it reduce the number of procedures. Too many procedures mean some gold nuggets get lost, perhaps until they're moved to a more specific package.

I'd say we're all guilty of putting a procedure in the inappropriate package or letting a "utils" package grow too big. Me probably more than many, but there's been a lot of clean-up in 5.1. If there's any documentation you read about 5.1, let it be the release notes.

I also found the function mentioned in an old email I marked for blogging about because of an OTN forum post I was listening to. Not because of the original topic, but another deprecated/renamed procedure mentioned within.

Authorization Evaluation Point

The default evaluation point for authorization schemes is once per session. This means the first time APEX comes across a component protected by an authorization scheme, it will evaluate it and remember the result.

Authorization Scheme Evaluation Point

This default option is best for performance, but if you want to afford your users the ability to pick up changes in authorization without needing to log out then back in, then you can use one of the other options.

Re-setting authorization schemes at runtime

A cool API exists called apex_authorization.reset_cache. The documentation states

"it resets the authorization caches for the session and forces a re-evaluation when an authorization is checked next".

This means you can provide a button to the user that would clear the slate for all authorization scheme outcomes in that session and force APEX to re-evaluate any authorisation schemes it encounters.

This will be handy for me because it offers a chance to do this on demand at runtime, instead of once or 'all the time'. It also does the job across multiple applications that share authentication.

This is one of quite a few library functions now available in 5.1, in fact, this particular one has been around in some form since 4.2, but buried in apex_util.reset_authorizations.

Under the hood

If you want to see the interactions in the underlying APEX table, this query will help.

select s.*, a.application_id, a.authorization_scheme_name
from apex_050000.wwv_flow_session_authz$ s
join apex_application_authorization a
  on a.authorization_scheme_id = s.authorization_id
where session_id = 16678957299354

See, it's not magic. Just clever.

References

apex_authorization.reset_cache
Providing Security through Authorization
APEX 5.1 Release Notes

APEX attributes for Escaping Special Characters

A relatively common on the forums is regarding the escaping of special characters in reports, but it seems the developer isn't always sure what is actually happening and how to how to search for it.

It seems I've had this on my "to blog" list since April 2015, but now that 5.1 has been released, it seems more people are coming out to leave 4.x can't work out where the Standard Report Column option is.

APEX 4.x Display As attribute

This was required when HTML was present in the query, either to add tabular items manually using apex_item, or to style data (though you should use HTML Expression instead)

Example of special characters being escaped

For instance, if you've written a query like so

SELECT APEX_ITEM.CHECKBOX2(1, empno, 'CHECKED') chk, ename
FROM   emp
ORDER BY 1

And are only seeing the HTML code in your column output

<input type="checkbox" name="f01" value="7369" CHECKED />

Then you need to Escape Special Characters, now found in the Security section of the column properties as a Yes/No option.

APEX 5.0 Escape Special Characters attribute

This is defaulted to Yes to help protect from cross-site scripting (XSS), a common security concern in the web world where data entered by users is stored in the database, then when rendered it can be interpreted as HTML code.

Set to No to allow your data to be rendered as you may expect. 
Note that in the 5.0 component view this is still referenced as Display As - Standard Report Column.

The change in terminology is documented in the 5.0 release notes

Report column property naming differences

Please note that if setting this attribute to no, you should still make efforts to protect your applications by escaping data where possible. For example, if I wanted to replace all carriage returns with the HTML line break, you can still escape your data then add HTML content.

replace(apex_escape.html(card_title), chr(10), '<br>')

You could probably do a variation of this using apex_escape.html_whitelist

If you're combining two fields, separated by the line break:

apex_escape.html(phone)||'<br>'||apex_escape.html(email)

then you might as well use HTML Expression and keep your data/UI layers separate.

HTML Expression attribute

Check out the open source project APEX-SERT to help find potential security concerns with your Oracle APEX applications.

See escaping examples in APEX reports here
http://www.grassroots-oracle.com/2017/01/escape-special-characters-apex-demo.html