real-world performance training€¦ · •first determines if the sql statement will run serial or...

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Real-World Performance TrainingParallel Execution

Real-World Performance Team


Parallel Execution


Serial and Parallel Execution

• Serial Execution– SQL is executed by one process

– The correct solution when:• the query references a small data set

• high concurrency

• efficiency is important

• Parallel Execution– SQL is executed by many processes working together

– The correct solution when:• the query references a large data set

• low concurrency

• elapsed time is important

• Used to reduce the execution time of queries– Multiple processes work together to use more resources on the system, such as CPU and IO


Parallel Execution

Query Coordinator (QC) The “top level” process for the parallel query

Parallel Execution Server (PX) An (OS) process that operates on part of a parallel query

Parallel server group The group of parallel server processes that operate on a row source

Degree of Parallelism (DoP) The number of parallel execution servers used in each parallel server group during parallel execution

Basics

5


Parallel Execution

• Table Setting

– Can specify a value or set to parallel default

• Hint

– Useful for testing but usually not appropriate for production

• Alter session– Useful for testing but usually not appropriate for production

• Auto DoP

– The optimizer determines the DoP

Ways to set the DoP


Parallel Execution

• parallel_min_servers– Specifies the minimum number of px processes started for the instance

• parallel_max_servers– Specifies the maximum number of px processes started for the instance

• parallel_threads_per_cpu– Specifies the number of px processes per CPU—OS threads are already accounted for

in CPU_COUNT, so set to 1

• Parallel_degree_policy– Determines how the DoP is calculated

Configuration Parameters


Parallel Execution

• The PARALLEL_DEGREE_POLICY parameter controls how the DoP is chosen– MANUAL

• The default• Uses manual DoP rules

– AUTO, which enables• Auto DoP• In Memory Parallel Execution• Parallel Statement Queuing

– ADAPTIVE• The same as AUTO but also enables performance feedback to determine the DoP• New in 12c

– LIMITED• Just enables Auto DoP

– Only used when the table parallel decoration is set to DEFAULT

PARALLEL_DEGREE_POLICY Parameter


Parallel Execution

• The DoP is calculated based on table or system settings

– Uses the parallel decoration on the table

– If the table parallel decoration is set to “default” it uses the formula

CPU_COUNT * PARALLEL_THREADS_PER_CPU * # of instances

• Manual DoP– Facilitates using a consistent DoP across users, schemas, queries and tables if tables

have the same settings

– Also allows for inconsistent DoPs if tables and/or instances have different settings

Manual DoP


Parallel Execution

• First determines if the SQL statement will run serial or parallel– Uses the PARALLEL_MIN_TIME_THRESHOLD parameter

– Defaults to 10 seconds

– Defaults to 1 second for DBIM

– Needed for DBIM on RAC

• Automatically calculates the most “efficient” DoP for a SQL statement– Does not take system workload into account

– The DoP calculation is based primarily on expected IO prior to 12c

• Ignores the table parallel decoration

Auto DoP


Resource Management with Parallel Execution


Parallel Execution

• Resource Manager

– The Max DoP setting limits the DoP for a consumer group

• PARALLEL_DEGREE_LIMIT

– This parameter limits the DoP when using Auto DoP

Ways to limit the DoP


Parallel Execution

• parallel_adaptive_multi_user

– Reduces DoP based on system load

– Usually reduces DoP too much—recommend setting to FALSE

• Parallel statement queuing– Creates a FIFO queue for parallel statements

–Make SQL statements wait for px resources to become available before execution starts instead of allowing SQL statements to run with insufficient px resources

–When all of the parallel server processes in the pool are in use, statements queue

Ways to control system resources with parallel execution


Parallel Execution

• Parallel execution is used to reduce the execution time of queries

–Multiple processes work together to use more resources on the system, such as CPU and IO

• A simple configuration should be used to determine the DoP

– Coordinate parallel parameters

– Avoid using hints and alter session

• A resource management policy is needed when using parallel execution

– To keep the system under control

– To ensure SQL statements are able to execute in parallel

The Basics


• Available PX processes defined by the following parameters which are defined per instance

– parallel_min_servers=32

– parallel_max_servers=64

• By default, PX servers will be allocated for parallel SQL and if all PX servers are busy subsequent SQL executions will be downgraded

Oracle Confidential – Internal/Restricted/Highly Restricted 15

PX Workload with No Resource Management

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 16


Query StatusRequested DoP

PX Allocated

Execution DoP




PX Allocated

Execution DoP

A Running 8 16 8




PX Allocated

Execution DoP

A Running 8 16 8

B Running 12 24 12




PX Allocated

Execution DoP

A Running 8 16 8

B Running 12 24 12

C Running 8 16 8




PX Allocated

Execution DoP

A Running 8 16 8

B Running 12 24 12

C Running 8 16 8

D Running 12 8 4




PX Allocated

Execution DoP

A Running 8 16 8

B Running 12 24 12

C Running 8 16 8

D Running 12 8 4

E Running 32 0 1


• parallel_min_servers and parallel_max_servers still define the number of px servers available for execution

• parallel_servers_targetdefines the pool of px servers available for SQL statements in the queue

Oracle Confidential – Internal/Restricted/Highly Restricted 22

PX Workload with Resource Management


PX Workload with Parallel Statement Queuing


PX Allocated

Execution DoP

A 8




PX Allocated

Execution DoP

A Running 8 16 8




PX Allocated

Execution DoP

A Running 8 16 8

B Running 12 24 12




PX Allocated

Execution DoP

A Running 8 16 8

B Running 12 24 12

C Running 8 16 8




PX Allocated

Execution DoP

A Running 8 16 8

B Running 12 24 12

C Running 8 16 8

D Queued 12




PX Allocated

Execution DoP

A Finished 8 16 8

B Running 12 24 12

C Running 8 16 8

D Queued 12




PX Allocated

Execution DoP

A Finished 8 16 8

B Running 12 24 12

C Running 8 16 8

D Running 12 24 12




PX Allocated

Execution DoP

A Finished 8 16 8

B Running 12 24 12

C Running 8 16 8

D Running 12 24 12

E Queued 32




PX Allocated

Execution DoP

A Finished 8 16 8

B Finished 12 24 12

C Finished 8 16 8

D Finished 12 24 12

E Queued 32




PX Allocated

Execution DoP

A Finished 8 16 8

B Finished 12 24 12

C Finished 8 16 8

D Finished 12 24 12

E Running 32 64 32


Parallel Execution

• Parallel Statement Queuing– Can be enabled separately by setting _parallel_statement_queuing=true

– Can be used with Resource Manager to create multiple queues for different consumer groups

– Set PARALLEL_SERVERS_TARGET based on CPU resources on the system

Parallel Statement Queuing


Parallel Execution

• Implement a simple setup to understand what is happening in the system

• Base your plan/strategy on the amount of system resources you want to make available for parallel execution

• Use resource manager to specify the max DoP for consumer groups

• Set tables to the highest DoP that can be used in the resource manager plan

Recommendations

real-world performance training€¦ · •first determines if the sql statement will run serial or...

Documents