Guest lecture on Database Performance

Guest lecture on Database Performance
Oct 31, 2016 by Gert Poel


A few weeks ago I got an invitation to give a guest lecture at the "AP Hogeschool" in Antwerp about "Database Performance".
I've already presented on conferences and teached some courses, but until now only for professionals. This would be the first time for students.

It would be for the "Software Engineering" course of 2nd year bachelor degree IT.
The students already learned about the Oracle database and should know SQL and PL/SQL.

Because it was for students and it was in the course "Software Engineering", I deceided to give the subject a bit more context instead of diving into database performance immediately.

I made three parts:

  1. Thick Database Paradigm
  2. Performance in SQL
  3. Performance in PL/SQL


In the first part about "Thick Database Paradigm" I explained the concept and showed this using a few demo's made in Oracle Application Express(Apex) and Oracle Rest Data Services(ORDS).

If you want more information about the "Thick Database Paradigm", I recommend the article "Why use PL/SQL?" of Bryn Llewellyn.

I wanted to point out that the database is more than just only a "data store" and when you're using it, you better use the full power of it.
Using the full power of the database for building an application means that you have to think about design and have to write SQL and PL/SQL.

With this as starting point, I moved on to the SQL performance part.
Beginning by explaining the most important feature of an Oracle Database when talking about performance: the "Cost Based Optimizer".  I also talked about statistics, indexes,... all things that influence an execution plan of a query.  Then I moved on to how to read/interpret an explain plan.
And the last part of the SQL performance was Query Result cache.

Over to PL/SQL performance...
I'm mainly a PL/SQL developer, so a lot of PL/SQL performance enhancements came up: Nocopy hint, pipelined table functions, Function Result Cache and bulk processing.  Everything explained by examples and demo's.  

I did this guest lecture last week.  Everything went well, except a few hiccups in the (live) demo's.
I hope I didn't overwhelm the students too much...  There weren't any questions during the lecture, but afterwards a student came to me with a few interesting questions which I explained.  And from these questions I could make up he understood almost everything, which is good.

According to the lecturer for that course it was an interesting take on the subject.

For me it was a great experience standing in front of students.  Looking forward to a next time!