In-depth analysis of Oracle Parallel principles and case studies

I. Brief introduction to the features of OLTP and OLAP systems. A: OLTP and OLAP are the two most common systems in our daily production database. In short, OLTP is based on multiple transactions.

I. Brief introduction to the features of OLTP and OLAP systems. A: OLTP and OLAP are the two most common systems in our daily production database. In short, OLTP is based on multiple transactions.

Introduction: the parallel technology belongs to the big data field and is suitable for OLAP systems. It is widely used in scenarios such as task division, data block division, and abundant resources, this article focuses on six points, including parallel principles, practical applications, performance comparison, parallel direct loading, index attributes, and feature summary. The tests below are my notes. These notes also refer to the Guide by Tan Huaiyuan, author of "making Oracle run faster 2". I would like to express my gratitude to tan, thanks to friends like zhixiang yangqiaojie who helped us, let's start a pleasant journey!

I. Brief introduction to the features of OLTP and OLAP Systems

A: OLTP and OLAP are the two most commonly used systems in the daily production database. In short, OLTP is a system based on multi-transaction short time slices, the memory efficiency determines the database efficiency.
OLAP is a system based on a large dataset with a long time slice. The SQL Execution efficiency determines the database efficiency. Therefore, the "parallel" technology belongs to the scope of OLAP systems.
Ii. Parallel technology implementation mechanism and occasion
A: Parallel Processing is relative to serial processing. A large data block is divided into n small data blocks, and n processes are started to process n data blocks respectively, finally, the coordinater integration result is returned to the user. In fact, there is still a problem of communication between parallel processes in a parallel execution process (Inter-line interactive operations ). As mentioned above, parallel processing is a big data processing technology suitable for OLAP and not for OLTP, because SQL Execution efficiency in OLTP systems is usually very high.
Iii. Test the application and rules of parallel technology in practice
(1) When parallel technology is used on the indexed table leo_t, but it does not work
Create a table
LS @ LEO> create table leo_t as select rownum id, object_name, object_type from dba_objects;
Create an index on the table id column
LS @ LEO> create index leo_t_idx on leo_t (id );
Collect leo_t statistics
LS @ LEO> execute dbms_stats.gather_table_stats (ownname => 'Ls', tabname => 'Leo _ t', method_opt => 'for all indexed columns size
2', cascade => TRUE );
Start four degree of parallelism for the table
LS @ LEO> alter table leo_t parallel 4;
Start execution plan
LS @ LEO> set autotrace trace explain stat
LS @ LEO> select * from leo_t where id = 100; data retrieved using the index has not started parallel
Execution Plan
----------------------------------------------------------
Plan hash value: 2049660393
Bytes -----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------
| 0 | select statement | 1 | 28 | 2 (0) | 00:00:01 |
| 1 | table access by index rowid | LEO_T | 1 | 28 | 2 (0) | 00:00:01 |
| * 2 | index range scan | LEO_T_IDX | 1 | 1 (0) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("ID" = 100)
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
4 consistent gets 4 consistent reads, that is, processing 4 data blocks
0 physical reads
0 redo size
544 bytes sent via SQL * Net to client
381 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Note: we started parallelism on this table, but it didn't work because the CBO optimizer uses B-tree indexes to retrieve data and directly locates rowid (B-tree indexes are suitable for low repetition rate ). field ), therefore, four consistent reads occur. It is found that the efficiency of using indexes is very high, and the resource cost is relatively small. It is not necessary to use parallel operations.
(2) read a parallel execution plan
LS @ LEO> select object_type, count (*) from leo_t group by object_type; object type grouping statistics
35 rows selected.
Execution Plan Parallel Execution Plan
----------------------------------------------------------
Plan hash value: 852105030
Bytes ------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | TQ | IN-OUT | PQ Distrib |
Bytes ------------------------------------------------------------------------------------------------------------------
| 0 | select statement | 10337 | 111K | 6 (17) | 00:00:01 |
| 1 | px coordinator |
| 2 | px send qc (RANDOM) |: TQ10001 | 10337 | 111K | 6 (17) | 00:00:01 | Q1, 01 | P-> S | QC (RAND) |
| 3 | hash group by | 10337 | 111K | 6 (17) | 00:00:01 | Q1, 01 | PCWP |
| 4 | px receive | 10337 | 111K | 6 (17) | 00:00:01 | Q1, 01 | PCWP |
| 5 | px send hash |: TQ10000 | 10337 | 111K | 6 (17) | 00:00:01 | Q1, 00 | P-> P | HASH |
| 6 | hash group by | 10337 | 111K | 6 (17) | 00:00:01 | Q1, 00 | PCWP |
| 7 | px block iterator | 10337 | 111K | 5 (0) | 00:00:01 | Q1, 00 | PCWC |
| 8 | table access full | LEO_T | 10337 | 111K | 5 (0) | 00:00:01 | Q1, 00 | PCWP |
Bytes ------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
44 recursive CILS
0 db block gets
259 consistent gets 259 consistent reads, that is, processing 259 data blocks
0 physical reads
0 redo size
1298 bytes sent via SQL * Net to client
403 bytes encoded ed via SQL * Net from client
4 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
35 rows processed
Ps-ef | grep oracle can also detect four parallel processes and one coordination process from the background process.
Oracle 25075 1 0? 00:00:00 ora_p000_LEO
Oracle 25077 1 0? 00:00:00 ora_p001_LEO
Oracle 25079 1 0? 00:00:00 ora_p002_LEO
Oracle 25081 1 0? 00:00:00 ora_p003_LEO
Oracle 25083 1 0? 00:00:00 ora_p004_LEO
Note: In the select statement for grouping and sorting, a large number of datasets are processed (259 consistent reads occur). In this case, splitting data blocks in parallel can improve the efficiency, therefore, oracle uses the parallel technology to explain the parallel execution plan steps. The parallel execution plan should be read from the bottom up. When we see the PX (parallel execution) keyword, the parallel technology is used.
1. First, full table Scan
2. The parallel process accesses the data block through iterative iterator and submits the scan result to the parent process as a hash group.
3. Perform the hash group operation on the data transmitted by the child process by the parallel parent.
4. The parallel sub-process (px send hash) sends the processed data. The sub-process is relative to the parent process. We define the sender as the sub-process, and the receiver as the parent process.
5. The parallel parent process (px receive) receives the processed data.
6. query coordinator integration results (Object Type grouping statistics) are sent to the parallel coordination process in a random order)
7. After completion, QC will return the integrated result to the user.
Description of the special IN-OUT column IN the parallel execution plan (indicating the direction of the data stream IN the Operation)
Parallel to Serial (P-> S): indicates that a Parallel operation sends data to a Serial operation. Generally, the Parallel result is sent to the QC of the Parallel scheduling process for summary.
Parallel to Parallel (P-> P): indicates that a Parallel operation sends data to another Parallel operation. Generally, it refers to data exchange between a Parallel parent process and a Parallel child process.
Parallel Combined with parent (PCWP): Parallel operations performed by the same subordinate process, and parent operations are also Parallel.
Parallel Combined with Child (PCWC): Parallel operations performed by the same subordinate process, and Parallel sub-operations.
Serial to Parallel (S-> P): indicates that a Serial operation sends data to a Parallel operation. If the select part is a Serial operation, this problem occurs.
(3) introduce four common parallel initialization parameters
Parallel_min_percent 50% indicates that the minimum threshold for SQL parallelism is specified for execution. If this threshold is not reached, oracle Reports A ora-12827 error.
Parallel_adaptive_multi_user TRUE indicates that the SQL concurrency is dynamically adjusted based on system resources, and the best execution performance has been achieved.
Parallel_instance_group indicates the parallel operation between several instances.
Parallel_max_servers 100 indicates that the number of parallel processes of the entire database instance cannot exceed this value
Parallel_min_servers 0 indicates the number of parallel processes initially allocated when the database is started. If the degree of parallelism we set is smaller than this value, the parallel coordination process will allocate the number of parallel processes according to our degree of parallelism, if the concurrency we set is greater than this value, the parallel coordination process will start other parallel processes to meet our needs.
(4) use hint to test DML parallel query performance
When can parallel technology be used?
1. Object Property: a parallel keyword is specified during creation, which is valid for a long time.
2. SQL forcible execution: Use the hint prompt method in SQL to use parallel, temporarily valid, which restricts the execution of SQL statements. In this test, the hint method is used.
LS @ LEO> select/* + parallel (leo_t 4) */count (*) from leo_t where object_name in (select/* + parallel (leo_t1 4) */object_name from
Leo_t1 );
Execution Plan
----------------------------------------------------------
Plan hash value: 3814758652
Bytes -------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | TQ | IN-OUT | PQ Distrib |
Bytes -------------------------------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 94 | 16 (0) | 00:00:01 |
| 1 | sort aggregate | 1 | 94 |
| 2 | px coordinator |
| 3 | px send qc (RANDOM) |: TQ10002 | 1 | 94 | Q1, 02 | P-> S | QC (RAND) |
| 4 | sort aggregate | 1 | 94 | Q1, 02 | PCWP |
| * 5 | hash join semi | 10337 | 948K | 16 (0) | 00:00:01 | Q1, 02 | PCWP |
| 6 | px receive | 10337 | 282K | 5 (0) | 00:00:01 | Q1, 02 | PCWP |
| 7 | px send hash |: TQ10000 | 10337 | 282K | 5 (0) | 00:00:01 | Q1, 00 | P-> P | HASH |
| 8 | px block iterator | 10337 | 282K | 5 (0) | 00:00:01 | Q1, 00 | PCWC |
| 9 | table access full | LEO_T | 10337 | 282K | 5 (0) | 00:00:01 | Q1, 00 | PCWP |
| 10 | px receive | 10700 | 689K | 11 (0) | 00:00:01 | Q1, 02 | PCWP |
| 11 | px send hash |: TQ10001 | 10700 | 689K | 11 (0) | 00:00:01 | Q1, 01 | P-> P | HASH |
| 12 | px block iterator | 10700 | 689K | 11 (0) | 00:00:01 | Q1, 01 | PCWC |
| 13 | table access full | LEO_T1 | 10700 | 689K | 11 (0) | 00:00:01 | Q1, 01 | PCWP |
Bytes -------------------------------------------------------------------------------------------------------------------
Scan the leo_t1 table in the subquery in parallel, scan the leo_t table in the primary query, send the result to the QC integration result of the parallel coordination process in a random order, and finally return the result to the user.
Predicate Information (identified by operation id ):
---------------------------------------------------
5-access ("OBJECT_NAME" = "OBJECT_NAME ")
Note
-----
-Dynamic sampling used for this statement
Statistics
----------------------------------------------------------
28 recursive cballs
0 db block gets
466 consistent gets 466 consistent reads, that is, 446 data blocks are processed
0 physical reads
0 redo size
413 bytes sent via SQL * Net to client
381 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed