Home » RDBMS Server » Performance Tuning » Performance monitoring
Performance monitoring [message #235136] Fri, 04 May 2007 02:54 Go to next message
Messages: 29
Registered: February 2007
Junior Member

Could somebody please answer to my question: Oracle 9i had to be configure with statspack's. Do we need to do the same on Oracle 10g too? I was reading an article on 10g and it said, 10g has AWR which does the same thing as Statspack in earlier version and we may not need to configure statspack in 10g. is it true? If yes, how could i configure AWR?

Appreciated if someone please take a moment to answer this.

Many thanks in advance,
Re: Performance monitoring [message #235139 is a reply to message #235136] Fri, 04 May 2007 03:04 Go to previous message
Messages: 48
Registered: February 2007
The concepts for AWR and Statspack similar in the sense both make use of internal V$ views, store them, and analyze them.

Both types of snapshots are uniquely identified via the SNAP_ID column.Similarly named tables store the same type of data in both environments.

Both STATSPACK and AWR produce similar reports using packaged SQL*Plus scripts. STATSPACK uses spreport.sql, while AWR uses scripts specific to AWR.

While STATSPACK needs to be installed manually, AWR is installed, configured, and managed by default.
STATSPACK snapshots impose a reasonable load during collection. However, AWR collections occur continually and are offloaded to selected background processes, allowing smoother operation.

STATSPACK is not accessible via OEM for viewing or management, whereas AWR is accessible both via the OEM as well as via SQL and PL/SQL for viewing and management.

The way high-impact or high-load SQL is captured in AWR is quite different from STATSPACK. While STATSPACK scans V$SQL for high-load SQL based on a certain set of defaulted lower limits, such as on number of logical and physical I/Os per stored SQL statement, AWR recognizes high-load SQL as it occurs. This enables accurate capture of the right SQL data as it occurs, rather than collecting high-load SQL from V$SQL, which may capture SQL that occurred prior to, and thus outside of, the snapshot period. This was a major weakness in STATSPACK.

STATSPACK data is stored in the PERFSTAT schema in any designated tablespace, while AWR data is stored in the SYS schema in the new SYSAUX tablespace.

STATSPACK in Oracle Database 10g
STATSPACK is also available for Oracle Database 10g. The PERFSTAT schema is installed by default, although no STATSPACK related objects are created. The standard sp*.sql scripts are available in the RDBMS\ADMIN directory.

Previous Topic: Problem with spreport.sql
Next Topic: Movting Index partitions to differenet tablespaces.
Goto Forum:

Current Time: Mon Oct 24 15:10:10 CDT 2016

Total time taken to generate the page: 0.07228 seconds