Charles Wetherell, Consulting Member of the PL/SQL development team, was kind enough to offer these insights regarding PL/SQL optimization and native code generation.
The documentation describes the PL/SQL compilation controls.
I wrote a blog post about inlining.
When the optimizing PL/SQL code generator was first introduced, I wrote a paper about the definition of PL/SQL and the optimizations that are allowed in PL/SQL programs. You may find that your understanding of PL/SQL is deepened and your programming becomes more sophisticated after you read this.
A PL/SQL programmer asked why PL/SQL native code generation was turned off when the PL/SQL optimization level was set to 1.
There are four PL/SQL optimization levels:
0. Esoteric for some long-since-passed compatibility issues with release 9 and before
1. Basic code generation with debugging data created
2. Global optimization
3. Automatic inlining of local procedures
Each level builds on the level before. Debugging data is not created above level 1.
Generally, native code generation is independent of optimization level. Native code generation is turned off at levels 1 (and 0) because it interferes with debugging. In other words, PL/SQL code compiled at optimization levels 0 and 1 is always interpreted when executed.
You should never use level 0. That is a blanket prescription. Certainly no new code should ever require it.
0. Esoteric for some long-since-passed compatibility issues with release 9 and before
1. Basic code generation with debugging data created
2. Global optimization
3. Automatic inlining of local procedures
Each level builds on the level before. Debugging data is not created above level 1.
Generally, native code generation is independent of optimization level. Native code generation is turned off at levels 1 (and 0) because it interferes with debugging. In other words, PL/SQL code compiled at optimization levels 0 and 1 is always interpreted when executed.
You should never use level 0. That is a blanket prescription. Certainly no new code should ever require it.
Level 1 basic code generation applies many simple optimizations. Level 2 code generation adds global analysis that considers the possible flow of control through each subprogram when optimizing. This extra analysis markedly improves the quality of the code generated. The default optimization level is 2 and you should not generally use level 1 unless you specifically want to debug or do something else that requires level 1. Optimization level 2 is likely to speed up PL/SQL code by a factor of 2 to 3. But see the comment on SQL below.
Probably, code will run noticeably faster if you always use level 3; the inlining is controlled in a way that almost always generates a performance improvement. I know of no realistic cases where code ran significantly slower or had other problems because of the use of level 3.
Native compilation will almost always improve code performance significantly as well. This means that the most interesting combinations are:
Native and opt = 2
Native and opt = 3
Probably, code will run noticeably faster if you always use level 3; the inlining is controlled in a way that almost always generates a performance improvement. I know of no realistic cases where code ran significantly slower or had other problems because of the use of level 3.
Native compilation will almost always improve code performance significantly as well. This means that the most interesting combinations are:
Native and opt = 2
Native and opt = 3
So far as I know, there are no significant cases where higher optimization levels cause the PL/SQL compiler to slow down noticeably or to consume too many resources during compilation. In other words, compilation expense should not be a factor in deciding what optimization level to use.
Most PL/SQL applications do NOT spend most of the their execution time in PL/SQL. Much of their time is spent doing the SQL triggered by embedded SQL statements in the PL/SQL program. A likely time split is something like 75% of execution time in SQL and 25% in PL/SQL. The PL/SQL compiler settings have essentially no effect on the performance of SQL. Of course, your particular application might have a substantially different time split, but spending more than 50% of application time in PL/SQL would be regarded as unusual.
I wrote a blog post about inlining.
When the optimizing PL/SQL code generator was first introduced, I wrote a paper about the definition of PL/SQL and the optimizations that are allowed in PL/SQL programs. You may find that your understanding of PL/SQL is deepened and your programming becomes more sophisticated after you read this.
Comments
Post a Comment