Forum Stats

  • 3,782,581 Users
  • 2,254,668 Discussions
  • 7,880,130 Comments

Discussions

Create Index with the PARALLEL 4 option...

Unknown
edited Feb 23, 2016 7:12PM in SQL & PL/SQL

Hello All, I hope this finds you all in Good Spirits.

I had one of my Developers create an index today in one of our many test environments against a table that had many millions of rows in it and they used the

"PARALLEL 4" option.

Does the PARALLEL 4 option create 4 threads on the database to create it faster?

What does that option actually do?

thanks,

Lady Allora.

Tagged:
Billy VerreynnePaulzip

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,541 Blue Diamond
    edited Feb 22, 2016 5:58PM Accepted Answer

    When creating an index oracle must first collect the symbolic key/ROWID pairs (used in index lookups) with a full-table scan. With Parallel option the scan on full table can occur in parallel based on the number of CPUs, table partitioning and disk configuration. This can make the task faster. In your case 4 is a best case, it means up to 4 processes can be used, but doesn't mean they will be. You'll need 5 or more CPUs (one is used as a kind of controller / overseer).

«1

Answers

  • Paulzip
    Paulzip Member Posts: 8,541 Blue Diamond
    edited Feb 22, 2016 5:58PM Accepted Answer

    When creating an index oracle must first collect the symbolic key/ROWID pairs (used in index lookups) with a full-table scan. With Parallel option the scan on full table can occur in parallel based on the number of CPUs, table partitioning and disk configuration. This can make the task faster. In your case 4 is a best case, it means up to 4 processes can be used, but doesn't mean they will be. You'll need 5 or more CPUs (one is used as a kind of controller / overseer).

  • Unknown
    edited Feb 22, 2016 7:16PM
    LadyAllora wrote:
    
    Hello All, I hope this finds you all in Good Spirits.
    
    
    I had one of my Developers create an index today in one of our many test environments against a table that had many millions of rows in it and they used the
    "PARALLEL 4" option.
    
    Does the PARALLEL 4 option create 4 threads on the database to create it faster?
    
    What does that option actually do?
    
    
    thanks,
    Lady Allora.
    

    I suggest that you produce EXPLAIN PLAN so you can see for yourself how Oracle processes the statement.

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited Feb 22, 2016 7:19PM
    LadyAllora wrote:
    
    Hello All, I hope this finds you all in Good Spirits.
    
    
    I had one of my Developers create an index today in one of our many test environments against a table that had many millions of rows in it and they used the
    "PARALLEL 4" option.
    
    Does the PARALLEL 4 option create 4 threads on the database to create it faster?
    
    What does that option actually do?
    
    
    thanks,
    Lady Allora.
    

    Always best to give the documentation a go, they really do document a lot of this stuff

    http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm

    Cheers,

  • Unknown
    edited Feb 22, 2016 7:49PM
    I had one of my Developers create an index today in one of our many test environments against a table that had many millions of rows in it and they used the
    "PARALLEL 4" option.
    
    Does the PARALLEL 4 option create 4 threads on the database to create it faster?
    

    What did your developer say when you ask them?

    What did the documentation say when you looked it up there?

    When was the last time you actually read the documentation before asking someone for an answer?

    Billy Verreynne
  • Unknown
    edited Feb 23, 2016 12:52PM

    I suggest you take your RUDE comments some where else...

  • Unknown
    edited Feb 23, 2016 1:04PM
    LadyAllora wrote:
    
    I suggest you take your RUDE comments some where else...
    

    since when is EXPLAIN PLAN rude?

  • Unknown
    edited Feb 23, 2016 1:13PM

    Hello Paulzip,

    Ok, this answered my question perfectly. I was curious about if that option is did create spawned processes.

    Thanks for answering my Question. You're a real champ.

    thanks,

    Lady Allora.

    Paulzip
  • Unknown
    edited Feb 23, 2016 4:38PM

    You lack social manners.

    I will not tolerant some testosterone driven male that misdirects his anger and thinks he can get away with it.

    You've insulted the wrong Woman....

  • Unknown
    edited Feb 23, 2016 4:47PM

    EXPLAIN PLAN shows Parallel  Slave processes (when they  are used) so you could have seen the obvious answer for yourself.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,850 Gold Crown
    edited Feb 23, 2016 5:32PM

    If the degree is 4 (and Oracle runs it at that degree) then the number of extra processes used will be 8:

    Sample execution plan:

    SQL> explain plan for

      2  create index t1_i1 on t1(object_id) parallel (degree 4);

    Explained.

    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT

    ----------------------------------

    Plan hash value: 2666861883

    ------------------------------------------------------------------------------------------------------------------

    | Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

    ------------------------------------------------------------------------------------------------------------------

    |   0 | CREATE INDEX STATEMENT   |          |   565K|  2761K|   340   (2)| 00:00:02 |        |      |            |

    |   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |

    |   2 |   PX SEND QC (ORDER)     | :TQ10001 |   565K|  2761K|            |          |  Q1,01 | P->S | QC (ORDER) |

    |   3 |    INDEX BUILD NON UNIQUE| T1_I1    |       |       |            |          |  Q1,01 | PCWP |            |

    |   4 |     SORT CREATE INDEX    |          |   565K|  2761K|            |          |  Q1,01 | PCWP |            |

    |   5 |      PX RECEIVE          |          |   565K|  2761K|   289   (3)| 00:00:02 |  Q1,01 | PCWP |            |

    |   6 |       PX SEND RANGE      | :TQ10000 |   565K|  2761K|   289   (3)| 00:00:02 |  Q1,00 | P->P | RANGE      |

    |   7 |        PX BLOCK ITERATOR |          |   565K|  2761K|   289   (3)| 00:00:02 |  Q1,00 | PCWC |            |

    |   8 |         TABLE ACCESS FULL| T1       |   565K|  2761K|   289   (3)| 00:00:02 |  Q1,00 | PCWP |            |

    ------------------------------------------------------------------------------------------------------------------

    Note

    -----

       - estimated index size: 13M bytes

    19 rows selected.

    The PX SEND / PX RECEIVE at operations 6 and 5 show you that you would get two sets of parallel slaves, one set to scan the table and distribute the data to the other set by key range so that the members of the second set don't overlap in the ranges they use to sort and build the index.

    Regards

    Jonathan Lewis

This discussion has been closed.