Re: Slow CREATE INDEX PARALLEL

From: Keith Moore <kmoore_at_zephyrus.com>
Date: Wed, 11 Mar 2009 18:21:20 -0500 (CDT)
Message-ID: <b0f328578cf30cdb7efc5afbb41b81e5.squirrel_at_lady.zephyrus.com>

For the NOPARALLEL create, here is the information from the tkprof output. For whatever reason, it does not have an explain plan

CREATE INDEX "SA"."IND_SUBC_CURRQ2QUEUE" ON "SA"."TABLE_SUBCASE" ("SUBC_CURRQ2QUEUE")
PCTFREE 20
NOLOGGING
COMPUTE STATISTICS
TABLESPACE "SA_NDX02" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.07       0.07          3         75          0           0
Execute      1    376.09     933.94    1532994    1488348        236           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    376.16     934.02    1532997    1488423        236           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       103        0.29          0.79
  db file scattered read                      46471       58.63        651.59
  direct path write                             886        0.22          4.34
  db file parallel read                           1        0.01          0.01
  direct path read                             2385        0.30          3.66
  control file sequential read                    9        0.00          0.00
  control file parallel write                     3        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

For the PARALLEL create, I unfortunately do not have the trace file for the main process. I think it's related to it being 4:30 in the morning when I was working.

I do have the parallel execution processes and here is the tkprof output for one of the processes. From the trace files it looks like Oracle actually did a PARALLEL 2 and not PARALLEL 3.

SELECT /*+ Q43471000 NO_EXPAND ROWID(A1) */ A1."SUBC_OWNER2USER" C0,   A1."SUBC_WIP2WIPBIN" C1,A1.ROWID C2
FROM
 "SA"."TABLE_SUBCASE" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       3.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2    512.52    5639.23     747754    3375229          0    10070312
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4    512.52    5642.25     747754    3375229          0    10070312

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5

Rows Row Source Operation

-------  ---------------------------------------------------

10070313 GRANULE ITERATOR ROWID RANGE (cr=3375229 r=747754 w=0 time=5607544350 us)
10070313 TABLE ACCESS BY ROWID RANGE OBJ#(53788) (cr=3375229 r=747754 w=0 time=5595552862 us)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  library cache pin                               3        3.00          3.01
  direct path read                            23151        0.60        318.49
  PX Deq: Execution Msg                       24263        2.00        522.77
  db file sequential read                   1288362       58.61       5100.81
  buffer read retry                              24        1.00         24.13
  PX Deq Credit: need buffer                   1448        0.18          0.36
  PX Deq Credit: send blkd                       86        1.84         12.64
  PX qref latch                                   1        0.00          0.00
********************************************************************************

> What are the execution plans for each?
>
> I would recommend a DOP that is a power of 2 (2/4/8/16/...), as it
> leads to the most even distribution of work (similar to why power of 2
> is recommended for the number of hash partitions - it yields the
> best/even distribution of keys).
>
>
> On Wed, Mar 11, 2009 at 2:18 PM, Keith Moore <kmoore_at_zephyrus.com> wrote:
>> Oracle 9.2.0.4
>> Solaris 8
>>
>> I have a database where creating an index on a particular table takes about
>> 25
>> minutes but if I create a similar index on the same table with the "PARALLEL
>> 3" clause it takes over 100 minutes.
>>
>> I ran a 10046 trace on both statements and the normal CREATE INDEX does
>> multiblock (scattered) reads, generally 1 Megabyte per read. The problem is
>> that the index created PARALLEL 3 does single block (sequential) reads.
>>
>> Can anyone explain why it would do single block reads? Is this a bug?
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 11 2009 - 18:21:20 CDT