Re: Why woulld a query run with different performance inside/outside of a stored procedure?

Jeremy wrote:
> Puzzled. Usually if I find a slow running query in a stored procedure, I
> lift it out and run it in sqlplus or TOAD etc and tune it from there.
>
> Today I am looking at a query which, when declared in a procedure like
> this:
>
> for h in (select ......)
> loop
> ..
> end loop;
>
> takes 3-4 seconds to parse and execute - i.e. if I stick a trace line
> before the "for" and after the "loop" line, the first row becomes
> available after 3-4 seconds.
>
> Lifting the SQL out and running in sqlplus, it takes < 0.5 seconds and
> has a pretty good explain plan. The query has one condition based on a
> parameter in the plsql procedure which, when run in sqlplus, is replaced
> by a constant - other than that the SQL is identical.
>
> Not sure where to start in trying to find the cause of this discrepancy,
> would appreciate any pointers.
>
> cheers
>
> --
> jeremy
>
> ============================================================
> ENVIRONMENT:
> Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8
> ============================================================

Interesting article:
http://www.oracle.com/technology/tech/pl_sql/pdf/PLSQL_9i_New_Features_Doc.pdf

"3.2. BULK BINDING IN NATIVE DYNAMIC SQL 3.2.1. DEFINING Consider a program to populate elements of a PL/SQL collection from a SELECT query thus...

declare
  type employee_ids_t is table of employees.employee_id%type index by binary_integer;
  employee_ids employee_ids_t;
  n integer:=0;
begin
  for j in ( select employee_id from employees where salary < 3000 )   loop
    n := n+1;
    employee_ids(n) := j.employee_id;
  end loop;
end;

Each explicit row by row assignment of the collection element to the cursor component causes a context switch between the PL/SQL engine and the SQL engine resulting in performance overhead. The following formulation (one of a family of constructs generically referred to as bulk binding and available pre-Oracle9i)...

begin
  select employee_id
    bulk collect into employee_ids
    from employees where salary < 3000;
end;

 ...substantially improves performance by minimizing the number of context switches required to execute the block. (The above fragments work pre-Oracle 9i.)"

The above is possibly one explanation - cost of context switching.

Predicted cardinality (expected number of rows that will be returned) is another possibility - a 10053 trace would tell if this is the case. When bind variables are used, the predicted cardinality will be 5% of the rows. The predicted cardinality rules change if constants (literals) are used, and change again if histograms are present. Changes in the predicted cardinality can change the execution plan, possibly to a less efficient one. This is a second possible explanation.

When bind variables are used, Oracle 9i is permitted to peek at the bind variables on the initial hard parse (it seems that there may be an opportunity for Oracle to peek at the bind variables on every execution if CURSOR_SHARING is set to SIMILAR, but there are warnings about using this setting in the "Cost-Based Oracle Fundamentals" book). So, if atypical values were used for the bind variable values during the initial hard parse, future executions of the SQL statement with typical values could suffer from an inefficient execution plan. If the two SQL statements (one executed in PL/SQL and the other in SQLPlus) differ by even a single space, they are not considered the same SQL statement, and a hard parse will be required. This is a third possible explanation.

If CURSOR_SHARING is set to FORCE, constants (literals) will be automatically replaced with bind variables. If the two SQL statements (one executed in PL/SQL and the other in SQLPlus) differ by even a single space, they are not considered the same SQL statement, and a hard parse will be required. Thus, the two SQL statements may have different execution plans. This is a fourth possible explanation.

There must be more possible explanations.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Jan 11 2007 - 08:07:32 CST