Hasler - Expert Oracle SQL optimization, deployment, and statistics

IOUG Press is a joint effort by the Independent Oracle Users Group (the IOUG) and Apress to deliver some of the highest-quality content possible on Oracle Database and related topics. The IOUG is the world's leading, independent organization for professional users of Oracle products. Apress is a leading, independent technical publisher known for developing high-quality, no-fluff content for serious technology professionals. The IOUG and Apress have joined forces in IOUG Press to provide the best content and publishing opportunities to working professionals who use Oracle products.

Our shared goals include:

• Developing content with excellence
• Helping working professionals to succeed
• Providing authoring and reviewing opportunities
• Networking and raising the profiles of authors and readers

To learn more about Apress, visit our website at www.apress.com . Follow the link for IOUG Press to see the great content that is now available on a wide range of topics that matter to those in Oracle's technology sphere.

Visit www.ioug.org to learn more about the Independent Oracle Users Group and its mission. Consider joining if you haven't already. Review the many benefits at www.ioug.org/join . Become a member. Get involved with peers. Boost your career.

About the Author

Tony Hasler is an independent software consultant specializing in SQL tuning for Oracle databases. He graduated with an honors degree in computer studies from Lancaster University in the United Kingdom in 1978 and spent the next 15 years working as a software developer.

Tony worked in the United Kingdom and the Netherlands for the first couple of years of his career and then moved to New Jersey, where he met his wife-to-be, Marianne. Tony returned to the United Kingdom in 1988 to join Digital Equipment Corporation (DEC) as a team leader on the OpenVMS operating system development team. During his five years at DEC Tony filed a patent on optimizations for logging protocols, and as a representative of the British Standards Institute (BSI) he participated in the creation of a number of publications from the International Standards Organization (ISO).

Soon after leaving DEC in 1993, Tony began his second career in support work, specializing mainly in performance tuning of an application by Wall Street Systems ( http://www.wallstreetsystems.com/ ). In 2004 Tony moved away from applications and operating systems and began specializing in Oracle database technology. At the time of writing, Tony is working as a full-time SQL performance specialist.

Tony is a well-known speaker in the United Kingdom and has published several articles for the UK Oracle User Group. Tony also occasionally makes an entry in his blog, http://tonyhasler.wordpress.com/ , when he finds something interesting to talk about.

Although Tony lives in Scotland with his wife, Marianne, none of his current clients have operations in Scotland; they are global financial institutions with UK headquarters in London. The world of telecommuting has arrived.

Tony has two grown-up children, both of whom have followed in their fathers footsteps. Timothy has a degree in computer gaming from Abertay University in Dundee and is working as a software developer in Edinburgh, while Thomas is working as a software developer in London after obtaining a software engineering degree from Edinburgh.

About the Technical Reviewers

Frits Hoogland is an IT professional specializing in Oracle database performance and internals. Frits frequently presents on Oracle technical topics at conferences around the world. In 2009 he received an Oracle ACE award from the Oracle Technology Network, and a year later he became an Oracle ACE Director. In 2010 he joined the OakTable Network. In addition to developing his Oracle expertise, Frits works with MySQL, PostgreSQL, and modern operating systems. Frits currently works at Enkitec LP.

Randolf Geist has been working with Oracle software for 19 years now, and as a freelance database consultant since 2000. He focuses primarily on performance-related issues, in particular helping people to understand and unleash the power of the Oracle cost-based optimizer (CBO)being available for short-term, short-notice assignments regarding Oracle performance troubleshooting and in-house seminars and consulting.

He blogs about CBO-related issues at oracle-randolf.blogspot.com, regularly contributes to the official OTN database-related forums, and is also co-author of Expert Oracle Practices, a book published in 2010 by the OakTable Press in which he contributed the performance-related chapters.

Randolf is a regular speaker at all major user conferences around the world and also acts as an instructor for the Oracle University as part of their Celebrity Seminar program, where he gives seminars on Advanced Oracle Performance Troubleshooting. Randolf is a proud member of the OakTable Network and the Oracle ACE Director program.

Dominic Delmolino is the lead database technologist for Agilex. He specializes in Oracle and Agile database deployment methods and is a member of the OakTable Network. He enjoys researching and presenting on advanced database topics.

Acknowledgments

It may seem like an act of political correctness for an author to begin a list of acknowledgments by thanking their spouse. That is unless you are an authoror an authors spousein which case, you would understand. It has taken me somewhere between 1,000 and 1,500 hours to write this book. When you consider that I have kept a full-time job at the same time you may begin to see the impact that such a project has had on the lives of family and friends. On top of the usual stresses of life, we moved between houses a few months ago and Marianne had to do the vast majority of the work involved! Writing this book would have been utterly impossible without Mariannes support, and my thanks are heartfelt.

Although I would in no way wish to liken myself to Isaac Newton or the other great minds that have used this metaphor, I am but a dwarf standing in the shoulders of giants. My two main sources of both information and inspiration are books entitled Cost Based Oracle by Jonathan Lewis and Troubleshooting Oracle Performance by Christian Antognini. Jonathans book was published by Apress in 2008 and the second edition of Christians book was published in 2014, also by Apress. I am fortunate to know both authors personally. Jonathan has engaged in e-mail correspondence to help clear up my own misunderstandings on a couple of topics in this book, and Christian has provided me with a draft of his own chapter on optimizer transformations, thus saving me countless hours of work.

However, the idea for writing this book did not come from either Jonathan or Christian. It did, in fact, come from Dan Tow, author of SQL Tuning, a book published by OReilly in 2003, but a book that I only discovered in 2011. Ill come back to Dans book in the introduction.

I have to admit that I am not inclined to spend a lot of my spare time trawling through large numbers of blog entries, but it would be wrong of me to suggest that my education and inspiration have come solely from words written on real paper. The Internet is a wonderful resource for filling holes in your knowledge, and one website in particular, www.hellodba.com , a Chinese website from Wei Huang, has provided me with a lot of details about many of the undocumented hints in the Oracle database.