15
Generating and Reviewing Recommendations

The topics in this chapter include:

Generating Tuning Recommendations

When you have finished collecting and editing your tuning data, Oracle Expert is ready to generate tuning recommendations. Select the Recommendations page of the tuning session window.

When you click the Generate button, Oracle Expert begins its analysis of the collected data. If you want specific details of the progress of the analysis while the analysis is running, click the Work in Progress button in the toolbar.

After an analysis is complete, you can examine the tuning recommendations on the Recommendations page. Note that Oracle Expert considers the interdependencies between recommendations while generating the list of recommendations for an analysis. If you use all the recommendations from an Oracle Expert analysis, you will be implementing the best overall Oracle Expert tuning recommendations for the categories selected, based on the data collected for the analysis.

Analyzing Data Efficiently

Before performing an analysis, Oracle Expert looks at the tuning categories you have selected for the tuning session. Then, during the analysis, Oracle Expert analyzes only the collected data that it requires to generate recommendations for the selected categories. This means, for example, if you select the instance tuning category for a tuning session, Oracle Expert excludes collected Schema or Workload class data from the analysis because Oracle Expert does not use schema or workload data when making SGA tuning recommendations.

Tunable Rule

The Tunable rule is another name for the "Enable or disable tuning for the current object and its children" rule, which is a Common rule available for some of the objects that you collect during a tuning session. An object's Tunable rule value (either Enabled or Disabled) determines whether or not the object is included in an Oracle Expert analysis. If the Tunable rule is not available for an object or you do not explicitly set a value for its Tunable rule, Oracle Expert uses the value of the rule from the next higher level of the Review page hierarchy where the rule is instantiated (or the default value of the rule, if none of the higher objects in the hierarchy have instantiated the rule). If you explicitly set the Tunable rule value for an object, Oracle Expert uses that value for the Tunable rule. The default value of the Tunable rule for all objects is Enabled.

A tuning session analysis excludes objects with a Tunable rule value of Disabled. Oracle Expert does not generate any tuning recommendations for these objects or their children.

Suppose you select the Access Methods tuning category for a tuning session because you want Oracle Expert to determine if a particular table is properly indexed. To perform Access Methods tuning, Oracle Expert requires that Schema class data be collected for the schema that includes the table for which you want recommendations (see Table 13-1). However, because you are interested in recommendations on only a single table, you can exclude the other tables in the schema from the analysis (by setting the Tunable rule's value to Disabled for the tables for which you do not want recommendations).

As another example, suppose you select the SQL Tuning category for a tuning session because you want Oracle Expert to tune the SQL statements for two applications. To perform SQL tuning, Oracle Expert requires that Workload class data be collected for these applications. If you have previously collected workload data for other applications besides the two you are interested in tuning, the other applications can be excluded from the analysis. You can exclude an application from an analysis by setting the value of the application's Tunable rule to Disabled.

You can verify and change the value of the Tunable rule for an object by following these steps:

Select the object on the Review page of the tuning session window.
Choose Edit=>Modify.
Choose the Rules tab.
Choose the Common tab.
Optionally, change the Tunable rule's value.

Deleting an Object

Another way to exclude an object from an analysis is to delete the object. Select the object on the Review page and choose Edit=>Delete.

Restrictions During an Analysis

While an analysis is in progress, you cannot:

change the tuning scope or session characteristic values
add, modify, or delete data on the Review page of the tuning session window
perform any collections
generate implementation files
delete database or tuning session

Canceling an Analysis

To cancel an analysis that is in progress, choose Cancel=>Cancel Process. After confirming that you want to cancel the analysis, Oracle Expert displays a dialog box with the Continue option, which returns you to the Recommendations page.

Invalid Objects Discovered During an Analysis

When Oracle Expert collects data for a tuning session, during the collection process it can determine that some objects are invalid. For more information about objects that are marked invalid after a collection, see "Collecting Invalid Data".

During an analysis, Oracle Expert performs additional validation and can discover other invalid objects. For example, Oracle Expert may find schema objects that refer to other schema objects that have not been collected. Oracle Expert considers a schema object to be invalid if it refers to another schema object for which data has not been collected.

The Oracle Expert Analysis report describes invalid objects discovered during the analysis. Oracle Expert includes invalid data when it analyzes tuning session data, but it does not generate any recommendations regarding invalid objects. For more information about generating an Analysis report, see "Generating an Analysis Report".

Invalidating an Analysis

A valid analysis is one that completes without errors. If a valid analysis exists and you attempt to perform a collection or modify or delete tuning session data, Oracle Expert warns you that the operation will invalidate the analysis. You will be given a choice as to whether or not to continue with the operation. If you continue, the analysis will be marked as invalid, which means:

when you attempt to generate an Analysis report, you receive a message that the analysis is invalid
when you click the View Detail button on the Recommendations page, you receive a message that the analysis is invalid
you will not be able to generate implementation files on the Scripts page from the invalid analysis

Reviewing Tuning Recommendations

When Oracle Expert finishes analyzing the collected data, you can select the Recommendations tab on the tuning session window. This displays the Recommendations page, where you can review the Oracle Expert tuning recommendations.

On the Recommendations page, recommendations of a particular type are grouped together under descriptively named folders similar to the folders on the Review page. Instance rules are shown in descending order of priority. To view the recommendations of a particular type, click the plus sign (+) for the folder.

For more information about why Oracle Expert made a particular recommendation, select the recommendation and either click the View Detail button or double-click the recommendation. Details of the rationale for the recommendation will then be displayed.

When reviewing the Instance Analysis recommendations, you may decline individual recommendations. To decline a recommendation, select the recommendation and click the right mouse button, then click Decline. The icon next to the selected recommendation will be marked with the international No symbol. The recommendation's state can be restored by clicking Accept again.

When you decline a recommendation, you must perform an analysis again because other recommendations may have a dependency on the declined recommendation.

When you decline a recommendation, Oracle Expert instantiates an override rule for the parameter that was declined, using the current parameter setting. For example, if the parallel_max_servers parameter is currently set to 5 and you decline the "Decrease parallel_max_servers to 0" instance recommendation, Oracle Expert instantiates the "Parallel_max_servers parameter override" rule with a value of 5 for the Parallel query parameters object.

To see the newly instantiated override rule, select the Parallel query parameters object on the Review page, choose Edit=>Modify, select the Rules tab, then the parallel_max_servers tab. As long as the "Parallel_max_servers parameter override" rule is instantiated at the Parallel query parameters object level, Oracle Expert will use the current value during instance evaluation for the tuning session. If you remove the instantiation of the "Parallel_max_servers parameter override" rule, Oracle Expert will re-evaluate the parameter.

If you change your mind about declining a recommendation, toggle its state by highlighting the recommendation and clicking the right mouse button, and then selecting Accept. This action also removes the instantiation of the affected override rule at the instance parameter level, which means Oracle Expert can make this recommendation again during the tuning session. Once you decline any recommendations and perform an analysis, you must manually remove the instantiation of the affected override rule (as described in the previous paragraph) before Oracle Expert will re-evaluate the parameter for the tuning session.

After you decline one or more recommendations from the current analysis, another analysis must be performed to re-evaluate all recommendations because other recommendations may have a dependency on the declined recommendations. If you attempt any of the following operations, a message is displayed advising you that declined recommendations exist:

generate an Analysis or Recommendation Summary report (by choosing the appropriate option from the Report menu)
display more detail about a recommendation on the Recommendations page (by double-clicking on the recommendation)
generate implementation files on the Scripts page (by clicking the Generate button)

You have the option of continuing with these operations after receiving the message. Note that if you generate implementation files after declining one or more recommendations, the implementation files will contain parameters and scripts to implement all of the Oracle Expert recommendations (not just the recommendations you chose to use).

If you decline one or more of the recommendations, you can return to the Analyze page of the tuning session window and begin another analysis of the collected data. Oracle Expert will take the recommendations you declined into account, then generate new recommendations (after considering the interdependencies between the recommendations). The new set of tuning recommendations does not include the recommendations you declined.

During an analysis, Oracle Expert may generate one or more recommendations that you cannot explicitly decline (these appear as grayed-out recommendations on the Recommendations page) and that you do not want to implement. If so, you can change rules at the appropriate object level to influence Oracle Expert so that these recommendations are not generated during future analysis sessions, or you can manually edit the recommendations in the implementation files.

For example, suppose Oracle Expert recommends that you create a new index with 16 segments on a particular table. You may agree with the recommendation to create the index, but you do not want to create any indexes with more than 6 segments. In this situation, you can go to the Review page, select the table for which Oracle Expert recommended the 16-segment index, and choose Edit=>Modify, which displays the Edit dialog box. Go to the Rules page, select the Index tab, and click the Advanced button. Find the "Maximum index key segments" rule, and change its value to 6. This will ensure that Oracle Expert recommends indexes with 6 or fewer segments for the table during future analysis sessions.

When Oracle Expert has generated recommendations you want to implement, select the Scripts tab, which displays the Implement page.

To review all of the recommendations for a tuning session and the rationale for those recommendations, generate an Analysis report by choosing Report=>Analysis.

15 Generating and Reviewing Recommendations