Jonathan Lewis Seminar – Optimising Oracle Performance by Design-

Last week I visited the Oaktable Seminar “Performance by Design” by Jonathan Lewis, held in Utrecht. I would like to share some of the topics of the seminar which I found particularly interesting.  

Locking

The locking mechanism in Oracle is equivalent to the English queue. Strictly speaking, locking is a enqueue waiting in line for the enqueue resource. Processes whitch require a lock on a certain row in the table, actually have to wait in line for other processes in the queue to complete.<br>Another thing with locking is, that row locking does not find place in a way that most people think of as “locking the row”. There is not a structure where all the row locks currently held by the system. Advantage: no actual limit on the number of concurrent row locks. Disadvantage: you can never find out which rows are locked.

Latching

Where locking is the gentle and polite queuing in front of a London bus, latching is a group of American Football players trying to get hold of the same ball. Oversimplified, a latch is a flag which marks a linked list. In a multi-user system linked lists may not be modified while being read by another process. So a latch consists of a counter and an exclusive write flag; readers top up the counter only when the exclusive write flag is not set. On the other hand, writers can set the exclusive write flag at any time but cannot start modifying the linked list before every reader has unsubscribed itself and hence deregistered themselves off the latch.

A brief troubleshooting strategy

A rule of thumbs which I found very useful:

• if your system has high cpu, the problem is probably in bad or excess sql.
• if there is a high amount of waits, the problem is probably in latches, locks or I/O.
• I/O can be both in disk or network; locks can be internal or external

Statspack is very useful, but it doesn’t tell you everything. Configure statspack so that it takes regular snapshots and see what it does on various times. Always keep an eye of the scale of things when interpreting Statspack results. What is the timespan of your snapshot, how big is the system, and maybe there was a batch job running at the time of your snapshot?

There are a lot of very informative performance views in the database. Learn to use them! A short list of them: v$sess_io, v$filestat, v$segstat, v$sql

What I’ve learned

• There are many ways leading to Rome, some methods appear very fast but you usually pay for it in another step of the process
• A commit is cheap and a rollback is very, very expensive. This is because a rollback does not roll things back, but actually re-applies all information in the undo buffer to the datablocks and then…. commits.
• Setting up a new connection to the database comes at a cost, but ending a connection can be really expensive. Thinks of all the work that needs to be done to clean op the PGA!
• Fine-grain access control, row-level security and VPD’s are very fancy but have a large performance impact. In effect, every reference to a table or view is replaced with an inline view.
• In fact, be very careful with every new feature introduced by Oracle; examine the performance-impact before you use or even activate the feature.
• Resource management does not work as nice as you might expect it to.
• Oracle is (not yet) fit for actual use of objects in the database. Creating objecttypes is not a problem of course!
• Last but not least: there is still so much too learn!