Thursday, July 30, 2015

Smoothing rough edges for productive programming

No language is perfect and while PL/SQL is an incredibly "tight fit" for building applications on top of Oracle SQL (and, of course, Oracle Database), it also is not yet quite perfect.

Maybe in Oracle Database 13(?) - (?) being whichever letter Larry decides will best reflect the main theme of that version....

In any case, in the meantime, and to paraphrase a saying:
You write code with the language you've got, not the language you want.
So the key thing is to maximize your productivity any way you can, all along the way.

And sometimes the steps you can and should take in this area can be very small, but they can still add up.

Case in point: displaying a Boolean value with DBMS_OUTPUT.PUT_LINE.

As anyone who's spent time with PL/SQL knows, DBMS_OUTPUT.PUT_LINE is the procedure provided by Oracle to display text from within a PL/SQL block on your screen.

It accepts a string (and anything that can be implicitly converted to a string) and sends it to the DBMS_OUTPUT buffer. Then when your PL/SQL block completes, the host environment in which you executed that block reads the buffer and shows you the text.

Simple enough.

And that takes us back to:
DBMS_OUTPUT.PUT_LINE accepts a string (and anything that can be implicitly converted to a string)....
As of 12.1, PL/SQL does not support implicit conversion of Booleans (which have just three values: TRUE, FALSE and NULL - well, I guess that's two values and one non-value) into anything at all.

So when I try to display a Boolean using DBMS_OUTPUT.PUT_LINE, all hell breaks loose:














Well, that's a bummer. What's a programmer to do?

What too many of us do is sigh and remember that this happened last week, too, and then spend another minute or so converting the code into this:

DECLARE
   l_boolean BOOLEAN := TRUE;
BEGIN
   IF l_boolean
   THEN
      DBMS_OUTPUT.put_line ( 'true' );
   ELSE
      DBMS_OUTPUT.put_line ( 'false' );
   END IF;
END;
/

Then we see our value. And then we have wasted our time. Again. And again.

Argh. No. Do not do this. Do not waste your time in this way. Life is too short.

Instead, accept that you will have this experience over and over again unless you take action. In this case, a very small action:
Save that pattern of code somewhere so you can access it quickly and easily the next time you need it.
Now, there are at least two ways to do this.

Save Pattern As Reusable Snippet or Template

If you are using an editor that's a bit smarter than Notepad, it likely allows you to save code snippets, and then grab them as needed.

In SQL Developer, you have choices in this arena: Code Snippet and Code Template.

Code Snippets offer the ability to create sets of code you access via drag-n-drop. Jeff Smith, SQL Developer Product Manager offers a fine explanation of how to use those on his blog.

For this kind of situation, I prefer to use Code Templates, for one simple reason: you can pull the template into your current editor with the auto-complete keystroke (Control-Space by default). This is a much smoother and faster way to suck in small chunks of commonly used code, and keep on coding.

Briefly here are the steps to go through to set up a new code template:

1. Open up SQL Developer Preferences and click on SQL Editor Code Templates under Database:


















2. Then click on Add Template, type in a very short name for your template (just a suggestion :-) ) , and then paste in the code:















Save it, close Preferences, and from that point on, when you are writing code, type (in my case) "b2v", press Control-Space and voila! Your code will appear. Nice.

Save Pattern As Reusable Code

You could also save the pattern as reusable code: encapsulate the entire IF statement inside a procedure (in essence, extending the PL/SQL language), and then call that procedure as needed.

CREATE OR REPLACE PROCEDURE display_boolean (
      boolean_in IN BOOLEAN)
   AUTHID DEFINER
IS
BEGIN
   CASE boolean_in
      WHEN TRUE THEN DBMS_OUTPUT.put_line ( 'TRUE' );
      WHEN FALSE THEN DBMS_OUTPUT.put_line ( 'FALSE' );
      ELSE DBMS_OUTPUT.put_line ( 'NULL' );
   END CASE;
END;
/

Or you could get rather "fancy" and build yourself an entire package to help manage Booleans in a consistent manner in your code:

CREATE OR REPLACE PACKAGE boolean_pkg AUTHID DEFINER
IS  
   FUNCTION bool_to_str (boolean_in IN BOOLEAN)
      RETURN VARCHAR2;

   /* Consistency with existing TO_CHAR functions, though 
      of course you still need to preface it with 
      "boolean_pkg". */

   FUNCTION to_char (boolean_in IN BOOLEAN)
      RETURN VARCHAR2;

   FUNCTION str_to_bool (string_in IN VARCHAR2)
      RETURN BOOLEAN;

   FUNCTION true_value
      RETURN VARCHAR2;

   FUNCTION false_value
      RETURN VARCHAR2;
      
   PROCEDURE put_line (boolean_in IN BOOLEAN);
END boolean_pkg;
/

CREATE OR REPLACE PACKAGE BODY boolean_pkg
IS
   /* Change strings to language appropriate values! */
   c_true    CONSTANT VARCHAR2 (4) := 'TRUE';
   c_false   CONSTANT VARCHAR2 (5) := 'FALSE';

   FUNCTION bool_to_str (boolean_in IN BOOLEAN)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN
      CASE boolean_in
         WHEN TRUE THEN c_true
         WHEN FALSE THEN c_false
         ELSE NULL
      END;
   END bool_to_str;

   FUNCTION to_char (boolean_in IN BOOLEAN)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN bool_to_str (boolean_in);
   END;

   FUNCTION str_to_bool (string_in IN VARCHAR2)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN 
      CASE string_in 
        WHEN c_true THEN TRUE
        WHEN c_false THEN FALSE
        ELSE NULL
      END;

      /* Or you could be "nicer" and support many
         different common values...
      RETURN 
      CASE string_in 
        WHEN string_in IN (c_true, 'T', 'Y', 'YES') THEN TRUE
        WHEN string_in IN (c_false, 'F', 'N', 'NO') THEN FALSE
        ELSE NULL
      END;
      */
   END str_to_bool;

   FUNCTION true_value
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN c_true;
   END true_value;

   FUNCTION false_value
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN c_false;
   END false_value;
   
   PROCEDURE put_line (boolean_in in boolean)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (bool_to_str (boolean_in));
   END;
END boolean_pkg;
/

Yes, sure, you can copy and paste this code and use it. Be my guest. It sure isn't rocket science. :-)

And that's the whole point. It usually doesn't come down to anything elaborate or sophisticated to avoid this repetitive coding disorder. Just a little bit of discipline, getting things set up.

Oh and then remembering how to access your reusable stuff (the name of your snippet/template, the actual existence of that reusable code). :-)

Thursday, July 23, 2015

Thoughts on PL/SQL, code sharing, and code generation

[June 2016 update: we have now launched a community code generation project: oddgen - for Oracle Data Dictionary Generator. More information available here. Join the project!]

[While I am sure you will be BLOWN AWAY by what I write below, just in case you are already convinced of the need for a flexible code generator for PL/SQL, skip to "Needed: Flexible Code Generator for PL/SQL"]

If you have spent much time in the PL/SQL world, you will have noticed some unusual aspects:

  • Almost no third party libraries or utilities (prominent exceptions: utPLSQL, Logger, Alexandria Library)
  • Minimal code sharing
  • Most applications built from scratch
The cause(s) of this odd reality (odd in comparison to, say, the way JavaScript apps are built)? Likely several, including:
  • Management concerns about putting "someone else's code" in their database
  • PL/SQL community well established before the days of open source, so no tradition of sharing, of free code, etc.
  • How much code can actually be shared for PL/SQL-based applications?
That last point/question is what I'd like to explore a bit in this post.

It's easy to shake our heads at the "sad state of affairs" when it comes to sharing of code in the PL/SQL world, when it comes especially to thinking about the thousands of PL/SQL teams around the world who, again and again, built an application almost from the ground up with minimal code re-use.

It's easy to conclude: "If only those developers knew better." or "If only managers could be less suspicious of open source." and so on.

But I am thinking more and more lately that the reality of minimal code sharing in our community may more to do with the fundamental nature of PL/SQL (and by extension any other database programming language, such as Transact-SQL).

PL/SQL is not a general purpose programming language. 

It is purpose-built to "extend" the SQL set-oriented, declarative data access language with procedural constructs. 

It is optimized both in terms of performance and syntax to allow programmers to easily (relatively speaking, anyway) build secure, high performance and maintainable applications on top of Oracle Database.

So PL/SQL is tightly tied to SQL, which means it is tightly tied to the tables used in each application. Which means that a large percentage of the code you write/need in PL/SQL will be specific to a given set of tables and columns - and those tables change for each application, usually by a whole lot

From this perspective, it makes more sense that there would be relatively little reuse and code sharing. Reuse is driven by patterns: we write the same code over and over, so, well, why not use the same code over and over, right?

Yes and no.

There's no shortage of patterns in PL/SQL code, but most of them have to do with patterns of SQL operations inside a PL/SQL subprogram. Heck, you could even consider any DML statement as a kind of template or pattern in and of itself (with the infamous "TAPI" - table API - a collection of all such templates). But what varies? A lot: the table names, the column names, the WHERE clause predicates, when you need to fetch all rows for a given value (foreign keys), the conditions under which you want to fetch just one row for a given value (primary keys), etc.

Lots of patterns, but little opportunity for "out of the box" reuse.

I am coming around to seeing that this fundamental reality of PL/SQL as a language designed to serve as an interface layer on top of SQL statements is the biggest "obstacle" to code reuse, and building PL/SQL applications on an extensive foundation/framework of code.

How about you? Do you see a flaw in my thinking?

Because if you do not, you might end up in the same place I find myself:

If code sharing / reuse may not be terribly applicable to PL/SQL, then conversely code generation is way more applicable. 

With code generation, you start from a generic template and then you apply that template to a particular database object, such as a table or view or package. The result is generated code that is specific to that object, but follows the pattern.

tapigen2 offers a fine example of such a code generator. In this utility, Oscar has put the template inside his generator, a part of which you can see here:


CREATE OR REPLACE PACKAGE BODY tapi_gen2
AS
   --Global private variables
   g_spec_template   VARCHAR2 (32767);
   g_body_template   CLOB;
   g_vars            teplsql.t_assoc_array;
   g_unque_key       dbo_name_t;


   PROCEDURE load_spec_template
   AS
   BEGIN
      --Spec Template
      g_spec_template :=
         q'^CREATE OR REPLACE PACKAGE tapi_${table_name}
IS
   /**
   -- # TAPI_${table_name}
   -- Generated by: tapiGen2 - DO NOT MODIFY!
   -- Website: github.com/osalvador/tapiGen2
   -- Created On: ${date}
   -- Created By: ${user}
   */


Then when I run the generator, say against the classic HR employees table, I get this and much more:


create or replace PACKAGE tapi_employees
IS
   /**
   -- # TAPI_employees
   -- Generated by: tapiGen2 - DO NOT MODIFY!
   -- Website: github.com/osalvador/tapiGen2
   -- Created On: 21-JUL-2015 12:56
   -- Created By: HR
   */
   --Scalar/Column types
   SUBTYPE hash_t IS varchar2 (40);   
   SUBTYPE employee_id IS employees.employee_id%TYPE;
   SUBTYPE first_name IS employees.first_name%TYPE;
   SUBTYPE last_name IS employees.last_name%TYPE;
   SUBTYPE email IS employees.email%TYPE;
   SUBTYPE phone_number IS employees.phone_number%TYPE;
   SUBTYPE hire_date IS employees.hire_date%TYPE;
   SUBTYPE job_id IS employees.job_id%TYPE;
   SUBTYPE salary IS employees.salary%TYPE;
   SUBTYPE commission_pct IS employees.commission_pct%TYPE;
   SUBTYPE manager_id IS employees.manager_id%TYPE;
   SUBTYPE department_id IS employees.department_id%TYPE;
   ...   

I have a long and very mixed (in terms of success) history with code generators. I've built several TAPI generators over the years, but reached my pinnacle of code generation craziness with a tool called QNXO (quality in, excellence out - terrible name with nary a single hit in Google, and I still got sued for copyright violation. :-) ). 

Later rebranded as the Quest CodeGen Utility (and no longer available), this utility was a design pattern factory. Built around a Code Generation Markup Language (that I designed, and was therefore kind of hokey), you can define whatever pattern you wanted, for whatever target "language" you wanted (generate a PL/SQL TAPI, a Java class, HTML doc, whatever), then point it at a database object and voila! lots of really cool code.

It also offered a bewildering amount of flexibility (specify your naming standards, for example) that made it hard to use. It didn't very far, but I still think it is a great idea.

And I want to resurrect that idea again, but see if we, the PL/SQL community, can be a much better job implementing it. Because I think that this is:


Needed: Flexible Code Generator for PL/SQL

I am convinced that a code generator with all the right features, with just enough but not too much flexibility, could make a powerful difference in the lives of many PL/SQL developers. You would be able to build your applications faster, relying on high quality code that is, in essence, pre-tested. 

You'd still have to write lots of bespoke, custom code, sure - but a much larger percentage of your code base than ever before could be generated - and re-generated as needed (or simply use the generated code as a starting point and modify from there).

I am also convinced that this effort should from the start be an open source project, primarily driven by PL/SQL developers, not Oracle employees (though I and others would certainly help). 

Here are what I see as the key features of such a utility:
  • (external) Template driven - the template (using what language? that is something to be decided. XML? Angular expressions? A variant on my old CGML? Hmmm....) would be separate from the code generator. 
  • Template libraries - anyone can build their own template, share it with others or keep it to themselves. 
  • Flexible naming conventions - in my experience, if developers cannot specify utilization of their own naming conventions, they will resist using the generated code.
I don't think this will be all that hard to do, especially if it is built incrementally and we avoid scope creep. With the right tempting language, most of the complexity could be "buried" in the templates themselves.

Have I convinced (any of) you? Are you also enthused about code generation and see the potential in a database programming language context? Would you like to help on this project (a little, a lot, doesn't matter - every bit helps!)? I figure we need at least two people who would like to take a leadership role, set up the project, and push it along. I will help, but I think I should take a back seat (but not very far back) for a number of reasons.

Well, I'd love to read your comments below, or send an email to steven.feuerstein@oracle.com. I will compile a list of interested parties and organize a conference call to discuss this further. 






Tuesday, July 21, 2015

PL/SQL Brain Teaser: how many ways can you define a cursor in PL/SQL?

According to the PL/SQL User Guide,
cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.
So how many ways can you define a cursor in PL/SQL?

Let's include in this ways in which you can get Oracle to define a cursor for you, as well.

Please submit a comment with JUST ONE example of a cursor definition. You can submit multiple ideas, but with multiple comments. 

If you include several in a single comment, I will take only the first one that has not already been suggested

Monday, July 20, 2015

Getting started: Sharing your Oracle Database Developer experience

Last week, I was emailing back and forth with Paul, who is a senior developer/architect at a major financial firm. He is widely respected within the company, a valued resource for many development teams. He has also garnered the respect of the PL/SQL development team.

Yet Paul is not well known outside of his company. He does not use Twitter, does not have a blog.

I encouraged him to share his knowledge publicly and he asked me: "How should I get started?"

My first instinct was to say:
  • Register on the OTN Community!
  • Start a blog!
  • Start a Twitter account!
  • Set up a Facebook page!
  • Join LinkedIn!
  • etc.
etc. But then I reflected back on the effort it's taken me, "Oracle celebrity" me, to build up my Twitter following, get people to read the posts on my blog, and I decided to hold off on the instinctive response and instead ask myself:

How can someone who is technically strong but without name recognition contribute to the community in a way that doesn't feel like a waste of time (as in: "If a tree falls in the forest and no one is there to hear it....")?

So here goes:

1. Sure, go ahead and make sure you are fully social media-augmented. Create a Twitter account (and follow me @sfonplsql and oddly enough @stevefeuerstein). Create your Facebook page. Create your LinkedIn account. Make sure you are registered in the OTN Community. Etc.

Then follow people you respect who are present in these ecosystems. This is an incredibly important thing to do for all Oracle technologists (by which I mean: follow me! :-) ). Compared to many other programming communities, Oracle Database developers (SQL, PL/SQL, etc.) are not present to great numbers on Twitter. This means that the "big names" in this same arena lag behind the counts you will see, say, in the SQL Server world. 

And that sends the wrong signal about the vibrancy and breadth of our community. It's a numbers game and we need all of you to "be a number."

2. Make yourself heard on existing forums and popular accounts. Basically the advice "Go where everyone is and participate". 

Once you follow enough people on Twitter, you will have ample opportunity to read and respond to tweets in your area of experience. Ask questions, add nuance to a tweet, share your specific experiences. Go to StackOverflow and take a shot at answering questions. 

Do you read blogs of your favorite programmers and authors? Enter comments on their posts, adding your experiences, asking questions. Bloggers love getting comments and love answering questions. 

3. [With all proper approvals from your employer] Contribute to or otherwise support open source projects for Oracle Database developers. While open source doesn't play a very big role in our lives now, it can only increase over time. Check out the following:
  • OraOpenSource - "Build great open source products for Oracle."
  • Alexandria Library - "A collection of various utility packages for PL/SQL, as well as links to useful libraries hosted and maintained elsewhere."
Don't expect your life to be changed overnight. It takes time to be noticed in a world in which everyone has a voice, and we all go on and on.

Do you have other suggestions for getting started, building a following, sharing your expertise? Please, then, post a comment!

Thursday, July 16, 2015

Nuances of NOCACHE, a.k.a., Steven eating (partially) his words

I posted this tweet yesterday:
#314b #plsql Avoid gaps in sequence NEXTVALs: use NOCACHE option when creating or modifying sequence. #FrugalProgramming
It was a great success social media-wise. Several Oracle technologists I admire greatly responded to it, and none favorably.

But my twitter stats will improve, so hurray! :-)

Seriously, though, it was a problematic tweet, partly because it was a piece of advice that only makes sense in a fairly narrow context, or as @brynlite pointed out:
I expect to repeat this often, Steven: don't advocate specific solutions until the requirements have been stated
To switch I rather snarkily replied:
Keep on saying it, I guess, Bryn, but I am not sure how the advice can apply in the world of Twitter.
OK, fine, it wasn't my finest moment.

As I was stretching this morning I realized: I should have written a post on my blog explaining the tweet, and then I could refer to that. Or as PL/SQL Challenge SQL Quizmaster @kibeha tweeted:
Reason why tweets aren't well suited to teach - unless always tweeet links to blog / video / etc
So here we go:

At the PL/SQL Challenge, we write quizzes. This happens on a regular, but not frequent basis. In other words, insertions into the QDB_QUESTIONS table (I could explain the difference in our data model between a "question" and a "quiz", but why bother, right?) do not resemble placing orders on Amazon.com.

A reviewer noted that with the QDB_QUESTIONS_SEQ defined with the usual cache of 20, question IDs (which we reference regularly in the reviewing and editing process, since there is no "name" per se for a question) would leap forward 20 at a time.

This gave a distorted view (not presented to our players) of quiz submissions. So he recommended switching to NOCACHE to minimize the gaps in question IDs.

I thought that was a fine and sensible idea, and so applied that change. Then, as is often the case, I used what I learned (or was reminded of) that day for my daily PL/SQL tweet.

Unfortunately, this particular "tip" is not appropriate for many (most, really) scenarios faced by developers when needing to generate unique ID numbers.

My apologies, and thanks to all for the corrective advice.

Tuesday, July 14, 2015

Inline your subprograms - but only within same program unit

Got an email from an expert Oracle Database developer (let's call him Sheldon, in honor of my dad, who taught himself programming - RPG, no less!) yesterday:
Is inlining (via PRAGMA INLINE) even something worth talking about?  I’m getting the feeling that it’s not. I’m trying to come up with an example to show folks the beauty of inlining function calls.  And I’m getting the idea that either oracle is doing something like inlining code all the time or is just really super-efficient on the calls these days.
Well, sure, Oracle Database is really super-efficient. Always. Automagically. Don't have to worry about doing any tuning yourself. Oh, wow, what a wonderful world!

Pinch. Wake up! That was a dream you were having, Steven.

Oracle Database does an awful lot to ensure high performance for our applications, but it's not yet fully automatic or automagic. We can do things to drastically screw up performance. And we can also take actions, both in the way we write our code and in the features we utilize, to improve performance.

Inlining is one of those. First, an explanation of the basic concept, then I will address Sheldon's question.

Suppose that I have written very nicely modularized code, with lots of functions defining APIs to underlying rules and data, with nested subprograms to improve readability, etc. 

If you want to write an application that is easy to debug, maintain and enhance, you simply must modularize your logic.

But there is a price to pay: each time the PL/SQL engine executes the invocation of a subprogram, there is some overhead involved (look up the code, transfer data back and forth, etc.).

The idea behind inlining is that at the time of compilation, the compiler replaces the invocation of a subprogram with that subprogram's implementation. Then at runtime, the cost of executing the subprogram is reduced.

Here's a very crude example. I write this code:

CREATE PROCEDURE my_proc
IS
   n NUMBER;

   FUNCTION nested_func (p NUMBER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN p * 10;
   END;
BEGIN
   FOR indx IN 1 .. 10000000
   LOOP
      n := nested_func (indx);
   END LOOP;
END;
/

But with inlining enabled, what is actually executed looks more like this:

CREATE PROCEDURE my_proc
IS
   n NUMBER;
BEGIN
   FOR indx IN 1 .. 10000000
   LOOP
      n := indx * 10;
   END LOOP;
END;
/

Simple enough, right?

OK. In Oracle Database 11g, we (not that I was a part of "we" - Oracle Corporation - back when 11.1 was released) added a new PL/SQL optimization level (3) and a new pragma (INLINE) to allow PL/SQL developers to take advantage of inlining. You can turn it on globally via the optimization level or in a very granular fashion with the pragma (which means: command to the compiler).

I won't go into all the details here. Check out the doc or Tim Hall's fine article or Adrian Billington's helpful exploration for lots more information.

So then - back to Sheldon's question. He was unable to verify the performance benefit from inlining. What's the problem? Well, let's read the introduction to Subprogram Inlining from the doc:
One optimization that the compiler can perform is subprogram inlining. Subprogram inlining replaces a subprogram invocation with a copy of the invoked subprogram (if the invoked and invoking subprograms are in the same program unit). 
Note that last section within parentheses:

The invoked and invoking subprograms must be in the same program unit.

What exactly does that mean? 

It means that the function you want to inline must be:
  1. Defined as a nested subprogram within the subprogram that invokes it, or...
  2. Defined within the same package. 
You've already seen an example of the former. But before I move on to an example of the latter (inlining subprograms from same package) or even showing you an example where inlining will not happen, I'd like to cover:

How do I know whether or not inlining is working?

My approach has been to perform timings on various implementations and see if a particular scenario makes things faster. That can be productive, except when the optimization technique is fairly fine-tuned in its applicability (which can be the case with inlining).

Bryn Llewellyn, PL/SQL Product Manager (follow @brynlite), suggests a far superior approach: use compile-time warnings (though in this case, less of a warning than an FYI).

Let's compile the my_proc procedure with inlining enabled (that's what level 3 optimization means) and compile-time warnings enabled:

alter session set Plsql_Warnings = 'Enable:All'
/

alter session set Plsql_Optimize_Level = 3
/

CREATE PROCEDURE my_proc
IS
   n NUMBER;

   FUNCTION nested_func (p NUMBER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN p * 10;
   END;
BEGIN
   FOR indx IN 1 .. 10000000
   LOOP
      n := nested_func (indx);
   END LOOP;
END;
/

Then I issue a SHOW ERRORS command and see in SQL Developer the following:


Inlining was performed, and once that was done, there were no more calls to NESTED_FUNC, so the  compiler also removed the nested subprogram definition. Cool!

Now let's do the same thing for an invocation of a subprogram that is defined within the same package, but outside of the invoking subprogram:

alter session set Plsql_Warnings = 'Enable:All'
/

alter session set Plsql_Optimize_Level = 3
/

CREATE OR REPLACE PACKAGE pkg
   AUTHID DEFINER
IS
   PROCEDURE test_inlining;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg
IS
   FUNCTION private_func (p NUMBER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN p * 10;
   END;

   PROCEDURE test_inlining
   IS
      n              NUMBER;
   BEGIN
      FOR indx IN 1 .. 10000000
      LOOP
         n := private_func (indx);
      END LOOP;
   END;
END;
/

And SHOW ERRORS reveals:



Finally, let's try to inline a subprogram defined outside of the invoking subprogram's program unit.


alter session set Plsql_Warnings = 'Enable:All'
/

alter session set Plsql_Optimize_Level = 3
/

CREATE OR REPLACE FUNCTION my_func (p NUMBER)
      RETURN PLS_INTEGER
IS
BEGIN
   RETURN p * 10;
END;
/

CREATE OR REPLACE PROCEDURE test_inlining
IS
   n NUMBER;
BEGIN
   FOR indx IN 1 .. 10000000
   LOOP
      n := my_func (indx);
   END LOOP;
END;
/

And what warnings do I see now?





Not a single mention of inlining. Even with the optimization level set to 3. That should be a pretty good hint, right?

But let's make sure. I will explicitly request inlining of the call to MY_FUNC (which should not be necessary, given the optimization level, but bear with me). 

CREATE OR REPLACE PROCEDURE test_inlining
IS
   n NUMBER;
BEGIN
   FOR indx IN 1 .. 10000000
   LOOP
      PRAGMA INLINE (my_func, 'YES');
      n := my_func (indx);
   END LOOP;
END;
/

And then I see the following warnings:



Ah....so you can that the compiler tells me that I've made the request - but it was not "done", only "requested". 

As stated in the documentation, since my_func is not defined within the same program unit as the invoking subprogram, inlining does not apply.

So now I've shown you when inlining will take place and how you can verify that the compiler performed as expected. 

Compile-time warnings are really quite cool. Turn them on in your IDE and pay attention to what they say!

Going back to the original question from Sheldon, though: "Is inlining (via PRAGMA INLINE) even something worth talking about?  I’m getting the feeling that it’s not."

In my (admittedly limited) experience, it can be hard to come up with scripts that demonstrate massive performance improvements. It will definitely help most in "extreme" scenarios involving a very large number of subprogram invocations (loops, recursion, etc.).

[2016-09-23] The recommendation from the PL/SQL Development Team is unequivocal on this matter: you should set the PL/SQL optimization level to 3 at the instance level. Give the compiler the maximum number of tools to use to optimize your code. It may not make a big difference, but it will surely help overall.

I could, I suppose, take the position that you should just turn on inlining globally (optimization level) and let the compiler have at it, except that the documentation also states:
If a particular subprogram is inlined, performance almost always improves. However, because the compiler inlines subprograms early in the optimization process, it is possible for subprogram inlining to preclude later, more powerful optimizations. 
If subprogram inlining slows the performance of a particular PL/SQL program, then use the PL/SQL hierarchical profiler (explained in Oracle Database Development Guide) to identify subprograms for which you want to turn off inlining. 
 So I would suggest that you not bump up the optimization level to 3 globally, and instead put in specific, explicit requests to inline precisely in those programs in which the use case fits clearly and you can demonstrate a noticeable improvement.

Monday, July 13, 2015

Will Oracle ruin the PL/SQL Challenge?

I received this email from a person who has played the PL/SQL Challenge quizzes for years and now wanted to delete his account and player history.

Here's part of the reason he gave:
"I get the impression that the PL/SQL Challenge website is getting closer and closer to Oracle corporation (SSO with oracle.com, Oracle advertisements on the start page, …) – I always valued the PL/SQL Challenge as an independent resource, and these changes are IMHO going in the wrong direction."
This took me by surprise, but perhaps it should not have. Since rejoining Oracle last year (after being away for 22 years), it has become more and more clear to me that a noticeable percentage of my fellow Oracle Database developers and DBAs love the technology but don't love the company. 

I suppose that explains why soon after I went back to Oracle, several people came up to me with a sad look on their face, as if they’d lost a good friend: “Why did you join Oracle?” they asked, shaking their heads. 

For me, being back at Oracle means that I will be able to do more, have more impact, help more PL/SQL developers than ever before. I was excited and thought everyone else would be, too. Clearly, I had underestimated the depth of negative feelings.

This refrain (“Why oh why did you join Oracle?”) has surfaced multiple times over the last six months, as I hired well-known and highly-respected experts in the community (for example, Dan McGhan and Connor McDonald).

Now the PL/SQL Challenge has been moved to an Oracle server, and at least one person (which likely means there are a bunch more) is worried that this move will somehow compromise the independence of the website (implying, I suppose, that criticism of Oracle will not be tolerated, that we will just make excuses for any bugs encountered, that the site will turn into a big marketing effort). 

I do not see happening at all with the PL/SQL Challenge, nor with my more general work on the PL/SQL language.

No one at Oracle up my management chain has ever told me to do things differently now that the PL/SQL Challenge is Oracle property. No has told me to censor certain kinds of comments on the PL/SQL Challenge site, or that I must "toe the corporate line". 

Players remain free and encouraged to comment on quizzes, even if that means you will point out a bug or complain about something in the SQL or PL/SQL languages. And I pledge to you that I will approve comments that are critical of Oracle  both company and technology - so long as they are expressed in an appropriate manner and are relevant to the quiz at hand. 

Same thing goes with this blog.

Please, by all means, give it a try! What’s your complaint? What’s your beef with Oracle? 

To my way of thinking, it’s much better for us to hear your concerns and respond to them, hopefully by improving our products and the services we provide to you..

I am excited about moving the PL/SQL Challenge inside Oracle. It is a powerful platform for delivering quizzes, and I have high hopes of seeing it utilized by more of the product teams (weekly quizzes on APEX! ORDS! Database Administration! Java?). I also have high hopes that with the increased visibility that will come from promoting the quizzes on Oracle Technology Network, the Oracle Learning Library and more, many more Oracle technologists will benefit from the quizzes and the community that has grown up around them.

As for Oracle advertisements on the home page, it is true that we have added a couple of images on the right hand side to help you get more information about Oracle Database 12c and Oracle’s Cloud offerings. I don’t see these as ads, but as helping serve the common needs of Oracle and its user base.  Do you think you do not need to learn about 12.1 features or about where Oracle is going in the Cloud?

I did not join Oracle to help it improve its stock price (though that will be fine with me: I still own some). I joined Oracle to help Oracle Database developers utilize our fantastic technology more fully and to ensure that future generations of application developers understand how Oracle Database can help them build powerful, secure, maintainable applications.

We are currently moving forward with several initiatives in this area, including:

1. Oracle Database Developer Choice Awards: Years ago, Oracle gave out annual Excellence Awards to developers, such as the PL/SQL Developer of the Year award (I am honored to have received this twice). But then those awards stopped. We have restarted - and reshaped - those awards. The Dev Choice awards recognize both technical experience and community building, and our users will vote to choose the winners.


2. Project Solver: You will be hearing lots more about this soon. Solver revolves around the idea of constructing a community-curated expertise repository on all things Oracle Database (and eventually beyond) that will help our users solve their problems much more quickly and will high-quality solutions. This is not about Oracle passing down the word from on high (“Steven says this about PL/SQL”), but about ensuring that the thousands of Oracle Database experts all around the world are better able to share their hard-won wisdom and get recognition for their accomplishments. I know that there are many PL/SQL developers who know as much about PL/SQL as I do, and have real-world experiences that go far deeper than my knowledge in specific areas. 

You know who I’m talking about. That's right: you. Wouldn’t it be great to share your expertise, and be recognized for it?

3. More and better multimedia resources: It’s not easy for a well-established and successful organization to change direction, especially as the world around it changes as quickly as is happening in the 21st century. For too long Oracle has relied on whitepapers and slide decks - mostly stiff and corporate - to transfer expertise. Well, hey, it’s a YouTube, Twitter, LinkedIn kinda world, it’s a quirky, social, don’t take yourself too seriously kinda world, and it’s time Oracle caught up. So we are investing in both people and gear to allow us to “play” in this new world. Hopefully at least some of you have enjoyed Chris Saxon’s Magic of SQL video series and my Practically Perfect PL/SQL channel. You’ll see lots more of that sort of thing in the months and years to come.

I am not trying to argue that our users don't have legitimate gripes about some of the things Oracle does. And I am not nearly arrogant enough to think that the projects in which I am involved will lead to widespread change at Oracle. That’s above my pay grade. I do know, however, that executives in the Product Development organization understand the importance of our current and future users, and the need to change how we communicate and partner with you. And they have given me (and others) resources to get started on that change. 

So by all means, keep hold of your skepticism, voice it wherever and whenever, but please also recognize initiatives from Oracle that are addressing the sources of your skepticism. And very specifically, help me and my Oracle Developer Advocates team put users front and center, and give all of us better resources to build better applications on top of Oracle Database.