Oracle Parallel Principles (i)

A brief introduction to the characteristics of OLTP and OLAP systems

A: OLTP and OLAP are the 2 most commonly used systems in our daily production libraries, and simply say that OLTP is a system based on a multiple transaction short time slice, and the efficiency of the memory determines the efficiency of the database.

OLAP is a long time slice system based on large data set, the efficiency of SQL execution determines the efficiency of database. Therefore, "parallel parallel" technology belongs to the category of OLAP system

Second, parallel technology implementation mechanism and occasion

A: Parallel is relative to the serial, a large data block divided into n small blocks of data, at the same time start n process processing n data blocks, and finally by the parallel coordinator Coordinater integration results returned to the user. In fact, there is a communication problem between parallel processes in parallel execution (interaction between parallel operations). It's also said that parallelism is a large data processing technology that is suitable for OLAP and is not suitable for OLTP because the efficiency of SQL execution in OLTP systems is usually very high.

Third, test the application of parallel technology in practice and rules

(1) Use of parallel technology on indexed table leo_t, but no effect

Create a table

Ls@leo> CREATE TABLE leo_t as select RowNum ID, object_name,object_type from dba_objects;

To create an index on a table ID column

Ls@leo> CREATE index LEO_T_IDX on leo_t (ID);

Collect table leo_t statistic information

Ls@leo> Execute dbms_stats.gather_table_stats (ownname=> ' LS ',tabname=> ' leo_t ',method_opt=> ' for all Indexed columns Size

2 ', cascade=>true);

To start 4 degrees of parallelism for a 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; The data retrieved using the index does not start the parallel

Execution Plan Implementation Plans

----------------------------------------------------------

Plan Hash value:2049660393

-----------------------------------------------------------------------------------------

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

-----------------------------------------------------------------------------------------

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

-----------------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("ID" =100)

Statistics Statistical information

----------------------------------------------------------

1 Recursive calls

0 db Block gets

4 consistent gets 4-time consistency read, that is, 4 blocks of data processing

0 physical Reads

0 Redo Size

544 Bytes sent via sql*net to client

381 bytes received via sql*net from client

2 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

1 rows processed

Description: We started parallelism on this table, but it didn't work because the CBO optimizer used the B-tree index to retrieve the data directly to the ROWID (B-tree index features are suitable for fields with a lower repetition rate), so only 4 consistent reads have occurred, and it is found that using indexes is very efficient, The cost of resources is relatively small without the need to use parallelism.

(2) Read a parallel execution plan

Ls@leo> Select Object_type,count (*) from the leo_t group by Object_type; Object Type Grouping statistics

Rows selected.

Execution Plan Parallel execution plans

----------------------------------------------------------

Plan Hash value:852105030

--------------------------------------------------------------------------------------------------------------- ---