Art and Science of Oracle Performance Tuning

By Josephine Jenkins,2014-06-29 10:57
29 views 0
Art and Science of Oracle Performance Tuning

    Chapter 1: The Craft of Performance Tuning

    Database administrators perform a wide variety of taskssome exciting, and others more routine.

    On the down side, not too many DBAs get really excited about setting up backups, monitoring disk space, or checking to see if the exports ran okay last night. For most DBAs, these tasks are not especially challenging, nor do they call for much creativity.

    In stark contrast to these more mundane tasks, performance tuning is actually a very fascinating part of database work; however, many DBAs do not realize how rewarding it can be. This is unfortunate, because performance tuning is one of the DBA’s greatest challenges, with both tangible and intangible rewards. A truly competent performance-tuning specialist at times looks like a magician performing the impossible, or helping a client through some emergency. How many jobs provide the opportunity to be an instant “hero” who saves the day by fixing critical systems for a large corporation? Yet that is exactly what a skilled performance expert can accomplish.

    Let me share an example that illustrates the dynamics of performance tuning.

    Case Study # 1: Winning the Interview, Losing the Job

    Years ago, I went to an interview at a firm that creates public safety programs for

    police departments. The firm’s IT manager wanted to see how I would approach

    some difficult database issues, so he demonstrated some performance problems the

    firm was experiencing with its Oracle 7.3 databases. Specifically, he remarked that

    the time to establish a connection with Sql*Plus seemed to take a long time.

    As I listened to the manager, I recalled that the symptoms he described perfectly

    matched a well-documented “feature” with an Oracle utility called “Otrace.” With 1Otrace active, connections to Sql*plus will take longer and longer over a period of

    months. This happens because Otrace scans a special data file, which grows larger

    and larger with each new connection.

    The amazing thing about this problem was that I had just solved an identical Otrace

    problem the week beforehand! Not believing my good fortune, I asked the manager

    if this problem were real, or was this some type of “joke” to test me. Of course, it

    was not a joke at all, and the manager was very serious about solving the problem.

    After I double-checked the documentation on Otrace, I showed the manager how to

    disable it. He followed my recommendation, deactivating Otrace, and one of the

    firm’s biggest performance problems was solved during the interview! Needless to

    say, I felt pretty good about my showing in the interview.

    The Otrace performance problem was quickly solved, not because I was so smart, or knew some clever “tricks,” but because I was aware of some fundamental problems with the database, and

    recognized symptoms that matched a particular problem. In fact, that particular problem was

     - 1 - 6/29/2012

    Chapter 1: The Craft of Performance Tuning

    well documented with an Oracle “Alert.” Anyone who had read the alert and had a good understanding of database operations would have also found the root cause rapidly. Besides showing how exciting performance tuning can be, this real-life example also illustrates the importance of a thorough understanding of basic principles of database operation. Without a sound grasp of the fundamentals, the DBA must resort to guessing, or inappropriate measures such as trying to eke-out the highest possible “hit ratio,” or setting some init.ora parameters to ridiculously high values.

    The importance of understanding database fundamentals is the subject of our first “Magician’s


    Magician’s Trick #1

    Nothing can substitute for a solid understanding of

    database principles.

Throughout the book, we will be providing Magician’s Tricks that emphasize critical points in

    performance tuningpoints that are frequently overlooked or misunderstood. Some of these axioms might seem obvious at first, but many of these “common sense” tips are not so

    commonly practiced.

    Magician’s Trick #1 might appear to be obvious, but experience indicates the opposite. Perhaps it is just ignorance, but many analysts seem to dread the hard work necessary to truly understand how an Oracle database operates. Instead, these misguided analysts continually look for the “silver bullet” that will magically solve the tough performance problem.

    There is no Silver Bullet

    It is very common for administrators or managers to try to solve a thorny performance problem without attempting any real understanding of database principles! This scenario often manifests itself with some type of magic “tool” that will supposedly analyze the database and crank-out the

    solution. Trick #1 suggests that trying to use some sort of “tool” to solve performance problems will likely be futile, if the analyst lacks a solid grounding in how the database really works. It’s not that tools are designed badly; in fact, in the hands of a competent analyst, a tool can be

    very helpful. The point, rather, is that no tool can correct for a lack of understanding of the user. In other words, the wrong problem is being addressed. Instead of focusing on achieving true

    understanding that will lead to the best solution, the focus is shifted to a vain search for ways to avoid the labor that is required to achieve true understanding.

     - 2 - 6/29/2012

    Chapter 1: The Craft of Performance Tuning

    In our Otrace example, the firm with the performance problem had experienced the degradation for months. They were certainly not stupid people, but they simply did not have anyone who truly understood how an Oracle database worked. Imagine if that firm tried to avoid hiring a competent DBA, but insisted on trying to solve the performance problems via a “silver bullet” of some performance tool. This would have only led to frustration and embarrassment. In solving the Otrace problem, note that I did not waste time fooling around with the init.ora

    parameterssuch as doubling the database buffers, increasing shared pool size, or trying more parallel processes. Why not? Because the symptoms of the problem indicated that these steps would have been completely irrelevant to the problem at hand. Note that solving the problem involved no guesswork. Rather, a solution followed directly from listening to the exact symptoms.

    In future chapters we will discuss the performance tuning process in detail. For now, just remember that the first step for the DBA was to listen to the user as he described the problem.

    1; but Admittedly, it is a bit unusual to solve performance problems during the initial interviewthis scenario illustrates another important themethat is, the importance of the approach that the

    DBA uses to solve performance problems. This is the subject of another “Magician’s Trick:”

    Magician’s Trick #2

    The key to successful performance tuning is not

    technical sophistry, but rather, a sound approach

    based on good understanding of basic principles.

    In other words, the successful performance analyst is no smarter than other DBAs; he has just learned an overall process that has proven successful.

    The “Big” Questions

    Since you are reading this book, I assume you are interested in becoming an accomplished performance-tuning analyst. You know that there will be new things to learn and obstacles to overcome. In particular, you probably would like to know the answer to these questions:

    “Can I achieve remarkable performance tuning results?” And

    “Is performance tuning really that complex?”

     1 Since my experience with solving the Otrace problem during the interview, I give other DBAs the chance to do the same. When I interview prospective DBAs, I sometimes include a perplexing performance problem that I have been unable to solve. So far, no candidate has ever provided a quick solution, but I keep trying.

     - 3 - 6/29/2012

    Chapter 1: The Craft of Performance Tuning

Fortunately, the answers are:

    “Yes, you too can do these things!” and

    “No, it isn’t really that hard. Let me explain why.”

    Let me divulge a big “secret.” With a sound approach to performance tuning, many performance

    bottlenecks are actually very simple to correct. As we have seen with the Otrace problem, some

    performance tuning problems are incredibly simple to solve, using the right approach.

    Solutions to the vast majority of performance problems do not require consultation with Oracle Corporation on how to change some “undocumented” init.ora parameter that begins with a funny character. Just like the Otrace example at the start of this chapter, it is not unusual for a good performance-tuning expert to make just a few changes that lead to markedly improved performance.

    This path to tuning expertise is not based on clever equations, or “silver bullets,” but an understanding of what steps must be performed to succeed in the performance tuning process. From experience, I know that few performance problems are technically difficult to solve. With

    the proper approach and understanding, many performance problems are very simple to correct.

    The true way to become a performance-tuning expert is not through technical sophistication, but rather a good understanding of the overall approach. In future chapters we will address questions such as,

    ; How do I approach this problem?

    ; Where do I start?

    ; Is the information I have correct?”

    As we will see, truly challenging problems sometimes mean separating the real from the illusion, or even just correctly defining which problem should be solved. Plus, as always, there are many

    aspects of human interaction that should be considered.

    The More Oracle Changes . . .

    Faced with a bewildering assortment of performance problems, many DBAs are unsure of themselves when it comes to performance tuning. Without a proven approach, the whole process can be intimidating. After all, how can anyone keep up with all the new features? And what about all those cryptic “Sql hints”?

    The apparent obstacle posed by the enormous breadth of Oracle options is exploited by the marketers of some Sql performance tuning tools. In their marketing literature, they emphasize

     - 4 - 6/29/2012

    Chapter 1: The Craft of Performance Tuning

    the impossibility of the DBA fully understanding the many forms of Sql. Following this line of reasoning, the DBA must surely buy a tool (their tool) that can rapidly formulate and test the

    myriads of different Sql combinations.

    The huge flaw in this line of reasoning is the assumption that effective performance tuning requires that the DBA understand all the features available in the most recent database version. In truth, the DBA who has a good grasp of database principles can easily account for any new “horns and whistles” in the latest database release. As the first two Magician’s Tricks explained,

    sound understanding is the best long-term strategy to win in the game of performance tuning. Consider the game of chess. In a chess match, does a good player actually consider every

    possible move? Of course not. It is only the poorest of players (or the crudest of computer programs) that use that strategy. In reality, the master chess player knows the principles of the

    game, and only considers the options that show some promise, or are relevant. The performance DBA can use the same reasoning. He doesn’t need to try every option, because he knows that 99% of irrelevant. Experience shows that nearly all performance problems can be solved by applying sound principles of database operation. In other words, in spite of the bewildering set of new options that Oracle Corporation dazzles us with, there are some things that stay the same, even in a world of rapid database releases:

    Magician’s Trick #3

    The principles of sound performance tuning do not

    change from version to version.

    To illustrate Trick #3, consider how the Oracle optimizer joins two tables. The way of performing table joins has stayed nearly constant, even as database “horns and whistles” have been added. This means that, regardless of database version, the performance specialist must understand the difference between a nested loop and sort/merge join. Armed with this

    understanding, a large number of Sql performance problems can be solved, regardless of the exact database version.

    Of course, as technology advances, there are some changes that must be noted. In the case of table joins, for instance, the DBA should be aware of the “hash joins” method that is now often used. This addition is an easy adjustment for a DBA who already was well acquainted with the other two methods. The DBA who already possesses a good understanding of performance

     tuning principles will have little trouble assimilating additional features such as these.Art or Science?

     - 5 - 6/29/2012

    Chapter 1: The Craft of Performance Tuning

    Some specialists have argued that performance tuning is purely a sciencesimply the

    application of sound mathematics to a given problem. This notion is very appealing, because it suggests a nice, clean, deterministic way to solve any problem. Attractive as this idea is, does it really match reality?

    In reality, performance tuning is rarely 100% pure science. Rarely are performance problems handed to the DBA on an index card. In practice, performance problems take place in complex, even “whacky” organizations, filled with misinformation and human egos.

    Even when the root cause of a performance problem can be determined through a heuristic, the solution often calls for creative thinking. For instance, creative thinking (and tact) is often needed when the root cause of a performance difficulty is traced to the application. The

    designers may resist calls for changing their code, or argue that certain changes are too expensive, or simply not feasible. The professional performance expert cannot walk away at this point, but has an obligation to work with the designers to synthesize an acceptable solution. At this point, the DBA is more of an artist than a scientist.

    Thus, solving serious performance problems is not merely a mechanistic process, such as solving an equation in a school textbook. There are frequently other points to consider, such as determining what problem to solve, wading through misinformation, or dealing with the human factor. Of course, good analytic skills are necessary, but so are good creative skills. To ignore

    either aspect is to misunderstand how technical problems are solved in practice. In the next chapter, we will go “ backstage” to explore the steps followed by performance tuning experts. We will see that the ideal performance analyst is not just a scientist, but a man or woman who wears “many hats,” depending on the occasion.

     - 6 - 6/29/2012

Report this document

For any questions or suggestions please email