1 2 Previous Next 21 Replies Latest reply: Dec 1, 2006 5:02 AM by 135285 RSS

    Query Performance

    Denes Kubicek
      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
          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
            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
              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
                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
                  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
                    That view should work fine, I see no better alternatives.

                    Scott
                    • 7. Re: Query Performance
                      445907
                      Ok!

                      When this bug could be fixed?
                      • 8. Re: Query Performance
                        60437
                        next release, use workaround until then.
                        • 9. Re: Query Performance
                          VANJ
                          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
                            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
                              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
                                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
                                  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
                                    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