通过statspack诊断物理dg数据库性能方法

  今天是2014-04-23,继续整理一下dg的内容,顺便学习温习。对于物理dg来说,备库在11g是可以以只读模式打开,可以提供报表查询功能,往往我们在分析备库
性能的时候需要查询大量的v$视图,那么在11G之后可以在主库获得备库的statspack性能数据,便于分析。现在就看一下如果在主库上存储备用数据的statspack数
据信息。
 很久之前在创建过statspack,但是随着awr的引入该工具也被取代,但对于dg确实不错的选择。所有的脚本在$ORACLE_HOME/admin下面以sb开头的脚本文件。
 eg:

[oracle@dg-one admin]$ echo $ORACLE_HOME/admin
/u01/app/oracle/product/11.2.0/db_1/admin
[oracle@dg-one admin]$ ls -l sb*
-rw-r--r-- 1 oracle oinstall   2762 Jan 28  2010 sbaddins.sql
-rw-r--r-- 1 oracle oinstall 203822 Mar  6  2012 sbcpkg.sql
-rw-r--r-- 1 oracle oinstall    813 Jun 14  2007 sbcreate.sql
-rw-r--r-- 1 oracle oinstall  85599 Mar  6  2012 sbctab.sql
-rw-r--r-- 1 oracle oinstall   5744 Feb  8  2012 sbcusr.sql
-rw-r--r-- 1 oracle oinstall   3102 Jan 28  2010 sbdelins.sql
-rw-r--r-- 1 oracle oinstall   1936 May 19  2010 sbdoc.txt
-rw-r--r-- 1 oracle oinstall    684 Jun 10  2007 sbdrop.sql
-rw-r--r-- 1 oracle oinstall   4482 Mar  6  2012 sbdtab.sql
-rw-r--r-- 1 oracle oinstall    719 Jun 10  2007 sbdusr.sql
-rw-r--r-- 1 oracle oinstall   1025 Sep 24  2009 sblisins.sql
-rw-r--r-- 1 oracle oinstall   6062 Jan 28  2010 sbpurge.sql
-rw-r--r-- 1 oracle oinstall   4929 Jun 10  2007 sbrepcon.sql
-rw-r--r-- 1 oracle oinstall 259708 Mar  6  2012 sbrepins.sql
-rw-r--r-- 1 oracle oinstall    440 Jun 10  2007 sbreport.sql
-rw-r--r-- 1 oracle oinstall    548 Sep 24  2009 sbrepsql.sql
-rw-r--r-- 1 oracle oinstall  33224 Jan 28  2010 sbrsqins.sql
-rw-r--r-- 1 oracle oinstall   6909 Nov 10  2011 sbup1101.sql
-rw-r--r-- 1 oracle oinstall   4651 Aug 13  2010 sbup11201.sql
[oracle@dg-one admin]$

 

那么要做的首先是创建的statspack模式,这个过程分两步:
1、就是创建stdbyuser指定密码,指定存储表空间和临时表空间,以及创建相应的表和同义词
2、将备库加入进来
过程如下:
创建存储数据的表空间:

SQL> col name for a60
SQL> r   
  1* select name from v$datafile

NAME
------------------------------------------------------------
/u01/app/oracle/oradata/dg/system01.dbf
/u01/app/oracle/oradata/dg/sysaux01.dbf
/u01/app/oracle/oradata/dg/undotbs01.dbf
/u01/app/oracle/oradata/dg/users01.dbf

SQL> create tablespace statspack_stdby datafile '/u01/app/oracle/oradata/dg/stdby_stat.dbf' size 100M autoextend on next 10M maxsize 150M extent management local segment space management auto;

Tablespace created.

SQL> show parameter standby_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

创建statspack;
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: Amy
Amy


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
------------------------------ --------- ----------------------------
STATSPACK_STDBY                PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: statspack_stdby

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

Using tablespace TEMP as PERFSTAT temporary tablespace.


... Creating PERFSTAT user


... Installing required packages


... Creating views


... Granting privileges

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.

SQL> 
SQL> --
SQL> --  Build the tables and synonyms
SQL> connect perfstat/&&perfstat_password
Connected.
SQL> @@spctab
SQL> Rem
SQL> Rem $Header: rdbms/admin/spctab.sql /st_rdbms_11.2.0/3 2012/03/06 15:07:48 shsong Exp $
SQL> Rem
SQL> Rem spctab.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2012, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         spctab.sql
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         SQL*PLUS command file to create tables to hold
SQL> Rem         start and end "snapshot" statistical information
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Should be run as STATSPACK user, PERFSTAT
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    kchou       08/11/10 - Bug#9800868 - Add Missing Idle Events for
SQL> Rem                           11.2.0.2for Statspack & Standby Statspack
SQL> Rem    kchou       08/11/10 - Bug#9800868 - Add missing idle events to 11.2.0.2
SQL> Rem    cgervasi    05/13/09 - add idle event: cell worker idle
SQL> Rem    cgervasi    04/02/09 - bug8395154: missing idle events
SQL> Rem    rhlee       02/22/08 -
> Rem    cdgreen     03/14/07 - 11 F2
SQL> Rem    shsong      06/14/07 - Add idle events
SQL> Rem    cdgreen     02/28/07 - 5908354
SQL> Rem    cdgreen     04/26/06 - 11 F1
SQL> Rem    cdgreen     06/26/06 - Increase column length
SQL> Rem    cdgreen     05/10/06 - 5215982
SQL> Rem    cdgreen     05/24/05 - 4246955
SQL> Rem    cdgreen     04/18/05 - 4228432
SQL> Rem    cdgreen     03/08/05 - 10gR2 misc
SQL> Rem    vbarrier    02/18/05 - 4081984
SQL> Rem    cdgreen     10/29/04 - 10gR2_sqlstats
SQL> Rem    cdgreen     07/16/04 - 10gR2
SQL> Rem    cdialeri    03/25/04 - 3516921
SQL> Rem    vbarrier    02/12/04 - 3412853
SQL> Rem    cdialeri    12/04/03 - 3290482
SQL> Rem    cdialeri    11/05/03 - 3202706
SQL> Rem    cdialeri    10/14/03 - 10g - streams - rvenkate
SQL> Rem    cdialeri    08/05/03 - 10g F3
SQL> Rem    cdialeri    02/27/03 - 10g F2: baseline, purge
SQL> Rem    vbarrier    02/25/03 - 10g RAC
SQL> Rem    cdialeri    11/15/02 - 10g F1
SQL> Rem    cdialeri    09/27/02 - sleep4
SQL> Rem    vbarrier    03/20/02 - 2143634
SQL> Rem    vbarrier    03/05/02 - Segment Statistics
SQL> Rem    cdialeri    02/07/02 - 2218573
SQL> Rem    cdialeri    01/30/02 - 2184717
SQL> Rem    cdialeri    01/11/02 - 9.2 - features 2
SQL> Rem    cdialeri    11/30/01 - 9.2 - features 1
SQL> Rem    cdialeri    04/22/01 - Undostat changes
SQL> Rem    cdialeri    03/02/01 - 9.0
SQL> Rem    cdialeri    09/12/00 - sp_1404195
SQL> Rem    cdialeri    04/07/00 - 1261813
SQL> Rem    cdialeri    03/20/00 - Support for purge
SQL> Rem    cdialeri    02/16/00 - 1191805
SQL> Rem    cdialeri    01/26/00 - 1169401
SQL> Rem    cdialeri    11/01/99 - Enhance, 1059172
SQL> Rem    cmlim       07/17/97 - Added STATS$SQLAREA to store top sql stmts
SQL> Rem    gwood       10/16/95 - Version to run as sys without using many views
SQL> Rem    cellis.uk   11/15/89 - Created
SQL> Rem
SQL> 
SQL> set showmode off echo off;

If this script is automatically called from spcreate (which is
the supported method), all STATSPACK segments will be created in
the PERFSTAT user's default tablespace.

Using statspack_stdby tablespace to store Statspack objects

... Creating STATS$SNAPSHOT_ID Sequence

Sequence created.


Synonym created.

... Creating STATS$... tables

Table created.


Synonym created.

Table created.

............................................
Table created.
SQL> set echo off;
Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

SQL> exit


创建备库的基本statspacke模式:

[oracle@dg-one ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 23 14:34:05 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Choose the STDBYPERF user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for stdbyuser_password: Amy
Amy


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
------------------------------ --------- ----------------------------
STATSPACK_STDBY                PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: statspack_stdby

Using tablespace STATSPACK_STDBY 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.

Enter value for temporary_tablespace: 

Using tablespace TEMP as STDBYPERF temporary tablespace.


... Creating STDBYPERF user


... Installing required packages


... Granting privileges

NOTE:
SBCUSR complete. Please check sbcusr.lis for any errors.

SQL> 
SQL> connect stdbyperf/&&stdbyuser_password
Connected.
SQL> 
SQL> --
SQL> --  Build the tables
SQL> @@sbctab
SQL> Rem
SQL> Rem sbctab.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2012, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         sbctab.sql
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         SQL*PLUS command file to create tables to hold standby database
SQL> Rem         start and end "snapshot" statistical information
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Should be run as Standby Statspack user, stdbyperf
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    kchou       11/09/11 - Backport Bug#9695145 Missing Idle Events to
SQL> Rem                           Standby Statspack - RFI 10431923 Release 11.2.0.4
SQL> Rem    kchou       11/09/11 - Backport kchou_bug-9695145 from main
SQL> Rem    kchou       11/09/11 - Remove synonym STATS$IDLE_EVENT
SQL> Rem    kchou       08/11/10 - Bug#9800868 - Add Missing Idle Events for
SQL> Rem                           11.2.0.2for Statspack & Standby Statspack
SQL> Rem    kchou       08/11/10 - Bug#9800868 - Add missing idle events to 11.2.0.2
SQL> Rem    shsong      01/28/10 - add stats$lock_type
SQL> Rem    shsong      08/18/09 - Add db_unique_name
SQL> Rem    shsong      02/02/09 - remove stats$kccfn etc
SQL> Rem    shsong      07/10/08 - add stats$kccfn etc
SQL> Rem    shsong      02/28/07 - Fix bug
SQL> Rem    wlohwass    12/04/06 - Created, based on spctab.sql
SQL> Rem
SQL> 
SQL> set showmode off echo off;

If this script is automatically called from sbcreate (which is
the supported method), all STATSPACK segments will be created in
the STDBYPERF user default tablespace.

Using statspack_stdby tablespace to store Statspack objects

... Creating STATS$SNAPSHOT_ID Sequence

Sequence created.

... Creating STATS$... tables

Table created.


Table created.


Table created.


1 row created.
....
1 row created.


Commit complete.


View created.


NOTE:
SBCTAB complete. Please check sbctab.lis for any errors.

SQL> 
SQL> 
SQL> --
SQL> --  Add a standby database instance to the configuration
SQL> @@sbaddins
SQL> Rem
SQL> Rem sbaddins.sql
SQL> Rem
SQL> Rem Copyright (c) 2006, 2010, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         sbaddins.sql - Standby Database Statistics Collection Add Instance
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         SQL*PLUS command file which adds a standby database instance
SQL> Rem         for performance data collection
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Must be run from standby perfstat owner, STDBYPERF
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    shsong      01/28/10 - remove v$lock_type
SQL> Rem    shsong      08/18/09 - add db_unique_name to stats$standby_config
SQL> Rem    shsong      03/04/07 - fix bug
SQL> Rem    wlohwass    12/04/06 - Created
SQL> Rem
SQL> 
SQL> set echo off;

The following standby instances (TNS_NAME alias) have been configured
for data collection

=== 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: y
You entered: y


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: DG2
You entered: DG2


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: Amy
You entered: Amy

... Creating database link

... Selecting database unique name

Database
------------------------------
dg2

... Selecting instance name

Instance
------------
dg2





... Creating package

Creating Package STATSPACK_dg2_dg2..
No errors.
Creating Package Body STATSPACK_dg2_dg2..
No errors.

NOTE:
SBCPKG complete. Please check sbcpkg.lis for any errors.

SQL> 
SQL> undefine key tns_alias inst_name perfstat_password pkg_name db_unique_name
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 


 

至此完成了逻辑备库的dg性能分析工具的statspack的创建。

那么如何使用呢?
需要在主库上执行所创建的包,来收集备库信息,如下:

SQL> connect stdbyperf/Amy
Connected.
SQL> exec statspack_dg2_dg2.snap;
SQL> 
SQL> 
SQL> 
SQL> exec statspack_dg2_dg2.snap;
SQL> @?/rdbms/admin/sbreport.sql
SQL> Rem
SQL> Rem sbreport.sql
SQL> Rem
SQL> Rem Copyright (c) 2007, Oracle. All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         sbreport.sql
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         This script calls sbrepins.sql to produce standby statspack report
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Must run as the standby statspack owner, stdbyperf
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    shsong      02/15/07 - fix bug
SQL> Rem    wlohwass    12/04/06 - Created, based on spreport.sql
SQL> 
SQL> 
SQL> @@sbrepins
SQL> Rem
SQL> Rem sbrepins.sql
SQL> Rem
SQL> Rem Copyright (c) 2001, 2012, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         sbrepins.sql - StandBy statspack REPort INStance
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         SQL*Plus command file to report on differences between
SQL> Rem         values recorded in two snapshots.
SQL> Rem
SQL> Rem         This script requests the user to provide database unique name
SQL> Rem         and instance number of the instance to report on, then produce
SQL> Rem         the standby statspack report.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Run as the standby statspack owner, stdbyperf
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    shsong      01/25/10 - Bug 9307098
SQL> Rem    shsong      08/21/09 - use db_unique_name as primary key
SQL> Rem    shsong      06/16/08 - add active_agents to Managed Standby Stats
SQL> Rem    shsong      02/05/07 - Add stats for recovery_progress etc
SQL> Rem    shsong      02/04/07 - Created
SQL> Rem
SQL> 
SQL> set echo off;


Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Unique Name                 Instance Name
------------------------------ ----------------
dg2                            dg2

Enter the DATABASE UNIQUE NAME of the standby database to report
Enter value for db_unique_name: dg2
You entered: dg2

Enter the INSTANCE NAME of the standby database instance to report
Enter value for inst_name: dg2
You entered: dg2


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
------------ --------- ----------------- ----- --------------------
dg2                  1 23 Apr 2014 14:36     5
                     2 23 Apr 2014 14:46     5



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End   Snapshot Id specified: 2



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sb_dg2_dg2_1_2.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: standby_report     

Using the report name standby_report

STATSPACK Statistics Report for Physical Standby

Database
~~~~~~~~
DB Unique Name                 Instance     Startup Time    Release     RAC
------------------------------ ------------ --------------- ----------- ---
dg2                            dg2          23-Apr-14 14:22 11.2.0.4.0  NO

Host  Name:   dg-two           Num CPUs:    1        Phys Memory (MB):      997
~~~~

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- -------------------
Begin Snap:          1 23-Apr-14 14:36:59       29        .9
  End Snap:          2 23-Apr-14 14:46:05       29        .9
   Elapsed:                9.10 (mins)

Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:       176M              Std Block Size:         8K
     Shared Pool:       100M                  Log Buffer:     4,368K

Load Profile                   Total         Per Second
~~~~~~~~~~~~       ------------------  -----------------
      DB time(s):               10.9                0.0
       DB CPU(s):                2.6                0.0
 Redo MB applied:                1.6                0.0
   Logical reads:           29,038.0               53.2
  Physical reads:               43.0                0.1
 Physical writes:            1,587.0                2.9
      User calls:            1,009.0                1.9
          Parses:            1,445.0                2.7
     Hard parses:              899.0                1.7
W/A MB processed:               15.1                0.0
          Logons:                1.0                0.0
        Executes:            9,526.0               17.5
       Rollbacks:                0.0                0.0

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:
            Buffer  Hit   %:   99.88  Optimal W/A Exec %:  100.00
            Library Hit   %:   81.20        Soft Parse %:   37.79
         Execute to Parse %:   84.83         Latch Hit %:   99.97
Parse CPU to Parse Elapsd %:   44.96     % Non-Parse CPU:   36.12

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   88.16   88.60
    % SQL with executions>1:   82.35   55.45
  % Memory for SQL w/exec>1:   84.19   76.76

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
Standby redo I/O                                   543           8     15   37.6
control file parallel write                        587           7     12   33.3
CPU time                                                         3          15.8
db file async I/O submit                            24           1     52    5.9
db file sequential read                             31           1     23    3.3
          -------------------------------------------------------------

Host CPU  (CPUs: 1)
~~~~~~~~              Load Average
                      Begin     End      User  System    Idle     WIO     WCPU
                    ------- -------   ------- ------- ------- ------- --------
                       0.55    0.47      3.05    1.62   95.20    0.80

Note: There is a 20% discrepancy between the OS Stat total CPU time and
      the total CPU time estimated by Statspack
          OS Stat CPU time: 436(s) (BUSY_TIME + IDLE_TIME)
        Statspack CPU time: 546(s) (Elapsed time * num CPUs in end snap)

Instance CPU
~~~~~~~~~~~~
              % of total CPU for Instance:    1.21
              % of busy  CPU for Instance:   25.23
  %DB time waiting for CPU - Resource Mgr:

Memory Statistics                       Begin          End
~~~~~~~~~~~~~~~~~                ------------ ------------
                  Host Mem (MB):        996.9        996.9
                   SGA use (MB):        298.7        298.7
                   PGA use (MB):        240.4        240.5
    % Host Mem used for SGA+PGA:         54.1         54.1
          -------------------------------------------------------------

Recovery Progress Stats  DB/Inst: dg2/dg2  End Snap: 2
-> End Snapshot Time: 23-Apr-14 14:46:05
-> ordered by Recovery Start Time desc, Units, Item asc

Recovery Start Time Item                       Sofar Units   Redo Timestamp
------------------- ----------------- -------------- ------- ------------------
23-Apr-14 14:30:54  Log Files                      2 Files
23-Apr-14 14:30:54  Active Apply Rate            575 KB/sec
23-Apr-14 14:30:54  Average Apply Rat             23 KB/sec
23-Apr-14 14:30:54  Maximum Apply Rat            582 KB/sec
23-Apr-14 14:30:54  Redo Applied                  21 Megabyt
23-Apr-14 14:30:54  Last Applied Redo              0 SCN+Tim 23-Apr-14 14:46:06
23-Apr-14 14:30:54  Active Time                   26 Seconds
23-Apr-14 14:30:54  Apply Time per Lo              3 Seconds
23-Apr-14 14:30:54  Checkpoint Time p              0 Seconds
23-Apr-14 14:30:54  Elapsed Time                 911 Seconds
23-Apr-14 14:30:54  Standby Apply Lag              0 Seconds
          -------------------------------------------------------------

Time Model System Stats  DB/Inst: dg2/dg2  Snaps: 1-2
-> Ordered by % of DB time desc, Statistic name

Statistic                                       Time (s) % DB time
----------------------------------- -------------------- ---------
parse time elapsed                                   2.8      25.4
hard parse elapsed time                              2.7      25.0
DB CPU                                               2.5      23.4
sql execute elapsed time                             2.0      18.6
hard parse (sharing criteria) elaps                  0.3       2.5
repeated bind elapsed time                           0.0        .0
DB time                                             10.9
background elapsed time                             14.1
background cpu time                                  2.7
          -------------------------------------------------------------

Wait Events  DB/Inst: dg2/dg2  Snaps: 1-2
-> s - second, cs - centisecond,  ms - millisecond, us - microsecond
-> %Timeouts:  value of 0 indicates value was < .5%.  Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)

                                                                           Avg
                                                       %Time Total Wait   wait
Event                                           Waits  -outs   Time (s)   (ms)
---------------------------------------- ------------ ------ ---------- ------
Standby redo I/O                                  543      0          8     15
control file parallel write                       587      0          7     12
db file async I/O submit                           24      0          1     52
db file sequential read                            31      0          1     23
db file parallel read                               4      0          0     94
RFS write                                         613      0          0      0
Disk file operations I/O                        1,096      0          0      0
library cache lock                                  8      0          0     10
control file sequential read                    5,657      0          0      0
RFS dispatch                                      622      0          0      0
Data file init write                               24      0          0      1
log file sequential read                        1,087      0          0      0
os thread startup                                   1      0          0     17
RFS random i/o                                    543      0          0      0
RFS ping                                            9      0          0      1
latch free                                        104      0          0      0
db file single write                                2      0          0      2
SQL*Net more data to client                         6      0          0      0
latch: shared pool                                  1      0          0      2
SQL*Net message from client                       899      0      1,616   1798
DIAG idle wait                                  1,088    100      1,089   1001
MRP redo arrival                                  623      0        544    873
shared server idle wait                            18    100        540  30007
SQL*Net more data from client                      11      0          0      4
SQL*Net message to client                         899      0          0      0
          -------------------------------------------------------------

Background Wait Events  DB/Inst: dg2/dg2  Snaps: 1-2
-> %Timeouts:  value of 0 indicates value was < .5%.  Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)

                                                                           Avg
                                                       %Time Total Wait   wait
Event                                           Waits  -outs   Time (s)   (ms)
---------------------------------------- ------------ ------ ---------- ------
control file parallel write                       551      0          7     13
db file async I/O submit                           24      0          1     52
db file parallel read                               4      0          0     94
Disk file operations I/O                        1,096      0          0      0
library cache lock                                  5      0          0     14
control file sequential read                    5,166      0          0      0
Data file init write                               24      0          0      1
log file sequential read                        1,087      0          0      0
os thread startup                                   1      0          0     17
latch free                                        104      0          0      0
db file single write                                2      0          0      2
rdbms ipc message                               3,494     84      8,851   2533
DIAG idle wait                                  1,088    100      1,089   1001
smon timer                                          2    100        600 ######
MRP redo arrival                                  623      0        544    873
pmon timer                                        181    100        543   3002
shared server idle wait                            18    100        540  30007
dispatcher timer                                    9    100        540  60005
          -------------------------------------------------------------

Wait Event Histogram  DB/Inst: dg2/dg2  Snaps: 1-2
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)

                           Total ----------------- % of Waits ------------------
Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s   >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
Data file init write         24   75.0  16.7   4.2   4.2
Disk file operations I/O   1096   99.4    .2    .2                .3
RFS dispatch                622   99.8    .2
RFS ping                      9   88.9  11.1
RFS random i/o              543   99.8    .2
RFS write                   613   96.1   2.6    .8    .3    .2
SQL*Net more data to clien    6  100.0
Standby redo I/O            543     .2  27.8  37.0   5.9   3.1  13.3  12.7
asynch descriptor resize      1  100.0
control file parallel writ  587         47.0  26.7   4.6   3.2  11.1   7.3
control file sequential re 5657  100.0    .0
db file async I/O submit     24                4.2  12.5   8.3  45.8  29.2
db file parallel read         4   25.0                          25.0  50.0
db file sequential read      31   58.1                    16.1   9.7  16.1
db file single write          2         50.0  50.0
latch free                  104   99.0   1.0
latch: row cache objects      1  100.0
latch: shared pool            1        100.0
library cache lock            8         12.5        50.0  12.5  25.0
log file sequential read   1087  100.0
os thread startup             1                                100.0
DIAG idle wait             1088                                      100.0
MRP redo arrival            623     .3          .8   1.8   1.1   1.8  75.8  18.5
SQL*Net message from clien  895    7.2  12.8   8.3   2.6   3.1   1.2  53.9  10.9
SQL*Net message to client   895  100.0
SQL*Net more data from cli   11   63.6         9.1   9.1   9.1   9.1
class slave wait              1  100.0
dispatcher timer              9                                            100.0
pmon timer                  181                                            100.0
rdbms ipc message          3493     .1    .1    .1    .3    .5    .5  63.4  35.1
shared server idle wait      18                                            100.0
smon timer                    2                                            100.0
          -------------------------------------------------------------
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

那么备库的性能诊断,就有了利器。

另外,我们最希望的是自动去执行收集统计信息,那么statspack有spauto.sql去创建job,那么对于stdby也可以创建job使其自动去执行。
参考我的另一篇日志《oracle statspack学习》http://blog.csdn.net/rhys_oracle/article/details/11694355

过程如下:

SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000
SQL> show parameter timed_statistics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     TRUE
SQL> 
SQL> col owner for a14
SQL> col db_link for a20
SQL> col host for a18
SQL> col username for a40
SQL> select * from dba_db_links;

OWNER          DB_LINK              USERNAME
-------------- -------------------- ----------------------------------------
HOST               CREATED
------------------ ---------
STDBYPERF      STDBY_LINK_DG2       PERFSTAT
DG2                23-APR-14


SQL> set linesize 200
SQL> r
  1* select * from dba_db_links

OWNER          DB_LINK              USERNAME                                 HOST               CREATED
-------------- -------------------- ---------------------------------------- ------------------ ---------
STDBYPERF      STDBY_LINK_DG2       PERFSTAT                                 DG2                23-APR-14

SQL> conn stdbyperf/Amy
Connected.
SQL> select instance_name from v$instance@STDBY_LINK_DG2;

INSTANCE_NAME
----------------
dg2

SQL> 


 

创建job:

 

SQL> select job,schema_user,last_date,what,instance from user_jobs;

no rows selected

SQL> variable jobno number;
SQL> variable instno number;
SQL> begin
  2    select instance_number into :instno from v$instance@STDBY_LINK_DG2;
  3    dbms_job.submit(:jobno, 'statspack_dg2_dg2.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
  4    commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select job,schema_user,last_date,what,instance from user_jobs;

       JOB SCHEMA_USER                    LAST_DATE WHAT                                                           INSTANCE
---------- ------------------------------ --------- ------------------------------------------------------------ ----------
        23 STDBYPERF                                statspack_dg2_dg2.snap;                                               1

SQL> 


便于验证修改job,使其5分钟运行一次。

SQL> variable jobno number;
SQL> variable instno number;
SQL> begin
  2    select instance_number into :instno from v$instance@STDBY_LINK_DG2;
  3    dbms_job.submit(:jobno, 'statspack_dg2_dg2.snap;', trunc(sysdate+1/288,'MI'), 'trunc(sysdate+1/288,''MI'')', TRUE, :instno);
  4    commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select last_date,this_date,next_date,what from user_jobs;

LAST_DATE           THIS_DATE           NEXT_DATE           WHAT
------------------- ------------------- ------------------- ------------------------------------------------------------
                                        2014-04-23 16:00:00 statspack_dg2_dg2.snap;
                                        2014-04-23 15:20:00 statspack_dg2_dg2.snap;

SQL> 

SQL> select job,next_date,what from user_jobs;

       JOB NEXT_DATE           WHAT
---------- ------------------- ------------------------------------------------------------
        23 2014-04-23 16:00:00 statspack_dg2_dg2.snap;
        24 2014-04-23 15:20:00 statspack_dg2_dg2.snap;

SQL> exec dbms_job.remove('23');

PL/SQL procedure successfully completed.

SQL> select job,next_date,what from user_jobs;

       JOB NEXT_DATE           WHAT
---------- ------------------- ------------------------------------------------------------
        24 2014-04-23 15:20:00 statspack_dg2_dg2.snap;

SQL> 


 

然后再次查看报告:

SQL> 

SQL> conn STDBYPERF/Amy Connected. SQL> @?/rdbms/admin/sbreport.sql

Instances in this Statspack schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Unique Name                 Instance Name ------------------------------ ---------------- dg2                            dg2

Enter the DATABASE UNIQUE NAME of the standby database to report Enter value for db_unique_name: dg2 You entered: dg2

Enter the INSTANCE NAME of the standby database instance to report Enter value for inst_name: dg2 You entered: dg2

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 ------------ --------- ----------------- ----- -------------------- dg2                  1 23 Apr 2014 14:36     5                      2 23 Apr 2014 14:46     5                     11 23 Apr 2014 15:20     5                     12 23 Apr 2014 15:25     5                     13 23 Apr 2014 15:30     5                     14 23 Apr 2014 15:35     5                     15 23 Apr 2014 15:40     5

 

Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap:

 

可以看到job执行正常。

至此完成。


 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页