Databases Reference
In-Depth Information
Enabling parallel SQL
In the recent past, we have seen the passage from the megahertz era to the multi-core era
in microprocessor design. Even laptops and small devices have multi-core CPUs available
that can take advantage of applications accomplishing work in parallel.
In this recipe we will see how to enable parallel execution of queries to speed them up.
Getting ready
To observe a performance gain in this recipe, we obviously need a machine with a
minimum of two cores. A single-core machine, using parallel SQL execution leads to
a dip in performance when compared to using normal sequential execution.
How to do it...
In this recipe, we will use a query that returns about 1 million records. To avoid displaying this
huge amount of data in our terminal, we will need to copy the code in a SQL script file, naming
it
TEST.SQL
, for example, and then execute it using the
@
operator from SQL*Plus:
@TEST.SQL
For clarity, the content of the script is split into the following steps:
1.
Connect to the database as
SYSDBA
:
CONNECT / AS SYSDBA
2.
Empty the buffers from previous executions to be sure no data is already cached in
memory:
ALTER SYSTEM FLUSH BUFFER_CACHE;
3.
Display the current date/time:
SELECT TO_CHAR(SYSDATE,'YYYYMMDD_HH24MISS') TIMECOL FROM DUAL;
4.
Disable the output to terminal and spool on the
NULL
device:
SET TERMOUT OFF
SPOOL /DEV/NULL
5.
Execute a long query:
SELECT
S.PROD_ID, S.CUST_ID, S.TIME_ID
FROM SH.SALES S
ORDER BY S.AMOUNT_SOLD DESC;
