 Analyzing Database Performance with Oracle Statspack
Troy Campano
Without the proper tools, performance analysis and
tuning can be difficult. Luckily, Oracle provides a free command-line tool
called "Oracle Statspack" that will give you some insight into
how your Oracle database is performing. In this article, I'll
describe how to install, run, and report on performance within an Oracle
database using Statspack.
Installing Oracle Statspack
If you already have your Oracle database installed
then you are halfway done with your Statspack installation. The Statspack
installation files, which are really just a series of SQL scripts, are
already installed in your Oracle installation directories.
Where to Find the Installation Software
The installation files that you will need for
Statspack are located in the $ORACLE_HOME/rdbms/admin/ directory (where
$ORACLE_HOME points to your Oracle installation directory). In this
directory you should see a bunch of .sql scripts whose file names begin
with "sp". For example, you should see some files named:
spauto.sql, spcpkg.sql, spcreate.sql, as well as many more. You will need
to run these sql scripts when we are ready to install Statspack.
Creating a Tablespace/User to Store Statspack Data
The first thing we need to do is create a tablespace
for the Statspack data. Generally, people create the Statspack tablespace
with the name "PERFSTAT". This is because the name of the
Statspack user will be "PERFSTAT" as well. Here is the SQL for
creating the PERFSTAT tablespace:
SQL> CREATE TABLESPACE PERFSTAT
2 DATAFILE '/path/to/my/tablespaces/perfstat.dbf' \
SIZE 500M AUTOEXTEND OFF
3 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
4 LOGGING
5 ONLINE
6 SEGMENT SPACE MANAGEMENT AUTO
7 /
Tablespace created.
In the next step, we will be running the Statspack SQL scripts to install Statspack.
Installing Statspack in the Database
Now we are at the fun part. It is time to actually
install Statspack in the database. Open a command-line prompt and make sure
your $ORACLE_HOME environment variable is set to the correct place:
$> cd $ORACLE_HOME/rdbms/admin/
Make sure that the "sqlplus" executable is
in your path and log into the database as someone with the DBA privileges
and execute the spcreate.sql script. The SQL script shown in Listing 1 will
prompt you for a few questions like the password for the PERFSTAT user, the
name of the tablespace for the Statspack objects (in our case it's
"PERFSTAT"):
If all goes well, you should now have the Statspack
objects in your database within the PERFSTAT schema. You will want to make
sure you see the "No errors" message at the bottom of the
output from spcreate.sql to make sure you did not encounter any problems.
Taking Statspack Snapshots Manually
Now that you have Statspack installed, let's
take two snapshots and then generate a Statspack Report. Assuming you are
still in the $ORACLE_HOME/rdbms/admin/ directory and logged in as perfstat,
run this command twice like so:
SQL> execute statspack.snap
PL/SQL procedure successfully completed.
SQL> execute statspack.snap
PL/SQL procedure successfully completed.
To create a Statspack report, you need to give it two
Statspack snapshots to compare. Running the above command gives you the two
snapshots to compare. Now you can generate a Statspack report.
Generating a Statspack Report
Again, assuming you are still in the $ORACLE_HOME/rdbms/admin/
directory and logged in as perfstat, run the spreport.sql
script to create a Statspack report. It will show you a list of available
snapshots and their IDs (which will most likely be 1 and 2). The spreport
program will ask you for the two snapshot IDs that you want to compare and
the name you want to give your Statspack report (see Listing 2).
I cut out the bulk of the report data above but
don't fret; we'll look at the Statspack report output step by
step later in the article.
Automating Snapshots
Once you have Statspack up and running, you can
schedule it to run at an automatic interval. Again, assuming you are in the
ORACLE_HOME/rdbms/admin/ directory and logged in as perfstat, run the
"spauto.sql" script (Listing 3).
The "spauto.sql" script, by default,
schedules the Statspack job to take a snapshot every hour. If you want a
Statspack snapshot to be taken more often, make a copy of the spauto.sql
file and edit it. Change the section that says:
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;',
trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
commit;
end;
/
If you want the Statspack to run every 10 minutes,
then change it to say this:
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;',
trunc(sysdate+1/144,'MI'), 'trunc(SYSDATE+1/144,''MI'')', TRUE, :instno);
commit;
end;
/
Issue your copy of the spauto.sql script, and it will
schedule a snapshot to be taken every 10 minutes.
Note that your job_queue_processes parameter in the
instance must be set greater than one. Otherwise, when you schedule your
job, it will never actually execute:
SQL> set linesize 150;
SQL> show parameter job_queue_processes
NAME TYPE VALUE
----------------------------- --------------------------- -----
job_queue_processes integer 0
Here I don't have job_queue_processes set above
zero, so I issue an "ALTER SYSTEM" command to increase it to 2:
SQL> ALTER SYSTEM SET job_queue_processes = 2 SCOPE=BOTH;
System altered.
SQL> SHOW PARAMETER job_queue_processes
NAME TYPE VALUE
----------------------------- --------------------------- -----
job_queue_processes integer 2
Uninstalling Statspack
Just in case you want to throw away your Statspack
installation and start over, you can run the uninstall script, which
includes getting rid of the perfstat user, tables, and procedures. To
uninstall Statspack from an Oracle database running Oracle 8.1.7 and above,
just run the ORACLE_HOME/rdbms/admin/spdrop.sql script. To uninstall
Statspack from an Oracle database running Oracle 8.1.6, run the
ORACLE_HOME/rdbms/admin/spdrp.sql.
Interpreting Your Statspack Report
Now that you have Statspack taking snapshots and you
are able to create reports, let's take a closer look at those reports
and see what each section means.
The Statspack Header
The beginning of the Statspack report displays some
basic information about your instance, including database name, instance
name, DB ID, version, host, and the start and end times of the snapshots
used in your report. Here is an example:
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
-------- ---------- ---------- -------- ----------- ------- ---------
ORCL 2586436430 ORCL 1 9.2.0.4.0 NO localhost
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- ------------
Begin Snap: 4873 13-Dec-05 05:00:05 110 37.4
End Snap: 4875 13-Dec-05 07:00:04 651 203.7
Elapsed: 119.98 (mins)
Cache Sizes
The next section, Cache Sizes, shows some of the
instance settings including: Buffer Cache (DB_CACHE_SIZE), Standard Block
Size (DB_BLOCK_SIZE), Shared Pool Size (SHARED_POOL_SIZE), and Log Buffer
(LOG_BUFFER). These are all instance parameters that you can modify in your
spfile/pfile. :
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 3,008M Std Block Size: 8K
Shared Pool Size: 1,920M Log Buffer: 10,240K
Load Profile
The "Load Profile" section shows you the
load on your instance per second and per transaction. You can compare this
section between two Statspack Reports to see how the load on your instance
is increasing or decreasing over time.
Redo Size & Block Changes Increase:
If you see an increase here, then more DML statements are taking place
(meaning your users are doing more INSERTs, UPDATEs, and DELETEs than
before):
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
-------------- ---------------
Redo size: 352,535.71 8,517.66
Logical reads: 202,403.30 4,890.29
Block changes: 2,713.47 65.56
Physical reads: 44.22 1.07
Physical writes: 27.46 0.66
User calls: 787.32 19.02
Parses: 301.40 7.28
Hard parses: 0.05 0.00
Sorts: 317.78 7.68
Logons: 0.10 0.00
Executes: 2,975.84 71.90
Transactions: 41.39
% Blocks changed per Read: 1.34 Recursive Call %: 87.43
Rollback per transaction %: 27.56 Rows per Sort: 7.22
Instance Efficiency Percentages
The "Instance Efficiency Percentages"
section is very useful. It gives you an overview of your instance health.
Any time you make instance parameter changes you should take a look to see
whether this affects your instance efficiency in any way. Here is a
description of some of the fields. (Note: as stated in the Statspack
report, your goal here is for these percentages to be as close to 100% as
possible.):
Buffer Nowait %: This is the percentage
of time that the instance made a call to get a buffer (all buffer types are
included here) and that buffer was made available immediately (meaning it
didn't have to wait for the buffer...hence "Buffer
Nowait").
Buffer Hit %: This means that when a
request for a buffer took place, the buffer was available in memory, and
physical disk I/O did not need to take place.
Library Hit %: If your Library Hit
percentage is low, it could mean that your shared pool size is too small or
that the bind variables are not being used (or at least being used
properly).
Execute to Parse %: Here is the formula
used to get this percentage:
round(100*(1-parsevalue/executevalue),2)
So, if you run some SQL, and it has to be parsed every
time you execute it (because no plan exists for this statement), then your
percentage would be 0%. The more times that your SQL statement can reuse an
existing plan, the higher the "Execute to Parse" ratio is.
One way to increase the parse ratio is to use
"bind variables". This allows the same plan to be used for
multiple SQL statements. The only thing that changes in the SQL is the
parameters used in your statement's WHERE clause. For Java/JDBC
Programmers, that means using PreparedStatements as opposed to regular
Statements:
Parse CPU to Parse Elapsd %: Generally,
this is a measure of how available your CPU cycles were for SQL parsing. If
this is low, you may see "latch free" as one of your top wait
events.
Redo NoWait %: You guessed it -- the
instance didn't have to wait to use the redo log if this is 100%.
In-memory Sort %: This means the instance
could do its sorts in memory as opposed to doing physical I/O (very good).
You don't want to be doing your sorts on disk, especially in an OLTP
system. Try increasing your SORT_AREA_SIZE or PGA_AGGREGATE_TARGET in your
spfile/pfile to see if that helps if your in-memory sorting is not between
95% and 100%.
Soft Parse %: This is an important one,
at least for OLTP systems. This means that your SQL is being reused. If
this is low (not between 95% and 100%), then make sure that you're
using bind variables in the application and that they're being used
properly.
Latch Hit %: This should be pretty close
to 100%; if it's not, check out what your top wait events are to try
to fix the problem (pay specific attention to "latch free"):
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.98 In-memory Sort %: 100.00
Library Hit %: 100.04 Soft Parse %: 99.98
Execute to Parse %: 89.87 Latch Hit %: 94.99
Parse CPU to Parse Elapsd %: 75.19 % Non-Parse CPU: 99.46
Top 5 Timed Events (Called "Top 5 Wait Events" in 8i)
This section is crucial in determining the performance
drains in your database. It will actually tell you the amount of time the
instance spent waiting. Here are some common reasons for high wait events:
DB file scattered read: This can be seen
fairly often. Usually, if this number is high, it means there are a lot of
full tablescans going on. This could be because you need indexes or the
indexes you do have are not being used.
DB file sequential read: This could
indicate poor joining orders in your SQL or waiting for writes to
"temp" space. It could mean that a lot of index reads/scans are
going on. Depending on the problem, it might help to tune
PGA_AGGREGATE_TARGET and/or DB_CACHE_SIZE.
CPU Time: This could be completely
normal. However, if this is your largest wait event, it could mean that you
have some CPU-intensive SQL going on. You may want to examine some of the
SQL further down in the Statspack report for SQL statements that have large
CPU Time.
SQL*Net more data to client: This means
the instance is sending a lot of data to the client. You can decrease this
time by having the client bring back less data. Maybe the application
doesn't need to bring back as much data as it does.
Logfile sync: A logfile sync happens each
time a commit takes place. If there are a lot of waits in this area then
you may want to examine your application to see whether you are committing
too frequently.
Logfile buffer space: This happens when
the instance is writing to the log buffer faster than the log writer
process can actually write it to the redo logs. You could try getting
faster disks, but you might first try increasing the size of your redo
logs; that could make a big difference (and doesn't cost much).
Logfile switch: This could mean that your
committed DML is waiting for a logfile switch to occur. Make sure the file
system where your archive logs reside is not getting full. Also, the DBWR
process may not be fast enough for your system, so you could add more DBWR
processes or make your redo logs larger:
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
------------------------------- ------------ ----------- --------
db file sequential read 187,787 906 88.60
SQL*Net more data to client 49,707 57 5.55
CPU time 54 5.33
log file parallel write 1,011 2 .22
latch free 6,226 2 .16
-------------------------------------------------------------------
The SQL Sections (Buffer Gets, Disk Reads, Executions,
and Parse Counts)
The following sections show you the Top SQL (or
"worst performing" SQL) grouped by four sections: Buffer Gets,
Disk Reads, Executions, and Parse Counts. Review the top SQL statements in
each of these sections to see whether they can be tuned better. These
sections are a great way to how many times the SQL is being executed, how
much CPU time is being used to execute them, and the total time for the
statement to execute:
SQL ordered by Parse Calls for DB: ORCL Instance: ORCL Snaps: 4873 -4875
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
144,300 144,300 6.65 4199666855
Module: JDBC Thin Client
select parameter, value from nls_session_parameters
Note that if you take the hash value for the SQL
statement, run the ORACLE_HOME/rdbms/admin/sprepsql.sql script, and enter
the hash value when it prompts you, it will pull up the Execution Plan for
that SQL statement. That is pretty cool!
Instance Activity Stats
This section may provide some insight into some
potential performance problems that were not as easily visible from
previous sections in the report. This section is also useful when comparing
Statspack reports from the same timeframes on different days.
Tablespace and Data File I/O Statistics
These sections help give you some visibility into I/O
rolled up to the tablespace level and I/O stats on your data files:
Tablespace IO Stats for DB: ORCL Instance: ORCL Snaps: 4873 -4875
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- --------- -------- -------- ------
UNDOTBS
146 0 5.8 1.0 117,119 16 50,681 1.3
APP1
19,395 3 10.5 1.0 32,613 5 1,886 2.8
INDEX1
36,919 5 0.7 6.3 977 0 526 5.0
APP2
6,969 1 11.7 1.0 13,559 2 2,513 2.5
SYSTEM
15,056 2 0.8 1.8 360 0 13 3.8
Conclusion
As you can see, Oracle Statspack can be a powerful
companion when it comes to analyzing and improving performance in your
Oracle database. It can allow you see some of the bottlenecks (like where
more memory needs to be allocated) and determine which SQL statements are
taking up more than their share of resources. I hope this article will
assist you in your future performance tuning adventures. Good luck!
Troy Campano is an Oracle DBA for Liberty Mutual, an
insurance company. Troy also develops Web sites and Web applications using
Java, PHP, ASP, Coldfusion, and others. In his free time he enjoys playing
piano, guitar, maintaining his technical website www.troygeek.com, and
spending time with his wife, Denise. He can be reached at: troycampano@yahoo.com.
|