Hi,
Answer of the question in the subject is “Using too much V in SQL statements”. Let’s see why and what should you do.
First things first, I am NOT a APEX developer. I am a DBA and Database Developer but some of my clients are using APEX and I am dealing with many sql functions that uses V function in apex. Since the day 1, I did not like this V function because developers using it in very different ways and it returns VARCHAR2 but it uses for all kind of compares without any conversion functions (this is not on the V function of course).
So, what is the problem here? At my clients’ codes, V function is used heavily in sql statements. Some queries that I dealt with lead me to investigate V function because even if the query has no performance issue, still, it takes too much to complete. When I checked the V function, I find out that it is not DETERMINISTIC (which shouldn’t be because of the logic of DETERMINISTIC but I will talk about this later). Let’s do some tests but before I must explain a few things. I hacked the code of V a little bit and add some logging codes to demonstrate how it is worked and performance issues about it but I won’t explain how I do it (probably many of you can do it easily) and share any source code of it. whenever a V function is called my logging code will write the parameter name of v function into “my_log” table.
I added my log codes, set an apex session in my sql developer session and run these selects:
|
|
delete my_log; select v('OBJECT_ID'),v('OBJECT_ID'),v('OBJECT_ID'),v('OBJECT_ID') from dual; select log_data from my_log; OBJECT_ID OBJECT_ID OBJECT_ID OBJECT_ID |
I delete log table first then call a select statement with four V function in select list run over DUAL table and then check the log table, as you see it is called 4 times. So same data is read 4 times and 4 plsql function call is occurred. this caused too much context switch between SQL and PLSQL and this can reduce your performance dramatically. Especially, Where clause is extremely dangerous, why? Because V function might run for all rows in the table which will cause a disaster.
|
|
drop table tmp purge; create table tmp as select * from all_objects; -- I set 12 into OBJECT_ID variable in apex session delete my_log; select object_name, v('OBJECT_ID') obj_id1, v('OBJECT_ID') obj_id2 from tmp where object_id = v('OBJECT_ID') FET$ 12 12 -------------------------------------- select Count(*) from my_log; 79647 |
as you can see, this query called V function 79.647 times why? because there are 79.645 rows in the TMP table. Since I use V in Where clause, condition ( object_id = v(‘OBJECT_ID’) ) executed for every rows in the table (79.645 rows) and only 1 of the rows meets the condition so select list runs 1 times and there are 2 more V function (same with the one in where clause) so totally V called 79.647 times. I hope you see the problem here. in your application code if you use V function in your sql statements, it might be called too much!
How about an index? yep that will help:
|
|
create index i_tmp on tmp(object_id); delete my_log; select object_name, v('OBJECT_ID') obj_id1, v('OBJECT_ID') obj_id2 from tmp where object_id = v('OBJECT_ID'); FET$ 12 12 -------------------------------------- select Count(*) from my_log; 3 |
so it run only 3 times at this execution because this time search operation (where condition) is run on an index not table so V function is called 1 times in where clause and 2 times in select list. this means you should have indexes at least for your condition columns.
is that enough? Let me show you a pseudo code on a database:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Select Some Columns, Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...), Decode(V('SOME_ITEM'), ...) From Some Tables... Where Col1 = V('SOME_ITEM') .... |
so many same V function call in select statement. creating and index will help you about where clause how about select list? since all items in select list cause a function call (even with same parameters) let’s test performance:
|
|
set timing on; declare a varchar2(80); begin for i in 1..10000 loop select v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID'), v('OBJECT_ID') into a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a from dual; end loop; end; / Elapsed: 00:00:03.229 |
calling this query for 10.000 times (or you can thing this as calling sql 1 times for 10.000 rows) takes 3.2 seconds (20 same V function). if we change the code to this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
declare a varchar2(80); begin for i in 1..10000 loop with my_data as ( select /*+ no_merge */ v('OBJECT_ID') v_object_id from dual ) select v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id into a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a from my_data; end loop; end; / Elapsed: 00:00:00.553 |
this query takes only 0.5 seconds. timings might significant but think about 1000 different queries that run on your database 1000 times per day and calculate spend of time for just calling V function. it is a lot! your significant amount of db time might be wasted.
Let’s improve the code a little bit more:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
declare a varchar2(80); v_value varchar2(80); begin v_value := v('OBJECT_ID'); for i in 1..10000 loop with my_data as ( select v_value v_object_id from dual ) select v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id, v_object_id into a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a from my_data; end loop; end; / Elapsed: 00:00:00.199 |
even better. if you increase number of call, you will see that first 2 plsql code timings will increase linearly but the last code (code just above) will give you almost same performance. Remember, V is a function not a variable. Use variables instead of using V if you can.
Even if these might help your system, main problem is still there: V is not DETERMINISTIC. you might change your codes as I suggested above or create more indexes but many developers also use V in VIEWS which is another and for me a bigger disaster. it is harder to modify the code you cannot use bind variables etc… What would happen if V was deterministic? I modified V and made it deterministic and let’s see the result with first test case without index:
|
|
drop table tmp purge; create table tmp as select * from all_objects; -- I set 12 into OBJECT_ID variable in apex session delete my_log; select object_name, v('OBJECT_ID') obj_id1, v('OBJECT_ID') obj_id2 from tmp where object_id = v('OBJECT_ID') FET$ 12 12 -------------------------------------- select Count(*) from my_log; 2 |
pay attention that there is no index but V is just called 2 times (1 for where clause and 1 for select list, event if select list has 2 copy). it was 79.647 without DETERMINISTIC if you remember the first result.
So making V as deterministic will solve almost every kind of problem but only problem is what I looking for here is not deterministic. Why? Let me explain what deterministic is first. if a function always returns same value with same parameters then this function is a deterministic function like TO_CHAR or SUBSTR. this allow oracle to know that if calls with same parameters are repeated, it can be cached and used first calculation result for on going calls. This way, if same function with same parameters are called in a SQL statement, that function is called only 1 times but this is not V. Result of V is not deterministic because application might change the value of a V item and this means V call with same parameters can return different results because it is just return a context variable value.
So, it shouldn’t be deterministic but it should be something that acting like deterministic for an sql statement. I don’t think any apex developer wants to see a change of V values during a select statement. I think they assume values of V won’t change during the sql statement. So, there should be something like DETERMINISTIC_FOR_CALL in Oracle database. we should be able to define plsql objects as “deterministic” while they are used in a sql statement. Their result should be calculated once and used during the sql call. I think this is also necessary for any plsql object that we can call on SQL. because current structure of plsql is kind of violating the consistency. A select statement returns rows as they were when the sql call started. even if the rows are changed during the sql call, result of the statement won’t be affected from those changes but PLSQL breaks that. when a function calculated for every row, a select statement inside the plsql object can return different results because it will be affected by on going changes. This is a database option. Database should provide us this functionality, Apex team has nothing to do about it.
By the way, I am really frustrated about data type conversions about V. since V returns VARCHAR2, many developer does not care about type conversions. if you compare a V value with a number then you should use “TO_NUMBER(V(…))” for example or at least (as I search for it) NV function (number result of V). this is another issue to me.
For now, I suggest to avoid using V in a SQL statement as much as possible and use bind variable if you can. Let’s say you have some SQL with so many V call with same parameter, what can you do? you can “force” oracle to cache them:
|
|
drop table tmp purge; create table tmp as select * from all_objects; set timing on; select STATUS, min(obj1), min(obj2), min(obj3), min(obj4), min(obj5) from ( select v('OBJECT_ID') obj1, V('OBJECT_ID') obj2, V('USER_NAME') obj3, V('USER_NAME') obj4, V('USER_NAME') obj5, STATUS from tmp ) group by STATUS; Elapsed: 00:00:03.262 |
there are better way to write this sql but I just want to demonstrate a way. as you see this query takes 3.26 seconds to complete because of too many V calls. you can rewrite your query and cache the V results first and then use them as this:
|
|
select STATUS, min(obj1), min(obj2), min(obj3), min(obj4), min(obj5) from ( select obj obj1, obj obj2, usr obj3, usr obj4, usr obj5, STATUS from tmp cross join (select /*+ no_merge */ v('OBJECT_ID') obj, V('USER_NAME') usr from dual) v_values ) group by STATUS; Elapsed: 00:00:00.192 |
I am adding a NO_MERGE hint to prevent optimizer to merge queries before query run. as you see this version returns in 0.19 seconds 15 times faster becuase V functions are called only 2 times (2 different V item is selected OBJECT_ID and USER_NAME).
Edit:
Another and maybe better solution for this problem is using your own V function and create it as deterministic:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Create Or Replace Function My_V ( P_Item In Varchar2, P_Flow In Number Default Null, P_Scope In Varchar2 Default 'SESSION_AND_USER', P_Escape In Varchar2 Default 'N' ) Return Varchar2 Deterministic As Begin Return V(P_Item, P_Flow, P_Scope, P_Escape); End; / set timing on; select STATUS, min(obj1), min(obj2), min(obj3), min(obj4), min(obj5) from ( select my_v('OBJECT_ID') obj1, my_V('OBJECT_ID') obj2, my_V('USER_NAME') obj3, my_V('USER_NAME') obj4, my_V('USER_NAME') obj5, STATUS from tmp ) group by STATUS; Elapsed: 00:00:00.149 |
this is faster than previous join method. only thing that you should change V calls with MY_V.
I hope, this helps you on your systems. Now after those tests, I must connect with my clients and explain them to change their coding to gain significant db time.
wish you all healthy days.
