Oracle_Parallel.rar_oracle资源-CSDN文库

共2个文件

pdf：2个

版权申诉

oracle

147 浏览量 2022-09-14 20:17:57 上传评论收藏 686KB RAR 举报

资源详情

资源评论

资源推荐

收起资源包目录

Oracle_Parallel.rar （2个子文件）

Oracle_Parallel_SQL并行处理2_执行计划分析与问题跟踪.pdf 499KB

Oracle_Parallel_SQL并行处理1_深入原理介绍.pdf 238KB

Oracle Parallel SQL

(Part 2)

Extracted from the book “Oracle Performance

Survival Guide” by Guy Harrison

Parallel SQL allows a SQL statement to be processed by multiple threads or processes simultaneously.

Today’s widespread usage of dual and quad core processors means that even the humblest of modern computers

running an Oracle database will contain more than one CPU. While desktop and laptop computers might have only a

single disk device, database server systems typically have database files spread – striped – across multiple

independent disk devices. Without parallel technology – when an SQL statement is processed in serial – a session

can only make use of one of these CPUs or disk devices at a time. Parallel processing can improve the performance

of suitable SQL statements to a degree which is often not possible by any other method. Parallel processing is

available in Oracle Enterprise Edition only.

This is the second of a three part series in which we’ll look at how Oracle can parallelize SQL statements and how

you can use this facility to improve the performance of individual SQLs or the application as a whole. In

the first

installment, we looked at Oracle parallel processing architecture and configuration. In this installment, we’ll look at

how to monitor and measure parallel query activity.

Parallel explain plans ..................................................................................................................................................... 2

Tracing ........................................................................................................................................................................... 4

The V$PQ_TQSTAT view .............................................................................................................................................. 5

Monitoring the parallel Execution Queue ....................................................................................................................... 9

Other statistics ............................................................................................................................................................. 11

PQO Monitoring in Toad .............................................................................................................................................. 12

Summary ...................................................................................................................................................................... 13

Parallel explain plans

EXPLAIN PLAN will reflect additional steps for a parallelized SQL statement which reflect the additional parallel

operations involved in the parallel execution.

For instance, consider this simple SQL statement and explain plan:

SQL> EXPLAIN PLAN FOR

2 SELECT * FROM customers

3 ORDER BY cust_last_name;

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | SORT ORDER BY | |

| 2 | TABLE ACCESS FULL| CUSTOMERS |

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

The CUSTOMERS table is scanned, and the rows scanned are sorted.

When the statement is parallelized, additional operations are added to the execution plan:

SQL> EXPLAIN PLAN FOR

2 SELECT /*+ parallel */ *

3 FROM customers

4 ORDER BY cust_last_name;

SQL> SELECT * FROM table (DBMS_XPLAN.display

2 (null,null,'BASIC +PARALLEL'));

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

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

| 0 | SELECT STATEMENT | | | | |

| 1 | PX COORDINATOR | | | | |

| 2 | PX SEND QC (ORDER) | :TQ10001 | Q1,01 | P->S | QC (ORDER) |

| 3 | SORT ORDER BY | | Q1,01 | PCWP | |

| 4 | PX RECEIVE | | Q1,01 | PCWP | |

| 5 | PX SEND RANGE | :TQ10000 | Q1,00 | P->P | RANGE |

| 6 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |

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

The new plan contains a variety of ‘PX’ steps which describe the parallel operations involved. Let’s look at each of

these steps:

PX BLOCK ITERATOR

This operation is typically the first step in a parallel pipeline. The BLOCK

ITERATOR breaks up the table into chunks which are processed by each of the

parallel servers involved.

PX SEND

PX SEND operations simply indicate that data is being sent from one parallel

process to another.

PX RECEIVE

PX RECEIVE operations indicate the data being received by one parallel

process from another.

PX SEND QC

a send operation to the parallel query co-coordinator process.

PX COORDINATOR

This step simply indicates that the Parallel query co-coordinator is receiving the

data from the parallel streams and returning it to the SQL statement.

Figure 1 illustrates how these steps relate to parallel processing with a Degree of Parallelism of 2.

CUSTOMER

TABLE

Parallel

Process #1

Parallel

Process #1

Scan

Parallel

Process #3

Parallel

Process #4

Order By

Query Co-

ordinator

PX BLOCK

ITERATOR

SEND

RECEIVE

PX SEND

COORDINATOR

SELECT * FROM customers ORDER BY cust_last_name;

IGURE 1 ILLUSTRATION OF EXPLAIN PLAN PARALLEL EXECUTION STEPS

PX SEND and PX RECEIVE operations are associated with distribution options – shown in the “PQ Distrib” column of

DBMS_XPLAN - which describe how data is sent from one slave to another. In sort operations it’s typical to see the

RANGE option, since rows to be sorted are distributed based on the value of the sort columns. For instance when

sorting by CUST_FIRST_NAME as in the query above, Oracle might send names from A-K to one slave and names

from L-Z to the other. Here are some of the other distribution options:

RANGE

Rows are distributed based on ranges of values. This is typical when sort operations

are parallelized.

HASH

Rows are distributed to parallel query slaves based on a hash of the value concerned.

This is suitable for joins and HASH GROUP BY operations, and generally ensures a

more even distribution of rows than for RANGE operations.

RANDOM

Rows are randomly assigned to parallel query slaves.

ROUND ROBIN

Rows are distributed one at a time in a circular fashion, just as you would deal cards in

a game of poker.

The IN-OUT column of the DBMS_XPLAN output describes how data flows between and within the parallel

processes. The column corresponds to the OTHER_TAG column in the PLAN_TABLE table. These columns can

contain one of the following values:

IN-OUT

value

OTHER_TAG value Description

P->P PARALLEL_TO_PARALLEL

This tag denotes parallel processing that

passes results to a second set of parallel

processes. For instance, a parallel table scan

may have passed results to a parallel sort.

P->S PARALLEL_TO_SERIAL This is usually the top level of a parallel query.

The results are fed in parallel to the query

coordinator.

PCWP

PCWC

PARALLEL_COMBINED_WITH_PARENT

PARALLEL_COMBINED_WITH_CHILD

The step was executed in parallel. Either the

parent step or the child step was also executed

in parallel by the same process. For instance,

in a parallel nested loops join, the parallel

query process scanned the driving table and

also issued index lookups on the joined table.

S->P PARALLEL_FROM_SERIAL

A serial operation which passed results to a

set of parallel processes. The presence of this

tag may indicate a serial bottleneck within a

parallel statement, since it suggests that

parallel processing might wait on serial

processing.

The presence of a PARALLEL_FROM_SERIAL or S->P tag in the PLAN_TABLE or DBMS_XPLAN output

may represent a serial bottleneck in an otherwise parallel execution stream.

Tracing

Using SQL trace to tune our queries becomes somewhat more difficult when the SQL is parallelized. This is because

each process involved in the parallel execution has its own trace file. Furthermore, since these processes are

shared amongst all parallelized SQLs and sessions, the trace files will contain trace data for other SQLs and

sessions in addition to the one we are interested in.

However, it is possible – through a somewhat convoluted process – to trace parallel execution. Here are the steps:

1. Set a unique client identifier in your session using DBMS_SESSION.SET_IDENTIFIER

2. Enable tracing for that client identifier using DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE.

3. Run your parallel SQL

4. Use the trcsess utility to create a new trace file which contains only trace entries for you client identifier

5. Analyze the new trace file as usual.

Here we invoke steps 1,2 and 3:

BEGIN

DBMS_SESSION.set_identifier ('gh pqo test 27');

DBMS_MONITOR.client_id_trace_enable

(client_id => 'gh pqo test 27',

waits => TRUE);

END;

SELECT /*+ parallel */ prod_name, SUM (amount_sold)

FROM products JOIN sales

USING (prod_id)

GROUP BY prod_name

ORDER BY 2 DESC;

Here we perform steps 4 and 5:

评论收藏

内容反馈

版权申诉

钱亚锋

粉丝: 86
资源: 1万+

Oracle_Parallel.rar_oracle

评论0

最新资源

Oracle_Parallel.rar_oracle

评论0

Intel_Parallel_Studio_XE_Cluster_Edition_2018_Update_4_Linux.part5_Downloadly.ir.rar

Intel_Parallel_Studio_XE_Cluster_Edition_2019_Update_3.part4_Downloadly.ir.rar

Intel_Parallel_Studio_XE_2019_Update_3_macOS.part3_Downloadly.ir.rar

Intel_Parallel_Studio_XE_Cluster_Edition_2018_Update_4_Windows.part4_Downloadly.ir.rar

Xilinx_parallel_cable_jtag.rar

Intel_Parallel_Studio_XE_All_Licenses_Downloadly.ir.rar

opencv_videoio_ffmpeg_64.dll

test_parallel_matching.py

parallel-studio-a.lic

主动串行存储器ug_altasmi_parallel中文版

5_data_parallel_tutorial.ipynb

IDMAparallel.rar_idmaparallel.rar_interleave matlab_parallel int

main_c.rar_parallel C code_parallel fft

Parallel_IO.rar_Parallel_IO_parallel_并口_并口输入输出

SEIG-IN-PARALLEL.rar_Seig_seig matlab_seig.rar_simiulink

Fir_filter_parallel.rar

inverterLCL_double.rar_control inverter _parallel inverter_单相有源_

thread_parallel.rar

lirc_parallel.rar_The Signal

冰河的渗透实战笔记-冰河.pdf

大灰狼远控2021最新版，解压密码222

J-LINK V10 V11固件.rar

ISO21434.pdf

Web安全漏洞扫描工具-AWVS14

CTF 竞赛入门指南（ctf-all-in-one）.pdf

Web中间件常见漏洞总结.pdf

stm32f103 adc采样+dma传输+fft处理 频率计_fft处理_stm32_ADCFFT_频率计_ADC采样_

jts-1.14.zip

CobaltStrike4.4.zip

最新资源

stm32f103 adc采样+dma传输+fft处理频率计_fft处理_stm32_ADCFFT_频率计_ADC采样_