Oracle Parallel Query

Source: Internet
Author: User
The so-called parallel execution refers to the physical division of a large serial task (any DML, General DDL) into multiple small parts, which can be processed at the same time.

The so-called parallel execution refers to the physical division of a large serial task (any DML, General DDL) into multiple small parts, which can be processed at the same time.

Parallel Oracle execution

Parallel Execution refers to the physical division of a large serial task (any DML, General DDL) into multiple small parts, these smaller parts can be processed at the same time.

When to use parallel execution:

1. There must be a very large task

2. Sufficient resources (CPU, I/O, and MEMORY) are required)

Parallel Query

Parallel query allows you to divide an SQL SELECT statement into multiple smaller queries, and each query runs concurrently. Finally, combine the results of each small query to obtain the final result.

Start parallel query in several ways:

1. When a hint is used in a query, the following error occurs: select/* + parallel (4) */count (*) from test_a; --- specify a parallel query with a degree of parallelism of 4.

2. Use alter table to modify a table:

  • Alter table test_a parallel 4; -- tells oracle to use parallelism Level 4 when creating the execution plan for this table
  • Alter table test_a parallel; -- tell objective El, "Please consider parallel execution ." Oracle determines the proper degree of Parallelism Based on the system workload and query itself. Resources are sufficient, concurrency increases, resources are scarce, and concurrency decreases. Oracle can dynamically increase or decrease the amount of concurrent resources required for queries. --- You prefer to use this method to start parallel queries.
  • Observe the parallel query through the execution plan of a parallel query

    SQL> alter table test_a parallel;

    Table altered.

    Elapsed: 00:00:00. 05
    SQL> select count (*) from test_a;

    COUNT (*)
    ----------
    602704

    Elapsed: 00:00:00. 21

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2663325361

    Bytes --------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Cost (% CPU) | Time | TQ |IN-OUT| PQ Distrib |
    Bytes --------------------------------------------------------------------------------------------------------
    | 0 | select statement | 1 | 1290 (1) | 00:00:16 |
    | 1 | sort aggregate | 1 |
    | 2 | px coordinator |
    | 3 | px send qc (RANDOM) |: TQ10000 | 1 | Q1, 00 | P-> S | QC (RAND) |
    | 4 | sort aggregate | 1 | Q1, 00 | PCWP |
    | 5 | px block iterator | 602K | 1290 (1) | 00:00:16 | Q1, 00 | PCWC |
    | 6 | table access full | TEST_A | 602K | 1290 (1) | 00:00:16 | Q1, 00 | PCWP |
    Bytes --------------------------------------------------------------------------------------------------------

    Next, let's take a look at the highlights of page 2nd:

    Recommended reading:

    Oracle DML Process

    PL/SQL ORA-14551: cannot perform DML operations in queries solved

    Common MySQL DDL, DML, and DCL languages (example)

    Execute batch DML exercises for Oracle basic transactions and ForAll

    Oracle DML Statement (insert, update, delete) rollback Estimation

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.