Database Reference
In-Depth Information
Undocumented Features
How does a SQL profile influence the query optimizer? Oracle provides no real answer to this question in its
documentation. It's my belief that the best way to use a feature efficiently is to know how it works. So, let's take a look
under the hood. Simply put, a SQL profile stores a set of hints representing the adjustments to be performed by the
query optimizer. Some of these hints are documented and used in other contexts. Others are undocumented and
commonly used for SQL profiles only. In other words, they have probably been implemented for this purpose. All of
them are regular hints and, therefore, can be directly added to a SQL statement as well.
Before discussing how to query the list of hints associated to a SQL profile, let's introduce an example based on
the
profile_all_rows.sql
script. Its purpose is to show you that, with a SQL profile, it's possible to instruct the query
optimizer to change the optimizer mode. In this specific case, changing the optimizer mode is required because a
query contains the
rule
hint that forces the query optimizer to work in rule-based mode. The query and its execution
plan are the following:
SQL> SELECT /*+ rule */ * FROM t ORDER BY id;
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
| 2 | INDEX FULL SCAN | T_PK |
--------------------------------------------
Note
-----
-
rule based optimizer used
(consider using cbo)
After letting the SQL Tuning Advisor work on the query and accepting the SQL profile it advises, the execution
plan changes as follows. As pointed out in the
Note
section, a SQL profile is used during the generation of the
execution plan:
SQL> SELECT /*+ rule */ * FROM t ORDER BY id;
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 1015K| | 277 (1)| 00:00:04 |
| 1 | SORT ORDER BY | | 10000 | 1015K| 1120K| 277 (1)| 00:00:04 |
| 2 | TABLE ACCESS FULL| T | 10000 | 1015K| | 38 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Note
-----
-
SQL profile "all_rows" used for this statement
