AWR Report for Standard Edition?

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 on, 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.