Thursday, April 20, 2017

Tips for getting along with your DBA

Developers and DBAs: can't we all just get along?

Sure we can!

We just have to break out of the old routine of

Developer: Hey, DBA, add twelve indexes to make my code run faster!
DBA: Hey, Developer, tune your code to make it run faster!

That is, finger-pointing.

Instead, we need to work together, and developers I am not the least big reluctant to say:

It's up to us, not the DBAs, to take the first steps.

So here are tips on what you, the developer, can do to foster a strong, collaborative and highly productive relationship with your DBA:

1. Ask your DBA for advice. 

"I want to make my code run faster. What do you think I should do?" There's no better to improve a relationship than to show some humility and express interest in the opinions - and knowledge - of others.

2. Do the right thing. 

Learn about the performance-related features of PL/SQL (and SQL) and apply them. Here are some links to help get started:

PL/SQL Optimization and Tuning (Doc)
High Performance PL/SQL Videos
SQL Analytics Videos by Connor McDonald
Introduction to Indexing Videos by Chris Saxon

3. Give your DBA a heads-up when your pattern of writing code changes. 

Utilizing new and different features of PL/SQL can have a ripple effect on memory consumption and overall application performance. Don't blindside your DBA.

For example, you learn about executing "bulk SQL" from PL/SQL. So cool! So powerful! And potentially a big PGA memory suck, through the use of collections.

Or you discover the Function Result Cache. Another very exciting enhancement added in 11.1. "Hey, I'm going to add the RESULT_CACHE clause to 100 functions. So easy!" Yes, but you might kill overall database activity with latch contention.

Friday, April 14, 2017

Databases for Developers class on Oracle Dev Gym: take it any time!

Chris Saxon, a Developer Advocate at Oracle, has put together a 12 week "bootcamp" introduction to SQL. Each week consists of a short video, plus 3 quizzes. You probably won't need more than 30 minutes to complete them. Every fourth week, Chris will be on a live webcast to answer questions.

While there is a start date for each new class, there is no end date.

This means that you if you missed the beginning week or two (or seven!), you can still register for the class and take the earlier classes. To do this, visit the Oracle Dev Gym (for which you will need an Oracle account). Then go to the Classes page in one of two ways, shown below with the blue arrows.

Click on Databases for Developers, then click on the Register button.

You can then pick from any of the weeks in the Course Outline that have already been started, and work your way through them.

We hope you enjoy, and get lots out of, the Databases for Developers course! And while you are at the Dev Gym, be sure to check out our weekly tournaments as well our library of over 2,500 quizzes on SQL, PL/SQL, Database Design, Oracle Application Express and Logic!

Thursday, April 6, 2017

Does level 3 optimization change PL/Scope data? No!

I gave a webinar on April 6, 2017 for the Taste of Kscope17 series for ODTUG ( on Change Impact Analysis with PL/Scope. Here are the slides from SlideShare. I will add a link to the video when it is available.

After my presentation, this question came up: if you set the optimization level to 3 (inlining of subprogram code), will that change the PL/Scope data gathered? Interesting question.

Suppose your function body contains an assignment to variable x. Just that one place. But the function is called in ten places. Will PL/Scope find ten assignments to x or just one?

Just one, as you can see in this LiveSQL script. The identifier information is gathered before optimization. Which makes perfect sense. Post-optimized code is no longer PL/SQL code.

Here's the procedure I tested this one:


      RETURN p * 10;


      RETURN p * 10;
   DBMS_OUTPUT.put_line (f1 (1));
   DBMS_OUTPUT.put_line (f2 (TRUE) + f2 (FALSE));

   PRAGMA INLINE (f3, 'NO');
   DBMS_OUTPUT.put_line (f3 (55));

Here's the query I used to get my identifier information back out.

  SELECT i.signature ||'-'|| s.line ||'-'|| s.text text 
    FROM    user_identifiers i 
            user_source s 
         ON ( = i.object_name 
             AND s.TYPE = i.object_type 
             AND s.line = i.line) 
   WHERE object_name = 'PLSCOPE_DEMO'  
ORDER BY s.line

And the output is the same regardless of the optimization level:

47BFC756469F1D97B6C84EF73A9C5D48-5-   FUNCTION f1 (p NUMBER)
785705602C9312732B24D9A341360ACF-5-   FUNCTION f1 (p NUMBER)
C762ED3C314ABB3D7257031401DD1583-5-   FUNCTION f1 (p NUMBER)
C762ED3C314ABB3D7257031401DD1583-5-   FUNCTION f1 (p NUMBER)
2C17DB6428F739B212C1E11EED057D63-6-      RETURN PLS_INTEGER
785705602C9312732B24D9A341360ACF-9-      RETURN p * 10;
1B5895A65C6952FDD192221BFC45A132-12-   FUNCTION f2 (p BOOLEAN)
C0BA9F319D1E759AD02A3C7138D62232-12-   FUNCTION f2 (p BOOLEAN)
EE1C5F13825B7DF0BF06D82DE633992E-12-   FUNCTION f2 (p BOOLEAN)
1B5895A65C6952FDD192221BFC45A132-12-   FUNCTION f2 (p BOOLEAN)
2C17DB6428F739B212C1E11EED057D63-13-      RETURN PLS_INTEGER
C0BA9F319D1E759AD02A3C7138D62232-16-      RETURN CASE WHEN p THEN 10 ELSE 100 END;
335958BDCA260D5E550A47F8194048DC-19-   FUNCTION f3 (p PLS_INTEGER)
205AC954A1ADD2BC3DC6A112A2081ADA-19-   FUNCTION f3 (p PLS_INTEGER)
2C17DB6428F739B212C1E11EED057D63-19-   FUNCTION f3 (p PLS_INTEGER)
335958BDCA260D5E550A47F8194048DC-19-   FUNCTION f3 (p PLS_INTEGER)
2C17DB6428F739B212C1E11EED057D63-20-      RETURN PLS_INTEGER
205AC954A1ADD2BC3DC6A112A2081ADA-23-      RETURN p * 10;
C762ED3C314ABB3D7257031401DD1583-26-   DBMS_OUTPUT.put_line (f1 (1));
1B5895A65C6952FDD192221BFC45A132-29-   DBMS_OUTPUT.put_line (f2 (TRUE) + f2 (FALSE));
1B5895A65C6952FDD192221BFC45A132-29-   DBMS_OUTPUT.put_line (f2 (TRUE) + f2 (FALSE));
335958BDCA260D5E550A47F8194048DC-32-   DBMS_OUTPUT.put_line (f3 (55));

For more information about PL/Scope:

For more information about Inlining:

Sunday, April 2, 2017

Wikileaks bombshell: PL/SQL source of all other modern programming languages!

Copyright @2017 CodeNewsWire "News coders can use", dateline 1 April 2017

Wikileaks dumped its latest batch of revelations on the world on April 1, 2017, this time focusing on the world of software programming. From dishing out the dirt on the origins of the Internet (think: Area 51) to emails candidly deriding JSON as nothing more than the latest attempt (XML being the last one) to avoid carefully designing your database, this trove of previously secret secrets is sure to keep Silicon Valley gossiping for months.

But buried deep within the 2.5 trillion byte download is evidence of a conspiracy so vast, so unbelievable, so extraordinary, that it is hard, well, to believe.

But if it came from Wikileaks it must be true. And that conspiracy was built around - and is maintained around - this incredible bit:
All modern programming languages, from Java to JavaScript, Scala to Go, are actually all implemented in the Oracle PL/SQL language. Oh, and Linux, too.
You are probably laughing to yourself, right now, right? PL/SQL? That straightforward - and some might argue, rather archaic - procedural language, apparently useful only for managing transactions in the Oracle Database? How could you possibly implement Java in it? Linux? JavaScript?

Wikileaks has, apparently, two words for you:

It is well-known to practitioners of PL/SQL that there are several documented indeterminate behaviors in the language (which some, cynically, try to brush aside as merely "undocumented"). For example, the state of a variable that you SELECT INTO will be indeterminate if the statement raises TOO_MANY_ROWS. It seems to usually have the data from the first row selected in it, but this cannot be trusted.

Developer responses in the modern age (aka, the Age of Apps) to this indeterminacy have been to shrug and get on with life.

But Wikileaks has discovered minutes of a secret meeting taking place in 1991 in the office of the CEO of Oracle, attended by none other than James Gosling (creator of Java), Linus Torvald (inventor of Linux and Git), Brendan Eich (creator of JavaScript) and several others whose identity were masked in the minutes.

At this meeting, Larry Ellison disclosed that his engineers had designed PL/SQL to exploit quantum entanglement (which manifest as "indeterminacies") as a pathway into multiverse threading. The result was a programming language so elegant, so powerful, so subtle and so mysterious that it can be used to implement anything and everything.

The assembled experts were blown away. And thoroughly convinced by a 5 minute demonstration by Ellison, which involved, among other things, using PL/SQL to look into the box containing Schroedinger's Cat to tell us precisely and unambiguously whether or not it is alive. Or was. Or could be. Whatever.

The fear from the crowded meeting was evident, but Ellison put those fears to rest. "Don't worry, fellas," he was recorded as telling them. "We are not going to announce this news to the world. It will be too destabilizing. Instead, we've built a quantum-level API that you can all use to build whatever you want. And if you insist on continuing to use C, that's OK, too, because we've used the PL/SQL tachyon exploit to travel back in time and re-implement C in PL/SQL as well."

In the end, all these language experts agreed: there was too much to gain from PL/SQL to ignore it. But the world could never know. And so it was decided: Oracle would continue to promote PL/SQL as a database programming language, special-purpose and not very object-oriented. Purveyors of other languages would continue to make fun of PL/SQL and tout their own latest and greatest innovations.

CodeNewsWire reached out to Edward Snowden, whistleblower supreme (or arch-traitor, depending on your point of view), regarding this incredible revelation. "What?" he replied. "This is news? I thought that was in my dump from the NSA and CIA. All the best Agency developers write nothing but pure PL/SQL, on hopped-up quantum computers. And they use edition-based redefinition."

Steven Feuerstein, author of way too many books on PL/SQL, was hit harder by this news than most. On the one hand, he was pleased to hear about the enhanced power of PL/SQL. On the other hand, as he expressed it on his Twitter account, "How could I have missed something as big as this? And could I get another book out of it?"

Determined to gain insight into what really went on - and is going on, and will go on, all at the same time - he tracked down Linus Torvald to a heavily fortified Git Repo in a Helsinki rave club. Torvald wouldn't open the door, but he did shout the following: "Go away! Linux is mine, all mine! I was never at that meeting! The cat is dead, always dead, in all the universes I've visited using my PL/SQL transporter. Oh, crap."

Now the world knows.

It's all PL/SQL, all the time.

But don't worry, you can live in denial, and keep on programming in JavaScript or Go or Went or Ruby or Scala or Java.

Just show some respect.