1 Parallelism Concepts Copyright 2007 Oracle All rights

Objectives After completing this lesson, you should be able to: • Identify the benefit

Introduction to Parallel Execution CPU scan CPU idle CPU scan Server without parallelism 1

System Conditions to Implement Parallelism • • 1 -4 SMP, MPP, clusters using RAC

Operations That Can Be Parallelized • Access methods: – Table scans, fast full index

Parallelization Rules • A SQL statement can be parallelized if: – It includes a

Enabling Parallel DML/DDL/QUERY The ALTER SESSION statement enables parallel mode: ALTER SESSION ENABLE DISABLE

Enabling Parallel DML/DDL/QUERY • You can use V$SESSION to look at session status: –

How Parallel Execution Works • • With serial execution, only one process is used.

The Granule • The basic unit of work in parallelism is called the granule.

Parallel Operations SELECT cust_last_name, cust_first_name FROM customers ORDER BY cust_last_name; Execution Servers Consumers SQL

Parallel Execution with Real Application Clusters Execution slaves have node affinity for execution coordinator

How Parallel Execution Servers Communicate Rows distribution: • PARTITION • HASH • RANGE •

Degree of Parallelism • • • 1 -14 Degree of parallelism (DOP) is the

Default Degree of Parallelism The default DOP: • Is used for a parallel operation

Parallel Execution Plan • • • For the same statement, a parallel plan generally

OTHER_TAG Column OTHER_TAG Interpretation SERIAL Serial execution SERIAL_FROM_REMOTE (S -> R) Serial execution at

Serial and Parallel Execution Plans SELECT cust_city, sum(amount_sold) FROM sales s, customers c WHERE

Parallel Plan Interpretation PX Coordinator PX Send QC SELECT /*+ PARALLEL */ cust_city, sum(amount_sold)

Parallel Plan Interpretation PX Coordinator PX Send QC Set 2 Hash Group By PX

Parallel Plan Interpretation PX Coordinator PX Send QC Set 2 Hash Group By Hash

Parallel Plan Interpretation PX Coordinator PX Send QC P->P(H) Set 2 Set 1 Hash

Parallel Plan Interpretation PX Coordinator PX Send QC P to S Set 1 :

Parallel Execution Server Pool • • A pool of servers are created at instance

Minimum Number of Parallel Execution Servers • • • 1 -25 In order for

Object’s PARALLEL Clause • • Can be specified for tables and indexes View degree

PARALLEL Clause: Examples CREATE INDEX ord_customer_ix ON orders (customer_id) NOLOGGING PARALLEL; ALTER TABLE customers

Using Parallelization Hints The following parallelization hints are used to override existing DOPs. •

Parallelization Hints SELECT /*+ FULL(s) ORDERED USE_HASH(c) PARALLEL(s) PARALLEL(c) PQ_DISTRIBUTE(c, NONE, BROADCAST) */ c.

Parallelization Hints • • PQ_DISTRIBUTE(<inner table>, <outer distribution>, <inner distribution>) Possible distribution combinations: –

Parallelism and Cost-Based Optimization Always analyze tables used in parallel by using: • DBMS_STATS

Summary In this lesson, you should have learned how to: • Benefit from using

2 Types of Parallel Operations Copyright © 2007, Oracle. All rights reserved.

Objectives After completing this lesson, you should be able to: • List the types

Parallelization Rules Revisited • A SQL statement can be parallelized if: – It includes

Enabling Parallel DML/DDL/QUERY • You can use V$SESSION to look at sessions status: –

Parallel Query The various query types that can be parallelized are: • Access methods:

Parallel Partitioned Table Scan by ROWID for partitioned tables. SELECT /*+ PARALLEL(SALES, 9)*/ *

Parallel Partitioned Index Scans can be performed by partition for partitioned indexes and tables.

Partitionwise Joins QS QS QS Partial partitionwise join Query slave 2 -42 QS Full

Non-Partitionwise Join: Example Consider the table: • SALES to be hash partitioned on PROD_ID

Partial Partitionwise Join: Example Consider the table: • SALES to be hash partitioned on

Full Partitionwise Join: Example • Consider the table: – SALES to be partitioned on

Full Partitionwise Join: Example • Consider the table: – SALES to be range partitioned

Partitionwise Join Compatibility • • Number of partitions should be a multiple of DOP.

Parallel DDL • The parallel DDL statements for nonpartitioned tables and indexes are: –

Space Management for Parallel DDL • Creating tables or indexes in parallel could lead

Fragmentation and Parallelism PQ 1 PQ 3 PQ 2 … HWM Internal Fragmentation PQ

Creating Indexes in Parallel Nonpartitioned index Object Parallel execution server Index piece Data 2

Creating Indexes in Parallel Global partitioned index Object Parallel execution server Index piece Data

Creating Indexes in Parallel Local partitioned index Object Parallel execution server Index piece Data

Parallel DDL: Example DDL statements are parallelized by specifying a PARALLEL clause: CREATE BITMAP

Parallel DML: Overview • Complement parallel query architecture by providing parallelization for: – –

When to Use Parallel DML Scenarios where parallel DML is used include: • Refreshing

Restrictions on Parallel DML • After a PDML statement modifies an object, it is

Performance Benefits of Parallel DML Can dramatically speed up DML transactions that can be

Automatic Parallelization of DML • • Multiple sessions are unnecessary. You do not need

Enabling Parallel DML • • You must enable PDML. The ALTER SESSION statement enables

Parallel DML: Example MERGE /*+ PARALLEL(c, 3) PARALLEL(d, 3) */ INTO customers c USING

Direct-Path Insert HWM HWM HWM … … Data 2 -63 Segment Parallel Execution Server

Enabling Direct-Path Insert • Supported for: – INSERT…SELECT version of the INSERT command –

Direct-Path Insert Logging • • Whether to log redo and undo during the insert

PDML and Undo Segments UPDATE /*+PARALLEL(sales, 4)*/ sales SET amount_sold=amount_sold*6. 55957; Coor PQ 1

Recovery for PDML • User-issued rollback: – Performed in parallel by coordinator and parallel

PDML Locking: Considerations • • Exclusive locks held on each impacted partition Increase value

Parallel Execution of Functions 2 -71 • A function must be declared with the

Summary In this lesson, you should have learned how to: • Identify the types

3 Monitoring and Tuning Parallel Operations Copyright © 2007, Oracle. All rights reserved.

Objectives After completing this lesson, you should be able to: • Set initialization parameters

Tuning Parameters for Parallel Execution • • The initial computed values of parallel execution

Using Default Parameter Settings Parameter Defaults Comments PARALLEL_ADAPTIVE_ MULTI_USER TRUE Throttles DOP requests to

Balancing the Workload • • • To optimize performance, all parallel execution servers should

Adaptive Multiuser and DOP • • The adaptive multiuser feature adjusts the DOP on

PX Message Pool • • • Parallel execution requires additional memory buffers used to

PX Message Pool Total PX Message Pool should be: Memory = 3*Size*Users*Groups*(DOP²+2*DOP) • •

Using V$PX_PROCESS_SYSTAT SELECT name, SUM(bytes) FROM V$SGASTAT WHERE pool = 'large pool' GROUP BY

Shared Pool • • 3 -83 If PX Message buffers are allocated in this

PGA Sizing • Use the automatic PGA memory management using the PGA_AGGREGATE_TARGET initialization parameter.

Resource Manager and the DOP Resource plan: NIGHT_PLAN 3 -85 Resource consumer group Allocation

Data Warehouse Scenario • • • Tables in third normal form, and star schema

Parameter Setting: Example • Manually – PARALLEL_ADAPTIVE_MULTI_USER=FALSE – PARALLEL_THREADS_PER_CPU=4 – SHARED_POOL_SIZE=20 M • Automatically

Are There Execution Problems? • Use V$PQ_TQSTAT to find out whethere is data distribution

Data Distribution and V$PQ_TQSTAT SELECT /*+PARALLEL*/ cust_city, sum(amount_sold) FROM sales s, customers c WHERE

Data Distribution and V$PQ_TQSTAT DFO_NUMBER TQ_ID SERVER_TYP PROCESS NUM_ROWS ---------- ----1 0 Consumer P

Using Other Dynamic Performance Views • General information: – V$FILESTAT – V$SESSTAT, V$SYSSTAT •

Using V$PX_SESSION SELECT qcsid, server_group "Group", server_set "Set", degree "Degree", req_degree "Req. Degree" FROM

Using V$PX_SESSTAT SELECT qcsid, server_group "Group", server_set "Set", name "Stat. Name", VALUE FROM V$PX_SESSTAT

Using V$PX_PROCESS SELECT * FROM V$PX_PROCESS; SERV ---P 002 P 003 P 004 P

Using V$SYSSTAT SELECT name, value FROM V$SYSSTAT WHERE UPPER(name) LIKE '%PARALLEL OPERATIONS%' OR UPPER(name)

Using V$SYSSTAT NAME ---------------------queries parallelized DML statements parallelized DDL statements parallelized DFO trees parallelized

Tuning PDML 3 -99 • • Using local striping for local indexes Using global

Summary In this lesson, you should have learned how to: • Set initialization parameters

Slides: 93

Download presentation

Objectives After completing this lesson, you should be able to: • Identify the benefit of using parallel operations • Describe system conditions under which to use parallel operations • Describe how parallel operations work • Control the parallel execution server pool • List operations that can be parallelized 1 -2 Copyright © 2007, Oracle. All rights reserved.

Operations That Can Be Parallelized • Access methods: – Table scans, fast full index scans – Partitioned index range scans – Various SQL operations • Joins: – Nested loop, sort merge – Hash, star transformation, partitionwise join • DDL statements: – CTAS, CREATE INDEX, REBUILD INDEX [PARTITION] – MOVE, SPLIT, COALESCE PARTITION • DML statements: – INSERT SELECT, UPDATE, DELETE, MERGE – SQL*Loader 1 -5 Copyright © 2007, Oracle. All rights reserved.

Parallelization Rules • A SQL statement can be parallelized if: – It includes a PARALLEL hint – Parallelization is forced using the ALTER SESSION FORCE command – The object operated on is or was declared with a PARALLEL clause (dictionary DOP greater than one) • Degree of parallelism (DOP) is determined by looking at referenced objects: – Parallel queries use the largest specified or dictionary DOP – Parallel DDL sets the DOP to the one specified by the PARALLEL clause 1 -6 Copyright © 2007, Oracle. All rights reserved.

Enabling Parallel DML/DDL/QUERY The ALTER SESSION statement enables parallel mode: ALTER SESSION ENABLE DISABLE FORCE PARALLEL n PARALLEL DML DDL QUERY • 1 -7 Used to override dictionary DOPs with FORCE Copyright © 2007, Oracle. All rights reserved.

Enabling Parallel DML/DDL/QUERY • You can use V$SESSION to look at session status: – PDML_STATUS – PDDL_STATUS – PQ_STATUS • Values for the columns listed above can be: – ENABLED – DISABLED – FORCED 1 -8 Copyright © 2007, Oracle. All rights reserved.

How Parallel Execution Works • • With serial execution, only one process is used. With parallel execution: – One parallel execution coordinator process is used – Many parallel execution servers are used – Table is dynamically divided into granules Serial process Coordinator process SELECT COUNT(*) FROM sales SALES 1 -9 Parallel execution servers Copyright © 2007, Oracle. All rights reserved.

The Granule • The basic unit of work in parallelism is called the granule. Type of granules: – Block range granules are dynamically generated at execution time. – Partition granules are statically determined by the number of partitions. • • • 1 -10 One granule is read per parallel execution server. Parallel execution servers progress from one granule to the next. The type of granule used is dependent on the kind of parallel operation being performed. Copyright © 2007, Oracle. All rights reserved.

Parallel Operations SELECT cust_last_name, cust_first_name FROM customers ORDER BY cust_last_name; Execution Servers Consumers SQL Data Producers sort A-K scan Dispatching results sort L-S scan Coordinator sort T-Z scan DOP=3 Intraparallelism Interparallelism 1 -11 Copyright © 2007, Oracle. All rights reserved. Table on disk Table’s dynamic partitioning (granules)

Parallel Execution with Real Application Clusters Execution slaves have node affinity for execution coordinator but will expand if needed. Node 1 Node 2 Node 3 Node 4 Execution coordinator Shared disks 1 -12 Copyright © 2007, Oracle. All rights reserved. Parallel execution server

How Parallel Execution Servers Communicate Rows distribution: • PARTITION • HASH • RANGE • ROUND-ROBIN • BROADCAST • QC(ORDER) • QC(RANDOM) QC Parallel Execution Server Set 1 Parallel Execution Server Set 2 DOP=3 1 -13 Copyright © 2007, Oracle. All rights reserved.

Degree of Parallelism • • • 1 -14 Degree of parallelism (DOP) is the number of parallel execution servers used by one parallel operation. DOP applies only to intraoperation parallelism. If interoperation parallelism is used, then the number of parallel execution servers can be twice the DOP. No more than two sets of parallel execution servers can be used for one parallelized statement. When using partition granules, use a relatively high number of partitions. Copyright © 2007, Oracle. All rights reserved.

Default Degree of Parallelism The default DOP: • Is used for a parallel operation that does not specify a DOP • Is dynamically calculated at run time • Depends on: – Total number of CPUs – PARALLEL_THREADS_PER_CPU • 1 -15 May be reduced depending on the availability of parallel execution servers or by the Oracle Resource Manager Copyright © 2007, Oracle. All rights reserved.

Parallel Execution Plan • • • For the same statement, a parallel plan generally differs from the corresponding serial plan. In order to generate the execution plan, use the EXPLAIN PLAN command, or execute the statement. In order to view the execution plan: – – • Columns of interest: – – 1 -16 Select directly from PLAN_TABLE Select directly from V$SQL_PLAN Run $ORACLE_HOME/rdbms/admin/utlxplp. sql Use the DBMS_XPLAN. DISPLAY table function OBJECT_NODE OTHER_TAG DISTRIBUTION OTHER (Prior to 10 g) Copyright © 2007, Oracle. All rights reserved.

OTHER_TAG Column OTHER_TAG Interpretation SERIAL Serial execution SERIAL_FROM_REMOTE (S -> R) Serial execution at a remote site PARALLEL_FROM_SERIAL (S -> P) Serial execution: Output partitioned or broadcast to PX PARALLEL_TO_PARALLEL (P -> P) Parallel execution: Output repartitioned to second set of PX PARALLEL_TO_SERIAL (P -> S) Parallel execution: Output returns to coordinator PARALLEL_COMBINED_WITH_ PARENT (PCWP) Parallel execution: Output used by the same PX in the next step PARALLEL_COMBINED_WITH_ CHILD (PCWC) Parallel execution: Input from previous step used by same PX 1 -17 Copyright © 2007, Oracle. All rights reserved.

Serial and Parallel Execution Plans SELECT cust_city, sum(amount_sold) FROM sales s, customers c WHERE s. cust_id=c. cust_id GROUP BY cust_city; HASH GROUP BY HASH JOIN TABLE ACCESS FULL CUSTOMERS PARTITION RANGE ALL TABLE ACCESS FULL SALES PX COORDINATOR PX SEND QC HASH GROUP BY PX RECEIVE PX SEND HASH GROUP BY HASH JOIN PX RECEIVE PX SEND PX BLOCK ITERATOR TABLE ACCESS FULL CUSTOMERS PX BLOCK ITERATOR TABLE ACCESS FULL SALES 1 -18 P->S QC(RANDOM) PCWP P->P HASH PCWP P->P (BROADCAST) PCWC PCWP Copyright © 2007, Oracle. All rights reserved.

Parallel Plan Interpretation PX Coordinator PX Send QC SELECT /*+ PARALLEL */ cust_city, sum(amount_sold) FROM sales s, customers c WHERE s. cust_id=c. cust_id GROUP BY cust_city; Hash Group By PX Receive Hash Join PX Receive PX Block Iterator PX Send Hash PX Send Broadcast Full Scan Sales Hash Group By PX Block Iterator Full Scan Customers 1 -19 Copyright © 2007, Oracle. All rights reserved.

Parallel Plan Interpretation PX Coordinator PX Send QC Set 2 Hash Group By PX Receive PX Send Hash Group By P->P(B) : TQ 10 PCWP Hash Join PX Receive Full Scan Sales Set 1 PX Send Broadcast PX Block Iterator Full Scan Customers 1 -20 PX Block Iterator Copyright © 2007, Oracle. All rights reserved.

Parallel Plan Interpretation PX Coordinator PX Send QC Set 2 Hash Group By Hash Join PX Block Iterator PX Receive Full Scan Sales PX Send Hash Group By 1 -21 Copyright © 2007, Oracle. All rights reserved.

Parallel Execution Server Pool • • A pool of servers are created at instance startup. Minimum pool size is determined by PARALLEL_MIN_SERVERS. • • Pool size can increase based on demand. Maximum pool size is determined by PARALLEL_MAX_SERVERS. • If a parallel execution server is idle for more than a threshold period of time, it is terminated. – Slaves specified in the minimum set are never terminated. 1 -24 Copyright © 2007, Oracle. All rights reserved.

Minimum Number of Parallel Execution Servers • • • 1 -25 In order for an operation to be parallelized, at least two parallel execution servers must be started. You can specify a minimum percentage of available parallel execution servers for one operation to be parallelized. PARALLEL_MIN_PERCENT specifies this minimum percentage. If this minimum percentage is not available, an error is reported (ORA-12827). Default value is 0. Copyright © 2007, Oracle. All rights reserved.

Object’s PARALLEL Clause • • Can be specified for tables and indexes View degree of parallelism in the DEGREE column of DBA_TABLES (dictionary DOP) Modified by using corresponding ALTER command Used to specify the DOP during object’s DDL – CREATE INDEX – CREATE TABLE … AS SELECT – Partition maintenance commands NOPARALLEL 1 -26 integer Copyright © 2007, Oracle. All rights reserved.

PARALLEL Clause: Examples CREATE INDEX ord_customer_ix ON orders (customer_id) NOLOGGING PARALLEL; ALTER TABLE customers PARALLEL 5; ALTER TABLE sales SPLIT PARTITION sales_q 4_2000 AT ('15 -NOV-2000') INTO (PARTITION sales_q 4_1, PARTITION sales_q 4_2) PARALLEL 2; 1 -27 Copyright © 2007, Oracle. All rights reserved.

Using Parallelization Hints The following parallelization hints are used to override existing DOPs. • PARALLEL (table_name, DOP_value) SELECT /*+PARALLEL(SALES, 9)*/ * FROM SALES; • • NOPARALLEL (table_name) PARALLEL_INDEX (table_name, index, DOP_value) SELECT /*+ PARALLEL_INDEX(c, ic, 3)*/ * FROM customers c WHERE cust_city = 'CLERMONT'; • 1 -28 NOPARALLEL_INDEX (table_name, index) Copyright © 2007, Oracle. All rights reserved.

Parallelization Hints SELECT /*+ FULL(s) ORDERED USE_HASH(c) PARALLEL(s) PARALLEL(c) PQ_DISTRIBUTE(c, NONE, BROADCAST) */ c. channel_desc, SUM(amount_sold) FROM sales s, channels c WHERE s. channel_id = c. channel_id GROUP BY c. channel_desc; SORT GROUP BY HASH JOIN PARTITION RANGE ALL TABLE ACCESS FULL SALES TABLE ACCESS FULL CHANNELS |P->S|QC (RANDOM) |P->P|HASH |PCWP| |P->P|BROADCAST ALTER SESSION SET PARALLEL_BROADCAST_ENABLED=TRUE 1 -29 Copyright © 2007, Oracle. All rights reserved.

Parallelization Hints • • PQ_DISTRIBUTE(<inner table>, <outer distribution>, <inner distribution>) Possible distribution combinations: – – – 1 -30 HASH, HASH NONE, BROADCAST, NONE PARTITION, NONE, PARTITION NONE, NONE Copyright © 2007, Oracle. All rights reserved.

Parallelism and Cost-Based Optimization Always analyze tables used in parallel by using: • DBMS_STATS package to gather statistics: – DEGREE parameter specifies DOP to be used If not specified, or set to NULL, the tables dictionary DOP is used. Use DBMS_STATS. DEFAULT_DEGREE to use default DOP. – Statistics on indexes are not gathered in parallel. – It is also recommended to gather system statistics. – New GATHER AUTO option • 1 -32 ANALYZE commands (always run serially) Copyright © 2007, Oracle. All rights reserved.

Summary In this lesson, you should have learned how to: • Benefit from using parallel operations • Validate system conditions under which to use parallel operations • Control the parallel execution server pool • Read a parallel execution plan 1 -33 Copyright © 2007, Oracle. All rights reserved.

Objectives After completing this lesson, you should be able to: • List the types of parallel operations • Describe parallel query, parallel DDL, and parallel DML • Determine when partitionwise join is used • Describe locking behavior for parallel DML operations • Explain parallelism and undo • Discuss parallel execution of functions 2 -35 Copyright © 2007, Oracle. All rights reserved.

Parallelization Rules Revisited • A SQL statement can be parallelized if: – It includes a parallel hint – Parallelization is forced using the ALTER SESSION FORCE command – The object operated on is/was declared with a parallel clause (dictionary DOP greater than one) – Not prevented from doing so by the Resource Manager • DOP is determined by looking at referenced objects: – Parallel queries use the largest specified or dictionary DOP. – Parallel DML sets the DOP to the number of partitions of the manipulated object. – Parallel DDL sets the DOP to the one specified by the PARALLEL clause. 2 -36 Copyright © 2007, Oracle. All rights reserved.

Enabling Parallel DML/DDL/QUERY The ALTER SESSION statement enables parallel mode: ALTER SESSION ENABLE DISABLE FORCE PARALLEL n PARALLEL DML DDL QUERY • • 2 -37 Used to override dictionary DOPs with FORCE QUERY only starting with Oracle 8 i R 2 Copyright © 2007, Oracle. All rights reserved.

Enabling Parallel DML/DDL/QUERY • You can use V$SESSION to look at sessions status: – PDML_STATUS – PDDL_STATUS – PQ_STATUS • Values for the columns listed above can be: – ENABLED – DISABLED – FORCED 2 -38 Copyright © 2007, Oracle. All rights reserved.

Parallel Query The various query types that can be parallelized are: • Access methods: – Table scans, index full scans – Partitioned index range scans – Various SQL operations: GROUP BY, ORDER BY, NOT IN, EXISTS, IN, SELECT DISTINCT, UNION ALL, MINUS, INTERSECT, CUBE, ROLLUP, aggregates • Join methods: – Nested loop, sort merge – Hash, star transformation, partitionwise join 2 -39 Copyright © 2007, Oracle. All rights reserved.

Parallel Partitioned Table Scan by ROWID for partitioned tables. SELECT /*+ PARALLEL(SALES, 9)*/ * FROM SALES; PQ 4 PQ 6 PQ 3 PQ 7 PQ 2 PQ 8 PQ 1 2 -40 PQ 5 SALES Copyright © 2007, Oracle. All rights reserved. PQ 9

Parallel Partitioned Index Scans can be performed by partition for partitioned indexes and tables. The hint must contain the table name, index name, and degree of parallelism. SELECT /*+ PARALLEL_INDEX(c, ic, 3)*/ * FROM customers c WHERE cust_city = 'MARSEILLE'; PQ 1 Nonprefixed index IC Indexed column cust_city PQ 2 PQ 3 2 -41 Copyright © 2007, Oracle. All rights reserved.

Non-Partitionwise Join: Example Consider the table: • SALES to be hash partitioned on PROD_ID • TIMES to be hash partitioned on CALENDAR_YEAR SELECT * FROM sales s, times t WHERE s. time_id = t. time_id; HASH JOIN PARTITION HASH (ALL)[1 --> m] TABLE ACCESS FULL (s) PARTITION HASH (ALL)[1 --> n] TABLE ACCESS FULL (t) 2 -43 Copyright © 2007, Oracle. All rights reserved. P->S PCWP P->P QC

Partial Partitionwise Join: Example Consider the table: • SALES to be hash partitioned on TIME_ID • TIMES to be hash partitioned on CALENDAR_YEAR SELECT * FROM sales s, times t WHERE s. time_id = t. time_id; HASH JOIN PARTITION HASH (ALL)[1 --> m] TABLE ACCESS FULL (s) PARTITION HASH (ALL)[1 --> n] TABLE ACCESS FULL (t) 2 -44 Copyright © 2007, Oracle. All rights reserved. P->S PCWP P->P QC

Full Partitionwise Join: Example • Consider the table: – SALES to be partitioned on TIME_ID – TIMES to be partitioned on TIME_ID • Both tables are equipartitioned. SELECT * FROM sales s, times t WHERE s. time_id = t. time_id; HASH JOIN PARTITION HASH (ALL)[1 --> m] TABLE ACCESS FULL (s) PARTITION HASH (ALL)[1 --> n] TABLE ACCESS FULL (t) 2 -45 Copyright © 2007, Oracle. All rights reserved. P->S PCWP QC

Full Partitionwise Join: Example • Consider the table: – SALES to be range partitioned on PROD_ID and to be subpartitioned by hash on TIME_ID – TIMES to be hash partitioned on TIME_ID • Both tables are equipartitioned on hash dimension. SELECT * FROM sales s, times t WHERE s. time_id = t. time_id; HASH JOIN PARTITION RANGE (ALL)[1 --> n] PARTITION HASH (ALL)[1 --> m] TABLE ACCESS FULL (s) PARTITION HASH (ALL)[1 --> m] TABLE ACCESS FULL (t) 2 -46 Copyright © 2007, Oracle. All rights reserved. P->S PCWP PCWP QC

Partitionwise Join Compatibility • • Number of partitions should be a multiple of DOP. Partitions should be of equal size. Use preferably: – Hash partitioning or composite partitioning – Number of hash partitions as a power of two • Possible cases for full partitionwise join: R/S Range Hash List Composite Range N/A Range Hash N/A Hash List N/A List Hash List Range, hash, or list Composite Range 2 -47 Copyright © 2007, Oracle. All rights reserved.

Parallel DDL • The parallel DDL statements for nonpartitioned tables and indexes are: – CREATE INDEX – CREATE TABLE. . . AS SELECT – ALTER INDEX. . . REBUILD • The parallel DDL statements for partitioned tables and indexes are: – – – – 2 -48 CREATE INDEX CREATE TABLE. . . AS SELECT ALTER TABLE. . . MOVE PARTITION ALTER TABLE. . . SPLIT PARTITION ALTER TABLE. . . COALESCE PARTITION ALTER INDEX. . . REBUILD PARTITION ALTER INDEX. . . SPLIT PARTITION Copyright © 2007, Oracle. All rights reserved.

Space Management for Parallel DDL • Creating tables or indexes in parallel could lead to: – External fragmentation – Internal fragmentation • • • Each parallel execution server creates its own temporary segment using specified storage attributes. Internal fragmentation will almost always happen. External fragmentation should be taken care of only for dictionary-managed tablespaces: – Temporary segments might be trimmed. – Use MINIMUM EXTENT at tablespace level. 2 -49 Copyright © 2007, Oracle. All rights reserved.

Fragmentation and Parallelism PQ 1 PQ 3 PQ 2 … HWM Internal Fragmentation PQ 4 … HWM External Fragmentation HWM … Data 2 -50 … … Unused portion below HWM Unused portion above HWM Copyright © 2007, Oracle. All rights reserved. Free space

Parallel DDL: Example DDL statements are parallelized by specifying a PARALLEL clause: CREATE BITMAP INDEX fk_sales_prod ON sales(prod_id) PARALLEL 16 LOCAL NOLOGGING; 2 -54 Copyright © 2007, Oracle. All rights reserved.

Parallel DML: Overview • Complement parallel query architecture by providing parallelization for: – – • 2 -55 INSERT UPDATE DELETE MERGE Useful when changing big tables Copyright © 2007, Oracle. All rights reserved.

When to Use Parallel DML Scenarios where parallel DML is used include: • Refreshing large tables in a data warehouse • Creating intermediate summary tables • Using scoring tables • Updating historical tables • Running batch jobs 2 -56 Copyright © 2007, Oracle. All rights reserved.

Restrictions on Parallel DML • After a PDML statement modifies an object, it is no longer possible to query or modify this object in the same transaction (ORA-12838). • • • Limited integrity constraint support is provided. Clustered tables are not supported. PDML is not allowed: – On tables with enabled triggers – On remote objects – When the operation is part of a distributed transaction 2 -58 Copyright © 2007, Oracle. All rights reserved.

Performance Benefits of Parallel DML Can dramatically speed up DML transactions that can be parallelized Serial update: … UPDATE sales SET amount_sold=amount_sold*6. 55957; … Parallel update: alter session enable parallel DML; UPDATE /*+PARALLEL(sales, 12)*/ sales SET amount_sold=amount_sold*6. 55957; 2 -59 Copyright © 2007, Oracle. All rights reserved. … …

Automatic Parallelization of DML • • Multiple sessions are unnecessary. You do not need to know the key value or ROWID ranges to divide the level of work. • Statements do not need to be manually coordinated. Session 1 UPDATE sales SET amount_sold=amount_sold*6. 55957 WHERE time_id > '30 -JAN-2002'; Session 2 UPDATE sales SET amount_sold=amount_sold*6. 55957 WHERE time_id < '30 -JAN-2002'; 2 -60 Copyright © 2007, Oracle. All rights reserved.

Enabling Parallel DML • • You must enable PDML. The ALTER SESSION statement enables parallel DML mode: ALTER SESSION ENABLE DISABLE ALTER SESSION FORCE • • 2 -61 PARALLEL DML PARALLEL n Parallel queries are still parallelized, even if parallel DML is disabled. The default mode of a session is DISABLE PARALLEL DML. Copyright © 2007, Oracle. All rights reserved.

Parallel DML: Example MERGE /*+ PARALLEL(c, 3) PARALLEL(d, 3) */ INTO customers c USING diff_customers d ON (d. cust_id = c. cust_id) WHEN MATCHED THEN UPDATE SET c. cust_last_name = d. cust_last_name, c. cust_city = d. cust_city WHEN NOT MATCHED THEN INSERT (c. cust_id, c. cust_last_name) VALUES (d. cust_id, d. cust_last_name); 2 -62 Copyright © 2007, Oracle. All rights reserved.

Enabling Direct-Path Insert • Supported for: – INSERT…SELECT version of the INSERT command – MERGE command • • Used by default when PDML mode is enabled In serial mode, use the APPEND hint: – Right after the INSERT keyword – Right after the SELECT keyword – Right after the MERGE keyword 2 -64 Copyright © 2007, Oracle. All rights reserved.

Direct-Path Insert Logging • • Whether to log redo and undo during the insert operation on a table or index Object attribute: – LOGGING – NOLOGGING • Specified at creation or alteration time of: – Object itself – Tablespace in which the object resides 2 -65 Copyright © 2007, Oracle. All rights reserved.

Recovery for PDML • User-issued rollback: – Performed in parallel by coordinator and parallel execution servers • Process recovery: – PMON roll backs work done by dead process – Others roll back their work • System recovery: – SMON coordinates the dead transactions – Fast-start fault recovery is used if enabled Fast-Start On-Demand Rollback Fast-Start Parallel Rollback FAST_START_PARALLEL_ROLLBACK=HIGH 2 -67 Copyright © 2007, Oracle. All rights reserved.

PDML Locking: Considerations • • Exclusive locks held on each impacted partition Increase value of the following parameters: – DML_LOCKS – ENQUEUE_RESOURCES • Example: Table with 600 partitions, and parallel DELETE with DOP of 100 involving all partitions: – Coordinator: 1 table lock SX, 600 partition locks X – Each slave: 1 table lock SX, 1 partition lock NULL and 1 partition-wait lock S per owned partition • 2 -69 Concurrent queries use the same behavior with normal DML operations except for the session executing the PDML operation. Copyright © 2007, Oracle. All rights reserved.

Parallel Execution of Functions 2 -71 • A function must be declared with the PARALLEL_ENABLE keyword. • If the function is declared in a package, then it must be declared with: PRAGMA RESTRICT_REFERENCES with at least WNDS, WNPS, RNPS [, RNDS]. • Any variable is private to each parallel execution server. Copyright © 2007, Oracle. All rights reserved.

Summary In this lesson, you should have learned how to: • Identify the types of parallel operations • Explain parallel query, parallel DDL, and parallel DML • Determine when partitionwise join is used • Describe locking behavior for parallel DML operations • Describe parallelism and undo • Discuss parallel execution of functions 2 -72 Copyright © 2007, Oracle. All rights reserved.

Objectives After completing this lesson, you should be able to: • Set initialization parameters related to parallel operations tuning • Query dynamic performance views related to parallel operations tuning • Determine your memory requirement needs • Tune PDML operations 3 -74 Copyright © 2007, Oracle. All rights reserved.

Tuning Parameters for Parallel Execution • • The initial computed values of parallel execution parameters should be acceptable in most cases. They are based on the values of CPU_COUNT and PARALLEL_THREADS_PER_CPU at database startup. Oracle Corporation recommends that you use the default settings. Manual tuning of parallel execution is more complex than using default settings. – Manual parallel execution tuning requires more attentive administration than automated tuning. – Manual tuning is prone to user-load and system resource miscalculations. 3 -75 Copyright © 2007, Oracle. All rights reserved.

Using Default Parameter Settings Parameter Defaults Comments PARALLEL_ADAPTIVE_ MULTI_USER TRUE Throttles DOP requests to prevent system overload PARALLEL_MAX_SERVERS CPU_COUNT x Maximizes the number of processes used by parallel execution PARALLEL_EXECUTION_ MESSAGE_SIZE Increasing 4 K to 8 K improves parallel performance if SGA is large enough PARALLEL_THREADS_ PER_CPU x (1; 2 if PGA_AGGREGATE_TARGET > 0) x 5 3 -76 2 KB (port specific) Copyright © 2007, Oracle. All rights reserved.

Balancing the Workload • • • To optimize performance, all parallel execution servers should have equal workloads. For parallelization by block range or parallel execution servers, the workload is dynamically divided among the parallel execution servers. By choosing an appropriate DOP, you can: – Minimize workload skew – Optimize performance 3 -77 Copyright © 2007, Oracle. All rights reserved.

Adaptive Multiuser and DOP • • The adaptive multiuser feature adjusts the DOP on the basis of user load. PARALLEL_ADAPTIVE_MULTI_USER set to: – TRUE improves performance in a multiuser environment (default) – FALSE is used for batch processing • PARALLEL_AUTOMATIC_TUNING has been deprecated in Oracle Database 10 g. – Kept for backward compatibility only 3 -79 Copyright © 2007, Oracle. All rights reserved.

PX Message Pool • • • Parallel execution requires additional memory buffers used to communicate between slaves. No recommended size for those buffers Let Oracle size these buffers automatically by: – Setting PARALLEL_AUTOMATIC_TUNING to TRUE – Buffers allocated in Large Pool instead of Shared Pool • 3 -80 In case of ORA-4031, raise your Large Pool. Copyright © 2007, Oracle. All rights reserved.

PX Message Pool Total PX Message Pool should be: Memory = 3*Size*Users*Groups*(DOP²+2*DOP) • • • 3 -81 Size = PARALLEL_EXECUTION_MESSAGE_SIZE Users = # parallel concurrent users running with optimal DOP Groups = # parallel execution server groups per statement Copyright © 2007, Oracle. All rights reserved.

Using V$PX_PROCESS_SYSTAT SELECT name, SUM(bytes) FROM V$SGASTAT WHERE pool = 'large pool' GROUP BY ROLLUP (name); NAME SUM(BYTES) ------PX msg pool 38092812 free memory 299988 38392800 SELECT * FROM V$PX_BUFFER_ADVICE WHERE RTRIM(statistic) = 'Buffers HWM'; STATISTIC VALUE ----------Buffers HWM 3620 3 -82 Copyright © 2007, Oracle. All rights reserved.

Shared Pool • • 3 -83 If PX Message buffers are allocated in this Pool, you should use the previous formula to accommodate the Shared Pool. Parallel execution plans consume more space than serial plans. More cursors are used in parallel mode. Monitor recompilation hit ratio in V$SQLAREA. Copyright © 2007, Oracle. All rights reserved.

PGA Sizing • Use the automatic PGA memory management using the PGA_AGGREGATE_TARGET initialization parameter. – Available only for dedicated server mode – Set to at least: (Total System Memory*80%)*50% • If using WORKAREA_SIZE_POLICY set to MANUAL: – HASH_AREA_SIZE >= MAX(SQRT(S)/2, 1 M)/#PES S being the smallest table input in megabytes #PES being the number of concurrent parallel execution servers executing hash joins – SORT_AREA_SIZE should range between 256 KB and 4 MB 3 -84 Copyright © 2007, Oracle. All rights reserved.

Resource Manager and the DOP Resource plan: NIGHT_PLAN 3 -85 Resource consumer group Allocation method parameters OLTP CPU = 25% Max degree of parallelism = 2 DSS CPU = 75% Max degree of parallelism = 20 Copyright © 2007, Oracle. All rights reserved.

Data Warehouse Scenario • • • Tables in third normal form, and star schema and summary tables Ad hoc queries and high volume processing to convert a repository into the star schema and the summary tables System characteristics: – – 3 -87 CPUs: 8 Memory : 2 GB Users: 40 Disk: 80 GB Copyright © 2007, Oracle. All rights reserved.

Parameter Setting: Example • Manually – PARALLEL_ADAPTIVE_MULTI_USER=FALSE – PARALLEL_THREADS_PER_CPU=4 – SHARED_POOL_SIZE=20 M • Automatically – – 3 -88 PROCESSES=123, SESSIONS=140 PARALLEL_MAX_SERVERS=80 LARGE_POOL_SIZE=78 MB PARALLEL_EXECUTION_MESSAGE_SIZE= 4096 Copyright © 2007, Oracle. All rights reserved.

Are There Execution Problems? • Use V$PQ_TQSTAT to find out whethere is data distribution skew or bad object statistics: – Contains traffic statistics between slaves at table queue level – Valid only when queried from the parallel session • • 3 -89 Check I/O and CPU bound at the operating system level and decide on the parallelism. Check for contention. Copyright © 2007, Oracle. All rights reserved.

Data Distribution and V$PQ_TQSTAT SELECT /*+PARALLEL*/ cust_city, sum(amount_sold) FROM sales s, customers c WHERE s. cust_id=c. cust_id GROUP BY cust_city; . . . PX SEND QC (RANDOM) HASH GROUP BY PX RECEIVE PX SEND HASH GROUP BY HASH JOIN PX RECEIVE PX SEND BROADCAST PX BLOCK ITERATOR TABLE ACCESS FULL 3 -90 : TQ 10002 P->S PCWP : TQ 10001 P->P PCWP : TQ 10000 P->P PCWC CUSTOMERS PCWP PCWC SALES PCWP Copyright © 2007, Oracle. All rights reserved. QC (RAND) HASH BROADCAST

Data Distribution and V$PQ_TQSTAT DFO_NUMBER TQ_ID SERVER_TYP PROCESS NUM_ROWS ---------- ----1 0 Consumer P 002 55500 1 0 Consumer P 003 55500 1 0 Producer P 000 55310 1 0 Producer P 001 55690 1 1 Consumer P 000 613 1 1 Consumer P 001 589 1 1 Producer P 002 602 1 1 Producer P 003 600 1 2 Consumer QC 608 1 2 Producer P 000 309 1 2 Producer P 001 299 3 -91 Copyright © 2007, Oracle. All rights reserved.

Using Other Dynamic Performance Views • General information: – V$FILESTAT – V$SESSTAT, V$SYSSTAT • Information about parallel execution: – – – – • 3 -92 V$PX_SESSION V$PX_PROCESS_SYSSTAT V$PX_SESSTAT V$PQ_SLAVE V$PX_BUFFER_ADVICE TIMED_STATISTICS should be set to TRUE. Copyright © 2007, Oracle. All rights reserved.

Using V$PX_SESSION SELECT qcsid, server_group "Group", server_set "Set", degree "Degree", req_degree "Req. Degree" FROM V$PX_SESSION ORDER BY 1, 3, 4; QCSID Group Set Degree Req. Degree ----- --- ---------9 9 9 7 1 1 2 2 9 21 1 1 2 2 9 18 1 2 2 2 9 20 1 2 2 2 3 -94 Copyright © 2007, Oracle. All rights reserved.

Using V$PX_SESSTAT SELECT qcsid, server_group "Group", server_set "Set", name "Stat. Name", VALUE FROM V$PX_SESSTAT A, V$STATNAME B WHERE A. statistic# = B. statistic# AND name = 'physical reads' AND value > 0 ORDER BY 1, 3, 4; QCSID Group Set Stat. Name VALUE ----- --- -------9 9 physical reads 3863 9 7 1 1 physical reads 2 9 21 1 1 physical reads 2 9 18 1 2 physical reads 2 9 20 1 2 physical reads 2 3 -95 Copyright © 2007, Oracle. All rights reserved.

Using V$PX_PROCESS SELECT * FROM V$PX_PROCESS; SERV ---P 002 P 003 P 004 P 005 P 000 P 001 3 -96 STATUS PID SID SERIAL --------- -----IN USE 16 16955 21 7729 IN USE 17 16957 20 2921 AVAILABLE 18 16959 AVAILABLE 19 16962 IN USE 12 6999 18 4720 IN USE 13 7004 7 234 Copyright © 2007, Oracle. All rights reserved.

Using V$SYSSTAT SELECT name, value FROM V$SYSSTAT WHERE UPPER(name) LIKE '%PARALLEL OPERATIONS%' OR UPPER(name) LIKE '%PARALLELIZED%' OR UPPER(name) LIKE '%PX%'; 3 -97 Copyright © 2007, Oracle. All rights reserved.

Using V$SYSSTAT NAME ---------------------queries parallelized DML statements parallelized DDL statements parallelized DFO trees parallelized Parallel operations not downgraded Parallel operations downgraded to serial Parallel operations downgraded 75 to 99 pct Parallel operations downgraded 50 to 75 pct Parallel operations downgraded 25 to 50 pct Parallel operations downgraded 1 to 25 pct PX local messages sent PX local messages recv'd PX remote messages sent PX remote messages recv'd 3 -98 Copyright © 2007, Oracle. All rights reserved. VALUE ----347 0 0 463 28 31 252 128 43 12 74548 74128 0 0

Tuning PDML 3 -99 • • Using local striping for local indexes Using global striping for global indexes Increasing INITRANS for global indexes Using NOLOGGING • • Using multiple archivers Using multiple DBWRs or I/O slaves Copyright © 2007, Oracle. All rights reserved.

Summary In this lesson, you should have learned how to: • Set initialization parameters related to parallel operations tuning • Query dynamic performance views related to parallel operations tuning • Determine your memory requirement needs • Tune PDML operations 3 -100 Copyright © 2007, Oracle. All rights reserved.