Stelios Charalambides - Oracle SQL tuning with Oracle SQLTXPLAIN

About the Author

Stelios Charalambides has more than 20 years experience working with Oracle databases. He is OCP certified from 7 to 11g and has worked as a Senior Consultant DBA on both sides of the Atlantic, dealing with all aspects of system design, implementation, and post-production support, solving practical problems in a wide variety of environments. He now works as a Principal Oracle Support Engineer developing time- critical solutions for tier-one customers with high-profile performance problems. Though born in the UK, Stelios now lives in New Hampshire with his wife, two children, and two dogs. Once this book is complete, he looks forward to devoting more time to his hobbies.

About the Technical Reviewer

Mark Bobak is a Senior Oracle DBA at ProQuest Company in Ann Arbor, MI. Hes been working in IT for over 25 years. For the past 13 years, he has worked as an Oracle DBA. He is an Oracle ACE and a member of the OakTable Network. He is also active in his local Oracle User Group (SEMOP) and attends and presents at conferences at the local, state, national, and international levels.

Acknowledgments

Carlos Sierra is the author of SQLTXPLAIN. His enthusiasm, patience, and willingness to listen allowed me to enter the world of Oracle SQL tuning with SQLT. Without his help and inspiration, this book would not have been possible. I hope that the text meets his expectations and that I havent made any errors in my description of his baby. If I have I apologize. The error was all mine.

Thanks are also due to my first family within Oracle (The Oracle Performance Group), Chris Crocker, and Cathy Scully (who had the good sense to hire a Brit) and Mike Matagranno, whose persistence in getting me to move to Oracle was truly astounding. I hope none of you have regretted that decision. Special mention must go to Mauro Pagano, whose special knowledge and endless enthusiasm convinced me that I had something useful to say about SQLT.

Many thanks also to Mike Gervais, my first manager, whose kindness helped so much during my first months at Oracle. Alan Bashy, Nick Meola, and Peter Gadah are fellow members of the performance team who also deserve special mention. They are truly awesome and showed me that no matter how much you think you know about tuning, there is always more to learn. I have been very lucky to come across so many knowledgeable and enthusiastic colleagues and managers. There isnt room here to thank them all, and for that I am sorry.

Thanks are also due to Professor Garry Hunt who always believed in my potential and taught me that anything is possible if you put your mind to it.

From Apress, Jonathan Gennick made the final decision to publish this book, showing faith when others were doubtful. Thanks also to Anamika Panchoo, who put up with my missed deadlines: Im sorry, I did my best. Last but by no means least, Chris Nelsons herculean efforts have made immeasurable improvements to my poor illiterate scratchings (dont change that) and allowed me to produce a book I hope will be useful for years to come. Chris shouted, pushed, and otherwise kept me going by telling me the finish line was just around the corner. For your patience and hard work I sincerely thank you.

APPENDIX A

Installing SQLTXPLAIN

You may ask, why show the installation log for a utility that installs in five minutes and only has at most five parameters as inputs. The plain fact of the matter is that despite the simplicity of the inputs and that most installs can be quick and easy, there are situations where the installation can fail: either because the inputs were wrong or because the installation steps were not done from suitably privileged accounts. You occasionally get questions about an installation that may have worked, but the user is not clear. This is another reason to show what a normal installation looks like. I also look at alternative ways in which SQLT can be installed, including a silent mode and a remote install mode. There are also ways to change the setup of SQLT after it has installed, and I mention some of the options available there. Finally I also mention how to de-install SQLT, in case you want to install a later version, for example. By showing these options and describing the installation, I hope to convince you that the installation is simple and robust and should be considered an asset to any system rather than a liability.

A Standard SQLT Installation

As an assistance to anyone installing SQLT I have supplied a partial install log of the SQLT utility. I have highlighted and documented those areas of the installation that are of interest and note. New comers to SQLT may find this useful, but regular users will find the section on other ways to install SQLT more interesting. In the example SQLT installation below I have bolded my responses to make it clear where I am entering data, and I have removed blank lines for brevity. In this example the SQLT zip file has been downloaded to a local directory and unzipped. Inside the zip file we find the sqlt directory and the install directory (as we saw in Chapter 1). Now we connect as SYS into SQL*Plus and start the main installation script sqcreate.sql

So far the installation has initialized and discovered the environment it is installing itself into. The next steps are to gather information so that the installation can be properly targeted. For most circumstances installing into the local database is the easiest and simplest option. You only need a tablespace in which SQLT will store its information, packages, functions, and the data repository. The size of this is generally very small (in my small installation this was about 2 Mbytes). If you specify a remote connection here the data will be stored elsewhere, such as on a remote database.

The optional Connect Identifier is not often used, as you are normally installing SQLT locally. We cover the case of a remote install later, in the section A Remote SQLT Installation. In this case, however, we just press Return.

The next section is the part of the installation that most often causes confusion. The main application user of SQLT is the schema name of the user that actually executes the SQL to be analyzed. This is not SQLTXPLAIN. Throughout most of this book my example schema is called STELIOS, so I enter