Sunday, 22 February 2009

Partitioned Index rebuild with "Parallel" and without "Parallel" clause

Introduction:
In this article we will see, the impact of "Parallel" clause of rebuilding a partition index. We would also enable tracing on the session which execute the Alter index rebuild statement, and also all those parallel sessions, which are invoked automatically when we use "Parallel" clause.

Steps:
For this test, we can make one partitioned index unusable, we will use exchange partition clause of Oracle 9i, without specifying "Update global indexes", this will cause index unusable. And then we will rebuild both with "Parallel" and without "Parallel" clause.

1- Check the status of partitioned index.

SQL> select substr(index_name,1,20) index_name,substr(partition_name,1,20) part_name , status from dba_ind_partitions
where index_name like '%TEST%'
;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
TEST_PARTITIONED_TABLE_IDX JAN2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_02 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_02 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_02 USABLE



2- Exchange the partition with table.

a- we need to make an empty table of the partitioned table, and then we will exchange the partition with that empty table.

sql>create table TEST_PARTITIONED_TABLE_Test as select * from TEST_PARTITIONED_TABLE ;

Table created.

B- We will now exchange the partition JAN2008_02 with table TEST_PARTITIONED_TAB LE_Test.

SQL> alter table TEST_PARTITIONED_TABLE exchange partition JAN2008_02 with table TEST_PARTITIONED_TABLE_Test;

Table altered.

c- check the number of rows in the partition JAN2008_02, to verify that rows has been moved to the TEST_PARTITIONED_TABLE.

SQL>select count(1) from TEST_PARTITIONED_TABLE partition (jan2008_02);

COUNT(1)
----------
0

d- check the number of rows in teh TEST_PARTITIONED_TABLE

SQL>select count(1) from TEST_PARTITIONED_TABLE_test;

COUNT(1)
----------
14140873


e- get the data back in the partition JAN2008_02.

SQL>alter table TEST_PARTITIONED_TABLE exchange partition JAN2008_02 with table TEST_PARTITIONED_TABLE_Test;

Table altered.

3- Check index status after exchange partition.

INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
TEST_PARTITIONED_TABLE_IDX JAN2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_02 UNUSABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX JAN2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_02 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_03 USABLE
TEST_PARTITIONED_TABLE_IDX FEB2008_04 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_01 USABLE
TEST_PARTITIONED_TABLE_IDX MAR2008_02 USABLE

4- Rebuild unusable index partition JAN2008_02 without "Parallel"

SQL>alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online;

Index altered.

5- We can check the trace file for the above statement, and can estimate the time taken to rebuild the above index.

From the trace file for the above session.

alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 230.33 845.32 614267 522610 6455 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 230.33 845.32 614267 522610 6455 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: ALL_ROWS
Parsing user id: 5 (SYSTEM)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 27213 0.13 56.25
direct path write 1113 0.73 169.77
direct path read 11273 0.53 232.59
db file sequential read 30 0.01 0.10
rdbms ipc reply 117 0.04 0.10
log file sync 1 0.01 0.01
SQL*Net message to client 1 0.00 0.00



The elapsed time is 845.32 seconds (approx. 14.08 min.)

Now to check the impact of the parallel clause, we need to repeat steps 1 to 3.

5- after completing the steps 1 - 3, now we can rebuild the index with parallel clause.

SQL>alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online parallel;

Index altered.

6- From the trace file,

alter index TEST_PARTITIONED_TABLE_IDX rebuild partition JAN2008_02 online parallel

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 1 0 0
Execute 1 1.58 765.66 599831 527410 12060 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.60 765.68 599831 527411 12060 0

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 5 (SYSTEM)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
ksu process alloc latch yield 3 0.09 0.19
process startup 6 0.04 0.24
PX Deq: Join ACK 4 0.00 0.00
PX Deq: Execute Reply 683 1.96 761.48
PX Deq: Parse Reply 3 0.01 0.01
PX qref latch 23 0.01 0.30
PX Deq: Table Q qref 2 0.00 0.00
db file sequential read 9 0.01 0.01
rdbms ipc reply 98 0.04 0.12
log file sync 2 0.01 0.01
PX Deq: Signal ACK 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 2443.89 2443.89


The elapsed time is 765.68 second. (approx. 12.76 min)

Result:

The rebuild with parallel clause is faster then normal rebuild of the index.

1 comment:

db2profile said...

Clean I must say.
Thankyou for sharing.

Could you guide me how can I view trace of individual statement like you mentioned in point 5.
What setting needs to be done ?
Where are the ouput directed ?