Developing and Implementing Applications, continued - Switching Consumer Groups
(Page 2 of 8 )

Three of the parameters in the CREATE_PLAN_DIRECTIVE procedure allow sessions to switch consumer groups when resource limits are met. As shown in the previous section, the parameters for CREATE_PLAN_DIRECTIVE include Switch_Group, Switch_Time, and Switch_Estimate.

The Switch_Time value is the time, in seconds, a job can run before it is switched to another consumer group. The default Switch_Time value is NULL (unlimited). You should set the Switch_ Group parameter value to the group the session will be switched to once the switch time limit is reached. By default, Switch_Group is NULL. If you set Switch_Group to the value ‘CANCEL_ SQL’, the current call will be cancelled when the switch criteria is met. If the Switch_Group value is ‘KILL_SESSION’, the session will be killed when the switch criteria is met.

You can use the third parameter, Switch_Estimate, to tell the database to switch the consumer group for a database call before the operation even begins to execute. If you set Switch_Estimate to TRUE, Oracle will use its execution time estimate to automatically switch the consumer group for the operation instead of waiting for it to reach the Switch_Time value.

You can use the group-switching features to minimize the impact of long-running jobs within the database. You can configure consumer groups with different levels of access to the system resources and customize them to support fast jobs as well as long-running jobs—the ones that reach the switch limit will be redirected to the appropriate groups before they even execute.

Implementing Stored Outlines

As you migrate from one database to another, the execution paths for your queries may change. Your execution paths may change for several reasons:

1. You may have enabled different optimizer features in the different databases.
   
2. The statistics for the queried tables may differ in the databases.
   
3. The frequency with which statistics are gathered may differ among the databases.
   
4. The databases may be running different versions of the Oracle kernel.

The effects of these differences on your execution paths can be dramatic, and they can have a negative impact on your query performance as you migrate or upgrade your application. To minimize the impact of these differences on your query performance, you can use a feature called a stored outline.

A stored outline stores a set of hints for a query. Those hints will be used every time the query is executed. Using the stored hints will increase the likelihood that the query will use the same execution path each time. Hints decrease the impact of database moves on your query performance. You can view the outlines and related hints via the USER_OUTLINES and USER_OUTLINE_HINTS views.

To start creating hints for all queries, create custom categories of outlines and use the category name as a value of the CREATE_STORED_OUTLINES parameter in the database initialization file, as shown here:

CREATE_STORED_OUTLINES = development

In this example, outlines will be stored for queries within the DEVELOPMENT category. You must have the CREATE ANY OUTLINE system privilege in order to create an outline. Use the create outline command to create an outline for a query, as shown in the following listing:

create outline YTD_SALES
    for category DEVELOPMENT
     on
select Year_to_Date_Sales
  from SALES
 where region = 'SOUTH'
  and period = 1;

NOTE

If you do not specify a name for your outline, the outline will be given a system-generated name.

If you have set CREATE_STORED_OUTLINES to a category name in your initialization file, Oracle will create stored outlines for your queries; using the create outline command gives you more control over the outlines that are created.

NOTE

You can create outlines for DML commands and for create table  as select commands.

Once an outline has been created, you can alter it. For example, you may need to alter the outline to reflect significant changes in data volumes and distribution. You can use the rebuild clause of the alter outline command to regenerate the hints used during query execution, as shown next:

alter outline YTD_SALES rebuild;

You can also rename an outline via the rename clause of the alter outline command, as shown here:

alter outline YTD_SALES rename to YTD_SALES_REGION;

You can change the category of an outline via the change category clause, as shown in the following example:

alter outline YTD_SALES_REGION change category to DEFAULT;

To manage stored outlines, use the DBMS_OUTLN package, which gives you the following capabilities:

1. Drop outlines that have never been used
   
2. Drop outlines within a specific category
   
3. Move outlines from one category to another
   
4. Create outlines for specific statements
   
5. Update outlines to the current version’s signature

Each of these capabilities has a corresponding procedure within DBMS_OUTLN. To drop outlines that have never been used, execute the DROP_UNUSED procedure, as shown in the following example:

execute DBMS_OUTLN.DROP_UNUSED;

You can clear the “used” setting of an outline via the CLEAR_USED procedure. Pass the name of the outline as the input variable to CLEAR_USED:

execute DBMS_OUTLN.CLEAR_USED('YTD_SALES_REGION');

To drop all the outlines within a category, execute the DROP_BY_CAT procedure. The DROP_ BY_CAT procedure has the name of the category as its only input parameter. The following example drops all the outlines within the DEVELOPMENT category:

execute DBMS_OUTLN.DROP_BY_CAT('DEVELOPMENT');

To reassign outlines from an old category to a new category, use the UPDATE_BY_CAT procedure, as shown in the following example:

execute OUTLN_PKG.UPDATE_BY_CAT -
 (oldcat => 'DEVELOPMENT', -
  newcat => 'TEST');

To drop a specific outline, use the drop outline command.

If you have imported outlines generated in an earlier release, use the UPDATE_SIGNATURES procedure of DBMS_OUTLN to ensure the signatures are compatible with the current release’s computation algorithm.

Next: Editing Stored Outlines >>
 

More Oracle Articles
More By McGraw-Hill/Osborne

Comments On: Developing and Implementing Applications, continued

· This article is an excerpt from the book "Oracle Database 10g DBA Handbook,"...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter five of the book Oracle Database 10g DBA Handbook, written by Kevin Loney and Bob Bryla (McGraw-Hill/Osborne, 2005; ISBN: 0072231459). Check it out today at your favorite bookstore. Buy this book now.