Review: Oracle Performance Tuning for 10gR2, Second Edition

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 doesn’t work, or works poorly, then the flashy car isn’t 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, wouldn’t 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, wouldn’t 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? We’re 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.

Identifying Tools

Gavin Powell’s Oracle Performance Tuning for 10^gR2 does an excellent job in identifying tools and what to look for. The tools include the following:

• Explain plan
  
• SQL trace with TKPROF
  
• TRCSESS
  
• Autotrace in SQL*Plus
  
• Performance views

Explain Plan

No doubt you’ve 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

• Inappropriate use of indexing where full table scans can sometimes be faster
  
• Overindexing on individual tables or too many indexes in a database in general
  
• Large selections from large tables sometimes caused by filtering too late
  
• Unnecessary sorting

Joins

• Sort-merge where nested loops or hash joins can be used
  
• Cartesian joins, some outer joins, and anti-joins
  
• Highly complex mutable (multiple-table) joins
  
• Noncorrelated subquery semi-joins
  
• Using views, especially multiple layers of views calling subviews
  
• Inherently poorly performing steps like UNION and concatenation operations

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? It’s okay if you can’t answer these questions; after all, we’re not experts (yet), right? But at least we’re becoming familiar with the terminology. Powell’s 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.

Other tools

Before diving into more complex tools, determine what your tracing needs consist of. Powell categorizes tracing into six groups (with key parameter settings included):

No tracing

timed_statistics = TRUE

timed_os_statistics = 0

sql_trace = false

Some tracing

timed_statistics = TRUE

timed_os_statistics = 5

sql_trace = false

max_dump_file_size = 1M

statistics_level = BASIC or TYPICAL

SQL tracing

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

Session-Level Tracing

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 you’re 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.

Performance views

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 what’s 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:

• Executions
  
• Disk + Buffer Reads per Row
  
• Rows per Sort
  
• Rows per Fetch
  
• Parses per Execution
  
• Disk versus Logical Reads
  
• Optimizer Cost
  
• CPU Time
  
• Elapsed Time

Powell provides examples of these factors to help identify bad (in terms of performance) SQL code.

In Closing

This article serves as an introduction to a very good book on performance tuning. In the next article, we’ll 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, we’ll look at a book from Rampant’s Oracle In-Focus Series, Oracle Tuning, The Definitive Reference.

» See All Articles by Columnist Steve Callan