摘要:《oracle statspack snap shot 安裝》
2009/07/18
因為公司的資料庫問題，深覺要有些工具來監測資料庫效能。
statspack 是個很好的選擇，這裡介紹如何安裝，至於監測出來的資料
如何判斷，那又是另一個議題，待我有心得之後再跟大家分享。
______________________________________________________________
C:\>cd oracle\product\10.2.0\db_a\rdbms\admin
C:\oracle\product\10.2.0\db_A\RDBMS\ADMIN>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 7月 18 08:30:24 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys as sysdba
輸入密碼:
已連線.
SQL> show parameter instance_name
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
instance_name string
OracleA
-- 先建立帳號 perfstat 的預設tablespace
SQL> create tablespace perfstat datafile '+ASMGROUP1' size 500M;
SQL> @spcreate.sql
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
輸入 perfstat_password 的值: change_on_install
change_on_install
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
------------------------------ --------- ----------------------------
CWMLITE PERMANENT
DRSYS PERMANENT
EXAMPLE PERMANENT
INDX PERMANENT
ODM PERMANENT
ORATEXT PERMANENT
PERFSTAT PERMANENT
SYSAUX PERMANENT *
TINGSPACE PERMANENT
TOOLS PERMANENT
USERS PERMANENT
TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
XDB PERMANENT
Pressing will result in STATSPACK's recommended default
tablespace (identified by *) being used.
輸入 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.
輸入 temporary_tablespace 的值: temp
Using tablespace temp as PERFSTAT temporary tablespace.
... Creating PERFSTAT user
... Installing required packages
.
.
.
.
.
.
.
NOTE:
SPCTAB complete. Please check spctab.lis for any errors.
SQL> -- Create the statistics Package
SQL> @@spcpkg
SQL> Rem
SQL> Rem $Header: spcpkg.sql 31-may-2005.14:07:53 cdgreen Exp $
SQL> Rem
SQL> Rem spcpkg.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2005, Oracle. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem spcpkg.sql
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem SQL*PLUS command file to create statistics package
SQL> Rem
SQL> Rem NOTES
SQL> Rem Must be run as the STATSPACK owner, PERFSTAT
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem cdgreen 05/24/05 - 4246955
SQL> Rem cdgreen 04/18/05 - 4228432
SQL> Rem cdgreen 02/28/05 - 10gR2 misc
SQL> Rem vbarrier 02/18/05 - 4081984
SQL> Rem cdgreen 01/25/05 - 4143812
SQL> Rem cdgreen 10/29/04 - 10gR2_sqlstats
SQL> Rem cdgreen 10/25/04 - 3970898
SQL> Rem cdgreen 07/16/04 - 10g R2
SQL> Rem vbarrier 03/18/04 - 3517841
SQL> Rem vbarrier 02/12/04 - 3412853
SQL> Rem cdialeri 12/04/03 - 3290482
SQL> Rem cdialeri 11/05/03 - 3202706
SQL> Rem cdialeri 10/14/03 - 10g - streams - rvenkate
SQL> Rem cdialeri 08/05/03 - 10g F3
SQL> Rem cdialeri 07/31/03 - 2804307
SQL> Rem vbarrier 02/25/03 - 10g RAC
SQL> Rem cdialeri 01/28/03 - 10g F2: baseline, purge
SQL> Rem cdialeri 11/15/02 - 10g F1
SQL> Rem cdialeri 10/29/02 - 2648471
SQL> Rem cdialeri 09/11/02 - 1995145
SQL> Rem vbarrier 04/18/02 - 2271895
SQL> Rem vbarrier 03/20/02 - 2184504
SQL> Rem spommere 03/19/02 - 2274095
SQL> Rem vbarrier 03/05/02 - Segment Statistics
SQL> Rem spommere 02/14/02 - cleanup RAC stats that are no longer needed
SQL> Rem spommere 02/08/02 - 2212357
SQL> Rem cdialeri 02/07/02 - 2218573
SQL> Rem cdialeri 01/30/02 - 2184717
SQL> Rem cdialeri 01/09/02 - 9.2 - features 2
SQL> Rem cdialeri 11/30/01 - 9.2 - features 1
SQL> Rem hbergh 08/23/01 - 1940915: use substrb on sql_text
SQL> Rem cdialeri 04/26/01 - 9.0
SQL> Rem cdialeri 09/12/00 - sp_1404195
SQL> Rem cdialeri 04/07/00 - 1261813
SQL> Rem cdialeri 03/28/00 - sp_purge
SQL> Rem cdialeri 02/16/00 - 1191805
SQL> Rem cdialeri 11/01/99 - Enhance, 1059172
SQL> Rem cgervasi 06/16/98 - Remove references to wrqs
SQL> Rem cmlim 07/30/97 - Modified system events
SQL> Rem gwood.uk 02/30/94 - Modified
SQL> Rem densor.uk 03/31/93 - Modified
SQL> Rem cellis.uk 11/15/89 - Created
SQL> Rem
SQL>
SQL> set echo off;
Creating Package STATSPACK...
已建立套裝程式.
沒有任何錯誤
Creating Package Body STATSPACK...
已建立套裝程式主體.
沒有任何錯誤
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
-- start to snapshot
SQL>exec statspack.snap
已順利完成 PL/SQL 程序.
.
.
.
-- 等系統運作一段時間後再執行一次
SQL>exec statspack.snap
已順利完成 PL/SQL 程序.
-- 取得報告
SQL>@spreport.sql
.
.
.
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
oraclea ORACLEA 1 18 7月 2009 09:0 5
5
2 18 7月 2009 09:0 5
7
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
輸入 begin_snap 的值: 1
Begin Snapshot Id specified: 1
輸入 end_snap 的值: 2
End Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press to continue, otherwise enter an alternative.
輸入 report_name 的值: c:\spreport.txt
Using the report name c:\spreport.txt
STATSPACK report for
.
.
.
.
.
.
. 此時檔案就輸出至指定的位置，此例放在c:\spreport.txt
最難的就系統的判讀，網路上有一些案例分享，或許可以從中得到些什麼。
---------------------------------------------------------
附註:
1.如果安裝過程遇見ORA-01659，表示Tablespace空間不足，可以在create tablespace時，
設定autoextend on就可以了。
2.關於statspack的sql都在<%ORACLE_HOME%>/RDBMS/ADMIN/SP*.SQL