Oracle Parallel parameter setup ____oracle

Oracle Parallel parallel execution Operations

2009-08-22 17:16:08| Category: System Management Technology | reporting | font size subscription

Introduction: The Oracle Parallel feature has not been a concern for the past. A few days ago a brother encountered a problem, just let me think this thing still have a lot of places to pay attention to, it is necessary to carefully familiar with. In fact, the problems encountered are not complicated:
A statement similar to the following: INSERT INTO XXXX select/*+parallel (a)/* from XXX A; the amount of data around 75G, the brother from the morning to the afternoon has not run out, came to ask me how, said that ordinary 2hrs can run after the things run for hours and still sprinkle movement. Check the system performance is also more normal, Cpu,io are not busy, the average read speed in 80m/s about (reluctantly), but the average write speed of only 10M. Wait for the event inside a large number of ' PX Deq credit:send blkd ', which can be seen in parallel to the problem, and finally learned that there is a problem with the parallel usage, 20 minutes after the modification to complete the operation. The right approach should be:
Alter session enable DML parallel;

Insert/*+parallel (xxxx,4)/into XXXX select/*+parallel (a) * * * from XXX A;

Because Oracle does not open PDML by default, the DML statement must be manually enabled. It is also important to say that parallelism is not an extensible feature, and that it is very prudent to use parallelism in an OLTP environment only if the data warehouse or a few of the tools used as a DBA facilitate the full utilization of resources in the context of bulk data operations. In fact, there are a number of limitations to PDML, such as not supporting triggers, referential constraints, advanced replication, and distributed transactions, as well as additional space usage, PDDL. The parallel Excution can refer to the official documentation and is also brilliantly described in Thomas Kyte's new book, Expert Oracle Database architecture.

..............................................................................................................................
..............................................................................................................................

I executed one of the sessions
Sql> CREATE table Test3 parallel 4 AS SELECT * from Test1;

Table has been created.

Sql> SELECT * from V$mystat where rownum=1;

SID statistic# VALUE
---------- ---------- ----------
151 0 1

Sql>

Then immediately in another session by the previous execution does not end, look below, so that there are 4 parallel processes in the processing of
Sql> select * from V$px_session;

Saddr SID serial# qcsid qcserial# qcinst_id server_group server_set server# DEGREE req_degree
-------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ----------
6d31e434 131 16 151 107 1 1 1 1 4 4
6D32421C 136 11 151 107 1 1 1 2 4 4
6D3267AC 138 18 151 107 1 1 1 3 4 4
6D31F6FC 132 11 151 107 1 1 1 4 4 4
6D335BD4 151 107 151

Sql> SELECT * from V$mystat where rownum=1;

SID statistic# VALUE
---------- ---------- ----------
137 0 1

Sql>

I enlarged the post

Sql>/

Saddr SID serial# qcsid qcserial# qcinst_id server_group server_set server# DEGREE req_degree
-------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ----------
6D31864C 126 10 151 107 1 1 1 1 7 10
6D31F6FC 132 17 151 107 1 1 1 2 7 10
6D32421C 136 15 151 107 1 1 1 3 7 10
6D3267AC 138 22 151 107 1 1 1 4 7 10
6D322F54 135 11 151 107 1 1 1 5 7 10
6d31e434 131 18 151 107 1 1 1 6 7 10
6d327a74 139 5 151 107 1 1 1 7 7 10
6D335BD4 151 107 151

8 rows have been selected.

Sql>

Strange, how to look at only 7, I was there but written
Sql> CREATE TABLE Test4 parallel as SELECT * from Test1;

Table has been created.

How about 3 less.

But I actually only have one CPU machine, which indicates what the problem is.

BTW

Sql> Show Parameter Parallel_max

NAME TYPE VALUE
------------------------------------ ---------------------- -------------------
Parallel_max_servers Integer 20
Sql>

....................................................................................................................................
....................................................................................................................................
How many parallel server to open also depends on the load of the system at that time, parallel is very consuming system resources,

This degree of parallelism is related to your initialization parameters. Cpu_count, PARALLEL_THREADS_PER_CPU and so on are all related. If you do not explicitly specify the degree of parallelism when you build the table, Oracle automatically sets the degree of parallelism as needed.

Using Oracle parallel queries to play the power of multiple CPUs
Installing more CPUs on 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. Divide the table into logical blocks as shown in Figure A by opq,oracle.

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.

A description of the parallel parameter for 10G
Parallel_adaptive_multi_user Boolean TRUE
Description: Enables or disables an adaptive algorithm designed to improve the performance of a multiuser environment that uses parallel execution. By automatically reducing the degree of parallelism of the request by system load,
Implement this feature when you start a query. When parallel_automatic_tuning = TRUE, it works best.
Value Range: TRUE | FALSE
Default value: True if parallel_automatic_tuning = true; otherwise FALSE

Parallel_automatic_tuning Boolean TRUE
Note: If set to TRUE, Oracle will determine the default value for parameters that control parallel execution. In addition to setting this parameter,
You must also set parallelism for the tables in the system.
Value Range: TRUE | FALSE
Default value: FALSE

Parallel_execution_message_size integer 4096
Description: Specifies the size of the message in parallel execution (parallel query, PDML, parallel recovery, and replication). If the value is greater than 2048 or 4096,
Requires a larger shared pool. If parallel_automatic_tuning = TRUE,
A message buffer is specified outside the large storage pool.
Value range: 2148-infinity.
Default value: If Parallel_automatic_tuning is FALSE, the normal value is 2148; If Parallel_automatic_tuning is TRUE, the value is 4096 (depends on the operating system).

Parallel_instance_group string
Description: A cluster database parameter that identifies a parallel routine group that is used to generate a large number of concurrent execution dependencies. Parallel operation only on its instance_groups
A routine that specifies a matching group in a parameter produces a large number of concurrent execution dependencies.
Value range: A string representing the group name.
Default value: A group consisting of all current activity routines

Parallel_max_servers integer 160
Description: Specifies the maximum number of parallel execution servers or parallel recovery processes for a routine. If necessary, the number of query servers allocated at the start of the routine is increased to that number.
Value range: 0-256
Default value: Determined by Cpu_count, parallel_automatic_tuning and Parallel_adaptive_multi_user

Parallel_min_percent integer 0
Description: Specifies the minimum percentage of threads that execute requirements in parallel. Setting this parameter ensures that parallel execution will display an error message when no appropriate query subordinate process is available.
And the query is therefore not executed.
Value range: 0-100
Default value: 0, which means that the parameter is not used.

Parallel_min_servers integer 0
Description: Specifies the minimum number of query server processes that Oracle creates after executing a startup routine in parallel.
Value range: 0-parallel_max_servers.
Default value: 0

Parallel_server Boolean TRUE
Description: Set Parallel_server to TRUE to enable the cluster database option.
Value Range: TRUE | FALSE
Default value: FALSE

Parallel_server_instances Integer 2
Description: The number of currently configured routines. It is used to determine the size of the SGA structure, which is determined by the number of configured routines. Setting this parameter correctly will improve the SGA
Memory usage. There are several parameters that are computed in this quantity.
Value range: Any value other than 0.
Default value: 1

PARALLEL_THREADS_PER_CPU Integer 2
Description: Describes the number of processes or threads that a CPU can handle during parallel execution.
The parallel adaptive algorithm and load balancing algorithm are optimized. If your computer has an overload in performing a typical query, you should reduce the value.
Value range: Any value other than 0.
Default value: Depending on the operating system (usually 2)

Example: Parallel Execution for a session
Execute sessions in parallel, sometimes in order to speed up execution, take full advantage of multiple CPU resources, and perform operations such as creating indexes in parallel.
You can use the ALTER SESSION statement to perform certain actions in parallel
ALTER Session ENABLE PARALLEL dml| ddl| QUERY
Close with the following statement
Alter session disable parallel ddl| dml| QUERY
Force parallel Execution:
ALTER Session FORCE PARALLEL dml| ddl| QUERY