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

- 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 




 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  will result in the database's default Temporary
tablespace (identified

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