Experiments from the Field..Based on True Stories

                 

Lets face it. Deadlock and Latching issues have always been hard to
debug. Many a times, we don't focus too well on application tuning,
even though, it always advisable to look at the application design and
user access trends first. These things don't come to the mind too
readily.

Many a times, we do face situations where the users are
deadlocked, sitting and watching blue screens or the application was
freezing up. While there are many reasons for this to happen: Poor OS
implementation, (swapping/disk/Memory issues) , RDBMS bugs, RDBMS
initialization parameter values, having a lot of non sharable SQL, a
good share of cases can also be caused due to Poor application coding,
without undertanding the sequence in which resources should be accessed
or acted upon (updated/deleted etc).


This is one such case study in which a deadlock was traced
back to a deadlock situation un-aware code and the solution was to
enhance the code to not cause deadlocks in the future. While the change
was really simple, picturizing the deadlock in action was really
difficult.

This article also goes into the mechanics of contructing the
deadlock situation in slow motion. This makes it an instructive case
study to read through for future reference.

Read the entire case study here...

                                        

They say, it pays to K.I.S.S (Keep It Simple and Smart). The simplest ideas are the greatest ideas.This philosophy also applies to sql
coding when we tell the oracle SQL engine what to fetch and how
to fetch it (Indirectly). There are great rewards for simplistic thinking.


Lets discuss this principle through a case study in which the user was
trying to fetch very simple data through an over-kill query, involving 
RA_* views which were internally using some of the same tables. The SQL
engine was therefore just trying to fetch and filter too much data from
the
same sources. It was not certainly efficient.


In this case study, we see how its important to understand the "Why" of
a query, rather than the "How" of a query. Many times, just rethinking
the whole approach pays great dividends.

Read the entire case study here...