One of the things that Oracle has been taking care of well are the repetitive database administration tasks making it easier for DBA. I can easily remember the days when the long path of Pfile was required to start the database. Pfile in itself is a lifesaver in certain situations but being able to use spfile instead of the manually edited Pfile is really a great advantage. Oracle’s constant process of making things easier continues with every new release of the database. In 10g Oracle introduced the automated maintenance tasks feature. It automated two of the very important tasks. In 11g Oracle further added an additional job and now uses the AUTOTASK framework. The three automated tasks in 11g are below.
Automatic Optimizer Statistics Collection
Gathering statistics for the objects of a database is something that every DBA needs to do because database performance is dependent on it. Oracle made this task automated in 10g so that you don’t have to worry about doing it manually every day. This task runs in maintenance windows defined by Oracle by default. The default configuration specifies that this task is run on daily basis as well as on weekends. Depending on the configuration that you may have for your database it is recommended that you schedule this task every 4 hours. Statistics about database object help the DBA to better understand the condition of database server. For example if we insert bulk data into table then it is evident that its size will grow but we cannot be sure of how much until and unless we have statistics up to date. This is just one example and ongoing production database may have hundreds of operations like this and if your statistics are stale then you can never be sure of your database’s health.
Automatic Segment Space Advisor
This task checks the table segments and advises the DBA on segments which have free space that can be reclaimed. This task was also introduced in 10g. Again this task runs daily and it is advised that it should be run in every 4 hours. Although most of segment space management is automated with uniform extent size but still there are situations where you need to check segment space and check if there is free space available. There may be thousands of segments inside the database and keeping track of all of them will always be quite hectic. But this automated task does the trick and most of the work and you have clear picture regarding what segments need your attention.
Automatic SQL Tuning Advisor
This is new to 11g and basically checks the SQL queries that took longer time to process and advises the DBA about how to tune them. Part of its job is also to auto tune by applying SQL profiles on the statements under consideration. This can also be a great time saver especially for a busy DBA. The fact that it implements SQL profiles to problematic queries can help most of the queries get auto tuned. If there are queries which need more than just implementing new SQL profiles like rebuilding or creating new indexes, this task also gives suggestions for those extra actions that are needed to be taken as well. By default all of these tasks are enabled and scheduled to run during the Oracle Scheduler maintenance window every night. During this maintenance window, Oracle scheduler creates a job for each one of these tasks and once the task is completed it drops that job.
What is so inspiring about this feature is the ease and convenience that it brings for these tasks which are not optional as they are required for smooth functioning of the database. Not only is it hectic performing these tasks manually, but it is also time consuming. Also it is quite possible that you may not be able to run them as good as Oracle itself. Even if you want to automate it yourself, a great amount of time is required developing it and the effort to configure it properly. With these new AUTOTASK jobs you can manage these tasks very easily. The DBA_AUTOTACK_OPERATION view provides the name of the task which are enable.
SELECT CLIENT_NAME, STATUS
Here the client name will be the name of the task and status will be either “Enabled” or “Disabled”. If you have not changed anything then all three tasks will be enabled.
As mentioned above these tasks are normally scheduled to run in maintenance window every night. But if you want to know the exact timings of their runs then you can the following command.
SELECT * FROM
The output will provide the timings of maintenance window during which these tasks are supposed to run. Oracle also provides a PL/SQL package to manage these tasks i.e. DBMS_AUTO_TASK_ADMIN. You can use this package to enable or disable some or all of the tasks. If you want to disable all of these tasks then you can use the following command.
To enabled all the tasks
To enable or disable a specific task, you can provide the job name
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE (‘auto optimizer stats collection’, NULL, NULL);
Additionally Enterprise Manager can be used for configuring these tasks graphically.
Further Reading: Foreign Keys on Virtual Columns, IPv6 Support and more in 11g.