Execution Plans - Troubleshooting Oracle Performance - page 307

For each operation in the execution plan, you see which query block it's related to and, optionally, which object
it's executed on. This information is essential when the SQL statement references the same table several times.
Query block names are discussed in more detail along with hints in Chapter 11.
The fourth section shows the set of hints, called outline , that should be sufficient to reproduce that particular
execution plan. Be aware that the outline doesn't always contain all the necessary hints. Chapter 11 explains why
some outlines aren't sufficient to reproduce an execution plan and also describes how it's possible to store and take
advantage of such an outline with, for example, stored outlines and SQL plan baselines:
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."ID"))
FULL(@"SEL$1" "T1"@"SEL$1")
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
USE_HASH(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
The following section is only displayed when the query optimizer takes advantage of bind variable peeking. Each
bind variable's data type and value are provided:
Peeked Binds (identified by position):
--------------------------------------
1 - :T1_ID (NUMBER): 6
2 - :T2_ID_MIN (NUMBER): 6
3 - :T2_ID_MAX (NUMBER): 19
The next section shows which predicates are applied. For each, it's shown where (line) and how ( access , filter
or storage ) they're applied:
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:T2_ID_MIN<=:T2_ID_MAX)
2 - access("T1"."N"="T2"."N")
4 - access("T2"."ID">=:T2_ID_MIN AND "T2"."ID"<=:T2_ID_MAX)
5 - filter("T1"."ID">:T1_ID)
Although an access predicate is used to locate rows by taking advantage of an efficient access structure (for
example, a hash table in memory, like for operation 2, or an index, like for operation 4), a filter predicate is applied
only after the rows have already been extracted from the structure storing them. In addition, when an Exadata storage
server is used, a storage predicate points out that a specific filter is offloaded to the underlying storage subsystem.