Oracle Tips and Techniques

Oracle Enterprise Manager 11g is no doubt a very useful tool for database administrators.  Whoever has used it can testify that it makes life so easier.  For example the Oracle Enterprise Manager 11g Segment Advisor can give very useful information at the click of buttons. Getting these information through command line will need significant effort as well as knowledge.

But we must also be aware that it can also have significantly detrimental effect on your production database if you do not control usage.

If you maintain a database in a corporate environment it may be sometime necessary  to give access to your Oracle Enterprise Manager 11g to management stuff who may not be too tech savvy or at least with Oracle technologies. What they may be looking for is just to monitor activities or trends. But if they start running different advisors then it may impact a production database.

Today I was running Segment Advisor for just two tablespaces. One of them was 125G and another 32G. While running the segment advisor from one Oracle Enterprise Manager session I monitored the impact of this activity from another session through the database instance performance tab.

The impact looks significant to me. Note that this database is a non production database and was quite (meaning no serous activity happening at the time of running).

This is how looks in Top Activity screen

Looking at the I/O usage looks much scarier:

Whenever I run the segment advisor (twice in the above screenshot) the I/O MB per sec and I/O Requests per sec increases significantly.

Imagine running this type of activity in an OLTP production database. It will seriously impact the database performance as normal database operations will need to compete with non significant advisor related activity for CPU time as well as memory.

So it is advisable to run these advisors in quiet times whenever possible unless it is really urgent.