Oracle Parallel Principles and examples

Parallel: Divide a work into several pieces and divide it into different processes and process them simultaneously. Process level

Concurrency: Multiple sessions are accessed at the same time, which is usually the number of concurrent numbers. Session level

Database version

Leo1@leo1> select * from V$version;

BANNER

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

Oracle Database 11g Enterprise Edition release 11.2.0.1.0-64bit Production

Pl/sql Release 11.2.0.1.0-production

CORE 11.2.0.1.0 Production

TNS for Linux:version 11.2.0.1.0-production

Nlsrtl Version 11.2.0.1.0-production

A parallel query execution plan with a 2-table association is given, and a parallel data flow diagram is drawn.

(1) Concurrent applications:

1.OLAP business model

First of all, we often mix and wash the concept of OLAP and data warehouse is not a thing.

Answer: NO

OLAP is a business model, which is a kind of business model of bulk loading, such as online data analysis system.

Data Warehouse: The underlying database that supports this business model.

2. System resources are relatively idle

When the system CPU more IO load memory is large enough, you can use parallel operations, you need to consider resource balance.

3. The data conforms to the parallelism characteristic

What is this description, is that you are operating the data is not serialized, you can split, while the different parts of the data to operate,

It is not appropriate to use parallel operations if it is a process that calculates the former to count the latter.

For example, statistical lines can be used in parallel, allowing 10 processes to count the number of rows in different regions and then merge the results back to the user.

(2) First express the use of parallel 3 ways

1.hints Mode Temporary effective

Select/*+ Parallel (leo1 2) */COUNT (*) from LEO1;

2.alter Table Object level definition parallel directly modify object properties long term validity

ALTER TABLE LEO1 parallel 4; Defines the Leo1 table parallelism of 4

3.alter Sessions Force Parallel session level defines the validity of a parallel session lifecycle

Alter session force parallel query parallel 4; Force definition of parallelism to 4

Usually/*+ parallel (leo1 4) * *

4. Parallel queries and parallel DDL can be accessible using parallelism, and if you want to use parallel DML, you need to start session DML parallel functionality

Alter session enable parallel DML;

(3) Experiment

Now we do a parallel operation, analysis of the parallel execution planning process, perceptual feelings of the charm of parallel

leo1@leo1> drop table Leo1 purge; Clean up the environment

Table dropped.

leo1@leo1> drop table Leo2 purge;

Table dropped.

Leo1@leo1> CREATE TABLE Leo1 as SELECT * from Dba_objects; Create a Leo1 table

Table created.

Leo1@leo1> CREATE TABLE Leo2 as SELECT * from Leo1; Simulate Leo1 create Leo2 table

Table created.

Leo1@leo1> Execute dbms_stats.gather_table_stats (ownname=> ' LEO1 ',tabname=> ' LEO1 '),

Method_opt=> ' For all indexed columns size 254 ');

Pl/sql procedure successfully completed.

Leo1@leo1> Execute dbms_stats.gather_table_stats (ownname=> ' LEO1 ',tabname=> ' LEO2 '),

Method_opt=> ' For all indexed columns size 254 ');

Pl/sql procedure successfully completed.

Whole-Table Analysis of LEO1 and LEO2 including data distribution analysis (data skew degree, i.e. straight square graph)

Tips

If you want to have n tables associated with the query and all open parallel, hints how to write it, please see the following

/*+ Parallel (leo1,leo2,2) * *, right? Of course not.

/*+ Parallel (leo1 leo2,2) * *, right? Of course it's not right.

/*+ Parallel (leo1,2) parallel (leo2,2) * * *? Yes, kiss them.

/*+ Parallel (leo1,2) parallel (leo2,2) parallel (leo3,2) ... * * After the same continuation of the same

leo1@leo1> select/*+ Parallel (leo1,2) parallel (leo2,2) */count (*) from Leo1,leo2

where leo1.object_id=leo2.object_id;

Execution plan parallelism of 2 execution plans

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

Plan Hash value:2718975204

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