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.

One more COALESCE vs NVL example to finish the week...

How many times do you think you've ever written something like this
nvl('x',user)

Small cost? What if you executed it all the time.

What if we wrote it as
coalesce('x',user)

No biggy?

Executed on my laptop with tracing on

TRACING SAGE@sw10g> begin
  2  for i in 1..power(2,17) loop
  3    if nvl('x',user) = 'x' then
  4    null;
  5    end if;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.42

Without tracing it was still about 4 seconds.

TRACING SAGE@sw10g> begin
  2  for i in 1..power(2,17) loop
  3    if coalesce('x',user) = 'x' then
  4    null;
  5    end if;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

Why?

Check out the tkprof output...

SELECT USER 
FROM
 SYS.DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute 131072      2.21       1.04          0          0          0           0
Fetch   131072      2.81       0.53          0          0          0      131072
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   262144      5.03       1.58          0          0          0      131072

Every reference to USER in your PL/SQL or Forms code executes that statement from dual.

Sometimes we need to consider the little things, and considered using COALESCE instead of NVL.