Re: ONLINE INDEX CREATION

I am running it again in a lower environment, this time without ONLINE. These are servers with 288 cores and 4TB SGA. Parallel servers setting all look good. Still not running in PARALLEL. I will be doing more testing without COMPRESS etc.

Still interested to hear from others if they have an suggestion/queries I can use to troubleshoot this. I am also gathering a 10046 trace.

Enter value for sql_id: cwqxdrvryzjrv

Enter value for child: 0

old 1: select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD, format=>'ALLSTATS LAST +outline'))

new 1: select * from TABLE(dbms_xplan.display_cursor('cwqxdrvryzjrv', 0, format=>'ALLSTATS LAST +outline'))

PLAN_TABLE_OUTPUT

SQL_ID cwqxdrvryzjrv, child number 0

CREATE INDEX USER1.BI_ADD_FAILED_PIECE_SCAN_D_I7 ON USER1.BI_ADD_FAILED_PIECE_SCAN_DLY(ORGN_FAC_SEQ_ID, DESTN_FAC_SEQ_ID, ORGN_FAC_ZIP_3) INITRANS 10 NOLOGGING PARALLEL 64 LOCAL COMPRESS 3 Plan hash value: 3405290185

| Id | Operation | Name |
Starts | E-Rows | A-Rows | A-Time | Buffers |

| 0 | CREATE INDEX STATEMENT |

                          |      0 |        |      0 |00:00:00.01 |       0

| 1 | PX COORDINATOR |
| 0 | | 0 |00:00:00.01 | 0 |

| 2 | PX SEND QC (RANDOM) | :TQ10000
| 0 | 4256K| 0 |00:00:00.01 | 0 |

| 3 | PX PARTITION LIST ALL |
| 1 | 4256K| 2005 |00:02:09.31 | 24042 |

| 4 | INDEX BUILD NON UNIQUE (LOCAL)| BI_ADD_FAILED_PIECE_SCAN_D_I7
| 2005 | | 2005 |00:00:00.64 | 24042 |

| 5 | SORT CREATE INDEX |
| 2005 | 4256K| 0 |00:00:00.63 | 24042 |

| 6 | TABLE ACCESS FULL | BI_ADD_FAILED_PIECE_SCAN_DLY
| 2005 | 4256K| 0 |00:00:00.62 | 24042 |

Note

• Degree of Parallelism is 64 because of table property
• estimated index size: 272G bytes

25 rows selected.

SQL> _at_racsys

ID    SID    SERIAL# USERNAME   LOGON_TIME      ELP_MT
SQL_EXEC_START        SPID     SQL_ID         CHD EVENT
MODULE                         SERVICE_NAME STATE      WAIT_TIME_SEC PROGRAM

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

  1   1038      53781 SYS        13-SEP-18 11:05     72 13-SEP-18
11:05       182996   cwqxdrvryzjrv    0 On CPU / runqueue
sqlplus_at_HOST1 (TNS     SYS$USERS    WAITED                 0 sqlplus_at_HOST1

                                 V1-V3)
SHORT TIME               V1-V3)

SQL> _at_ashhistory

Enter value for sid: 1038

Enter value for serial: 53781

Enter value for inst_id: 1

old 3: WHERE SESSION_ID=&SID and SESSION_SERIAL#=&serial and inst_id=&inst_id

new 3: WHERE SESSION_ID=1038 and SESSION_SERIAL#=53781 and inst_id=1

Enter value for minute: 72

old 4: and SAMPLE_TIME>=(sysdate-&minute/(24*60))

new 4: and SAMPLE_TIME>=(sysdate-72/(24*60))

SQL_ID        EVENT                                            SECONDS

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

cwqxdrvryzjrv gc current block 2-way                                 1

              gc current grant busy                                  1

              enq: TS - contention                                   5

              enq: FB - contention                                  12

              enq: HW - contention                                  14

              gc current grant 2-way                                33

              row cache lock                                        59

              enq: IV -  contention                                283

              On CPU                                              3860

                                                                  4268

10 rows selected.

Thanks
Ramsankar Cheruvattath (Ram)
4072096276

On Thu, Sep 13, 2018 at 12:54 PM Ramsankar Cheruvattath < ram.cheruvattath_at_gmail.com> wrote:

> Thanks Andrew!  I already checked parallel query servers availability.
> There were plenty available, but it was not using it.
>
> Thanks
> Ramsankar Cheruvattath (Ram)
> 4072096276
>
>
> On Thu, Sep 13, 2018 at 12:36 PM Andy Sayer <andysayer_at_gmail.com> wrote:
>
>> Yes, you can do it parallel (and in this circumstance it’s parallel query
>> + parallel DDL) and online. The main difference is that your reading of the
>> table can’t use direct path reads when it’s online.
>>
>> If you can’t see parallel sessions being used then it’s likely because
>> there weren’t any free to be used. If you have diagnostic+tuning pack
>> licenced you can run the query on the bottom of
>>
>> https://ctandrewsayer.wordpress.com/2017/10/19/how-many-rows-were-insertedupdateddeleted-in-my-merge/
>> against the sql_id to see the reported reason for DOP downgrade.
>>
>> That said, direct path read for parallel query on a partitioned table
>> won’t necessarily happen anyway due to some bugs that have only recently
>> been addressed.
>>
>> Hope that helps,
>> Andrew
>>
>

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