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)|
9 times out of 10, I reckon you should be using the bind variable syntax of
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
(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 organisationsAnd 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$sqlareaconfirmed 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.