Oracle 8.1.7.3 on HPUX 11.0

In our third party developed app, I have noticed the response time
with packaged variables, or anything _not_ hardcoded, seems to much
slower than hardcoding. See below:

QL*Plus: Release 8.1.7.0.0 - Production on Tue Jul 8 13:49:01 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.3.0 - 64bit Production

SQL> spool r.out
SQL> DECLARE
2 p_rowtype host_orderdetail%rowtype;
3 BEGIN
4 SELECT hd.*
5 INTO p_rowtype
6 FROM host_orderdetail hd
7 WHERE hd.host_status = 'RELEASE'
8 AND hd.orderid NOT IN
9 (
10 SELECT ho.orderid
11 FROM host_orders ho
12 WHERE ho.host_status IN ('RELEASE',
13 'ERROR')
14 )
15 ORDER BY adddate ASC;
16 END;
17 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4


Elapsed: 00:00:00.02
SQL>
SQL> DECLARE
2 p_rowtype host_orderdetail%rowtype;
3 pc_HOST_RELEASE CONSTANT host_sku.host_status%TYPE :=
'RELEASE';
4 pc_HOST_ERROR CONSTANT host_sku.host_status%TYPE :=
'ERROR';
5 BEGIN
6 SELECT hd.*
7 INTO p_rowtype
8 FROM host_orderdetail hd
9 WHERE hd.host_status = pc_HOST_RELEASE
10 AND hd.orderid NOT IN
11 (
12 SELECT ho.orderid
13 FROM host_orders ho
14 WHERE ho.host_status IN (pc_HOST_RELEASE,
15 pc_HOST_ERROR)
16 )
17 ORDER BY adddate ASC;
18 END;
19 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6


Elapsed: 00:03:42.17
SQL>

Does anyone have any insight as to why is the variable version so much
slower than the hardcoded value?

Thanks,

Steve

stephen.howard (AT) us (DOT) pwcglobal.com (Steve Howard) wrote in message news:<6d8b7216.0307081017.d9bb749 (AT) posting (DOT) google.com>...
I cannot see why you use 'host_sku.host_status%TYPE' instead of
declaring this constants in the package and using it directly in your
anonymous block:

CREATE OR REPLACE PACKAGE host_sku
AS
pc_HOST_RELEASE CONSTANT CHAR(7) := 'RELEASE';
pc_HOST_ERROR CONSTANT CHAR(5) := 'ERROR';
....
END host_sku;

An then:

DECLARE
p_rowtype host_orderdetail%rowtype;
BEGIN
SELECT hd.*
INTO p_rowtype
FROM host_orderdetail hd
WHERE hd.host_status = pc_HOST_RELEASE
AND hd.orderid NOT IN ( SELECT ho.orderid
FROM host_orders ho
WHERE ho.host_status IN (
host_sku.pc_HOST_RELEASE,

host_sku.pc_HOST_ERROR )
)
ORDER BY hd.adddate ASC;
END;

The first time you invoke the package this is loaded into memory and
subsequent invocations will take the variable values right from the
memory. (Notice that the package will be loaded as a whole, not only
the parts that you are invoking.)

The advantages of using ('bind') variables come up when executing the
same code multiple times with different values, avoiding parsing and
execution plans (Oracle uses the same execution plan, only changing
the values).

I tried this in one of my tables:

CREATE OR REPLACE PACKAGE prueba_host
AS
SORT_1 CONSTANT CHAR(6) := 'Sort 1';
SORT_2 CONSTANT CHAR(6) := 'Sort 2';
END prueba_host;

DECLARE
p_rowtype sort_ja%rowtype;
BEGIN
SELECT *
INTO p_rowtype
FROM sort_ja
WHERE ds_sort = prueba_host.SORT_1
AND ds_sort NOT IN ( SELECT ds_sort
FROM sort_ja
WHERE ds_sort IN ( prueba_host.SORT_1,
prueba_host.SORT_2 )
)
ORDER BY D_CEL ASC;
END;
/

ERROR en línea 1:
ORA-01403: no data found
ORA-06512: at line 4


Transcurrido: 00:00:00.10


DECLARE
p_rowtype sort_ja%rowtype;
BEGIN
SELECT *
INTO p_rowtype
FROM sort_ja
WHERE ds_sort = 'Sort 1'
AND ds_sort NOT IN ( SELECT ds_sort
FROM sort_ja
WHERE ds_sorteo IN ( 'Sort 1', 'Sort 2'
)
)
ORDER BY D_CEL ASC;
END;
/

ERROR en línea 1:
ORA-01403: no data found
ORA-06512: at line 4


Transcurrido: 00:00:00.71


Anyway, look at the execution plans and take your own conclusions. Try
to find out how the optimizer is working. IMHO the difference is too
big to be only a HARDCODE/NOT HARCODE problem.

Regards.

miotromailcarlos (AT) netscape (DOT) net (Carlos) wrote in message news:<1de5ebe7.0307082326.31dc89b8 (AT) posting (DOT) google.com>...
Carlos, Steve's observatcion that code using bind variables seems to
run slower than the same SQL statement written using constants is a
commom occurrence. The cost based optimizer, CBO, knows more about
the data when a constant is present than what it can determine from
the statistics when a bind variable is present even in the absense of
histograms.

The explain plans for the same SQL statement substituting bind
variables for constants will often be different. Many developers make
the mistake of explaining SQL using constants where bind variables
appear in the code. They are not the same statement.

Steve expalin your SQL using constants and :v1, :v2 etc... in place of
the constants and compare the plans. If different you have your
answer, with proof, so try tuning the statement to run the same way it
runs when constants are used.

Note version 9 has a bind variable value peek feature where on first
execution the plan is adjusted for the value found in the variable.
Which may or may not be a good think depending on the data value skew.

HTH -- Mark D Powell --

Mark.Powell (AT) eds (DOT) com (Mark D Powell) wrote in message news:<2687bb95.0307090530.6834303f (AT) posting (DOT) google.com>...

Dear Mark:

Steve was talking about 'packaged' variables, not 'bind' variables,
this is why I wrote ('bind') enclosed in parentheses, since if we use
the 'packaged' variables the optimizer will act as if they were bind
variables.

As I said, 'the advantages of using ('bind') variables come up when
executing the same code multiple times with different values'. This is
the 'ABC' of a well designed client software. (You can 'ask Tom', but
I'm not sure of the kindness of his answer: he has been holding a
crusade in order to everyone to understand this). The CBO does NOT act
considering the VALUES of the items used in the predicates, but
considering HOW are the statements constructed (among other factors).

I know that, but I also know that when you use bind variables the
optimizer generates one execution plan and then uses it for all the
same queries no matter if the variable values are the same or not,
avoiding some parsing too.
Moreover, is more likely that a hard coded query falls off the memory
(LRU) than a query that has bind variables instead .

Yes, 9i takes the work that programmers should do: making bind
variables statements from expressions made up with constant values.

I hold on with my opinion about Steve's tests: 'the difference is too
big to be only a HARDCODE/NOT HARCODE problem'. I think my own example
is clear enough.

Greetings.