Re: ONLINE INDEX CREATION

Hi Jonathan

Thanks for the response. So I noticed that after running for about 45 minutes or so, it started using parallel processing. This is in both cases, PARALLEL with and without ONLINE. However, all of the parallel slaves are waiting on "PX Deq: Execution Msg".

Could this be a BUG?

SQL> list

  1 SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",

  2 DEGREE "Degree", REQ_DEGREE "Req Degree"

  3* FROM GV$PX_SESSION ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET SQL> /      QCSID SID Inst Group Set Degree Req Degree

• ---------- ---------- ---------- ---------- ---------- ----------

       997 2465 1 1 1 2 2

       997 3865 2 1 1 2 2

       997 997 1

      1038 2803 1 1 1 64 64

      1038 2817 1 1 1 64 64

      1038 2494 1 1 1 64 64

      1038 2858 1 1 1 64 64

      1038 2873 1 1 1 64 64

      1038 2885 1 1 1 64 64

      1038 2901 1 1 1 64 64

      1038 2913 1 1 1 64 64

      1038 2929 1 1 1 64 64

      1038 2943 1 1 1 64 64

      1038 2958 1 1 1 64 64

      1038 2969 1 1 1 64 64

      1038 2997 1 1 1 64 64

      1038 3026 1 1 1 64 64

      1038 3040 1 1 1 64 64

      1038 3054 1 1 1 64 64

      1038 3096 1 1 1 64 64

      1038 3124 1 1 1 64 64

      1038 3166 1 1 1 64 64

      1038 3223 1 1 1 64 64

      1038 3249 1 1 1 64 64

      1038 3278 1 1 1 64 64

      1038 3305 1 1 1 64 64

      1038 3334 1 1 1 64 64

      1038 3376 1 1 1 64 64

      1038 3404 1 1 1 64 64

      1038 3434 1 1 1 64 64

      1038 3473 1 1 1 64 64

      1038 3488 1 1 1 64 64

      1038 3516 1 1 1 64 64

      1038 3544 1 1 1 64 64

      1038 3557 1 1 1 64 64

      1038 2438 1 1 1 64 64

      1038 2466 1 1 1 64 64

      1038 2480 1 1 1 64 64

      1038 2326 1 1 1 64 64

      1038 2493 1 1 1 64 64

      1038 2508 1 1 1 64 64

      1038 2535 1 1 1 64 64

      1038 2522 1 1 1 64 64

      1038 2565 1 1 1 64 64

      1038 2606 1 1 1 64 64

      1038 2620 1 1 1 64 64

      1038 2634 1 1 1 64 64

      1038 2578 1 1 1 64 64

      1038 2648 1 1 1 64 64

      1038 2703 1 1 1 64 64

      1038 2718 1 1 1 64 64

      1038 2732 1 1 1 64 64

      1038 2760 1 1 1 64 64

      1038 2789 1 1 1 64 64

      1038 2801 1 1 1 64 64

      1038 2816 1 1 1 64 64

      1038 2845 1 1 1 64 64

      1038 2857 1 1 1 64 64

      1038 2871 1 1 1 64 64

      1038 2886 1 1 1 64 64

      1038 2914 1 1 1 64 64

      1038 2928 1 1 1 64 64

      1038 2941 1 1 1 64 64

      1038 2312 1 1 1 64 64

      1038 2970 1 1 1 64 64

      1038 2984 1 1 1 64 64

      1038 2745 1 1 1 64 64

      1038 1038 1

Thanks
Ramsankar Cheruvattath (Ram)
4072096276

On Thu, Sep 13, 2018 at 2:21 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> 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
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Ramsankar Cheruvattath <ram.cheruvattath_at_gmail.com>
> Sent: 13 September 2018 18:31:34
> To: andysayer_at_gmail.com
> Cc: Oracle Mailing List
> Subject: 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 (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