oracle 11G使用statspack查看数据库的性能

1.安装Statspack工具(statspack工具的脚本在$ORACLE_HOME/rdbms/admin目录下,全是以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;    --创建独立的表空间存储statspack数据

Tablespace created.

SQL> @ /home/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/spcreate.sql     --执行安装statspack脚本文件,会提示输入perfstat用户的密码,默认表空间,临时表空间

SQL>

2.使用statspack用户登陆,生成数据

SQL> conn perfstat/system

Connected.

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> execute statspack.snap;

PL/SQL procedure successfully completed.

SQL> select max(snap_id) from stats$snapshot;    --查看有多少个报告

MAX(SNAP_ID)

------------

  4

SQL> @ /home/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/spreport.sql  --生成报告

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    --查看statspack中的信息

3.statspack管理

SQL> @ /home/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/sppurge.sql  --删除历史数据

SQL> @ /home/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/sptrunc.sql  --清空表中的数据

SQL> @ /home/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/spdrop.sql   --卸载statspack工具