Tuning The Untunable: When Indexes and Optimizer Don’t Help

In 2004, I gave a tuning presentation in the Boston area at NOUG

الرئيسية

If you haven’t seen it, do take a look. It actually went viral and I received emails from around the world.

The PDF can be found here:

Oracle Performance Tuning

Recently, I gave a tuning presentation at the Manitoba Oracle User’s Group
http://mboug.wordpress.com/

This MBOUG presentation is a follow up. It covered a number of very mysterious tuning scenarios that I’ve encountered since NOUG, and the techniques I used to overcome them. In these cases, using optimizer and indexes usually did not help at all.

The PDF can be found here:  

Presentation: Graph Databases – Overview and Applications

In April 2014, I gave a presentation at my Alma Mater, the University of Winnipeg: Graph Databases – Overview and Applications

It was presented to the faculty and students of the Applied Computer Science Master’s program.

Most had not seen graph databases before. However, I expect that some of them will be using graphs in the near future.  🙂

A PDF of the presentation can be found here:

Stamping Out Cartesian Products

Over the last number of years, I kept finding Cartesian Products (CP) in queries and other objects like views.

These have caused a number of serious performance issues.  A single CP in a query can make the whole server go slow.  The other problem is the result sets, which come back many multiples too big.

No gizmo tool will help you with a CP.  No query analyzer will tell you that you have a problem, or how to fix it.  Sorry mouse lovers.

Cartesian products are one problem area that only observation, knowledge, and good SQL skills will overcome.

When I have pointed out the cartesian products to the rest of the crew, many were in denial.  The really odd thing was that the more “experienced” the database person was, say, over 10 or 15 years of experience, the less obvious it was to them.  And the more they insisted on playing with statistics or indexes.  Which of course did nothing to deal with the real issues.

Oracle Performance Tuning

One of the first entries to start my notes should be the one thing I’ve already done a lot of writing on.

Since I started working with the Oracle database in 1995, I’ve always been very concerned about tuning, performance and response times. Even with the poor software and hardware that we had, I still had to make it work fast. Even though much of the conventional wisdom at the time was to just buy faster hardware, this was never an option at the companies where I worked. I made things go fast by doing what I could do; changing the code, not by buying new hardware.

Later, I was promoted from a developer, to a DBA, and a unix admin. A new job in year 2000 – 2001 gave me a daunting challenge.  The startup wanted to insert ONE BILLION rows of data into an Oracle 8i database, every day. Which is about 11,000 rows per second.  This is not difficult now, but with year 2000 hardware and Oracle 8i software, I was only getting about 6,000 row inserts per second to begin with on the hardware I had. I researched how to do this all day and evening. It took me very deep into Oracle internals and storage.

One of my “few” discoveries was that a number of the tuning paradigms at the time, after I tried them, really made absolutely no significant difference at all. Some of these concepts were whether we used RAID 0, 1, or 5, and hit ratios in Oracle’s buffers. My rigorous experiments where I modified only one variable at a time, such as the RAID setting, produced no difference in the data results at all. The data results were not supporting the paradigms.