Rebuild index test result

记录一下今天的测试结果：

System Configuration:  Sun Microsystems  sun4u Sun Fire V890
System clock frequency: 150 MHz
Memory size: 32768 Megabytes

========================= CPUs ===============================================

           Run   E$  CPU    CPU 
Brd  CPU   MHz   MB Impl.   Mask
--- ----- ---- ---- ------- ----
 A  0, 16 1500 32.0 US-IV+   2.2
 B  1, 17 1500 32.0 US-IV+   2.2
 A  2, 18 1500 32.0 US-IV+   2.2
 B  3, 19 1500 32.0 US-IV+   2.2
 C  4, 20 1500 32.0 US-IV+   2.2
 D  5, 21 1500 32.0 US-IV+   2.2
 C  6, 22 1500 32.0 US-IV+   2.2
 D  7, 23 1500 32.0 US-IV+   2.2

oracle@ortlab1:/u01/usr/ods/ods>> sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Apr 2 15:49:48 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


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

parallel_max_servers                 integer
10
parallel_min_percent                 integer
0
parallel_min_servers                 integer
6
parallel_server                      boolean
FALSE

SQL> set timing on

SQL> create index IN$REL_STEP_REF$TUID$RID ON REL_STEP_REFERENCE (TEST_UID, RUN_ID)
LOCAL
parallel 10
tablespace param_idx_s;  2    3    4 

Index created.

Elapsed: 00:04:56.50
SQL> drop index IN$REL_STEP_REF$TUID$RID;

Index dropped.

Elapsed: 00:00:09.12
SQL> create index IN$REL_STEP_REF$TUID$RID ON REL_STEP_REFERENCE (TEST_UID, RUN_ID)
LOCAL
parallel 10
nologging
tablespace param_idx_s;  2    3    4    5 

Index created.

Elapsed: 00:04:43.29
SQL> drop index IN$REL_STEP_REF$TUID$RID;

Index dropped.

Elapsed: 00:00:09.42
SQL>
SQL> create index IN$REL_STEP_REF$TUID$RID ON REL_STEP_REFERENCE (TEST_UID, RUN_ID)
LOCAL
parallel 4
nologging
tablespace param_idx_s;  2    3    4    5 

Index created.

Elapsed: 00:04:41.47
SQL> drop index IN$REL_STEP_REF$TUID$RID;

Index dropped.

Elapsed: 00:00:07.13
SQL> create index IN$REL_STEP_REF$TUID$RID ON REL_STEP_REFERENCE (TEST_UID, RUN_ID)
LOCAL
parallel 8
nologging
tablespace param_idx_s;  2    3    4    5 

Index created.

Elapsed: 00:05:47.03
SQL> drop index IN$REL_STEP_REF$TUID$RID;

Index dropped.

Elapsed: 00:00:08.54
SQL>
SQL> create index IN$REL_STEP_REF$TUID$RID ON REL_STEP_REFERENCE (TEST_UID, RUN_ID)
LOCAL
nologging
tablespace param_idx_s;  2    3    4 

Index created.

Elapsed: 00:08:52.65
SQL> drop index IN$REL_STEP_REF$TUID$RID;

Index dropped.

Elapsed: 00:00:04.87
SQL> create index IN$REL_STEP_REF$TUID$RID ON REL_STEP_REFERENCE (TEST_UID, RUN_ID)
LOCAL
parallel 4
nologging
tablespace param_idx_s;  2    3    4    5 

Index created.

Elapsed: 00:04:40.16
SQL> drop index IN$REL_STEP_REF$TUID$RID;

Index dropped.

Elapsed: 00:00:03.02
SQL> alter session force parallel query;

Session altered.

Elapsed: 00:00:00.00
SQL> create index IN$REL_STEP_REF$TUID$RID ON REL_STEP_REFERENCE (TEST_UID, RUN_ID)
LOCAL
parallel 4
nologging
tablespace param_idx_s;  2    3    4    5 

Index created.

Elapsed: 00:04:29.80
SQL> select * from v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    0             0
DML Parallelized                        0             0
DDL Parallelized                        1           121
DFO Trees                               1           121
Server Threads                          4             0
Allocation Height                       4             0
Allocation Width                        1             0
Local Msgs Sent                       272        925021
Distr Msgs Sent                         0             0
Local Msgs Recv'd                     280        926251
Distr Msgs Recv'd                       0             0

11 rows selected.

Elapsed: 00:00:00.19
SQL> drop index IN$REL_STEP_REF$TUID$RID;

Index dropped.

Elapsed: 00:00:10.42
SQL> create index IN$REL_STEP_REF$TUID$RID ON REL_STEP_REFERENCE (TEST_UID, RUN_ID)
LOCAL
parallel 6
nologging
tablespace param_idx_s;  2    3    4    5 

Index created.

Elapsed: 00:05:04.88
SQL> drop index IN$REL_STEP_REF$TUID$RID;

Index dropped.

Elapsed: 00:00:09.01
SQL> create index IN$REL_STEP_REF$TUID$RID ON REL_STEP_REFERENCE (TEST_UID, RUN_ID)
LOCAL
parallel 4
nologging
tablespace param_idx_s;  2    3    4    5 

Index created.

Elapsed: 00:04:16.25

×××××××××××××××××××××××××××××××××××××××××××××××××××××
过程描述：用Parallel with nologging/nologging 组合测试INDEX rebuild performance.
PARALLE:2,4,6,8,10

结论：8个CPU 用4 parallel is the fastest nologging .

体会：parallel degree 和速度不成正比。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3637/viewspace-720219/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/3637/viewspace-720219/