Oracle Week 2016: Advanced PL/SQL Optimizing for Better Performance (slides)

This is the presentation for Oracle Advanced PL/SQL session I did in Oracle Week 2016, a few weeks ago.

I wrote about the Oracle Week conference in the previous post so I won’t go over that again… 🙂

This presentation was all about PL/SQL. Most of it are things that are quite some old, but for some reason people seem to miss out on them. The presentation is divided into 3 parts.

The first one is development and it takes up most of the day. We talk about complex data types, cursors and things that are into PL/SQL should probably know. In this part I also try to add some new features and stuff that people will find interesting, or else it gets really boring. The second part is about the compilation processes and the last part is about tuning.

This year presentation was quite big – around 40 people in the class and got excellent feedback: 4.97/5 for overall satisfaction.

Here is the agenda:

  • Developing PL/SQL:
    • Composite datatypes, advanced cursors, dynamic SQL, tracing, and more…
  • Compiling PL/SQL:
    • Dependencies, optimization levels, and DBMS_WARNING
  • Tuning PL/SQL:
    • GTT, Result cache and Memory handling
  • Oracle 11g, 12cR1 and 12cR2 new useful features
  • SQL Developer Command Line tool

Read more

PL/SQL Teaser: A Valid Package With No Callable Subprograms

Last week I saw a PL/SQL teaser tweet by Steven Feurestein (@sfonplsql):

The question sounded simple: can you build a valid package with header and body with more than one procedure – that can compile but can never run. That was a really interesting question and I found myself thinking about it while working.

Since it is really had some good things we could learn from it, let’s go over my thought process and get to know this unique PL/SQL behavior.

Read more