Miscellaneous Metalink Performance Articles

21 12 2009

December 21, 2009

(Forward to the Next Post in the Series)

A couple months ago I scanned through Metalink looking for interesting articles.  I found a couple that seem to be well written, most with recent modification dates, that someone out there might enjoy reading when troubleshooting performance problems.  Hopefully, the documents still exist on the Metalink replacement.

  • Doc ID 233112.1 “START HERE – Diagnosing Query Tuning Problems” – basically a click to jump to the specific problem being experienced.
  • Doc ID 745216.1 “Query Performance Degradation – Upgrade Related – Recommended Actions” – a tree like structure for performance tuning.
  • Doc ID 398838.1 “FAQ: Query Tuning Frequently Asked Questions” – another tree like structure.
  • Doc ID 223806.1 “Query with Unchanged Execution Plan is Slower than Previously” – another tree like structure.
  • Doc ID 387394.1 “Query using Binds is Suddenly Slow”
  • Doc ID 604256.1 “Why is a Particular Query Slower on One Machine than Another?” – another tree like structure.
  • Doc ID 372431.1 “Troubleshooting: Tuning a New Query”
  • Doc ID 163563.1 “Troubleshooting: Advanced Query Tuning” – another tree like structure
  • Doc ID 122812.1 “How to Tune a Query that Cannot be Modified”
  • Doc ID 67522.1 “Diagnosing Why a Query is Not Using an Index”
  • Doc ID 69992.1 “Why is my hint ignored?”
  • Doc ID 163424.1 “How to Identify a Hot Block within the Buffer Cache”
  • Doc ID 223117.1 “Tuning I/O-related waits” – another tree structure
  • Doc ID 402983.1 “Database Performance FAQ” – mentions pstack, system state dumps, 10046 traces, AWR/Statspack
  • Doc ID 66484.1 “Which Optimizer is Being Used”
  • Doc ID 271196.1 “Automatic SQL Tuning – SQL Profiles”
  • Doc ID 276103.1 “Performance Tuning Using 10g Advisors and Manageability Features”
  • Doc ID 463288.1 “How to generate an outline with a good plan loaded into the shared_pool”
  • Doc ID 43718.1 “View: V$SESSION_WAIT Reference”

Actions

Information

4 responses

23 12 2009
Balakrishna.

Hi Charles,

Its a kind of Off-Topic all together for this discussion . Since i dont have your email id , i need to comment here . I think you have read lots of books thats what i understand by looking at the Book Reviews you wrote . I just wanted to know which books do you recommend me to read sequence wise please ..

As i always think how can people blog Oracle Internals and their Experience . I understand one thing , in case if you want to become Expert you need to spend lots of time in Experimenting things thats how you learn and think widely about Oracle .

A Small Request from my end , i am expecting your reply to this comment positively .

Thanks & Regards

Bala

23 12 2009
Charles Hooper

Bala,
Thanks for the comment. Your question is actually the topic of a future blog post that I had planned for sometime in the next couple of weeks.

Until that time, I suggest that you read the 15th post in this forum thread, where I described some of the books that marked the turning points in my knowledge of Oracle, and also what motivates me to continue learning about Oracle:

http://forums.oracle.com/forums/thread.jspa?threadID=972294&start=0

You also need to decide if your interest is in general DBA work, or digging into the details of performance tuning.

Quick suggestions:
* A solid foundation of Oracle specific SQL is needed. I enjoyed reading “Mastering Oracle SQL and SQL*Plus“, and I believe that book provides a solid foundation. That the book appears to be in the process of being updated, and might even include page numbers this time (http://www.apress.com/book/view/9781430271970). I am currently reading “Oracle SQL Recipes: A Problem-Solution Approach” (http://www.apress.com/book/view/1430225092), probably about 30 pages into the book now – and I believe that I have already found a small handful of minor errors/issues with the book that would make it difficult to use as a starting point.
* A solid foundation of understanding Oracle’s behavior is needed. I believe that Tom Kyte’s “Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions” book (http://www.apress.com/book/view/9781590595305) is one of the best sources. I understand that Tom Kyte also re-wrote the Oracle 11.2.0.1 “Concepts Guide” (http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/toc.htm), so that might be a decent substitute for his book.
* If you are planning to do general DBA work, probably the next book should be on the topic of RMAN. The books in the Oracle documentation library are good, and you will find two reviews of other RMAN books on this blog.
* Next, I would suggestion reading a book that provides a solid foundation of the Oracle wait interface. “Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning” seems to be the best source of that information, but it would be nice to see an update of the book that covers more recent releases of Oracle.
* Next, the “Oracle Performance Tuning Guide” from the Oracle documentation library.
* Next, I suggest the book “Troubleshooting Oracle Performance” – the book is great for not only introducing people to various approaches for troubleshooting problems, but also provides foundation knowledge that is needed in order to understand why an approach worked.
* Next, I suggest digging deeper into troubleshooting with 10046 trace files – Cary Millsap’s “Optimizing Oracle Performance” is the best source for this information.
* Next, I suggest digging deeper into troubleshooting with 10053 trace files – Jonathan Lewis’ “Cost-Based Oracle Fundamentals” is the best source for this information.

If queueing theory, introduced in “Optimizing Oracle Performance“, is of interest, take a look at “Forecasting Oracle Performance”

If Statspack/AWR report reading, introduced in the “Performance Tuning Guide” is of interest, see the excellent series of articles on Jonathan Lewis’ blog.

If you want your jaw to drop, take a look at Tanel Poder’s blog. I also recommend reading all of the blog entries on Jonathan Lewis’ blog and Richard Foote’s blog.

I have not yet had the opportunity to read the chapters in the “Expert Oracle Practices: Oracle Database Administration from the Oak Table” that were written by other members of the OakTable Network. But, I can tell you that this blog, and that of Randolf Geist, provide a good idea of what our two chapters in the book will cover – essentially everything from “guessing” based on what one would find through a Google search, to trying to use the buffer cache hit ratio, all the way to reading process stack dumps (and quite a number of other things in between). I suspect that the other chapters of the book will be just as helpful, if not more so. Because I have not seen the other chapters, I really cannot tell you if this book belongs in the above list, or even where it belongs. It does not appear that there is a full description of the book on Amazon’s site or the book publisher’s site. A table of contents for the book may be found here:

http://hoopercharles.wordpress.com/2009/11/30/expert-oracle-practices-oracle-database-administration-from-the-oak-table-book/

OK, so much for a quick list of suggestions.

23 12 2009
Balakrishna.

Hi,

Thanks for your reply …. I really appreciate .

But Expert can never become with out DBA Concepts .. so i prefer Both DBA concepts and some in depth Performance tuning techniques.

Just for curisity are you in Oracle-L in case if yes .. with which email id do you interact with Oracle-L.

Thanks & Regards

Bala

23 12 2009
Charles Hooper

No, I am not a member of Oracle-L, however I do try to read the posts on the list. I considered joining the list in the past, but found that by the time I would be able to provide an answer, five other people would have already supplied a more complete answer.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 148 other followers

%d bloggers like this: