Statspack00

Objectives

  • Install perfastat schema
    • perfstat->performance statistics
  • captrure snapshots
  • Report
  • Configure parameters
  • Maintenance

<<参考文档:$ORACLE_HOME/rdbms/admin/spdoc.txt>>

Install perfstat schema

  • Prepair tablespace
    • 不能放在system、undo、user、temporatory tablespace里面
  • Execute spcreate.sql
create tablespace perfstat 
SQL> create tablespace perfstat datafile '/u01/oradata/md/perfstat01.dbf' size 200m autoextend off;

Tablespace created.
Check Error 
[oracle@DG1 admin]$ cat spcpkg.lis
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.

Capture snapshot

  • Manual snap
  • Automatic snap
    • Dbms_job
    • Operating system command

Manual snap

SQL> conn perfstat/perfstat
Connected.
SQL> execute statspack.snap;

PL/SQL procedure successfully completed.
SQL> select * from stats$statspack_parameter;

      DBID INSTANCE_NUMBER SESSION_ID SNAP_LEVEL    NUM_SQL EXECUTIONS_TH PARSE_CALLS_TH DISK_READS_TH BUFFER_GETS_TH SHARABLE_MEM_TH VERSION_COUNT_TH PIN_STATSP ALL_I LAST_MODI UCOMMENT                      JOB SEG_PHY_READS_TH SEG_LOG_READS_TH SEG_BUFF_BUSY_TH SEG_ROWLOCK_W_TH SEG_ITL_WAITS_TH SEG_CR_BKS_RC_TH SEG_CU_BKS_RC_TH OLD_SQL_CA
---------- --------------- ---------- ---------- ---------- ------------- -------------- ------------- -------------- --------------- ---------------- ---------- ----- --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------
3718810889               1          0          5         50           100           1000          1000          10000         1048576               20 TRUE       FALSE 28-APR-13                               1000             10000              100              100              100             1000             1000 FALSE

1 row selected.

Automatic snap

下面自动执行脚本来源于:$ORACLE_HOME/rdbms/admin/spauto.sql

创建自动执行脚本 
variable 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);
  commit;
end;
/
user_jobs 
SQL> desc user_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(8)
 THIS_DATE                                          DATE
 THIS_SEC                                           VARCHAR2(8)
 NEXT_DATE                                 NOT NULL DATE
 NEXT_SEC                                           VARCHAR2(8)
 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,log_user,priv_user from user_jobs;

       JOB LOG_USER                       PRIV_USER
---------- ------------------------------ ------------------------------
         3 PERFSTAT                       PERFSTAT
USER_JOBS 
SQL> select job,log_user,priv_user,to_char(next_date,'YYYY-MM-DD HH24:MI:SS') from user_jobs;

       JOB LOG_USER                       PRIV_USER                      TO_CHAR(NEXT_DATE,'
---------- ------------------------------ ------------------------------ -------------------
         3 PERFSTAT                       PERFSTAT                       2013-04-28 15:00:00
SQL> define
DEFINE _DATE           = "28-APR-13" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "poli" (CHAR)
DEFINE _USER           = "PERFSTAT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "ed" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
查看statspack default level 
SQL> desc stats$statspack_parameter;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 SESSION_ID                                NOT NULL NUMBER
 SNAP_LEVEL                                NOT NULL NUMBER
 NUM_SQL                                   NOT NULL NUMBER
 EXECUTIONS_TH                             NOT NULL NUMBER
 PARSE_CALLS_TH                            NOT NULL NUMBER
 DISK_READS_TH                             NOT NULL NUMBER
 BUFFER_GETS_TH                            NOT NULL NUMBER
 SHARABLE_MEM_TH                           NOT NULL NUMBER
 VERSION_COUNT_TH                          NOT NULL NUMBER
 PIN_STATSPACK                             NOT NULL VARCHAR2(10)
 ALL_INIT                                  NOT NULL VARCHAR2(5)
 LAST_MODIFIED                                      DATE
 UCOMMENT                                           VARCHAR2(160)
 JOB                                                NUMBER
 SEG_PHY_READS_TH                          NOT NULL NUMBER
 SEG_LOG_READS_TH                          NOT NULL NUMBER
 SEG_BUFF_BUSY_TH                          NOT NULL NUMBER
 SEG_ROWLOCK_W_TH                          NOT NULL NUMBER
 SEG_ITL_WAITS_TH                          NOT NULL NUMBER
 SEG_CR_BKS_RC_TH                          NOT NULL NUMBER
 SEG_CU_BKS_RC_TH                          NOT NULL NUMBER
 OLD_SQL_CAPTURE_MTH                       NOT NULL VARCHAR2(10)

SQL> select dbid,snap_level from stats$statspack_parameter;

      DBID SNAP_LEVEL
---------- ----------
3718810889          5
修改采集级别为7，并使参数立即生效. 
SQL> execute statspack.snap(i_snap_level=>7,i_modify_parameter=>'true');

PL/SQL procedure successfully completed.

SQL> select dbid,snap_level from stats$statspack_parameter;

      DBID SNAP_LEVEL
---------- ----------
3718810889          7
修改采集级别为6，并使参数立即生效. 
SQL> execute statspack.modify_statspack_parameter(i_snap_level=>6);

PL/SQL procedure successfully completed.

SQL> select dbid,snap_level from stats$statspack_parameter;

      DBID SNAP_LEVEL
---------- ----------
3718810889          6
删除一个已经存在的job 
SQL> select job,log_user,priv_user,to_char(next_date,'YYYY-MM-DD HH24:MI:SS') from user_jobs;

       JOB LOG_USER                       PRIV_USER                      TO_CHAR(NEXT_DATE,'
---------- ------------------------------ ------------------------------ -------------------
         3 PERFSTAT                       PERFSTAT                       2013-04-29 01:00:00

SQL> execute dbms_job.remove(3);

PL/SQL procedure successfully completed.

SQL> select job,log_user,priv_user,to_char(next_date,'YYYY-MM-DD HH24:MI:SS') from user_jobs;

no rows selected

maintenance

  • Make_baseline & clear_baseline
  • Purge snapshot except baseline
    • Purge只会把没有标记为base_line的snapshot给清除掉
  • Truncate all tables with perfstat schema
  • Drop perfstat schema 
  • Manual statistics perfstat`s objects
  • Export perfstat schema

>>make base_line 

view snap_id from stats$snapshot; 
SQL> select snap_id from stats$snapshot;

   SNAP_ID
----------
         1
         2
         3
         4
         5
         6
        11
        12
        13
        21
        22

   SNAP_ID
----------
        31
        32
        33

14 rows selected.
生成snap_id 为11至13的baseline 
exec statspack.make_baseline(i_begin_snap=>11,i_end_snap=>13);
purge snap_id between 11 and 32 without mark baseline; 
SQL> execute statspack.purge(i_begin_snap=>11,i_end_snap=>32);

PL/SQL procedure successfully completed.
view snap_id again 
SQL> select snap_id from stats$snapshot;

   SNAP_ID
----------
         1
         2
         3
         4
         5
         6
        11
        12
        13
        33

10 rows selected.

>>Purge snapshots

  • 方法一:(begin snap_id & end snap_id )
  • 方法二:(begin date & end date)
  • 方法三:(before date)
  • 方法四:(remain days)
  • 方法五:(sppurge.sql)

>>导出数据 

expperfstat.par文件内容

expperfstat.par 
file=perfstat.dmp
log=perfstat.log
compress=y
grants=y
indexes=y
rows=y
constraints=y
owner=perfstat
consistent=y

exp userid=perfstat/perfstat parfile=expperfstat.par

Truncate table 
SQL> @sptrunc.sql

Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables.  You may
wish to export the data before continuing.


About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If would like to exit WITHOUT truncating the tables, enter any text at the
begin_or_exit prompt (e.g. 'exit'), otherwise if you would like to begin
the truncate operation, press <return>


Enter value for begin_or_exit: 
Entered at the 'begin_or_exit' prompt

... Starting truncate operation

Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


10 rows deleted.


2 rows deleted.


Commit complete.


Package altered.


... Truncate operation complete

>>Drop perfstat schema

使用脚本:spdrop.sql

spdrop.sql script contents 
[oracle@DG1 admin]$ vi spdrop.sql
Rem
Rem $Header: spdrop.sql 03-may-00.15:57:17 cdialeri Exp $
Rem
Rem spdrop.sql
Rem
Rem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      spdrop.sql
Rem
Rem    DESCRIPTION
Rem      SQL*PLUS command file drop user, tables and package for
Rem      performance diagnostic tool STATSPACK
Rem
Rem    NOTES
Rem      Note the script connects INTERNAL and so must be run from
Rem      an account which is able to connect internal.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    cdialeri    05/03/00 - 1261813
Rem    cdialeri    02/16/00 - 1191805
Rem    cdialeri    08/13/99 - Drops entire STATSPACK environment
Rem    cdialeri    08/13/99 - Created
Rem

--
--  Drop PERFSTAT's tables and indexes

@@spdtab


--
--  Drop PERFSTAT user

@@spdusr

Create perfstat schema

使用脚本:spcusr.sql($ORACLE_HOME/rdbms/admin/spcusr.sql)

>>Manual statistics perfstat`s objects

execute dbms_stats.gather_schema_stats(ownname=>'PERFSTAT',cascade=>true);

other

  • Upgrade statspack version
升级statspack用到脚本 
[oracle@DG1 admin]$ ls -l spu*
-rw-r--r-- 1 oracle oinstall   588 3月  15 2000 spuexp.par
-rw-r--r-- 1 oracle oinstall 20341 6月  28 2007 spup102.sql
-rw-r--r-- 1 oracle oinstall 23049 5月  31 2005 spup10.sql
-rw-r--r-- 1 oracle oinstall 30938 3月  23 2004 spup816.sql
-rw-r--r-- 1 oracle oinstall 23615 3月  23 2004 spup817.sql
-rw-r--r-- 1 oracle oinstall 19412 3月  23 2004 spup90.sql
-rw-r--r-- 1 oracle oinstall 41707 3月  23 2004 spup92.sql
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/arcer/p/3049345.html