Shulou(Shulou.com)06/01 Report--

Introduction: oracle parallelism has not been paid much attention to before. A few days ago, a brother encountered a problem that made me feel that this thing still has a lot to pay attention to, and it is necessary to be familiar with it carefully. In fact, the problem encountered is not complicated:

A sentence similar to the following: insert into xxxx select / + parallel (a) / * from xxx a; has about 75 gigabytes of data. the brother has not finished running from morning to afternoon. He came to me and asked me what was going on. He said that what 2hrs could run for hours was still messing around. Check the system performance is also relatively normal, cpu,io are not busy, the average READ speed is about 80M/s (barely), but the average write speed is less than 10m. There are a large number of''PX Deq Credit: send blkd','in the waiting event. You can see that there is something wrong with the parallelism here, and you finally know that there is a problem with the parallel usage, and the operation is completed 20 minutes after the modification. The right thing to do 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, you must enable the DML statement manually. In addition, we have to say that parallelism is not an extensible feature, and it is beneficial to make full use of resources only in data warehouse or as a tool for a small number of people, such as DBA, while parallelism needs to be used very carefully in OLTP environment. In fact, PDML still has many limitations, such as not supporting triggers, reference constraints, advanced replication, distributed transactions and other features, but also brings additional space footprint, the same is true of PDDL. You can refer to the official documentation for Parallel excution, which is also brilliantly described in Thomas Kyte's new book "Expert Oracle Database architecture".

.

.

I execute on one of the SESSION

SQL > create table test3 parallel 4 as select * from test1

The table has been created.

SQL > select * from v$mystat where rownum=1

SID STATISTIC# VALUE 151 0 1

SQL >

Then immediately multiply another SESSION by an execution that is not finished. See below, so there are four parallel processes in the process.

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 4 4

6D32421C 136 11 151 107 11 1 24 4

6D3267AC 138 18 151 107 1 1 13 4 4

6D31F6FC 132 11 151 107 11 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 enlarge it,

SQL > /

SADDR SID SERIAL# QCSID QCSERIAL# QCINST_ID SERVER_GROUP SERVER_SET SERVER# DEGREE REQ_DEGREE

6D31864C 126 10 151 107 1 1 1 7 10

6D31F6FC 132 17 151 107 1 1 1 2 7 10

6D32421C 136 15 151 107 1 1 13 7 10

6D3267AC 138 22 151 107 1 1 1 4 7 10

6D322F54 135 11 151 107 11 15 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

Eight rows have been selected.

SQL >

Strange, how do you think there are only seven? I wrote it there.

SQL > create table test4 parallel 10 as select * from test1

The table has been created.

Why are there three missing?

But I actually have only one CPU machine. What does that mean?

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, and parallelism is very resource-consuming.

This degree of parallelism is related to your initialization parameters. CPU_COUNT, PARALLEL_THREADS_PER_CPU, and so on are all relevant. If you do not specify the degree of parallelism when you create the table, oracle will automatically set the degree of parallelism as needed.

Use Oracle parallel query to exert the power of multi-CPU

It is a current trend to install more CPU on a separate server. With symmetric multiprocessing server (SMP), it is not surprising that an Oracle server has 8, 16, or 32 CPU and gigabit RAM SGA.

Oracle keeps pace with hardware development and provides a lot of features for multi-CPU. Starting with Oracle8i, Oracle implements parallelism in every database function, including SQL access (full table retrieval), parallel data manipulation, and parallel recovery. The challenge for Oracle Pro is to configure as many CPU 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 the response time for large full-table retrieval and to invoke parallel transaction rollbacks and so on.

Use OPQ

When performing a legal, large-scale full-table retrieval in Oracle, OPQ can greatly improve response time. The table is divided into logical blocks as shown in figure A through OPQ,Oracle.

Figure A

Tables divided by OPQ

Once the table is divided into blocks, Oracle enables parallel subqueries (sometimes called chores processes), each of which reads a block of a large table at the same time. When all subqueries are complete, Oracle passes the results to the parallel query scheduler, which reschedules the data, sorts if necessary, and passes the results to the end user. OPQ is infinitely scalable, so a full table retrieval that used to take a few minutes now has a response time of less than 1 second.

OPQ relies heavily on the number of processors, and the reason why running in parallel can greatly improve the performance of full table retrieval is that one parallel process (the number of CPU on the N=Oracle server) is used.

It is important to note that Oracle9i can automatically detect the external environment, including the number of CPU on the server. During installation, Oracle9i checks the number of CPU on the server, sets a parameter called cpu_count, and uses cpu_count as the default initialization input parameter. These initialization parameters affect Oracle's processing of internal queries.

Here are some of the parameters that Orale sets according to cpu_count during installation:

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 CPU affects these parameters.

Parameter fast_start_parallel_rollback

One of the exciting things about Oracle parallelism is the ability to call parallel rollback in the event of a system crash. When a rare crash occurs in the Oracle database, Oracle automatically detects outstanding transactions and rolls back to the starting state. This is called a parallel hot start, while Oracle uses the cpu_count-based fast_start_parallel_rollback parameter to determine the nature of the outstanding transaction.

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 the number of system CPU, but some DBA think it 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 Oracle knows the number of CPU in the server, it automatically allocates the appropriate number of child processes to improve the response time of parallel queries. Of course, there will be other external factors, such as table partitioning and disk input / output subsystem layout, but setting parallel_max_servers parameters according to cpu_count will give Oracle a reasonable basis to choose the degree of parallelism.

Because the parallel operation of Oracle depends heavily on the number of CPU on the server, parallel_max_servers is set to the number of CPU on the server. If you run multiple instances on a single server, the default value is too large, resulting in excessive page exchange and a heavy CPU burden. The degree of parallelism also depends on the number of partitions in the target table, so 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, which defines the number of reserved RAM for immediate writing to the redo log information, is affected by cpu_count. Oracle recommends that the maximum log_buffer is cpu_count multiplied by 500KB or 128KB. The number of CPU is important for log_buffer because Oracle generates multiple log write (LGWR) processes to release redo information asynchronously.

Log_buffer is one of the most misunderstood RAM parameters in Oracle, and there are usually the following configuration errors:

Log_buffer is set too high (for example, greater than 1MB), which causes performance problems because high volume results in write synchronization (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 within the Oracle database to manage database buffering, which is heavily dependent on the number of CPU on the server.

Many smart Oracle9i DBA uses multi-flush data buffers (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 a lot in Oracle8i, but becomes an undeclared parameter in Oracle9i because Oracle now sets a reasonable default value based on the number of CPU the database has.

Db_block_lru_latches is set to half of the cpu_count on the server by default (for example, there is only one Oracle database on the server). Oracle recommends that db_block_lru_ latches should never be more than two or three times the size of cpu_count or 1/50 of db_block_buffers.

This calculation has a problem if you use multiple buffer pools 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.

Strengthen the server

Oracle databases are always improving performance, and the ability to detect cpu_count and basic parameter settings against an external server environment is an important enhancement for Oracle software.

As more Oracle systems move to SMP, these parameters become more important when customers take enhancement measures and move a large number of databases to huge servers with 32 or 64 CPU.

Description of parallel parameters of 10G

Parallel_adaptive_multi_user boolean TRUE

Description: enable or disable an adaptive algorithm designed to improve the performance of multiuser environments that use parallel execution. By automatically reducing the parallelism of the request according to the system load

Implement this feature when you start the query. When PARALLEL_AUTOMATIC_TUNING = TRUE, the effect is the best.

Value range: TRUE | FALSE

Default: if PARALLEL_AUTOMATIC_TUNING = TRUE, the value is TRUE; otherwise FALSE

Parallel_automatic_tuning boolean TRUE

Note: if set to TRUE, Oracle will determine the default values for the 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 messages for parallel execution (parallel query, PDML, parallel recovery and replication). If the value is greater than 2048 or 4096

A larger shared pool is needed. If PARALLEL_AUTOMATIC_TUNING = TRUE

The message buffer will be specified outside the large storage pool.

Value range: 2148-infinity.

Default value: if PARALLEL_AUTOMATIC_TUNING is FALSE, usually 2148; if PARALLEL_AUTOMATIC_TUNING is TRUE, the value is 4096 (depending on the operating system).

Parallel_instance_group string

Description: a cluster database parameter that identifies a group of parallel routines used to generate a large number of parallel execution dependencies. Parallel operations only apply to their INSTANCE_GROUPS

The routines that specify a matching group in the parameter produce a large number of parallel execution dependencies.

Value range: a string that represents the group name.

Default value: group made up of all currently active 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 when the routine starts will be 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 required for parallel execution. Setting this parameter ensures that an error message is displayed when there is no appropriate query slave process available for parallel execution

And the query will not be executed as a result.

Value range: 0-100

The default value is 0, which means that this parameter is not used.

Parallel_min_servers integer 0

Description: specifies the minimum number of query server processes created by Oracle after parallel execution of startup routines.

Value range: 0-PARALLEL_MAX_SERVERS.

Default value: 0

Parallel_server boolean TRUE

Description: you can enable the cluster database option by setting PARALLEL_SERVER to TRUE.

Value range: TRUE | FALSE

Default value: FALSE

Parallel_server_instances integer 2

Description: the number of routines currently configured. It is used to determine the size of the SGA structure, which is determined by the number of routines configured. Setting this parameter correctly will improve SGA.

Memory usage of the Several parameters are calculated with this number.

Value range: any non-zero value.

Default value: 1

Parallel_threads_per_cpu integer 2

Description: indicates 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 the computer shows signs of being overloaded when executing a typical query, you should reduce this value.

Value range: any non-zero value.

Default value: depending on the operating system (usually 2)

For example: Parallel Execution for a Session

Parallel execution of sessions, sometimes in order to speed up execution, make full use of multi-CPU resources, such as parallel indexing operations.

To perform certain operations in parallel, you can use alter session statements

ALTER SESSION ENABLE PARALLEL DML | DDL | QUERY

Turn it off 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 parallel processes allowed in the system. The Oracle documentation describes this parameter as follows:

PARALLEL_MAX_SERVERS specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle increases the number of processes from the number created at instance startup up to this value.

If you set this parameter too low, some queries may not have a parallel execution process available to them during query processing. If you set it too high, memory resource shortages may occur during peak periods, which can degrade performance.

This parameter is static in 9i and older versions, and the modification requires a restart. Dynamic modification can take effect after 10g. In the case of a RAC environment, each node should be set to the same value.

For 9i

1. If PARALLEL_AUTOMATIC_TUNING=FALSE

PARALLEL_MAX_SERVERS=5

two。 If PARALLEL_AUTOMATIC_TUNING=TRUE

PARALLEL_MAX_SERVERS=CPU_COUNT x 10

In 9i, PARALLEL_AUTOMATIC_TUNING defaults to FALSE, 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

two。 If PGA_AGGREGATE_TARGET=0

PARALLEL_MAX_SERVERS=CPU_COUNT x PARALLEL_THREADS_PER_CPU x 5

If a machine has 50 CPU and Oracle9i is installed, the default value of PARALLEL_MAX_SERVERS is 5. After upgrading to Oracle10g, the default value of PARALLEL_MAX_SERVERS becomes 1000, which is a noteworthy change and usually needs to be reset as needed.

For OLTP libraries, it is not appropriate to set too large PARALLEL_MAX_SERVERS, because there are not many scenarios available for parallel operations in OLTP, which is generally used when creating and adjusting indexes. Parallel scanning using direct path read will lead to the segment checkpoint of scanned objects. If the system is very busy at that time, the consequences may be very serious.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.