Performance

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:

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
 

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.
 

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$'
/

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.

Check the TIMED_STATISTICS parameter value.
SQL> SHO 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
 

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.

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.

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