Re: ONLINE INDEX CREATION

We are on 12.1.0.2

Thanks
Ramsankar Cheruvattath (Ram)
4072096276

On Fri, Sep 14, 2018 at 5:03 AM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> Actually it only took about 8 minutes to run up a quick model and test the
> effecs.
> create /*+ parallel(N) */ index ...
>
> and
>
> create index parallel N local
>
> took the same execution path and degree on a small test (10M rows, 4 hash
> partitions, N=4 and N = 3).
> The only difference was that the second option left the index with degree
> N after creation.
>
> Which version of 12, by the way - I was running 12.2.0.1.
>
> Another important thought:
> If there was a 45 minutes pause before parallel slaves started then what
> was the QC reporting as work done or swait events for teh 45 minutes ?
>
> Possibilities - you've got parallel querying queueing in place and your
> statement was queued; or it was doing an insanely huge amount of dynamic
> sampling (though it ddn't show in the plan notes) before running the create.
>
> PX Deq: Execution Msg
> is a PX slave waiting for instructions - and a common instruction it would
> be waiting for is "it's time to die", but that specific unlikely to be a
> long-lasting wait in the case of your CREATE INDEX which (if my model is
> correct) although - if you really to have 2005 partitions and got 64 PX
> slaves and they shared partitions properly then at some point you could
> have had 21 slaves working on the last 21 partitions (2005 = 31 * 64 + 21)
> while the other 43 were in waiting for the "die" message.
>
> Regards
> Jonathan Lewis
>
>
>
>
> ________________________________________
> From: Ramsankar Cheruvattath <ram.cheruvattath_at_gmail.com>
> Sent: 13 September 2018 19:43:39
> To: Jonathan Lewis
> Cc: andysayer_at_gmail.com; Oracle Mailing List
> Subject: 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<mailto: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<mailto:oracle-l-bounce_at_freelists.org>
> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on
> behalf of Ramsankar Cheruvattath <ram.cheruvattath_at_gmail.com<mailto:
> ram.cheruvattath_at_gmail.com>>
> Sent: 13 September 2018 18:31:34
> To: andysayer_at_gmail.com<mailto: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><mailto:
> 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><mailto: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