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工具 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |