Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Friday, February 19, 2010

Parallel index rebuild

You can use parallel index rebuild option whenever you have to rebuild an index since, most of the time, parallel rebuild option will be much faster than a noparallel operation.

I will try to demonstrate this situation below.

First create a tablespace and a database user
C:\uural\work_cmd\20081014>sqlplus sys@db01 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 19 15:16:33 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@db01> create tablespace tbs01;

Tablespace created.

SYS@db01> create user dbuser01 identified by dbuser01 default tablespace tbs01;

User created.

SYS@db01> grant connect, resource to dbuser01;

Grant succeeded.

SYS@db01> grant alter system to dbuser01;

Grant succeeded.

Connect to the database with the new user
C:\uural\work_cmd\20100219>sqlplus dbuser01/dbuser01@db01

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 19 15:32:56 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

DBUSER01@db01>

Create a test table
DBUSER01@db01> @cre_table01
DBUSER01@db01> create table table01 nologging pctfree 90
2  as
3  select dbms_random.value(1,1000000) id
4  , rpad('*',4000,'*') data
5    from dual
6  connect by level <= 1000000;

Table created.
Create an index on this test table
DBUSER01@db01> select blocks,bytes from user_segments where segment_name = 'TABLE01';

BLOCKS BYTES
---------- ----------
1007616 8254390272

1 row selected.

DBUSER01@db01> create index table01_01 on table01(id) nologging;
Index created.
Make this index unusable
DBUSER01@db01> alter index table01_01 unusable;
Clear the buffer cache of the database
SYS@db01> alter system flush buffer_cache;

System altered.
Set timing on
DBUSER01@db01> show timing
timing OFF
DBUSER01@db01> set timing on
Enable session trace
DBUSER01@db01> alter system set sql_trace=TRUE;

System altered.

Elapsed: 00:00:00.00
Run NOPARALLEL index rebuild
DBUSER01@db01> alter index table01_01 rebuild nologging;

Index altered.

Elapsed: 00:01:01.20
It took 1 minutes 1 seconds Check the trace file
********************************************************************************

alter index table01_01 rebuild nologging


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          1          1          0           0
Execute      1     20.11      59.45    1000005    1000174       6897           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     20.11      59.49    1000006    1000175       6897           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 378
********************************************************************************  
Before trying the PARALLEL option clear the buffer cache again
SYS@db01> alter system flush buffer_cache;

System altered.
Rebuild the index with PARALLEL option this time
DBUSER01@db01> alter index table01_01 unusable;

Index altered.

Elapsed: 00:00:00.07
DBUSER01@db01> alter index table01_01 rebuild parallel nologging;

Index altered.

Elapsed: 00:00:30.62
DBUSER01@db01> 
It took 30 seconds Check the trace file again
********************************************************************************

alter index table01_01 rebuild parallel nologging


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          1          1          0           0
Execute      1      0.06      29.72    1005359    1003636       9010           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.06      29.73    1005360    1003637       9010           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 378



******************************************************************************** 

As you can see parallel index rebuild is much faster than noparallel index rebuild.
Since, most of the time, parallel operations use the server CPU more efficiently, especially in data warehouse databases
we better use the parallel option in database operations whenever possible.

Regards,
Ural

No comments: