GTT's allow parallelism?

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Tue, 3 Oct 2006 22:31:05 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E36024BE59A@CWYMSX04.Corp.Acxiom.net>

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)

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_at_acxiom.com>

"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.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 03 2006 - 22:31:05 CDT