SQL Performance (MOSC)

MOSC Banner

11gR2 : Different use of PARALLELISM

edited May 28, 2013 3:34AM in SQL Performance (MOSC) 1 commentAnswered
Hi,

In 11gR2, I would like to optimize a well-tuned query like:

SQL > INSERT INTO T1 SELECT ... others SELECT ...;

=>  30 min.

1/ By using parallelism in table DDL (table level).

SQL > ALTER TABLE T1 PARALLEL ;

SQL > INSERT INTO T1 SELECT ... others SELECT ...;

=>  5 min !

1/ By using hint in INSERT and in all SELECT of the query :

SQL> ALTER session ENABLE PARALLEL DML;

SQL > INSERT /*+ PARALLEL(AUTO) */   INTO T1 SELECT /*+ PARALLEL(AUTO) */    ... others  SELECT /*+ PARALLEL(AUTO) */   ...;

=>  20 min only !

Why this difference with parallelism at table level ?

Best regards.

GD.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center