Oracle parallel Query

81 53,5297 P_Base_Day_ I _NewTaredUser 17:28:56 18:24:21 55 insert success base
82 53,5300 P_BASE_DAY_ I _NEWTAREDUSER_test 17:29:31 17:54:21 24 insert success base

 

This is two identical processes that access 10 million data for inner join statistics. The previous one took 55 minutes and the last one took 24 minutes.

Insert/* + parallel (t_newtraed_test, 4) */into t_newtraed_test
Select B. addtime, 0, B. username, sysdate, 0, c. lotid, 0, c. playid, 0, sysdate
From
(
Select username, min (addtime) as addtime
From
(
Select/* + PARALLEL (x, 5) PARALLEL (z, 5) */x. F_username as username, x. f_addtime as addtime
From T_Gather_ProUser x
Inner join t_gather_project z ON x. f_projectid = z. f_id and x. f_lotteryid = z. f_lotteryid
....

 

The table can be

Alter table ba. P_ADMIN PARALLEL (DEGREE Default INSTANCES Default );

This is determined by ORACLE.

Often reported ORA-12801: parallel query server P029 error signal
ORA-00018: exceeds the maximum number of sessions

It can be seen that it has opened too many sessions.

 

 

 

The following documents are for reference:

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 and reschedule the data. If necessary, Oracle sorts the results and sends them to the end user. OPQ has unlimited scalability. Therefore, it takes a few minutes to retrieve the entire table. The current 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

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. Because of the number of CPUs in the Oracle CLEAR 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 ). Log_buffer 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 intelligent DBAs of Oracle9i use multi-buffer data (for example, 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 currently 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.