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.

APEX variables in SQL

When I was first learning Oracle Application Express, I found one of the trickiest things was deciphering when to use which substitution string. Martin Giffy D'Souza succinctly describes variable reference options here.

Oracle supplies a number of built-in substitution strings. The APEX documentation provides a number of examples of here, for example generating links and referring to the session number:

(from the Oracle Documentation)

Trouble is, each syntax has it's place - trouble is if you use the wrong one you could impact the performance of your application - as I described here.

9 times out of 10, I reckon you should be using the bind variable syntax of :SESSION

What I find when I visit clients a number of developers - typically those self-taught, use the &SESSION. syntax in queries - which is bad, and this goes for any variable you want to reference.

The reason for this is because you're essentially flooding your shared SQL area in the database with similarly parsed SQL statements. This is bad because Oracle sweats when it has to to a hard-parse on your queries, as opposed to recognising a query you've executed before, and running it again with different bind variables - this is soft-parsing which Oracle can do blindfolded with it's little toe.

To elaborate, I created to basic report pages with slightly different SQL statements.

select org_id, name -- Good query
  ,'f?p='||:app_id||':1:'||:app_session lnk
from organisations;

select org_id, name -- BAD query
  ,'f?p=&APP_ID.:1:&SESSION.' lnk
from organisations;

It's a subtle different, but the highlighted line 6 is where the curry will burn.

I enabled tracing on my application by adding a parameter to the end of my URL
http://localhost:8080/apex40/f?p=105:7:1368517209058184::NO&p_trace=YES
(more information on tracing your Apex application can be found in the documentation)
I opened both pages a number of times, logging out a few times in the process to generate new session numbers

Then I located my trace file, ran tkprof over it, opened up the output and searched for "organisations". Forgetting all the other information for the moment, there was one instance of this:

select org_id, name
  ,'f?p='||:app_id||':1:'||:app_session lnk
from organisations

And a number of instances that all looked very similar

select org_id, name
  ,'f?p=4000:1:1223945495716883' lnk
from organisations

select org_id, name
  ,'f?p=105:1:3914512356591996' lnk
from organisations

select org_id, name
  ,'f?p=105:1:4361599949844983' lnk
from organisations

select org_id, name
  ,'f?p=105:1:8029570771757325' lnk
from organisations
...

See a concerning trend?

A quick peek in v$sqlarea confirmed the same issue - although I would like to ask the Oracle APEX team (or someone who knows more than me in these matters) why the parse calls is above the execution count for my "good sql" - it doesn't seem right to me.

Looking at it a second time, I notice the fourth result is from the application builder, so I would guess the extra parses come from me defining the page (5 to update the report... really?!)

One would need to obtain finer measurements to determine the hard vs soft parse count difference.

select sql_text,executions, parse_calls
from v$sqlarea 
where sql_text like '%--%from organisations%';

At the end of the day, please keep issues like this in mind when writing your queries.

Insync 2011 Day 3

First you may be thinking - hang on, there was only 2 days in the Insync conference, wasn't there?

Just like a PL/SQL boolean, there is true, false, and unknown to that statement.
Ooh, did I really go there?

True - there was two days of scheduled presentations.

False - like any conference, there are often fringe events scheduled before or after the main event.

Unknown - for those unfortunate enough not to have heard of the event, in the Sydney Convention & Exhibition Centre there was an extra day-long session entitled "A Day of Real World Performance". More information can be found here.

It was co-presented by

• Tom Kyte - author of AskTom - directing the core of the discussion with slides in a centre screen, providing a developer perspective.
• Graham Wood - Oracle Database Performance Architect - dishing out Enterprise Manager output as an Exadata box is manipulated
• Andrew Holdsworth - Head of Oracle's Real World Performance team.

It was less of your usual seminar style format, more of a conversational piece - which made it much easier to concentrate after two days of conference presentations. This style also made it more engaging. There was a general script, but Graham mentioned to me at one point that after nine roadshows, none ever really come out the same - I'm not surprised!

They kicked off by loading a terabtye of data into a remote Oracle Exadata box, then used that as a platform to discuss performance issues from compression to statement parsing to index use - but nothing like you've ever really seen before. Andrew kept tweaking settings in the exadata machine to show how they can affect performance - accompanied by a screen full of numbers whaling up and down; Graham showed pretty graphs that illustrated what was going on post-change; and Tom get adding his pearls of wisdom with his easy listening voice.

Mark Lancaster also described the event in his blog and I must agree - if this roadshow comes to a town near you, it is a must see. If you can't, try out the videos. They don't do it any justice, but apparently they have been used to sway decisions by the powers that be in the correct direction.

A little tip - don't book flights for conferences without checking/looking out for these events - I know a few people who missed out because they booked flights too early. Mind you, I also know there are a few people who flew up from Melbourne specifically for this day.

And to cap it off - Tony, Barbara & I finished the day with a beer overlooking Darling Harbour in a wonderful sunny Sydney afternoon.

... but then we had to head to the airport to go home...

Scott

Recursive Subquery Factoring

OK, this post is partially for my benefit because I'm sure in future I'll need to re-think how this works - and I'll want the basic syntax on hand.

From 11g Release 2, the SQL WITH clause has been extended to allow recursive queries. This new syntax complies with the ANSI standards, as opposed to Oracle's CONNECT BY, START WITH keywords.

It's officially called "recursive subquery factoring", but it's also known as tree-walking or a hierarchical query.

Unfortunately in this simple example, the ANSI syntax is somewhat more verbose. I wonder if this evens out as the complexity of the query increases, or if the readability of the code is "scalable"?

-- 10g method
SELECT o.org_id, o.name, o.parent_org_id, level
FROM organisations o
CONNECT BY PRIOR org_id = parent_org_id
START WITH org_id = 1000;

-- 11 method
WITH org_sage (org_id, name, parent_org_id, reportlevel) AS
  (SELECT org_id, name, parent_org_id, 1 reportlevel
   FROM   organisations
   WHERE  org_id = 1000
   UNION ALL
   SELECT o.org_id, o.name, o.parent_org_id, reportlevel+1
   FROM  org_sage p, organisations o
   WHERE p.org_id = o.parent_org_id
)
SELECT org_id, name, parent_org_id, reportlevel
FROM org_sage;

Another unfortunate outcome is a quick test of throughput - 10000 iterations on my laptop gave the following respective timings.

.81 secs
.000081 secs per iteration
3.56 secs
.000356 secs per iteration

So it might be best to compare the two in your scenario/data, and consider the value of using the ANSI format in your case.

Further documentation can on hierarchical queries can found here, and in the SQL Language Reference, under the SELECT statement, looking at subquery factoring.

Remember, the key to understanding recursion is understanding recursion :-)

Opinions of the masses

When to hard-code is always a big question when it comes to programming languages. SQL certainly has a few exceptions. PL/SQL - not so much.

So when it comes to hard coding within PL/SQL, I'd say I'd agree with Steven Feuerstein. I agree with most of his standards, and his article on this is concise.

In the other corner, Jeff Kemp raises an excellent point about hard coding in SQL. We worked on a complex project together once where hard coding of certain literals was important - and done correctly. Commenting never goes astray. This example is way out of context so it's hard to illustrate, but if I may:

SELECT something
FROM my_table
WHERE entity_type = 123 /* XYZ type */
AND entity_id = :my_bind_parameter;

Where entity_type contains the value that will never change, and hence Jeff's adage - "do not change this code"

Also note Narendra's comment on Jeff's article - be very careful about the use of cursor_sharing = FORCE in these scenarios.

I just wanted to highlight these two related articles.