Tech13 – Day 2: 11 different streams with 3 dedicated to the databases. Many interesting topics and it’s sometimes hard to choose to which session attend. I tried to keep an eye on sessions about performances.

I was interested on more than 6 sessions for that day but I can’t speak of all.

PL/SQL performance

Timothy Hall, blogger from oracle-base.com was speaking about improving the performance of PL/SQL calls from SQL. It’s not always easy to start the day with technical content but it was very interesting and well covered.

Several technics have been discussed like:
– scalar subquery caching
– Query result cache
– Function result cache
– Manual caching using collections or context

The main point here is that caching can be an efficient way for improving performance and it you’re on 11g you should consider using result cache technics if applicable. See my blog about the query result cache feature.

Build API for pluggable database provisionning

I also attended to a session from Bryn Llewellyn. It was about creating an API from creating pluggable databases en 12c.
In fact, creating and altering pluggable databases is done through SQL commands. The main challenge here is to be able to execute the wrapper procedure as SYSDBA. Using a package in SYS schema with definer rights it’s not working. Not so easy to follow.
The workaround is to use a scheduler job starting SQL Plus script that connect with a privileged user using ‘AS SYSDBA’.

ASH

This session was hosted by Graham Wood, it was all about Active Session History (ASH). I already talked a little about ASH in blogs from the Open World.
ASH can be difficult to use because there are many dimensions in the buffer (93 columns available) and there are two common mistakes: apply sum or average function on column “time_waited”. As a sample doing such mathematics is wrong because the longer the event is the more chance you have to get it in ASH (time bias).

But ASH is ver efficient if you use it on one dimension to know what is going on in the database even if the database is under big load.
It’s is now time for a drink in exhibition hall. More on next days !