Review: Oracle Performance Tuning for 10gR2, Second Edition
January 24, 2007
Oracle Performance Tuning Tools
Within Oracle, performance tuning (sometimes referred to as performance and tuning) is by far one of the hardest subject areas to master. There are no shortcuts to being good at tuning an Oracle database, but there certainly are a lot of tools and advice covering the specifics of how, what, when and where. Why you should tune should be fairly obvious.
For many DBAs, an attempt to tune a database probably boils down to a comfort level, or a lack thereof. Many people have no compunction about digging into code and making changes to production code on the fly, and many of those same people also know that investigating Oracle internals is also necessary, but lack the confidence or knowledge to go that extra step. As an analogy, operating a database is akin to owning/operating a car. You can clean up and detail the exterior and passenger compartment to the nth degree, but if the engine doesnt work, or works poorly, then the flashy car isnt going anywhere soon or fast.
Armed with some fundamentals of car/database mechanics, you can diagnose and fix many problems. Your alternatives are to live with the problem or pay a lot of money to specialists/consultants. If all you needed to do was clean a battery post to make the engine start more reliably, and after seeing how simple it was to perform that routine maintenance, wouldnt you feel bad about spending the money and time to get that done? If all you needed to do was add an index to prevent a full table scan and reduce an hours-long running query down to a few minutes, wouldnt you feel bad about literally giving money to a well-dressed (at your expense) high-priced consultant?
Performance tuning boils down to four components: recognizing that there is a problem, knowing which tools are at your disposal, and knowing what to look for, and then fixing the problem. Recognizing the problem comes about from a reactive or proactive perspective. The tools are many, but which one is the right one to use? Were not going to know everything regarding what to look for, but how about being able to find the most common problems? Fixing the problem then becomes a matter of trying, and in many cases, applying some tried and true measures.
Gavin Powells Oracle Performance Tuning for 10gR2 does an excellent job in identifying tools and what to look for. The tools include the following:
No doubt youve seen many references to an explain plan, but what should you be looking for in a plan? Mastering the essentials or basics of what an explain provides goes a long ways towards identifying and solving problems. Powell lays out a list of red flags related to indexes and joins.
Full table scans and poor use of indexing, or lack of use of indexing
Many of the red flags are easy to understand, but some are probably obscure to many DBAs. What is an anti-join? Can you provide a definition of what a noncorrelated subquery semi-join is? Its okay if you cant answer these questions; after all, were not experts (yet), right? But at least were becoming familiar with the terminology. Powells book (chapter 12) provides the reader with the information necessary to use and implement a plan table (and with plenty of examples throughout the book).
Powell states that the explain plan (the resulting query plan it generates) can be used for verification and fine tuning, and that it is important to note that query plans do not tell you everything. If more information is needed, then a tracing tool should be used.
Before diving into more complex tools, determine what your tracing needs consist of. Powell categorizes tracing into six groups (with key parameter settings included):
timed_statistics = TRUE timed_os_statistics = 0 sql_trace = false
timed_statistics = TRUE timed_os_statistics = 5 sql_trace = false max_dump_file_size = 1M statistics_level = BASIC or TYPICAL
timed_statistics = TRUE timed_os_statistics = 5 sql_trace = TRUE max_dump_file_size = 1M statistics_level = TYPICAL
Too much tracing
timed_statistics = TRUE timed_os_statistics = 5 sql_trace = TRUE max_dump_file_size = 1M statistics_level = ALL
Oracle Support Tracing
trace_enabled = FALSE
Steps covered elsewhere, including Oracle documentation
Depending on your version of Oracle, you may have to enter 1M as 1024000 (i.e., use an integer value).
Do you have a system where disk space is constantly being consumed by trace files? Is trace_enabled enabled? Is this (below) an example of what youre seeing for number and sizes of trace files? This example is from one node of a three node RAC cluster. Note how many GB of disk space was consumed within the last hour.
Check the setting and disable it.
NAME TYPE VALUE ------------------------------------ ----------- --------- log_archive_trace integer 0 sql_trace boolean FALSE trace_enabled boolean TRUE tracefile_identifier string SQL > alter system set trace_enabled=false; System altered.
Powell lays out very clear examples of how to enable and use SQL Trace, with one of the steps being how to identify your trace file of interest. Going one step further, TKPROF is introduced as a means of formatting a trace file and making it more human readable. A 10g tool named trcsess can be used to combine multiple trace files into one. And as many users know, Autotrace within SQL*Plus is a handy way to get execution plan statistics.
Oracle provides many views into its internal workings, and performance views are just one of those mandatory items or subjects you need to be familiar with. Armed with some in depth knowledge of just a few views, you can expose quite a bit of information about whats going on (or not). Four views (v$sqlarea, v$sql, v$sqltext, and v$sql_plan) can help you determine problems related to these potential problem areas:
Powell provides examples of these factors to help identify bad (in terms of performance) SQL code.
This article serves as an introduction to a very good book on performance tuning. In the next article, well take a look at more examples within the book and go into more detail of what to look for and how to solve performance problems. Later on, well look at a book from Rampants Oracle In-Focus Series, Oracle Tuning, The Definitive Reference.Buy this book