This content has been marked as final. Show 21 replies
What approach would you sugest beside using the v function?
Well, if your dynamic query needs to read from session state, you have to use the v() function.
I already suggested another approach in my earlier post that you could use in your procedure i.e. fetch session state values just once, store them in session context and refer to them as bind variables using the special sys_context() function.
See http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1288401763279 and other threads on AskTom for details on the sys_context function.
You are proposing an approach which I understand. However, If my SQL is like this:
SELECT any_valueand it is processed in the background by a procedure, how would you write the
WHERE any_value = v ('any_item')
procedure to apply the dbms_session.set_context on any given number of
page items dynamicaly?
processed in the background by a procedure
This is the first time you are mentioning this. Kind of an important detail, don't you think?!
The v() function can be used to read session state only when called directly from a authenticated APEX session. By background job, do you mean a DBMS_JOB that runs asynchronously from the APEX session that launched it? If so, then, by definition, it cannot access session state. You would need to store the session state data that the job needs along with some appropriate identifier before you fire off the job.
The background job can then access those stored values and construct your dynamic SQL using the sys_context technique I mentioned earlier. Needless to say, that technique just makes the generated SQL bind-variable friendly so that that shared pool is not cluterred with similar SQL, usual stuff.
[You seem to understand everything, are aware of all the various techniques and constructs involved and respond to others' suggestions with "Yes, I know that but ...". It would be better if you lay out all your requirements first, all your constraints and then ask for help. Just to avoid back and forth]
Belive or not, I do understand when I say I do. Why would I use v function instead of :item if
I wouldn't need to? My original post was a question on the function and the performance
when this function used. Then you mention a dbms_session.set_contex technique and I
asked you to show me how this techinque works for a particular requirement and you keep
refering to the technique you mentioned earlier... Can you show that technique or not?
My original post was a question on the function and the performance when this function used
OK so your original question, as stated, has been answered. During the process of answering this question, a bug in the v() function was uncovered that I am sure the APEX team has duly noted. This bug, as I understood it, would be responsible for increasing the execution time of the v() function slightly.
asked you to show me how this techinque works for a particular requirement
The particular requirement being when the code in question is being run as a background job (DBMS_JOB), correct?
I responded to this in my earlier post.
I refered you to a AskTom thread where the sys_context() technique is demonstrated in great detail. that technique just makes the generated SQL bind-variable friendly so that that shared pool is not cluterred with similar SQL, usual stuff
I also said that a DBMS_JOB is unable (to the best of my knowledge) to access session state unless that data were to be made available to it via your own mechanisms.
What question would you like me to address, that I have not done so already?
I found this thread after I already did some investigations about possible performance impacts when using functions in a SQL statement. Posted my findings at http://inside-apex.blogspot.com/2006/11/caution-when-using-plsql-functions-in.html
During my investigation I also found out why your index solution is much faster.
It's not only because your column has an index now and you are doing an index access on it. I think the real issue is something else, why it is so much faster.
If the optimizer is using this column index, it seems that he has some special logic for the function which is compared against this column. The V function is just called once, instead of the 1.000's times before! That is making your performance difference.
To the APEX team:
1) Wouldn't it make sense to add the DETERMINISTIC for the optimizer. A lot of inexperienced users are using V in SQL statements and probably complain about how bad Oracle performs.
2) Based on the code of the 2.0 V function (in 2.2 it's wrapped): wouldn't it be much faster to index the internal cache by item name instead of BINARY_INTEGER. This would save looping thru all stored values, especially if you have a lot of items...
for i in 1..wwv_flow.g_substitution_item_name.count loop if l_item = wwv_flow.g_substitution_item_name(i) then ... end if; end loop;
if wwv_flow.g_substitution_item_name.EXISTS(l_item) then .... end if;
as Lev already suggested and proved with a short test he did, that the V function has a much better performance if you add the DETERMINISTIC optimizer hint. I have created a drop in replacement for the V, NV and a new DV function, which are wrapper functions for the existing APEX V- and NV functions. The drop in should work with all 2.x versions.
Check it out at http://inside-apex.blogspot.com/2006/12/drop-in-replacement-for-v-and-nv.html
Any comments are welcome!