A friend pointed me at this interesting blog (The Go-Faster Oracle Blog), which has some useful links to performance tools. Analyzing an Oracle database without having the Enterprise Manager either installed or maybe not available to you can be a nightmare. He has developed some Excel sheets, which access the AWR data and output graphics and more right down to individual session analysis. Very useful tool-set, so take a look.
Recently in Performance Category
Christian
Antognini has made a number of presentations on the topic of Oracle's 11g Optimizer. He has posted his presentation and demo, which he last held at the DOAG Conference in Nürnberg, Germany. He also has a whole load of useful other publications available for viewing.
This article is basically looking at using multiple values in bind variables but it strikes upon an interesting point regarding the CBO. There are situations where the optimizer assumes a value of 8168 rows simply as a good guess.
When tuning a database you have to contend with a vast number of possibilities as to how to go about it. Whilst anything Oracle supplies in the way of tools and techniques can be regarded as a valid means of correcting some unwanted behavior there are some tools which should be avoided in production.
The use of "Hints" in particular should regarded as an exploratory method for diagnosing what could be done to improve things or what might happen when a database gets loaded with large amounts of data and performance starts to degrade. Using hints in a production environment should only be done as a last resort and as a temporary work-around until some more robust correction can be installed. This article 'Rules for Hinting' by Jonathan Lewis demonstrates why that is.
So be warned - there are almost always better solutions, which could be used instead of hints.
Okay, so we all know that problem of SQL-Statements suddenly going AWOL and using a completely different execution plan from what the database has used in the past. Most of the time the database will take the right route by changing the execution plan - but, alas, not always. By using outlines you can force the Oracle database to use the same execution plan regardless of what has changed in the way of statistics or indexes. This entry neatly explains how it works and when you might want to use outlines.
These videos are really great for demonstrating why things often go wrong in a database. It's very easy to understand how good technology can be used badly and the results are disastrous.
The videos are also quite funny, so enjoy...
Part1...
Part2...
Part3...
This OTN article explains why it is sometimes difficult for people to grasp the paradigm of writing good SQL which the Cost-Based-Optimized (CBO) can break down effectively. Often SQL-Statements are still written with the Rule-Based-Optimizer (RBO) in mind and that makes life very difficult for the CBO. This article helps to bring you up to date with what you should be thinking of when writing SQL statements for Oracle10g.
