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?

Tomas

Tomeo wrote:
Why do you use "Optimizer=RULE" ???

Anyway ... if you use a function, the optimizer will have not the
tinyest clue as to what this function does, so your function will just
be executed once for each result row.
If you C&P your function-select into the big select, then the sql
engine can take a look at the whole picture and (it seems in this case)
come up with a much more performant plan.

br,
Martin

Tomeo wrote:
User defined functions are one of the main killers of performance, they
can probably make a more elegant and flexible solution sometimes but
performance almost always are worse because there must be context
switch from sql between pl/sql engines. If you are doing this switch
for thousands of rows it adds up and takes a very observable time.
So use user defined functions very cautiously and when you really need
them.
More info
http://asktom.oracle.com/pls/ask/f?p...60122715103602

Gints Plivna
http://www.gplivna.eu

Gints Plivna wrote:
One of the most bothersome things in Oracle (or probably in every other
database).
Implementing "good" application design by reusing code, structuring
stuff with packages and procedures, etc. will hit you on the head with
the performance cudgel. >:-(

When will they give me templates for my packaged SQL?!?

cheers,
Martin

Martin T. wrote:

Please explain ... off-line if you prefer.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote:
Hu? Off-line?

It's just a fancy idea of mine ...
So we have VIEWS ... which are stored queries to make things more
organized.
Why not have more flexible "views" where part of the "view" remains
variable until used ... so that the user of the view could push
predicates or whatever into the view ... the SQL engine wouldn't even
be aware of it, just the PL/SQL compiler.
Or at least give me packaged views ... cursors are alright but they're
so awful to use in PL/SQL code compared to inline queries ... (And I so
hate having to define everything on the schema level.)

Well. Just my 002. I'm sure you disagree ;-)

cheers,
Martin

Tomeo wrote:
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

Martin T. wrote:
I don't disagree. But how is this different from putting a pipelined
table function with NDS into a package?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote:

However, NDS is evaluated at runtime, so we do not have the advantage
of compile-time check.
Maybe it would also be a bit less performant and it frankly sounds more
complicated :-)

Anyway .. thanks for pointing this out, I have actually never used
pipelined functions yet so I'll think about it next time ...

cheers,
Martin