我的编程空间,编程开发者的网络收藏夹
学习永远不晚

部署statspack工具(一)

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

部署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服务


免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

部署statspack工具(一)

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

Fabric:Python远程部署工具

关于FabricFabric是一个Python库和命令行工具,旨在为应用部署和系统管理任务的SSH的流水线式操作,使之更加高效和方便。Fabric提供了一套基本的执行本地和远程shell命令、上传和下载文件的操作,包括一些辅助函数,例如驱动
2023-01-31

Centos7部署nmon监控工具

Nmon是一款计算机性能系统监控工具,因为它免费,体积小,安装简单,耗费资源低,广泛应用于AIX和Linux系统上传软件包
2023-06-05

python 自动化部署工具Fabric

自动化部署工具Fabric简介    Fabric就是一个帮助我们在上线时减少重复/繁琐操作的自动化部署利器,对于缺乏成熟运维平台的众多小公司的运维或开发人员来说,掌握这个工具是有必要的。1. Fabric是什么Fabric官方文档的描述如
2023-01-31

Kubernetes集群部署工具大全

1. kubeadm: 是Kubernetes官方推荐的部署工具,用于快速设置单主机Kubernetes集群。2. kops: 是一个开源工具,用于在AWS上部署和管理生产级别的Kubernetes集群。3. Rancher: 是一个开源的
2023-09-22

python 自动化部署工具-fabri

今天闲来无事,来介绍一下利用fabric 来部署代码包。安装 pip install fabricfabric 默认引用fafile.py,指定执行文件加参数-f,如:fab -H 127.0.0.1 -f fabtest.py test生
2023-01-31

Centos7怎么部署nmon监控工具

今天小编给大家分享一下Centos7怎么部署nmon监控工具的相关知识点,内容详细,逻辑清晰,相信大部分人都还太了解这方面的知识,所以分享这篇文章给大家参考一下,希望大家阅读完这篇文章后有所收获,下面我们一起来了解一下吧。nmon是一种在A
2023-06-28

一键部署工具easy-jenkins,界面友好,操作简单

文章目录 前言一、项目地址二、使用步骤1.项目结构2.启动主类 EasyJenkinsApplication3.安装4.项目启动图 三、功能点介绍1.部署列表1.添加连接(部署jar)1.添加本地项目地址2.添加服务器相关信息
2023-08-22

如何使用docker部署Dashdot工具箱

Docker部署Dashdot工具箱指南Dashdot工具箱是一个开源工具集,用于构建和管理云原生应用程序。使用Docker部署Dashdot具有多项优势,包括简化部署、提升可移植性和增强隔离性。本指南提供了详细的步骤,包括安装Docker、克隆Dashdot存储库、构建Docker镜像、运行容器和访问DashdotGUI。您还可以自定义部署,例如使用持久性存储、Ingress、SSL证书和集成CI/CD管道。有关更多信息,请参阅Dashdot文档和Docker文档。
如何使用docker部署Dashdot工具箱
2024-04-02

Gradio快速部署工具怎么应用

这篇文章主要介绍“Gradio快速部署工具怎么应用”的相关知识,小编通过实际案例向大家展示操作过程,操作方法简单快捷,实用性强,希望这篇“Gradio快速部署工具怎么应用”文章能帮助大家解决问题。1.分享演示share=True通过在方法中
2023-07-05

Kubernetes 应用程序部署工具概览

将应用程序部署到 Kubernetes 就像在 yaml 或 json 中编写一些资源定义并将其与 kubectl 一起应用一样简单,但它也可以自动化很多(并且很复杂)。

怎么使用Docker部署webssh工具sshwifty

这篇文章主要讲解了“怎么使用Docker部署webssh工具sshwifty”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“怎么使用Docker部署webssh工具sshwifty”吧!一、s
2023-07-05

如何使用 ASP 网站部署工具解决网站部署难题

ASP 网站部署工具为 ASP 网站的部署提供了便捷的解决方案,它帮助开发者快速、轻松地将 ASP 网站部署到生产环境,无需手动执行复杂的部署步骤。
如何使用 ASP 网站部署工具解决网站部署难题
2024-02-07

SSH连接与自动化部署工具parami

paramiko是基于Python实现的SSH2远程安全连接,支持认证及密钥方法。可以实现远程命令执行,文件传输,中间SSH代理等功能,相对于Pexpect,封装层次更高。pip install Paramikohttp://www.par
2023-01-31

编程热搜

目录