Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Calling function from select statement is slower then using subselect in select

Re: Calling function from select statement is slower then using subselect in select

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 26 Oct 2006 10:47:37 -0700
Message-ID: <1161884857.035093.97260@f16g2000cwb.googlegroups.com>

Tomeo wrote:
> [snip query with function]
>
> 3324 rows selected.
>
> Elapsed: 00:02:27.80
>
> Statistics
> ----------------------------------------------------------
> 29956 recursive calls
> 0 db block gets
> 15369502 consistent gets
> 0 physical reads
> 0 redo size
> 68024 bytes sent via SQL*Net to client
> 2861 bytes received via SQL*Net from client
> 223 SQL*Net roundtrips to/from client
> 6662 sorts (memory)
> 0 sorts (disk)
> 3324 rows processed
>
>
> [snip query with scalar subquery]
>
> 3324 rows selected.
>
> Elapsed: 00:00:04.87
>
>
> Statistics
> ----------------------------------------------------------
> 26632 recursive calls
> 0 db block gets
> 125433 consistent gets
> 0 physical reads
> 0 redo size
> 68029 bytes sent via SQL*Net to client
> 2861 bytes received via SQL*Net from client
> 223 SQL*Net roundtrips to/from client
> 6662 sorts (memory)
> 0 sorts (disk)
> 3324 rows processed
>
> Why when I use function instead subquery, my query takes so long?
> What I'm doing wrong?
>
> Tomas

The obvious difference in execution statistics is this: 15+ million consistent gets as opposed to just 125k, and a bit less recursive calls. Consistent gets are main contributor to the run time here. Why so much less cg's? Because when you use subquery, the optimizer sees that you use the same data in several places in the query, it doesn't need to read it again as it has to inside the function. Add to this SQL to PL/SQL and back context switches every function call requires and you get the picture.

Regards,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Thu Oct 26 2006 - 12:47:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US