Database Reference
In-Depth Information
Text Normalization
One of the main advantages of a SQL profile is that although it applies to a specific SQL statement, no modification of
the SQL statement itself is needed in order to use it. In fact, the SQL profiles are stored in the data dictionary, and the
query optimizer selects them automatically. Figure
11-6
shows what the basic steps carried out during this selection
are. First, the SQL statement is normalized to make it not only case insensitive but also independent of the used blank
spaces. A signature is computed on the resulting SQL statement. Then, based on that signature, a lookup in the data
dictionary is performed. Whenever a SQL profile with the same signature is found, a check is performed to make sure
the SQL statement to be optimized and the SQL statement tied to the SQL profile are equivalent. This is necessary
because the signature is a hash value, and, therefore, there could be collisions. If the test is successful, the hints
associated to the SQL profile are included in the generation of the execution plan.
Figure 11-6.
Main steps carried out during the selection of a SQL profile
If the SQL statement contains literals that change, it's likely that the signature, which is a hash value, changes
as well. Because of this, the SQL profile may be useless because it's tied to a very specific SQL statement that will
possibly never be executed again. To avoid this problem, the database engine is able to remove literals during the
normalization phase. This is done by setting the
force_match
parameter to
TRUE
while accepting the SQL profile.
To investigate how text normalization works, you can use the
sqltext_to_signature
function in the
dbms_
sqltune
package. It takes two parameters,
sql_text
and
force_match
, as input. The former specifies the SQL
statement, and the latter specifies the kind of text normalization to be used. The following excerpt of the output
generated by the
profile_signature.sql
script shows the impact of the
force_match
parameter on the signature of
different, but similar, SQL statements:
