Re: Stored proc slower than direct sql query. Help needed

On 11 Apr 2002, deja_at_chothu.mailshell.com wrote:
> Intentionally vague..that's a curious phrase! Anyways, here's the
> code:
>
> DECLARE
> Counter INTEGER;
> CurrentUserID VARCHAR2(15);
> SavingsByCombo VARCHAR2(2);

[...]

> ClientId VARCHAR2(20);
>
> BEGIN
> CURRENTUSERID := 'joseph';
> SAVINGSBYCOMBO := NULL;
[...]

> EX_PROCCODES := NULL;
> CLIENTID := '1';
I quickly checked your variables between BEGIN/DECLARE. You use very few of them. I actually on saw one, CLIENTID. Why ask the proc to work this hard when it doesn't have to?

> INSERT INTO cas.RPTACTIVITY
> (vclistuser, sortid1, vcnumber, vcprovidername,
> dtdischargedate,
> rcoveredcharges, sidrg, sinewdrg, ractualpay,
> vcreviewername,
> savings)
> SELECT CURRENTUSERID, --!!Here
> DECODE (
[...]

> AND (u.vcuserid = r.vcauditorid AND u.vcclientid = CLIENTID)

Okay, here is where I think the culprit might be. In your proc, the CLIENTID is a bound variable. I'm guessing that the plan the proc is using is different than the plan the sqlplus session is using. You are probably running the sqlplus query with "u.vcclientid = '1'" The CBO will not be able to utilize a histogram in the proc but it may in your sqlplus session. (This seems to be an id, so it shouldn't matter, but ...)

So, try something. In your sqlplus session do the following:

variable clientid varchar2(20);
exec :clientid := '1';

Now, run your query, but this time use a bind variable.

    insert into blah

        [...]
    AND (u.vcuserid = r.vcauditorid AND u.vcclientid = :clientid)

How fast does that query run?

> AND (ac.iauditnumber = a.iauditnumber AND a.vcauditstatus = 'C')
> AND ( o.uclaimkey = ac.uclaimkey(+)
> AND o.iauditnumber = ac.iauditnumber(+)
> )

[...]

> BETWEEN TO_DATE('01/01/1900','MM/DD/YYYY') AND
> TO_DATE('01/01/2000','MM/DD/YYYY') )
> )
> AND ( NULL IS NULL
> OR NULL IS NOT NULL
> AND DECODE (
> 'GrossIdent',

Now, what is up with this NULL IS NULL OR NULL IS NOT NULL?

[...]

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.