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

标签: statspack 诊断 物理 | 发表时间:2014-04-23 23:34 | 作者:xiaohai20102010
出处:http://blog.csdn.net

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

[[email protected] admin]$ echo $ORACLE_HOME/admin
/u01/app/oracle/product/11.2.0/db_1/admin
[[email protected] 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
[[email protected] 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模式:

[[email protected] ~]$ 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 [email protected]_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 [email protected]_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 [email protected]_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执行正常。

至此完成。


 

作者:xiaohai20102010 发表于2014-4-23 15:34:14 原文链接
阅读:54 评论:0 查看评论

相关 [statspack 诊断 物理] 推荐:

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

- - CSDN博客推荐文章
  今天是2014-04-23,继续整理一下dg的内容,顺便学习温习. 对于物理dg来说,备库在11g是可以以只读模式打开,可以提供报表查询功能,往往我们在分析备库. 性能的时候需要查询大量的v$视图,那么在11G之后可以在主库获得备库的statspack性能数据,便于分析. 现在就看一下如果在主库上存储备用数据的statspack数.

oracle Statspack安装使用攻略

- - CSDN博客数据库推荐文章
oracle Statspack从Oracle8.1.6被引入,马上成为DBA和Oracle专家用来诊断数据库性能的强有力工具. 通过Statspack我们可以很容易的确定Oracle数据库的瓶颈所有,记录数据库性能状态,也可以使远程技术人员迅速了解的的数据库运行状况. 所以,了解和使用Statspack对于DBA来说至关重要.

如何诊断CDN故障

- - 火丁笔记
某项目使用CDN做文件下载服务,最近不时有网友反馈下载出错,因为CDN是第三方提供的,且节点众多,所以诊断起来有点麻烦,必须想想招儿. 首当其冲的问题是如何确认CDN有哪些节点. 幸运的是通过 阿里测提供的服务,我们能拿到这个IP列表,当然这个IP列表不可能百分百完整,不过应该包含了大部分的节点,有兴趣的可以参考 百度的JQuery CDN例子.

JVM诊断调优CheatSheet

- - ImportNew
使用top去获取进程cpu使用率;使用/proc文件查看进程所占内存. 查看类的一些信息,如字节码的版本号、常量池等. 查看进程的gc情况. jstat -gcutil [pid] (显示总体情况). jstat -gc [pid] 1000 10(每隔1秒刷新一次 一共10次). 查看jvm内存使用状况.

初步诊断你的 GC

- - IT瘾-dev
本文是好友阿飞写的,并且经过作者同意发的原创. 阿飞Javaer,转载请注明原创出处,谢谢. JVM的GC机制让Java程序员省去了自己垃圾回收的烦恼,大大提高了生产效率. 但是正因为JVM垃圾回收机制足够优秀,导致很多Java程序员对JVM这个黑盒了解甚少,很多人没有去了解它,很多人也没机会去了解它.

使用pt-stalk诊断MySQL问题

- - haohtml's blog
在MySQL服务器出现短暂(5~30秒)的性能波动的时候,一般的性能监控工具都很难抓住故障现场,也就很难收集对应较细粒度的诊断信息. 另外,如果这种波动出现的频率很低,例如几天才一次,我们也很难人为的抓住现场,收集数据. 这正是pt-stalk所解决的问题. pt-stalk是 Percona-Toolkit的一部分(其前身是 Aspersa的一部分).

网站诊断之建议篇

- - Google 黑板报 - Google (谷歌)中国的博客网志,走近我们的产品、技术和文化
发表者:谷歌中文搜索质量团队. 转载自: 谷歌中文网站管理员博客. 发布时间:2012年1月18日 上午 10:46:00. 几周之前,我们曾邀请非营利性的公益网站站长向我们的搜索质量团队提交他们的网站,参加我们的在线网站诊断活动. 感谢积极参加此次活动的公益网站站长. 现在我们根据提交的网站,总结出了一些需要改进的地方,并提供了一些建议以及您可以从谷歌获得的资源.

使用pt-stalk诊断MySQL问题

- - OurMySQL
在MySQL服务器出现短暂(5~30秒)的性能波动的时候,一般的性能监控工具都很难抓住故障现场,也就很难收集对应较细粒度的诊断信息. 另外,如果这种波动出现的频率很低,例如几天才一次,我们也很难人为的抓住现场,收集数据. 这正是pt-stalk所解决的问题. pt-stalk是 Percona-Toolkit的一部分(其前身是 Aspersa的一部分).

ThreadSafe:诊断并发问题的利器

- - Java译站
听到ThreadSafe这个东西我的第一反应就是, ”天啊,又出了一个静态代码分析工具”. 在内部开发中引入了像PMD或者FindBugs这类的工具,又花了不少时间优化成零警告后,我感觉已经不再需要其它的工具了. ThreadSafe这个工具跟别的代码分析工具一样,但有一点不同,它更专注于Java开发中一个非常重要的领域——并发.

诊断Java中的内存泄露

- - ImportNew
每次我怀疑有内存泄漏时,我都要翻箱倒柜找这些命令. 首先,我用下面的命令监视进程:. (如果有的话还有New Relic). 如果你看到内存上升很快,可能是因为虚拟机设置. 如果你没有明确指定JVM的内存设置,它将设置默认值给他们. 如果这些都不符合你所希望的,那么你就需要指定JVM的内存设置. 可以用下面的命令设置最小和最大堆大小:.