Faulty Quotes 3 – Contradictory Information

December 21, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Frustrated by some of the confusing and contradictory information I have encountered on the Internet over the years, I put together a “cheat sheet” to help identify useful information.  The “cheat sheet” lists several questions that one might consider when reviewing books and web articles prior to changing parameters (or implementing other changes) based on the information found in those sources:

• Is a specific Oracle release mentioned in the book or article? What was true, or thought to be true, with release 8.0.5 might not be true or even a good idea with release 11.2.0.1.
• Does the article have a publication date, and is there a revision history that identifies the date and what modifications were made to the article? Articles which change from one day to the next without knowing what changed, and why the article changed, are difficult to use as justification for changes to the initialization parameters.
• Are there any articles by other authors on the Internet which agree with the author’s suggestions or sharply disagree with the author’s suggestions? If Oracle’s official documentation strongly disagrees with the contents of the article, which of the two sources are correct? Should the advice be deemed an over-generalization which worked as a fix for a one time problem that is now advertised as something all DBAs should do as a first step in performance tuning?
• Is there reproducible evidence that supports the claims made? Or, is the majority of the justification similar to “I have seen it a 100 times” or “a DBA at a fortune 50 company said to do this” or “I have been doing this for 25 years, and you should too”?
• Does the parameter actually control the behavior which it is purported to control, and are there any potential side effects from modifying the parameter?

Miscellaneous Metalink Performance Articles

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”