【前言】Oracle 11G开始支持了active dataguard，这时候从库就可以分担一些主库的读的压力了。这种架构有个问题就是从库的性能压力很难排除出来。有个朋友也是使用从库进行数据的抽取，但是这个从库每隔一段时间就会由于压力过大而导致系统宕机了。在排查问题的时候很多时候需要读取awr报告，但是从库的awr报告是属于主库的，给整个排查增加了难度。还好这个时候从库是可以生成statspack报告的。
这个操作需要进行一些简单的配置，以下的操作都是在主库上面进行的，通过sys用户登录，详细操作如下：
- 创建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: |
- 创建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. |
- 创建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 |
-
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. |
- 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 |
- 其他删除操作
SQL>@?/rdbms/admin/sbpurge #删除快照
SQL>@?/rdbms/admin/sbdelins #删除配置
SQL>@?/rdbms/admin/sbdrop #删除schema
- 详细报告
|
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 |