Oracle 11g Release 1
I seem to be hitting all of the AWR report bugs lately. The latest one is that the AWR report is missing data and shows lots of errors of the form:
WARNING (-20023) ORA-20023: Missing start and end values for time model stat: parse time elapsed
A bit of searching on Metalink reveals that this seems to be a bug (#7532789) and is discussed in document 1181573.1. The bug information seems to note Enteprise edition only, but I have found the problem on Standard edition too.
While it might list this as a bug, it is actually saying that your system is in fact not configured to capture diagnostic information. The "bug" is that it gives a nasty error instead of telling you that there is no diagnostic information available because "diagnostic and tuning option is not available or not turned on".
While it might list this as a bug, it is actually saying that your system is in fact not configured to capture diagnostic information. The "bug" is that it gives a nasty error instead of telling you that there is no diagnostic information available because "diagnostic and tuning option is not available or not turned on".
Setting CONTROL_MANAGEMENT_PACK_ACCESS to "DIAGNOSTIC+TUNING" will certainly solve the problem. But beware, you need to be licensed to make use of this functionality. While it might be built into the Oracle database on all database editions, you are not actually allowed to access it. It is an optional extra available with Enterprise Edition only. See license information here and my rant in AWR and Oracle Licensing. It would probably be wise to contact your Oracle consultant and/or Oracle support before making this change.
Anyway, if you have obtained the relevant licensing, then:
Anyway, if you have obtained the relevant licensing, then:
alter system set control_management_pack_access="DIAGNOSTIC+TUNING" scope=both;
For me, after running a few AWR snapshots, most of the data began to appear in the reports. However some of the report sections continued reporting errors. A restart of the database seemed to sort out the last remaining errors. I now have the full AWR report being produced.
Obviously, the old snapshots that were made prior to setting CONTROL_MANAGEMENT_PACK_ACCESS will obviously still report errors. There's absolutely nothing you can do about that. But at least from now on, you will be able to see what your DB is up to.
Aside:
Interesting that they have made diagnostics an optional extra. It is so fundamental to the DBA. Unfortunately for us, procurement will have no idea what any of the options are and will inevitably purchase the "no frills" version. And then everyone will be wondering why Oracle doesn't work. Bad move by Oracle in my opinion. Oracle needs to give DBA's as much free help as possible to diagnose performance problems, and if they don't, the answer will simply be.... "Oracle is slow"!
Special thanks to Hans Forbrich for pointing out the license requirements in his OTN thread reply.
1 comment:
After sleeping on it, I realise that there is in fact a free version that we are allowed to use. It's called STATSPACK, and it's free to the best of my knowledge. But it's not installed by default. Fortunately it is easy to install and use.
All those using AWR, go back to STATSPACK... 10 to 1 you don't have a license to use AWR.
Post a Comment