Mohamed Houri’s Oracle Notes

August 5, 2017

Parallel index rebuild: Oracle versus MS-SQL Server

Filed under: Oracle — hourim @ 4:57 pm

A couple of months ago I have been prompted by this MS-SQL Server article about rebuilding indexes in parallel. The bottom line of this article is that MS-SQL Server engine will not use parallel run when rebuilding a unique index starting with a column having a single distinct value. This article will first demonstrate this claim using MS-SQL Server 2016 and then extend the same demonstration to Oracle in its latest version.

1. SQL Server

The followings will create a table and its unique three columns composite clustered index

select @@version
Microsoft SQL Server 2016 (RTM-GDR) (KB3210111) - 13.0.1728.2 (X64)  
Dec 13 2016 04:40:28   Copyright (c) Microsoft Corporation 
Developer Edition (64-bit) on Windows 8.1 6.3 <X64> (Build 9600: ) 

-- create table
create table t (c1 int, c2 int, c3 int);

set nocount on
begin tran
declare @i int = 0
while @i < 10000000
begin
    insert into t values (@i, @i, @i)
    set @i = @i + 1
end
commit tran

-- create a unique clustered index
create unique clustered index ix on t(c1,c2,c3)

Now that we are done with the model we are going to rebuild the above clustered index using parallel run

-- This will use parallel plan
ALTER INDEX ix on [dbo].t rebuild WITH (maxdop=10, online=on)

Before rebuilding the index I have activated the ”Include Actual Execution Plan” icon of SSMS (SQL Server Management Studio) so that I got the following “row source” plan where the two yellow arrows indicate that the rebuild has been, indeed, parallelized:

However has the first column c1 of the index been single valued the index would have not been rebuild parallely by the SQL Server engine as the followings demonstrate:

-- set the leading column of the clustered index to a single value
 update t set c1 = 1

-- update statistics with 100% sample
update statistics t with fullscan

–- This will use serial plan
ALTER INDEX ix on [dbo].t rebuild WITH (maxdop=10, online=on)

The execution plan of the second index rebuild show that this time MS-SQL refuses to obey the parallel hint and decides to go serially as shown below:

The MS-SQL documentation stipulates that when the leading key of the index of a non-partitioned table has a single distinct value it becomes useless to give a parallel thread a range of single value to process them. In other words the leading column should have multiple distinct values in order for parallelism to be worth a utilization.

Now that we have set the scene for MS-SQL let’s reproduce it for Oracle

2. Oracle

SQL> select banner from v$version where rownum=1;

BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table t (c1 int, c2 int, c3 int);

SQL> begin
       for j in 1..10000000
     loop
        insert /*+ append */ into t values (j, j, j);
     end loop;
     commit;
     end;
     /

SQL> create unique index ix on t(c1,c2,c3);

SQL> exec dbms_stats.gather_table_stats(user, 't');

SQL> select 
         column_name
         ,num_distinct
         ,histogram
    from 
        all_tab_col_statistics
    where table_name = 'T'
    order by 2;

COLUMN_NAM NUM_DISTINCT HISTOGRAM
---------- ------------ ---------------
C1              9914368 NONE
C3              9914368 NONE
C2              9914368 NONE

It’s now time to use parallelism to rebuild the above index

SQL> alter index ix rebuild parallel 4;

select
    dfo_number, tq_id, server_type, instance, process, num_rows
from
   v$pq_tqstat
order by
    dfo_number, tq_id, server_type, instance, process;

DFO_NUMBER      TQ_ID SERVER_TYPE    INSTANCE PROCESS    NUM_ROWS
---------- ---------- ------------ ---------- -------- ----------
         1          0 Consumer              1 P000        2923571
         1          0 Consumer              1 P001        2623371
         1          0 Consumer              1 P002        2270919
         1          0 Consumer              1 P003        2182139
         1          0 Producer              1 P004        2638901
         1          0 Producer              1 P005        2637316
         1          0 Producer              1 P006        2611722
         1          0 Producer              1 P007        2114251
         1          0 Ranger                1 QC               12
         1          1 Consumer              1 QC                4
         1          1 Producer              1 P000              1
         1          1 Producer              1 P001              1
         1          1 Producer              1 P002              1
         1          1 Producer              1 P003              1


Parallel Execution Details (DOP=4 , Servers Allocated=8)
===================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    |
|                |       |         | Time(s) | Time(s) | Waits(s) |
===================================================================
| PX Coordinator | QC    |         |    0.10 |    0.02 |     0.01 |
| p000           | Set 1 |       1 |    5.53 |    4.12 |     0.02 |
| p001           | Set 1 |       2 |    5.48 |    3.60 |     0.01 |
| p002           | Set 1 |       3 |    4.87 |    3.20 |     0.03 |
| p003           | Set 1 |       4 |    4.97 |    3.03 |     0.01 |
| p004           | Set 2 |       1 |    1.05 |    1.05 |          |
| p005           | Set 2 |       2 |    1.01 |    0.83 |          |
| p006           | Set 2 |       3 |    1.04 |    1.03 |          |
| p007           | Set 2 |       4 |    0.99 |    0.84 |          |
===================================================================

SQL Plan Monitoring Details (Plan Hash Value=3277881472)
===========================================================================
| Id |         Operation          |   Name   |  Rows   | Cost |   Time    |
|    |                            |          | (Estim) |      | Active(s) |
===========================================================================
|  0 | CREATE INDEX STATEMENT     |          |         |      |         1 |
|  1 |   PX COORDINATOR           |          |         |      |         1 |
|  2 |    PX SEND QC (ORDER)      | :TQ10001 |      82 |      |         1 |
|  3 |     INDEX BUILD UNIQUE     | IX       |         |      |         4 |
|  4 |      SORT CREATE INDEX     |          |      82 |      |         6 |
|  5 |       PX RECEIVE           |          |      82 |    2 |         4 |
|  6 |        PX SEND RANGE       | :TQ10000 |      82 |    2 |         2 |
|  7 |         PX BLOCK ITERATOR  |          |      82 |    2 |         1 |
|  8 |          TABLE ACCESS FULL | T        |      82 |    2 |         1 |
===========================================================================

As expected Oracle, in accordance with MS-SQL Server, has obeyed the instruction of rebuilding the index in parallel.

Let’s now unify the distinct values of the leading column c1 of the index and then try rebuilding it parallely:

 SQL> update t set c1 = 1;

SQL> exec dbms_stats.gather_table_stats(user, 't');

SQL> select 
         column_name
         ,num_distinct
         ,histogram
      from 
        all_tab_col_statistics
      where table_name = 'T'
      order by 2;

COLUMN_NAM NUM_DISTINCT HISTOGRAM
---------- ------------ ----------
C1                    1 NONE
C3              9914368 NONE
C2              9914368 NONE

SQL> alter index ix rebuild parallel 4;

select
    dfo_number, tq_id, server_type, instance, process, num_rows
from
   v$pq_tqstat
order by
    dfo_number, tq_id, server_type, instance, process;
DFO_NUMBER      TQ_ID SERVER_TYPE     INSTANCE PROCESS     NUM_ROWS
---------- ---------- ------------- ---------- --------- ----------
         1          0 Consumer               1 P000         2923571
         1          0 Consumer               1 P001         2623371
         1          0 Consumer               1 P002         2270919
         1          0 Consumer               1 P003         2182139
         1          0 Producer               1 P004         2694213
         1          0 Producer               1 P005         2542784
         1          0 Producer               1 P006         2814202
         1          0 Producer               1 P007         1950991
         1          0 Ranger                 1 QC                12
         1          1 Consumer               1 QC                 4
         1          1 Producer               1 P000               1
         1          1 Producer               1 P001               1
         1          1 Producer               1 P002               1
         1          1 Producer               1 P003               1

Parallel Execution Details (DOP=4 , Servers Allocated=8)
===================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    |
|                |       |         | Time(s) | Time(s) | Waits(s) |
===================================================================
| PX Coordinator | QC    |         |    0.78 |    0.02 |     0.01 |
| p000           | Set 1 |       1 |    5.16 |    3.95 |     0.01 |
| p001           | Set 1 |       2 |    5.10 |    3.56 |     0.01 |
| p002           | Set 1 |       3 |    4.09 |    3.15 |     0.01 |
| p003           | Set 1 |       4 |    4.70 |    3.23 |     0.00 |
| p004           | Set 2 |       1 |    1.38 |    1.08 |     0.02 |
| p005           | Set 2 |       2 |    1.26 |    1.23 |     0.03 |
| p006           | Set 2 |       3 |    1.32 |    1.12 |     0.03 |
| p007           | Set 2 |       4 |    1.31 |    0.99 |     0.03 |
===================================================================

SQL Plan Monitoring Details (Plan Hash Value=3277881472)
===========================================================================
| Id |         Operation          |   Name   |  Rows   | Cost |   Time    |
|    |                            |          | (Estim) |      | Active(s) |
===========================================================================
|  0 | CREATE INDEX STATEMENT     |          |         |      |         2 |
|  1 |   PX COORDINATOR           |          |         |      |         7 |
|  2 |    PX SEND QC (ORDER)      | :TQ10001 |     10M |      |         1 |
|  3 |     INDEX BUILD UNIQUE     | IX       |         |      |         4 |
|  4 |      SORT CREATE INDEX     |          |     10M |      |         5 |
|  5 |       PX RECEIVE           |          |     10M | 2397 |         3 |
|  6 |        PX SEND RANGE       | :TQ10000 |     10M | 2397 |         2 |
|  7 |         PX BLOCK ITERATOR  |          |     10M | 2397 |         1 |
|  8 |          TABLE ACCESS FULL | T        |     10M | 2397 |         1 |
===========================================================================		

In contrast to MS-SQL server which refuses to use parallelism when rebuilding a unique clustered index starting with a column having a unique distinct value, Oracle has successfully rebuild the same kind of unique index in approximatively the same execution time.

3. SUMMARY

We’ve considered two instances of the same unique composite index:

  1. A unique composite index starting with a column having a unique distinct value
  2. A unique composite index starting with a more selective column

It is interesting to see that Oracle, in contrast to MS-SQL Server, is in the capacity of using parallelism when rebuilding both type of indexes. In addition Oracle rebuild execution time of the second type of indexes is as good as that of the first type.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: