Troubleshooting Oracle Performance by Christian Antognini

Author:Christian Antognini
Language: eng
Format: epub, pdf
Publisher: Apress®
Published: 0101-01-01T00:00:00+00:00

How It Works

The following sections describe what stored outlines are and how to work with them. To manage stored outlines, except with Oracle9i, no graphical interface is provided in Enterprise Manager.

What Are Stored Outlines?

A stored outline is an object associated to a SQL statement and is designed to influence the query optimizer while it generates execution plans. More concretely, a stored outline is a set of hints or, more precisely, all the hints that are necessary to force the query optimizer to consistently generate a specific execution plan for a given SQL statement.

One of the advantages of a stored outline is that it applies to a specific SQL statement, but to use it, you don't need to modify the SQL statement. In fact, stored outlines are stored in the data dictionary, and the query optimizer selects them automatically. Figure 7-6 shows the basic steps carried out during this selection. First, the SQL statement is normalized by removing blank spaces and converting nonliteral strings to uppercase. The signature of the resulting SQL statement is computed. Then, based on that signature, a lookup in the data dictionary is performed. Whenever a stored outline with the same signature is found, a check is performed to make sure that the SQL statement to be optimized and the SQL statement tied to the stored outline are equivalent. This is necessary because the signature is a hash value, and consequently, there could be conflicts. If the test is successful, the hints making up the stored outline are included in the generation of the execution plan.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.