This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Dec 1, 2006 3:02 AM by 135285 RSS

Query Performance

Denes Kubicek Oracle ACE Director
Currently Being Moderated
I noticed that if I use the following:

v('P1_ITEM') instead of :P1_ITEM

queries take several times longer to execute. I am trying to use dynamic SQL and that works fine. However, as soon as I need to include page items as variables in my PL/SQL procedure (using v('P1_ITEM')), a normal, one second query, executes more than two minutes. Any ideas why, any similar issues?

Denes Kubicek
  • 1. Re: Query Performance
    VANJ Journeyer
    Currently Being Moderated
    Details, details!

    1. What is the region type, PL/SQL function body returning SQL query?

    2. If so, is the function body code right in the region definition or are you calling a stored function returning a string?

    If the latter, as you know, if you need to read session state from the function, you have no choice but to use the v('ITEM') notation.

    But if the function body is right in the region definition, you can use either notation. Is this your situation?

    Your description makes it seem like you have the option of using either notation but that the v() notation is slower than the :bind notation.

    3. Give us a small example, one using :ITEM notation that is fast and the same query using v('ITEM') notation that is slow.
  • 2. Re: Query Performance
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    I understand. Have a look here:

    http://htmldb.oracle.com/pls/otn/f?p=31517:57

    It doesn't realy matters where you use v('ITEM') notation - on your page, in your process... I found that if Item is NULL the query using this notation takes forever.

    Denes Kubicek
  • 3. Re: Query Performance
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    I have a page with a lot of optional items - used to search upon. In the query where clause
    I would say

    AND customer LIKE NVL(:P1_CUSTOMER_NAME, customer_name)

    However, If I need to run a procedure, using the items and need to use v('ITEM') notation,
    the procedure will take a long time to complete if my optional item is empty. This can be
    produced on the link I posted before. Allthough, there is no background process but just a normal query.

    Denes Kubicek
  • 4. Re: Query Performance
    60437 Employee ACE
    Currently Being Moderated
    Denes - It is no surprise that calling a PL/SQL function in the predicate for each row is much slower than using a bind variable. Even in compiled code, the v function should be used only to fetch session state into local variables which can, in turn, be used as bind variables. However, it is still curious that only when your item is null does the function take so much longer to run. It turns out to be a bug in the v function for that case. Try using v('p57_sales_rep',null,null) as a workaround for this bug. This prevents the function from searching the preferences table for the item's value, which it erroneously thinks is indicated if it finds a null value for the item in the in-memory session cache. The correct behavior should be for it to proceed to search the preferences table only if it does not locate the item name in the session cache, not if it locates the name and finds a null value.

    I'm not sure, but you might be eligible for a prize for this one. Thanks for setting up the example.

    Scott
  • 5. Re: Query Performance
    445907 Newbie
    Currently Being Moderated
    OK this sounds reasonable...but let me extend the question...
    <br>
    If we have to make parameterized view based on let's say "APP_USER" (to ensure some security and in our case "Virtual Private Database" is not accepted option) then approach like this:
    <br>
    create or replace view as 
    select *
    from users_table
    where user_name = v('APP_USER');
    <br>
    is not OK (according performance mentioned before)? Is there any other approach to achieve the same functionality ?
    <br>
    THX!
    <br>
    P.S.
    <br>
    We saw that bug before in mentioned views much before, but in our case, quried table was table from db_link database, so we thought it is problem with db_link and parameter query! So we decide to leave that approach...now looks like we were maybe wrong...
    <br>
    <br>

    Message was edited by:
    Funky
  • 6. Re: Query Performance
    60437 Employee ACE
    Currently Being Moderated
    That view should work fine, I see no better alternatives.

    Scott
  • 7. Re: Query Performance
    445907 Newbie
    Currently Being Moderated
    Ok!

    When this bug could be fixed?
  • 8. Re: Query Performance
    60437 Employee ACE
    Currently Being Moderated
    next release, use workaround until then.
  • 9. Re: Query Performance
    VANJ Journeyer
    Currently Being Moderated
    Even in compiled code, the v function should be used only to fetch session state into local variables which can, in turn, be used as bind variables

    Scott: A predicate like
    where column = v('ITEM')
    would cause the v function to be invoked for each row regardless of how the v function itself is written, wouldn't it?

    The optimization you refer to above would kick in only if we re-write the above predicate as
    where column = (select v('ITEM') from dual)
    See Using the "V" function in dynamic queries

    This would allow the optimizer to realize that return value of the v() function is invariant for the entire query and can be treated as a bind variable.

    Denes: In my experience, for non-trivial queries, I find that, regardless of bind variable notation or V() notation and regardless of the bug uncovered in this thread, using a predicate like
    where col=nvl(:ITEM,col)
    tends to be not very performant. I rewrite that as
    where :ITEM is null or col = :ITEM
    Sometimes even the performance of this is not satisfactory, in which case a PL/SQL function body returning SQL query is indicated
    ...
    if :ITEM is not null then
      q := q || ' and col=:ITEM';
    end if;
    ...
    Both these techniques allow the optimizer to properly optimize the query.

    Thanks.
  • 10. Re: Query Performance
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Scott,

    I was afraid you will mention the difference between bind variables and functions...

    The thing with NULL values is only the half of the truth. But, since you mentioned
    a price :), I was investigating further and came to a conclusion:

    1) the workarround you mentioned does a bit speed up the performance

    2) however, the significant improvment in the performance can be reached only
    if you index the column used in your expression. Once again, I put an example
    on the same page:

    http://htmldb.oracle.com/pls/otn/f?p=31517:57

    There you can see a difference between all different uses.

    Denes Kubicek
  • 11. Re: Query Performance
    le Journeyer
    Currently Being Moderated
    Denes,

    I agree with Vikas - function V is called for each row.

    You can see it in trace file.
    It can be fixed with DETERMINISTIC key in function:

    create table tmp as select * from ALL_OBJECTS

    select count(*) from tmp
    COUNT(*)
    ----------
    42300
    1 row selected in 0.047 sec

    select count(*) from tmp where object_name = 'xxxxxxxxxxxxxxx';
    0 rows selected in 0.047 sec

    create or replace function V1(Name in varchar2) return varchar2
    DETERMINISTIC is
    begin
    return(v(name));
    end V1;

    select count(*) from tmp where object_name = V1('P1_NAME');
    0 rows selected in 0.047 sec.

    create or replace function V1(Name in varchar2) return varchar2 is
    begin
    return(v(name));
    end V1;

    select count(*) from tmp where object_name = V1('P1_NAME');
    0 rows selected in 2.83 sec.

    I think that function V is deterministic and probably this key word could be added to
    its code.

    Lev
  • 12. Re: Query Performance
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Lev,

    Thanks for the tip. I know the function is executed
    once for each row. That wasn't even a point of my
    question. What I was asking was why it performs so
    badly if the item it is evaluating is simply NULL.

    If you have read my previous post you may try creating
    indexes and will see how it boosts performance in this
    particular case. This is a solution I will go with,
    regardless of the fact if there is a bug or not.

    I took a table containing arround half a million of
    records (accounts payables) and rund the following query
    with the following result:
    SELECT supplier_name
      FROM SYN_AP_INVOICES
    WHERE supplier_name LIKE V ('p1_item', NULL, NULL)

    Description     Value

    recursive calls     253
    db block gets     0
    consistent gets     9390
    physical reads     0
    SQL*Net roundtrips to/from client     4
    sorts (memory)     5

    query execution time: 19 secs
    After creating an index on the column supplier_name:
    CREATE INDEX syn_ap_inv_suppl_idx ON SYN_AP_INVOICES (supplier_name)
    the result was as follows:
    Description     Value

    recursive calls     20
    db block gets     0
    consistent gets     6
    physical reads     0
    SQL*Net roundtrips to/from client     4
    sorts (memory)     1

    query execution time: 156 msecs.
    Denes Kubicek
  • 13. Re: Query Performance
    VANJ Journeyer
    Currently Being Moderated
    SELECT supplier_name
    FROM SYN_AP_INVOICES
    WHERE supplier_name LIKE V ('p1_item', NULL, NULL)


    After creating an index on the column supplier_name

    Denes: For a query like the above, without an index on the SUPPLIER_NAME column, Oracle will do a full-table scan. Creating an index on the supplier_name column will improve performance, regardless of APEX, V() function or any of that stuff. You know all this.

    I know the function is executed once for each row

    That is exactly the point here.

    What I was asking was why it performs so badly if the item it is evaluating is simply NULL.

    As Scott indicated, there is a tiny bug with the function but the effects of the bug are being greatly magnified because the v() function is being called once for each row. So, a performance enhancing tip (in addition to or instead of the index) would be to minimize the number of the times the function is called.

    One option is to use that SELECT from DUAL as I showed in my earlier post.

    Another alternative is to call the function once to get the value and use SYS_CONTEXT in your query.
    dbms_session.set_context('MY_CTX','NAME,NVL(v('P1_ITEM'),'%'));
    ...
    where supplier_name like sys_context('MY_CTX','NAME')
  • 14. Re: Query Performance
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    Vikas,

    This discussion is getting interesting. I didn't explain what I want to achive. Maybe this leads to a confusion and eventually I could get helped and solve a problem in another way:

    1. I am using dynamic SQL in my procedure and SQL query is stored in my item,

    2. SQL query may or may not reference other page items. I can't use :ITEM notation
    but v('ITEM') notation.

    What approach would you sugest beside using the v function?

    Denes Kubicek
1 2 Previous Next