Statspack报告收集

最新推荐文章于 2024-10-17 18:58:27 发布

clp9519084

最新推荐文章于 2024-10-17 18:58:27 发布

阅读量136

点赞数

文章标签：数据库网络 java

俗话说，工欲善其事，必先利其器。做数据库性能分析，也要有一款好的工具。statspack就是oracle自带的一个强大的性能分析工具。

安装statspack需要用户具有sysdba的权限。首先以sysdba登陆，为statspack创建一个单独的tablespace。

SQL> create tablespace perfsata datafile '/u01/oradata/orcl/perfstat_01.dbf' size 512m;
Tablespace created.

然后运行spcreate.sql

SQL> @?/rdbms/admin/spcreate.sql
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: password
Enter value for default_tablespace: perfstat
Using tablespace PERFSTAT as PERFSTAT default tablespace.
Enter value for temporary_tablespace: temp
Using tablespace temp as PERFSTAT temporary tablespace.
... Creating PERFSTAT user
... Installing required packages
... Creating views
... Granting privileges
NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.
SQL> -- Build the tables and synonyms
SQL> connect perfstat/&&perfstat_password
Connected.
SQL> @@spctab
Using perfstat tablespace to store Statspack objects
... Creating STATS$SNAPSHOT_ID Sequence
... Creating STATS$... tables
NOTE:
SPCTAB complete. Please check spctab.lis for any errors.
SQL> -- Create the statistics Package
SQL> @@spcpkg
Creating Package STATSPACK...
Package created.
Creating Package Body STATSPACK...
Package body created.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

SQL> @spcreate

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: password

Enter value for default_tablespace: perfstat
Using tablespace PERFSTAT as PERFSTAT default tablespace.

Enter value for temporary_tablespace: temp
Using tablespace temp as PERFSTAT temporary tablespace.

... Creating PERFSTAT user
... Installing required packages
... Creating views
... Granting privileges

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.

SQL> --  Build the tables and synonyms
SQL> connect perfstat/&&perfstat_password
Connected.
SQL> @@spctab

Using perfstat tablespace to store Statspack objects

... Creating STATS$SNAPSHOT_ID Sequence
... Creating STATS$... tables

NOTE:
SPCTAB complete. Please check spctab.lis for any errors.

SQL> --  Create the statistics Package
SQL> @@spcpkg

Creating Package STATSPACK...
Package created.

Creating Package Body STATSPACK...
Package body created.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

安装过程中statspack会提示输入用户密码，默认表空间和临时表空间。

Enter value for perfstat_password: oracle

Enter value for default_tablespace:PERFSATA

Enter value for temporary_tablespace: temp

以上安装完成；

生成快照，收集报告

SQL> EXECUTE STATSPACK.SNAP;