Compare 9i Statspack & 10g AWR Views

Posted by Sagar Patil
Statspack AWR
STATS$DATABASE_INSTANCE Tables store historical data or snapshots
STATS$IDLE_EVENT
STATS$LEVEL_DESCRIPTION
STATS$ROLLSTAT
STATS$SESSION_EVENT
STATS$SNAPSHOT
STATS$SQL_PLAN_USAGE
STATS$SQL_SUMMARY WRH$_ACTIVE_SESSION_HISTORY WRI$_ADV_ACTIONS
STATS$STATSPACK_PARAMETER WRH$_ACTIVE_SESSION_HISTORY_BL WRI$_ADV_ASA_RECO_DATA
STATS$BG_EVENT_SUMMARY WRH$_BG_EVENT_SUMMARY WRI$_ADV_DEFINITIONS
WRH$_BUFFERED_QUEUES WRI$_ADV_DEF_PARAMETERS
WRH$_BUFFERED_SUBSCRIBERS WRI$_ADV_DIRECTIVES
STATS$BUFFER_POOL_STATISTICS WRH$_BUFFER_POOL_STATISTICS WRI$_ADV_FINDINGS
WRH$_COMP_IOSTAT WRI$_ADV_JOURNAL
WRH$_CR_BLOCK_SERVER WRI$_ADV_MESSAGE_GROUPS
WRH$_CURRENT_BLOCK_SERVER WRI$_ADV_OBJECTS
WRH$_DATAFILE WRI$_ADV_PARAMETERS
STATS$DB_CACHE_ADVICE WRH$_DB_CACHE_ADVICE WRI$_ADV_RATIONALE
WRH$_DB_CACHE_ADVICE_BL WRI$_ADV_RECOMMENDATIONS
STATS$DLM_MISC WRH$_DLM_MISC WRI$_ADV_REC_ACTIONS
WRH$_DLM_MISC_BL WRI$_ADV_SQLA_FAKE_REG
STATS$ENQUEUE_STAT WRH$_ENQUEUE_STAT WRI$_ADV_SQLA_MAP
WRH$_EVENT_NAME WRI$_ADV_SQLA_STMTS
WRH$_FILEMETRIC_HISTORY WRI$_ADV_SQLA_TMP
STATS$FILESTATXS WRH$_FILESTATXS WRI$_ADV_SQLT_BINDS
WRH$_FILESTATXS_BL WRI$_ADV_SQLT_PLANS
STATS$INSTANCE_RECOVERY WRH$_INSTANCE_RECOVERY WRI$_ADV_SQLT_RTN_PLAN
WRH$_INST_CACHE_TRANSFER WRI$_ADV_SQLT_STATISTICS
WRH$_INST_CACHE_TRANSFER_BL WRI$_ADV_SQLW_COLVOL
WRH$_JAVA_POOL_ADVICE WRI$_ADV_SQLW_STMTS
STATS$LATCH WRH$_LATCH WRI$_ADV_SQLW_SUM
WRH$_LATCH_BL WRI$_ADV_SQLW_TABLES
STATS$LATCH_CHILDREN WRH$_LATCH_CHILDREN WRI$_ADV_SQLW_TABVOL
WRH$_LATCH_CHILDREN_BL WRI$_ADV_TASKS
STATS$LATCH_MISSES_SUMMARY WRH$_LATCH_MISSES_SUMMARY WRI$_ADV_USAGE
WRH$_LATCH_MISSES_SUMMARY_BL WRI$_AGGREGATION_ENABLED
WRH$_LATCH_NAME WRI$_ALERT_HISTORY
STATS$LATCH_PARENT WRH$_LATCH_PARENT WRI$_ALERT_OUTSTANDING
WRH$_LATCH_PARENT_BL WRI$_ALERT_THRESHOLD
STATS$LIBRARYCACHE WRH$_LIBRARYCACHE WRI$_ALERT_THRESHOLD_LOG
WRH$_LOG WRI$_DBU_CPU_USAGE
WRH$_METRIC_NAME WRI$_DBU_CPU_USAGE_SAMPLE
WRH$_MTTR_TARGET_ADVICE WRI$_DBU_FEATURE_METADATA
WRH$_OPTIMIZER_ENV WRI$_DBU_FEATURE_USAGE
WRH$_OSSTAT WRI$_DBU_HIGH_WATER_MARK
WRH$_OSSTAT_BL WRI$_DBU_HWM_METADATA
WRH$_OSSTAT_NAME WRI$_DBU_USAGE_SAMPLE
STATS$PARAMETER WRH$_PARAMETER WRI$_OPTSTAT_AUX_HISTORY
WRH$_PARAMETER_BL WRI$_OPTSTAT_HISTGRM_HISTORY
WRH$_PARAMETER_NAME WRI$_OPTSTAT_HISTHEAD_HISTORY
STATS$PGASTAT WRH$_PGASTAT WRI$_OPTSTAT_IND_HISTORY
STATS$PGA_TARGET_ADVICE WRH$_PGA_TARGET_ADVICE WRI$_OPTSTAT_OPR
WRH$_PROCESS_MEMORY_SUMMARY WRI$_OPTSTAT_TAB_HISTORY
STATS$RESOURCE_LIMIT WRH$_RESOURCE_LIMIT WRI$_SCH_CONTROL
STATS$ROWCACHE_SUMMARY WRH$_ROWCACHE_SUMMARY WRI$_SCH_VOTES
WRH$_ROWCACHE_SUMMARY_BL WRI$_SEGADV_CNTRLTAB
WRH$_RULE_SET WRI$_SEGADV_OBJLIST
WRH$_SEG_STAT WRI$_SQLSET_BINDS
WRH$_SEG_STAT_BL WRI$_SQLSET_DEFINITIONS
STATS$SEG_STAT_OBJ WRH$_SEG_STAT_OBJ WRI$_SQLSET_MASK
WRH$_SERVICE_NAME WRI$_SQLSET_PLANS
WRH$_SERVICE_STAT WRI$_SQLSET_PLANS_TOCAP
WRH$_SERVICE_STAT_BL WRI$_SQLSET_PLAN_LINES
WRH$_SERVICE_WAIT_CLASS WRI$_SQLSET_REFERENCES
WRH$_SERVICE_WAIT_CLASS_BL WRI$_SQLSET_STATEMENTS
WRH$_SESSMETRIC_HISTORY WRI$_SQLSET_STATISTICS
STATS$SESSTAT WRH$_SESS_TIME_STATS WRI$_SQLSET_WORKSPACE
STATS$SGA WRH$_SGA WRI$_TRACING_ENABLED
STATS$SEG_STAT , STATS$SGASTAT WRH$_SGASTAT
WRH$_SGASTAT_BL
WRH$_SGA_TARGET_ADVICE
STATS$SHARED_POOL_ADVICE WRH$_SHARED_POOL_ADVICE
STATS$SQL_STATISTICS WRH$_SQLSTAT
WRH$_SQLSTAT_BL
STATS$SQLTEXT WRH$_SQLTEXT
WRH$_SQL_BIND_METADATA
STATS$SQL_PLAN WRH$_SQL_PLAN
WRH$_SQL_SUMMARY
STATS$SQL_WORKAREA_HISTOGRAM WRH$_SQL_WORKAREA_HISTOGRAM
WRH$_STAT_NAME
WRH$_STREAMS_APPLY_SUM
WRH$_STREAMS_CAPTURE
WRH$_STREAMS_POOL_ADVICE
WRH$_SYSMETRIC_HISTORY
WRH$_SYSMETRIC_SUMMARY
STATS$SYSSTAT WRH$_SYSSTAT
WRH$_SYSSTAT_BL
STATS$SYSTEM_EVENT WRH$_SYSTEM_EVENT
WRH$_SYSTEM_EVENT_BL
WRH$_SYS_TIME_MODEL
WRH$_SYS_TIME_MODEL_BL
WRH$_TABLESPACE_SPACE_USAGE
WRH$_TABLESPACE_STAT
WRH$_TABLESPACE_STAT_BL
WRH$_TEMPFILE
STATS$TEMPSTATXS WRH$_TEMPSTATXS
WRH$_THREAD
STATS$UNDOSTAT WRH$_UNDOSTAT
WRH$_WAITCLASSMETRIC_HISTORY
STATS$WAITSTAT WRH$_WAITSTAT
WRH$_WAITSTAT_BL

Statspack Scripts

Posted by Sagar Patil

Installation
The following scripts must be run as a user with SYSDBA privilege:
* SPCREATE.SQL: Creates entire Statspack environment (calls SPCUSR.SQL, SPCTAB.SQL, SPCPKG.SQL) * SPDROP.SQL: Drops entire Statspack environment (calls SPDTAB.SQL, SPDUSR.SQL)

Read more…

Locate Hard hitting SQL from Statpack Repository

Posted by Sagar Patil

1. Login as PERFSTAT user on database.  It won’t work unless U login as PERFSTAT user

Read more…

Locate Server Workload from Statspack for days in Past

Posted by Sagar Patil

Change a.statistic# to respective value

Stats for Working Hours

select to_char(trunc(b.snap_time),'DD-MM-YYYY') ,statistic#,name, sum(value) from STATS$SYSSTAT A, stats$snapshot B
where a.snap_id=b.snap_id
and trunc(b.snap_time) > trunc(sysdate -30)
and to_char(b.SNAP_TIME,'HH24') > 8
and to_char(b.SNAP_TIME,'HH24') <18
and a.statistic#=54
group by trunc(b.snap_time) ,statistic#,name
order by trunc(b.snap_time)

Read more…

What is causing Redo / Writes/ Log Creation- Statspack 8i/9i

Posted by Sagar Patil

Physical WRITES from the time Instance Started

Read more…

How to Install Statspack (8i/9i/10g)

Posted by Sagar Patil

Read more…

Locate Server Workload for a Defined Timeframe

Posted by Sagar Patil

Retrieve Stats for a Day from Statspack tables

select  to_char ( trunc ( b.snap_time )
 , 'DD-MM-YYYY' )
 , statistic#
 , name
 , sum ( value )
 from STATS$SYSSTAT A
 , stats$snapshot B
 where a.snap_id = b.snap_id
 and trunc ( b.snap_time ) > trunc ( sysdate - 30 )
 and a.statistic# = 54 < Replace
 with Stats
 Number Below > group
 by trunc ( b.snap_time )
 , statistic#
 , name
 order by trunc ( b.snap_time );

Read more…

How to find current Statspack level?

Posted by Sagar Patil

1. Look at table PERFSTST.STATS$SNAPSHOT
2. Run spreport.sql and you will notice it along with the snapids listed

Read more…

A database is creating more than 100 logs every single hour. What is happening ?

Posted by Sagar Patil

Solution: I tried looking into statspack report but it wouldn’t flag the insert/update/delete activity. Also statspack won’t record some of system activity which may contribute to redo logs.Only option to use was “LOGMINER”.

Read more…

Top of Page

Top menu