Oracle Performance Diagnostics-Learn Statspack notes (i) [Installation and testing]

Oracle| Notes | performance
Oracle Performance Diagnostics-Learn Statspack notes (i)



Author: Liu Yingbo

Time: 2004-3-2

Mail:liuyingbo@126.com, please correct me.



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



Prepare before installation



A. First, the confirmation of system parameters:

Job_query_processes: In order to establish automatic task, perform data collection, this parameter is more than 0

Time_statistics: To collect operating system timing information and so on, you need to set it to true

B. It is recommended that it is best to separate the data table space and temporary table space for Perfstat users (that is, install Statspack to be built), the data table space should have at least 100M of free space, otherwise create Statspack object will fail, if you intend to use statspack for a long time, you can consider building slightly larger Data table space.



Installation

A. Installation scripts

The directory where the script is installed is $oracle_home/rdbms/admin, In the oracle8.1.6 version of the installation script is Statscre.sql, after the 8.1.7 version started is spcreate.sql, install the required users before 9i needs internal or have SYSDBA permissions of users, 9i needs the user is SYS (9i no longer exists intern Al user)



Execute the installation script as follows:

sql> @ $ORACLE _home/rdbms/admin/spcreate



B. In the installation process, you need to fill in the Perfstat user's password, and select Perfstat User's data table space and temporary table space, after the installation is completed, see the corresponding. lis file check the installation is correct, there are problems can be completed by Spdrop.sql Statspack Uninstall, rerun SP Create.sql completes the installation of Statspack.



Test

The simplest Statspack report is generated, run two times Statspack.snap, and then run Spreport.sql generate a report based on a two-point time. If you are a previous version of Oracle, you need to modify the Spcpkg.sql to change the substr to SUBSTRB, as follows: 8.1.7.3

Select l_snap_id

, P_dbid

, P_instance_number

, substr (sql_text,1,31) àsubstrb (sql_text,1,31)



Establish a simple statspack reporting process 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



SPReport will be listed in the execution of the required snapshots, you need to fill out the report description of the start and end of the snapshot number, and fill out the report file name, of course, you can not fill in, use the default report file name, the default will be generated in the directory $oracle_home/rdbms/admin



This will verify that the Statspack has been properly installed.



Automatic collection of Statspack snapshots

Normal in the real environment, we need continuous sampling for a period of time, so that the generation of statspack can better reflect the status of the system, we can from the Spauto.sql from the dynamic collection of data.



It may be designed to modify the contents of the following sections

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

/

The main is to modify the value of 1/24, is currently an hour of automatic collection of data, if you want to change for half an hour to collect data on the modified to 1/48, the same, carried out or large or small changes.



After execution, you can see information such as the job number that is currently collecting data automatically in the Spauto.lis file. When you want to generate a statspack report, just select any two snapshot numbers that do not span the downtime. Note that Statspack is not able to cross the downtime.





adjourned

................................................................................................

Thanks to Eygle and his "Statspack Use Guide" article