How to use STATSPACK instead of AWR in Oracle Standard Edition

Hi, Habr! I present to your attention the translation of the article "How to use statspack instead of awr in oracle standard edition" .

How to use STATSPACK instead of AWR in Oracle Standard Edition.

Problem with reports

I collected performance data in an unfamiliar database. After I run the AWR report, run the command
')

@?/Rdbms/admin/awrrpt.sql 

Got an error:

 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 a chance that the diagnostic package 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 am a little careful about the value of NONE. Since Oracle Enterprise Edition includes a diagnostic package license, but Standart Edition does not, I did not have the right to use it. See 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, "Enterprise Edition" is not displayed in the console. This is definitely the Standart Edition. Well, at least I know that I have no right to use it.

When I looked again at the <ORACLE_HOME> / rdbms / admin directory, I found that STATSPACK is still there, which is the original performance data collection tool for older databases and is very similar to AWR in functions. Best of all, we can use STATSPACK as a replacement for AWR in the Standard Edition without limitations.

Installing STATSPACK

Creating a STATSPACK with a scheduled task requires at least two steps.

1) Run the spcreate.sql script
I suggest you go to the <ORACLE_HOME> / rdbms / admin directory as your current working directory, because all the logs at runtime will remain in this directory.

Run the script:

 SQL> @spcreate.sql; 

Let's see what we have in this script:

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

It requires 3 basic SQL scripts to run.
spcusr.sql

• It will create a user named PERFSTAT, the default tablespace will be SYSAUX, and the temporary tablespace will be TEMP.
• Both table spaces can be configured, but before running this script, you must make sure that table spaces exist in your database.
• You need to remember the password if you want to execute some of the subroutines later. Spctab.sql

In the spctab.sql table, 71 STATSPACK tables will be created.

 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 the required STATSPACK stored procedures for PERFSTAT.

2. Run the script spauto.sql
This script will send the task at a specified interval (1 hour).

 SQL> @spauto.sql; 

In which he presents such work:

 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 run time.

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

More considerations

For deeper statistics, you need to change the snapshot level to 7 instead of the default level 5.

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

This command will change the binding level to 7 and simultaneously take a snapshot.