部署statspack工具（一）

来源：互联网发布：淘宝店铺数据分析app 编辑：程序博客网时间：2024/04/27 14:08

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

1.1关闭SGA自动管理机制

查看是否开启了ASSM

idle>showparameter 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>altersystem set memory_target=0;

idle>altersystem set sga_target=0;

idle>showparameter 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>showparameter 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>altersystem set db_cache_size=30m;

Systemaltered.

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

idle>altersystem set shared_pool_size=70m scope=spfile;

Systemaltered.

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

idle>showparameter db_cache_size;

NAME TYPE VALUE

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

db_cache_size big integer 32M

idle>showparameter shared_pool_size;

NAME TYPE VALUE

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

shared_pool_size big integer 72M

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

COMPONENT CURRENT_SIZE/1024/1024

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

sharedpool 80

largepool 4

javapool 4

streamspool 4

DEFAULTbuffer cache 24

KEEPbuffer cache 0

RECYCLEbuffer cache 0

DEFAULT2K buffer cache 0

DEFAULT4K buffer cache 0

DEFAULT8K buffer cache 0

DEFAULT16K buffer cache 0

DEFAULT32K buffer cache 0

SharedIO Pool 0

ASMBuffer Cache 0

14rows selected.

2.部署statspack

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

idle>createtablespace tools

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

3 size300m;

Tablespacecreated.

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

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

Choose the PERFSTAT user's password

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

Not specifying a password will result in theinstallation FAILING

Enter value forperfstat_password: oracle

Choose the Default tablespace for the PERFSTATuser

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

Below is the list of online tablespaces in thisdatabase which can

store user data. Specifying the SYSTEM tablespace for the user's

default tablespace will result in the installationFAILING, as

using SYSTEM for performance data is notsupported.

Choose the PERFSTAT users's defaulttablespace. This is the tablespace

in which the STATSPACK tables and indexes will becreated.

TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE

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

EXAMPLE PERMANENT

SYSAUX PERMANENT *

TOOLS PERMANENT

USERS PERMANENT

Pressing <return> will result in STATSPACK'srecommended default

tablespace (identified by *) being used.

Enter value fordefault_tablespace: tools

Using tablespace TOOLS as PERFSTAT defaulttablespace.

Choose the Temporary tablespace for the PERFSTATuser

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

Below is the list of online tablespaces in thisdatabase which can

store temporary data (e.g. for sortworkareas). Specifying the SYSTEM

tablespace for the user's temporary tablespacewill result in the

installation FAILING, as using SYSTEM forworkareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE

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

TEMP TEMPORARY *

Pressing <return> will result in thedatabase's default Temporary

tablespace (identified by *) being used.

Enter valuefor temporary_tablespace:回车

… …

NOTE:

SPCPKG complete. Please check spcpkg.lis for anyerrors.

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

selectinstance_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>execstatspack.modify_statspack_parameter(i_snap_level=>7);

PL/SQL procedure successfully completed.

创建序列

sys@TESTDB12>alteruser scott identified by tiger;

Useraltered.

sys@TESTDB12>connscott/tiger;

Connected.

scott@TESTDB12>CREATESEQUENCE emp2_empno

2 INCREMENT BY 1

3 START WITH 1

4 MAXVALUE 100000000

5 CACHE 10000

6 NOCYCLE;

Sequencecreated.

3搭建查询环境

3.1创建新表并插入数据

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

Tablecreated.

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

Tablealtered.

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

Tablealtered.

将emp2 表设为nologging

scott@TESTDB12>altertable emp2 nologging;

Tablealtered.

插入2千万行数据：

scott@TESTDB12>begin

2 fori in 1..20000000 loop

3 insert into emp2

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

5 ifmod(i,1000)=0 then

6 commit;

7 endif;

8 endloop;

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 ]

sqlplusscott/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>connperfstat/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>Remspauto.sql

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 thecollection 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.oraparameter to be

perfstat@TESTDB12>Rem set to a number >0 before automaticstatistics 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>spoolspauto.lis

perfstat@TESTDB12>

perfstat@TESTDB12>--

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

perfstat@TESTDB12>

perfstat@TESTDB12>variablejobno number;

perfstat@TESTDB12>variableinstno 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/SQLprocedure successfully completed.

perfstat@TESTDB12>

perfstat@TESTDB12>prompt

perfstat@TESTDB12>prompt Job number for automated statisticscollection for this instance

Jobnumber for automated statistics collection for this instance

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

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

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

Notethat this job number is needed when modifying or removing

perfstat@TESTDB12>prompt the job:

thejob:

perfstat@TESTDB12>printjobno

JOBNO

----------

perfstat@TESTDB12>

perfstat@TESTDB12>prompt

perfstat@TESTDB12>prompt Job queue process

Jobqueue process

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

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

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

Belowis the current setting of the job_queue_processes init.ora

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

parameter- the value for this parameter must be greater

perfstat@TESTDB12>prompt than 0 to use automatic statisticsgathering:

than0 to use automatic statistics gathering:

perfstat@TESTDB12>showparameter 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

Nextscheduled run

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

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

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

Thenext scheduled run for this job is:

perfstat@TESTDB12>selectjob, next_date, next_sec

2 from user_jobs

3 where job = :jobno;

JOB NEXT_DATE NEXT_SEC

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

23 28-JUL-14 04:31:00

1row selected.

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

4.2验证statspack自动生成的报告

perfstat@TESTDB12>altersession set nls_date_format='yyyy-mm-dd hh24:mi:ss';

perfstat@TESTDB12>selectsnap_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 forbegin_snap:21

Enter value forend_snap: 31

Enter value for report_name:

Specify the Begin and End Snapshot Ids

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

Enter value forbegin_snap: 31

Enter value forend_snap: 32

Enter value for report_name:

Specify the Begin and End Snapshot Ids

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

Enter value forbegin_snap: 32

Enter value forend_snap: 33

Enter value for report_name:

Specify the Begin and End Snapshot Ids

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

Enter value forbegin_snap: 33

Enter value forend_snap: 34

Enter value for report_name:

4.3取消statspack自动生成快照

perfstat@TESTDB12>selectjob,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-2805:46:00

perfstat@TESTDB12>execdbms_job.remove('23');

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

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

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

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

0 0