Re: Slow response with packaged varaibles...

From: Carlos <miotromailcarlos_at_netscape.net>
Date: 9 Jul 2003 00:26:27 -0700
Message-ID: <1de5ebe7.0307082326.31dc89b8@posting.google.com>

stephen.howard_at_us.pwcglobal.com (Steve Howard) wrote in message news:<6d8b7216.0307081017.d9bb749_at_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:
>
> ...
>
> Does anyone have any insight as to why is the variable version so much
> slower than the hardcoded value?
>
> Thanks,
>
> Steve

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. Received on Wed Jul 09 2003 - 02:26:27 CDT