使用 Statspack 实践
今天分析一个text search问题时候，使用了Statspack工具，做一个记录。实践中，参考了 Eygle 网站上的Statspack专题 和 Oracle9i Database Performance Tuning Guide and Reference 的 C21-Using Statspack
安装Statspack
创建Statspack使用的表空间
C:\oracle\ora10g\RDBMS\ADMIN>sqlplus sys/fvt6eip as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jan 8 01:36:24 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create tablespace perfstat datafile 'c:\oracle\oradata\perfstat.dbf'
size 500M extent management local;
Tablespace created.
|
安装Statspack:执行前，确认Temp Database的名字，本例使用temp.
SQL> @spcreate.sql
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: fvt6eip
fvt6eip
Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.
Choose the PERFSTAT users's default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.
TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
ICMLFQ32 PERMANENT
ICMLNF32 PERMANENT
ICMLSNDX PERMANENT
ICMLSTXT PERMANENT
ICMSFQ04 PERMANENT
ICMVFQ04 PERMANENT
PERFSTAT PERMANENT
SYSAUX PERMANENT *
Pressing will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: perfstat
Using tablespace PERFSTAT as PERFSTAT default tablespace.
Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.
Choose the PERFSTAT user's Temporary tablespace.
TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP TEMPORARY *
Pressing will result in the database's default Temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace: temp
Using tablespace temp as PERFSTAT temporary tablespace.
...........
...........
Creating Package STATSPACK...
Package created.
No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
|
抓取Statspack Snapshot
SQL> execute statspack.snap
PL/SQL procedure successfully completed.
|
确认抓取Snapshot步骤成功
SQL> select snap_id from stats$snapshot;
SNAP_ID
----------
1
1 row selected.
|
生成Statsnap 报告:至少有两次的Snapshot 才能生成报告, 输入相同的snapshotid 会导致ORA-20200: End Snapshot Id 1 must be greater than Begin Snapshot Id 1。
生成第二个snapshot
SQL> execute statspack.snap
PL/SQL procedure successfully completed.
|
产生报告@spreport.sql ,出入开始的snapsshot和结束的snapshot，以及report名称即可
通过@spauto.sql 可以配置Oracle Job来周期性收集snapshot 供分析所有。 使用exp/imp工具可以导入和导出 statspack的数据。这些可以在以上的参考文档中找到详细步骤，不赘述。
This entry was posted
on 星期三, 01月 7th, 2009 at 14:10 and is filed under oracle.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.