Oracle Performance Diagnosis - Learning Statspack Notes (1)
Author: Liuying Bo
Time: 2004-3-2
Mail: liuyingbo@126.com, please correct
Reprint, please indicate the source and the author
Description: Oracle8.1.6 began to introduce Statspack, Statspack is a powerful tool for diagnosing Oracle performance
Pre-installation
A. The first is the confirmation of the system parameters:
Job_Query_Processes: In order to establish an automatic task, execute data collection, this parameter is greater than 0
Time_statistics: To collect operating system timing information, you need to set it to true
B. It is recommended that it is recommended to separate data table space and temporary table space for Perfstat users (ie, installation StatsPack). The data table space must have at least 100M idle space, otherwise the StatsPack object will fail, if intended Long-term use of Statspack, you can consider a slightly large data table space.
installation
A. Install the script
The installed script is located is $ oracle_home / rdbms / admin, in the Oracle8.1.6 version of the installation script is statscre.sql, then the 8.1.7 version begins to spcreate.sql, install the required user before 9i, need INTERNAL, INTERNAL or SYSDBA permission User, 9i users need users are SYS (9i does not exist in internal users)
Perform the installation script is as follows:
SQL> @ $ oracle_home / rdbms / admin / spcreate
B. During the installation process, you need to fill in the password of the Perfstat user, and select the PERFSTAT user's data table space and temporary table space. After the installation is complete, the corresponding .LIS file checks if the installation is correct, there is a problem with SPDROP.SQL Complete StatsPack's uninstall, re-run spcreate.sql to complete the installation of Statspack.
test
The simplest STATSPACK report is generated, running twice statspack.snap, then runs Spreport.sql to generate a report based on two time points. If it is 8.1.7.3, it is necessary to modify spcpkg.sql, to modify substr to substrb, as follows:
SELECT L_SNAP_ID
, p_dbid
, p_instance_number
Substr (SQL_Text, 1, 31) à Substrb (SQL_Text, 1, 31)
Establishing a simple STATSPACK reporting process is as follows:
SQL> Execute statspack.snap
PL / SQL Procedure SuccessFully Completed.
SQL> Execute statspack.snap
PL / SQL Procedure SuccessFully Completed.
SQL> @ $ oracle_home / rdbms / admin / spreport
The snapshot that needs to be selected during the execution of Spreport, you need to fill in the start and end of the report description, and fill in the file name of the report, of course, can not fill, use the default report file name, the default will generate In the directory $ oracle_home / rdbms / admin
This will verify that Statspack has been completely installed.
Automatically collect STATSPACK snapshots
Normal in a real environment, we need a continuous sampling for a while, so that STATSPACK can better reflect the current situation of the system, we can collect data from spauto.sql. It is primarily designed to modify the following
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;
/
It is mainly to modify the 1/24 value, which is currently automatically collected once an hour. If you want to change the data for half an hour to collect data, you will be modified to 1/48, the same, or large or small modifications.
After execution, you can see the JOB number of the currently collecting data in the spAuto.lis file. When you want to generate a STATSPACK report, just select any two snapshot serial numbers that do not cross the downtime. Note that Statspack cannot spanned by stop.
(to be continued)
......................................................................................
Thanks to Eygle and his "Statspack Use Guide"
