Your database history is precious. Keeping it is crucial. Keep it as if you keep a valuable treasure.
And why is that? because it holds the normal and the abnormal behaviour of your database, and both or them are equally important.
It will save you, over and over again. That’s a guarantee. (more…)
The following simple update was running every evening, endlessly, on a production Exadata machine, Oracle version 11.2.0.3 (well, not truly endlessly, but for about an 30-60 minutes, which is considered to be quite endless in my standards…). (more…)
Exadata or not, sometimes Oracle is still Oracle, optimizer is still optimizer, and math is still math.
Here is the story: Instance is Oracle 11.2.0.3, running on an Exadata machine (X2-2 half Rack).
User is complaining query is taking too long to finish (about 5 minutes).
When things are starting to get wrong, Database ( i.e DBA ) is always the first tier to blame. I am sure you have faced this situation, probably much more than once or twice.
Guilty as charged, until proven innocent, right? (more…)
A bit of an history before I begin…
Oracle 10.2 had exposed bind variables values passed to a SQL statement via a new view named V$SQL_BIND_CAPTURE. Nevertheless, to limit the overhead, Oracle captured the bind at most every 15 minutes for a given cursor. (more…)
Historical performance data is extremely important when tuning application. This gives us the ability to instantly know when things start go wrong, and the ability to investigated over the past what start causing things to go wrong. Often I am feeling like a modern Sherlock Holmes, searching for criminals 🙂 (more…)
Recently one of my customers had loaded a new version into production. Immediately performance issues have started to rise. I have spotted a new query that became one of the top-3 heaviest SQLs running in the instance. Certainly not something that can be ignored… (more…)
In my previous post “Beyond Database Tuning” I have shown a situation where application was running a very heavy SQL (~2 hours) each day, while in fact, after speaking with the application, I understood that this SQL is simply not needed and its output is only functioning as a loop counter in a Java code. (more…)
Let me share with you something that happened to me today.
I saw this very heavy SQL, running several times a day, taking up to 2 hours each time, consuming lot of database resources (CPU & IO).
This select was doing a count on a very big table (150M rows) joined with a smaller table (5M rows) and had some non selective filter predicates in its where clause.
Tuning capability didn’t look so promising for this SQL.
I have decided to go and ask the application for the purpose of this SQL, and investigate what it does with its result (count).
What I found was quite a shock. They code was basically doing that: (more…)
When we search for untuned SQLs we can look for SQL that its execution time is objectively long, but we should also search for SQL which has high amount of short executions, or in other words frequently executed SQL. When dealing with frequently executed SQLs, even a small improvement in one execution (average execution time) will yield a performance improvement, since this “small” improvement of one execution is multiplied by its many executions. (more…)