In a previous post, I lamented about how I do not like the Compression Advisor that runs automatically in Oracle 11.2. I do not have this option licensed yet Oracle insists on letting me know which tables would benefit from compression when I cannot legally compress them!
In the previous post, I referred to the fact that I first noticed the Compression Advisor running because of a increase in my redo log generation rate. I haven’t verified yet, but it may be an increase because I am running a physical standby and have set the database to FORCE LOGGING. So it is possible that if you are not running a standby, you may not have noticed the increase in redo generation.
As noted in that previous post, I turned off the Space Advisor. Earlier this summer, I had a need to turn the Space Advisor back on even though it meant I would also be turning on the Compression Advisor. I knew I would have to live with the increase redo generation rate.
Over the past few months with the Compression Advisor running, I started getting EM alerts once in a while for tablespaces getting full. My application has data for each year. So I have one tablespace for 2007, one for 2008, etc. I thought it odd one weekend when I got an alert saying that the 2007 tablespace was filling up considering it is 2012 and the application isn’t updating the 2007 data any more. But then a few minute later, the alert cleared. This was just the Compression Advisor doing its thing. It is unaware that this is 2007 data. Most applications don’t segregate data based on year, so I do not expect this problem to affect many people. But I could see the need to be able to tell the Compression Advisor to ignore tables in certain tablespaces.
More recently, I was working with someone on their table compression. A thought occurred to me that it would be beneficial if I could go back into past Compression Advisor runs as part of the normal maintenance tasks to see which tables would benefit from Advanced Compression. I could point-and-click my way through the advisor recommendations in Enterprise Manager, but that is too cumbersome. With the help of ChetanDBA on the Oracle Support Communities, I was pointed to the correct view to be able to query this info. I was able to come up with this query that you can use to determine which tables to compress for OLTP:
select distinct o.attr1 as owner,o.attr2 as table_name
from dba_advisor_objects o
join dba_advisor_actions a
This increased blood flow helps men maintain quality of erection and http://www.glacialridgebyway.com/windows/Threshing%20Rig%20Alley.html canada viagra cialis also getting erections enough for an intercourse activity. The medication has taken the world by storm and has caused quite a stir, as men from all around the world rate Learn More Here viagra properien and that a vast majority are insistent on viagra. The consequence with this capsule lives in body for around five to viagra samples uk six hours. The Presence of these mechanisms in (super P force) operates splendidly view now viagra 100 mg to assuage the intricacy of sexual mess known as ED and PE. on o.task_id=a.task_id
where a.command=’ENABLE COMPRESSION’
order by owner,table_name;
So far so good. Now back to my 2007 tablespace. When will the Compression Advisor be done analyzing tables in that tablespace? If I could rewrite the above query a little, I could see how many tables in the 2007 tablespace are recommended for compression. In looking at the data, I was surprised by the results. Run this query in your database:
select o.attr1 as owner,o.attr2 as table_name, count(*) as num_recommendations
from dba_advisor_objects o
join dba_advisor_actions a
on o.task_id=a.task_id
where a.command=’ENABLE COMPRESSION’
group by o.attr1,o.attr2
order by num_recommendations desc;
Now for the part that really gets me. I haven’t been running the Compression Advisor for a long time yet it has analyzed my top table over 300 times! Seriously! I have to put up with increase redo generation and alerts on tablespaces due to this monster for it to tell me the same thing over and over again. How about this suggestion…if you’ve already recommended a table for compression, don’t recommend it again! How much will the data really change to decide if compression should or should not be used? If the table could have benefited from compression a month ago, then there is probably a 99.9999% chance that the table could benefit from compression today. The Compression Advisor in the nightly maintenance window should be smarter and not be redoing the same work over and over again.
