wishing on a star
Oracle Parallel SQL Array Part 1
Extracted from the book “Oracle Performance
四级核心词汇Survival Guide” by Guy Harrison
Parallel SQL allows a SQL statement to be procesd by multiple threads or process simultaneously.
Today’s widespread usage of dual and quad core processors means that even the humblest of modern computers running an Oracle databa will contain more than one CPU. While desktop and laptop computers might have only a single disk device, databa rver systems typically have databa files spread – striped – across multiple independent disk devices. Without parallel technology – when an SQL statement is procesd in rial – a ssion can only make u of one of the 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 Enterpri Edition only.
2015考研数学
This is first of a ries of articles in which we’ll look at how Oracle can parallelize SQL statements and how you can u this facility to improve the performance of individual SQLs or the application as a whole.
Understanding parallel SQL (2)
Parallel performance gains (5)
Deciding to u parallel processing (7)
breastControlling parallel query (8)
quitting
Determining the degree of parallelism (8)
结果英文The parallel hints (9)
Parallel configuration parameters (10)
Summary (11)
Understanding parallel SQL
In a rial - non-parallel - execution environment, a single process or thread1 For instance, consider the following SQL statement: undertakes the operations required to
never say goodbye mp3process your SQL statement and each action must complete before the succeeding action can commence. The single Oracle process may only leverage the power of a single CPU and read from a single disk at any given instant. Becau most modern hardware platforms include more than a single CPU and becau Oracle data is often spread across multiple disks, rial SQL execution is unable to take advantage of all of the available processing power.
SELECT *
FROM sh.customers
ORDER BY cust_first_name, cust_last_name, cust_year_of_birth
If executing without the parallel query option, a single process would be responsible for fetching all the rows in the CUSTOMERS table. The same process would be responsible for sorting the rows to satisfy the ORDER BY clau. Figure 1 illustrates the workflow.
F IGURE 1S ERIAL EXECUTION OF A SQL STATEMENT
We can request that Oracle execute this statement in parallel by using the PARALLEL hint:
inmyopinion1A process is an unit of execution with its own private memory. A thread is also a unit of execution, but shares memory with other threads within a process.
SELECT /*+ parallel(c,2) */ *
FROM sh.customers c
ORDER BY cust_first_name, cust_last_name, cust_year_of_birth
If parallel processing is available, the CUSTOMERS table will be scanned by two process in parallel. A further two process will be employed to sort the resulting rows. A final process – the ssion which issued the SQL in the first place - will combine the rows and return the result t. Figure 2 illustrates this quence of events.
F IGURE 2P ARALLEL E XECUTION
Oracle supports parallel processing for a wide range of operations, including queries, DDL and DML:
•Queries that involve table or index range scans.
•Bulk inrt, update or delete operations.
•Table and index creation.
latecomeParallel process and the Degree of Parallelism
The Degree of Parallelism (DOP) defines the number of parallel streams of execution that will be created. The number of parallel process is more often twice the degree of parallelism. This is becau each step in the execution plan needs to feeds data into the subquent step so two ts of process are required to maintain the parallel stream of processing.
冲杀Figure 3 (page 5) shows how parallel slaves are allocated for a DOP of two.
Parallel slave pool
The Oracle rver maintains a “pool” of parallel slave process available for parallel operations. The Databa configuration parameters PARALLEL_MIN_SERVERS and PARALLEL_MAX_SERVERS determine the initial and maximum size of the pool.
Parallel query IO
The buffer cache helps reduce disk IO by buffering frequently accesd data blocks in shared memory. Oracle has an alternate IO mechanism – direct path IO – which it can u if it determines that that it would be faster to bypass the buffer cache and perform the IO directly.
In Oracle 10g and earlier, parallel query always us direct path IO, and rial query will always u buffered IO. In 11g, Oracle can u buffered IO for parallel query (from 11.2 onwards) and rial queries may u direct path IO. However, it remains true that parallel queries are less likely to u buffered IO and may therefore have a higher IO cost than rial queries.
SELECT /*+ parallel(c,2) */ * FROM customers c
SELECT /*+ parallel(c,2) */ * FROM customers c
ORDER BY cust_last_name,cust_first_name
SELECT /*+ parallel(c,2) */ cust_last_name,count (*) FROM customers c
GROUP BY cust_last_name ORDER BY 2 desc
F IGURE 3 P ARALLEL PROCESS ALLOCATION FOR A DEGREE OF PARALLELISM (D EGREE OF P ARALLELISM ) OF 2
Parallel performance gains
The performance improvements that you can expect to obtain from parallel SQL depend on the suitability of your host computer, Oracle configuration and the SQL statement. If all the conditions for
parallel processing are met, you can expect to get substantial performance improvements in proportion to the Degree of Parallelism employed.
