database industry is clearly in the midst of massive server consolidation,
an economic imperative whereby the old-fashioned one database/one server
approach of the client-server days has been rendered obsolete. Today, single
servers with 32 and 64 CPU’s and hundreds of gigabytes of RAM can host
dozens of large Oracle databases.
2nd age of mainframe
computing came about to
facilitate easier DBA management, there remain the impressive benefits of
having a back of dozens of CPU’s to perform full scans very quickly.
When invoking Oracle parallel query, there are many perils and pitfalls:
To relieve these issues with parallel query, in Oracle 11g Release 2, the
following new parallel query parameters are included:
a close look at these important enhancements to Oracle parallel query in 11g
parallel_degree_policy parameter is related to the amount of table data
residing in the data buffer cache.
Using parallel_degree_policy allows Oracle to bypass direct path
reads when Oracle determines that lots of the table data blocks already
reside in the data buffer cache.
In traditional 32-bit systems (limited by on ly a few
gig of RAM for the SGA), direct path reads (which bypass the SGA were always
faster than reading a large table through the data buffer.
However, with the advent of 64-bit servers with dozens of gigabytes
for the db_cache_size, large
tables are often be fully cached, negating the need to always perform direct
path reads for parallel large-table full-table scans.
conducted some benchmark tests of
parallel_degree_policy and we see details on how
parallel_degree_policy evaluates the caching of large tables:
“If PARALLEL_DEGREE_POLICY is set to AUTO then Oracle
might perform buffered IO instead of direct path IO. . . The documentation
says that the optimizer decides whether or not to use direct path depending
on the size of the table and the buffer cache and the likelihood that some
data might be in memory.”
parameter only allows parallel query to be invoked against large tables or
indexes, those where the num_rows suggests that it will take more than nn
seconds to scan the table. The
parallel_min_time_threshold is 30 (seconds), but you can now adjust this
threshold according to your optimal definition of what constitutes a “large
table”. This parameter appears
to be related to the deprecated
parameter seta a limit on the maximum degree of parallelism.
The default is cpu_count*2.
parameter prohibits “parallel parallelism”, a case where parallel queries on
a RAC node are limited only to the local instance node.
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.