NYOUG Training Event: "High Performance PL/SQL" on November 7, 2014

If you want to be able to create contemporary high-performing PL/SQL code, welcome to my training event in NYC (hosted by NYOUG) on November 7, 2014:

http://nyoug.org/event/nyoug-fall-2014-training-event-high-performance-plsql/?instance_id=258

I think, it is as important as ever to follow best practices for server-side development. There are lots of options that could get the job done "now", but very few that would survive for months/years. Also, there is a constant pressure from various "alternative" solutions to move the business logic somewhere else (front-end/ middle-tier/other database technologies/you name it). But my strong believe that by properly utilizing available Oracle technologies you can achieve much better results without compromising data security and manageability.

I've put together more than a decade of my experience to a full-day seminar that can really help you. Here is an outline:

1. Know your Environment – This section introduces the available tools and features related to
PL/SQL performance tuning including: data dictionary views, logging, tracing, profiling etc. It
also discusses the proper ways to instrument code deal with and exception handling.

2. User-Defined PL/SQL Functions in the SQL Context – For most developers, knowledge of
PL/SQL starts from writing user-defined functions. As a result, even if this code is functionally
correct, the program units are fired significantly more often than needed, impact CBO
decisions, and cause execution plan degradation. This section addresses these issues and
includes a number of examples of how PL/SQL can extend basic SQL functionality.

3. SQL Sets Within PL/SQL – The goal of PL/SQL is to efficiently communicate with SQL sets
using cursors. This key feature of database cursors is often missed by assuming that they are
nothing more than pointers to SQL sets. The most efficient way of working with SQL sets is to
access data using set-based operators. This section reviews the results of two case studies
focused on comparing different access approaches. The first one looks for the most efficient
ways of moving significant volumes of data between remote locations, while the second tests
the impact of MULTISET operations on resource utilization.

4. Triggers are NOT Evil - Both table triggers and INSTEAD-OF triggers are examined from a
global system optimization point of view that includes not only the aspect of functional
correctness, but also the tradeoffs between multiple goals. For example, depending upon the
available hardware, developers can select either CPU-intense or I/O-intense solutions. This
section also covers some of the most common performance problems related to different kinds
of DML triggers and the proper ways of resolving them.

5. Keeping the Cache - One of the most important performance tuning strategies is to ensure
that you are not doing exactly the same thing multiple times. However, often you cannot avoid
this repetition. In those cases, the best option is to minimize the impact by utilizing existing
caching techniques, either manual or built-in. Currently in Oracle, there are different caching
mechanisms, each with its own strengths, drawbacks, and side-effects. This section introduces
all of them and details the selection criteria for matching the appropriate mechanism to specific
situations.3

6. WHEN OTHERS THEN… – The topic of PL/SQL performance tuning is too large to cover
in a 6-hour seminar. However, some tips and tricks can be very useful to attendees. This last
section describes some one-off cases that overlap multiple areas. It also includes an extended
question & answer period where attendees will get a chance to share their experiences and ask
questions.

Oracle OpenWorld'14 Guide!

I think, the biggest challenge for people coming to such huge events as Oracle OpenWorld is to navigate through all of the events that simultaneously happen at various locations. You are always under stress that you may miss something, or that you may hit a bad talk, or that you may not even know that there was a talk directly linked to your key production problems.

Finally there is a real guide to OpenWorld - and I am very pleased have one of my presentations listed there:

http://www.otechmag.com/2014/otech-magazine-fall-2014/

Looking forward to see everybody in San Fran!

My new book

It seems to me that I talked about my most recent book everywhere except for my own blog... So, let me introduce you to the one and only performance tuning book dedicated to PL/SQL:

This book took me much longer to write than I initially anticipated - and to be fair, that's the reason for staying quiet at the blog. I just didn't have time/energy to write everywhere :-) Although, it was fun writing - I finally got a chance to create a digest of my own knowledge and put it into any format I choose to. Of course, some shaping from my esteemed guru and co-author Dr. Paul Dorsey was definitely needed, but still it was as free-form as you get. Publishers trusted us so much that they didn't attach any strings to the contract - we were reshaping the structure of the book up to the very end.

I think, I did my best and reached the goal I was shooting at. I really hope, this book can help a lot of people to build better software. Now it's your turn to tell me whether I was right or wrong.

P.s. Here is a review from Steven Feuerstein - link.

Good sources of Oracle information

Pretty often I am being asked about various online and printed source of Oracle-related information. Finally I decided to create a blog post here where I will start to accumulate all the goodies that I just "happen" to find. Maybe I will eventually create even some kind of a guide... Let's see.

Before we start - a couple of disclaimers:
* It is my personal list of favorite resources, so all of the following statements assumed to be prefixed with IMHO :-)
* The list represents my own interests, which are somewhat between DBA and Developer lands (plus a bit of Architect).
* I highly value printed books, because while working on them authors structure (and check!) the material much better comparing to blogs. So, if I mention a name below, you also should check his/her books - they will be worth your money. Even blogs have the advantage of being more interactive (you may get immediate feedback), the quality of books should not be underestimated.

Ok, let's start:

1. My gurus (overall)
- Tom Kyte asktom.oracle.com - no extra explanations needed, I hope ;-)
- Steven Feuerstein http://www.stevenfeuerstein.com/ - one of the main faces behind PL/SQL for the last 20 years
- Jonathan Lewis http://jonathanlewis.wordpress.com/ - after his explanations I finally started to get the Cost-Based Optimizer
- Daniel Morgan http://www.morganslibrary.org/ - huge collection of useful articles and examples! Often more understandable than original Oracle docs.

2. My teachers in the field of performance tuning
- Tanel Poder http://blog.tanelpoder.com/ - I've seen him reading raw trace files! He can decipher X$-objects too.
- Cary Millsap http://carymillsap.blogspot.com/ - extremely rare case of holistic approach to performance tuning (Method-R!)
- Craig Shallahamer http://shallahamer-orapub.blogspot.com/ - another example of very scientific approach to tuning
- Chris Antognini http://antognini.ch/blog/ - a lot of depth in understanding of how Oracle internals work together.

3. My fellows. They do the same kind of job as I do (only sometimes better!)
- Tim Hall http://www.oracle-base.com/
- Adrian Billington http://www.oracle-developer.net/
- Alex Nuijten http://nuijten.blogspot.com/
- Rob van Wijk http://rwijk.blogspot.com/
- Marco Gralike http://www.liberidu.com/blog/

4. DBAs land
- Arup Nanda http://arup.blogspot.com/.
- Alex Faktulin http://afatkulin.blogspot.com/
- Company blog (a lot of smart people up there!): http://www.pythian.com/blog/ Just to name a few: Alex Gorbachev, Michael Abbey, Christo Kutrovski, Jared Still, etc.

Analytic functions in real life

Yesterday while working on a real production report I found a requirement that forced me to come up with a bit nontrivial usage of analytic functions. Here is the problem (translated to SCOTT-level terms):

- I have a table that contains a DATE column (HIREDATE)
- Input parameter is also a date (IN_DT)
- For each department I need to show all rows that have HIREDATE after IN_DT plus the most recent row from the past (only one!).

Obviously, if you split the last condition into two, the solution is reasonably clear:

SELECT empno, ename, deptno, hiredate
FROM scott.emp
WHERE hiredate>:IN_DT
UNION ALL
SELECT empno, ename, deptno, hiredate
FROM (
     SELECT e.*,
            row_number() 
               over(PARTITION BY deptno
                    ORDER BY hiredate desc, 
                             empno desc
                   ) rank_nr
     FROM scott.emp e
     WHERE hiredate<=:IN_DT
     )
WHERE rank_nr = 1

The only problem of this solution - it needs two passed over EMP table. But in my production case that table is huge. Also, just to make life interesting, the result of the specified query has to be joined to 8 other tables to provide the required output. It is understandable, that my first optimization idea was to somehow do a single-pass query. To my own surprise, Oracle analytic functions are flexible enough to help me out:

SELECT empno, ename, deptno, hiredate, rank_nr
FROM
(
SELECT e.*,
      dense_rank()  
        over(PARTITION BY deptno
             ORDER BY 
               CASE WHEN hiredate > :IN_DT THEN '9999'
                    ELSE to_char(hiredate,'YYYYMMDDHH24MISS')||
                           lpad(empno,10,'0')
               END  desc   
            ) rank_nr,
      max(hiredate) 
        over(
             PARTITION BY deptno) max_dt        
FROM scott.emp e
)
WHERE max_dt > :IN_DT AND rank_nr IN (1,2)
      OR
      max_dt < = :IN_DT AND rank_nr = 1

In this solution I two analytical functions
1. DENSE_RANK is forced to order in the following way:
- bring all future dates to the top by assigning them exactly the same value '9999'

- order all other dates by converting them to 'YYYMMDDHH24MISS' format. In that format DESC would bring the most recent date up anyways.

- to resolve conflicts of multiple entries associated with the same date concatenate past dates with padded EMPNO. This way textual sort would correctly work even if the length of EMPNO may differ.

2. MAX() OVER() brings the highest hire date for each department.

Now the outer SELECT has enough information to filter out correct rows: if the highest date if after the parameter, you need to bring ranks 1 and 2 (all future dates + the last past date). Otherwise you need only rank 1 (last past date).

I understand, that this solution may take a couple of seconds to wrap your head around :-). Still - I was able to do exactly what I needed in a single pass!

Summary: if you are working on reports, analytic function is the MUST. That's the only safe way to write efficient code that would consistently work over large data sets.

Oracle OpenWorld 2014

Less than 3 weeks left before the biggest Oracle event of the year kicks in - and I bet, Oracle OpenWorld'14 will impress anybody (as usual). I am honored to be selected as one of speakers there. Here are my two talks:

- UGF4428 "Expanding SQL Horizons: PL/SQL User-Defined Functions in the Real World"
  Sunday, Sep 28, 1:30 PM - 2:15 PM
  Moscone South - 310

- CON4429  "Calling SQL from PL/SQL the Right Way"
Thursday, Oct 2, 2:30 PM - 3:15 PM
Moscone South - 308

Everybody is welcome! I really think that these presentations can improve you day-to-day PL/SQL.

P.s. I will do my best to provide a daily update from the ground! There should be a lot of interesting things to share.

Time to resurrect blogging!

Sorry for staying quiet for some time, but for some good reasons. Some of these reasons will be explained in later posts, but for now I would like to share the story I wrote upon request of Steven Feuerstein, who asked to tell how PL/SQL impacted our life. Here is my story:

-----------------

I started to work with databases already in college, but more from the data modelling/business analysis angle. When I moved to the USA in 2000 (just after graduation) I quickly recognized that my language skills are too limited to work as BA, so I went to evening Oracle courses in Philadelphia to deepen by technical skills – SQL, PL/SQL, SQL*Plus, into to administration etc. 

And suddenly I realized that I like it even more than playing with ERDs! Also, a bit of a lucky break – one of my teachers recommended me to his former boss. To be fair, I have no idea how I survived that interview – somewhere in the middle of it I realized that this boss is no less than Dr. Paul Dorsey, whose book I was recently quoting in my Master Degree Thesis… Anyways, on February 26th, 2001 Dulcian Inc got a brand new 21-year-old employee with the focus on database development. 

And couple of months later I’ve got a task that really shaped my specialization: I needed to write a module that would check for invalid objects in the schema and compile them in the appropriate order of dependencies. So, the set of tools is obvious (now), but at that time I had to discover: data dictionary views, object collections, recursion, and, finally, Dynamic SQL. For my reader/listeners – that list sounds really familiar, isn’t? :-) Looks like Oracle versions change, but favorite toys stay the same!

After that module started to work (I think, even Dr. Paul got surprised!) my field was settled: solving strange/unsolvable database problems using SQL and PL/SQL. That’s pretty much what I still do now – even 14 years later. Bad news – there are lots of such problems. Good news – it’s fun to solve them (and that’s the way I get new stories for books/presentations!)

---------------------

Not exactly a fairy tale, but it definitely shows that you never know how your career will move forward, especially just after college. By the way, I brought with me to the USA that Russian-translated book by Dr. Paul! It stays right now in the office together with other memorabilia. Here is the scan: