Configuring the Query Optimizer - Troubleshooting Oracle Performance - page 291

The sort_area_retained_size initialization parameter is dynamic and can be changed at the instance and
session levels. In a 12.1 multitenant environment, it can also be set at the PDB level.
hash_area_size
If manual PGA management is enabled, the hash_area_size initialization parameter specifies (in bytes) the size of
the work areas used for hash joins. Be aware that this is the size of one work area and that a single session may allocate
several work areas. That means the total amount of the PGA used for the whole system depends on the number of
allocated work areas and not on the number of sessions. The default value is twice the value of the sort_area_size
initialization parameter. Again, suggesting specific values is difficult. In any case, for values up to 4MB, it should be set
to at least four to five times the value of the sort_area_size initialization parameter. If not, the query optimizer may
overestimate the cost of hash joins and, as a result, favor merge joins to them. Again, the work areas aren't always fully
allocated. In other words, the value specified by the hash_area_size initialization parameter is only a limit. Specifying
a value larger than is really required isn't necessarily a problem.
The hash_area_size initialization parameter is dynamic and can be changed at the instance and session levels.
In a 12.1 multitenant environment, it can't be set at the PDB level.
bitmap_merge_area_size
If manual PGA management is enabled, the bitmap_merge_area_size initialization parameter specifies (in bytes)
the size of the work areas used for merging bitmaps related to bitmap indexes. The default value is 1MB. Once again,
it's practically impossible to give general advice regarding suggested values. Clearly, larger values might improve
performances if a lot of bitmap indexes (for example, because of star transformation—see Chapter 14) are used.
The bitmap_merge_area_size initialization parameter is static and can't be changed at the system or session
level. A database instance bounce is therefore necessary to change it. In a 12.1 multitenant environment, it can't be set
at the PDB level.
On to Chapter 10
This chapter describes how to achieve a good configuration of the query optimizer by setting initialization
parameters. For this purpose, it's essential to understand not only how initialization parameters work but also how
object and system statistics influence the query optimizer.
Even with the best configuration in place, the query optimizer may fail to find an efficient execution plan. When
the performance of a SQL statement is questioned, the first thing to do is to review the execution plan. Chapter 10
discusses how to obtain execution plans and, more important, how to interpret them. I present some rules on how to
recognize inefficient execution plans as well.