通过statspack诊断物理dg数据库性能方法
来源：互联网 发布：php final 方法 编辑：程序博客网 时间：2024/05/22 08:23
今天是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 a60SQL> r 1* select name from v$datafileNAME------------------------------------------------------------/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.dbfSQL> 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_fileNAME TYPE VALUE------------------------------------ ----------- ------------------------------standby_file_management string AUTO创建statspack；SQL> @?/rdbms/admin/spcreate.sqlChoose the PERFSTAT user's password-----------------------------------Not specifying a password will result in the installation FAILINGEnter value for perfstat_password: AmyAmyChoose the Default tablespace for the PERFSTAT user---------------------------------------------------Below is the list of online tablespaces in this database which canstore user data. Specifying the SYSTEM tablespace for the user'sdefault tablespace will result in the installation FAILING, asusing SYSTEM for performance data is not supported.Choose the PERFSTAT users's default tablespace. This is the tablespacein which the STATSPACK tables and indexes will be created.TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE------------------------------ --------- ----------------------------STATSPACK_STDBY PERMANENTSYSAUX PERMANENT *USERS PERMANENTPressing <return> will result in STATSPACK's recommended defaulttablespace (identified by *) being used.Enter value for default_tablespace: statspack_stdbyUsing 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 canstore temporary data (e.g. for sort workareas). Specifying the SYSTEMtablespace for the user's temporary tablespace will result in theinstallation 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 Temporarytablespace (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 privilegesNOTE:SPCUSR complete. Please check spcusr.lis for any errors.SQL> SQL> --SQL> -- Build the tables and synonymsSQL> connect perfstat/&&perfstat_passwordConnected.SQL> @@spctabSQL> RemSQL> Rem $Header: rdbms/admin/spctab.sql /st_rdbms_11.2.0/3 2012/03/06 15:07:48 shsong Exp $SQL> RemSQL> Rem spctab.sqlSQL> RemSQL> Rem Copyright (c) 1999, 2012, Oracle and/or its affiliates.SQL> Rem All rights reserved.SQL> RemSQL> Rem NAMESQL> Rem spctab.sqlSQL> RemSQL> Rem DESCRIPTIONSQL> Rem SQL*PLUS command file to create tables to holdSQL> Rem start and end "snapshot" statistical informationSQL> RemSQL> Rem NOTESSQL> Rem Should be run as STATSPACK user, PERFSTATSQL> RemSQL> Rem MODIFIED (MM/DD/YY)SQL> Rem kchou 08/11/10 - Bug#9800868 - Add Missing Idle Events forSQL> Rem 11.2.0.2for Statspack & Standby StatspackSQL> Rem kchou 08/11/10 - Bug#9800868 - Add missing idle events to 11.2.0.2SQL> Rem cgervasi 05/13/09 - add idle event: cell worker idleSQL> Rem cgervasi 04/02/09 - bug8395154: missing idle eventsSQL> Rem rhlee 02/22/08 -> Rem cdgreen 03/14/07 - 11 F2SQL> Rem shsong 06/14/07 - Add idle eventsSQL> Rem cdgreen 02/28/07 - 5908354SQL> Rem cdgreen 04/26/06 - 11 F1SQL> Rem cdgreen 06/26/06 - Increase column lengthSQL> Rem cdgreen 05/10/06 - 5215982SQL> Rem cdgreen 05/24/05 - 4246955SQL> Rem cdgreen 04/18/05 - 4228432SQL> Rem cdgreen 03/08/05 - 10gR2 miscSQL> Rem vbarrier 02/18/05 - 4081984SQL> Rem cdgreen 10/29/04 - 10gR2_sqlstatsSQL> Rem cdgreen 07/16/04 - 10gR2SQL> Rem cdialeri 03/25/04 - 3516921SQL> Rem vbarrier 02/12/04 - 3412853SQL> Rem cdialeri 12/04/03 - 3290482SQL> Rem cdialeri 11/05/03 - 3202706SQL> Rem cdialeri 10/14/03 - 10g - streams - rvenkateSQL> Rem cdialeri 08/05/03 - 10g F3SQL> Rem cdialeri 02/27/03 - 10g F2: baseline, purgeSQL> Rem vbarrier 02/25/03 - 10g RACSQL> Rem cdialeri 11/15/02 - 10g F1SQL> Rem cdialeri 09/27/02 - sleep4SQL> Rem vbarrier 03/20/02 - 2143634SQL> Rem vbarrier 03/05/02 - Segment StatisticsSQL> Rem cdialeri 02/07/02 - 2218573SQL> Rem cdialeri 01/30/02 - 2184717SQL> Rem cdialeri 01/11/02 - 9.2 - features 2SQL> Rem cdialeri 11/30/01 - 9.2 - features 1SQL> Rem cdialeri 04/22/01 - Undostat changesSQL> Rem cdialeri 03/02/01 - 9.0SQL> Rem cdialeri 09/12/00 - sp_1404195SQL> Rem cdialeri 04/07/00 - 1261813SQL> Rem cdialeri 03/20/00 - Support for purgeSQL> Rem cdialeri 02/16/00 - 1191805SQL> Rem cdialeri 01/26/00 - 1169401SQL> Rem cdialeri 11/01/99 - Enhance, 1059172SQL> Rem cmlim 07/17/97 - Added STATS$SQLAREA to store top sql stmtsSQL> Rem gwood 10/16/95 - Version to run as sys without using many viewsSQL> Rem cellis.uk 11/15/89 - CreatedSQL> RemSQL> SQL> set showmode off echo off;If this script is automatically called from spcreate (which isthe supported method), all STATSPACK segments will be created inthe PERFSTAT user's default tablespace.Using statspack_stdby tablespace to store Statspack objects... Creating STATS$SNAPSHOT_ID SequenceSequence created.Synonym created.... Creating STATS$... tablesTable 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 sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 23 14:34:05 2014Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> @?/rdbms/admin/sbcreate.sqlChoose the STDBYPERF user's password-----------------------------------Not specifying a password will result in the installation FAILINGEnter value for stdbyuser_password: AmyAmyChoose the Default tablespace for the STDBYPERF user---------------------------------------------------Below is the list of online tablespaces in this database which canstore user data. Specifying the SYSTEM tablespace for the user'sdefault tablespace will result in the installation FAILING, asusing SYSTEM for performance data is not supported.Choose the STDBYPERF users's default tablespace. This is the tablespacein which the STATSPACK tables and indexes will be created.TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE------------------------------ --------- ----------------------------STATSPACK_STDBY PERMANENTSYSAUX PERMANENT *USERS PERMANENTPressing <return> will result in STATSPACK's recommended defaulttablespace (identified by *) being used.Enter value for default_tablespace: statspack_stdbyUsing 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 canstore temporary data (e.g. for sort workareas). Specifying the SYSTEMtablespace for the user's temporary tablespace will result in theinstallation 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 Temporarytablespace (identified by *) being used.Enter value for temporary_tablespace: Using tablespace TEMP as STDBYPERF temporary tablespace.... Creating STDBYPERF user... Installing required packages... Granting privilegesNOTE:SBCUSR complete. Please check sbcusr.lis for any errors.SQL> SQL> connect stdbyperf/&&stdbyuser_passwordConnected.SQL> SQL> --SQL> -- Build the tablesSQL> @@sbctabSQL> RemSQL> Rem sbctab.sqlSQL> RemSQL> Rem Copyright (c) 1999, 2012, Oracle and/or its affiliates.SQL> Rem All rights reserved.SQL> RemSQL> Rem NAMESQL> Rem sbctab.sqlSQL> RemSQL> Rem DESCRIPTIONSQL> Rem SQL*PLUS command file to create tables to hold standby databaseSQL> Rem start and end "snapshot" statistical informationSQL> RemSQL> Rem NOTESSQL> Rem Should be run as Standby Statspack user, stdbyperfSQL> RemSQL> Rem MODIFIED (MM/DD/YY)SQL> Rem kchou 11/09/11 - Backport Bug#9695145 Missing Idle Events toSQL> Rem Standby Statspack - RFI 10431923 Release 11.2.0.4SQL> Rem kchou 11/09/11 - Backport kchou_bug-9695145 from mainSQL> Rem kchou 11/09/11 - Remove synonym STATS$IDLE_EVENTSQL> Rem kchou 08/11/10 - Bug#9800868 - Add Missing Idle Events forSQL> Rem 11.2.0.2for Statspack & Standby StatspackSQL> Rem kchou 08/11/10 - Bug#9800868 - Add missing idle events to 11.2.0.2SQL> Rem shsong 01/28/10 - add stats$lock_typeSQL> Rem shsong 08/18/09 - Add db_unique_nameSQL> Rem shsong 02/02/09 - remove stats$kccfn etcSQL> Rem shsong 07/10/08 - add stats$kccfn etcSQL> Rem shsong 02/28/07 - Fix bugSQL> Rem wlohwass 12/04/06 - Created, based on spctab.sqlSQL> RemSQL> SQL> set showmode off echo off;If this script is automatically called from sbcreate (which isthe supported method), all STATSPACK segments will be created inthe STDBYPERF user default tablespace.Using statspack_stdby tablespace to store Statspack objects... Creating STATS$SNAPSHOT_ID SequenceSequence created.... Creating STATS$... tablesTable 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 configurationSQL> @@sbaddinsSQL> RemSQL> Rem sbaddins.sqlSQL> RemSQL> Rem Copyright (c) 2006, 2010, Oracle and/or its affiliates.SQL> Rem All rights reserved.SQL> RemSQL> Rem NAMESQL> Rem sbaddins.sql - Standby Database Statistics Collection Add InstanceSQL> RemSQL> Rem DESCRIPTIONSQL> Rem SQL*PLUS command file which adds a standby database instanceSQL> Rem for performance data collectionSQL> RemSQL> Rem NOTESSQL> Rem Must be run from standby perfstat owner, STDBYPERFSQL> RemSQL> Rem MODIFIED (MM/DD/YY)SQL> Rem shsong 01/28/10 - remove v$lock_typeSQL> Rem shsong 08/18/09 - add db_unique_name to stats$standby_configSQL> Rem shsong 03/04/07 - fix bugSQL> Rem wlohwass 12/04/06 - CreatedSQL> RemSQL> SQL> set echo off;The following standby instances (TNS_NAME alias) have been configuredfor data collection=== END OF LIST ===THE INSTANCE YOU ARE GOING TO ADD MUST BE ACCESSIBLE AND OPEN READ ONLYDo you want to continue (y/n) ? ----------------（添加备用节点）Enter value for key: yYou entered: yEnter 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: DG2You entered: DG2Enter the PERFSTAT user's password of the standby database---------------------------------------------------------Performance data will be fetched from the standby database viadatabase link. We will connect to user PERFSTAT.Enter value for perfstat_password: AmyYou entered: Amy... Creating database link... Selecting database unique nameDatabase------------------------------dg2... Selecting instance nameInstance------------dg2... Creating packageCreating 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_nameSQL> SQL> SQL> SQL> SQL>
至此完成了逻辑备库的dg性能分析工具的statspack的创建。
那么如何使用呢？
需要在主库上执行所创建的包，来收集备库信息，如下：
SQL> connect stdbyperf/AmyConnected.SQL> exec statspack_dg2_dg2.snap;SQL> SQL> SQL> SQL> exec statspack_dg2_dg2.snap;SQL> @?/rdbms/admin/sbreport.sqlSQL> RemSQL> Rem sbreport.sqlSQL> RemSQL> Rem Copyright (c) 2007, Oracle. All rights reserved.SQL> RemSQL> Rem NAMESQL> Rem sbreport.sqlSQL> RemSQL> Rem DESCRIPTIONSQL> Rem This script calls sbrepins.sql to produce standby statspack reportSQL> RemSQL> Rem NOTESSQL> Rem Must run as the standby statspack owner, stdbyperfSQL> RemSQL> Rem MODIFIED (MM/DD/YY)SQL> Rem shsong 02/15/07 - fix bugSQL> Rem wlohwass 12/04/06 - Created, based on spreport.sqlSQL> SQL> SQL> @@sbrepinsSQL> RemSQL> Rem sbrepins.sqlSQL> RemSQL> Rem Copyright (c) 2001, 2012, Oracle and/or its affiliates.SQL> Rem All rights reserved.SQL> RemSQL> Rem NAMESQL> Rem sbrepins.sql - StandBy statspack REPort INStanceSQL> RemSQL> Rem DESCRIPTIONSQL> Rem SQL*Plus command file to report on differences betweenSQL> Rem values recorded in two snapshots.SQL> RemSQL> Rem This script requests the user to provide database unique nameSQL> Rem and instance number of the instance to report on, then produceSQL> Rem the standby statspack report.SQL> RemSQL> Rem NOTESSQL> Rem Run as the standby statspack owner, stdbyperfSQL> RemSQL> Rem MODIFIED (MM/DD/YY)SQL> Rem shsong 01/25/10 - Bug 9307098SQL> Rem shsong 08/21/09 - use db_unique_name as primary keySQL> Rem shsong 06/16/08 - add active_agents to Managed Standby StatsSQL> Rem shsong 02/05/07 - Add stats for recovery_progress etcSQL> Rem shsong 02/04/07 - CreatedSQL> RemSQL> SQL> set echo off;Instances in this Statspack schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~DB Unique Name Instance Name------------------------------ ----------------dg2 dg2Enter the DATABASE UNIQUE NAME of the standby database to reportEnter value for db_unique_name: dg2You entered: dg2Enter the INSTANCE NAME of the standby database instance to reportEnter value for inst_name: dg2You entered: dg2Specify 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> withoutspecifying a number lists all completed snapshots.Listing all Completed Snapshots SnapInstance Snap Id Snap Started Level Comment------------ --------- ----------------- ----- --------------------dg2 1 23 Apr 2014 14:36 5 2 23 Apr 2014 14:46 5Specify the Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for begin_snap: 1Begin Snapshot Id specified: 1Enter value for end_snap: 2End Snapshot Id specified: 2Specify 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_reportSTATSPACK Statistics Report for Physical StandbyDatabase~~~~~~~~DB Unique Name Instance Startup Time Release RAC------------------------------ ------------ --------------- ----------- ---dg2 dg2 23-Apr-14 14:22 11.2.0.4.0 NOHost 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,368KLoad 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.7W/A MB processed: 15.1 0.0 Logons: 1.0 0.0 Executes: 9,526.0 17.5 Rollbacks: 0.0 0.0Instance 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.97Parse 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.76Top 5 Timed Events Avg %Total~~~~~~~~~~~~~~~~~~ wait CallEvent Waits Time (s) (ms) Time----------------------------------------- ------------ ----------- ------ ------Standby redo I/O 543 8 15 37.6control file parallel write 587 7 12 33.3CPU time 3 15.8db file async I/O submit 24 1 52 5.9db 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.80Note: 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 ascRecovery Start Time Item Sofar Units Redo Timestamp------------------- ----------------- -------------- ------- ------------------23-Apr-14 14:30:54 Log Files 2 Files23-Apr-14 14:30:54 Active Apply Rate 575 KB/sec23-Apr-14 14:30:54 Average Apply Rat 23 KB/sec23-Apr-14 14:30:54 Maximum Apply Rat 582 KB/sec23-Apr-14 14:30:54 Redo Applied 21 Megabyt23-Apr-14 14:30:54 Last Applied Redo 0 SCN+Tim 23-Apr-14 14:46:0623-Apr-14 14:30:54 Active Time 26 Seconds23-Apr-14 14:30:54 Apply Time per Lo 3 Seconds23-Apr-14 14:30:54 Checkpoint Time p 0 Seconds23-Apr-14 14:30:54 Elapsed Time 911 Seconds23-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 nameStatistic Time (s) % DB time----------------------------------- -------------------- ---------parse time elapsed 2.8 25.4hard parse elapsed time 2.7 25.0DB CPU 2.5 23.4sql execute elapsed time 2.0 18.6hard parse (sharing criteria) elaps 0.3 2.5repeated bind elapsed time 0.0 .0DB time 10.9background elapsed time 14.1background 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 waitEvent Waits -outs Time (s) (ms)---------------------------------------- ------------ ------ ---------- ------Standby redo I/O 543 0 8 15control file parallel write 587 0 7 12db file async I/O submit 24 0 1 52db file sequential read 31 0 1 23db file parallel read 4 0 0 94RFS write 613 0 0 0Disk file operations I/O 1,096 0 0 0library cache lock 8 0 0 10control file sequential read 5,657 0 0 0RFS dispatch 622 0 0 0Data file init write 24 0 0 1log file sequential read 1,087 0 0 0os thread startup 1 0 0 17RFS random i/o 543 0 0 0RFS ping 9 0 0 1latch free 104 0 0 0db file single write 2 0 0 2SQL*Net more data to client 6 0 0 0latch: shared pool 1 0 0 2SQL*Net message from client 899 0 1,616 1798DIAG idle wait 1,088 100 1,089 1001MRP redo arrival 623 0 544 873shared server idle wait 18 100 540 30007SQL*Net more data from client 11 0 0 4SQL*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 waitEvent Waits -outs Time (s) (ms)---------------------------------------- ------------ ------ ---------- ------control file parallel write 551 0 7 13db file async I/O submit 24 0 1 52db file parallel read 4 0 0 94Disk file operations I/O 1,096 0 0 0library cache lock 5 0 0 14control file sequential read 5,166 0 0 0Data file init write 24 0 0 1log file sequential read 1,087 0 0 0os thread startup 1 0 0 17latch free 104 0 0 0db file single write 2 0 0 2rdbms ipc message 3,494 84 8,851 2533DIAG idle wait 1,088 100 1,089 1001smon timer 2 100 600 ######MRP redo arrival 623 0 544 873pmon timer 181 100 543 3002shared server idle wait 18 100 540 30007dispatcher 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.2Disk file operations I/O 1096 99.4 .2 .2 .3RFS dispatch 622 99.8 .2RFS ping 9 88.9 11.1RFS random i/o 543 99.8 .2RFS write 613 96.1 2.6 .8 .3 .2SQL*Net more data to clien 6 100.0Standby redo I/O 543 .2 27.8 37.0 5.9 3.1 13.3 12.7asynch descriptor resize 1 100.0control file parallel writ 587 47.0 26.7 4.6 3.2 11.1 7.3control file sequential re 5657 100.0 .0db file async I/O submit 24 4.2 12.5 8.3 45.8 29.2db file parallel read 4 25.0 25.0 50.0db file sequential read 31 58.1 16.1 9.7 16.1db file single write 2 50.0 50.0latch free 104 99.0 1.0latch: row cache objects 1 100.0latch: shared pool 1 100.0library cache lock 8 12.5 50.0 12.5 25.0log file sequential read 1087 100.0os thread startup 1 100.0DIAG idle wait 1088 100.0MRP redo arrival 623 .3 .8 1.8 1.1 1.8 75.8 18.5SQL*Net message from clien 895 7.2 12.8 8.3 2.6 3.1 1.2 53.9 10.9SQL*Net message to client 895 100.0SQL*Net more data from cli 11 63.6 9.1 9.1 9.1 9.1class slave wait 1 100.0dispatcher timer 9 100.0pmon timer 181 100.0rdbms ipc message 3493 .1 .1 .1 .3 .5 .5 63.4 35.1shared server idle wait 18 100.0smon 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_processesNAME TYPE VALUE------------------------------------ ----------- ------------------------------job_queue_processes integer 1000SQL> show parameter timed_statisticsNAME TYPE VALUE------------------------------------ ----------- ------------------------------timed_statistics boolean TRUESQL> SQL> col owner for a14SQL> col db_link for a20SQL> col host for a18SQL> col username for a40SQL> select * from dba_db_links;OWNER DB_LINK USERNAME-------------- -------------------- ----------------------------------------HOST CREATED------------------ ---------STDBYPERF STDBY_LINK_DG2 PERFSTATDG2 23-APR-14SQL> set linesize 200SQL> r 1* select * from dba_db_linksOWNER DB_LINK USERNAME HOST CREATED-------------- -------------------- ---------------------------------------- ------------------ ---------STDBYPERF STDBY_LINK_DG2 PERFSTAT DG2 23-APR-14SQL> conn stdbyperf/AmyConnected.SQL> select instance_name from v$instance@STDBY_LINK_DG2;INSTANCE_NAME----------------dg2SQL>
创建job：
SQL> select job,schema_user,last_date,what,instance from user_jobs;no rows selectedSQL> 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; 1SQL>
便于验证修改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/AmyConnected.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 reportEnter value for db_unique_name: dg2You entered: dg2
Enter the INSTANCE NAME of the standby database instance to reportEnter value for inst_name: dg2You 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> withoutspecifying a number lists all completed snapshots.
Listing all Completed Snapshots
SnapInstance 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执行正常。
至此完成。
0 0
- 通过statspack诊断物理dg数据库性能方法
- 通过statspack诊断物理dg数据库性能方法
- 用Oracle中的Statspack诊断数据库性能实例
- 通过rman建立物理dg
- 性能诊断sp_sysmon物理IO
- ORACLE性能诊断―学习statspack笔记（二）[概述]
- ORACLE性能诊断―学习statspack笔记（四）[扩展ｓｔａｔｓｐａｃｋ收集服务器统计]
- oracle物理dg安装：方法一
- oracle物理dg安装：方法二
- 使用STATSPACK调整数据库性能（一）
- 物理DG与逻辑DG的区别与逻辑DG同步异常处理方法
- ORACLE性能诊断―学习statspack笔记（一） [安装与测试]
- ORACLE性能诊断―学习statspack笔记（三）[表的分类]
- ORACLE性能诊断―学习statspack笔记（三）[表的分类]
- 通过DG来升级duplicate方法不合适
- Oracle性能诊断的方法
- Oracle性能诊断的方法
- Oracle10g物理DG详细配置方法及步骤
- 翱翔在计算机系统 DMA(1)
- POJ 3258 River Hopscotch
- 硬件电路可靠性设计
- 在C++中执行cmd指令的两种方法
- 离别我送你千里之外
- 通过statspack诊断物理dg数据库性能方法
- .NET 数据类型之类（Class）
- 翱翔在计算机系统 DMA(2)
- Xcode4编译过程中一些常见错误
- 视频压缩的基本原理，一些常见压缩算法的概念
- vi常用方法
- 最新Android 4.x 搭建开发环境
- 洋槐花，使人奋进的花
- Windows 7 SP1 导致含有 ADO 的程式失效問題
