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.
- Execute spcreate.sql
- spcusr.sql
- spctab.sql
- spcpkg.sql
- Execute spauto.sql
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.
