Anatomy of an optimization profile
An optimization profile can contain global optimization guidelines, and it can contain specific optimization guidelines that apply to individual DML statements in a package. Global optimization guidelines apply to all data manipulation language (DML) statements that are executed while the profile is in effect.
- You could write a global optimization guideline requesting that the optimizer refer to the materialized query tables (MQTs) Test.SumSales and Test.AvgSales whenever a statement is processed while the current optimization profile is active.
- You could write a statement-level optimization guideline requesting that the optimizer use the I_SUPPKEY index to access the SUPPLIERS table whenever the optimizer encounters the specified statement.
- The global optimization guidelines section can contain one OPTGUIDELINES element
- The statement profiles section that can contain any number of STMTPROFILE elements
<?xml version="1.0" encoding="UTF-8"?> <OPTPROFILE> <!-- Global optimization guidelines section. Optional but at most one. --> <OPTGUIDELINES> <MQT NAME="Test.AvgSales"/> <MQT NAME="Test.SumSales"/> </OPTGUIDELINES> <!-- Statement profile section. Zero or more. --> <STMTPROFILE ID="Guidelines for SAMP Q9"> <STMTKEY SCHEMA="SAMP"> <![CDATA[SELECT S.S_NAME, S.S_ADDRESS, S.S_PHONE, S.S_COMMENT FROM PARTS P, SUPPLIERS S, PARTSUPP PS WHERE P_PARTKEY = PS.PS_PARTKEY AND S.S_SUPPKEY = PS.PS_SUPPKEY AND P.P_SIZE = 39 AND P.P_TYPE = 'BRASS' AND S.S_NATION = 'MOROCCO' AND S.S_NATION IN ('MOROCCO', 'SPAIN') AND PS.PS_SUPPLYCOST = (SELECT MIN(PS1.PS_SUPPLYCOST) FROM PARTSUPP PS1, SUPPLIERS S1 WHERE P.P_PARTKEY = PS1.PS_PARTKEY AND S1.S_SUPPKEY = PS1.PS_SUPPKEY AND S1.S_NATION = S.S_NATION)]]> </STMTKEY> <OPTGUIDELINES> <IXSCAN TABID="Q1" INDEX="I_SUPPKEY"/> </OPTGUIDELINES> </STMTPROFILE> </OPTPROFILE>
The OPTPROFILE element
An optimization profile begins with the OPTPROFILE element. In the preceding example, this element consists of a VERSION attribute specifying that the optimization profile version is 9.1.
The global optimization guidelines section
Global optimization guidelines apply to all statements for which the optimization profile is in effect. The global optimization guidelines section is defined in the global OPTGUIDELINES element. In the preceding example, this section contains a single global optimization guideline telling the optimizer to consider the MQTs Test.AvgSales and Test.SumSales when processing any statements for which the optimization profile is in effect.
The statement profile section
A statement profile defines optimization guidelines that apply to a specific statement. There can be zero or more statement profiles in an optimization profile. The statement profile section is defined in the STMTPROFILE element. In the preceding example, this section contains guidelines for a specific statement for which the optimization profile is in effect.
- The statement key identifies the statement to which the statement-level optimization guidelines apply. In the example, the STMTKEY element contains the original statement text and other information that is needed to unambiguously identify the statement. Using the statement key, the optimizer matches a statement profile with the appropriate statement. This relationship enables you to provide optimization guidelines for a statement without having to modify the application.
- The statement-level optimization guidelines section of the statement profile is represented by the OPTGUIDELINES element. This section is made up of one or more access or join requests, which specify methods for accessing or joining tables in the statement. After a successful match with the statement key in a statement profile, the optimizer refers to the associated statement-level optimization guidelines when optimizing the statement. The example contains one access request, which specifies that the SUPPLIERS table referenced in the nested subselect use an index named I_SUPPKEY.
Elements common to both the global optimization guidelines and statement profile sections
- The REGISTRY element can set certain registry variables at either
the statement or global level. The REGISTRY element is nested in the
The REGISTRY element contains an OPTION element. The OPTION element has NAME and VALUE attributes which are used to set the value of the named registry variable.
If you specify a value for a registry variable at the global level, that value applies to all the statements in the connection on which the profile is applied. If you specify a value for a registry variable at the statement level, that value applies only to that statement within the STMTKEY. This value at the statement level takes precedence over the value at the global level.
- The STMTMATCH element sets the matching type used when the compiling
statement is matched to the statements in the optimization profile.
The STMTMATCH element has an EXACT attribute which can be set to either TRUE or FALSE. The default value of STMTMATCH EXACT is TRUE.
If STMTMATCH EXACT is set to TRUE, exact matching is applied. If STMTMATCH EXACT is set to FALSE, inexact matching is applied.