Skip to main content

Does the PL/SQL compiler remove code that is used?

Yes. No. Sort of.

 It's (not all that) complicated.

This question hit my Twitter feed yesterday:
When you enable all warnings, have you ever seen a "PLW-06006-- uncalled procedure removed" (lots of them), when they surely are called?
Now that, I must admit, has to be a little bit concerning. You write code, you know it is going to, or should be, executed, and yet the PL/SQL compiler tells you it's been removed?

OK, OK, calm down. Everything is just fine.

Here's the explanation:
  • The optimizer performed an inlining optimization, so all the code for that procedure (or function) was moved to where it is invoked.
  • The "original" nested or private subprogram that you wrote (and, don't worry, is still and always will be in the source code of your program unit) is, truth be told, never going to be called. 
  • So then the compiler removed it (did not include it in the compiled code - which is not PL/SQL code any longer).
Let's take a look at some code, and what happens when we run it (you can see this for yourselves via my LiveSQL script): 
ALTER SESSION SET plsql_optimize_level = 3
/

Statement processed

ALTER SESSION SET plsql_warnings='enable:all'
/

Statement processed

CREATE OR REPLACE PROCEDURE show_inlining
   AUTHID DEFINER
IS
   FUNCTION f1 (p NUMBER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN p * 10;
   END;

   FUNCTION f2 (p BOOLEAN)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN CASE WHEN p THEN 10 ELSE 100 END;
   END;

   FUNCTION f3 (p PLS_INTEGER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN p * 10;
   END;
BEGIN
   DBMS_OUTPUT.put_line ('f1 called: ' || f1 (1));

   PRAGMA INLINE (f2, 'YES');
   DBMS_OUTPUT.put_line ('f2 called: ' || TO_CHAR (f2 (TRUE) + f2 (FALSE)));

   PRAGMA INLINE (f3, 'NO');
   DBMS_OUTPUT.put_line ('f3 called: ' || f3 (55));
END;
/

Warning: PROCEDURE SHOW_INLINING
Warning: PROCEDURE SHOW_INLINING 
Line/Col: 4/4 PLW-06027: procedure "F1" is removed after inlining 
Line/Col: 10/4 PLW-06027: procedure "F2" is removed after inlining 
Line/Col: 22/4 PLW-06005: inlining of call of procedure 'F1' was done 
Line/Col: 25/4 PLW-06005: inlining of call of procedure 'F2' was done 
Line/Col: 25/4 PLW-06004: inlining of call of procedure 'F2' requested 
Line/Col: 25/4 PLW-06005: inlining of call of procedure 'F2' was done 
Line/Col: 25/52 PLW-06004: inlining of call of procedure 'F2' requested 
Line/Col: 28/4 PLW-06008: call of procedure 'F3' will not be inlined

BEGIN
   show_inlining;
END;
/

f1 called: 10
f2 called: 110
f3 called: 550
Please note: all the functions (f1 - f3) were executed!

As you can see, the warnings feedback ("PLW" stands for PL/SQL Warning) tells the story: procedures are removed after inlining.

Though I suppose we could be a little more explicit - and reassuring - and say:
PLW-06027: procedure "F1" is removed after inlining....
but just from the compiled code, not the source code of your program unit!

Labels:

Comments

Post a Comment

Popular posts from this blog

Why DBMS_OUTPUT.PUT_LINE should not be in your application code

A database developer recently came across my  Bulletproof PL/SQL  presentation, which includes this slide. That first item in the list caught his attention: Never put calls to DBMS_OUTPUT.PUT_LINE in your application code. So he sent me an email asking why I would say that. Well, I suppose that is the problem with publishing slide decks. All the explanatory verbiage is missing. I suppose maybe I should do a video. :-) But in the meantime, allow me to explain. First, what does DBMS_OUTPUT.PUT_LINE do? It writes text out to a buffer, and when your current PL/SQL block terminates, the buffer is displayed on your screen. [Note: there can be more to it than that. For example, you could in your own code call DBMS_OUTPUT.GET_LINE(S) to get the contents of the buffer and do something with it, but I will keep things simple right now.] Second, if I am telling you not to use this built-in, how could text from your program be displayed on your screen? Not without a lot o...
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch, ...
4 comments
Read more

Table Functions, Part 1: Introduction and Exploration

Please do feel encouraged to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done! Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs. So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into. Of course, I am not the first to do so. I encourage to check out the  documentation , as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall . Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latter part...
31 comments
Read more