Database Automation - Oracle 11g

Automating routine DBA tasks not only enhances your productivi ty but also diminishes the chances of making on-the-job errors.Higher database availability, proactive database management, and improved performance are all benefits of automating data base management processes. Oracle has continually automated the DBA tasks, making vast strides in memory and storage management in recent releases of the data base. In Oracle Data base 11g, you’ll find that the following areas have been further automated:

  • Memory management:Oracle Database 11g introduces a much simplified, unified tuning method for both the SGA and the PGA.
  • Database maintenance tasks feature: This now includes a new task,the Automatic SQL Tuning Advisor task.
  • Partition management:The new Partition Advisor, a part of the SQL Access Advisor,provides advice on partitioning methods for tables,indexes,and materialized views.
  • Streams management: The Streams Performance Advisor helps you identify replication bottlenecks.
  • Data Repair: The Data Repair Advisor identifies and fixes problems such as SQL exceptions, missing files,bad data blocks,and bad undo records.

In the following sections,we discuss the new automatic memory management feature and the additions to the automated database maintenance tasks feature.

Automatic Memory Management

Over the past couple of releases (starting with Oracle Database 9iprimarily),Oracle has been steadily moving toward completely auto mating the allocation of memory for Oracle instances.The process of automation really started with Oracle Data base 9i (sga_target), continued in Oracle Data base 10g (automatic PGA management with the pga_target parameter),and now culminates in Oracle Data base 11g with the intro duction of a new capability called automatic memorymanagement, which provides a completely hands-off way to manage memory. With this method,a single parameter called memory_target lets you automate memory allocation for both the SGA and PGA components of Oracle's memory.

Once you set the memory_target parameter, Oracle automatically allocates the SGA and the PGA memory and automatically redistri butes memory between these two components as dictated by the database work load.Oraclerecommends you use automatic memory management to simplify the allocation of memory to Oracle instances.

Oracle still supports the manual management of memory as well as thepartially automatic memory management introduced in Oracle Database 9i and Oracle Data base 10g. To avoid confusion, here are the different ways you can manage Oracle memory allocation in Oracle Data base 11g:

  • Automatic memory management: This is new to Oracle Data base 11g and involves the use of the memory_target (and memory _max_target) initialization parameter.
  • Automatic shared memory management and automatic program global area management: These use the sga_target and pga_target parameters to set values for the SGA and aggregate PGA.the database then tunes the SGA and PGA components.
  • Manual shared memory management: This means you manually set values for each component of the SGA,such as the buffer cache and the shared pool, with parameters such asdb_cache_size and shared_pool_size.
  • Manual PGA memory management: This involves setting various *_AREA_SIZE parameters(for example,HASH_AREA_SIZE) to control the size of the SQL work areas.

You can still use manual shared memory and PGA management as well as automatic SGA and automatic PGA management in Oracle Data base 11g. How ever,Oracle strongly recommends switching to the simple and efficient automatic memory management, which we discuss in detail here.

Automatic memory manage ment unifies the management of the SGA and the PGA.The memory allocation mechanism adjusts automatically to changes in database work load and practically eliminates any out-of-memory errors. Oracle uses an indirect memory transfer mechanism to move memory back and forth between the PGA and the SGA.

The memory transfer utilizes the operating system,which frees the shared memory and releases it to the operating system,from where other components can request additional memory.

You can still set specific minimum values for the various caches using familiar para meters such as shared_pool_size and db_cache_ size. If you do set values for any of these parameters,the data base won’t autotune them any longer, instead taking the values that you manually set as the minimum values for those caches.

New Automatic Memory Initialization Parameters

There are two key new memory-related initialization para meters in Oracle Data base 11g that let you set up automatic memory mana gement for a data base instance. You set the target memory size ini tialization parameter named memory_target, to specify the memory you want to allocate for an Oracle instance the optional second parameter is the maximum memory size initialization parameter (or memory_max_size),and it sets the maximum memory you can allocate to the instance.

The memory_max_size parameter’s value sets the ceiling on the value of the memory_target parameters.The memory_max_size para meter is static, while the memory_target parameter is dynamic. Thus,you can calibrate the value of the memory_target parameter up and down,as long as you don’t exceed the maximum possible value for memory allocation which is the value set for the memory_max_ size parameter.

Adopting Automatic Memory Management

You can choose automatic memory management for Oracle’s memory manag ement either when you’re creating a new data base or later.

At Database Creation Time

If you want to specify automatic memory management during databasecreation,set a value for the memory_target and memory_ max_ target parameters in the initialization parameter file before you execute the create data base SQL statement,as shown here:

memory_target = 1000m
memory_max_target = 1500m

If you’re using the DBCA to create the new database and choose Advanced Installation,you can select the automatic memory management option during database creation.If you choose the Basic Installation option instead, automatic memory management is the default.

You can arrive at a tentative size for the memory_target parameter by adding the memory allocations you currently make using the sga_target andpga_target parameters.You can set the value of the memory_max_target parameter based on the physical memory you have available for theinstance.Note that even with the automatic memory management method,you can still set minimum values for both the SGA and the PGA.

After Database Creation

If you want to convert to automatic memory management, you can do so by using the two automatic memory-relatedparameters, memory _target andmemory_max_ target.Even though memory_target is a dynamic parameter, you can’t switch to automatic memory management while the database is running.Once you add the new memory parameters,you must restart the data base for the parameters to take effect and automatic memory management to come into force. Here are the steps:

  1. If you’re using an init.ora file for your initialization parameters, just add the two automatic memory parameters, memory_target and memory_max_target, as shown in the previous section, and then restart the data base If you’re using a server parameter file instead, use the following command to record the new memory parameters in your spfile first:
    If you omit setting the memory_max_target parameter, the value of the parameter defaults to the value you set for the memory_target parameter.
  2. Shut down and restart the instance for automatic memory management to come into force.Once you do this, you can adjust the value of the memory_target para meter dynamically, without an instance restart.

The memory_max_target para meter isn’t dynamic, so the value you set for it in the previous step will come into force only after the instance restart. If you adopt automatic memory management by setting the memory_target parameter to a value greater than 0, then the following will be true:

  • If you don’t set the sga_target and pga_target parameters (or set both of these parameters to zero), Oracle will completely automate memory management and will not use any minimum values for either sga_target or pga_target. When the instance starts, it gives 60 percent of the memory target to the SGA and 40 percent to the PGA.
  • Any values you set for the sga_target and pga_target parameters are treated as the minimum values for those parameters.The size of the memory_target parameter will be equal to the sum of the pga_target and sga_target parameter values.
  • If you set either the sga_target or pga_target parameter, Oracle will autotune both the parameters and will set the value of the parameter you didn’t specify to the difference between memory_target and the value of the parameter you did set.
  • If you omit the memory_max_target parameter, it defaults to the value you set for the memory_target parameter.If, on the other hand, you set the memory_max_ tar get parameter but omit the memory_target parameter,the memory_target parameter will default to zero.However,after starting the instance,you can dynamically change the memory_target parameter to any value up to the value you set for the memory_max_target parameter.

You can simply use Database Control to turn the automatic memorymanagement feature on by following these steps:

  1. On the database home page, click the Server tab.
  2. Click the Memory Advisors link in the Database Configuration section.
  3. On the Memory Advisors page, click the Enable button to enable automatic memory management.

You can check current memory allocation under automatic memory management by using the show parameter memory command:

The query shows that the memory_target parameter is set at 240M and the memory_max_target parameter is at 252M.

After Database Creation

Monitoring Memory Allocation

For guidance on the appropriate value for the memory_target parameter, use the V$MEMORY_ TARGET_ ADVICE view, which provides advice on memory sizing based on the current values for the memory _target parameter and the potential decrease or increase in the db_time para meter, which indicates the time spent by the instance on database calls, based on increasing or decreasing the value of the memory_target parameter.

In this example, the memory_target parameter is set at 356MB. As you can see from the estd_db_time column,there is no signific ant reduction in db_time, even if you double the value of the memory_target para meter.How ever, if you see a substantial reduc tion in db_time that correlates to higher memory_target parameter values, you must consider raising the value of the memory_target parameter.

The sga_target parameter is dynamic,so you can always use an alter system statement to calibrate the size of the para meter.Since the database needs some of the components to be at a certain minimum size,it prevents you from setting the value too low for the parameter.

The V$MEMORY_DYNAMIC_COMPONENTS view shows the current status of all memory components:

Note that although you don’t have to set them anymore, the data base will internally set and manage the sizes of both the sga_ target and pga_target parameters.

You may on occasion get the following error(the following was on a Linux system) when you use automatic memory management to allocate memory to the Oracle instance:

SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system

Contrary to what the error seems to indicate,the previous error more likely means that th ekernel parameter/dev/shm wasn’t correctly sized on your system.Make sure the/dev/shm allocation is at least equal to thesga_max_size parameter’s value.

Automated Data base Maintenance Tasks

In the Oracle Database 10g release, Oracle for the first time provided the automated main tenance tasks feature with which you could schedule predefined data base maintenance tasks during various maintenance windows. The primary focus of these maintenance tasks is to collect information about things such as optimizer statistics and segment free space.

Oracle Database 11g enhances the automated maintenance tasks feature by adding a new automated maintenance task—the SQL Tuning Advisor task.In add ition,there are improvements in the management of maintenance windows and resource allocation.You can now have a separate maintenance window for each day of the week.In Oracle Data base 10g,to control the allocation of CPU resources to the automatic system tasks,you had to create a resource plan and then assign that plan to a maintenance window.Orale Database 11g comes with a default resource manager plan enabled.This default plan contains a subplan that controls the resources consumed by the automated maintenance tasks.

Oracle Data base 11g provides a new PL/SQLpackage,DBMS_AUTOTASK_ADMIN, to manage the automated management tasks.In addition,you can employ Database Control to manage autotasks.We’ll cover the improvements in the three areas of automatic maintenance task management where there are enhancements in the Oracle Data base 11g release default automated maintenance tasks, maintenance windows,and the resource manager plan for automated tasks—in the following sections.

Automated Maintenance Tasks

Oracle Database 11g comes with three predefined automatic maintenance tasks,two of which you’re familiar with from Oracle Database 10g:

  • The Automatic Optimizer Statistics Collection task collects statistics for all objects with no or stale statistics.
  • The Automatic Segment Advisor task provides advice on which database segments have free space that you can reclaim.

Oracle Data base 11g adds a new automatic maintenance task, called Auto matic SQL Tuning Advisor task, which examines SQL statement performance and makes SQL profile recommen dations to improve the statements.If you want, you can also configure the Automatic SQL Tuning Advisor task so it automatically implements its SQL Profile recommendations.

You can query the new view DBA_AUTOTASK_TASK to find out the names and status of the automatic tasks in your database,as shown in the following example:

The output of the query shows that all three of the predefined automated maintenance tasks are enabled by default.

New Maintenance Windows

All automated maintenance tasks are scheduled to run during the Oracle Scheduler maintenance window,which is part of the window group named maintenance_window_group.A maintenance window is simply a time span during which a job could be run by the Oracle Scheduler.The window usually is run according to a repeating interval such as every Sunday between 12 a.m. and 6 a.m.,for example.The Oracle Scheduler creates a job when the window “opens” for eachmaintenance task that’s scheduled to run during the maintenance window.When the automated task job completes, the Oracle Schedulerautomatically drops that job. Unlike the other two auto mated maintenance tasks.

The Automatic SQL Tuning Advisor runs only once during each maintenance window.If you have a long maintenance window, the database runs the other two maintenance tasks every four hours.

Upon installing Oracle Database 10g,two schedule windows were predefined:a WEEKNIGHT_WINDOW and a WEEKEND_WINDOW.In Oracle Data base 11g,there are seven predefined maintenance windows, five for each of the weekdays and two windows for the two days in the weekend, Friday and Saturday.Each of the seven predefined windows is named after the day of the week,as in MONDAY_ WINDOW and SUNDAY_WINDOW, for example. Each of the weekday windows starts at 10 p.m. and ends at 2 a.m.,provi ding a time span of four hours to execute scheduled maintenance tasks.The two weekend windows provide a much longer time span of 20 hours each.You can view all the predefined maintenance windows by querying the DBA_AUTOTASK_SCHEDULE view, as shown here:

Note that there’s a separate window for each day of the month, named for the day of the week.When a maintenance task runs past the operational window, it continues to completion, based on the priority you allocate to that task.

Default Resource Manager Plan

All maintenance windows use the resource plan DEFAULT_MAINTENANCE_PLAN by default, with the automated tasks running under the subplanORA$AUTOTASK _SUB_PLAN You can query the DBA_RSRC_PLAN_DIRECTIVES view to find out the resource allocations for this subplan.

You can change the resource allocation for any of the automated maintenance tasks by changing the resource allocation for the subplan ORA$AUTOTASK_SUB_ PLAN.Of course, this will also involve adjusting resource allocation for other subplans or groups in the DEFAULT_MAINTENANCE_PLAN resource plan to keep the total resource allocation at 100 percent.Please refer to the Oracle manual titled Data base Administrator’s Guide for more details on resource allocation.

Each of the automated maintenance tasks belongs to a special resource consumer group.The following query on the DBA_RSRC_ CONSUMER_GROUPS view shows the various resource onsumer groups.

Note that there is a separate High Priority consumer group for each of the three automatic maintenance tasks (space_group, sql_ group,stats_group).In addition, there is also a consumer group named health_group for supporting the Health Monitor ,which performs automatic database health checks.

All three maintenance tasks run in all pre defined maintenance windows,but you can disable any of the tasks by using the disable procedure of the DBMS_AUTO_TASK_ADMIN package.Similarly, you can modify a maintenance window or create a new maintenance window to suit your needs.

The main automated database management functions include modi fying the automated tasks, managing the maintenance windows,and allocating resources for the various windows.We briefly cover each of these functions in the following sections.

Modifying the Automatic Tasks

DBAs and developers can now use the new DBMS_AUTO_TASK_ADMIN package to modify maintenance task execution.You can use this package to enable and disable automatic main tenance tasks in some or all maintenance windows.You can disable all automatic maint enance tasks,for example,by executing the disable procedure with out any arguments,as shown here:

SQL> execute dbms_auto_task_admin.disable;

You can reenable all the automated tasks with the enable procedure, as shown in the following example:

The client_name column in the DBA_AUTOTASK_OPERATION view identifies the client(for example,the SQL Tuning Advisor),and the operation_name column ident ifies the actual automatic maintenance task (for example,automatic sql tuning task),as shown by this query:

If you would rather disable only a specific task,specify the client_name parameter when executing the disable procedure, instead of executing it with out any parameters.You can disable a maintenance task for a specific window by using the window_name argument,as shown in this example:

The preceding PL/SQL code will disable the maintenance task temporarily until you reenable it.

Modifying the Maintenance Windows

You can customize the predefined maintenance windows or create more convenient windows by using the DBMS_SCHEDULER package.You can remove maintenance window by using the remove_window_group_memberprocedure,as shown here:

Use the create_window procedure of the DBMS_SCHEDULER package to create a new window.To create a maintenance window,you must add this new window to the window group maintenance_window_group.Call theadd_window_group_member procedure to add your new window to the group named maintenance_window_group.Here’s an example:

Once you create the new window named late_night_window, you must add it to the window group maintenance_window_group by using theadd_window_group_member procedure,as shown here:

The create_window procedure creates the new window calledLATE_NIGHT_WINDOW and adds it to the window group named maintenance_window_group.The duration attribute of the create_windowprocedure sets the window duration at two hours,and the repeat_interval attribute specifies that this window be open daily starting from 10 p.m.

Use the set_attribute procedure of the DBMS_SCHEDULER package to modify any attributes of a maintenance window,such as the dura tion of the maintenance window,for example.Use the DBMS_SCHEDULER package’s enable and disable proce dures to disable a maintenance window before changing its attributes and then to reenable the window once you change its attributes.You can remove a maint enance window by using theremove_window_group_member procedure.

Tracking Job Runs

You can track the various maintenance job runs by issuing a query such as the following on the DBA_AUTOTASK_HISTORY view:

Notice that all three database maintenance jobs run daily at 10 p.m. by default.You can use the following procedures from the DBMS_AUTO_TASK_ADMIN package to perform various management tasks relating to the automatic maintenance tasks:

  • get_p1_resources: This procedure returns the percentage of resources allocated to each of the four high-priority resource groups relating to the maintenance tasks.
  • set_pi_resources: Use this procedure to modify the resource allocation to each of the resource groups used by the automatic maintenance task (AUTOTASK) clients.
  • set_attribute: This procedure lets you set attributes for a task, a client, or an operation.
  • override_priority: You can use this procedure to override task priorities at the client,operation, and task level.For example,you can set the priority of a task to urgent,thereby making it jump to the top of the maintenance window the next time the data base executes that task.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 11g Topics