SQL Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 216

WHERE ps_partkey = p_partkey
AND ps_suppkey = s_suppkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
GROUP BY n_name,
r_name,
p_name,
s_name;
Plan hash value: 710495980
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost(%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 228M| 29G| | 7336K (1)| 24:27:18 |
| 1 | HASH GROUP BY | | 228M| 29G| 31G| 7336K (1)| 24:27:18 |
|* 2 | HASH JOIN | | 228M| 29G| | 351K (1)| 01:10:19 |
| 3 | TABLE ACCESS FULL | REGION | 10 | 270 | | 3 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 114M| 11G| | 350K (1)| 01:10:10 |
| 5 | TABLE ACCESS FULL | NATION | 50 | 1450 | | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 57M| 4458M| 83M| 350K (1)| 01:10:06 |
| 7 | TABLE ACCESS FULL | SUPPLIER| 2040K| 60M| | 10493 (1)| 00:02:06 |
|* 8 | HASH JOIN | | 28M| 1372M| 343M| 251K (1)| 00:50:19 |
| 9 | TABLE ACCESS FULL| PART | 7204K| 261M| | 37669 (1)| 00:07:33 |
| 10 | TABLE ACCESS FULL| PARTSUPP| 28M| 354M| | 162K (1)| 00:32:34 |
There are different types of hints that could be applied based on the type of optimization path to be taken. Oracle
supports over 60 different types of hints for the various types of operations. Multiple hints may also be used for the
same SQL statement, for example, if you want to use the NOPARALLEL and the FIRST_ROWS hint at the same time. The
statement block can only have a comment containing hints, and that comment must follow the SELECT , INSERT ,
UPDATE , and DELETE key words of the statement. Immediately following the comment, the “+” is used to tell the
optimizer that there is a hint inside the comment. For example
SELECT /*+ NOPARALLEL, FIRST_ROWS */ ...
If the syntactically correct hint is not specified, the optimizer will not report any errors but will generate an
execution path as if there was no hint specified.
Although Oracle does provide this method to change the optimizer behavior using hints, such a method should
be avoided as much as possible. The disadvantage of using hints is the extra code that needs to be maintained,
checked, and controlled every time there is change to the environment. Changes to the database and host
environment can make hints obsolete or even have negative consequences. Due to these reasons, hints should only be
used as a last resort to query optimization. Other more efficient methods should be used instead.
Hints help change the execution path for a given statement. What if all the statements in a session need
to be changed? In this case, the execution paths can be changed for the entire session by modifying the Oracle
initializing parameters.
Altering the Session
In an earlier section, we discussed that the optimizer mode is set at the system level when the database is started. The
optimizer mode is defaulted to ALL_ROWS and can be changed at the system level, which means the parameter value
will apply to the entire database (all user/sessions connecting to the database). The changes made in the init.ora
file or the spfile applies to the entire database; the value can be modified to apply for just the session using an ALTER
SESSION command before executing the statements in the session.
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;