DB OPTimize

Tag Archive: Merav Kedem

Yes, sometimes it is the database fault

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…)

Exadata Storage Index solving an endless update

The following simple update was running every evening, endlessly, on a production Exadata machine, Oracle version (well, not truly endlessly, but for about an 30-60 minutes, which is considered to be quite endless in my standards…).  (more…)

No, It is not always the database fault..

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…)

Bind variables in Oracle 11.2

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…)

Abnormal program behavior and massive log file sync

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…)

Bad screen design, bad performance

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…)

Thinking of adding a new index? Tune SQL’s text before

Creating an index can certainly do marvellous things to SQLs. Even after all those years in the field, it is still always thrilling for me to witness huge performance improvement seen after applying a suitable index to an unturned query. The performance gain is simply spectacular and the notices slump in resource consumption this query is taking (I/O, CPU, etc) is immediate. (more…)

Beyond database tuning

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…)