An optimization profile can contain global guidelines, which apply to all data manipulation language (DML) statements that are executed while the profile is in effect, and it can contain specific guidelines that apply to individual DML statements in a package.
An optimization profile contains two major sections where you can specify these two types of guidelines: a global optimization guidelines section can contain one OPTGUIDELINES element, and a statement profile section can contain any number of STMTPROFILE elements. An optimization profile must also contain an OPTPROFILE element, which includes metadata and processing directives.
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.1.0.0">
<!--
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 represented by the global OPTGUIDELINES element. In the preceding example, this section contains a single global optimization guideline specifying that the MQTs Test.AvgSales and Test.SumSales should be considered 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 represented by the STMTPROFILE element. In the preceding example, this section contains guidelines for a specific statement for which the optimization profile is in effect.