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

5 thoughts on “Be careful of V

  1. Patrick Wolf

    Hi John,

    good that you pointed that out again! It can’t be said often enough.

    I think your last statement has to be clarified a little bit, because it’s maybe misleading for new APEX users.

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

    “v(’ITEM’) for PL/SQL” in that context means when you write a PL/SQL package and you want to access APEX page item. If you write PL/SQL APEX processes/computation/… you should also use the :ITEM syntax!

    Greetings
    Patrick

    Like

    Reply
  2. John Scott

    Hi Patrick,

    The bit at the end is really just an over-simplification of when you should use them, as I showed you can easily use v() in queries (it works) and you can also use the bind notation.

    I think a degree of ‘common sense’ needs to be used to determine when best to use each bit of syntax. As you say, the v() syntax should really be used in PL/SQL packages which are to be used outside of the ‘context’ of APEX whereas :BIND notation should be used whenever inside the ‘context’ of APEX.

    Like

    Reply
  3. niphtrique

    The use of sys_context(‘USERENV’, ‘CLIENT_INFO’) is a good alternative.

    I did some testing:

    TST>select count(1) from tst_emp where empname = trim( upper( v( ‘APP_USER’ ) ) );

    COUNT(1)
    ———-
    16384

    Verstreken: 00:00:04.17
    TST>select count(1) from tst_emp where empname = ‘NIPHTRIQUE’;

    COUNT(1)
    ———-
    16384

    Verstreken: 00:00:00.82
    TST>exec dbms_application_info.set_client_info(‘NIPHTRIQUE’);

    PL/SQL-procedure is geslaagd.

    Verstreken: 00:00:00.01
    TST>select count(1) from tst_emp where empname = sys_context(‘USERENV’, ‘CLIENT_INFO’);

    COUNT(1)
    ———-
    16384

    Verstreken: 00:00:00.84

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s