Database Reference
In-Depth Information
SQL_TEXT SIGNATURE
--------------------------------------------------- --------------------
SELECT * FROM dual WHERE dummy = 'X' 7181225531830258335
select * from dual where dummy='X' 7181225531830258335
SELECT * FROM dual WHERE dummy = 'x' 18443846411346672783
SELECT * FROM dual WHERE dummy = 'Y' 909903071561515954
SELECT * FROM dual WHERE dummy = 'X' OR dummy = :b1 14508885911807130242
SELECT * FROM dual WHERE dummy = 'Y' OR dummy = :b1 816238779370039768
•
force_match
set to
TRUE
: Blank spaces and case- and literal-insensitive. Nevertheless, the
substitution of literals isn't performed if a bind variable is present in the SQL statement.
SQL_TEXT SIGNATURE
--------------------------------------------------- --------------------
SELECT * FROM dual WHERE dummy = 'X' 10668153635715970930
select * from dual where dummy='X' 10668153635715970930
SELECT * FROM dual WHERE dummy = 'x' 10668153635715970930
SELECT * FROM dual WHERE dummy = 'Y' 10668153635715970930
SELECT * FROM dual WHERE dummy = 'X' OR dummy = :b1 14508885911807130242
SELECT * FROM dual WHERE dummy = 'Y' OR dummy = :b1 816238779370039768
Be aware that it's possible to have two SQL profiles for the same SQL statement: one with
force_match
set to
FALSE
, and the other with
force_match
set to
TRUE
. If two SQL profiles exist, the one with
force_match
set to
FALSE
takes precedence over the one with
force_match
set to
TRUE
. This is sound because the one with
force_match
set to
FALSE
is more specific than the other. That means that you might have one SQL profile to cover most of the literals,
and another for literals requiring particular handling (for example, when a restriction based on a literal is applied to a
column containing skewed data).
Activating SQL Profiles
The activation of SQL profiles is controlled at the system and session levels by the
sqltune_category
initialization
parameter. Its default value is
DEFAULT
. This is the same default value as for the
category
parameter of the
accept_
sql_profile
procedure of the
dbms_sqltune
package. As a result, if no category is specified when accepting the SQL
profile, by default the SQL profile is activated. It takes as a value the name of a category specified while accepting the
SQL profile. For example, the following SQL statement activates the SQL profiles belonging to the
test
category at the
session level:
ALTER SESSION SET sqltune_category = test
The initialization parameter supports a single category. Obviously, this implies that a session is able to activate
only a single category at a given time.
In order to know whether a SQL profile is used by the query optimizer, you can take advantage of the functions
available in the
dbms_xplan
package. As shown in the following example, the
Note
section of their output explicitly
provides the needed information:
SQL> EXPLAIN PLAN FOR SELECT * FROM t ORDER BY id;
SQL> SELECT * FROM table(dbms_xplan.display);
