Guerrilla Oracle Tuning Part #1: Process Intro, History, Benefits & Limitations

[vaillan]

This is the first post in a series of posts proving greater details on using a the gather info script to diagnose both Windchill and other database performance problems.

1. Introduction

---

Using the gather info script to perform Guerilla Oracle tuning is a proven processes in which key aspects of a databases performance are considered, the major bottlenecks and most impactful SQL statements are identified,  and the corrective actions for the major problems are determined.  The goal is this process is to have a high positive impact on the systems overall performance with a moderate amount of time,  invested by an analyst.  Included in the process are identifying the systems biggest problems, guidelines on tuning SQL statements and rules for creating indexes along with considerations for carefully making changes to a production system. 

2. Process History, Benefits and Limitations

---

This tuning process first started to be developed around 2000, in the years since has been refined and improved.  The drive behind the processes development was the need to enable people in an enterprise software technical support department to diagnose and resolve Oracle performance problems remotely.  In many, even most cases gaining the appropriate database login and network security access is time consuming process, difficult or in the case of government locations or defense contractors not feasible.  It’s hard to overstate the importance of an application’s performance to its success, this combined the needs to evaluate a large volume of systems per day with little delay between the data coming in and recommendations being made combined, and the absolute requirement to NOT to cause additional problems and also knowing that most recommendations would be made directly to a production system with little or no testing; required a process which was repeatable, fast scalable, accurate, and gave results that didn’t cause harm to the system in the process.  The guerilla Oracle tuning process is the result.  It has been taught to numerous people and applied to vast majority of Windchill customers around the world at one time or another.

The first version of the diagnostic SQL script which supports the process was developed initially for Oracle 8 and has been enhanced for each new Oracle release.  The script started development back when Oracle's stats pack was the only diagnostic output available, and not even close to being enough to diagnose a system.  Since then AWR & ADDM have been added, and both outputs fit into this process if their outputs are available; in particularly the AWR report covering a specific time period fits in well.  But an AWR report isn’t enough, the output from the SQL script is needed also.  It was the competing needs of quick accurate, remote access only, high stakes and the lack of available Oracle diagnostic output which led to the drive behind the script and acquiring the knowledge to interpret its output.  The result is the Oracle tuning process documented here.

1. The goals and benefits of the Guerilla Tuning Process are:

• Reduce the time it takes diagnose a problem.  The goal is to within a couple of minutes of reviewing the data to have a pretty good idea of the major problems are.  Drilling down into the specifics of each of the problems, and creating solutions takes longer.  But generally most systems can be evaluated and recommendations generated in under an hour by a trained analyst.

• Quickly create solutions to improve the performance of a system.  Generating a series of steps to be applied to the target system is the output of the process.  It is important the recommendations be both developed speedily and are not too complicated to be applied easily by someone with little database knowledge.  For example changing a table to be partitioned might be “the best” solution but it would be exceedingly rare for this to be recommendations both because of the complexity to initially create and on it's going maintenance costs.  It is better to work up to more involved and complicated solutions rather then immediately jumping to them.  Most recommendations involve changing init.ora parameters or adding indexes.  Other solutions can be recommended on sub-sequent passes at improving the system, bit keeping recommendations as simple as possible initially is preferred.

• A “check your analysis” step to ensure the solutions are addressing the biggest problems.  Understanding the databases “story” (discussed in a later post) allows an analyst to understand the major bottlenecks in a system and ensure that the solutions provided address these biggest problems creating a built in feedback loop between the wait events, segment statistics and the resource intensive SQL.  For example, if the system wait events indicate an I/O problem, the memory advisor views indicate a lack of buffer cache, the segment statistics will indicate that particular tables or indexes are experiencing a disproportionate amount of I/O.  With this information the analyst knows they are likely going to need to either give the system additional memory, provide tuning solutions for SQL involving the high I/O table, indexes or make a change involving the physical location of these segments to improve performance (there are several other solutions which could be pursued for this example too).  For a given problem there are a series of solution available.  Working from the easiest to implement   Identifying the scope of the problem(s) the database is experiencing is the first step then and ensuring that the solutions which are recommended for implemented are aimed at improving these problems.  This may sound obvious, but so much of the literature about Oracle performance involves, things which could improve performance but very little about when to recognize when that solution is the most appropriate one, and when it’s worthwhile to implement.  For example, it’s possible to look at a system and realize it’s disk I/O bound, but without knowing if there are memory bottlenecks or if there was a SQL statement or statements overwhelming the system and in need of tuning.  It’s not possible to determine if balancing the disk I/O would be a good solution, although it is a solution which is found in the literature for disk I/O problems.  The guerilla tuning process generally aims to address the underperforming SQL first, then look to adjust init.ora parameters, and then lastly maybe look to adjust a physical aspect of the database.   In many cases however, adding indexes and changing a few init.ora parameters are done together.

• Requires Limited Analysis time.  The time required to analyze a report varies but generally the time required is somewhere 15 - 90 minutes range.  How long an analysis takes depends on a number of factors, including the skill of the analyst, the number of problems present and the severity of those problems.  Often counter-intuitively the more severe the problems are, the faster the analysis can be done.  This is because there is usually one overriding problem that needs to be addressed, often a severe lack of available memory or one particularly egregious SQL statement which needs to be tuned or re-written.  However, it is when the when a system is struggling as the result of a number of different problems, the analysis will take longer to complete as solutions for each of these problems need to be worked out.

2. Limitation of the Guerilla Tuning Process:

There are unfortunatly a few limitations of the process, but these are generally not serious and do not generally impose that many consrtaints on the end tuned system.

• May miss some problems first time through the report, although the process can be repeated.  However when the report output is combined with AWR reports from time intervals when there was a problem, problems will not go un-missed.  It is only when depending on the diagnostic report (gather info report) output that something may be missed.  This can happen if SQL statements which have caused a problem in the past are aged out of the shared pool and no longer in the V$ views there report is accessing.    However, based on experience it is unusual for the report.txt file to not have the most common and the biggest problems of the system contained in it.  Also, it’s possible for AWR reports covering narrow time intervals to miss the bigger problems the database is experiencing.

• Requires a higher level of skill by analyst.  The process can be done at different levels.  At one end it’s possible to bring relative newcomers to Oracle performance up to speed and being productive in a relatively short period of time.  However, to get the highest level of results from the analysis, it requires a higher level of skill and a great deal of practice.  There is a dedictated performance group in many regions and many of the members of these groups have looked at hundreds of reports if not thousands in some cases.  If you are having a critical or highly impactful performance problem opening a technical support case is something that should be done asap.

• Aims for acceptable solution.  In order to quickly and competently tune a system it’s necessary to not get stuck in ‘paralysis by analysis’, this is easy enough to do.  There need to be reasonable and defensible recommendations made based on an analysis; these recomendations must be reasonably expected to work and bring relief to the users and not cause harm or degreade performance.  Unfortunately, there will be situations where uncertainty exists and in those situations, making the minimum of number of changes and then re-evaluating is often the best course of action.  Things will be clearer with the second time through with the first set of recommendations in place, and what the next steps should be will be clearer.

The next post will discuss the processes guiding principles and how there is no such thing as a free change.