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

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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 25 Oct 2006 06:48:03 -0700
Message-ID: <1161784083.766539.114250@m7g2000cwm.googlegroups.com>

hpuxrac wrote:
> Tomeo wrote:
> > Hi, I have SQL query, that I'm trying to tune up.
> >
> > If I simplify it, I can say: When I run my select statement, where I'm
> > calling function
> > (that is just returning ABS(SUM) value) in main query, then it takes
> > much more longer, than if I replace function with subselect(subquery).
> > Why this happens? I'm expecting almost the same execution time. Please
> > see query, execution time, explain plan and statistic below.
> >
> > SQL*Plus: Release 10.1.0.4.2
> > Connected to:
> > Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
> > With the Partitioning, Oracle Label Security, OLAP and Oracle Data
> > Mining options
> > JServer Release 9.2.0.7.0 - Production
> >
> > set timing on
> > set autotrace traceonly explain statistics
> > PROMPT ORIGINAL QUERY
> > SELECT
> > --DISTINCT
> > accd.tbraccd_term_code Term,
> > p.spriden_id "VEC",
> > Fz_Sbalunapplied2(accd.tbraccd_detail_code, accd.tbraccd_pidm)
> > "Balance",
> > s.spriden_id "Person"
> > FROM
> > tbbdetc detc,
> > spriden s,
> > spriden p,
> > tbraccd accd,
> > spraddr addr
> > WHERE accd.tbraccd_crossref_detail_code = detc.tbbdetc_detail_code
> > AND s.spriden_pidm = accd.tbraccd_crossref_pidm
> > AND p.spriden_pidm = accd.tbraccd_pidm
> > AND s.spriden_change_ind IS NULL
> > AND p.spriden_change_ind IS NULL
> > AND detc.tbbdetc_dcat_code IN ('FEE','TUI')
> > AND p.spriden_entity_ind = 'C'
> > AND addr.ROWID(+) = F_Get_Address_Rowid
> > (s.spriden_pidm,'ENRLADDR','A',SYSDATE,1,'S',NULL)
> > AND accd.tbraccd_term_code LIKE '200400%';
> >
> > 3324 rows selected.
> >
> > Elapsed: 00:02:27.80
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=RULE
> > 1 0 NESTED LOOPS (OUTER)
> > 2 1 NESTED LOOPS
> > 3 2 NESTED LOOPS
> > 4 3 NESTED LOOPS
> > 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'TBRACCD'
> > 6 5 INDEX (RANGE SCAN) OF 'TBRACCD_CREDITS_INDEX' (N
> > ON-UNIQUE)
> > 7 4 TABLE ACCESS (BY INDEX ROWID) OF 'TBBDETC'
> > 8 7 INDEX (UNIQUE SCAN) OF 'PK_TBBDETC' (UNIQUE)
> > 9 3 TABLE ACCESS (BY INDEX ROWID) OF 'SPRIDEN'
> > 10 9 INDEX (RANGE SCAN) OF 'SPRIDEN_KEY_INDEX' (UNIQUE)
> > 11 2 INDEX (RANGE SCAN) OF 'SPRIDEN_KEY_INDEX' (UNIQUE)
> > 12 1 TABLE ACCESS (BY USER ROWID) OF 'SPRADDR'
> >
> > 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
> >
> > set autotrace off
> > PROMPT SOURCE FOR function Fz_Sbalunapplied2
> > select text from all_source where name = UPPER('Fz_Sbalunapplied2')
> > order by line asc;
> >
> > TEXT
> > --------------------------------------------------------------------------------
> > FUNCTION Fz_Sbalunapplied2(DETAIL_CODE IN VARCHAR2, PIDM IN NUMBER)
> >
> > RETURN NUMBER
> > AS
> > RN_UNAPPLIED NUMBER (17,2) := 0;
> > BEGIN
> > SELECT ABS(SUM(NVL(ACCD.TBRACCD_BALANCE,0)))
> > INTO RN_UNAPPLIED
> > FROM TBRACCD ACCD,
> > TBBDETC DETC
> > WHERE ACCD.TBRACCD_DETAIL_CODE = DETC.TBBDETC_DETAIL_CODE
> >
> > AND ACCD.TBRACCD_DETAIL_CODE = DETAIL_CODE
> > AND ACCD.TBRACCD_PIDM = PIDM
> > AND DETC.TBBDETC_TYPE_IND = 'P';
> > RETURN RN_UNAPPLIED;
> > END Fz_Sbalunapplied2;
> >
> > 16 rows selected.
> >
> > Elapsed: 00:00:00.12
> >
> > <b>Here I replace function with query, that is inside of function: </b>
> > SELECT --DISTINCT
> > accd.tbraccd_term_code Term,
> > p.spriden_id "VEC",
> > (SELECT ABS(SUM(NVL(ACCD.TBRACCD_BALANCE,0)))
> > FROM TBRACCD ACCD,
> > TBBDETC DETC
> > WHERE ACCD.TBRACCD_DETAIL_CODE = DETC.TBBDETC_DETAIL_CODE
> > AND ACCD.TBRACCD_DETAIL_CODE =
> > accd.tbraccd_detail_code
> > AND ACCD.TBRACCD_PIDM = accd.tbraccd_pidm
> > AND DETC.TBBDETC_TYPE_IND = 'P') "Balance",
> > s.spriden_id "Person"
> > FROM
> > tbbdetc detc,
> > spriden s,
> > spriden p,
> > tbraccd accd,
> > spraddr addr
> > WHERE accd.tbraccd_crossref_detail_code = detc.tbbdetc_detail_code
> > AND s.spriden_pidm = accd.tbraccd_crossref_pidm
> > AND p.spriden_pidm = accd.tbraccd_pidm
> > AND s.spriden_change_ind IS NULL
> > AND p.spriden_change_ind IS NULL
> > AND detc.tbbdetc_dcat_code IN ('FEE','TUI')
> > AND p.spriden_entity_ind = 'C'
> > AND addr.ROWID(+) = F_Get_Address_Rowid
> > (s.spriden_pidm,'ENRLADDR','A',SYSDATE,1,'S',NULL)
> > AND accd.tbraccd_term_code LIKE '200400%';
> >
> > 3324 rows selected.
> >
> > Elapsed: 00:00:04.87
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=RULE
> > 1 0 SORT (AGGREGATE)
> > 2 1 NESTED LOOPS
> > 3 2 TABLE ACCESS (FULL) OF 'TBRACCD'
> > 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'TBBDETC'
> > 5 4 INDEX (UNIQUE SCAN) OF 'PK_TBBDETC' (UNIQUE)
> > 6 0 NESTED LOOPS (OUTER)
> > 7 6 NESTED LOOPS
> > 8 7 NESTED LOOPS
> > 9 8 NESTED LOOPS
> > 10 9 TABLE ACCESS (BY INDEX ROWID) OF 'TBRACCD'
> > 11 10 INDEX (RANGE SCAN) OF 'TBRACCD_CREDITS_INDEX' (N
> > ON-UNIQUE)
> > 12 9 TABLE ACCESS (BY INDEX ROWID) OF 'TBBDETC'
> > 13 12 INDEX (UNIQUE SCAN) OF 'PK_TBBDETC' (UNIQUE)
> > 14 8 TABLE ACCESS (BY INDEX ROWID) OF 'SPRIDEN'
> > 15 14 INDEX (RANGE SCAN) OF 'SPRIDEN_KEY_INDEX' (UNIQUE)
> > 16 7 INDEX (RANGE SCAN) OF 'SPRIDEN_KEY_INDEX' (UNIQUE)
> > 17 6 TABLE ACCESS (BY USER ROWID) OF 'SPRADDR'
> >
> > 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?

> It is almost always faster to use just SQL instead of a combination of
> SQL and PLSQL function calls if you can.

> Tom Kyte says ( paraphrasing ) " Do it all in SQL ( or oracle's
> extensions to SQL ) " before doing it another way.

> When you have PLSQL functions invoked on a column oracle has to switch
> between processing rows and then take the value for a column in a row
> and context switch into a PLSQL runtime environment to execute the
> function ... then switch back into the processing of the row.

> This takes memory and cpu overhead to accomplish that.

> In contrast ( somehow ) when you use pure SQL or functions which are
> oracle's extension to SQL then ( somehow ) oracle keeps all of this
> execution basically in line while processing the rows.

Further along the same lines, Tom Kyte has also mention in a couple articles, and I believe also in his books, that there is a cost of a context switch when a SQL statement calls a PL/SQL procedure. The cost of the context switch can be quite high. One article where he briefly talks about this:
http://www.oracle.com/technology/oramag/oracle/03-may/o33asktom.html (see the third question).

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Oct 25 2006 - 08:48:03 CDT