A modifed version of statspack availabe on metalink (note 454848.1) can be deployed on the primary production database. This modified version uses a STDBYPERF schema which writes locally but reads remotely from PERFSTAT@standbydb.
Initial Setup (Oracle 11.1.0.7)
export ORACLE_SID=standbydb cd $ORACLE_HOME/rdbms/admin sql CREATE SMALLFILE TABLESPACE PERFSTAT DATAFILE '+DATA1/perfstat01.dbf' SIZE 4G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; @spdrop @spcreate exit; sql @sbcreate --wait a second or two, the account has to propagate to the standby @sbaddins
Configure Snapshots
Remember that PERFSTAT is not automatically purged/managed by the database, so this must be done by the DBA or scheduled.
-- workaround to avoid serious performance problem due to Bug 8323663 . setdb ordprod2 sql execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KCCTS', estimate_percent => 20 ); execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KCFIO', estimate_percent => 20 ); execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KCBFWAIT', estimate_percent => 20 ); execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KCCFN', estimate_percent => 20 ); execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'X$KCCFE', estimate_percent => 20 ); grant create job to stdbyperf; grant manage scheduler to stdbyperf; conn stdbyperf/*** -- if this takes more than ~40 seconds there is a performance issue that will need to be traced exec statspack_ordprod1.snap; -- automate stats collection every 30 min during Mon-Fri 7am-5pm exec dbms_scheduler.disable('ordrpt_statspack_snap_daily'); begin dbms_scheduler.create_job ( job_name => 'ordrpt_statspack_snap_daily', job_type => 'PLSQL_BLOCK', job_action => 'begin statspack_ordprod1.snap; end;', repeat_interval => 'FREQ=MINUTELY; INTERVAL=30; BYHOUR=7,8,9,10,11,12,13,14,15,16,17; BYDAY=MON,TUE,WED,THU,FRI', enabled => true, comments => 'Take statspack snapshot on remote standby db ordrpt daily Mon to Fri 7am to 5pm' ); end; / exec dbms_scheduler.enable('ordrpt_statspack_snap_daily');
Create Reports
Similar to AWR reports, run reports from caldbrac02
SQL> conn stbyperf/*** SQL> @?/rdbms/admin/sbreport
-- follows prompts to enter dbid, instanceid, snapshot intervals, output is text file
Author: Blair Boadway | Production DBA