Databases Reference
In-Depth Information
6.
Disable spooling:
SPOOL OFF
SET TERMOUT ON
7.
Display the current date/time:
SELECT TO_CHAR(SYSDATE,'YYYYMMDD_HH24MISS') TIMECOL FROM DUAL;
8.
Empty the buffers from previous executions to be sure no data is already cached
in memory:
ALTER SYSTEM FLUSH BUFFER_CACHE;
9.
Display the current date/time:
SELECT TO_CHAR(SYSDATE,'YYYYMMDD_HH24MISS') TIMECOL FROM DUAL;
10. Disable output to terminal and spool on the
NULL
device:
SET TERMOUT OFF
SPOOL /DEV/NULL
11. Execute the same query in step 5 using a hint to use parallel SQL execution:
SELECT /*+ PARALLEL (S, 2) */
S.PROD_ID, S.CUST_ID, S.TIME_ID
FROM SH.SALES S
ORDER BY S.AMOUNT_SOLD DESC;
12. Disable spooling:
SPOOL OFF
SET TERMOUT ON
13. Display the current date/time:
SELECT TO_CHAR(SYSDATE,'YYYYMMDD_HH24MISS') TIMECOL FROM DUAL;
How it works...
The script used in this recipe is made of two identical parts. In each part we initially flush
the buffer cache to be sure that there are no data blocks in memory cached from previous
executions of the same query.
We then enable the spool to a
NULL
device and we disable the terminal output to avoid
viewing the rows returned from our query.
We need to focus on steps 5 and 11. In step 5 we execute the query in normal sequential steps,
while in step 11 we add the
/*+
PARALLEL
(S,
2)
*/
hint. This hint requests parallel execution,
using 2 processes/threads, on table S (the alias used for the
SALES
table in our query).
