Evergreen Database Technologies, Inc.

SQL Tuning for DBAs: Reactive Performance Tuning

Stalking the wily Resource HogTim Gorman

Principal - Evergreen Database Technologies, Inc (http://www.evergreen-database.com)

Abstract

Every database administrator’s favorite way to start aday is to hear the cry:

The database is slow! Fix it!

It is as if people believe that an Oracle databaseadministrator has special init.ora parameters like:

_OVERCOME_POOR_DESIGN = TRUE

or:

_DISABLE_BAD_PERFORMANCE = TRUE

Most DBAs do not have a magic bullet. There aresome init.ora parameters that can be tweaked toincrease the size of different parts of the Oracleinstance’s System Global Area (SGA), such as theBuffer Cache, the Shared Pool, and the Redo LogBuffer. Making each of these data structures largercan improve system performance, due to the effects ofcaching I/O in shared memory. But there is quiteclearly a point of diminishing returns with any solutioninvolving caching, and at some point databaseperformance would still be poor even if the entiredatabase were cached in the SGA.

Every computer, no matter how large or small, hasonly so many operations that it can perform in acertain slice of time. In other words, no matter howmany MIPS (to randomly choose a common metric)are at your disposal, there is still a finite amount ofthem. If the application programs have not beenoptimised, then each program will consume anenormous number of MIPS, relative to what theyshould be consuming. However, large-scale computersystems are capable of performing such prodigiousquantities of work that, when a small number ofexcessively resource-intensive processes are executed,nobody notices that they were slow. If response time is2 seconds instead of 0.02 seconds, most human beingswill not find fault. This is the classic situationencountered during development; the quantity of datatested against is small and the overall load on thesystem is light or non-existent. As a result, the

operation does not appear to be slow or, if it does, itmay not be considered worth the effort to improve itsince this particular operation may not be executedoften.

However, everything changes when this code hits aheavily-loaded production system. All of a sudden,instead of running in an environment that had tons ofMIPS to spare, there is no longer any headroom. Thetask is now executing in an environment where anyresource-intensive process can overwhelm the scantremaining MIPS on a system.

In such a situation as this, system administrators anddatabase administrators should not be looking for waysto further increase the capacity of the machine, theoperating system, or the database instance. Takingthis approach is likely to yield only incrementalbenefits at best, since the problem is beingaccommodated instead of being fixed. It is not a lackof capacity that is causing the problem, rather it is aresource-intensive process, a resource hog, that isusing too many resources. Thus, increasing resourcecapacity only temporarily postpones the crisis. Justlike the use of appeasement in internationaldiplomacy, attempting to tune subsystems or thedatabase instance in an effort to increase capacity onlyencourages the beast to demand more.

Thus, it should be understood that systemadministrators and database administrators can onlydo so much to a system in order to correct intermittentperformance problems. If system capacity is notreasonable, then certainly it should be improved.Rather than looking for fault in the system or in thedatabase instance, critical eyes should first be turned tothe database application, to determine whether it issimply performing too much work in order toaccomplish it’s tasks.

What is reasonable system capacity? What is toomuch work? All of these phrases are subjective,dependent on your particular environment. I won’tattempt to quantify them here.

SQL Tuning For DBAs

Evergreen Database Technologies, Inc.

Rather, I will argue that if you are not currently usingtools, tips, and techniques similar to those described inthis paper, then there is a good chance that yoursystem is suffering extensive performance problemsdue to resource hogs. Your Oracle databaseapplication would be far better served by killing theminstead of searching for ways to accommodate them.

This paper provides a methodology for Oracledatabase administrators (DBAs) to detect suchresource hogs. Once they have been identified, thenthey must be prioritized so that scant resources are notwasted on operations that, although inefficient, are notdoing a great deal of damage to overall systemperformance. Another way to describe thisprioritization is triage, the medical term whereby themost seriously injured patients (who are not fatallywounded) are treated first.

Last, when only the worst few problems are identifiedand prioritized, then this paper will describe the tools,tips, and techniques for either fixing the SQLstatement outright or decisively determining how itshould be fixed.

What does the problem feel like?

When system performance problems occur, there areusually two different kinds of symptoms:

1. poor performance accompanied by surprisinglylow CPU consumption

2. poor performance accompanied by incredibly highCPU consumption

In the former case (#1), it is obvious that there is somekind of bottleneck which is strangling systemperformance. This bottleneck can be occurringanywhere: in the way the operating system allocateswork among multiple CPUs, in the I/O subsystem, inthe latches and locks that control access to SGA datastructures, in the design of the application where allactivity is being passed through a single sequentialprocess. Problems such as these in Oracle-basedsystems can be detected using the Session Waitinterface, which will not be covered by this paper.

In the latter case (#2), system performance problemsare undoubtedly due to the fact that the system issimply being hammered with more work than it canhandle at one time. It is being asked to do too manythings at once, and as a result, most processes arehaving to wait. As mentioned earlier, a commonresponse to this situation is to assume that nothing canbe done but to increase resource capacity, but more

impressive and permanent gains can be made byidentifying and eliminating the most resource-intensive processes, also known as resource hogs.Finding these processes in an Oracle-based systeminvolves using the SQL Area interface. This is theprimary focus of this paper.

Of course, in real life the problem is often acombination of these two scenarios:

3. poor performance accompanied by moderate tohigh CPU consumption

In this situation, CPU consumption may go throughpeaks and valleys. At times it is excessively high, andit is clear that the poor performance is due to that. Atother times, the CPU consumption drops to moderateor reasonable levels, yet overall performance does notimprove. Or, system administrators or DBAs maysimply feel that the system is not working too hard tojustify the poor performance. Sure, it is busy, but itdoesn’t seem that busy, in their subjective opinion.

It is still probably best to start the treatment of thisillness as if it were a resource issue (#2), not ablockage or bottleneck (#1), at least initially. Becausecertain low-level operations in the Oracle RDBMS aresingle-threaded (such as shared memory access whileupdating memory structures), the lack of CPUresources due to high consumption can exaggeratecontention to the point where it becomes a problem.Once the resource hogs are dealt with, it would thenbe more productive to check to see if the blockages orbottlenecks still exist.

All in all, unless you have a situation of poorperformance with very low CPU consumption, it isbest to start looking for resource hogs.

Looking for resource hogs

As documented in most tuning classes and books,there are several levels of tuning Oracle databaseapplications:

• business rules

• data model

• SQL statements

• database instance

• CPU, I/O, and memory subsystems

These are listed in order from the highest-level ofdesign to the lowest-level of technical detail.Conventional wisdom dictates that when you are

SQL Tuning For DBAs

Evergreen Database Technologies, Inc.

building a new application you start from the top-downand when you are tuning an existing productionapplication you start from the bottom-up.

In this paper, I would like to alter that tidy symmetrysomewhat, to assert that to tune an existing productionapplication you start in the middle, with the SQLstatements, not from the bottom-up. From a start inthe middle, you can then decisively determine whetherit is more appropriate to move upward (toward tuningthe data model or the business rules) or downward(toward tuning the database instance or the machine’ssubsystems) or to simply tune the SQL itself.

Why start with SQL statements?

Quite frankly, because it’s easy and because it’s thebasis for all processing in Oracle. The V$SQLAREAview contains information about SQL statementscurrently cached in the Shared Pool and can bequeried using a SQL statement from any utility. The“V$” views are all based upon the “X$” tables, whichare themselves based on internal memory structures inthe Oracle database instance’s SGA, not on tablesstored in datafiles. Thus, the information in “V$”views is initiated each time the database instance isstarted and is discarded when the instance isterminated. The V$SQLAREA view displays SQLstatements that are currently executing in the databaseinstance, as well as frequently used SQL statementsthat remain cached in the Shared Pool. Sincepractically all activity in Oracle (excepting certaindirect-path operations) is performed using SQLstatements, this is a wonderful central location formonitoring.

How to identify the “top 10” resource hogs

The V$SQLAREA view contains over two dozencolumns, including the following which are invaluablefor the kind of tuning we need to perform:

∗ SQL_TEXT

∗ SID

∗ BUFFER_GETS

∗ DISK_READS

The column SQL_TEXT contains the first 1000characters of the SQL statement itself; if the statementis longer than that, it can be found in it’s entirety inthe views V$SQLTEXT_WITH_NEWLINES orV$SQLTEXT. All these views are keyed on thecolumn SID, which is the Oracle session ID.

Each row in the V$SQLAREA view portrays a SQLstatement executing for an Oracle session. Thus, thereis a one-to-one relationship between rows in theV$SESSION view and the V$SQLAREA view.

The column BUFFER_GETS is the total number oftimes the SQL statement read a database block fromthe buffer cache in the SGA. Since almost every SQLoperation passes through the buffer cache, this valuerepresents the best metric for determining how muchwork is being performed. It is not perfect, as there aremany direct-read operations in Oracle that completelybypass the buffer cache. So, supplementing thisinformation, the column DISK_READS is the totalnumber times the SQL statement read database blocksfrom disk, either to satisfy a logical read or to satisfy adirect-read.

Thus, the formula:

(DISK_READS * 100) + BUFFER_GETS

is a very adequate metric of the amount of work beingperformed by a SQL statement. The weighting factorof 100 is completely arbitrary, but it reflects the factthat DISK_READS are inherently more expensivethan BUFFER_GETS to shared memory.

Querying the V$SQLAREA view and sorting theoutput by this formula is an easy way to allow the SQLstatements performing the largest amount of work tobe listed first. This query would look something like:

SELECT SQL_TEXT,

DISK_READS,

BUFFER_GETS,

SID,

HASH_VALUE

FROM V$SQLAREA

WHERE DISK_READS > 100000

AND BUFFER_GETS > 1000000

ORDER BY (DISK_READS * 100) + BUFFER_GETSDESC;

Oracle Consulting’s System Performance Group,established in the USA by Cary Millsap and CraigShallahammer, is largely responsible for popularizingthe use of this formula in the field as the basis foridentifying resource-intensive processes. The APS7(Application Performance Suite for Oracle7) toolkitincludes this query and the formula, which is referredto as a derived load factor.

The query’s WHERE clause reduces the amount ofdata returned by the query, so that inconsequential

SQL Tuning For DBAs

Evergreen Database Technologies, Inc.

SQL statements are not even displayed. Theseminimum values are, of course, subjective; yoursystem may require higher or lower thresholds in orderto allow this query to return only about 5-20 rows.

Having the query return 5-20 rows is important, as itenforces the idea of triage. When tuning a runningproduction system beset by resource hogs, it isimportant to remain focused on only the very worstcases. At most, you would only want to glance at themore than a handful of statements as a way ofchecking to see whether closely-related variations onthe top set of worst SQL statements are representednear the top. If several SQL statements are verysimilar, then they may be suffering from the sameproblem. If this is so, then it might be beneficial toconcentrate on this set, which cumulatively add up asserious problems even if the individual SQLstatements comprising the set do not.

Patterns to look for

• Rapidly descending load factor values This is aclear indication that the SQL statements detectedare indeed accounting for a significant percentageof the total amount of work being performed bythe Oracle database instance. If the first five SQLstatements listed by this query have a load factororders of magnitude higher than the next fifteenSQL statements, then it should be quite clear thatfixing then will have a dramatic impact on overalldatabase performance.

• DISK_READS close to or equal toBUFFER_GETS This indicates that most (if notall) of the gets or logical reads of database blocksare becoming physical reads against the diskdrives. This generally indicates a full-table scan,which is usually not desirable but which usuallycan be quite easy to fix.

Patterns to beware of

• Thresholds used in the WHERE clause of thequery need to be set to very low values What arevery low values? Of course, this is subjective, butfewer than 10,000 physical reads and 50,000logical reads is not particularly alarming for aproduction database. Of course, it depends on theapplication, but many run-of-the-mill SQLstatements can easily consume 10,000DISK_READS and not represent a problem of anykind. If the most expensive queries in the systemdo not exceed such low thresholds, then it couldmean that the database instance has simply not

been up and running for a long time yet. Or, itmay indicate that the size of the SQL Area in theShared Pool in the Oracle SGA is too small; as aresult, only currently executing SQL statementsare being stored in the Shared Pool, and all othersare being swapped out via the Shared Pool’s least-recently-used (LRU) algorithm. If you do notthink that the Shared Pool is too small, then itmight be that the culprit for resource consumptionmight not be an Oracle process at all. Carefullyexamine the operating system utilities todetermine whether a non-Oracle process isconsuming excessive amounts of system resources.Also, check the Oracle Session Wait interface (notcovered in this paper) to determine whether someform of contention is affecting performance insidethe Oracle processes, while a non-Oracle processis actually making CPU resources scarce.

• Load factor very similar for most of the top 5-20SQL statements This may be yet anotherindication that the Shared Pool may not be largeenough, as only currently executing SQLstatements are reporting statistics, and no SQLstatement is staying in the Shared Pool for verylong. Another explanation for this ambiguousresult is, again, that resource-intensive processesare not at the heart of the performance problems.Consider looking at operating-system utilities fornon-Oracle resource consumers or check theOracle Session Wait interface for contentionproblems (not covered in this paper).

• Bind variables not being used in SQL statements;lots of similar SQL statements varying only byhard-coded data values This is a classicpredicament, and indicates a problem that mayneed to be resolved before attempting to tune theSQL statements in question. If an application isnot using bind variables when it should, then eachuse of the SQL statement appears to the OracleRDBMS as a completely separate statement. As aresult, the caching mechanism of the SharedPool’s SQL Area is being entirely circumvented,causing massive amounts of resource contentionin the Shared Pool. When there are high levels ofconcurrently active users in this predicament (i.e.100-300 concurrently active users or more), thecontention resulting from not using bind variablescan be as detrimental as if the SQL statementswere not using indexes! Whenever this situationis detected in an online transaction processing(OLTP) environment, please consider the use of

SQL Tuning For DBAs

Evergreen Database Technologies, Inc.

bind variables to be as important as the use ofindexing.

Prioritization and triage

Based on the query of V$SQLAREA, it should be easyto identify a small number (3-5) of the very worst SQLstatements. Each such statement should be made intoa discrete task for a SQL tuning person. It isimportant to focus on this small number of tasks, fixthem, and then re-evaluate for the next “top 5” or “top10” all over again. Just like the medical technique oftriage, this is the fastest method of using a smallnumber of resources (i.e. SQL tuning people) toaddress a crisis in a complex environment. Focus ononly the very worst problems, fix them just enough sothat they are no longer a problem, but don’t lingerover them attempting to perfect them, and then re-assess.

Tools for tuning SQL statements

Quite briefly, the best tools are SQL trace andTKPROF with EXPLAIN PLAN.

EXPLAIN PLAN

The EXPLAIN PLAN utility is quite well documentedin the standard Oracle RDBMS documents Oracle7Server Tuning and Oracle8 Server Tuning, as well asadditional texts such as O’Reilly and Associate’s bookOracle Performance Tuning.

The EXPLAIN PLAN utility executes the OracleOptimizer against a SQL statement and inserts theexecution plan for the statement into another tableusually referred to as the plan table. The plan table is,by default, named PLAN_TABLE, and the script tocreate it can be found in a file named utlxplan.sql in asub-directory named rdbms/admin under theORACLE_HOME directory. On UNIX, the file mightbe named$ORACLE_HOME/rdbms/admin/utlxplan.sql and onWindows NT the file might be namedC:\ORANT\RDBMSnn\ADMIN\UTLXPLAN.SQL, wherenn is the version of the Oracle RDBMS, such as 73(for RDBMS 7.3).

EXPLAIN PLAN is designed to interpret exactly whatthe SQL statement will do, but it provides noinformation to help decide whether this execution planis a good idea, or not. As a result, EXPLAIN PLAN isvery limited as a tuning tool.

The SQL Trace utility

Another set of tools which addresses this deficiency isthe SQL Tracing utility, with it’s companion reportingformatter TKPROF.

The SQL Trace utility was originally written as adebugging facility for Oracle processes. Whenenabled, the Oracle server process creates a “flat file”in standard text, into which a record of all activity bythe Oracle process is placed. This trace file can growto be quite large, and it’s size is restricted by the valueof the MAX_DUMP_FILE_SIZE initializationparameter, set by the DBA. Trace files are located inthe operating-system directory indicated by theUSER_DUMP_DEST initialization parameter, and areusually named “sid_ora_nnnnn.trc”, where sid is theORACLE_SID string and nnnnn is the operating-system process ID of the Oracle server process. Eachentry in the trace file records a specific operationperformed while the Oracle server process isprocessing a SQL statement. These entries begin withthe following phrases:

PARSING IN CURSOR #nn

Parsing a SQL statement for cursornn; the full text of the SQLstatement is included in this entry

PARSE #nn:

Re-parsing the SQL statement forcursor nn.

EXEC #nn:

Executing the SQL statement forcursor nn. For DDL statements(such as CREATE, ALTER, andDROP) and DML statements (suchas INSERT, UPDATE, andDELETE), this is the final operationof the SQL statement.

FETCH #nn:

Fetching rows of data for the SQLstatement for cursor nn. This step isvalid only for queries (i.e. SELECTstatements).

WAIT #nn:

The SQL statement being executedby cursor nn is waiting for aparticular Oracle event to complete.This information mirrors the

SQL Tuning For DBAs

Evergreen Database Technologies, Inc.

V$SESSION_WAIT andV$SYSTEM_EVENT views.

BINDS #nn:

Displaying the data values passed tothe SQL statement for cursor nn.

PARSE ERROR #nn:

The following error was encounteredwhile parsing the SQL statement forcursor nn.

UNMAP #nn:

Freeing up any temporary structures(such as TEMPORARY segmentsused during sorting) used by theSQL statement for cursor nn.

STAT #nn:

Row count statistics for lines in theEXPLAIN PLAN associated with theSQL statement for cursor nn.

XCTEND #nn:

Indicates either a COMMIT orROLLBACK to end the transaction,associated with cursor nn.

For the purposes of performance tuning, the contentsof this trace file are too voluminous to be of any use.It is one of the ironies of the information age that asour abilities to provide information has skyrocketed,our abilities to understand and assimilate thatinformation has not changed very much. So, while theSQL Trace utility provides all the informationnecessary to understand how a SQL statement isworking in real-life, we use a report formatting utilitynamed TKPROF to make sense of it all.

Using the SQL Trace file just for debugging, nottuning…

One fact that is frequently overlooked: this trace filecan be used for application debugging. Rememberwhat information is stored here: a record of everyoperation performed by every SQL statement in anOracle session. The information is recorded in thesame order in which everything was executed.Information from recursive operations such as storedprocedure calls, packaged procedure calls, databasetrigger firings, and even internal Oracle recursive callsto the Oracle data dictionary are recorded.

This kind of information can be priceless if you aretrying to gain insight on exactly what is going onbehind the scenes. The problem is that theinformation in this file is very cryptic and concise.However, a great deal can be learned when youunderstand that much of the same information in thisfile shows up in the TKPROF report. Mapping dataitems in the raw SQL Trace file to data items on theTKPROF report is not difficult at all.

TKPROF report formatting utility

The TKPROF utility reads a SQL Trace trace file asinput and produces another “flat file” in standard textas output. It is a very simple utility, summarizing thetons of information provided in the trace file so that itcan be understood for performance tuning.

The problem with TKPROF is that people generally donot use it to it’s full advantage, and then assume (fromthe disappointing output) that both SQL Trace andTKPROF have little or no benefit.

TKPROF has only one mandatory command-lineparameter: the name of the SQL Trace trace file. Ifyou do not provide the name of a file into which thetuning report can be output, then TKPROF willprompt you interactively. Many people tend to provideonly this minimal amount of information.

The resulting report is only marginally useful. TheSQL statements and some of their accompanyingstatistics are duly output, but the order in which theSQL statements are printed in the report roughlymirrors the order in which they were executed. Thismeans that if an Oracle session executed dozens orhundreds of SQL statements, then it may be necessaryfor a person reading the report to wade through dozensor hundreds of pages in order to find a SQL statementwhose statistics show it to be a problem.

To combat this problem, the TKPROF utility has twocommand-line parameters, SORT and PRINT. TheSORT parameter allows you to specify the specificperformance statistics by which you would like theresulting reported sorted. There are twenty-threedifferent statistics by which TKPROF can sort; thesecan be displayed by running TKPROF withoutspecifying any command-line parameters. In UNIX,this would look as follows:

prscnt number of times parse was called

prscpu cpu time parsing

prsela elapsed time parsing

prsdsk number of disk reads during parse

SQL Tuning For DBAs

Evergreen Database Technologies, Inc.

prsqry number of buffers for consistent read during parse

prscu number of buffers for current read during parse

prsmis number of misses in library cache during parse

execnt number of execute was called

execpu cpu time spent executing

exeela elapsed time executing

exedsk number of disk reads during execute

exeqry nbr of buffers for consistent read during execute

execu number of buffers for current read during execute

exerow number of rows processed during execute

exemis number of library cache misses during execute

fchcnt number of times fetch was called

fchcpu cpu time spent fetching

fchela elapsed time fetching

fchdsk number of disk reads during fetch

fchqry number of buffers for consistent read during fetch

fchcu number of buffers for current read during fetch

fchrow number of rows fetched

userid userid of user that parsed the cursor

The most useful set of statistics pertain to logicalreads, which in the TKPROF reports are divided intotwo parts, consistent gets and current gets. Together,these two statistics represent logical reads, whichrepresent the number of accesses of database blocks inthe Oracle buffer cache in the SGA. Because this kindof activity is central to almost everything that Oracleprocesses do, logical reads represent an excellentmetric for performance tuning, representing raw work.A SQL statement that performs 1,000,000 logicalreads is certainly performing much more work thananother SQL statement performing 100 logical reads.What is useful about logical reads is that they are notaffected by the environment outside of the Oracledatabase instance. If a SQL statement requires1,000,000 logical reads on a busy UNIX machine, itwill still require 1,000,000 to perform that statementwhen the machine is lightly loaded or completelyunused otherwise. Thus, as a performance metric,logical reads are isolated from the environmentexternal to Oracle.

The same is true if the SQL statement is run againstan exactly identical database using the identicalversion of the Oracle RDBMS; 1,000,000 logicalreads will still be required. Thus, as a performancemetric, logical reads are portable across environments.

This combination of characteristics, isolated andportable, can not be said of any of the other two dozenperformance statistics by which TKPROF can sort.

In order to sort the TKPROF report by logical reads,use the following command-line parameter:

sort=exeqry,fchqry,execu,fchcu

Using this sorting criteria, the resulting TKPROFreport will display the most expensive SQL statementfirst, with less expensive SQL statements following indescending importance.

Now, reading the TKPROF report becomes mucheasier; the worst SQL statements “float” to the top.

However, the report may still be dozens or hundreds ofpages long. As you get used to using TKPROF, youwill undoubtedly notice that you will tend to only payattention to the first couple of SQL statements in thereport. This is, again, another form of triage; whybother with the SQL statements at the bottom of theTKPROF report that only use a couple of logical readseach?

To keep the report short, you can use TKPROF’scommand-line parameter PRINT, to which you specifythe number of SQL statements you wish to see in thereport. Used in conjunction with the SORT parameter,this will allow TKPROF to produce a report showingonly the very worst SQL statements from a session.For example:

sort=exeqry,fchqry,execu,fchcu print=10

will display the ten worst SQL statements shown inthe SQL Trace.

Last, be sure to use EXPLAIN PLAN with TKPROF,so that the execution plan (and the number of datarows processed at each step) can be displayed. Oneproblem here is that the TKPROF command-lineparameter to enable this requires an OracleUSERNAME and PASSWORD in clear text on theoperating-system command-line. The purpose is allowthe TKPROF utility to actually connect to the OracleRDBMS as the specified username and run theEXPLAIN PLAN utility.

Unfortunately, this can be a serious security breach, asthe password being passed in clear text on theoperating-system command-line provides anyone onthat system with opportunity to steal it. Please beaware of this fact, and use this option accordingly.

With the combination of run-time statistics as well asthe execution plan, SQL Trace and TKPROF with

SQL Tuning For DBAs

Evergreen Database Technologies, Inc.

EXPLAIN PLAN provide the decisive empirical datathat a SQL tuning person can use to determineconclusively whether a SQL statement is efficient ornot. Better yet, this information can be used tocompare one version of a statement against another,allowing a tuning person to determine whether oneexecution plan is superior to another.

This enables the testing of hypotheses, the ability totake a problem statement and baseline it. Startingwith this baseline, you can methodically test severaldifferent hypotheses. The execution plan informationallows you to verify whether what you have in mind istaking place. The run-time statistics (especially thelogical read statistics) provide conclusive empiricalproof of whether the hypothesis is an improvement ornot.

Enabling SQL Trace

There are three ways to enable the SQL Trace utility:

• for every Oracle process in a database instance

• within a single process, by that process

• within a single process, by another process

There are various reasons for using each method, asexplained below.

Enabling SQL Trace for an entire instance

This is done by setting the init.ora initializationparameter SQL_TRACE to the value of TRUE; thedefault value is FALSE. The database instance mustbe stopped and restarted to do this.

As a result, every single Oracle process, including thebackground processes such as SMON and PMON, willcreate a flat file in the file system. Backgroundprocess trace files will be created in the directoryspecified by the initialization parameterBACKGROUND_DUMP_DEST and all otherprocesses will create trace files in the directoryspecified by the initialization parameterUSER_DUMP_DEST. As you can imagine, in a busysystem this can result in quite a lot of disk space beingused up. Additionally, you can expect additional CPUconsumption as each Oracle process is burdened withthe additional overhead of recording every operationperformed to a trace file.

As a result, enabling SQL_TRACE in this manner issomething rarely done in production systems or busydevelopment or test systems.

Enabling SQL Trace for a single process, by thatsame process

This is the most common method of enabling SQLTrace, using the SQL command:

ALTER SESSION SET SQL_TRACE = TRUE

In interactive tools like SQL*Plus and WorkSheet, allyou need to do is enter the command as a SQLstatement.

In other utilities such as Oracle Forms or SQL*Forms,this may involve a command-line parameter, such as“-s”. Alternatively, tracing can be enabled byexecuting the ALTER SESSION command in a triggeror in a user-exit.

In the Oracle Precompilers, this command can beexecuted as:

EXEC SQL ALTER SESSION SET SQL_TRACE = TRUE

In Oracle Call interfaces (OCI), it is executed like anyother DDL command.

In PL/SQL, DDL commands such as ALTERSESSION cannot be used. The built-in packageDBMS_SESSION has a procedure namedSET_SQL_TRACE which takes a single boolean inputparameter of TRUE or FALSE.

When developing applications, it is wise to make surethat SQL Tracing can be enabled conditionally basedon a standard method, so that it can be enabled readilyunder production conditions.

For example, it is common to designate UNIXenvironment variables to convey information toprograms. If an environment variable namedPROG_SQLTRACE is given the value of TRUE, thenthe following code inside a PRO*C program could beused:

EXEC SQL CONNECT :username_password;

p_env = getenv(“PROG_SQLTRACE”);

if (p_env != 0)

{

if (strcmp(p_env, “TRUE”) == 0)

{

EXEC SQL ALTER SESSION SETSQL_TRACE = TRUE;

}

}

There are many ways to accomplish this; this is justone idea for one situation.

SQL Tuning For DBAs

Evergreen Database Technologies, Inc.

Enabling SQL Trace for a single process, byanother process

In many cases, code for enabling SQL Trace at run-time has not been embedded, so ordinarily it would notbe possible to use it.

But, there are two methods available for enabling SQLTrace on a running process from another process.

First, there is another procedure in theDBMS_SESSION package namedSET_SQL_TRACE_IN_SESSION, which takes threeinput parameters. The first two are SID andSERIAL#, which uniquely identify Oracle sessions.These two values comprise the primary key of theV$SESSION view. The third input parameter is theboolean value of TRUE or FALSE.

The other method is available only to DBAs. FromOracle RDBMS 7.3 and above, the Server Managerutility has a set of commands called ORADEBUG.Prior to 7.3, special-purpose utilities named oradbx(for UNIX) and orambx (for VMS) were required torun ORADEBUG commands.

In Server Manager, the following commands could beused to enable SQL Trace in the Oracle process whoseoperating-system process ID was nnnnn:

SVRMGR> oradebug setospid nnnnn

SVRMGR> oradebug event 10046 trace namecontext forever, level 0

When using oradbx or orambx, use:

oradbx> debug nnnnn

oradbx> event 10046 trace name contextforever, level 0

When enabling SQL Trace in this way, please beaware that the resulting trace file may not have caughteverything done by the process being traced. Theexplanation is simple: quite a lot may have happenedbefore you enabled tracing.

Summary

It is possible to identify the most resource-intensiveprocesses in an Oracle database instance, using theV$SQLAREA view.

It is then possible to tune those resource hogs usingSQL Trace and TKPROF. These tuning efforts mayresult in:

• the creation of indexes to better support the SQLstatement

• modifications to the text of the SQL statement toprovide a more efficient execution plan

• the realization that the SQL statement itselfcannot be tuned any better

Given such a realization as the third item, one is leftwith choices.

If the execution plan is the most efficient possible butperformance is not suitable, then either the data modeldoes not optimally support the SQL statement or theunderlying database instance or operating-systemneeds to be tuned.

In the former case, changes to the data model may inturn prompt examinations of the businessrequirements that underlie the SQL statement itself. Itis possible that business requirement may forcechanges to the data model, such as the creation ofsummary tables.

It is also possible that the SQL statement itself isinappropriate for satisfying the business requirement.This might result in the SQL statement being re-engineered or eliminated.

In the latter case, when it is realized that the SQLstatement is tuned efficiently and effectively uses thedata model while fulfilling it’s business requirement, itmight then become necessary to improve overalldatabase instance performance, or overall systemperformance. This may or may not involve increasingsystem resource capacity, such as additional CPUcapacity.

In closing, start with the SQL statements. If they areinefficient, all other tuning efforts are doomed to failor simply cost too much.

Finally, monitor for resource hogs frequently. Don’tlet them accumulate unnoticed, so they surprise you atmoments of crisis. This is as much a part of the DBAsjob as monitoring space and performing backups.Make sure that once they are identified, they getresolved; don’t toss them over the fence for someoneelse to clear up.

Good luck, and good hunting!