Use Oracle Parallel queries to leverage the power of multiple CPUs
Use Oracle Parallel queries to leverage the power of multiple CPUs
The ORACLE tutorial is: use Oracle Parallel query to exert the power of multiple CPUs. Parameters
Let's further look at how the number of CPUs affects these parameters.
Parameter fast_start_parallel_rollback
An exciting aspect of Oracle's parallel mechanism is the ability to call parallel rollback when the system crashes. When the Oracle database crashes rarely, Oracle can automatically detect unfinished transactions and roll back to the starting state. This is called Parallel hot start, and Oracle uses the fast_start_parallel_rollback parameter based on cpu_count to determine the level of integrity of unfinished transactions.
Parallel data manipulation language (DML) recovery can greatly speed up the restart after the Oracle database crashes. The default value of this parameter is twice the number of CPU resources in the system, but some DBAs believe that this value should be set to four times the number of cpu_count.
Parameter parallel_max_servers_parameter
A significant enhancement of Oracle is that it automatically determines the degree of OPQ parallelism. Since Oracle knows the number of CPUs on the server, it will automatically allocate the appropriate number of sub-processes to improve the response time of parallel queries. Of course, there will be other external factors, such as the table division and the layout of the disk input/output subsystem, however, setting the parallel_max_servers parameter based on cpu_count gives Oracle a reasonable basis to select the degree of parallelism.
Since parallel Oracle operations are heavily dependent on the number of CPUs on the server, parallel_max_servers is set to the number of CPUs on the server. If you run multiple instances on one server, the default value is too large, which will lead to excessive page switching and severe CPU burden. The degree of parallelism also depends on the number of partitions in the target table. Therefore, 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 log_buffer parameter defines the number of reserved RAM for immediate Writing of redo log information. This parameter is affected by cpu_count. The maximum log_buffer size recommended by Oracle is cpu_count multiplied by 500KB or 128KB. The number of CPUs is very important for log_buffer, because Oracle will generate a multi-log write (LGWR) process to asynchronously release redo information.
Log_buffer is one of the most misunderstood RAM parameters in Oracle. There are usually the following configuration errors:
Log_buffer is set too high (for example, greater than 1 MB). This may cause performance problems because large volumes of results will cause synchronization of writes (for example, log synchronization wait events are very high ).
The current trend is that log_buffer installs more CPUs on a single server. When using Symmetric Multi-processing server (SMP), it is not surprising that an Oracle Server has eight, 16 or 32 CPUs and several gibit ram sga.
Oracle keeps up with the pace of hardware development and provides many features for multiple CPUs. From Oracle8i, Oracle implements parallelism in each database function, including SQL access (full table search), parallel data operations, and parallel recovery. The challenge for Oracle Professional Edition is to configure as many CPUs as possible for your database.
One of the best ways to achieve parallelism in the Oracle environment is to use Oracle Parallel query (OPQ ). I will discuss how OPQ works and how it can be used to improve the response time of a large full table search and call parallel transaction rollback.
Use OPQ
When a legal and large-scale full table search is performed in Oracle, OPQ can greatly increase the response time. Using OPQ, Oracle divides tables
Figure
AThe Logical Block shown in.
Figure
Tables divided by OPQ
Once a table is divided into blocks, Oracle enables parallel subqueries (sometimes called a chores process). Each subquery simultaneously reads one part of a large table. After all the subqueries are completed, Oracle sends the results to the parallel query scheduler, which reschedules the data. If necessary, the results are sorted and passed to the end user. OPQ has unlimited scalability. Therefore, it takes a few minutes to retrieve the entire table, but the response time is less than 1 second.
OPQ relies heavily on the number of processors. The parallel operation can greatly improve the full table search performance, the premise is that a parallel process (N = the number of CPUs on the Oracle server) is used ).
Note that Oracle9i can automatically detect external environments, including the number of CPUs on servers. During installation, 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 processing of internal queries.
The following are some parameters set by Orale Based on cpu_count during installation:
- Fast_start_parallel_rollback
- Parallel_max_servers
- Log_buffer
- Db_block_lru_latches
[NextPage]
It is not a multiple of db_block_size. In Oracle9i, log_buffer should be a multiple of 2048 bytes.
Parameter db_block_lru_latches
The number of LRU locks is used inside the Oracle database to manage database buffering, which is heavily dependent on the number of CPUs on the server.
Many smart DBAs of Oracle9i use multi-buffer data (such as db_32k_cache_size). They recommend that you reset this unspecified parameter to the default maximum value. The db_block_lru_latches parameter is used a lot in Oracle8i, but it becomes an undisclosed parameter in Oracle9i, because Oracle now sets a reasonable default value based on the number of CPU resources in the database.
Db_block_lru_latches is set to half of cpu_count on the server by default (for example, there is only one Oracle database on the server ). Db_block_lru_latches recommended by Oracle should never exceed twice or three times of cpu_count, or 1/50 of db_block_buffers.
If multiple buffer pools are used, this calculation method has a problem because the number of locks allocated to each data buffer pool cannot be controlled. If the db_writers parameter is greater than 1, the default value may appear too small.
Strengthen servers
Oracle databases are always improving performance. The ability to detect cpu_count and basic parameter settings based on the external server environment is an important enhancement for Oracle software.
As more Oracle Systems are migrated to SMP, when the customer wants to take enhancement measures and move a large number of databases to a huge server with 32 or 64 CPUs, these parameters become more and more important.
Previous Page
