Skip to Main Content
  • Questions
  • Parsing required for DSS stored procedures.


Question and Answer

Tom Kyte

Thanks for the question, Albert .

Asked: February 19, 2007 - 11:48 am UTC

Last updated: February 19, 2007 - 2:22 pm UTC

Version: 11?

Viewed 1000+ times

You Asked

Hi Tom,

You have many times mentioned that for OLTP applications we should try to avoid parsing by using bind variables. For DSS type of applications, bind variables are not to be used to get the optimum execution plan.

But currently there is no provision for not using bind variables without resorting to dynamic sql. But we loose lots of advantages of static sqls (automatic dependency, no sql injection, etc) if we go for dynamic sqls. Why has not Oracle come up with a feature to solve this? Some thing like
create procedure proc <parse_every_time> is begin ...
Is it planned in the near future?


Albert Nelson A.

and Tom said...

Well, you are only really talking about PLSQL here since OCI, JDBC, VB - etc - all use 'dynamic sql' exclusively.

And you only "do not bind" for your big bad warehouse queries - something that is typically generated by a front end and PLSQL is so far from being involved.

And for the rare rare case you need to do it in PLSQL, ref cursors make this pretty easy.

There will likely never be a "parse every time"


  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Yes using PL/SQL

Albert Nelson, February 19, 2007 - 12:50 pm UTC

Hi Tom,

Yes, I am talking about PL/SQL.

Even for dataware house queries I find it more appealing to use stored procedures that returns ref cursors.

But the problem is when the number of inputs to the procedure increases, the various possible combinations of the inputs result in large amount of 'if else's. For example for two inputs I've to code

if p_in_1 is not null then
  open p_cur for select ... where p_in_1 =
elsif p_in_2 is not null then
  open p_cur for select ... where p_in_2 =
elsif p_in_1 is not null and p_in_2 is not null then
  open p_cur for select ... where p_in_1 = and p_in_2 = 
  open p_cur for select ...
end if;

As a compromise, I tend to code as

    v_1_selected number := 0;
    v_2_selected number := 0;
    if p_in_1 is not null then
      v_1_selected := 1;
    end if;
    if p_in_2 is not null then
      v_2_selected := 1;
    end if;

    open p_cur for 
      select ..
      where (v_1_selected = 0 or (v_1_selected = 1 and 
              p_in_1 = ..) and 
             v_2_selected = 0 or (v_2_selected = 1 and 
              p_in_2 = ..))

But I know that this will not genarate the optimum plan. I wanted to overcome this. Do you have any suggestion?


Albert Nelson A.
Tom Kyte
February 19, 2007 - 2:22 pm UTC

but you would not do this stuff in plsql, it isn't the way it is done.

but if you do need to do that, you use dynamic sql

open ref_cursor for plsql_variable;

Alberto Dell'Era, February 19, 2007 - 2:46 pm UTC

(Tom, long review ... but something I think you'll find interesting).

I've seen myself tons of pl/sql code in DWH/DSS systems, especially for the data loading/transformation (nightly) process, which is normally much more critical, performance-wise, then the (daily) report process, and so asks for the most accurate plans possible (and for pl/sql when sql is not enough). And I do agree that coding everything using dynamic sql to avoid using bind variables is cumbersome, error-prone and costly.

With bind variable peeking, using literals or bind variables should get the same plan - unless of course you reparse the same statement with different values for the binds.

So the problem can be reformulated (from pl/sql to sql) as "tell the CBO that this SQL statement has to be reparsed if the values of bind variables change"; since the values of the peeked binds are recorded (check v$sql_plan.other_xml for v$ - thanks to Jonathan Lewis for letting me know about this), it shouldn't be neither difficult nor expensive to check it at exec time.

So, why not a new hint such as /*+ reparse_on_bind_change */; it would be a "good" hint, that gives more information to the CBO, similar to "first_rows" or "cardinality".


Albert Nelson, February 20, 2007 - 1:54 am UTC

Alberto Dell'Era has expressed my feelings more clearly. /*+ reparse_on_bind_change */ is what I think is needed.

Thanks for also sharing about info on v$sql_plan.other_xml


Albert Nelson A.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library