Dataguard从库性能的监控

共计 7150 个字符，预计需要花费 18 分钟才能阅读完成。

【前言】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. 创建 stdbyperf 的 dblink

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

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-05/143389.htm