Oracle Performance Tuning Part 4

Color profile: Generic CMYK printerORACLE
profile
Composite Default screen
Series / Oracle Performance Tuning 101 / Vaidyanatha & Kostelac Jr. / 3145-4 / Chapter 4
Blind Folio 4:99
CHAPTER
4
Application Tuning
Tracking Down Bad SQL
Copyright 2001 by The McGraw-Hill Companies. All rights reserved. Printed in the United States of America. Except as permitted under the
Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval
system, without the prior written permission of the publisher, with the exception that the program listings may be entered, stored, and executed in a
computer system, but they may not be reproduced for publication.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Screen displays of copyrighted Oracle software programs have been reproduced herein with the permission of Oracle Corporation and/or its affiliates.
Information has been obtained by Publisher from sources believed to be reliable. However, because of the possibility of human or mechanical error
by our sources, Publisher, or others, Publisher does not guarantee to the accuracy, adequacy, or completeness of any information included in this
work and is not responsible for any errors or omissions or the results obtained from the use of such information.
Oracle Corporation does not make any representations or warranties as to the accuracy, adequacy, or completeness of any information contained in
this Work, and is not responsible for any errors or omissions.
P:\010Comp\Oracle8\145-4\ch04.vp
Monday, May 14, 2001 11:03:28 AM

profile
100
Blind Folio 4:100
Oracle Performance Tuning 101
Myth & Folklore

If a job runs for eight hours, you really need to trace it for the entire duration to get
complete information on the badly performing SQL statements.
Fact
You do not need to wait eight hours to find out what is wrong with the job, especially if
the job is iterative in nature. A trace file with, say, an hour of information will provide
you plenty of insight into the culprit SQL statements that are causing the job to run that
long. If a job has 16 SQL statements in it, chances are that if you fix the top three or four
SQL statements, you probably would have fixed the performance problem of the entire
job. We have walked into performance troubleshooting engagements where the culprit
jobs allegedly ran for two and a half days. We would have been fired on the spot, if we
had even suggested that we had to wait for two and a half days before we could begin
suggesting corrective action for the performance problem. An hour or so does the trick
most of the time; some unique situations may need more time.
ans of Sherlock Holmes, get ready. We are going on an investigative

mission. Tracking down bad SQL that makes your applications run like
snails requires perseverance just like finding out the details in a crime
scene. And since you are the DBA, it is your job to get to the bottom
of those SQL statements that can potentially leave your system in a
crippled state and have you wondering why on earth you arent sipping mai tais on
an exotic beach in the Caribbean instead. Make no mistake, you need the right tools
in this effort, you need to understand how to use these tools, but most importantly
you need the right mindset. You need to look out for those clues that are particularly
elusive. Everything may look perfectly normal on the outside, but when the numbers
dont add up, you know there is something else lurking out there. Another important
decision you will have to make is whether you tune a query performing 1,000,000
logical I/Os that is run once a day or tune a query that performs one logical I/O,
but is run 1,000,000 times a day. Which one should you work on? Are there any
systemwide differences in tuning one versus the other? Your application, the
environment, the load on the system when the queries are run, and the time of day
when the queries are run will all provide you with the answers to the above questions.
The SQL Statement Tuning Process

Following are the steps in the SQL statement tuning process:
1. Ensure TIMED_STATISTICS is set to TRUE at the instance level (set it
permanently in the init.ora or set it temporarily by executing an alter
system command).
Monday, May 14, 2001 11:03:29 AM

profile
Blind Folio 4:101
Chapter 4:
Application TuningTracking Down Bad SQL
2. Ensure MAX_DUMP_FILE_SIZE is set high enough. This controls the size of

your trace file.
3. Determine the location pointed to by USER_DUMP_DEST, and make sure
there is plenty of free disk space. This is the home of your trace files.
4. Turn on SQL_TRACE for the session in question.
5. Run the application.
6. Locate the trace files.
7. Run tkprof (transient kernel profile) on the trace file that was located in
step 6 to generate a trace output file.
8. Study the trace output file.
9. Tune the most expensive SQL statements.
10. Repeat steps 4 through 9 until required performance goals are achieved.
How to Trace SQL?

Tracing SQL statements can be compared to tracing or tapping into a suspects telephone
call not only to get all the details about the conversations, but also to determine the
originating point of the call (probably to find out the accomplices to the crime).
NOTE
It is important to note here that running the
application with meaningful and relevant data is
essential to the information that will be collected by
the tracing facility. If the data retrieved or manipulated
is not realistic, the trace output will also not be
realistic. Also, if you are running the cost-based
optimizer, ensure the validity of your statistics. When
was the last time you analyzed tables and indexes?
How much data has been infused into your tables
since then? Are your statistics even valid today?
Further, ensure that all relevant init.ora parameters are
set. If you have configured Oracle in MTS mode,
ensure that your current connection (for SQL tracing)
is in the dedicated mode, so the trace output will not
be split across multiple files.
The tracing of SQL statements can be done in several ways. It basically involves
setting the SQL_TRACE parameter at the session level. But before you start tracing
Wednesday, July 11, 2001 3:50:43 PM
101

profile
102
Blind Folio 4:102
SQL statements, the following Oracle initialization parameters (if applicable) need
to be set or modified:
USER_DUMP_DEST = <$ORACLE_ADMIN>/<$ORACLE_SID>/udump
Use this setting for OFA compliance. This parameter cannot be changed
dynamically and any change will require you to bounce the database. Look up
its current value to determine where your trace files are currently located.
TIMED_STATISTICS = TRUE
It is better to set this permanently in the init.ora, unless your version of Oracle
suffers from some undocumented feature associated with setting this permanently.
As always, before modifying any initialization parameter, you will need to
determine that there are no bugs logged against this parameter for your version of
the database. TIMED_STATISTICS can be modified dynamically by issuing an alter
system or alter session command to set this at the system level or session level and
can be turned off later. This is an option if you have problems keeping this parameter
permanently set in the init.ora.
MAX_DUMP_FILE_SIZE = 1024
This parameter determines the maximum size of the trace files on your system.
For trace efforts that need larger files, you can modify this parameter at the session
level, using an alter session command and set it to UNLIMITED to avoid running the
risk of potentially truncating the trace file.
CAUTION
Do not set SQL_TRACE to TRUE in the init.ora, as
it will trace every SQL statement that is executed.
This will cause noticeable delays and will also fill
up the file system where USER_DUMP_DEST points
to, with potentially unnecessary trace files. We
recommend that you keep this set to FALSE in your
init.ora (default). This should be a last-resort option
only when you have determined that SQL_TRACE
cannot be set to TRUE from your application
environment or from another session.
You can turn on trace for your current session by executing the following
commands:
SQL*Plus: Release 8.1.5.0.0 - Production on Fri Nov 10 20:57:18 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:

profile
Blind Folio 4:103
Chapter 4:
Oracle8i Enterprise Edition Release 8.1.5.0.0 Production

With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 Production
SQL> alter session set timed_statistics=true /* Optional Only if
setting at the instance level permanently causes problems */;
Session altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> /* Execute your SQL statements */
SQL> alter session set timed_statistics=false /* Only if it were set
to true in the current session */;
Session altered.
SQL> alter session set sql_trace=false;
Session altered.
The problem with using this method prior to Oracle 7.2 was that you had
to wait until all of your SQL statements had completed before you could turn
SQL_TRACE off within that session. However, in Oracle 7.2 and up, a method has
been provided to turn trace off from another session. The recommended method
to turn off tracing is to set SQL_TRACE to FALSE, either within the session or from
another session. Trace should not be turned off by just killing sessions. Turning
trace off in that manner can cause the contents of trace files to be truncated and/or
to contain invalid information.
The following method illustrates how to turn on SQL_TRACE on someone elses
session. This is especially useful when you encounter a performance problem with
an application, which may or may not support turning on SQL_TRACE from within.
Further, this method also allows you to turn on and turn off SQL_TRACE at will, without
having to wait for the job to run to completion. If needed, set TIMED_STATISTICS to
TRUE. Here is how:
Connected to:
SQL> select Sid,Serial#
2
from V$SESSION
3
where Username = 'BENCHMARK' /* Determine the SID, SERIAL# of the
session that you are trying to trace from v$session /;
SID
SERIAL#
---------- ---------11
54
SQL> execute dbms_system.set_sql_trace_in_session('11','54',TRUE);
PL/SQL procedure successfully completed.
103

profile
104
Blind Folio 4:104
SQL> /* Wait for SQL statements to execute for a certain period */

SQL> execute dbms_system.set_sql_trace_in_session('11','54',FALSE);
PL/SQL procedure successfully completed.
VIP
Since the core of this book is wait-event based
tuning, it should be noted that writing the wait
events of a job to a trace file and then studying it
is a very powerful troubleshooting tool. This method
has been discussed in the section Trapping Wait
Events to a Trace File in the chapter The Method
Behind the Madness.
Where Is My Trace File

and How Do I Find It?
The trace file generated by one of the above methods can be found in the directory
location pointed to by the Oracle initialization parameter USER_DUMP_DEST. If
you are not sure where that destination is, you can execute the following query to
determine the destination:
Connected to:
SQL> select Value
2
from V$PARAMETER
3
where Name = 'user_dump_dest' /* Determine the destination of
USER_DUMP_DEST */;
VALUE
----------------------------------/u01/app/oracle/admin/prod815/udump
The trace files are named using the <$ORACLE_SID>_ora_<process ID of the

server process> format. The process ID mentioned here is the Spid column in
V$SESSION. The following query will assist you in determining the process ID of
the server connected by the user named ST001:
select
from
where
and
S.Username, P.Spid, S.Program

V$SESSION S, V$PROCESS P
S.PADDR = P.ADDR
S.Username = 'ST001';
Monday, May 14, 2001 11:03:30 AM

profile
Blind Folio 4:105
Chapter 4:
If you are unable to determine the server/session process ID using the above
method (because there are multiple sessions including yours logged in using the
same username), there is another way to determine your trace file, if you are
running Oracle on UNIX. This method is not that elegant and is relevant only for
applications (user processes) that are launched from the server machine where the
Oracle database is running. Further, only applications that support the host command
are relevant to this discussion. The following steps will facilitate in identifying your
trace file. This method is not relevant for trace file identification on Windows NT.
SQL> ! /* Host out of SQL*Plus, Do not exit */
$ps
/* Determine the processes within your current shell. This is
to determine the process ID of your SQL*Plus session */
1262 pts/2
0:00 tcsh
1279 pts/2
0:03 sqlplus
1327 pts/2
0:00 ksh
$ps -ef | grep 1279 /* Now scan all the processes on the system and
filter out the ones with your SQL*Plus's process ID. This is to
determine the process ID of the server process that this SQL*Plus
session is attached to */
oracle 1280 1279 0 21:41:00 ?
0:12 oracleprod815
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 1279 1262 0 21:40:59 pts/2
0:03 sqlplus
oracle 1327 1279 0 22:38:57 pts/2
0:00 /bin/ksh
$cd /u01/app/oracle/admin/prod815/udump /* Change directory to the
USER_DUMP_DEST directory */
$ls lt | grep 1280
total 816
-rw-r----1 oracle
dba
408548 Nov 10 21:04 prod815_ora_1280.trc
Running tkprof on the Trace Files

The next step in the SQL tuning process is to run the utility tkprof that analyzes and
dissects trace files to provide readable trace output that is meaningful to an Oracle
DBA. To learn more about tkprof, you can execute the command without any
arguments and it will list the syntax and all of the sort options for trace output
display. Here is how:
C:\tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
105

profile
106
Blind Folio 4:106
table=schema.tablename
Use 'schema.tablename' with 'explain=' option.
explain=user/password
Connect to ORACLE and issue EXPLAIN PLAIN.
print=integer
List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no
TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
sort=option
Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
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 number 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
As you can see, there are plenty of options available for you to sort the trace
output data, and it can be done by simply specifying the sort=<option> in the
command-line syntax. The default sort option for the trace output from tkprof is
fchelaelapsed time fetching. Needless to say, trace outputs are sorted in decreasing
or descending order of values for a given option.
If you want to see the execution plan displayed as part of the trace output, you
need to ensure that the userid that you specify to run the Explain Plan in tkprof
owns the PLAN_TABLE under its schema. In the absence of the PLAN_TABLE in the
users schema, you will need to run the script utlxplan.sql, which is located under
the $ORACLE_HOME/rdbms/admin directory as the user, before running tkprof.
Following is an example run of tkprof that interprets the trace data collected in
the file named prod815_ora_1280.trc. It also creates a trace output file called
output.prf, utilizes a user called benchmark with a password of benchmark, does
Monday, May 14, 2001 11:03:31 AM

profile
Blind Folio 4:107
Chapter 4:
not show output of SQL statements run as user SYS, and sorts the output in
decreasing order of number of disk reads during the fetch phase.
$ tkprof
prod815_ora_1280.trc output.prf explain=benchmark/benchmark sys=no sort=fchdsk
Some of the more preferred sort options are prsela, exeela, and fchela, as elapsed
time is the actual time that it takes to run a given phase of SQL statement processing. We
personally have used fchela quite a bit, as the parse and execute phases of most SQL
statements are not usually the problem areas. Most of the work for a select statement is
done in the fetch phase, which is why fchela is more relevant for select statements. For
SQL statements that modify data (INSERT, UPDATE, DELETE), exeela is a more viable
option. If you need your statements ordered by total CPU utilization, use the sort option
of sort=(prsela, exeela, fchela). Similar output can be acquired for total physical I/O (I/O
operations that do not find the required Oracle blocks in the database buffer cache) by
using sort=(prsdsk, exedsk, fchdsk).
Total logical I/O (I/O operations that find the required Oracle blocks in the
database buffer cache) can be acquired by setting sort=(prsqry, prscu, exeqry,
execu, fchqry, fchcu). It is important to understand here that sorting by logical I/O
is a more consistent method, as physical I/O can change due to various reasons
such as an increase in the size of the database buffer cache. The perspective on
logical I/O provides a relative comparison of how much CPU time will be saved,
given that logical I/O (buffer cache manipulation) consumes CPU cycles.
NOTE
Since you are the DBA, you should be able to log
in as the oracle operating system user to run tkprof
and get the required access to the trace files. But if
tkprof needs to be run by some of your application
developers (who obviously should not be logging in
as oracle), you will need to set the init.ora parameter
_TRACE_FILES_PUBLIC=TRUE to provide them with
read access to the trace files.
Interpreting the Output from tkprof

Let us now look at a sample output from tkprof and understand the various
components. The following output is formatted for readability:
select
from
where
and
order
A.Id,A.Name,B.Name
AUTHOR A, BOOK B
A.Author_Id = B.Book_Author_Id
A.Author_Id = 101
by B.Name;
Monday, May 14, 2001 11:03:32 AM
107

profile
108
Blind Folio 4:108
call
-----Parse
Execute
Fetch
-----Totals
------
count
----1
1
27
------29
-------
cpu
---0.02
0.01
0.24
---0.27
----
elapsed
------0.02
0.01
0.36
------0.39
-------
disk query
---- ----0
0
0
0
1230 2342
---- ---1230 2342
---- ----
current
------0
0
0
------0
-------
rows
---0
0
399
---399
----
Now, let us understand the various columns in the tkprof trace output:
Call A phase in SQL statement processing (the define and bind phases are
also included in the Parse phase).
Count
CPU
Elapsed CPU time (in seconds) plus any time spent by the operating
system performing context switches, servicing interrupts, responding to
signals, performing I/O, waiting on resources, and so on.
Disk The number of Oracle blocks read from disk (physical I/O) for a
given phase.
Query The number of Oracle blocks read from memory in consistent mode.
Current The number of Oracle blocks read from memory in current mode.
Rows The number of rows processed in each phase. You should really see
values for select statements in the Fetch phase and for insert/update/
operations in the execute phase.
The number of times (in seconds) a phase was called/executed.

Actual CPU time utilized in executing a phase.
That was cool, except that you really could not make out the real difference
between Query and Current. For all practical reasons, there is no real need to split
up the logical I/O into these two buckets. In our experience, we usually sum up
(Query plus Current) to arrive at the total logical I/O for the SQL statement.
As mentioned in the chapter Application TuningIssues that Concern a DBA,
the ultimate goal of any SQL statement is to return the data to the user with the least
utilization of system resources and in a reasonable timeframe. This means that some
operations may require more I/O versus others that may require more CPU. There is
no magic number for the percentage of physical I/O to logical I/O, as this is subject
to the nature and frequency of the operations (SQL) being tuned. The core goals to
keep in mind in your application-tuning efforts are response time and system
throughput. As mentioned before, you need to determine the wait events on your
system and tune your applications and your system accordingly. Ultimately, you are
the best judge as to the amount of resources a given SQL statement can and should
Monday, May 14, 2001 11:03:32 AM

profile
Blind Folio 4:109
Chapter 4:
consume, given its response time requirements, its run frequency, and the load that
is placed on the system.
If some SQL statement performs 1,000,000 physical block I/Os to return 100
rows, you should definitely question that and determine the cause. Something does
not add up here. On the same note, you should also question an inordinate number
of logical I/Os in an operation. And just because the textbook definition of logical
I/O defines the operation to be at least 1,000 times faster than physical I/O, that
does not immediately imply that logical I/Os are always better than physical I/Os
(from Oracles perspective).
The key factor to consider here is the number of blocks your application is
visiting to return the data to the user. Another factor is the elapsed time to return
a set of rows to the user, and this also provides some basis for the impact of your
tuning efforts. You need to determine whether the number makes sense. Also
remember that it is not always beneficial to use an index. If you determine one or
more anomalies in your SQL statement from the above trace output, you will need
to rewrite your SQL statement to use more optimal methods and lesser resources.
Hey, OracleWhat Is Your

Plan of Action (P.O.A.)?
By Oracles P.O.A, we by no means are referring to Oracle Corporations plan of
action. We would like to clarify, for the record, that we are not privy to that kind of
information. What we are referring to is the P.O.A of a SQL statements execution.
The Explain Plan of a SQL statement can be compared to a normal question that we
ask one another in our daily lives, when we are not sure about the other persons
plans or agenda: What is your P.O.A.? The same holds true for Oracle.
How to Get Oracles P.O.A.?

One way to get it (and it is easy) is to specify the explain=userid/password option
in the tkprof command-line syntax. By doing that, you are requesting the Explain
Plan for the SQL statements that you trace, so that you can verify whether Oracle
is executing the SQL statements in a manner that makes performance sense. The
Explain Plan then shows up in the trace output for each SQL statement (except
recursive SQL and DDL SQL, which cannot be explained). When requesting that,
tkprof uses the PLAN_TABLE in the users schema for storing and then reporting the
execution plan.
Here is a sample Explain Plan:
select
from
where
and
order
A.Id,A.Name,B.Name
AUTHOR A, BOOK B
A.Author_Id = B.Book_Author_Id
A.Author_Id = 101
by B.Name;
Monday, May 14, 2001 11:03:32 AM
109

profile
110
Blind Folio 4:110
Query Plan
------------------------------------------------------------------1.0 SELECT STATEMENT Statement1 Cost = 148
2.1 SORT ORDER BY (7th)
3.1 FILTER (6th)
4.1 NESTED LOOPS (5th)
5.1 TABLE ACCESS BY ROWID AUTHOR (2nd)
6.1 INDEX UNIQUE SCAN AUTHOR_ID UNIQUE (1st)
5.2 TABLE ACCESS BY ROWID BOOK (4th)
6.2 INDEX RANGE SCAN BOOK_AUTH_ID NON-UNIQUE (3rd)
Another method is to provide a SQL to an Explain Plan command. Here we go:

explain plan for
select A.Id,A.Name,B.Name
from AUTHOR A, BOOK B
where A.Author_Id = B.Book_Author_Id
and A.Author_Id = 101
order by B.Name;
You can then run the following command to retrieve the Explain Plan from the
PLAN_TABLE:
select lpad(' ', 2*(Level 1))||Operation||' '||
decode(Id, 0, 'Cost = '||position) "Operation",
Options, Object_Name
from PLAN_TABLE
start with Id = 0
connect by prior Id = Parent_Id
order by Id;
NOTE
Remember to truncate your PLAN_TABLE
frequently, to prevent it from consuming
unnecessary space in your database (especially
true for third-party tuning tools).
Now that I Have the Plan, Can

Someone Help Me Read It?
The explain plan described in the previous section needs to be read in a tree-like
format by recursively going to the deepest level and then walking back to the
parent (first) level of the tree. In this case, the first occurrence of the deepest level
we encounter is 6.1, which is a unique scan on the AUTHOR_ID index to look
up data from the AUTHOR table. The results from step 6.1 are sent back to its
parent, 5.1.
Monday, May 14, 2001 11:03:33 AM

profile
Blind Folio 4:111
Chapter 4:
Now we check whether the level 5 has any other siblings and in this case there is
one 5.2. The level 5.2 has a child 6.2, which is the second operation that is
executed and is a RANGE SCAN on the BOOK_AUTH_ID index (mind you, this is
a non-unique index) to look up data from the BOOK table. The results from 6.2 are
sent back to its parent, 5.2.
There are no more siblings for level 5, so the combined results of 5.1 and 5.2
are sent back to its parent 4.1 (which performs the nested loops operation). The
results of step 4.1 are sent to its parent 3.1, which is the filter (for the value 101).
The results of 3.1 are then sent to its parent 2.1, which performs the ORDER BY
operation, and the results of that are then sent to the highest-level parent 1.0,
which then returns the data to the application. Now, wasnt that simple!
NOTE
Do not bet your life on the Cost=X number, as
it may not make sense in certain cases. The Cost=X
value is a measure of the amount of I/O the SQL
statement is expected to perform. Although
we can safely mention here that a Cost=1000000
is definitely a more expensive execution plan
(and hence may take much more time to execute)
than a Cost=4567, the same cannot be assumed
for all cases. For example, we have observed SQL
statements with, say, a Cost=4500 that are not
necessarily faster than the one with a Cost=4567.
There have been many occasions where we
have observed higher cost for execution plans for
SQL statements that ran the SQL much faster. You
may also notice an increase in cost when you /*+
HINT*/ SQL statements, and that is not necessarily
a bad thing.
What Is AUTOTRACE?
AUTOTRACE is quite possibly one of SQL*Pluss best kept secrets. This facility was
made available in SQL*Plus 2.3, and it provides some great information at your
fingertips without having to run SQL_TRACE, process the trace file through tkprof,
clean up and manage the trace files, and perform other administrative overhead
associated with those operations. It is very easy to set up the AUTOTRACE facility,
and even easier to use it.
AUTOTRACE can be set up by running the plustrce.sql script, which is located
under $ORACLE_HOME/sqlplus/admin. On releases of Oracle prior to 8.1.0 on the
Windows NT platform, you will have to look into the plusXX directory for this script.
Monday, May 14, 2001 11:03:33 AM
111

profile
112
Blind Folio 4:112
In some releases the plustrce.sql file has been found in the $ORACLE_HOME/dbs
directory (to our surprise).
To set up a user for AUTOTRACE, just log in as SYS and run the aforementioned
script. It creates among other objects a role called PLUSTRACE. When that script is
done, all you have to do is to grant the PLUSTRACE role to the relevant users, and
that gets them ready to use AUTOTRACE.
NOTE
Since AUTOTRACE automatically generates the
Explain Plan for one or more SQL statements for a
given user, the users schema should already have
the PLAN_TABLE created before attempting to use
AUTOTRACE. If not, please run the utlxplan.sql
script, which is located in the $ORACLE_HOME/
rdbms/admin directory, as the user running
AUTOTRACE.
Although the default method to use AUTOTRACE is to set autotrace on, that
may not always be feasible, especially if your query is returning hundreds of
thousands of rows. The traceonly option allows you to just look at the statistics,
without the data from the query.
TIP
The traceonly option can also be used as a very
quick method to get the Explain Plan for the SQL
statement. This is true when AUTOTRACE is turned
on from SQL*Pluss Windows version 2.3.x on
a client machine, and as soon as the Query is
Executing dialog box appears, you can click
the Cancel button to just get the Explain Plan.
The following is a sample run of AUTOTRACE:
SQL> set autotrace traceonly
SQL> select count(*) from TEST_OBJECTS;
Execution Plan
------------------------------------------------------------------0
1
2
0
1
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)

SORT (AGGREGATE)
TABLE ACCESS (FULL) OF 'TEST_OBJECTS' (Cost=1 Card=1)

profile
Blind Folio 4:113
Chapter 4:
Statistics
------------------------------------------------------------------28 recursive calls
16 db block gets
2 consistent gets
0 physical reads
0 redo size
1083 bytes sent via SQL*Net to client
669 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
As you can observe from this output, AUTOTRACE provides a wealth of

information, including the number of recursive calls, total logical I/O for the SQL
statement, physical I/O, the amount of redo generated (if applicable), SQL*Net traffic
information, sort statistics (memory versus disk), and the number of rows retrieved.
NOTE
As mentioned at the beginning of this chapter, you
need to prioritize your tuning efforts based on the
amount of resources consumed along with the
execution frequency of SQL statements on your
system. For example, in a 24-hour time period,
if 100 SQL statements contributed to 2,000,000
logical I/Os, and if one query performed 1,000,000
logical I/Os during a single execution, you would
need to focus on this query. Why? It comprised 1
percent of the workload yet consumed 50 percent
of the resources, thats why. This information can
be obtained by querying V$SQL for Buffer_Gets
and Executions.
In a Nutshell
Hunting down bad SQL statements requires discipline and pushes you to be aware
and adept in the various tools that Oracle provides to troubleshoot bad SQL statements.
This is important because unless you get to the bottom of the performance problem
with your SQL statements, you really cant tune your system. Allow us to say it one
more time: 80 percent or more of your systems performance problems are caused
due to bad SQL statements.
Monday, May 14, 2001 11:03:34 AM
113

profile
114
Blind Folio 4:114
The core steps to tune your SQL statements are:

1. Ensure TIMED_STATISTICS is set to TRUE at the instance level (set it
permanently in the init.ora or set it temporarily by executing an alter
system command).
2. Ensure MAX_DUMP_FILE_SIZE is set high enough. This controls the size of
your trace file.
3. Determine the location pointed to by USER_DUMP_DEST, and make sure
there is plenty of free disk space. This is the home of your trace files.
4. Turn on SQL_TRACE for the session in question.
5. Run the application.
6. Locate the trace files.
7. Run tkprof (transient kernel profile) on the trace file that was located in
step 6 to generate a trace output file.
8. Study the trace output file.
9. Tune the most expensive SQL statements.
10. Repeat steps 4 through 9 until required performance goals are achieved.
SQL_TRACE, TKPROF, EXPLAIN PLAN, and AUTOTRACE are some of the core
tools that are shipped with the Oracle database software, and have been known
to work consistently across multiple releases and operating system platforms.
Understanding these tools is essential to your success in your application tuning
efforts. Regardless of whom you choose as your third-party vendor for your Oracle
database performance monitoring tools and SQL tuning tools, you absolutely have to
understand and know your way around these core tools. Failing to do that may cause
Mr. Murphy to play tricks with you. There just may be that one day on the horizon
where you may not have access to your fancy GUI tools, but will need to determine
the core cause of an application performance problem.
Not being able to use these core tools provided by Oracle can put you and your
system in a wait mode (waiting to get to your GUI tools), which your system and
your business may not be able to afford. It is not only a requirement for a DBA to
know how to use these tools, but it also serves as an eye opener to understand what
your fancy GUI tools are actually doing behind the scenes. Now, just go and track
those bad SQL down, will you?
1002
O R I G I N A L AU T H E N T I C
O N LY F R O M O S B O R N E
Expert authors, cutting-edge coverage, the latest

releasesfind it all at OraclePressBooks.com
From a full selection of titles focusing
on Oracles core database products to
our in-depth coverage of emerging
applications, Web development tools,
and e-Business initiatives, Oracle Press
continues to provide essential resources
for every Oracle professional. For a
Online, you'll find:
complete list of Oracle Press titles

from the exclusive publishers of
FREE code for selected books

Oracle Press booksand other valuable
FREE sample chapters

Complete online Oracle Press catalog
resources, go to
OraclePressBooks.com.
Details on new and upcoming

Oracle Press titles
Special offers and discounts
Get the most complete information on
Enter-to-win contests
Oracle's #1 line of e-Business and database
News and press information
technologies at OraclePressBooks.com
Information about our expert authors

Details on how to write for Oracle Press
And much more!
O R A C L E P R E S S E X C L U S I V E LY F R O M M c G R AW- H I L L / O S B O R N E
TM

Oracle Performance Tuning Part 4

Uploaded by

Copyright:

Available Formats

Oracle Performance Tuning Part 4

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Oracle Performance Tuning Part 4

Uploaded by

Copyright:

Available Formats

Color profile: Generic CMYK printerORACLE

Color profile: Generic CMYK printerORACLE

Oracle Performance Tuning 101

Myth & Folklore

ans of Sherlock Holmes, get ready. We are going on an investigative

The SQL Statement Tuning Process

Color profile: Generic CMYK printerORACLE

Application TuningTracking Down Bad SQL

2. Ensure MAX_DUMP_FILE_SIZE is set high enough. This controls the size of

How to Trace SQL?

Color profile: Generic CMYK printerORACLE

Oracle Performance Tuning 101

Color profile: Generic CMYK printerORACLE

Application TuningTracking Down Bad SQL

Oracle8i Enterprise Edition Release 8.1.5.0.0 Production

Color profile: Generic CMYK printerORACLE

Oracle Performance Tuning 101

SQL> /* Wait for SQL statements to execute for a certain period */

Where Is My Trace File

The trace files are named using the <$ORACLE_SID>_ora_<process ID of the

S.Username, P.Spid, S.Program

Color profile: Generic CMYK printerORACLE

Application TuningTracking Down Bad SQL

408548 Nov 10 21:04 prod815_ora_1280.trc

Running tkprof on the Trace Files

Color profile: Generic CMYK printerORACLE

Oracle Performance Tuning 101

Color profile: Generic CMYK printerORACLE

Application TuningTracking Down Bad SQL

prod815_ora_1280.trc output.prf explain=benchmark/benchmark sys=no sort=fchdsk

Interpreting the Output from tkprof

Color profile: Generic CMYK printerORACLE

Oracle Performance Tuning 101

The number of times (in seconds) a phase was called/executed.

Color profile: Generic CMYK printerORACLE

Application TuningTracking Down Bad SQL

Hey, OracleWhat Is Your

How to Get Oracles P.O.A.?

Color profile: Generic CMYK printerORACLE

Oracle Performance Tuning 101

Another method is to provide a SQL to an Explain Plan command. Here we go:

Now that I Have the Plan, Can

Color profile: Generic CMYK printerORACLE

Application TuningTracking Down Bad SQL

Color profile: Generic CMYK printerORACLE

Oracle Performance Tuning 101

SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)

Color profile: Generic CMYK printerORACLE

Application TuningTracking Down Bad SQL

As you can observe from this output, AUTOTRACE provides a wealth of

Color profile: Generic CMYK printerORACLE

Oracle Performance Tuning 101

The core steps to tune your SQL statements are:

Expert authors, cutting-edge coverage, the latest

Online, you'll find:

complete list of Oracle Press titles

FREE code for selected books

FREE sample chapters

Details on new and upcoming