More On Database And Application Tuning

I've just come back from an initial couple of days at a client site, where we're looking at tuning up their data warehouse. I'd previously mentioned one of the areas we'd looked at (function-based indexes), and having done a bit more research, I thought it worth mentioning one of the sites I'd come across that looked interesting.

Brian Peasland has made available some white papers which are particularly relevant to the database tuning process. First up is a paper entitled "A Guide To Solving User Problems";

"Many times, a Database Administrator is called upon to solve problems that a specific user is encountering running their database application. It can be difficult to track down the cause of the user s problem in order to determine the appropriate resolution. This paper serves as a guide to tracking down user problems. This paper starts off at an introductory level and finishes with advanced techniques."

The paper goes through the initial information you'd want to find out about a user problem (what error messages are being received, does anyone else experience the problem, is it reproducable, and so on), and then steps through the various processes you'd want to go through to do a full technical analysis of the user's situation, including finding out details of their session, what SQL they're executing, locks and blocks, and so on. It then covers tracing and the famous 10046 event, and then goes through some common issues that might crop up, and how to resolve them.

Another couple of useful papers are "Knowing When To Rebuild Indexes", which discusses the situations where it's advisable to rebuild an index (usually when there's been lots of inserts, updates or deletes) and "Segment Statistics", which "Discusses how Oracle 9i's new V$SEGMENT_STATISTICS view can help you get information about index and table usage within your database. This is a great way to get answers to questions like which table is used the most and is the index used at all?"