Previous installment: Day Two: Miles To Go Before We Sleep Next installment: Day Four: Nobody Did It
How can you get very far,If you don’t know who you are?How can you do what you ought,If you don’t know what you’ve got?And if you don’t know which to doOf all the things in front of you,Then what you’ll have when you are throughIs just a mess without a clueOf all the best that can come trueIf you know What and Which and Who.
—The Tao of Pooh [Bear] by Benjamin Hoff
P.O.I.S.E.D. is the acronym I invented for the performance tuning method described by Chris Lawson in The Art and Science of Oracle Performance Tuning; it stands for Problem, Observation, Interpretation, Solution, Execution, and Documentation. Chris Lawson’s book was published in the days of Oracle 9i so it’s tempting to dismiss it as irrelevant. But as Mogens Norgaard recounted—in his foreword to the free reprint of Scaling Oracle8i by James Morle: “Once upon a time Jonathan Lewis was asked if his book Practical Oracle 8i would ever be published in an updated 9i version? His response was to suggest that you put a sticker with a ‘9’ over the ‘8’ on the cover of his book, because the advise [sic], methods and techniques described in the book were still very valid with Oracle 9i.” On a side note, you can do as I did and buy a used copy of Practical Oracle8i for pennies. My copy even came signed by Jonathan Lewis; you should be so lucky too. A used copy of Chris Lawson’s book cost me a little more; perhaps they are scarcer or perhaps somebody beat me to it.
P.O.I.S.E.D. is not just a performance-tuning method; it applies to any problem that must be solved. Every problem-solving exercise has these six stages, whether the exercise takes six minutes or six weeks. You may scoff but I’m a great believer in articulating principles; if I cannot articulate my principles, what is the likelihood of my observing them? It might lead to situations like this.
Emergency room nurse on the midnight shift:
We just admitted a man who is complaining that his head is hurting.
Emergency room intern:
Tell him to take a Tylenol and come back in the morning if his head is still hurting.
Emergency room nurse:
Should I be concerned about the knife sticking out of his head?
Some reports are slow after the database upgrade.
Let me check for chained rows.
The type of problem dictates the information that should be collected in the Observation phase. For example, if the problem is systemwide performance degradation, an AWR or “hanganalyze” report may be appropriate. The Interpretation phase concludes with a determination of root cause. From a management perspective this is a very good place to be even though a solution has not yet been constructed. Absent a determination of root cause, there is uncertainty; something that management abhors.
If we are investigating poor performance a single SQL statement, the tool of choice for the observation phase is SQLT for all but the simplest cases. SQL performance depends on many factors including CBO parameters, statistics, table structure, and indexes. A SQLT report assembles all the pertinent information into a single report. How big is the buffer cache? How big is the database? How big are the tables and indexes? What settings are used to collect statistics? Which columns involved in query predicates are not indexed? What are the definitions of views used in the queries? What is the degree of parallelism of the tables and indexes? Have any CBO parameters been modified recently? Is the data in the tables extremely volatile? Is the query plan continuously changing? Is the query plan being affected by adaptive cursor sharing, cardinality feedback, or adaptive query optimization?
Every question related to a single SQL statement is answered by the SQLT report. If investigating performance differences between two databases—such as a pre-upgrade database and a post-upgrade database—you need SQLT reports from both databases.
The Twelve Days of SQL
The Twelve Days of NoSQL
Copyright © 2015 Iggy Fernandez