Slow response with packaged varaibles...

From: Steve Howard <stephen.howard_at_us.pwcglobal.com>
Date: 8 Jul 2003 11:17:13 -0700
Message-ID: <6d8b7216.0307081017.d9bb749@posting.google.com>

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

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 Received on Tue Jul 08 2003 - 13:17:13 CDT