Monthly Archives: May 2007

A great APEX Podcast

Podcasts about APEX are few and far between, but I’ve just been listening to a great one called “The Buzz about Oracle ApEx” which you can find on Lewis Cunninghams blog.

Lewis interviews and chats to David Peak, Application Express Product Manager from Oracle and Peter Martin, an APEX user, there are some very interesting nuggets of information in there.

I hope this is just the start of more APEX related podcasts.

Be careful of V

Every so often a question will pop-up in the APEX forums or I’ll get an email from someone who has a query that runs great in dev but once they deploy the application to production performance nosedives. Now obviously there isn’t a magic answer for why this would happen, however time and time again I find that one cause of this problem is use of the v() function in queries rather than using the bind notation.

For those unfamiliar with Application Express, the v() function is a function which returns the value of a session item, for example v(‘APP_USER’) would return the name of the currently logged in user, whilst v(‘P1_NAME’) would return the value of the P1_NAME page item stored in session state. You can also use what is known as bind notation to refer to session state items, so rather than using v(‘APP_USER’) you can use :APP_USER in queries.

Since I get this question a lot, I wanted to show why, even though using the v() function will work, you should be aware of the consequences of using it in your queries. So here’s a quick demonstration, note that rather than creating an application, or using the SQL Workshop I’m actually going to use SQLPlus for the demo. You might be thinking at this point “what?!?! but you can’t access the v() function outside of an APEX app!”, well actually you can, but you need to know a couple of ‘tricks’.

Firstly I connect, via SQLPlus, to a schema that is associated with my workspace, I’ve created a largish table based on repeated records from the ‘SCOTT.EMP’ table –

jes@TESTDB> select count(*) from emp2;
COUNT(*)
———-
229376

Now I need to ‘fake it’ so that it looks like I’m running this from inside an APEX session, I can do this using the wwv_flow_api.set_security_group_id routine:

jes@TESTDB> select wwv_flow_api.get_security_group_id from dual;

GET_SECURITY_GROUP_ID
———————
0

jes@TESTDB> exec wwv_flow_api.set_security_group_id;

PL/SQL procedure successfully completed.

jes@TESTDB> select wwv_flow_api.get_security_group_id from dual;

GET_SECURITY_GROUP_ID
———————
3.6112E+15

Now a ‘typical’ sort of query you might perform in APEX would be querying a particular table for records that you were allowed to see, in other words where the value of v(‘APP_USER’), which represents your logged in username, matches the value in the table.

In my dummy data, the distribution of records per username is evenly spread:

jes@SPDB2> select distinct ename, count(*) from emp2 group by ename;

ENAME COUNT(*)
———- ———-
ADAMS 16384
ALLEN 16384
BLAKE 16384
CLARK 16384
FORD 16384
JAMES 16384
JONES 16384
KING 16384
MARTIN 16384
MILLER 16384
SCOTT 16384
SMITH 16384
TURNER 16384
WARD 16384

So, I can use a query like this:

jes@TEST> select count(*) from emp2 where ename = v(‘APP_USER’);

However that won’t work just yet, as we’re ‘faking’ an APEX session so v(‘APP_USER’) will return null, so we need to set that ourselves manually:

jes@TESTDB> exec apex_application.g_user := ‘SCOTT’;
PL/SQL procedure successfully completed.

jes@SPDB2> select v(‘APP_USER’) from dual;
V(‘APP_USER’)
—————-
SCOTT

So here I’ve manually set the value so that v(‘APP_USER’) will return ‘SCOTT’. We can now run the query:

jes@TESTDB> set timing on;
jes@TESTDB> select count(*) from emp2 where ename = v(‘APP_USER’);
COUNT(*)
———-
16384

Elapsed: 00:00:04.29

We can see that it took around 4 seconds for this query to run on my test system, now we can check what happens if we use bind notation (i.e. :APP_USER rather than v(‘APP_USER’)).

jes@TESTDB> var APP_USER varchar2(100);
jes@TESTDB> exec :APP_USER := ‘SCOTT’;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
jes@TESTDB> select count(*) from emp2 where ename = :APP_USER;

COUNT(*)
———-
16384
Elapsed: 00:00:00.04

Note that here I created a variable in SQLPlus and used that to pass as a bind variable into the query. The difference in time is huge, using the v() syntax the query took 4 seconds, using the bind notation it returned pretty much instantly (the 0.04 seconds is more or less negligible when measured using the SQLPlus ‘set timing on’ command).

The question is, why the big difference in timing? Well the answer is simple, the v() syntax is a function which is evaluated for every row that you are checking against. In other words in this simple example the v() function was called 229,376 times (that is the number of records in my emp2 table) even though the value it returns does not change (it is always the value of APP_USER we pass into it). When we use the bind notation, the optimizer uses the static value of the bind variable and we avoid the overhead of calling the v() function.

The reason why some people get ‘caught’ out by this is that in your dev and test system you will typically only have a fraction of the data you have in your live system, so while your query that uses v() syntax might perform great against a table with 300 records in it, once you roll it out to live you might find that it performs extremely badly against a table with 300,000 records in it. This is one of the reasons why I’m a big advocate of trying to use dev/test data that is representative of your live environment (to avoid ‘nasty surprises’ like this once you roll out to live).

It’s also important to note that this isn’t a ‘failing’ of the v() function in APEX, it’s really just basic SQL knowledge that you should be aware of. If you include a function call in a query that queries a large dataset then you *may* pay a performance penalty for calling that function over and over again (I say *may* since there are a few things you can do to mitigate that performance hit, although I won’t go into those things here).

Also, I have seen other workarounds suggested, such as creating a wrapper v() function which is declared as deterministic, i.e. it is telling the optimizer that the function will always return the same output value for a given input value, thus avoiding the optimizer need only call it once and re-use the value rather than calling the function for each row. However creating a wrapper round the v() function will take you aware from a ‘stock’ APEX install and you will need to remember to recreate the wrapper when you upgrade APEX (as well as testing that there are no other implications of creating the wrapper etc).

Using a wrapper like this is really just hiding the fact that you should use the bind notation for queries rather than using the v() syntax (use the v() syntax in PL/SQL).

Also, as with most things to do with Oracle, there is more than one way to write the original query so that the v() function is not called for each row, for example we could rewrite it like this –

jes@SPDB2> with myname as (select v(‘APP_USER’) as username from dual)
2 select count(*) from emp2 where ename = (select username from myname);

COUNT(*)
———-
16384

Elapsed: 00:00:00.03

However rather than having to rewrite your queries in such a way, I find it far easier to remember to use the different notations in this way –

:ITEM for SQL, v(‘ITEM’) for PL/SQL

UKOUG 2007

I was right in the middle of trying to decide whether to make an abstract submission for the UKOUG event in December and was coming to the conclusion that I’d ran out of time to think of something to submit, when I spotted they’ve now extended to the deadline for abstract submissions to 1st of June! Which means I now have no excuses for not putting on my thinking cap (a figure of speech…I don’t really have one although some days I might wish that I did) and trying to come up with something that people would find useful.

There are a few things that I’ve learned about presenting so far –

  • It always takes far longer to prepare your presentation (and whitepaper if you need to do one) than you think it will.
  • Linked to the above statement, no matter how early I start to prepare I’ll always end up wishing I’d started a week earlier.
  • The night before the presentation I’ll end up changing a bunch of slides and material anyway.
  • There’s always a fine line between presenting something that *you* find interesting and presenting something that *other people* will find interesting.
  • I’ll invariable forget to pack my mini-DVI cable and won’t be able to hook up my Macbook to the projector until I can ‘borrow’ one from one of the other Macbook carrying presenters.
  • No matter how many cups of water I put under the podium, it will always be one less than I need (am I the only person who finds presenting thirsty work?).

However, I have to say, although presenting can be one of the most nerve-wracking things to do, it is also incredibly rewarding, even just having one person coming up to you after the presentation and asking a question or saying that it was useful to them makes it all worthwhile.

So, it’s thinking cap time again, what abstract can I submit to UKOUG that APEX people would find most useful?

APEX – Delivering Pages in 3 Seconds or Less

When I was at the Collaborate 07 event, I gave a presentation entitled “APEX: Delivering Pages in 3 Seconds or Less (because users don’t like waiting)”, which was a bit of a ‘tongue-in-cheek’ title since APEX is capable of delivering pages much faster than that, but the title is really a reference to how quickly we as end-users (because we’re all end-users of websites) get bored and impatient if we have to wait too long for the page to be rendered after we do something.

The paper covers a couple of techniques you can use with APEX applications which can have huge effects on the scalability, performance and resource requirements for your Application. The two techniques are image caching and webserver compression and in my opinion there is almost no reason why every APEX application shouldn’t be taking advantage of these techniques.

As part of the Collaborate 07 presentation, I had to prepare a Whitepaper which goes into a great deal more detail than I could fit into an hour long presentation. I have subsequently had quite a few requests from people if they could see the Whitepaper, so for anyone who wants to take a look at a couple of techniques that could just make a difference between your application scaling to support more users and errr not scaling….then you can download a copy of the whitepaper here.

Announcing European APEX Training Days

APEX Evangelists, of which I am a co-founder, is very pleased to announce the general availability of our first Application Express European Training Days.

The 3-day course, covering many different advanced Application Express topics, will be held from the 4th to the 6th of September 2007 at the exclusive Le Plaza Hotel in Brussels (Belgium).

The training will be given by some of the most experienced APEX specialists in the world (and also perhaps a special ‘mystery’ guest speaker!), there will also be a lot of opportunity for Q&A and discussion about APEX. It will also be a great opportunity for people to meet others in Europe who are using APEX too.

For more details and to register for the training, head over to here
(direct link to the registration page here).

Why APEX running inside the database is a GOOD thing!

I’ve had quite a few discussions with people regarding whether APEX running inside the database is a good thing or a bad thing. Personally I believe it is definitely a good thing, I see no downsides really. However a recent forum thread

For me, the fact that APEX runs inside the database means that one of the major positives is you are able to take full advantage of each and every database available to you. A recent forum thread illustrates this very nicely. The poster in the thread has deleted some files and has unfortunately performed a commit before realising their mistake.

Here is where the features of the database can help you, for example we are able to take advantage of the Flashback feature to get the database back to the state it was in before the files were deleted.

The user in the forum deleted some files from the apex_application_files table (which is actually a view on an underlying table restricted so that you can only see files associated with your workspace), so first of all we query the apex_application_files view –

jes@DBTEST> select count(*) from apex_application_files;
COUNT(*)
———-
0

The reason this returns zero records is because I’m running the query from SQLPlus and not from the SQL Workspace (i.e. my workspace isn’t defined in SQLPlus), we can fix that in the following way –

jes@DBTEST> select wwv_flow_api.get_security_group_id from dual;
GET_SECURITY_GROUP_ID
———————
0

jes@DBTEST> exec wwv_flow_api.set_security_group_id;
PL/SQL procedure successfully completed.

jes@DBTEST> select wwv_flow_api.get_security_group_id from dual;
GET_SECURITY_GROUP_ID
———————
1.1830E+15

Note that you only need to perform the wwv_flow_api.set_security_group, but for completeness I showed that before the call the SGID was set to 0, but afterwards it was set to the correct value.

Now we can requery the apex_application_files view –

jes@DBTEST> select count(*) from apex_application_files;
COUNT(*)
———-
8

So now we can see that 8 files have been uploaded for this workspace. So, we can simulate what the user did by deleting the files, however before we do that I’m going to check in another SQLPlus session what the current SCN (System Change Number) is, since we’ll need that later –

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
3334372

So, the current SCN is 3334372, now we can delete the records –

jes@DBTEST> delete from apex_application_files;
8 rows deleted.

jes@DBTEST> commit;
Commit complete.

jes@DBTEST> select count(*) from apex_application_files;
COUNT(*)
———-
0

They’re gone…deleted, no way back (well, I could restore from backups of course), however there’s an easier way. Firstly we shut down the database and mount it.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 452984832 bytes
Fixed Size 779648 bytes
Variable Size 133175936 bytes
Database Buffers 318767104 bytes
Redo Buffers 262144 bytes
Database mounted.

Now use Flashback to go back to the state we were in back before we deleted the files (SCN 3334372).

SQL> flashback database to scn 3334372;
Flashback complete.

So now we can open the database, but we also need to remember to RESETLOGS since we have used Flashback (i.e the current logs are no longer needed/valid)

SQL> alter database open resetlogs;
Database altered.

Now we can requery the apex_application_files view –

jes@DBTEST> exec wwv_flow_api.set_security_group_id;
PL/SQL procedure successfully completed.

jes@DBTEST> select count(*) from apex_application_files;
COUNT(*)
———-
8

and we see that the data is back!

Now you could be saying at this point, so what? I could do that if I was using Java instead of APEX, all we’ve done is use Flashback technology to flashback the database.

Ahhh but there’s the great thing about APEX, imagine for a moment that instead of performing a delete on apex_application_files, that you were in the process of a massive enterprise change management task.

Imagine you have just run 394 SQL scripts to upgrade your production application from v3.41 to v3.42 and then you update your APEX application…imagine now that something terrible happens and it doesn’t work in production (every developer has this experience at least once in their life right?)…the question is, how easily can you regress your production environment back to the working environment you had before? Well the answer is, with APEX extremely easily, since you can flashback the database and not only will you go back to the working versions of your schema (the old versions of your objects etc), but because your APEX applications also live inside the database you will automatically go back to the version of the Application as it was at that point (i.e. the working version).

Compare this with say a traditional client-server solution, or a middle-tier solution where not only would you need to regress the database, but I would also need to regress the middle tier applications back to the previous versions, I would also need to remember to revert back to the old versions of any configuration files that the apps might need to and in my experience the fewers things you need to do the less chance there is of forgetting something.

There are a huge number of Oracle database features that make every area of managing production systems easier, from development through to change management, Flashback is just one out of many reasons why I think having APEX live inside the database is *definitely* a good thing!

**Note: The usual caveats apply here, don’t test the above code on your production system and then come back to me if you break it. When it comes to using things like this the best advice I can give is read the docs, read the docs, read the docs. Also please note that I’m not advocating that ‘flashback database’ is your first course of action, you should actually consider using some of the other Flashback technologies, such as flashback table etc where appropriate since flashing back the entire database is only desirable in certain circumstances, however for the purposes of this post it shows how easy it is to use.