RE: Cannot use PARALLEL on GTTs ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 6 Mar 2014 07:15:45 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DE1756_at_exmbx05.thus.corp>

That falls into the category of: "used to be true, but manuals not updated" According to a sample script I have (dated 2006) this had changed by 9.2.0.6

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Chitale, Hemant K [Hemant-K.Chitale_at_sc.com] Sent: 06 March 2014 06:59
To: ORACLE-L
Subject: Cannot use PARALLEL on GTTs ?

In 11.2 documentation on the PARALLEL Hint, http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#BABHFDDH I find this statement :

Oracle ignores parallel hints on temporary tables.

I ran a simple test (script below) and find that I can see only 1 PQ Slave Set for an INSERT PARALLEL SELECT PARALLEL execution.

REM Test PARALLEL Operations on GTT

spool GTT_Parallel_Ops

set echo on

alter session enable parallel dml;

drop table GTT_PARALLEL_OPS_TEST;

create global temporary table GTT_PARALLEL_OPS_TEST

(id_column number, data_col_1 varchar2(30), data_col_2 varchar2(128), date_col date)

on commit preserve rows

/

alter table GTT_PARALLEL_OPS_TEST parallel 4;

insert /*+ PARALLEL (MANUAL) */ into GTT_PARALLEL_OPS_TEST

select object_id, owner, object_name, created

from dba_objects

/

commit;

pause set up another session to monitor next DML

insert /*+ PARALLEL (MANUAL) */ into GTT_PARALLEL_OPS_TEST

select /*+ PARALLEL (MANUAL) */ * from GTT_PARALLEL_OPS_TEST

/

commit;

exec dbms_stats.gather_table_stats('','GTT_PARALLEL_OPS_TEST');

select num_Rows from user_tables

where table_name = 'GTT_PARALLEL_OPS_TEST'

/

select /*+ PARALLEL (MANUAL) */ count(*) from GTT_PARALLEL_OPS_TEST;

spool off

Hemant K Chitale

This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html.

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

Received on Thu Mar 06 2014 - 08:15:45 CET