Previous installment: The sisters of the mother of all SQL antipatterns First installment: DON’T PANIC
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: “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 Oracle 8i for pennies. My copy was signed by Jonathan Lewis; you should be so lucky too. A used copy of Chris Lawson’s book costs a little more; perhaps fewer copies were printed and they are therefore scarcer.
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 Observation and Interpretation phases feed each other. 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 he 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 the SQL statement is answered by the SQLT report.
In future installments, I will discuss the use of SQLT.
To be continued. Please leave your comments below or email me at firstname.lastname@example.org.
Copyright © 2015 Iggy Fernandez