GTT's allow parallelism?
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 3 Oct 2006 22:31:05 -0500
Platform is Linux RHEL 4, Oracle 10.2.0.2.
According to the Oracle10g SQL Reference Guide, under Restrictions for CREATE
GLOBAL TEMPORARY TABLE, it says:
* Parallel DML and parallel queries are not supported for temporary tables.
Parallel hints are ignored. Specification of the parallel_clause returns an
error.
Seems pretty clear to me. Except that I made a typo recently and included the
parallel clause. Not only did I not get an error, it seems to have worked in
parallel, for DDL (not denied in the doc), DML (denied), and Query (denied).
Here's my best attempt at proof:
/* Parallel DDL SQL */
CREATE GLOBAL TEMPORARY TABLE dherri_test_gtparallel_tb ON COMMIT PRESERVE ROWS
PARALLEL AS
SELECT * FROM dba_objects;
Table created.
SELECT degree, temporary, duration FROM user_tables WHERE table_name =
'DHERRI_TEST_GTPARALLEL_TB';
DEGREE T DURATION
---------- - ---------------
DEFAULT Y SYS$SESSION
1 row selected.
SELECT * FROM v$pq_sesstat;
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ------------------ ------------------
Queries Parallelized 0 0
DML Parallelized 0 0
DDL Parallelized 1 1
DFO Trees 3 3
Server Threads 32 0
Allocation Height 8 0
Allocation Width 1 0
Local Msgs Sent 5788 5788
Distr Msgs Sent 0 0
Local Msgs Recv'd 5796 5796
Distr Msgs Recv'd 0 0
11 rows selected.
=====================================================================
/* Parallel Query */
SELECT COUNT(DISTINCT object_id) FROM dherri_test_gtparallel_tb;
COUNT(DISTINCTOBJECT_ID)
------------------------
50655
1 row selected.
SELECT * FROM v$pq_sesstat;
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ------------------ ------------------
Queries Parallelized 1 1
DML Parallelized 0 0
DDL Parallelized 0 1
DFO Trees 1 4
Server Threads 16 0
Allocation Height 8 0
Allocation Width 1 0
Local Msgs Sent 558 6346
Distr Msgs Sent 0 0
Local Msgs Recv'd 574 6370
Distr Msgs Recv'd 0 0
11 rows selected.
======================================================================
/* Parallel DML */
ALTER SESSION FORCE PARALLEL DML;
Session altered.
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT forever, LEVEL 12';
Session altered.
INSERT INTO dherri_test_gtparallel_tb
SELECT * FROM dba_objects;
50656 rows created.
COMMIT;
Commit complete.
SELECT * FROM v$pq_sesstat;
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ------------------ ------------------
Queries Parallelized 0 1
DML Parallelized 1 1
DDL Parallelized 0 1
DFO Trees 3 7
Server Threads 32 0
Allocation Height 8 0
Allocation Width 1 0
Local Msgs Sent 5787 12133
Distr Msgs Sent 0 0
Local Msgs Recv'd 5795 12165
Distr Msgs Recv'd 0 0
11 rows selected.
I checked the bdump directory and sure enough there are 32 trace files, one per
Pxxx slave used in the parallel DML above. Have I misinterpreted the
documentation? Am I misinterpreting my results?
Dave
-------------------------------------
Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762
<mailto:dherri@xxxxxxxxxx>
-------------------------------------
"When I come home from work and see those little noses pressed against the
windowpane, then I know I am a success" - Paul Faulkner
*************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be
legally privileged.
If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.
If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.
Thank you.
*************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
