Re: ONLINE INDEX CREATION

The fact that your A-rows for the partition table scans shows zero (on allstats last) suggests that you've got parallel execution slaves executing the tablescans. For parallel queries you shouldn't use LAST as the last thing to execute the statement is the query coordinator. If the 2 minutes and 9 seconds is to be trusted it seems likely that you got some parallelism somewhere. How are you checking ? Your output doesn't tell us much when you don't show us how you're querying - but shouldn't you be looking for the QC_% columns in ASH to check whether your statement ran parallel ?

Regards
Jonathan Lewis

| 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 (TNS
                                                                                                                                    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<mailto: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<mailto: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