Home » SQL & PL/SQL » SQL & PL/SQL » Function in predicate slowing query (11g)
Function in predicate slowing query [message #393905] Tue, 24 March 2009 19:54 Go to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
So I have the following query:

SELECT * FROM (
    SELECT * FROM tab
    WHERE a=:1 and b>:2
    ORDER BY b)
WHERE rownum=1


This runs very quickly (.01 seconds). If I apply a function to the bind variables like this:

SELECT * FROM (
    SELECT * FROM tab
    WHERE a=some_func(:1) and b>some_func(:2)
    ORDER BY b)
WHERE rownum=1


The performance plummets and the query takes over 100 seconds to execute.

All the function does is check whether the parameter is empty and if so returns another value. It's basically equivalent to nvl(:1, chr(0)).

CREATE OR REPLACE FUNCTION some_func(string1 in VARCHAR2)
RETURN VARCHAR2
BEGIN
    IF string1 IS NULL THEN
        RETURN chr(0);
    END IF

    RETURN string1;
END;


Does anybody know why it takes so long to execute the query with the function? It seems to me like the function would take almost no time to evaluate on the bind variables. Why does it increase the execution time so much?


Re: Function in predicate slowing query [message #393906 is a reply to message #393905] Tue, 24 March 2009 19:57 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Post EXPLAIN PLAN for all SQL
I suspect that the use of the function precludes use of index & forces Full Table Scan.
Re: Function in predicate slowing query [message #393907 is a reply to message #393906] Tue, 24 March 2009 20:18 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Here it is:

Plan hash value: 323142607
----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |     1 |   239 |    40   (3)| 00:00:01 |
|*  1 |  COUNT STOPKEY                 |                   |       |       |            |          |
|   2 |   VIEW                         |                   |  1456 |   339K|    40   (3)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY       |                   |  1456 |   189K|    40   (3)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| tab_INDEX        |  1456 |   189K|    39   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | IDX_tab_INDEX_03 |   262 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)
   3 - filter(ROWNUM=1)
   5 - access("a"="SOME_FUNC"(:1) AND 
              "b">"SOME_FUNC"(:2))



It looks like it is using the index. Here's the explain plan output from the query without the function:

Plan hash value: 4281029124
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |   239 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |                   |       |       |            |          |
|   2 |   VIEW                        |                   |     2 |   478 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| tab_INDEX        |     2 |   266 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_tab_INDEX_03 |  1324 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM=1)
   4 - access("A"=:1 AND "B">:2)
Re: Function in predicate slowing query [message #393908 is a reply to message #393905] Tue, 24 March 2009 20:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
The difference in EXPLAIN PLAN confirms the performance differences.
The SQL with the functions do alot more work (CPU) & access many, many, many more bytes of data.
I am at a loss as to how you might make it perform better.

You might have to modify the underlying objects to avoid the use for the functions if faster response time is really a requirement.

SQL> ALTER SESSION SET SQL_TRACE=TRUE;
The resultant trace file will show you exactly where time is being spent,
but if you can't figure out a suitable work around, it is only a learning exercise which won't change reality.
Re: Function in predicate slowing query [message #393909 is a reply to message #393908] Tue, 24 March 2009 20:45 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Thanks for taking a look.

I'm kind of curious, though, why doesn't it just evaluate the function on the bind variables and then use the results as the constraints?

Re: Function in predicate slowing query [message #393937 is a reply to message #393909] Wed, 25 March 2009 01:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You do realize that your query takes a random row from the inner set, right? The rownum=1 and order by in the inner query don't guarantee you that you'll get the top record from the inner query.
In order to get that, use row_number()
Re: Function in predicate slowing query [message #393941 is a reply to message #393905] Wed, 25 March 2009 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your function is called (at least) twice for each row.

As you are in 11g and function is deterministic, you can cache the function result in SGA, then you will have about the same time for both query.

Of course, you could use NVL instead of your function. It would be far better.

Regards
Michel
Re: Function in predicate slowing query [message #393979 is a reply to message #393905] Wed, 25 March 2009 04:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your problem is this:

In the original query, it is easy to determine, using the index, which rows meet the criteria a=:1 and b>:2, without reading all the rows.
Once you apply the functions, the only way to determine which rows meet the criteria a=some_func(:1) and b>some_func(:2) is to read every row in the table and apply the functions to them.
Re: Function in predicate slowing query [message #393992 is a reply to message #393905] Wed, 25 March 2009 05:13 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Telling oracle your function is DETERMINISTIC might help.
Re: Function in predicate slowing query [message #394062 is a reply to message #393937] Wed, 25 March 2009 10:38 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Quote:

You do realize that your query takes a random row from the inner set, right? The rownum=1 and order by in the inner query don't guarantee you that you'll get the top record from the inner query.
In order to get that, use row_number()



Is that really true?

Why wouldn't it return the first row of the sub-query? I was under the impression that that was exactly what rownum specifies. Now if my query looked like this:

SELECT * FROM tab
WHERE a=some_func(:1) and b>some_func(:2)
ORDER by b
WHERE rownum=1


I would agree that it would return a random row. Since my ordering is instead in a subquery, I would expect Oracle to order the sub-query and then take the first result from that set. In other words, I'd expect it to behave like this:

SELECT * FROM tab
WHERE a=some_func(:1) and b>some_func(:2)
ORDER by b
LIMIT 1
Re: Function in predicate slowing query [message #394064 is a reply to message #393941] Wed, 25 March 2009 10:42 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Quote:

As you are in 11g and function is deterministic, you can cache the function result in SGA, then you will have about the same time for both query.



I'm not entirely sure how to do that. I'll do some research and try it out.

Quote:

Of course, you could use NVL instead of your function. It would be far better.



This doesn't work. Using NVL yields the same horrible performance as with my function.

Re: Function in predicate slowing query [message #394065 is a reply to message #393992] Wed, 25 March 2009 10:50 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Wed, 25 March 2009 10:13
Telling oracle your function is DETERMINISTIC might help.


i.e.
CREATE OR REPLACE FUNCTION some_func(string1 in VARCHAR2)
RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
    IF string1 IS NULL THEN
        RETURN chr(0);
    END IF;

    RETURN string1;
END;
Re: Function in predicate slowing query [message #394066 is a reply to message #394062] Wed, 25 March 2009 10:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you rewrite your functions so that you can write:

WHERE some_func(a) = :b1

Failing that, can you perform some_func on the values before you bind them?
Re: Function in predicate slowing query [message #394067 is a reply to message #393992] Wed, 25 March 2009 10:52 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Quote:

Telling oracle your function is DETERMINISTIC might help.



Simple, yet effective. Very nice. This did exactly what I was looking for. Branding my function as deterministic yielded performance comparable to not using a function. Thank you and Michel for suggesting this.

Quote:

In the original query, it is easy to determine, using the index, which rows meet the criteria a=:1 and b>:2, without reading all the rows.
Once you apply the functions, the only way to determine which rows meet the criteria a=some_func(:1) and b>some_func(:2) is to read every row in the table and apply the functions to them.



This is what I was confused about. Why would Oracle apply the function to any row in database? The function is on the bind variable. Since the bind variables never change over the course of the query, it doesn't make sense why Oracle would evaluate this function more than once.

In retrospect, now that I know of 'DETERMINISTIC', this behavior makes a little more sense. I suppose Oracle can't determine that my function only relies on the bind parameter. My function could be using some other non-deterministic factor in its evaluation (the current time for example). In this light it makes sense to evaluate the function on the bind values every time a row is compared to the predicate.
Re: Function in predicate slowing query [message #394070 is a reply to message #394066] Wed, 25 March 2009 10:58 Go to previous message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Quote:

Can you rewrite your functions so that you can write:

WHERE some_func(a) = :b1



No. Values are stored in the DB as chr(0). Bind values can be passed in as NULL and need to be converted to chr(0).

Quote:

Failing that, can you perform some_func on the values before you bind them?



That was going to be my next plan of attack. Since branding my function as 'DETERMINISTIC' fixed the problem, I don't think that'll be necessary.
Previous Topic: Copy the image file to a folder
Next Topic: Extracting Months
Goto Forum:
  


Current Time: Fri Dec 09 07:34:52 CST 2016

Total time taken to generate the page: 0.14414 seconds