Re: Slow response with packaged varaibles...

miotromailcarlos_at_netscape.net (Carlos) wrote in message news:<1de5ebe7.0307082326.31dc89b8_at_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.

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 -- Received on Wed Jul 09 2003 - 08:30:17 CDT