Here’s an odd little observation that came out of the UKOUG conference last week:
If you’re running 10g, and don’t have the relevant performance pack and diagnostic licences there are various views you are not allowed to query, packages you are not allowed to run, and reports you are not allowed to read.
To support the performance diagnostics, though, your database is probably churning away constantly dumping all sorts of useful data into the memory structures and tables that you are not allowed to access.
But that’s no longer a problem – it looks as if you can disable the AWR collection very easily in 10.2 with the following procedure call:
execute dbms_workload_repository.modify_snapshot_settings(interval => 0)
There’s just one problem with this – you can’t run this package unless you’re licensed to run the AWR. So if you want to disable the AWR, it looks like you have to pay to run it first.
Oracle Scratchpad 
Ouch.
Comment by Adam M. Donahue — November 19, 2006 @ 8:30 pm GMT Nov 19,2006 |
Yes, the entire licensing around AWR is horrible. I mentioned disabling statistics collection in the way you mention a year ago over on Dizwell, and Niall Litchfield pointed out at that time the licensing contradiction you mention (i.e., you can only disable it by running a package access to which requires you to purchase the license you’re seeking to avoid).
If you set STATISTICS_LEVEL=BASIC, that disables AWR statistics collection legally (though it disables a lot more than just AWR, unfortunately!)
Niall Litchfield’s petition regarding AWR licensing is still available for signing over at http://www.petitiononline.com/oraman/petition.html
Comment by Howard Rogers — November 20, 2006 @ 12:41 am GMT Nov 20,2006 |
Does Oracle offer a 2-second license to run that statement ? ;)
Comment by Alberto Dell'Era — November 20, 2006 @ 7:47 pm GMT Nov 20,2006 |
Howard,
Thanks for pointing out the petition.I have added myself to it.
Most customers are not even aware that they are not licensed for AWR.
Comment by Fairlie Rego — November 20, 2006 @ 11:49 pm GMT Nov 20,2006 |
I like the petition, but I think it should be taken a step further and reference the inability to “opt out” of ASH/AWR. These features consume “my resource” without any benefit to myself.
Comment by Daniel Fink — November 21, 2006 @ 2:57 pm GMT Nov 21,2006 |
How about this one ?
SQL> update wrm$_wr_control set SNAP_INTERVAL=’+40150 00:00:00.0′ where dbid =;
Comment by anonymous — November 21, 2006 @ 3:48 pm GMT Nov 21,2006 |
Anonymnous, from the Licence:
“All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables.”
wrm$_wr_control is an underlying table of view dba_hist_wr_control – not that I’m keen on updating Oracle’s own tables directly, of course. It looks as if you may have to update snapint_num at the same time.
There’s also the question of whether this disables the collection, or simply disables the flushing to disk at regular intervals. To disable collections, apart from the ‘statistics_level’ parameter, there are a number of parameters with names starting: _ash, _awr, and _swrf that seem to be related, and could be fiddled with.
Comment by Jonathan Lewis — November 21, 2006 @ 4:09 pm GMT Nov 21,2006 |
Statement Text
BEGIN dbms_workload_repository.modify_snapshot_settings(interval => 0); END;
Translates to this:
update wrm$_wr_control set snap_interval = :bind1, snapint_num = :bind2, retention = :bind3, retention_num = :bind4, most_recent_snap_id = :bind5, most_recent_snap_time = :bind6, mrct_snap_time_num = :bind7, status_flag = :bind8, most_recent_purge_time = :bind9, mrct_purge_time_num = :bind10, most_recent_split_id = :bind11, most_recent_split_time = :bind12, swrf_version = :bind13, registration_status = :bind14, mrct_baseline_id = :bind15
where dbid = :dbid
It looks as if you may have to update snapint_num at the same time ?
I think so…. I can check binds more closely…
Comment by anonymous — November 21, 2006 @ 4:30 pm GMT Nov 21,2006 |
_awr_restrict_mode might be the solution still not very clean, though. It looks like it disables AWR functionality and is supposed to be used for recreating AWR objects. I was looking on the Metalink and there are only indirect references to it. Credit for uncovering this parameter (at least for me) goes to Fairlie Rego who mentioned it in Oracle-L list about a week ago.
Comment by Alex Gorbachev — November 24, 2006 @ 1:42 am GMT Nov 24,2006 |
[…] I see that in a comment on the AWR Dilemma that Alex Gorbachev has pointed out the existence of the parameter _awr_restrict_mode as a possible attack on disabling AWR. […]
Pingback by Disabling AWR « Oracle Scratchpad — November 24, 2006 @ 7:22 am GMT Nov 24,2006 |
Wild idea: Just skip AWR at create time.
Has anyone investigated to opt-out of AWR at database-create time ?
Can we somehow skip running CATAWRTB.sql and the likes (which likes exactly) ?
Would the database still work (who has time to try this) ?
In earlier releases, I tended to compose a set of crdb.sql scripts for a lightweight database and fast deployment at any site. my set for 10.2 is rather large still as dont know what I can sensibly skip.
And I would not really want to go without AWR either. It will be part of the future, like it or not.
Comment by PdV — November 27, 2006 @ 5:33 pm GMT Nov 27,2006 |
I recently upgraded an 9.2.0.6/11.5.9 database to 10.2.0.2 in the lab. I used DBUA for upgrade and it seems that it ran catawrtb.sql script during the upgrade. We do not have the Oracle Database 10g Diagnostics and Tuning Packs licensed but I can run the procedure to manually disable AWR, create snapshots manually, etc. Am I missing something here?
Comment by Amir Hameed — December 5, 2006 @ 4:51 pm GMT Dec 5,2006 |
Can you just run ?\rdbms\admin\catnoawr.sql instead??
Comment by John Boogaard — March 28, 2007 @ 8:14 pm BST Mar 28,2007 |
[…] Some time ago I wrote about the problems of disabling the automatic workload repository (AWR). In one of the comments, John Boogaard asked the question: “Can you just run ?/rdbms/admin/catnoawr.sql instead […]
Pingback by AWR - again « Oracle Scratchpad — April 11, 2007 @ 10:34 pm BST Apr 11,2007 |
[…] these restrictions, you can read about them in the Oracle 10g Licensing Information Manual, here, here, and many other places.) What concerns us most is our belief that Oracle Corporation is missing […]
Pingback by Pythian Group Blog » An Open Letter to Larry Ellison on AWR and ASH Licensing — June 30, 2007 @ 1:45 am BST Jun 30,2007 |
[…] and ASH (Active Session History) licensing. This licensing topic has been previously discussed by Jonathan Lewis and Jared […]
Pingback by AWR and ASH Licensing - Letter to Larry Ellison « Mike R’s Blog — June 30, 2007 @ 9:20 pm BST Jun 30,2007 |
I had to sign that latter. This matter is endless.
Why couldn’t they come up with means to force the restriction while also allowing to disable it completely?
Comment by Vladimir Sadilovskiy — July 1, 2007 @ 1:55 pm BST Jul 1,2007 |
[…] As you’ll find linked in the open letter posting above, I found more interesting reading in this area in a few articles, one by Jared Still on DBAzine.com, and from last year, another by Jonathan Lewis. […]
Pingback by DanNorris.com » Licensing continues to "uninterest" me — July 2, 2007 @ 3:13 pm BST Jul 2,2007 |
I just found out about this restriction in reviewing Metalink. Worst part is that back in April the Oracle note 276103.1 talks about how to run the reports manually, which would imply that it is “okay” to run them in that mode. After all why on earth would you run them manually if you had Diagnostics Pack license?
This is without a doubt the worst thing Oracle has done in licensing. If AWR belongs to the Diagnostic Pack, then why not put the installation in to the database as part of the procedure for installing it on OEM? Not the first time anyone has had to install a client and server piece for an application. It is like the whole issue with OEM in the first place, where you add a new database, and it defaults to making all the packs accessible for it.
Oh well, they’ll learn when their market shares start tumbling as people move to other products, not because they’re better products, but strictly to spite Larry and crew over lousy licensing practices.
Comment by Greg Minor — October 2, 2007 @ 10:25 pm BST Oct 2,2007 |
We had Oracle consulting come out on site. What is the first thing they did? Ran an AWR report and we weren’t even licensed for it. I guess it’s something that no one is enforcing.
Comment by Tom — February 19, 2008 @ 5:35 pm GMT Feb 19,2008 |
See http://www.pythian.com/blogs/538/oracle-releases-method-to-disable-awr-collection
Oracle has offered-up a “free” way out! (you’ll need a MetaLink account to see the Note).
Comment by Richard — February 20, 2008 @ 8:08 am GMT Feb 20,2008 |
I’ve come across a new issue with AWR, i’m not sure if this is related to your post, but when i run reports it is restricting how many results it will allow you to receive. Well, thats purely with Google, other SE’s haven’t restricted just yet, but surely its a matter of time.
Comment by Steve Usher — August 15, 2008 @ 11:01 am BST Aug 15,2008 |
In 11g, the diagnostic pack is part of EE license. I guess all of the petition worked.
Comment by Eric Du — August 20, 2008 @ 6:07 pm BST Aug 20,2008 |
Eric, can you provide a link for that information.
The 11g documentation still states that the Diagnostic Pack must be purchased:
http://download.oracle.com/docs/cd/B28359_01/license.111/b28287/options.htm#CIHIHDDJ
“In order to use the features listed above, you must purchase licenses for the Diagnostic Pack.”
And it has a price in the Standard Price Lists
Click to access technology-price-list.pdf
Click to access eplext.pdf
Comment by Gary — August 20, 2008 @ 11:07 pm BST Aug 20,2008 |
[…] is neat. (But remember! You will need a diagnostics pack license for that as described here and here – if you need a free technology for performance monitoring, consider using Craig […]
Pingback by Usn’s IT Blog » Creating Oracle AWR reports quicksheet — February 13, 2009 @ 12:44 pm GMT Feb 13,2009 |
[…] 10.2. This was talked about a lot at the conference and was also picked up by Jonathan Lewis on his AWR Dilemma post.It seems that if you’re not licensed to run AWR, then you can quite easily disable it by […]
Pingback by Log Buffer #20: a Carnival of the Vanities for DBA’s « Lisa Dobson's blog for all things Oracle… — March 30, 2011 @ 2:28 pm BST Mar 30,2011 |
[…] it evolves, it slices, it dices! On sale today, and mind that you don’t disable it because that feature’s protected by license (I kid, I kid, they fixed that). And ASH, don’t even get me started. Rolling session-based […]
Pingback by High Performance Tuning Tools - Steve Karam :: The Oracle Alchemist — May 24, 2013 @ 1:27 pm BST May 24,2013 |