event "wait by other session" when create index with parallel

From: qihua wu <staywithpin@xxxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
Date: Fri, 26 Mar 2010 16:49:39 +0800
database is 10.2.04 , and _adaptive_direct_read is true.

We have a table of size 800G, I am creating the index using parallel 4,
during the time I was monitoring the job, the wait event is that 3 sessions
waitingon  "read by other session" and one sessions waiting on "db file
scattered read". What I can't understand is that since oracle assigns
different block ranges to different slaves, so they should read different
blocks, how could 3 session trying to read on the same blocks which is being
read by another slave (waiting on scattered read). And since the table is so
huge, I hope it could used "direct path read" instead of "scattered read",
any way to enforce direct path read?

The table has a column with long type, I dumped the block which was reading
by all 4 sessions, there was no chained row in it.

CREATE INDEX XXXXXXXXXXXXXX_IX1 ON XXXXXXXXXXXXXX ( LAST_MODIFIED_DATE ASC )
reverse tablespace MD_INDEX ONLINE
parallel 4 COMPUTE STATISTICS



----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name               | Rows  | Bytes | Cost
(%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT   |                    |       |       |
1268K(100)|          |        |      |            |
|   1 |  PX COORDINATOR          |                    |       |
|            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001           |   480M|
2290M|            |          |  Q1,01 | P->S | QC (ORDER) |
|   3 |    INDEX BUILD NON UNIQUE| XXXXXXXXXXXXXX_IX1 |       |
|            |          |  Q1,01 | PCWP |            |
|   4 |     SORT CREATE INDEX    |                    |   480M|
2290M|            |          |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |                    |   480M|  2290M|
1268K  (1)| 03:11:36 |  Q1,01 | PCWP |            |
|   6 |       PX SEND RANGE      | :TQ10000           |   480M|  2290M|
1268K  (1)| 03:11:36 |  Q1,00 | P->P | RANGE      |
|   7 |        PX BLOCK ITERATOR |                    |   480M|  2290M|
1268K  (1)| 03:11:36 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| XXXXXXXXXXXXXX     |   480M|  2290M|
1268K  (1)| 03:11:36 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------
event "wait by other session" when create index with parallel

Other related posts:
