猎人笔记

像猎人一样捕获ORACLE知识!QQ号:1916066696  群号:367875324

  • 博客访问: 2285638
  • 博文数量: 236
  • 用 户 组: 普通用户
  • 注册时间: 2013-06-16 22:27
个人简介

10年接触数据库,恰逢公司上ERP系统;期间从管理公司的erp数据库到管理公司所有的数据库过程,学习到的不仅仅是做技术,更多的是技术的管理;

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(236)

文章存档

2017年(11)

2016年(14)

2015年(51)

2014年(138)

2013年(22)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
Dataguard从库性能的监控 2017-04-19 15:37:21

分类: Oracle

【前言】Oracle 11G开始支持了active dataguard,这时候从库就可以分担一些主库的读的压力了。这种架构有个问题就是从库的性能压力很难排除出来。有个朋友也是使用从库进行数据的抽取,但是这个从库每隔一段时间就会由于压力过大而导致系统宕机了。在排查问题的时候很多时候需要读取awr报告,但是从库的awr报告是属于主库的,给整个排查增加了难度。还好这个时候从库是可以生成statspack报告的。

这个操作需要进行一些简单的配置,以下的操作都是在主库上面进行的,通过sys用户登录,详细操作如下:

  1. 创建statspack 所需要的schemas:PERFSTAT
  • 设置用户的密码
  • 数据表空间
  • Temp表空间

SQL> @?/rdbms/admin/spcreate.sql

Choose the PERFSTAT user's password

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

Not specifying a password will result in the installation FAILING

 

Enter value for perfstat_password: oracle

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

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

SYSAUX             PERMANENT *

USERS             PERMANENT

 

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

tablespace (identified by *) being used.

 

Enter value for default_tablespace:

 

Using tablespace SYSAUX 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:

 

  1. 创建schemas:stdbyperf
  • 设置用户的密码
  • 数据表空间
  • Temp表空间

SQL> @?/rdbms/admin/sbcreate

Choose the STDBYPERF user's password

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

Not specifying a password will result in the installation FAILING

 

Enter value for stdbyuser_password: oracle

oracle

 

Choose the Default tablespace for the STDBYPERF 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 STDBYPERF users's default tablespace. This is the tablespace

in which the STATSPACK tables and indexes will be created.

 

TABLESPACE_NAME      CONTENTS STATSPACK DEFAULT TABLESPACE

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

SYSAUX             PERMANENT *

USERS             PERMANENT

 

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

tablespace (identified by *) being used.

 

Enter value for default_tablespace:

 

Using tablespace SYSAUX as STDBYPERF default tablespace.

 

Choose the Temporary tablespace for the STDBYPERF 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 STDBYPERF 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.

 

  1. 创建stdbyperfdblink

SQL> @?/rdbms/admin/sbaddins

The following standby instances (TNS_NAME alias) have been configured

for data collection

 

DATABASE    INSTANCE DB LINK PACKAGE

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

TIANJIN      joe     STDBY_LINK_TIANJIN STATSPACK_TIANJIN_joe

 

=== END OF LIST ===

 

THE INSTANCE YOU ARE GOING TO ADD MUST BE ACCESSIBLE AND OPEN READ ONLY

 

Do you want to continue (y/n) ?

Enter value for key:

You entered:

 

Enter the TNS ALIAS that connects to the standby database instance

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

Make sure the alias connects to only one instance (without load balancing).

Enter value for tns_alias: TIANJIN #tnsnames文件配置

You entered: TIANJIN

 

Enter the PERFSTAT user's password of the standby database

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

Performance data will be fetched from the standby database via

database link. We will connect to user PERFSTAT.

Enter value for perfstat_password: oracle # PERFSTAT用户的密码

You entered: oracle

 

  1. stdbyperf收集系统性能信息

    SQL> connect stdbyperf/your_password

    SQL> exec statspack_<db_unique_name>_<instance_name>.snap

[oracle@db01 admin]$ sqlplus stdbyperf/oracle

 

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 2 02:42:09 2017

 

Copyright (c) 1982, 2011, Oracle. All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> exec statspack_TIANJIN_joe.snap

 

PL/SQL procedure successfully completed.

 

  1. stdbyperf收集系统性能信息

SQL> @?/rdbms/admin/sbreport

 

Instances in this Statspack schema

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

DB Unique Name         Instance Name

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

TIANJIN          joe

 

Enter the DATABASE UNIQUE NAME of the standby database to report

Enter value for db_unique_name: TIANJIN

You entered: TIANJIN

 

Enter the INSTANCE NAME of the standby database instance to report

Enter value for inst_name: joe

You entered: joe

 

Specify the number of days of snapshots to choose from

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

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed. Pressing <return> without

specifying a number lists all completed snapshots.

 

Listing all Completed Snapshots

                 Snap

Instance Snap Id Snap Started      Level Comment

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

joe     3 02 Mar 2017 02:42 5

 

  1. 其他删除操作

SQL>@?/rdbms/admin/sbpurge #删除快照

SQL>@?/rdbms/admin/sbdelins   #删除配置

SQL>@?/rdbms/admin/sbdrop   #删除schema

 

  1. 详细报告

DB Unique Name Instance Startup Time Release RAC

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

TIANJIN joe 18-Apr-17 14:05 11.2.0.3.0 NO

 

Host Name: db02 Num CPUs: 2 Phys Memory (MB): 2,000

~~~~

 

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment

~~~~~~~~ ---------- ------------------ -------- --------- -------------------

Begin Snap: 1 01-Mar-17 05:27:28 32 .8

End Snap: 2 01-Mar-17 05:28:26 32 .7

Elapsed: 0.97 (mins)

 

Cache Sizes Begin End

~~~~~~~~~~~ ---------- ----------

Buffer Cache: 356M Std Block Size: 8K

Shared Pool: 224M Log Buffer: 7,032K

 

Load Profile Total Per Second

~~~~~~~~~~~~ ------------------ -----------------

DB time(s): 1.6 0.0

DB CPU(s): 1.2 0.0

Redo MB applied: 1.4 0.0

Logical reads: 1,972.0 34.0

Physical reads: 12.0 0.2

Physical writes: 1,560.0 26.9

User calls: 492.0 8.5

Parses: 211.0 3.6

Hard parses: 163.0 2.8

W/A MB processed: 11.3 0.2

Logons: 1.0 0.0

Executes: 1,095.0 18.9

Rollbacks: 0.0 0.0

 

Instance Efficiency Indicators

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

Buffer Nowait %: 100.00 Redo NoWait %:

Buffer Hit %: 99.85 Optimal W/A Exec %: 100.00

Library Hit %: 78.84 Soft Parse %: 22.75

Execute to Parse %: 80.73 Latch Hit %: 100.00

Parse CPU to Parse Elapsd %: 47.98 % Non-Parse CPU: 45.39

 

Shared Pool Statistics Begin End

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

Memory Usage %: 55.11 64.14

% SQL with executions>1: 71.92 83.57

% Memory for SQL w/exec>1: 75.27 89.08

阅读(558) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册