Database Reference
In-Depth Information
Another possible utilization of a SQL profile is when there are objects that have inaccurate or missing object
statistics. Of course, that shouldn't happen, but when it does and dynamic sampling can't be used to provide the
query optimizer with the needed information, a SQL profile could be used. The
profile_object_stats.sql
script
provides an example of this. The hints making up the SQL profile generated by that script are, among others, the
following. As the name of the hints suggest, the aim of each hint is to provide object statistics either for a table, an
index, or a column:
TABLE_STATS("CHRIS"."T2", scale, blocks=735 rows=5000)
INDEX_STATS("CHRIS"."T2", "T2_PK", scale, blocks=14 index_rows=5000)
COLUMN_STATS("CHRIS"."T2", "PAD", scale, length=1000)
COLUMN_STATS("CHRIS"."T2", "COL2", scale, length=3)
COLUMN_STATS("CHRIS"."T2", "COL1", scale, length=3)
COLUMN_STATS("CHRIS"."T2", "ID", scale, length=3 distinct=5000 nulls=0 min=2 max=10000)
The last area I describe in this section about undocumented features is the possibility to manually create
a SQL profile. In other words, instead of asking the SQL Tuning Advisor to do an analysis and then, if advised,
accept a SQL profile, you can build a SQL profile yourself. A manually produced SQL profile is created by calling
the
import_sql_profile
procedure in the
dbms_sqltune
package. The following call is an example based on the
profile_import.sql
script. The
sql_text
parameter specifies the SQL statement that the SQL profile is tied to, and
the
profile
parameter specifies the list of hints. All other parameters have the same meaning as the parameters of
the
accept_sql_profile
procedure previously described:
dbms_sqltune.import_sql_profile(
name => 'import_sql_profile',
description => 'SQL profile created manually',
category => 'TEST',
sql_text => 'SELECT * FROM t ORDER BY id',
profile => sqlprof_attr('first_rows(42)','optimizer_features_enable(default)'),
replace => FALSE,
force_match => FALSE
);
■
even though the
import_sql_profile
procedure in the
dbms_sqltune
package isn't officially documented,
the method used to create the SQL profile is the same as the one used by the database engine when you accept advice
provided by the SQL tuning advisor. hence, i see no problem in using the
import_sql_profile
procedure. in addition,
the
coe_xfr_sql_profile.sql
script distributed through the Oracle Support note
SQLT (SQLTXPLAIN) - Tool that helps
to diagnose a SQL statement performing poorly or one that produces wrong results
(215187.1) uses this same procedure
to create a SQL profile. By the way, you can execute the
coe_xfr_sql_profile.sql
script to create a SQL profile for a
cursor cached in the library cache or stored in aWr.
Note
When to Use It
You should consider using SQL profiles whenever you're optimizing a specific SQL statement and you aren't able to
change it in the application (for example, when adding hints isn't an option). Remember that the aim of SQL profiles
is to provide the query optimizer with additional information about the data to be processed and about the execution
environment. So, don't use this technique if you need to force a specific execution plan for a specific SQL statement.
