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.
I collected performance data in an unfamiliar database. After I run the AWR report, run the command
Got an error:
Is there a chance that the diagnostic package is disabled?
Let's see the parameter CONTROL_MANAGEMENT_PACK_ACCESS.
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.
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.
Creating a STATSPACK with a scheduled task requires at least two steps.
1) Run the spcreate.sql script.
I suggest that you go to the <ORACLE_HOME> / rdbms / admin directory as your current working directory, because all logs will remain in this directory at run time.
Run the script:
Let's see what we have in this script:
It requires 3 basic SQL scripts to run.
spcusr.sql
In the spctab.sql table, 71 STATSPACK tables will be created.
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).
In which he presents such work:
And then show the next run time.
For deeper statistics, you need to change the snapshot level to 7 instead of the default level 5.
This command will change the binding level to 7 and simultaneously take a snapshot.
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 startandendvaluesfortimemodel stat: parsetime elapsed WARNING (-20023) ORA-20023: Missingstartandendvaluesfortimemodel stat: DB CPU WARNING (-20016) ORA-20016: Missingvaluefor 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
NONEI 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 that you go to the <ORACLE_HOME> / rdbms / admin directory as your current working directory, because all logs will remain in this directory at run time.
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.