Oracle Parallel Query Parallel query____oracle

Oracle Parallel queries parallel query

Bayi 53,5297 p_base_day_i_newtareduser 2009-06-25 17:28:56 2009-06-25 18:24:21 Insert Success Base
53,5300 p_base_day_i_newtareduser_test 2009-06-25 17:29:31 2009-06-25 17:54:21 Insert Success BASE

This is two of the same process accessing 60 million of data for inner join stats before the last one took 55 minutes after a spent 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,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

ALTER TABLE BA. P_admin PARALLEL (DEGREE default instances default);

It's up to Oracle to decide.

Can be reported ORA-12801: Parallel query server P029 send out the wrong signal
ORA-00018: Maximum number of sessions exceeded

This shows that it drives too many sessions.

The following information is available for your reference

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, who arranges the data, sorts if necessary, and passes the results to the end user. OPQ has unlimited scalability, so it takes less than 1 seconds for the full table to retrieve the current 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, assuming 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, although 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 of the number of CPUs in the Oracle clear server, he automatically assigns the appropriate number of child processes to increase the response time for parallel queries. Of course, there will be other external factors, such as table partitioning and disk input/output subsystem layout, but according to Cpu_count to set the parallel_max_servers parameters will give Oracle a reasonable basis to choose 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 there are usually several settings 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.

A lot of smart oracle9i DBAs use multiple-flush data buffers (such as db_32k_cache_size), and they recommend resetting this undeclared argument to the default maximum value. The db_block_lru_latches parameter is used very much in oracle8i, but it becomes an undeclared argument in Oracle9i, because Oracle currently 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.