Printable View
Soft-parsing, yes. But IMHO, this is a majorly over-rated issue. There is almost no reason to open a statement, then execute it multiple times with different values. Far better to run it a single time with a list of values, or collect the values into an array and do a bulk-statement, or something similar. Further, with proper cursor sharing, you'll cut down on the soft-parsing anyway.
- Chris
If we suppose that we are on a pre-9i database where we have to decide whether to rewrite a query to make it acceptable to the PL/SQL SQL engine, or use execute immediate in order to switch to the "native" SQL engine, then I guess the most important consideration would be the performance of the two SQL's.
If the enhanced syntax (analytic functions, subquery factoring etc) gave any kind of performance boost then the sacrifice on increased soft-parsing would be smal potatoes in comparison.
Are you on 8i or 9i, Chris? Would you anticipate reducing your usage of execute immediate following a migration to 9i?
I'm on 8i and have seen nothing yet that would make me re-think my usage of dynamic SQL when I move to 9i next month.
As I said before, the soft parsing issue is always small potatoes, IMHO. I regularly deal with massive SQL statements that I make orders of magnitude faster. I regularly deal with tiny SQL statements that I still make 10-50% faster with ease. I rarely, if ever, deal with SQL where the difference in soft parsing or not makes any difference whatsoever. And as I said, the scenarios where soft-parses can actually be avoided at the code level are very small and shrinking. Add to that the fact that cursor settings at the database level (a nice and high session_cached_cursors) can reduce soft parses across the board and we are left with the practical elimination of soft parsing as an issue.
It is more important, IMHO, to be able to label SQL so it is easily categorized in the SGA, and to use constants in the code, so that no value is ever hard-coded in the PL/SQL, but can still be hard-coded in the SQL for use with histograms. These are two of the things that dynamic SQL does very nicely. I also like sharing functionality between statements without having to make the SQL call a PL/SQL function, thus incurring context-switching penalties. Again, dynamic SQL handles this nicely. I like being able to dump out the SQL I execute through my debug packages, because there are certain problems with relying on the SGA alone. Again, dynamic SQL fits the bill. I like having sensible bind variable names, so that statements in the SGA are more usable. I have a performance utility that also depends upon this. Again, this is something that dynamic SQL can do that straight SQL cannot.
The fact that Oracle was stupid enough to have 2 different SQL engines with differing functionalities actually didn't directly affect my decision to use dynamic SQL as a standard.
- Chris