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.
<?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>
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.
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.
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 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 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.