Autotask clients fall into OTHER_GROUPS on custom plans

The autotask clients run in their own resource consumer groups.  So, stats, space, and SQL tuning tasks have some consumer groups that you have to include in your own plans if your plan will be active while the window for these tasks is open.

Here are the Oracle supplied consumer groups for the autotask clients:

SQL> select client_name,consumer_group
   2 from DBA_AUTOTASK_CLIENT;

CLIENT_NAME                      CONSUMER_GROUP
-------------------------------- ------------------------------
auto optimizer stats collection  ORA$AUTOTASK_STATS_GROUP
auto space advisor               ORA$AUTOTASK_SPACE_GROUP
sql tuning advisor               ORA$AUTOTASK_SQL_GROUP

In a case I was working on our custom resource plan was active but didn’t include any of the consumer groups listed above.  So, when stats ran it fell through to the other_groups part of the plan.  Sadly, other_groups was set to 0% which should never be the case and this caused the stats job to hang when the CPU use was high.

So, don’t make other_groups 0% or include these special autotask consumer groups in your plan if your plan will be active during the maintenance windows.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.