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

Parallel Query option is essentially nonscalable-- Practical Oracle8i:Building Efficient Database

In essence, the parallel operation characteristic of Oracle is to forcibly extract the idle resources of the database server (mainly CPU resources) and divide and conquer some heavy load and large amount of data. Parallel operation is a non-deterministic optimization strategy, which should be treated carefully when choosing. At present, the main aspects that use the features of parallel operations are as follows:

ü Parallel Query: parallel queries, using multiple operating system level Server Process to complete a SQL query at the same time

ü Parallel DML: parallel DML operations. Similar to Parallel Query. When performing DML operations on the big data scale, such as insert, update and delete, you can consider using the

ü Parallel DDL: parallel DDL operations. Such as building large-capacity data tables, indexing rebuild, etc.

ü Parallel Recovery, parallel recovery. When the database instance crashes and restarts, or when the storage media is recovered, the parallel recovery technology can be started. So as to reduce the recovery time.

ü Procedural Parallel, parallelization of process code.

1. Parallel query Parallel Query

Parallel query is a basic technology, and it is also a parallel technology often used by OLAP and Oracle Data Warehouse. As with the elements that have been emphasized earlier in this series, there are some prerequisites for hardware and software before determining the use of parallel technology:

The necessary conditions for task task. The alternative task task for parallel operations must be a large task job, such as a long query. Task time can usually be counted in minutes or hours. Only with such tasks and needs is it worthwhile for us to risk using parallel operation schemes.

Conditions for idle resources. Parallel processing can be considered only when database server resources are idle. If you are constantly busy, the hasty use of parallelism can only aggravate the competition for resources.

The biggest risk of parallel operation is that the efficiency caused by parallel contention decreases instead of rising. Therefore, it is necessary to determine the two premises and then carry out parallel planning processing.

2. Environmental preparation

First of all, prepare the experimental environment. Due to the general household PC virtual machine used by the author, parallelism and storage capacity can not reflect the real conditions, excuse me.

SQL > select * from v$version where rownum select count (*) from t

COUNT (*)

-

1160704

Select the 11gR2 server environment, the total amount of data in Datasheet T is more than 1 million.

First, let's take a look at the execution that does not use parallelism.

/ / extract the information about using cursors

SQL > select sql_text, sql_id, version_count from v$sqlarea where sql_text like 'select count (*) from t%'

SQL_TEXT SQL_ID VERSION_COUNT

Select count (*) from t 2jkn7rpsbj64t 2

SQL > select * from table (dbms_xplan.display_cursor ('2jkn7rpsbj64tparallel format = >' advanced', cursor_child_no = > 0))

PLAN_TABLE_OUTPUT

SQL_ID 2jkn7rpsbj64t, child number 0

-

Select count (*) from t

Plan hash value: 2966233522

-

| | Id | Operation | Name | Rows | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | 4464 | | |

| | 1 | SORT AGGREGATE | | 1 |

| | 2 | TABLE ACCESS FULL | T | 1160K | 4464 (1) | 00:00:54 |

-

The parallelism feature is not used in the execution plan to perform a full table scan. The execution time is 54s.

3. Parallel query plan

First, we set the corresponding degree of parallelism. There are two ways to set the degree of parallelism. One is to use hint to add to a specific SQL statement. The other is to set the parallelism property on large objects.

The advantage of the former is that it is mandatory and targeted. Is to specify specific SQL statements for parallel processing. This advantage is easy to control parallelism, the disadvantage is that it has a strong coercive force, when the amount of data is small, the advantage of using parallelism is not great. Moreover, if the specified degree of parallelism is displayed, it will bring the disadvantage of poor scalability of transplantation.

The latter specifies the degree of parallelism through the properties of the object. As an execution means, parallelism is provided to the optimizer for selection. In this way, CBO will generate the execution plan according to the resources in the system and the reality of the data. It may or may not be parallel in the plan. The advantage is that the parallelism is left to the optimizer CBO to judge, and the disadvantage is the risk of parallel abuse.

Here, the author sets the way to automatically determine the degree of parallelism.

SQL > alter table t parallel

Table altered

SQL > select count (*) from t

COUNT (*)

-

1160704

When a query is started, the pool of parallel servers in Oracle determines the number of parallel processes allocated according to the load and actual factors in the system. At this point, we can view the connection information in the process pool through the view v$px_process.

SQL > select * from v$px_process

SERVER_NAME STATUS PID SPID SID SERIAL#

P000 AVAILABLE 25 5776

P001 AVAILABLE 26 5778

The slave process is managed through the process pool, and once initialized, it will reside in the system for a certain period of time, waiting for the next parallel processing to arrive.

At this point, we check the v$process view and find the corresponding information.

SQL > select * from v$process

PID SPID PNAME USERNAME SERIAL# PROGRAM

25 5776 P000 oracle 13 oracle@oracle11g (P000)

26 5778 P001 oracle 6 oracle@oracle11g (P001)

(for reasons of space, there are omissions. )

32 rows selected

In the corresponding OS, there are also corresponding real processes to serve.

[oracle@oracle11g ~] $ps-ef | grep oracle

(for reasons of space, there are omissions. )

Oracle 5700 1 0 17:29? 00:00:02 oraclewilson (LOCAL=NO)

Oracle 5723 1 0 17:33? 00:00:00 ora_smco_wilson

Oracle 5764 1 2 17:40? 00:00:05 oraclewilson (LOCAL=NO)

Oracle 5774 1 0 17:42? 00:00:00 oraclewilson (LOCAL=NO)

Oracle 5776 1 0 17:43? 00:00:00 ora_p000_wilson

Oracle 5778 1 0 17:43? 00:00:00 ora_p001_wilson

Oracle 5820 1 1 17:44? 00:00:00 ora_w000_wilson

Because the query has finished, the corresponding parallel session information has disappeared and is invisible.

SQL > select * from v$px_session

SADDR SID SERIAL# QCSID QCSERIAL#

--

However, if the task takes a long time, the corresponding information can be captured.

Judging from the above, when we perform a parallel operation, Oracle will get the corresponding parallel process from the server pool to perform the operation. When the operation is completed, the waiting process will wait for a certain amount of time and then be recycled.

The resource consumption of the parallel operation process is viewed in the v$px_sysstat view.

SQL > col statistic for A30

SQL > select * from v$px_process_sysstat

STATISTIC VALUE

Servers In Use 0

Servers Available 0

Servers Started 2

Servers Shutdown 2

Servers Highwater 2

Servers Cleaned Up 0

Server Sessions 6

Memory Chunks Allocated 4

Memory Chunks Freed 0

Memory Chunks Current 4

Memory Chunks HWM 4

Buffers Allocated 30

Buffers Freed 30

Buffers Current 0

Buffers HWM 8

15 rows selected

Next, let's check the execution plan information.

SQL > set pagesize 10000

SQL > select * from table (dbms_xplan.display_cursor ('2jkn7rpsbj64tparallel format = >' advanced',cursor_child)

_ no = > 1))

PLAN_TABLE_OUTPUT

-

SQL_ID 2jkn7rpsbj64t, child number 1

-

Select count (*) from t

Plan hash value: 3126468333

-

| | Id | Operation | Name | Rows | Cost (% CPU) | Time | TQ | IN-OUT | PQ Distrib |

-

| | 0 | SELECT STATEMENT | 2478 | | |

| | 1 | SORT AGGREGATE | | 1 |

| | 2 | PX COORDINATOR |

| | 3 | PX SEND QC (RANDOM) |: TQ10000 | 1 | Q1Prime00 | P-> S | QC (RAND) |

| | 4 | SORT AGGREGATE | | 1 | Q1Power00 | PCWP |

| | 5 | PX BLOCK ITERATOR | | 1160K | 2478 (1) | 00:00:30 | Q1Power00 | PCWC |

| | * 6 | TABLE ACCESS FULL | T | 1160K | 2478 (1) | 00:00:30 | Q1recover00 | PCWP |

-

Predicate Information (identified by operation id):

6-access (: Z > =: Z AND: Z AND: Z

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.