部署statspack工具（一）

 禁用sga自动管理机制，分配比较小的数据缓冲区(30m)和共享池(70m)空间

1.1关闭SGA自动管理机制

查看是否开启了ASSM

idle>show parameter sga;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 804M

sga_target                           big integer 500M

关闭sga自动管理机制：(部分参数重启数据库生效)

sys@TESTDB12>alter system set memory_target=0;

idle>alter system set sga_target=0;

idle>show parameter memory;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

hi_shared_memory_address             integer     0

memory_max_target                    big integer 804M

memory_target                        big integer 0

shared_memory_address                integer     0

idle>show parameter sga;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 804M

sga_target                           big integer 0

1.2设置数据缓冲区的大小为30m

idle>alter system set db_cache_size=30m;

System altered.

1.3 设置共享池的大小为70m

idle>alter system set shared_pool_size=70m scope=spfile;

System altered.

1.4 验证设置好的数据缓冲区和共享池的大小

idle>show parameter db_cache_size;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_cache_size                        big integer 32M

idle>show parameter shared_pool_size;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

shared_pool_size                     big integer 72M

sys@TESTDB12>select component,current_size/1024/1024 from v$sga_dynamic_components;

COMPONENT                                                        CURRENT_SIZE/1024/1024

---------------------------------------------------------------- ----------------------

shared pool                                                                          80

large pool                                                                            4

java pool                                                                             4

streams pool                                                                          4

DEFAULT buffer cache                                                                 24

KEEP buffer cache                                                                     0

RECYCLE buffer cache                                                                  0

DEFAULT 2K buffer cache                                                               0

DEFAULT 4K buffer cache                                                               0

DEFAULT 8K buffer cache                                                               0

DEFAULT 16K buffer cache                                                              0

DEFAULT 32K buffer cache                                                              0

Shared io Pool                                                                        0

ASM Buffer Cache                                                                      0

14 rows selected.

2.部署statspack

2.1创建一个专门用于statspack的表空间tools

idle>create tablespace tools

  2  datafile '/u01/app/oracle/oradata/TestDB12/tools01.dbf'

  3  size 300m;

Tablespace created.

2.2以sysdba身份执行创建prefstat对象的脚本

sql> @?/rdbms/admin/spcreate.sql                                    /sppurge.sql是删除快照

Choose the PERFSTAT user's passWord

-----------------------------------

Not specifying a password will result in the installation FaiLING

Enter value for perfstat_password: oracle

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

------------------------------ --------- ----------------------------

EXAMPLE                        PERMANENT

SYSAUX                         PERMANENT *

TOOLS                          PERMANENT

USERS                          PERMANENT

Pressing <return> will result in STATSPACK's recommended default

tablespace (identified by *) being used.

Enter value for default_tablespace: tools

Using tablespace TOOLS 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 <return> will result in the database's default Temporary

tablespace (identified by *) being used.

Enter value for temporary_tablespace:回车

… …

NOTE:

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

Statstack安装完成。

2.3设置statspack自动产生快照的间隔时间为15分钟（一天有24小时96个15分钟）

{oracle@Redhat55.cuug.net:/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin}$ vi /u01/app/oracle/product/11.2.0/dbhome_1/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/96,'MI'), 'trunc(SYSDATE+1/96,''MI'')', TRUE, :instno);

  commit;

end;

2.4设置快照的默认级别为7级

perfstat@TESTDB12>exec statspack.modify_statspack_parameter(i_snap_level=>7);

PL/SQL procedure successfully completed.

创建序列

sys@TESTDB12>alter user scott identified by tiger;

User altered.

sys@TESTDB12>conn scott/tiger;

Connected.

scott@TESTDB12>CREATE SEQUENCE emp2_empno

  2  INCREMENT BY 1

  3  START WITH 1

  4  MAXVALUE 100000000

  5  CACHE 10000

  6  NOCYCLE;

Sequence created.

3搭建查询环境

3.1创建新表并插入数据

scott@TESTDB12>create table emp2 as select * from emp where 1=2;

Table created.

scott@TESTDB12>alter table emp2 modify empno number(10);

Table altered.

scott@TESTDB12>alter table emp2 modify ename varchar(30);

Table altered.

将emp2 表设为nologging

scott@TESTDB12>alter table emp2 nologging;

Table altered.

插入2千万行数据：

scott@TESTDB12>begin

  2  for i in 1..20000000 loop

  3  insert into emp2

  4  values (emp2_empno.nextval,'cuug'||i,'SALESMAN',7698,sysdate,1600,300,30);

  5  if mod(i,1000)=0 then

  6  commit;

  7  end if;

  8  end loop;

  9  commit;

 10  end;

 11  /

3.2编写查询业务脚本

{oracle@Redhat55.cuug.net:/home/oracle}$ mkdir -p script/bin/

{oracle@Redhat55.cuug.net:/home/oracle/script/bin}$  vi script/bin/share_pool_sql_1.sh

#!/bin/bash

CNT=1

while [ $CNT -lt 20000000 ]

do

sqlplus scott/tiger <<EOF

select * from emp2 where empno=$CNT;

exit

EOF

CNT=`expr $CNT + 1`

done 

4.运行查询业务脚本并产生statspack报告

4.1运行查询业务脚本并启动statspack的自动快照

{oracle@Redhat55.cuug.net:/home/oracle/script/bin}$ sh share_pool_sql_1.sh

sys@TESTDB12>conn perfstat/oracle

Connected.

perfstat@TESTDB12>@?/rdbms/admin/spauto

----------------------------------------------华丽的分割线----------------------------------------------------------------------------------

perfstat@TESTDB12>Rem

perfstat@TESTDB12>Rem $Header: spauto.sql 16-feb-00.16:49:37 cdialeri Exp $

perfstat@TESTDB12>Rem

perfstat@TESTDB12>Rem spauto.sql

perfstat@TESTDB12>Rem

perfstat@TESTDB12>Rem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.

perfstat@TESTDB12>Rem

perfstat@TESTDB12>Rem    NAME

perfstat@TESTDB12>Rem      spauto.sql

perfstat@TESTDB12>Rem

perfstat@TESTDB12>Rem    DESCRIPTION

perfstat@TESTDB12>Rem      SQL*PLUS command file to automate the collection of STATPACK

perfstat@TESTDB12>Rem      statistics.

perfstat@TESTDB12>Rem

perfstat@TESTDB12>Rem    NOTES

perfstat@TESTDB12>Rem      Should be run as the STATSPACK owner, PERFSTAT.

perfstat@TESTDB12>Rem      Requires job_queue_processes init.ora parameter to be

perfstat@TESTDB12>Rem      set to a number >0 before automatic statistics gathering

perfstat@TESTDB12>Rem      will run.

perfstat@TESTDB12>Rem

perfstat@TESTDB12>Rem    MODIFIED   (MM/DD/YY)

perfstat@TESTDB12>Rem    cdialeri    02/16/00 - 1191805

perfstat@TESTDB12>Rem    cdialeri    12/06/99 - 1059172, 1103031

perfstat@TESTDB12>Rem    cdialeri    08/13/99 - Created

perfstat@TESTDB12>Rem

perfstat@TESTDB12>

perfstat@TESTDB12>

perfstat@TESTDB12>spool spauto.lis

perfstat@TESTDB12>

perfstat@TESTDB12>--

perfstat@TESTDB12>--  Schedule a snapshot to be run on this instance every hour, on the hour

perfstat@TESTDB12>

perfstat@TESTDB12>variable jobno number;

perfstat@TESTDB12>variable instno number;

perfstat@TESTDB12>begin

  2    select instance_number into :instno from v$instance;

  3    dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/96,'MI'), 'trunc(SYSDATE+1/96,''MI'')', TRUE, :instno);

  4    commit;

  5  end;

  6  /

PL/SQL procedure successfully completed.

perfstat@TESTDB12>

perfstat@TESTDB12>

perfstat@TESTDB12>prompt

perfstat@TESTDB12>prompt        Job number for automated statistics collection for this instance

Job number for automated statistics collection for this instance

perfstat@TESTDB12>prompt        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

perfstat@TESTDB12>prompt        Note that this job number is needed when modifying or removing

Note that this job number is needed when modifying or removing

perfstat@TESTDB12>prompt        the job:

the job:

perfstat@TESTDB12>print jobno

     JOBNO

----------

        23

perfstat@TESTDB12>

perfstat@TESTDB12>prompt

perfstat@TESTDB12>prompt        Job queue process

Job queue process

perfstat@TESTDB12>prompt        ~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~

perfstat@TESTDB12>prompt        Below is the current setting of the job_queue_processes init.ora

Below is the current setting of the job_queue_processes init.ora

perfstat@TESTDB12>prompt        parameter - the value for this parameter must be greater

parameter - the value for this parameter must be greater

perfstat@TESTDB12>prompt        than 0 to use automatic statistics gathering:

than 0 to use automatic statistics gathering:

perfstat@TESTDB12>show parameter job_queue_processes

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

job_queue_processes                  integer     1000

perfstat@TESTDB12>prompt

perfstat@TESTDB12>

perfstat@TESTDB12>prompt

perfstat@TESTDB12>prompt        Next scheduled run

Next scheduled run

perfstat@TESTDB12>prompt        ~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~

perfstat@TESTDB12>prompt        The next scheduled run for this job is:

The next scheduled run for this job is:

perfstat@TESTDB12>select job, next_date, next_sec

  2    from user_jobs

  3   where job = :jobno;

       JOB NEXT_DATE NEXT_SEC

---------- --------- --------------------------------

        23 28-JUL-14 04:31:00

1 row selected.

----------------------------------------------华丽的分割线----------------------------------------------------------------------------------

4.2验证statspack自动生成的报告

perfstat@TESTDB12>alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';

perfstat@TESTDB12>select snap_id,snap_time,snap_level from stats$snapshot order by snap_time;

   SNAP_ID SNAP_TIME           SNAP_LEVEL

---------- ------------------- ----------

         1 2014-07-28 23:07:05          7

        11 2014-07-28 04:46:00          7

        12 2014-07-28 05:01:04          7

        13 2014-07-28 05:16:02          7

        14 2014-07-28 05:31:04          7

生成statspack分析报告

SQL> @?/rdbms/admin/spreport

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap:21

Enter value for end_snap: 31

Enter value for report_name:

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 31

Enter value for end_snap: 32

Enter value for report_name:

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 32

Enter value for end_snap: 33

Enter value for report_name:

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 33

Enter value for end_snap: 34

Enter value for report_name:

4.3取消statspack自动生成快照

perfstat@TESTDB12>select job,log_user,last_date,next_date from user_jobs;

       JOB LOG_USER                       LAST_DATE

---------- ------------------------------ -------------------

NEXT_DATE

-------------------

        23 PERFSTAT                       2014-07-28 05:31:04

2014-07-28 05:46:00

perfstat@TESTDB12>exec dbms_job.remove('23');

5.启samba共享并对生成的statspack报告copy到windows主机

[root@James ~]# service smb start        //启动samba服务

[root@James ~]# chkconfig smb on    //开机自动启动

{root@Redhat55.cuug.net:/root}# smbpasswd -a oracle //将系统oracle用户添加到samba服务