Re: How to find the 10 worst performing SQL statements?

"Andy" <enzoweb_at_hotmail.com> wrote

> I have been asked to find out the 10 worst performing SQL statements
> on a V816 database on Solaris.

Besides my DBA from Hell view.. ;-)

It is very difficult in determining the 10 worst performing SQL statements. This IMO indicated a bit of a lack of understanding about performance on the part of the person(s) who gave you this task.

Performance is not consistent. It can and will vary. Especially when you dealing with large increases in data volumes per month. An index range scan which gave the best performance, could run like a dog the next. With the reverse being true for a full table scan. So performance tuning is a bit of a misnomer as it is usually an ongoing administrative task of the DBA, and not a single once-off wave of the good old magic wand.

Statistics are session bound. If you have something like a batch update process which execute multiple SQLs against the database, how do you find which one is the problem? The statistics are for the entire session - not the individual SQL.

To find and isolate a single SQL is the problem. If you do not have a single SQL per session setup (something which I insist on in data warehousing), you can not rely on the session stats to tell you anything but a _global_ view of what that session/process did.

The only method is, like some of the other replies stated, to take snapshots of v$ tables in an attempt to catch a bad SQL in the act. But that too is problematic.

Even a SQL that has a short execution time (e.g. update of a few rows) can be a bad SQL.. For example, the WHERE clause could be using an index and is not. Or the developer uses a function on a column and not the literal. This can easily go undetected if the volumes are small.. but will become very noticeable when volumes increase.

Then there is the design of the database. It plays a major role in performance. With a bad design, there is often little you can do to fix certain performance problems. What really gets me is that it seems that none of the new people today heard of Codd, not even to mentioning understanding normalisation.

Then there is the technical implementation. Partitioning the data. Using parallel query. Or many of the other tools at the DBA's disposal. And hell yes, it is complex. Anyone who thinks that a real DBA position is easy and limited to simplistic administration tasks (hello all you so-called SQL-Server DBA's), are missing the point. And the reason why a good DBA is worth a good salary.

Then there is the process flow. Is the logic used by the developer correct? Is he/she processing the data in the best most effective way? Do they understand how to apply this logic within the constraints of a RDBMS and Oracle? Or do they think that rollbacks are unlimited and CPU power infinite? I'm still astounded by the number of developers that still think ito of flat file processing when it comes to processing data from a database. Oracle is not Adabas, VSAM or ISAM.

Lastly, performance is a compromise. Yes, an additional complex compound index will make reporting faster. However, it will make the OLTP side slower. Another bee in my bonnet is that it seems that designs today ignores this entirely. Come on, 13 indexes on a single table (from a posting a week or two ago)!! That is _ridiculous_.

But not far fetched as one of my largest production tables has 4 indexes (indexing the same set of columns in various combinations and sequences), with the total index size being 3x that of the table. Wish I was around here when they did the design as I would not have taken any prisoners, nor spared the lead pipe.

The bottom line is that finding the 10 worst performing SQL statements against a database is a futile exercise. There is no way to prove that these 10 are the worst performers. There are no guarantees that you can simply fix these and now magically have a high performance database.

Performance tuning is much more than that. Pity that your boss fails to understand that. BTW, are you working with Dilbert for the same boss? You have my total understanding and deepest sympathies Andy.

My advice? Drink coffee. Lots of it. Not that it helps, but then why care when you are full of caffeine.

--
Billy