Installation guide

19
Disable Oracle’s Automatic Memory Management (AMM)
While Oracle’s automatic memory management (AMM) promises to improve memory efficiency, AMM is
incompatible with huge pages. The overhead of regular size page tables far exceeds any possible gains from AMM.
Overall, AMM ends up wasting memory, and it should not be used for BI Data Warehousing workloads.
If you have configured your system for huge pages as described above, then AMM will already be disabled.
Oracle parallel query management in 11gR2
With 11gR2 Oracle introduced additional parallel query management features to improve BI/DW query
performance. Parallel execution enables the application of multiple CPU and I/O resources to the execution of a
single database operation. It reduces response time for data-intensive operations on large databases typically
associated with a decision support system (DSS) and data warehouses. Parallel execution is designed to exploit large
amounts of available hardware resources. Oracle parallel execution will benefit the DL980 and P2000 reference
configuration for the following reasons:
 Large number of physical and logical cores
 Sufficient I/O bandwidth
 Sufficient memory to support additional memory-intensive processes, such as sorting, hashing, and I/O buffers
The first improved option is parallel query queuing. In past versions of Oracle database software a large query that
needed to execute in parallel could run into a performance issue if all the parallel slaves on the system were already
consumed by other queries. This large query would then either run serially as a single process (which can take a very
long time to complete) or if the Oracle parameter parallel_min_percent was set the large query would almost always
fail. For more detailed information on parallel queries and this issue please see Oracle white paper “Oracle
Database Parallel Execution Fundamentals October 2010” and “Parallel Execution and Workload Management for
an Operational Data Warehouse”.
The second improved option is that Oracle can now use the in memory buffer cache for parallel execution, where in
the past parallel slaves would use direct reads of data from disk. Traditional parallel processing by-passed the
database buffer cache for most operations, reading data directly from disk (via direct path I/O) into the parallel
query server’s private working space (PGA). This meant that parallel processing rarely took advantage of the
available memory other than for its private processing. This feature in Oracle 11gR2 is referred to as “In Memory
Parallel Execution” where much larger systems like the DL980 with a large resource footprint or even a large DL980
cluster has a lot more data that can be cached for query use to improve performance. By having parallel query
servers access objects via the database buffer cache they can scan data significantly faster than they can on disk.
To enable both parallel queuing and parallel slave caching the Oracle init.ora parameter parallel_degree_polic y
needs to be set to auto, by default this parameter is set to manual to disable both of these functions. To enable just the
parallel queuing option keep parallel_degree_policy set to manual, but set _parallel_statement_queuing to TRUE.
These values can be applied to an individual session level as well as globally for the entire environment. Also note
that for small queries, parallel processing can be disabled by setting the parallel_min_time_threshold to a certain
execution time in seconds. This is supposed to prevent small queries from consuming valuable parallel slave processes
on the system and simply allows the small query to execute in serial, thus leaving the parallel processes available for
larger queries. The Oracle cost based optimizer will predict the length of time in which the query will run and then
make the decision on whether to run the particular query as serial or parallel. During our tests setting this time
threshold parameter proved to be very unreliable and most small queries did execute in parallel rather than serial,
even with changing the parallel_min_time_threshold settings. The only consistent and reliable way to execute these
small queries was to either manually set the session where the query is to be executed to serial (parallel limit of 1) or
to put a hint in the SQL statement { /*+PARALLEL(1) */}.
You can also set the number of parallel processes needed for large queries at the same time by providing hints in
these SQL statements. Typical numbers of parallel slaves tested were 8, 16 or 32 per process for the larger complex
queries. Next the parameter of parallel_min_percent = 25 was set, this means that at least 25% of the parallel slaves
need to be present and available for the query to execute. Setting of this parameter in earlier database versions
would have caused the query to fail if the resources were not available at the time the query was to be executed.
However with Oracle 11gR2 parallel query execution the query will now wait in the queue until enough parallel
slaves are available for it to execute. The query wait queue is a simple FIFO queue. Individual queries don’t need to
be tuned each time. Instead of the query failing or running in serial mode, Oracle will now put the query into a queue
to wait for enough slaves before executing. The DOP management is running the same as before, but now you have
the option of turning on queuing.