Oracle 11G uses statspack to view the performance of the database
1. Install the Statspack tool (the scripts for the statspack tool are in the $ORACLE_HOME/rdbms/admin directory, all files that start with sp)
[oracle@oracle ~] $sqlplus / as sysdba
SQL > select file_name,tablespace_name from dba_data_files
FILE_NAMETABLESPACE_NAME
-
/ home/oracle/app/oradata/orcl/users01.dbf USERS
/ home/oracle/app/oradata/orcl/undotbs01.dbf UNDOTBS1
/ home/oracle/app/oradata/orcl/sysaux01.dbf SYSAUX
/ home/oracle/app/oradata/orcl/system01.dbf SYSTEM
/ home/oracle/app/oradata/orcl/tong.dbf TONG1
6 rows selected.
SQL > create tablespace perfstat datafile'/ home/oracle/app/oradata/orcl/perfstat.dbf' size 200m;-- create a separate tablespace to store statspack data
Tablespace created.
SQL > @ / home/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/spcreate.sql-when you execute the installation statspack script file, you will be prompted for the perfstat user's password, default tablespace, and temporary tablespace
SQL >
two。 Log in using statspack users to generate data
SQL > conn perfstat/system
Connected.
SQL > execute statspack.snap
PL/SQL procedure successfully completed. -- perform snapshots to obtain data (perform several more times)
SQL > execute statspack.snap
PL/SQL procedure successfully completed.
SQL > execute statspack.snap
PL/SQL procedure successfully completed.
SQL > execute statspack.snap
PL/SQL procedure successfully completed.
SQL > select max (snap_id) from stats$snapshot;-see how many reports there are
MAX (SNAP_ID)
-
four
SQL > @ / home/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/spreport.sql-- generate a report
SQL > exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle admin] $vim sp_1_4.lst-- View information in statspack
3.statspack management
SQL > @ / home/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/sppurge.sql-- Delete historical data
SQL > @ / home/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/sptrunc.sql-- clear the data in the table
SQL > @ / home/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/spdrop.sql-- Uninstall the statspack tool