Skip to main content

Wait, did the PL/SQL compiler just REMOVE my code?

The PL/SQL compiler does more than compile - it also:
  1. automatically optimizes your code to run faster
  2. offers advice in the form of compile-time warnings to improve the quality and/or performance of your code
  3. allows you to conditionally include or exclude portions of your code for compilation
That's just fantastic - but it can now and then result in some confusing moments for the Oracle Database developer (well, at least this developer).

Recently, I was looking over the warnings I had gotten for a new package I'd written and saw this:


Wait - my "procedure" user_goals_cur was removed?

I could tell by the name that it was not a procedure - it was a cursor. So clearly the warning message hasn't been customized to the type of code removed. OK, that's no big deal - I can deal with that. 

But when I see the PLW-06006 warning, it has meant that I'd written a nested subprogram in a procedure or function but it was no longer used. It was superfluous code that should have been removed - so the compiler removed it for me (from the compiled code, not my source code).

But a cursor? Did I declare a cursor and then not use it? Entirely possible. 

So I opened up the package body and saw this:


As I expect you can see, the cursor is used. OK, now I was worried. It was removing code that I was using? How could that make any sense?

Well, you know what they say: Trust The Compiler. And trust people you know are way smarter than you - in this case, the PL/SQL development team.

So rather than panicking about the removal of essential code, I went back and took a closer look at the warnings report. And this time I noticed the second warning that referenced the same "USER_GOALS_CUR":


The cursor was inlined! Now this was interesting news for two reasons:
  1. I hadn't even realized that the compiler performed the inlining operation on cursors.
  2. It made me think that perhaps the inlining was somehow related to the removal of the cursor.
But first for those who are unfamiliar with the inlining feature, I will take a moment to explain.

Suppose I have a procedure defined as follows:

PROCEDURE p
IS
   FUNCTION f RETURN NUMBER
   IS BEGIN ... END;
BEGIN
   IF f() THEN ...
END;

When f is executed at runtime, PL/SQL spends a small amount of time finding the code for f. If you'd like to avoid this overhead, you can ask the compiler to inline the function. This means that when you compile your program, the invocation of a subprogram is replaced by the code itself. Your compiled code size increases, but the runtime performance is faster.

We recommend that you set your PL/SQL optimization level to 3 (default is 2) to take full advantage of this feature. You an also selectively request to inline or disable inlining with the PRAGMA INLINE statement. Check the doc for lots more information on inlining.

So when you see the PLW-06006 warning, you know that inlining optimization is enabled and has been put into effect.

And that's when the light bulb goes off in my head and I realize:
  1. A cursor really is no different than a function. You "pass in" bind variables and "return" one or more values via the SELECT list. So the compiler doesn't just inline procedures and functions. It inlines cursors, too! How cool is that?
  2. Once the cursor (or procedure or function) has been inlined, there is no reason to keep it defined as a cursor or subprogram in the declaration section. So the compiler removes it.
That all makes perfect sense, but, gee, that's also kind of confusing. False alarm, and all that. 

The bottom line is that the PL/SQL compiler will "remove" code (leave it "behind" in the source code and not move it to the compiled code) only when it is truly not used or not needed to execute your code.

So remember:

Trust your compiler.

But verify your understanding of what it is doing.









Labels:

Comments

  1. RahulJuly 7, 2016 at 9:29 PM

    Trust the compiler, but, still not sure what is the advantage of inline function here. Did we avoid the context switch using inline function ( I am sure no), What different it is when we open a cursor and return the value returned by SQL engine to the PLSQL record, compared to using a function which in turn calls the SQL which does the same thing.

    ReplyDelete
  2. Steven FeuersteinJuly 8, 2016 at 4:20 AM

    Context switches do not come into play here, since there is no switch between the SQL and PL/SQL engines. Instead, inlining avoids the overhead of looking up the code block for the procedure or function or (as it turns out) cursor. Instead, the code is simple "there", inline in the current block.

    ReplyDelete
  3. Scott SwankJuly 8, 2016 at 4:59 PM

    If you in-line a procedure or function you never have to allocate a stack frame for it. I.e. a local scope for the procedure's state: variables, etc. And then you don't have to remove that stack frame after exiting the procedure/function.

    This can be a significant win if you're calling a procedure in a loop (allocate frame, de-allocate frame, repeat), and the more state associated with the procedure the bigger the gain.

    ReplyDelete
  4. Steven FeuersteinJuly 9, 2016 at 5:22 AM

    Thanks, Scott. That is a very concise and clear explanation. Maybe you should write a book! :-)

    But I do see you've been busily adding useful code to Github! Check it out folks: https://github.com/scott-swank/plsql-util

    ReplyDelete
    Replies
    1. Scott SwankJuly 14, 2016 at 10:30 AM

      Thank ya Steven.

      And actually I have a few updates to push up to github now that you mention it. In point of fact, once I get a couple things tidied up I'd like to talk to someone about providing a bit of my code (not yet on github) to Oracle. Who should I talk to on that front?

      Delete
    2. Steven FeuersteinJuly 14, 2016 at 10:48 AM

      Scott, I am pretty use that Oracle will not accept code from outside contributors but why don't you follow up via email steven at feuerstein dot com, and we can explore further.

      Delete
    3. Steven FeuersteinJuly 15, 2016 at 9:38 AM

      ARGH. I can't even type my sort of email address right. I meant to type: steven dot feuerstein at oracle dot com. But no worries. I got your email via my stevenfeuerstein domain. Thanks!

      Delete
  5. AnonymousJuly 10, 2016 at 1:32 AM

    Hi Steven,

    As you have mentioned, The function call get replaced by the the code itself and that hasnt any associated context switches. So, if i have the below function with a huge code which gets replaced at the occurence of Function call, how is the procedural part of my code executed. A small description (any web link with detailed description) would be appreciated here.

    Function F return number is
    begin

    IF condition_1 THEN
    SELECT INTO value1
    ELSIF condition_2 THEN
    statements_2
    [ ELSIF condition_3 THEN
    statements_3
    ]...
    [ ELSE
    else_statements
    ]
    END IF;

    For rec in 1..10
    loop
    sum:=sum+rec;
    end loop;

    Return sum+value1;
    End if;


    Also, with reference to context switches, I always had a doubt of how does the inbuilt functions like nvl,decode, sum, avg etc. work ( i am aware of the standard package which sets the environment), but do we really call a PLSQL code in the background and are there any associated context switches.

    ReplyDelete
  6. Steven FeuersteinJuly 10, 2016 at 8:04 AM

    re: how is the procedural part of my code executed

    I suggest that the best answer to this question is: don't worry about it. You don't need to know about or concern yourself with the internal operations of the compiler (which can change with each new release anyway). It does the right thing and ensures that the logical result of your code remains unchanged.

    re: context switches and inbuilt functions like nvl,decode, sum, avg

    These functions are implemented directly in the SQL engine. There is no context switch for the built-in SQL functions.

    ReplyDelete
  7. RahulJuly 11, 2016 at 4:08 AM

    Thanks Steven!

    ReplyDelete

Post a Comment

Popular posts from this blog

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of work, revamping algorithms, ensuring correctness, you know the score. Then my eyes snagge
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

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p
24 comments
Read more