How to install and use the Oracle performance analysis tool statpack

Oracle performance analysis tool statpackThe installation and usage are what we will introduce in this article. Through Statspack, we can easily determine the bottleneck of the Oracle database and record the database performance status, remote technical support staff can also quickly learn the running status of your database. To enable automatic database collection, you need to modify two parameters:

Lab environment:

OS: redhat 5.5

DB: oracle11g

Parameter settings: job_queue_processes and timed_statistics

Query whether job_queue_processes is greater than 0: show parameter job_queue_processes;

If it is 0, we need to modify alter system set job_queue_processes = 10;

Check whether timed_statistics is true.

Modify: alter system set timed_statistics = true;

Install statpack:

User operations that require sysdba permissions, first create user tablespace, which depends on the size of the amount of information we collect to set, generally set to MB

 
 

  
  
create tablespace perfstat '/home/oracle/perfstat.dbf' size 500m extent management local;  
  
  
@$ORACLE_HOME/rdbms/admin/spcreate.sql 
 
 

Set a password for perfstat.

Input tablespace and temporary tablespace

SPCPKG complete. please check spcpkg. lis for any errors. the preceding statement is successful. the lis file is executed and re-built. If an error occurs during creation, we can delete and re-create the file:

@ $ ORACLE_HOME/rdbms/admin/spdrop. SQL: Click Create again.

Generate the survey report and switch to the perfstat User:

Collect System Information: execute statspack. snap;

View collected statistics:

 
 

  
  
SQL> select snap_id,snap_time,startup_time from stats$snapshot;  
  
  
SNAP_ID SNAP_TIME STARTUP_T  
  
  
---------- --------- ---------  
  
  
1 14-AUG-11 14-AUG-11  
  
  
2 14-AUG-11 14-AUG-11  
  
  
5 14-AUG-11 14-AUG-11  
  
  
3 14-AUG-11 14-AUG-11  
  
  
4 14-AUG-11 14-AUG-11 
 
 

We can also make him automatically count the system: @ $ ORACLE_HOME/rdbms/admin/spauto. SQL

Let's take a look at the main content of the file:

 
 

  
  
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; 
 
 

You can set the collection time by modifying this value.

1/24 HH every hour

1/48 MI every 30 minutes

1/144 MI every ten minutes

1/288 MI every five minutes

Remove automatic collection:

View the job number:

 
 

  
  
select job,job_user,priv_user,last_date,next_date,interval from user_jobs; 
 
 

Run:

 
 

  
  
execute dbms_jobs.remove('job'); 
 
 

Clear statistics:

 
 

  
  
@$ORACLE_HOME/rdbms/admin/sptrunc.sql; 
 
 

Statpack collection type:

Level: controls the type of collected data.

Threshold: Set the missing value of collected data. The default collection type is 5. We can change the collection type;

Execute statpack. snap (I _snap_level => 10, I _modify_parameter => 'true ');

Modify the collection method only: execute statpack. snap (I _snap_level => 10 );

Threshold: the snapshot threshold applies only to SQL statements obtained from the stat $ SQL _summary table.

Generate System Report: @ $ ORACLE_HOME/rdbms/admin/spreport. SQL

Enter the snap value we are querying and the path I am storing.

This article describes how to install and use the Oracle performance analysis tool statpack!