Oracle's plain behavior on EXECUTE IMMEDIATE with REPLACE and variable bind

Question

I am trying to avoid oracle's plain to be calculated at every dynamic SQL use.

Using REPLACE and then a EXECUTE IMMEDIATE without USING leads to overhead, as the string is different at every use, Oracle always seems to search a new plain.

But reading about EXECUTE IMMEDIATE statement a found the following:

You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. You still incur some overhead, because EXECUTE IMMEDIATE re-prepares the dynamic string before every execution.

Is that? Does USING bind forces Oracle to recalculate every dynamic string? If so, how to avoid it?

a) querytoselect = REPLACE( querytoselect, 'pattern', 'var' );
   EXECUTE IMMEDIATE querytoselect.

b) EXECUTE IMMEDIATE querytoselect USING var

Thanks.

If you are using bind variables, and the SQL statement to execute is the same, then I would think oracle would not do a hard parse again. Try a test and look at the V$sqlarea view to see what shows up. — OldProgrammer
– OldProgrammer, Commented Dec 5, 2013 at 19:00
The text on oracle's doc site raised the doubt. I'll search about V$sqlarea, I have some years of C++ but 0,5 of Oracle \m/ — Rodrigo Gurgel
– Rodrigo Gurgel, Commented Dec 5, 2013 at 19:04
This thread on Tom Kyte's site is kind of long but it deserves to be read. — Yaroslav Shabalin
– Yaroslav Shabalin, Commented Dec 5, 2013 at 20:15

Dave Costa · Accepted Answer · 2013-12-05 20:02:09Z

3

EXECUTE IMMEDIATE always does a parse, but if the statement is identical to one already in memory, it can simply use the plan already determined (this is called a "soft parse") instead of having to fully parse the statement and determine a new plan (a "hard parse").

There is really more subtlety to it than that, and depends on your configuration as well. For instance, even if a matching statement is found in memory, bind peeking and table statistics could result in a hard parse being performed to determine if there is a potentially better plan given the particular input.

But overall, a soft parse is less overhead than a hard parse, and using bind variables increases the chance of avoiding a hard parse.

To completely avoid re-parsing, you can use DBMS_SQL instead of EXECUTE IMMEDIATE and hold onto the cursor after parsing. I wouldn't bother doing this unless you've identified a significant performance issue caused by soft parsing.

answered Dec 5, 2013 at 20:02

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yaroslav Shabalin Over a year ago

@RodrigoGurgel Don't know the current situation in 11g and 12c versions, but in 9i and 10g DBMS_SQL was slower then EXECUTE IMMEDIATE and it was stated in documentation. If I correctly remember the difference was 1.5 - 3 times according to Oracle.

Rodrigo Gurgel Over a year ago

Ok, I'll search for it.

Collectives™ on Stack Overflow

Oracle's plain behavior on EXECUTE IMMEDIATE with REPLACE and variable bind

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related