"If everything
seems under control, you're just not going fast enough." -
Mario Andretti |
Read
first then play the video:
PRF-VIDEO -Diagnostic
and Tuning Tools (STATSPACK)
Diagnostic and Tuning Tools
Introduction
Another one of your
responsibilities as a DBA is to use and read the STATSPACK tool in a
case of a performance problem. Your job"s responsibilities dictate
that you should be at least informed of the following basic
fundamental subjects:
Installing and Configuring
the STATSPACK
Running the SPCREATE script
Creating user PERFSTAT
Connecting to the PERFSTAT
user
Cleaning all the STATSPACK
table
Producing a performance
report
Taking SNAPSHOTS
Setting the
TIMED_STATISTICS parameter
Understanding of the X$,
and V$ tables
Using the V$FIXED_TABLE
Understanding the PERFSTAT
scripts:
SPCREATE.SQL
SPTRUNC.SQL
SPREPORT.SQL
Reading the output PERFSTAT
file sp_1_6
Commands:
SHO PARAMETER
TIMED_STATISTICS
ALTER SYSTEM SET
TIMED_STATISTICS=TRUE;
START %ORACLE_HOME%
START %ORACLE_HOME%
START %ORACLE_HOME%
EXECUTE statspack.snap;
Hands-on
In this exercise you will learn how to: install and configure the
STATSPACK, create user PERFSTAT, and take a snapshot. You also learn
more about the X$ and V$ along with the TIMED_STATISTICS parameter
value and more.
So, let's connect to SQLPlus as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA
Introduction
STATSPACK was created in
response to a need for more relevant and more extensive statistical
reporting beyond what was available via UTLBSTAT/UTLESTAT reports.
These statistics can be stored permanently in the database so that
historical data is available for comparison and diagnosis.
Before we install STATSPACK
utility, let us revisit the content of our repository database.
View X$ and V$ tables
Query the number of X$, and V$ tables you have.
X$ tables
The X$ tables have cryptic
names and should not be queried directly. You can use the
V$FIXED_TABLE view to query information about tables owned by the SYS
user. Normally, they are started with X$, X$_, V$, and V$_.
SQL> SELECT COUNT(1) FROM v$fixed_table
WHERE SUBSTR(name,1,2) = 'X$'
/
V$ views
The V$ views are based on
the X$ tables, which are actually memory structures that are populated
at instance startup and cleared at instance shutdown.
SQL> SELECT COUNT(1)
FROM v$fixed_table
WHERE SUBSTR(name,1,2) = 'V$'
/
View and Set
TIMED_STATISTICS parameter
Check the TIMED_STATISTICS
parameter value.
SQL> SHOW PARAMETER TIMED_STATISTICS
Set the TIMED_STATISTICS parameter to TRUE to collect timing
information in the V$ view.
SQL> ALTER SYSTEM SET TIMED_STATISTICS=TRUE;
Create PERFSTAT user and its objects
Create the PERFSTAT user
with its objects. Make the TOOLS tablespace as its default tablespace
and the TEMP tablespace as its temporary tablespace. Then, run the
following script. Make sure that you have at least approximately 75
Megabytes of disk space for the installation.
SQL> START %ORACLE_HOME%
From this script the PERFSTAT user and its schema (lots of
tables, views, synonyms, packages, etc) will be created. You will get
a list of online tablespaces in the database. You should decide which
tablespace you wish to create the STATSPACK tables and indexes. This
will also be the PERFSTAT user"s default tablespace. You should not
specify the SYSTEM tablespace. Specifying the SYSTEM tablespace will
result in the installation FAILING, as using SYSTEM for performance
data is not supported. Specify the TOOLS tablespace for PERFSTAT user"s
default tablespace or whatever you wish.
Running the script, you
will see some prompt messages to answer. The following are those
messages:
In the "Enter value for
default_tablespace:" prompt, type tools.
In the "Enter value for
temprory_tablespace:" prompt, type temp.
Now, the PERFSTAT schema
should have been created.
Connect as the PERFSTAT
user that was created from SPCREATE script.
SQL> CONNECT PERFSTAT/PERFSTAT@SCHOOL
Run STATSPACK utility
Now, clean all the
STATSPACK tables.
SQL> START %ORACLE_HOME%
The STATSPACK tables will be truncated. It will remove all
data from STATSPACK tables. You may wish to export the data before
continuing.
In the "Enter value for
return:" prompt, answer "NO" if you don"t want to continue or
press enter key to continue. In this hands-on, press enter key to
continue.
At the end, you should get
a message indicating that all tables were truncated. After truncation,
you can make your snapshots. You can make as many snapshots you need.
Make sure that your snapshots are in the pick period and can address
the performance problems.
Take a snapshot. In this example, we will take six snapshots.
SQL> EXECUTE statspack.snap;
SQL> EXECUTE statspack.snap;
SQL> EXECUTE statspack.snap;
SQL> EXECUTE statspack.snap;
SQL> EXECUTE statspack.snap;
SQL> EXECUTE statspack.snap;
Generate a report
Produce a performance report.
SQL> START %ORACLE_HOME%
When you run the SPREPORT
script, you will be prompted with a list of all your snapshots. Its
will tell you that when a snapshot started, how many are there, its
database name and instance name, etc.
You can specify the
beginning and ending of snapshot. This will give you an option to
generate a report only on the period that you wish to be generated.
Check the started time and then use the snap ID next to it for
references. In these hands-on exercises, you are going to select all
the period.
In the "Enter value for
begin_snap" prompt, enter 1. In the "Enter value for end_snap:"
prompt, enter 6.
To specifying the report
name, report will prompt to you a default report file name such as
sp_1_6. You can either to press <return> to use the name or
enter an alternative name.
In the "Enter value for
report_name" prompt, press enter key.
Go to the MS explorer and
open and read the output file sp_1_6.
Watch the following columns:
In the Shared pool statistics: The Pct Misses in the Library cache
should be very low such as 2%. On the other hand the Hit Ratio should
be very high. Also, the Pct Misses in the Dictionary cache should be
low such as 15%.
In the Buffer Cache statistics: The Cache Hit% or Hit Ratio in the
Buffer cache should be above 90%. If it is 70 to 90 - might need
tuning. If less than 70 - it needs tuning.
In the latches statistics: The Pct Misses in the latch activity should
be very close to 0.
In the Rollback or UNDO segments statistics: the Rollback or UNDO
segments statistics: The Pct Waits in the Rollback segment stats
should be very low. If the Pct Waits column is high then you may need
more UNDO segments.
"I do not
consider it an insult, but rather a compliment to be called an
agnostic. I do not pretend to know where many ignorant men are
sure -- that is all that agnosticism means." - Clarence
Darrow, Scopes trial, 1925. |
Questions:
Q: Describe the STATSPACK
utility?
Q: How do you install the
STATSPACK utility?
Q: Describe the SPCREATE
script?
Q: How do you run the
SPCREATE script?
Q: Describe the PERFSTAT
user?
Q: How do you create the
PERFSTAT user?
Q: How do you clean the
STATSPACK tables?
Q: How do you produce a
performance report using the STATSPACK utility?
Q: How do you perform a
snapshot in the STATSPACK utility?
Q: Why and how do you set
the TIMED_STATISTICS parameter?
Q: Describe the
V$FIXED_TABLE view?
Q: Describe the following
scripts:
SPCREATE.SQL
SPTRUNC.SQL
SPREPORT.SQL
|