|Author's name: Jonathan Lewis
Author's Email: email@example.com
|Date written: 13-Mar-2001 / 7th July 2001
Oracle version(s): 7.3 - 8.1.7
|I have carefully crafted an SQL statement to run very efficiently under the normal SQL*Plus environment, however when use it as a cursor inside a PL/SQL block, it runs very slowly. What is happening and how can I fix it.|
Back to index of questions
This is to do with the way in which 'user recursive' optimisation is decided; and the main reason for issuing this note now is that I've just been hit by some quirky behaviour in 8.1
Historically, any user SQL executed inside a PL/SQL block ran with a 'recursive' optimizer mode of CHOOSE if the session optimizer_mode was set (either explicitly, or because of the init.ora file) to use cost-based optimisation, and it ran under RULE if the session wais running rule-based.
Effectively, now that most sites do generate the necessary statistics, this tends to mean that SQL inside PL/SQL blocks is usually running ALL_ROWS optimisation. You may ask why Oracle has implemented this 'strange' strategy, but there is a perfectly logical argument for it: typically a PL/SQL procedure cannot return until it has completed, i.e. until it has processed all the data available, which is exactly the option that ALL_ROWS optimisation caters for - returning all the rows at the cheapest cost..
Of course, most of us have at some time, particularly in the early days of cost-based optimisation, set the globally defined optimizer_mode to FIRST_ROWS because this tended to favour indexes over tablescans; and this is why we still notice a sudden change of access path when we wrap our SQL inside PL/SQL - the development path in pure SQL is FIRST_ROWS, the production path wrapped in PL/SQL is ALL_ROWS, and it can make a very big difference.
The solution is simple - any SQL in PL/SQL hand-crafted and static. It is reasonable to hint it for best effect, so make sure that you hint your SQL to first_rows if that's the way you want it to run.
This strategy no longer appears to be true - in 8.1, oracle introduces the hidden parameter _optimizer_mode_force, with the description force setting of optimizer mode for user recursive SQL also. This appears to default to FALSE up to and including 8.1.5 but TRUE for 8.1.6 onwards. Presumably the intent is that when set to TRUE, SQL in PL/SQL should follow the session optimizer_mode, and when set to false, the original behaviour should occur.
However, the strategy does not seem to be completely worked out yet (which is presumably why it is a hidden parameter). From recent experiments, it seems that this parameter is ignored in the 8.1.5 and 8.1.7 versions that I have been using, however: Under 8.1.5, the SQL inside the PL/SQL follows the session optimizer_mode all the time, and under 8.1.7 the original rule about CHOOSE vs. RULE applies.
Be warned - upgrading from 8.1.5 to 8.1.7 could have a funny performance impact on your PL/SQL procedures. When you run your regression tests, don't just check that the data comes out the same, and the access path comes out the same - check that the optimizer_goal quoted in the trace files and tkprof outputs is also the same.
Nathan Schroeder (firstname.lastname@example.org) has pointed out that there isa second interpretation to this question with a completely different answer. Running under SQL*Plus, a simple select statement uses an array fetch with a default value of 15 rows per call to the database. If you use the same piece of SQL in a PL/SQL package to drive a cursor for loop then the costs of single row processing can add a considerable overhead - and, of course, the work you then do to each row in the PL/SQL may add further time; giving you the impression that the SQL is running much more slowly in one environment than in the other.
Yet another reason for the change is the 'bind variable' problem. When you were crafting your query in SQL*Plus, were you using a set of literal constants as the inputs to the query ? Does your PL/SQL version make use of literal constants, or are they replaced by bind variables when the query actually executes. If so then the cost based optimizer may change its path because it can no longer use histogram statistics; it may change its path because of a data type coercion issue.
Further reading: How explain plan can be deceived. on the JLComp website.
Back to top
Back to index of questions