Skip to content

AWR Report for Standard Edition?

  • by

AWR for Standard Edition

I was assigned to collect some performance data in 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's 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.

But how to check whether the database is a standard edition or not. Let's check 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.

Found Oracle STATSPACK

When I took a real good look at ?/rdbms/admin/*.sql again, I found STATSPACK is still 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 extra license.

In other words, you may regard STATSPACK as AWR standard edition.

STATSPACK Installation

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

1. Execute spcreate.sql

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.

2. Execute spauto.sql

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;

Starting Datetime

To change the starting date, you can set NEXT_DATE like this:

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select job, next_date from user_jobs;

       JOB NEXT_DATE
---------- -------------------
        84 2019-05-27 10:00:00

SQL> exec dbms_job.next_date(84, to_date('2019-06-02 08:00:00','yyyy-mm-dd hh24:mi:ss'));

PL/SQL procedure successfully completed.

SQL> select job, next_date from user_jobs;

       JOB NEXT_DATE
---------- -------------------
        84 2019-06-02 08:00:00

Later, if you want to stop the job, you may issue the command.

SQL> exec dbms_job.remove(84);

PL/SQL procedure successfully completed.

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 troubleshooting 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 *