Oracle Parallel Parallel Setup Understanding

Introduction: The feature of Oracle parallelism has not been much of a concern in the past. A few days ago a brother encountered a problem, just let me think this thing still has a lot to pay attention to places, it is necessary to carefully familiar with the next. 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 what happened, said the usual 2hrs can run out of things run for several hours also scattered movement. Check the system performance is also relatively normal, cpu,io are not busy, the average read speed around 80m/s (reluctantly), but the average write speed of only 10M. Wait for the event inside a large number of ' PX Deq credit:send blkd ', here can see the parallel problem, and finally learned that there is a problem in parallel usage, modified 20 minutes after the completion of 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 when the data warehouse or a few tools such as a DBA facilitate the full utilization of resources in bulk data operations. In fact, Pdml still has a lot of limitations, such as unsupported triggers, referential constraints, advanced replication, and distributed transactions, as well as additional footprint, Pddl. For parallel excution, you can refer to the official documentation, which is also described in Thomas Kyte's new book, Expert Oracle Database architecture.

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

I do it in one session
Sql> CREATE table Test3 parallel 4 AS SELECT * from Test1;

The table is created.

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

   SID STATISTIC#      VALUE

   151          0          1

Sql>

Then immediately at another session by the last execution is not over, see below, so that there are 4 parallel processes in the process 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>

When I stepped up

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 see only 7, I was written there
Sql> CREATE TABLE Test4 parallel as SELECT * from Test1;

The table is created.

Why are there 3 missing?

But I actually only have a CPU machine, what do these explain?

BTW

sql> SHOW Parameter Parallel_max

NAME TYPE VALUE

Parallel_max_servers Integer 20
Sql>

....................................................................................................................................
....................................................................................................................................
Open how many parallel server also depends on the load of the system at that time, parallel is very consumption of 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 don't explicitly specify the degree of parallelism when you build a table, Oracle automatically sets the degree of parallelism as needed.

Leverage the power of multiple CPUs with Oracle parallel queries
Installing more CPUs in a separate server becomes a current trend. With a symmetric multi-processing server (SMP), it is not surprising that an Oracle server has 8, 16, or 32 CPUs and a few gigabytes of RAM in the SGA.

Oracle is keeping up with the pace of hardware development and provides many CPU-oriented features. Starting with Oracle8i, Oracle implements parallelism in every database function, including SQL access (full table retrieval), parallel data operations, and parallel recovery. The challenge for Oracle Pro 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 it can be used to improve response time for large full-table retrievals, as well as invoke parallel transaction rollback, and so on.

Using OPQ

OPQ can significantly increase response time when a valid, large, full-table retrieval is performed in Oracle. Divide the table into a logical block shown by Opq,oracle.

Figure A

Table divided by OPQ

Once the table is partitioned into chunks, Oracle enables parallel subqueries (sometimes called chore processes), and each subquery reads one chunk of a large table at the same time. When all subqueries are complete, Oracle passes the results to the parallel query scheduler, which re-arranges the data, sorts it if necessary, and passes the results to the end user. OPQ has infinite scalability, so it takes a few minutes for a full table to retrieve the current response time in less than 1 seconds.

OPQ is heavily dependent on the number of processors, and the performance of full table retrievals can be greatly improved by running in parallel, provided that N-1 concurrent processes (the number of CPUs on the n=oracle server) are 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.

Here are some of the parameters that Orale set at the time of installation according to Cpu_count:

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 of the exciting thing about Oracle parallelism is the ability to invoke parallel rollback when a system crashes. When a rare crash occurs in an Oracle database, Oracle can automatically detect incomplete transactions and roll back to the start state. This is referred to as parallel hot-start, and Oracle uses the Cpu_count-based fast_start_parallel_rollback parameter to determine the degree of disposition of unfinished transactions.

Parallel Data Manipulation Language (DML) recovery can dramatically 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 it should be set to four times times the value of Cpu_count.

Parameter Parallel_max_servers_parameter

A significant 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 parameter according to Cpu_count 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 you run multiple instances on a single server, the default value is too large to cause excessive page swapping and severe CPU load. The degree of parallelism also depends on the number of partitions in the target table, so the parallel_max_servers should be set large enough to allow Oracle to select the best number of parallel subqueries for each query.

Parameter Log_buffer

The parameter Log_buffer defines the amount of reserved RAM available for immediate write redo log information, which is affected by Cpu_count. Oracle recommends Log_buffer maximum of 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 is set too high (for example, greater than 1MB), which can cause performance problems because large volumes result in write synchronization (for example, the log synchronization wait event is 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 in the Oracle database to manage database buffering, which relies heavily on the number of CPUs on the server.

Many smart oracle9i DBAs use a multi-punch data buffer (such as db_32k_cache_size), and they recommend resetting this undeclared parameter to the default maximum value. The db_block_lru_latches parameter is used much in oracle8i, but it becomes an undeclared parameter in Oracle9i because Oracle now sets a reasonable default value based on the number of CPUs the database has.

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

If you use a multi-buffer pool, there is a problem with this calculation because you cannot control the number of locks assigned to each data buffer pool. If the Db_writers parameter is greater than 1, the default value may appear too small.

strengthening servers

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 enhancement for Oracle software.

As more Oracle systems move to SMP, these parameters become even more important when customers are taking enhancements and moving numerous databases to a huge server with 32 or 64 CPUs.

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 multi-user environments using parallel execution methods. Automatically reduces the degree of parallelism of requests by the system load,
This feature is implemented when a query is started. When parallel_automatic_tuning = TRUE, it works best.
Value Range: TRUE | FALSE
Default value: If Parallel_automatic_tuning = True, the value is true; FALSE otherwise

Parallel_automatic_tuning Boolean TRUE
Description: If set to TRUE, Oracle determines the default values for parameters that control parallel execution. In addition to setting this parameter,
You must also set the 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,
A larger shared pool is required. If parallel_automatic_tuning = TRUE,
The message buffer is specified outside the large storage pool.
Value range: 2148-infinity.
Default value: If Parallel_automatic_tuning is FALSE, the value is usually 2148; If Parallel_automatic_tuning is TRUE, the value is 4096 (as determined by the operating system).

Parallel_instance_group string
Description: A cluster database parameter that identifies a group of parallel routines used to produce a large number of parallel execution slaves. Parallel operation is only for the instance_groups
A routine that specifies a matching group in a parameter produces a large number of parallel execution slaves.
Value range: A string representing the group name.
Default value: A group of all current active routines

Parallel_max_servers integer 160
Description: Specifies the maximum number of concurrent 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 require parallel execution. Setting this parameter ensures that parallel execution displays an error message when there is no appropriate query dependent process available.
And the query will not be executed for this reason.
Value range: 0-100
The 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 the start 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 routines that are currently configured. 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 calculated from this number.
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 process in parallel execution.
and optimize the parallel adaptive algorithm and load balancing algorithm. If your computer has signs of overloading while performing a typical query, you should reduce the value.
Value range: Any value other than 0.
Default: Depends on operating system (typically 2)

Example: Parallel execution for a Session
Executing sessions in parallel, sometimes in order to speed up execution, make full use of multi-CPU resources, such as creating indexes in parallel operations.
To use parallelism to perform certain operations, you can use the ALTER SESSION statement
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

The Parallel_max_servers parameter is used to set the maximum number of concurrent processes allowed in the system. Oracle's documentation describes this parameter as follows:

parallel_max_servers Specifies the maximum number of PARALLEL execution processes and PARALLEL recovery processes for an I Nstance. As demand increases, Oracle increases the number of processes from the number created at instance startup .

If you set the This parameter too low, some queries could not be a parallel execution process available to them during query P Rocessing. If you set the it too high, the memory resource shortages may occur during peak periods, which can degrade performance.
This parameter is a static parameter in the 9i and older versions, and the modification requires a reboot. 10g can be changed after the dynamic effect. In the case of a RAC environment, the nodes should be set to the same value.

For 9i
1. If Parallel_automatic_tuning=false
Parallel_max_servers=5
2. If parallel_automatic_tuning=true
Parallel_max_servers=cpu_count x 10

Parallel_automatic_tuning defaults to False in 9i, so parallel_max_servers defaults to 5

For 10g
1. If Pga_aggregate_target >0
Parallel_max_servers=cpu_count x parallel_threads_per_cpu x 10
2. If pga_aggregate_target=0
Parallel_max_servers=cpu_count x parallel_threads_per_cpu x 5

If a machine has 50 CPUs, installed oracle9i, then parallel_max_servers default value is 5, after upgrading to oracle10g, the Parallel_max_servers default value will change to 1000, which is a notable variation , you usually need to reset the parameter as needed.

For OLTP libraries, it is not advisable to set too large a parallel_max_servers, because parallel operations are not available in OLTP, which is typically used when creating and adjusting indexes, parallel scanning using direct path read, Causes the segment checkpoint of the scanned object, which can be very serious if the system is very busy at the time.

Oracle Parallel Parallel Setup Understanding