ORACLE 9i中statpack的简单用法

维护的一套EBS系统用的数据库用的数据仍然是9i，最近一次月结的时候高峰期CPU占用率达100%，发现大量客户化的资产报表在运行，导致过账的请求被搁置，用户意见较大，打算升级硬件，于是要进行评估，先收集一些性能数据，只用用statpack了。用两种方式采集，一个是crontab，另一个是用ORACLE里面的job管理。

Drop 以前的perfstat用户下的所有对象和数据

bash-4.2$ sqlplus " / as sysdba"SQL*Plus: Release 9.2.0.6.0 - Production on Tue Dec 8 14:37:06 2015Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - ProductionSQL>   @?/rdbms/admin/spdropDropping old versions (if any)

初始环境

会默认创建一个用户perfstat，最好提前创建一个专用的表空间。

SQL>   @?/rdbms/admin/spcreate... Creating PERFSTAT user ...Choose the PERFSTAT user's password.Not specifying a password will result in the installation FAILINGSpecify PERFSTAT password#输入用户密码Enter value for perfstat_password: perfstat123perfstat123PL/SQL procedure successfully completed.Below are the list of online tablespaces in this database.Decide which tablespace you wish to create the STATSPACK tablesand indexes.  This will also be the PERFSTAT user's default tablespace.Specify PERFSTAT user's default   tablespace#输入监控数据存放的表空间Enter value for default_tablespace: APPS_TS_TOOLSUsing APPS_TS_TOOLS for the default tablespacePL/SQL procedure successfully completed.Choose the PERFSTAT user's temporary tablespace.Specifying the SYSTEM tablespace will result in the installationFAILING, as using SYSTEM for the temporary tablespace is not recommended.Specify PERFSTAT user's temporary tablespace.#确定临时表空间Enter value for temporary_tablespace: tempUsing temp for the temporary tablespaceNo errors.Creating Package Body STATSPACK...Package body created.No errors.NOTE:SPCPKG complete. Please check spcpkg.lis for any errors.#创建完成

statpack sql脚本所在路径

$ cd $ORACLE_HOME$ cd rdbms$ cd admin

用ORACLE的job功能来实现自动采样

这地方可以自定义下

$ vi spauto.sql"spauto.sql" 68 lines, 1892 characters RemRem $Header: spauto.sql 16-feb-00.16:49:37 cdialeri Exp $RemRem spauto.sqlRemRem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.RemRem    NAMERem      spauto.sqlRemRem    DESCRIPTIONRem      SQL*PLUS command file to automate the collection of STATPACKRem      statistics.RemRem    NOTESRem      Should be run as the STATSPACK owner, PERFSTAT.Rem      Requires job_queue_processes init.ora parameter to beRem      set to a number >0 before automatic statistics gatheringRem      will run.RemRem    MODIFIED   (MM/DD/YY)Rem    cdialeri    02/16/00 - 1191805Rem    cdialeri    12/06/99 - 1059172, 1103031Rem    cdialeri    08/13/99 - CreatedRemspool spauto.lis"spauto.sql" 68 lines, 1892 charactersspool spauto.lis----  Schedule a snapshot to be run on this instance every hour, on the hourvariable jobno number;variable instno number;begin  select instance_number into :instno from v$instance;--  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);#这儿设置的是采样级别和采样频率，涉及到dbms_job包的用法和stat包的用法  dbms_job.submit(:jobno, 'statspack.snap(i_snap_level=>7);', sysdate, 'trunc(SYSDATE+1/24/4,''MI'')', TRUE, :instno);  commit;end;/promptprompt  Job number for automated statistics collection for this instanceprompt  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~prompt  Note that this job number is needed when modifying or removingprompt  the job:print jobnopromptprompt  Job queue processprompt  ~~~~~~~~~~~~~~~~~prompt  Below is the current setting of the job_queue_processes init.oraprompt  parameter - the value for this parameter must be greaterprompt  than 0 to use automatic statistics gathering::q$ 

开始采样

$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.6.0 - Production on Tue Dec 8 10:05:52 2015Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - ProductionSQL> alter user perfstat identified by perf123;User altered.SQL> connect perfstat/perf123Connected.#执行完这个sql就把定时采集放到oracle里面的任务去了。SQL> @spauto.sqlPL/SQL procedure successfully completed.Job number for automated statistics collection for this instance~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Note that this job number is needed when modifying or removingthe job:     JOBNO----------      6274Job queue process~~~~~~~~~~~~~~~~~Below is the current setting of the job_queue_processes init.oraparameter - the value for this parameter must be greaterthan 0 to use automatic statistics gathering:NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------job_queue_processes                  integer     10Next scheduled run~~~~~~~~~~~~~~~~~~The next scheduled run for this job is:       JOB NEXT_DATE NEXT_SEC---------- --------- ----------------      6274 08-DEC-15 10:06:16SQL> desc dba_jobs Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- JOB                                       NOT NULL NUMBER LOG_USER                                  NOT NULL VARCHAR2(30) PRIV_USER                                 NOT NULL VARCHAR2(30) SCHEMA_USER                               NOT NULL VARCHAR2(30) LAST_DATE                                          DATE LAST_SEC                                           VARCHAR2(16) THIS_DATE                                          DATE THIS_SEC                                           VARCHAR2(16) NEXT_DATE                                 NOT NULL DATE NEXT_SEC                                           VARCHAR2(16) TOTAL_TIME                                         NUMBER BROKEN                                             VARCHAR2(1) INTERVAL                                  NOT NULL VARCHAR2(200) FAILURES                                           NUMBER WHAT                                               VARCHAR2(4000) NLS_ENV                                            VARCHAR2(4000) MISC_ENV                                           RAW(32) INSTANCE                                           NUMBER#看下任务的设置情况SQL> select job,what from dba_jobs;SQL> set linesize 132SQL> set pagesize 999SQL> /23 rows selected.SQL> col what for a50SQL> /        JOB WHAT---------- --------------------------------------------------      6274 statspack.snap(i_snap_level=>7);23 rows selected.SQL> select job,what, broken  from dba_jobs;       JOB WHAT                                               B---------- -------------------------------------------------- -      6274 statspack.snap(i_snap_level=>7);                   N23 rows selected.SQL> quit

用采样的snap快照来生成报告

SQL>  connect perfstat/my_perfstat_password#用这个sql来生成报告SQL>  @?/rdbms/admin/spreportCurrent Instance~~~~~~~~~~~~~~~~   DB Id    DB Name      Inst Num Instance----------- ------------ -------- ------------ 1812013746 SDDEV               1 SDDEVInstances in this Statspack schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   DB Id    Inst Num DB Name      Instance     Host----------- -------- ------------ ------------ ------------ 1812013746        1 SDDEV        SDDEV        IT2SerpDT3Using 1812013746 for database IdUsing          1 for instance numberCompleted Snapshots                               Snap                    SnapInstance     DB Name             Id   Snap Started    Level Comment------------ ------------ --------- ----------------- ----- --------------------SDDEV        SDDEV                1 08 Dec 2015 15:45     5                                  2 08 Dec 2015 16:12     5Specify the Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~#键入开始快照idEnter value for begin_snap: 1Begin Snapshot Id specified: 1#键入结束快照idEnter value for end_snap: 2End   Snapshot Id specified: 2Specify the Report Name~~~~~~~~~~~~~~~~~~~~~~~The default report file name is sp_1_2.  To use this name,press <return> to continue, otherwise enter an alternative.#键入快照名字，默认的是sp_1_2.lst，会放在当前的pwd文件夹下 Enter value for report_name: second_stat_report

清除不需要的数据

用这个语句 select * from stats$snapshot;来查下快照信息

bash-4.2$ sqlplus perfstat/perfstat123SQL*Plus: Release 9.2.0.6.0 - Production on Tue Dec 8 17:37:47 2015Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - Production--执行这个命令然后输入要清除的起始id就ok了SQL>  @?/rdbms/admin/sppurgeDatabase Instance currently connected to========================================                                Instance   DB Id    DB Name    Inst Num Name----------- ---------- -------- ---------- 1812013746 SDDEV             1 SDDEVSnapshots for this database instance====================================          Snap Snap Id Level Snapshot Started      Host            Comment-------- ----- --------------------- --------------- -------------------------       1     5  08 Dec 2015 15:45:26 IT2SerpDT3       2     5  08 Dec 2015 16:12:01 IT2SerpDT3Warning~~~~~~~sppurge.sql deletes all snapshots ranging between the lower andupper bound Snapshot Id's specified, for the database instanceyou are connected to.You may wish to export this data before continuing.Specify the Lo Snap Id and Hi Snap Id range to purge~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for losnapid: 1Using 1 for lower bound.Enter value for hisnapid: 2Using 2 for upper bound.Deleting snapshots 1 - 2.Purge of specified Snapshot range complete.  If you wish to ROLLBACKthe purge, it is still possible to do so.  Exitting from SQL*Plus willautomatically commit the purge.SQL> commit;Commit complete.

暂停statpack收集

broken 方式

$ sqlplus "/ as sysdba"SQL> conn perfstat/*********Connected.SQL> show userUSER is "PERFSTAT"SQL> exec dbms_job.broken(6294,true)PL/SQL procedure successfully completed.SQL> commit;Commit complete.SQL> SQL> select job,what,broken from dba_jobs where job=6294;   JOB WHAT                                                    BROKEN------ ------------------------------------------------------- ---------------------------------  6294 statspack.snap;                                         Y--如果重新启动的话用false参数启动作业 SQL> exec dbms_job.broken(1,false) PL/SQL 过程已成功完成。 SQL>commit; --停其他用户的job SQL>exec sys.dbms_ijob.broken(98,true); SQL>commit; 

remove 方式

SQL> begin  dbms_job.remove(4);   end;   / PL/SQL procedure successfully completed SQL> commit; Commit complete