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.

APEX performance issues with v()

One of the checks in the APEX Advisor is to determine if any instances of the v() function exist within your SQL.

This confirms one of the first APEX best practices I remember hearing, that you should use bind variables instead of v() within your SQL.
It makes sense, since v() is essentially making an interrogation on the session state table (wwv_flow_data).

Patrick Wolf talks more about performance with determinism here. Martin Giffy D'Souza summaries APEX variables nicely here.

In the comment thread Patrick also suggests the wonderful scalar subquery solution that works on all db versions
WHERE id = (SELECT v('MY_ITEM') FROM DUAL)

The trouble is, I think it's a difficult situation to test - how would you go about determining how many times Oracle decides to invoke the v() function, and for which parameters?

I came across this case was on a 10.2.0.4 db with Apex 3.1.2. I've added Groucho glasses to the query to protect the innocent/guilty.
It's also somewhat simplified. There were half a dozen instances of v(), but I've highlighted what turned out to be the offending line.

select * from a_6000_row_table p
where (  ...
or exists
  (select null
   from a_user_access_table aa
   where (aa.org_unit in (SELECT * FROM TABLE (InList_fn(a_pkg.genInList(p.year, p.org_unit))))
       or aa.org_unit in (SELECT * FROM TABLE (InList_fn(a_pkg.genDiffInList(p.year, p.id)))))
  and aa.inactive is null
  and aa.staff_id = v('LOCAL_USER_ID')
  and (  (aa.access_type in ('ABC','ABCXYZ'))
   or (   aa.access_type in ('DEF')
      and p.latest_status = 'YIPEE'))
  )

As it turns out line 9 wasn't alone in causing the query to take at least 5 seconds to return 1-10 of 6000 in an interactive report.
The first part of the where clause where it constructs a comma delimited list, converts it into a nested table, then interpreted as a table - also lends some form of hand in slowing down the query.

These function calls could probably be replaced with some form of WHERE EXISTS, but I think the original author had a steel plated encapsulation hat.

When first assessing this query, I thought the problem related to a stragg function in the column list which used a FOR-SELECT-LOOP, but then I spotted the obvious.
So I replaced all the v() references with bind variables, and all of a sudden the 5+ second query went sub one second.

I felt a little social, so I tweeted my little win, and Trent asked if I had more details, and I was curious to isolate the exact improvement as when I've briefly played with performance using v() functions on simple queries I came away unconvinced of any issues but happy to play it safe.

Some tests by themselves

1. Removing the IN list functions got the query to around 0.7s
2. Changing v() to a bind variable was around 1s
3. Changing v() to scalar subquery also gave a 1s query.
4. Setting entire query to use bind variables got down to about 0.5s

For those tuning experts out there who know much more about the Oracle mechanics than I do, I'd be curious to know if you can shed some light on how/why in particular these improvements came about.
For instance, what do you suppose the relationship between the IN list functions and the use of v() is?

For reference - I squeezed in a function call at line 10 that counted how many times it was called. It was always 6000 times, regardless of bind variable usage until I took out the call to the IN list, then it was 9 records. I didn't isolate the significance of that second number.

I remember when learning APEX it was difficult to determine when to use what syntax to refer to values in session state, so two lessons affirmed here today:

1. Bind variables work! Limit the use of v() function calls to assignments and conditions in PL/SQL packages
2. Scalar subqueries are just as awesome.

And I'll just add two more related points

1. Never (never say never) use &ITEM. syntax in queries - I've got a post in the works on that issue.
2. Don't forget about the nv() function, allowing you to compare the same datatype if you're dealing with numbers. You need to make your own dv function...

Scott

Injecting SQL into the New Year

No Oracle blog would be complete without a comment regarding SQL Injection.

This is a way to exploit a potential security vulnerability in the database layer of your application. Depending on the programming language used, and the way your SQL is written - this vulnerability could extend from viewing rows you're not supposed to; to viewing data from other columns/tables; to dropping/manipulating other database objects.

There are some security gurus out there, and I'd suggest you read some work by either Pete Finnigan or Peter Lorenzen.

For now I'll give a brief demo on an example that could be demonstrated in Oracle Application Express.

Let's say you have a simple report region on the EMP table, with a field allowing you to search by employee name:

You may wish to define this report region as SQL Query (PL/SQL function body returning SQL query), which allows you to dynamically create the SQL  - a common requirement in many applications.

The correct way to define this SQL would be to use bind variables. The following image demonstrates a simple example of this (using the Oracle 10g q-quote mechanism)

Now if this SQL was modified slightly, we introduce the possibility of SQL injection.

 

For those not familiar with the q-quote syntax, it may read:


v_sql := v_sql||'WHERE UPPER(ename) LIKE '''||:P3_ENAME||'%''';


You see, if we performed the following search, we could illicitly obtain all the rows from the query:

This happens because our SQL actually forms the following:


SELECT empno, ename, job, hiredate from emp WHERE UPPER(ename) LIKE 'S' or 1=1--%'


Where the OR 1=1 ensures all rows are returned. So not only are you taking a performance hit, you are creating vulnerabilities in your application. Lorenzen's paper describes some other potential threats within Apex.

If the region used bind variables, the same search criteria would just return no rows.

A simple example completed.

I hope everyone enjoyed any time off they may have had over the new year, I certainly did!