通过statspack诊断物理dg数据库性能方法
程序员文章站
2022-06-06 15:54:44
...
今天是2014-04-23，继续整理一下dg的内容，顺便学习温习。对于物理dg来说，备库在11g是可以以只读模式打开，可以提供报表查询功能，往往我们在分析备库 性能的时候需要查询大量的v$视图，那么在11G之后可以在主库获得备库的statspack性能数据，便于分析。现
今天是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 Pressingwill 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 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 Pressingwill 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 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. Pressingwithout 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 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 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 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 -> % of Waits - value: .0 indicates value was <.05 null is truly> Ordered by Event (idle events last) Total ----------------- % of Waits ------------------ Event Waits 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
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执行正常。
至此完成。