Because the analysis job is usually a batch operation performed after hours, you can tune it
by improving sort and full table scan performance at the session level. If you are performing the
analysis manually, increase the settings for the DB_FILE_MULTIBLOCK_READ_COUNT parameter
at the session level or the PGA_AGGREGATE_TARGET parameter at the system level to gathering
the statistics. If you are not using PGA_AGGREGATE_TARGET or do not want to modify a systemwide
setting, increase SORT_AREA_SIZE (which is modifiable at the session level) instead. The
result will be enhanced performance for the sorts and full table scans the analysis performs.
CAUTION
Increasing the DB_FILE_MULTIBLOCK_READ_COUNT in a RAC
database environment can cause performance problems when too
many blocks are shipped across the interconnect.
Hint Where Needed
In most cases, the cost-based optimizer (CBO) selects the most efficient execution path for queries.
However, you may have information about a better path. You may give Oracle a hint to influence
the join operations, the overall query goal, the specific indexes used, or the parallelism of the query.
Maximize the Throughput in the Environment
In an ideal environment, there is never a need to query information outside the buffer cache; all
of the data stays in memory all of the time. Unless you are working with a very small database,
however, this is not a realistic approach. In this section, you will see guidelines for maximizing
the throughput of the environment.
Pages:
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237