STATSPACK

How to Workaround Oracle AWR in Standard Edition

Oracle AWR in Standard Edition

I was assigned to collect performance data on an unfamiliar database. While I ran AWR report by executing @?/rdbms/admin/awrrpt.sql under SQL prompt, I got an empty report with many errors (ORA-20023 and others) like this:

WARNING (-20023) ORA-20023: Missing start and end values for time model stat: parse time elapsed WARNING (-20023) ORA-20023: Missing start and end values for time model stat: DB CPU WARNING (-20016) ORA-20016: Missing value for SGASTAT: free memory …

Is there any chance that the diagnostic pack is disabled? Let’see the parameter CONTROL_MANAGEMENT_PACK_ACCESS.

SQL> show parameter control_management_pack_access

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_management_pack_access       string
NONE

I was a little cautious about the NONE value. Since the enterprise edition includes the license of diagnostic pack whereas standard edition does not, I could have no right to use Oracle AWR in Standard Edition. Let’s see the version information.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

As we can see, there’s no “Enterprise Edition” in version information. It’s definitely a standard edition database. Well, at least I know I have no right to use it.

When I took a real good look at ?/rdbms/admin/*.sql again, I found STATSPACK is still in there, which is an original performance data gathering tool for older databases and very much like AWR in features. The best thing is that we can use STATSPACK as a substitute of AWR in standard editions without limits.

STATSPACK Installation

At least two steps should be done to create STATSPACK with scheduled job.

  1. Execute spcreate.sql
  2. I suggest you to switch to <ORACLE_HOME>/rdbms/admin as your present working directory, because all logs during executions will stay with this directory.

    SQL> @spcreate.sql;

    Let’s see what we have in this script.

    ...
    @@spcusr
    connect perfstat/&&perfstat_password
    @@spctab
    @@spcpkg

    There’re 3 main SQL scripts needed to run.

    • spcusr.sql
    • It will create an user called PERFSTAT, the default tablespace will be SYSAUX and the dafault temporary tablespace will be TEMP.

      Both tablespaces can be customized, but you have to make sure the tablespaces exist in you database before running this script.

      You need to remember the password in case you want to do some routines at a later time.

    • spctab.sql
    • There’re 71 tables of STATSPACK in sequence will be created in spctab.sql.

      STATS$DATABASE_INSTANCE
      STATS$LEVEL_DESCRIPTION
      STATS$SNAPSHOT
      STATS$DB_CACHE_ADVICE
      STATS$FILESTATXS
      STATS$TEMPSTATXS
      STATS$LATCH
      STATS$LATCH_CHILDREN
      STATS$LATCH_PARENT
      STATS$LATCH_MISSES_SUMMARY
      STATS$LIBRARYCACHE
      STATS$BUFFER_POOL_STATISTICS
      STATS$ROLLSTAT
      STATS$ROWCACHE_SUMMARY
      STATS$SGA
      STATS$SGASTAT
      STATS$SYSSTAT
      STATS$SESSTAT
      STATS$SYSTEM_EVENT
      STATS$SESSION_EVENT
      STATS$WAITSTAT
      STATS$ENQUEUE_STATISTICS
      STATS$SQL_SUMMARY
      STATS$SQLTEXT
      STATS$SQL_STATISTICS
      STATS$RESOURCE_LIMIT
      STATS$DLM_MISC
      STATS$CR_BLOCK_SERVER
      STATS$CURRENT_BLOCK_SERVER
      STATS$INSTANCE_CACHE_TRANSFER
      STATS$UNDOSTAT
      STATS$SQL_PLAN_USAGE
      STATS$SQL_PLAN
      STATS$SEG_STAT
      STATS$SEG_STAT_OBJ
      STATS$PGASTAT
      STATS$PARAMETER
      STATS$INSTANCE_RECOVERY
      STATS$STATSPACK_PARAMETER
      STATS$SHARED_POOL_ADVICE
      STATS$SQL_WORKAREA_HISTOGRAM
      STATS$PGA_TARGET_ADVICE
      STATS$JAVA_POOL_ADVICE
      STATS$THREAD
      STATS$FILE_HISTOGRAM
      STATS$EVENT_HISTOGRAM
      STATS$TIME_MODEL_STATNAME
      STATS$SYS_TIME_MODEL
      STATS$SESS_TIME_MODEL
      STATS$STREAMS_CAPTURE
      STATS$STREAMS_APPLY_SUM
      STATS$PROPAGATION_SENDER
      STATS$PROPAGATION_RECEIVER
      STATS$BUFFERED_QUEUES
      STATS$BUFFERED_SUBSCRIBERS
      STATS$RULE_SET
      STATS$OSSTATNAME
      STATS$OSSTAT
      STATS$PROCESS_ROLLUP
      STATS$PROCESS_MEMORY_ROLLUP
      STATS$SGA_TARGET_ADVICE
      STATS$STREAMS_POOL_ADVICE
      STATS$MUTEX_SLEEP
      STATS$DYNAMIC_REMASTER_STATS
      STATS$IOSTAT_FUNCTION_NAME
      STATS$IOSTAT_FUNCTION
      STATS$MEMORY_TARGET_ADVICE
      STATS$MEMORY_DYNAMIC_COMPS
      STATS$MEMORY_RESIZE_OPS
      STATS$INTERCONNECT_PINGS
      STATS$IDLE_EVENT

    • spcpkg.sql
    • It will create necessary stored procedures of STATSPACK for PERFSTAT.

  3. Execute spauto.sql
  4. This script will submit a job with sheduled interval (1 hour).

    SQL> @spauto.sql;

    In which, it submit a job like this:

    SQL> begin
      2    select instance_number into :instno from v$instance;
      3    dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
      4    commit;
      5  end;
      6  /

    And then show the next execution time.

    SQL> select job, next_date, next_sec
      2    from user_jobs
      3   where job = :jobno;

More Considerations

For later tuning more easily, you may want to gather statistics more deeply at segment level which is more meaningful and helpful, please change the snapshot level into 7 instead of the default level 5.

SQL> exec statspack.snap(i_snap_level => 7, i_modify_parameter => 'true');

This statement will modify the snap level to 7 and it will also take a snapshot at the same time.

Here I quote a paragraph from Oracle documentation about performance monitoring that can supplement this idea.

Oracle recommends using ADDM and AWR. However, Statspack is available for backward compatibility. Statspack provides reporting only. You must run Statspack at level 7 to collect statistics related to block contention and segment block waits.

For early 9i database, STATSPACK is not easy to do torubleshooting because of some bugs. You may like to know why your snapshot.snap is not working and always returns ORA-00001 and how to fix broken snapshot jobs.

Leave a Reply

Your email address will not be published. Required fields are marked *