Statspack Use Study Notes

You can use utlbstat and utlestat to obtain the performance difference report and analyze the report to determine the information that mainly affects the system. however, due to the uncertainty of performance differences between two points, such as the length of operation time and concurrency changes, the information collected is a comparison between two points. The longer the time, the less accurate the collected information and the shorter the collection time, the collected information is incomplete. at the same time, limited information is provided. With the development of Oracle, statspack has been replaced.
Compared with utlbstat and utlestat, statspack not only obtains two-point values, but also obtains Multi-Point Information and compares any two points. It can even be based on a certain point of interest, such as waiting for an event,

Generate a curve with multiple points of information. If the curve changes smoothly, it indicates that the wait event runs smoothly within the specified time period, and the occurrence of the event is within a sufficient range.

If the curve changes significantly, it indicates that the wait event occurs frequently. The current performance difference is related to the event, and the competition for resources is fierce. Therefore, statspack can more accurately describe the performance.

.
Second, statspack collects more information than utlbstat and utlestat. For example, the information of SQL statements running in the memory can be sorted by certain conditions to help you find the most influential

The SQL statement of performance.

First, statspack storage location
All statspack-related scripts are stored in the D:/Oracle/ora92/rdbms/admin/directory.

Second, how to install statspack
Statspack can be considered as a small software package. You need to install it before running it. Run the spcreate. SQL command in the Oracle Installation Directory D:/Oracle/ora92/rdbms/admin /.

After installation is complete, you can use it directly without installing it.
Before installation, make sure that the timed_statistics parameter is true. (Oracle 8i needs to be changed. 9i defaults to true)

SQL> show parameter timed_statistics

Name type value
-----------------------------------------------------------------------------
Timed_statistics Boolean true

Change method:
SQL> alter system set timed_statistics = true;

The system has been changed.

Note: The timed_statistics parameter is used to collect time information.

In addition, make sure that there are any available tablespaces used to store statspack objects. Oracle8i requires 80 Mb or more, and Oracle 9i requires at least mb. It is best to use a blank tablespace.

.
The script for creating a tablespace is as follows:
SQL> Create tablespace SP datafile 'd:/Oracle/oradata/ora92/sp01.dbf 'size 120 m;

The tablespace has been created.

Then run:

SQL> @? /Rdbms/admin/spcreate

During installation, you are prompted to enter the password of perfstat,
... Creating perfstat user...

Choose the perfstat user's password.

Not specifying a password will result in the installation failing

Specify perfstat Password
Enter the perfstat_password value: perfstat
Perfstat

If it is a production database, you must set a password because this user has considerable permissions.

Finally, specify the tablespace used:
Specify perfstat user's default tablespace
Enter the value of default_tablespace: SP
Using SP for the default tablespace

Specify temporary tablespace:

Choose the perfstat user's temporary tablespace.

Specifying the system tablespace will result in the installation
Failing, as using system for the temporary tablespace is not recommended.

Specify perfstat user's temporary tablespace.
Enter the value of temporary_tablespace: temp
Using temp for the temporary tablespace

After providing the above information, you can complete the installation.
If the script is run in SQL plus, if the installation fails, SQL plus will automatically exit without an error.

Third, use statspack to collect information

You can collect information manually or customize tasks for the database to automatically collect information. However, the system package statspack. Snap is called to collect information.

SQL> exec statspack. Snap;

The PL/SQL process is successfully completed.

The execution time and collection time are not necessarily dependent on the current system running task.
According to the optimization requirements, the collected data must be collected during business operation to reflect the status of the system during normal operation.
To generate a report, at least two or more points must be collected before the report can be produced.

SQL> exec statspack. Snap;

The PL/SQL process is successfully completed.
You can also call
SQL> @? /Rdbms/admin/spauto for automatic execution.

In general, it is recommended that statspack collect information every 15 minutes to half an hour. The amount of time is related to the system and the degree of busy. the normal operation of the system cannot be affected.

Cross-DB restart is not allowed between the two points collected.
Finally, you can call
SQL> @? /Rdbms/admin/spreport
To generate a report. When begin_snap and end_snap are input, they must be input from small to large.
Finally, enter the location where the generated report is stored. If this parameter is not specified, it will be placed in the default location. The location can be a full path.

Fourth. Uninstall statspack
Because statspack stores objects in SYS users, You cannot delete all objects manually. If you install the object in sqlplus, you are not prompted to exit and cannot install the object successfully.
You can uninstall the SDK using the following method:
SQL> @? /Rdbms/admin/spdrop
It can be completely uninstalled.