Using Oracle parallel query to play the power of multiple CPUs __oracle

using Oracle parallel queries to play the power of multiple CPUs2006-07-05 07:00 Author: Source: BLOG Editor: Ark the installation of more CPUs in a separate server is a current trend. With symmetric multi-processor (SMP), it is not surprising that an Oracle server has 8, 16 or 32 CPUs and several gigabytes of RAM.

Oracle has been following the pace of hardware development, providing a number of features that are geared toward multiple CPUs. Starting with Oracle8i, Oracle implements parallelism in each database function, including SQL access (full table retrieval), parallel data manipulation, and parallel recovery. The challenge for Oracle's Professional Edition is to configure as many CPUs as possible for the user's database.

One of the best ways to achieve parallelism in an Oracle environment is to use Oracle parallel queries (OPQ). I'll discuss how OPQ works and how to use it to improve response times for large full table retrievals and invoke parallel transaction rollbacks, and so on.

　　 using OPQ

OPQ can greatly improve response time when a legitimate, large, full table search occurs in Oracle. By opq,oracle the table into Figure AThe logical block as shown.

Figure A



　　 a table divided by OPQ

Once the table is divided into chunks, Oracle enables parallel subqueries (sometimes called chore processes), and each subquery reads a piece of a large table. After all the subqueries have been completed, Oracle passes the results to the parallel query scheduler, which arranges the data, sorts if necessary, and passes the results to the end user. The OPQ has unlimited scalability, so it takes less than 1 seconds for the full table to retrieve the response time before it took a few minutes.

OPQ relies heavily on the number of processors, and running in parallel can greatly enhance the performance of the full table retrieval, provided that the N-1 parallel process (the number of CPUs on the n=oracle server) is used.

It is important to note that oracle9i can automatically detect the external environment, including the number of CPUs on the server. At installation time, Oracle9i checks the number of CPUs on the server, sets a parameter named Cpu_count, and uses Cpu_count as the default initialization input parameter. These initialization parameters affect Oracle's handling of internal queries.

The following are some of the parameters that Orale set on Cpu_count at installation time:
Fast_start_parallel_rollback
Parallel_max_servers
Log_buffer
Db_block_lru_latches
Parameters

Let's take a closer look at how the number of CPUs affects these parameters.

Parameter Fast_start_parallel_rollback

One exciting thing about Oracle parallelism is the ability to invoke parallel rollback in the case of a system crash. Oracle can automatically detect unfinished transactions and roll back to the starting state when an Oracle database crashes infrequently. This is called parallel hot start, and Oracle uses the Cpu_count fast_start_parallel_rollback parameter to determine the degree of disposition of unfinished transactions.

Parallel Data Manipulation Language (DML) recovery can greatly speed up the restart of an Oracle database after it crashes. The default value for this parameter is twice times the number of system CPUs, but some DBAs think the value should be set to four times times cpu_count.

Parameter Parallel_max_servers_parameter

One notable enhancement of Oracle is the degree to which OPQ parallelism is automatically determined. Because Oracle knows the number of CPUs in the server, it automatically assigns the appropriate number of child processes to increase the response time for parallel queries. Of course, there are other external factors, such as table partitioning and the layout of the disk input/output subsystem, but setting the parallel_max_servers parameters according to Cpu_count will give Oracle a reasonable basis for choosing the degree of parallelism.

Because Oracle's parallel operations rely heavily on the number of CPUs on the server, Parallel_max_servers is set to the number of CPUs on the server. If multiple instances are running on a single server, the default value is too large, resulting in excessive paging and heavy CPU load. The degree of parallelism also depends on the number of partitions in the target table, so parallel_max_servers should be set to large enough to allow Oracle to select the optimal number of parallel subqueries for each query.

Parameter Log_buffer

The parameter Log_buffer defines the amount of reserved ram for instantly writing redo log information, which is affected by Cpu_count. Oracle recommends Log_buffer Max for cpu_count times 500KB or 128KB. The number of CPUs is important for Log_buffer, because Oracle generates a multi-log write (LGWR) process to asynchronously release redo information.

Log_buffer is one of the most misunderstood RAM parameters in Oracle and typically has several configuration errors:
Log_buffer are set too high (for example, greater than 1MB), which can cause performance problems, because large-capacity results make writing synchronized (for example, log synchronization wait events are very high).
Log_buffer is not a multiple of db_block_size. In the oracle9i, the Log_buffer should be a multiple of 2048 bytes.
Parameter db_block_lru_latches

The number of LRU locks is used internally within an Oracle database to manage database buffering, which relies heavily on the number of CPUs on the server.

Many smart oracle9i DBAs use multiple-flush data buffering (for example, db_32k_cache_size), and they recommend resetting this undeclared argument to the default maximum value. The db_block_lru_latches parameter is used much in oracle8i, but it becomes an undeclared argument in Oracle9i, because Oracle now sets a reasonable default value based on the number of CPUs owned by the database.

Db_block_lru_latches default is set to half of the Cpu_count on the server (for example, there is only one Oracle database on the server). Oracle recommends Db_block_lru_latches never surpass cpu_count twice times or three times times, or Db_block_buffers One-fiftieth.

This method of calculation has a problem if you use a multiple buffer pool because you cannot control the number of locks allocated to each data buffer pool. If the Db_writers parameter is greater than 1, the default value may appear too small.

　　 strengthen the server

Oracle databases are always improving performance, and the ability to detect cpu_count and basic parameter settings based on an external server environment is an important step for Oracle software.

As more Oracle systems move to SMP, these parameters become more important when customers are taking enhancements and moving a large number of databases to a huge server with 32 or 64 CPUs. Set Parallel_max_servers:
Recommended Value:2 * CPUs * number_of_concurrent_users
If you set this parameter too low, some queries could not have a parallel execution process available to them during query P Rocessing. If you are set it too high, memory resource shortages may occur during peak, periods can which degrade.

Parallel_min_servers:
Recommended Value:parallel_max_servers
The system parameter Parallel_min_servers allows you-specify the number of processes to is started and reserved for par Allel operations at startup in a single instance.

For example, if your have determined that maximum number of concurrent parallel server processes that your machine can Manage is-should set parallel_max_servers to 100. Next determine how many parallel server processes the average operation-needs, and how many operations-are to is ex ecuted concurrently. For this example, assume to have two concurrent operations with as the average degree of parallelism. At no given time, there could is parallel server processes busy on a instance. You are should therefore set the parameter parallel_min_servers to 80.

Consider decreasing parallel_min_servers if fewer PARALLEL server processes than this value are typically busy at any give N Time. Idle parallel server processes constitute unnecessary system overhead.

Consider increasing parallel_min_servers if more PARALLEL server processes than this value are typically active, and the Servers started "Statistic of v$pq_sysstat is continuously growing.