Testing an Oracle database for common bugs/performance issues?

Question

Are there any good scripts that I could run against my Oracle database to test for SQL defects or maybe common performance issues?

Edit: Everything in an Oracle database can be queried. From the PL/SQL packages, indexes and sql running stats. The performance books say look in this place and it will show some absolute values that need the developer to be able to interpret. Has anyone combined their knowledge to include this interpretation within the scripts?

Answer 1

Are you asking for the information in this book?

http://www.amazon.com/Oracle-Database-Performance-Techniques-Osborne/dp/0072263059/ref=sr_1_1?ie=UTF8&s=books&qid=1264619796&sr=1-1

Are you asking about this wiki?

http://wiki.oracle.com/page/Performance+Tuning

Or are you asking for this vendor information?

http://www.oracle.com/technology/deploy/performance/index.html

---

Edit. There is no magical set of queries that you simply run and set the various tuning options.

1. Oracle is very complicated. Changing a parameter to make one thing fast can make several other things faster or slower. Or makes makes the instance consume more real memory than you have installed. It's hard to generalize this into magical queries. You have tools, but even then, the tools give you tuning options and you may need to run different experiments.

2. Performance is a balance. You have to strike a balance between physical I/O time and CPU time. It's not possible to generalize this into a magical query. Your system may need faster physical I/O (data warehouses, for instance, often need this) because it can't effectively work from cache. My system may need faster processor time and will have to work in cache to achieve this.

3. Performance is a function of your application. No magical query of Oracle will reveal a single thing about how your application is designed to work.

Answer 2

Enterprise Manager and it's associated performance tools are a good place to start looking for queries that are consuming the most resources. Here you can see the plans generated for your SQL, view traces of long running queries, etc.

If you have a budget, there is Spotlight by Quest. I've only used the trial version, but I found it useful.

Answer 3

I would recommend checking out the book Optimizing Oracle Performance and any of Cary Millsap's other writings. It is a waste of time to think about optimizing every query. You really need an approach to finding out where your performance bottlenecks are. His Method R approach is a very good one to read up on. Also most of Tom Kyte's books go into detail about performance issues.