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

This article mainly explains "Oracle parallel query introduction and testing effect". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "Oracle parallel query introduction and test effect".

Parallel concept

The so-called parallel execution means that a large serial task (any DML, general DDL) can be physically divided into several small parts, which can be processed at the same time.

When to use parallel execution:

1. There must be a very large task

2. There must be sufficient resources (CPU,I/O,MEMORY)

Parallel query

Parallel queries allow a SQL SELECT statement to be divided into several smaller queries, each running concurrently. Finally, the results of each smaller query are combined to get the final result.

There are several ways to start parallel queries:

1. Use a hint prompt in the query:

Select / * + parallel (4) * / count (*) from test_a;-specifies a parallel query with a degree of parallelism of 4.

2. Use alter table to modify the table:

Alter table test_a parallel 4 Murray-tells oracle to use parallelism 4 when creating an execution plan for this table.

Data testing

I did a test in PL/SQL yesterday. Let's take a look at it first. There are 24548 records in the tskuplu table of my database.

First of all, I use the normal query method to find that all the queries come out in 103 seconds, as shown in the figure:

Then I use the parallel query method, which is set to 3 post-process queries, which takes 85 seconds. As shown in the figure:

At this time, I suddenly felt that I had saved nearly 18 seconds, and I thought it was really good, but when I checked again with an ordinary query, it was more than 80 seconds, and I was in a bad mood. To verify the parallel query again, I started testing again today.

Let's run a parallel query today to see the effect, 79 seconds.

Then I executed the ordinary query, and the result time was about the same. I didn't show the picture here. I was wondering if the parallel query was not executed, so we changed the next way and modified the table structure.

Through the automatic parallel query method of modifying the table above, we directly use the ordinary query to see the effect of the query, and the result is 97 seconds.

Through the v$px_process query, it is found that the parallelism has also been executed, but the result is not satisfactory.

Conclusion

Through the above test, I think there may be some problems with my testing machine, so parallel query has not substantially improved the query. Originally, I thought that if there was a substantial improvement, then in the program I am going to do, I can consider using parallel query to propose all, and then through cache processing. Now, it is more appropriate to use the paging query written by myself.

Summarize the common problems of parallel queries (excerpted from the Internet)

1. See if the parallel option is installed

Select * FROM V$OPTION

Where parameter like 'Parallel%'

Take a look

Is Parallel execution TRUE?

2. If it is TRUE, check it after executing the statement.

Select * from V$pq_sesstat

Where name like'% Parallelized'

If Queries Parallelized > > 0, parallelism is performed.

3. You can force the use of PARALLEL, regardless of the number of CPU, but there is no advantage in using parallelism under a single CPU

Alter session force parallel query

4. How do you know that the statement does not use PARALLEL?

If you use EXPLAIN, then there are two scripts to look at the execution plan UTLXPLS.UTLXPLP the first one looks at the serial plan, the second one can see the parallel plan, if you use SET AUTOTRACE, then if you see P-> S, then the plan is already parallel.

1. What are the parameters related to parallel queries?

Parallel_adaptive_multi_user boolean

Enables or disables an adaptive algorithm designed to improve the performance of multiuser environments that use parallel execution. This function is realized when the query is started by automatically reducing the parallelism of the request according to the system load. When PARALLEL_AUTOMATIC_TUNING = TRUE, the effect is the best.

TRUE | FALSE if PARALLEL_AUTOMATIC_TUNING = TRUE, the value is TRUE; otherwise it is FALSE

Parallel_automatic_tuning boolean

If set to TRUE,Oracle, default values are determined for parameters that control parallel execution. In addition to setting this parameter, you must also set parallelism for the tables in the system. TRUE | FALSE FALSE

Parallel_execution_message_size integer

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 required. If PARALLEL_AUTOMATIC_TUNING = TRUE, the message buffer is specified outside the large storage pool.

2148-infinity. If PARALLEL_AUTOMATIC_TUNING is FALSE, the usual value is 2148; if PARALLEL_AUTOMATIC_TUNING is TRUE, the value is 4096 (depending on the operating system).

Parallel_max_servers integer

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.

0-256 is determined by CPU_COUNT, PARALLEL_AUTOMATIC_TUNING and PARALLEL_ADAPTIVE_MULTI_USER

Parallel_min_percent integer

Specifies the minimum percentage of threads required for parallel execution. Setting this parameter ensures that when parallel execution does not have the appropriate query dependencies available, an error message is displayed and the query is therefore not executed.

Parallel_min_servers integer

Specifies the minimum number of query server processes created by Oracle for parallel execution of startup routines.

0-PARALLEL_MAX_SERVERS.

Parallel_threads_per_cpu integer

Describe the number of processes or threads that can be processed by a CPU during parallel execution, and optimize parallel adaptive algorithms and load balancing algorithms. If the computer shows signs of overload when executing a typical query, this value should be reduced.

Any non-zero value. Depending on the operating system (usually 2)

two。 What parallel queries are running at the moment?

14:13:46 SQL > desc v$px_session

Is the name empty? Types

SADDR RAW (4)

SID NUMBER

SERIAL# NUMBER

QCSID NUMBER

QCSERIAL# NUMBER

QCINST_ID NUMBER

SERVER_GROUP NUMBER

SERVER_SET NUMBER

SERVER# NUMBER

DEGREE NUMBER

REQ_DEGREE NUMBER

3. How can queries be executed in parallel?

If the degree of parallelism is specified when creating the table, for example:

Create TABLE LI2.PAR_T

(

A VARCHAR2 (5)

)

PARALLEL 5

Then the full table scan of the table will be done in parallel.

14:26:05 SQL > set autot on

14:26:11 SQL > select * from par_t

No rows selected

Time spent: 00: 00: 00.02

Execution Plan

0 Select STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=328)

10 TABLE ACCESS* (FULL) OF 'PAR_T' (Cost=1 Card=82 Bytes=328): Q6000

1 PARALLEL_TO_SERIAL Select / * + NO_EXPAND ROWID (A1) * / A1. "A" FRO

M "PAR_T" PX_GRANULE (0, BLOCK_RANGE

If the table does not specify a degree of parallelism, it can be implemented in hint when querying, for example:

Select / * + full (t) parallel (tmeme 5) * / * from your_table t where.

4. Why are queries that should have been executed in parallel not executed in parallel?

The parallelism of the system is determined by parallel_max_servers, if its parallelism is 5. 5. If a query with a degree of parallelism of 5 is running, the system can no longer run the parallel query until the query is completed, and the parallel query will be run in a non-parallel manner.

5 if there is a query whose parallelism is lower than the maximum number of parallelism in the system, how will the next parallel query run?

When you specify parallel degree 4 oracle tries to allocate 4 producer slaves and 4 consumer slaves. The producers can feed any of the consumers.

If there are only 2 slaves available then we use these.

If there is only 1 slave available then we go serial

If there are none available then we use serial.

If parallel_min_percent is set then we error ora 12827 instead of using a lower number of slaves or going serial

6. How big is the parallel_max_servers?

In a multi-CPU environment, the number of CPU-1 or CPU is generally taken as the maximum number of parallelism, because parallel queries also need a process to coordinate each parallel process. There is nothing to say about single CPU.

7. Can parallel queries improve the performance of the system?

When the parallel query is running, it is easy to make the machine run under high load, which greatly lengthens the processing time of other transactions. Parallel queries are generally suitable for manual execution at non-peak traffic, but not suitable for running parallel queries in programs.

PINNER:

Parallelism is not fast, but only suitable for low business requests and low concurrent operations in a data warehouse environment.

A typical OLTP system, if our system, absolutely does not allow parallel queries.

Thank you for your reading, the above is the content of "introduction of Oracle parallel query and test effect". After the study of this article, I believe you have a deeper understanding of the introduction of Oracle parallel query and test effect, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.