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

--------------------------------------------------------------- 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  witho

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