Discussion:
ONLINE INDEX CREATION
Ramsankar Cheruvattath
2018-09-13 15:49:31 UTC
Permalink
Hello

Does Oracle support building an index using both PARALLEL AND ONLINE clause?

I checked the 12c documentation below and the only restriction regarding
parallel is that it does not support parallel DML. There is no mention of
not supporting parallel index creation. 11g document is a lit bit more
vague.

I tried below, and it shows a parallel execution plan. However, parallelism
was not being used fro what I could see.

PLAN_TABLE_OUTPUT

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

SQL_ID f46mg3sxn40wq, child number 0

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

CREATE INDEX BI_ADD_FAILED_PIECE_SCAN_D_I7 ON

BI_ADD_FAILED_PIECE_SCAN_DLY(ORGN_FAC_SEQ_ID, DESTN_FAC_SEQ_ID,

ORGN_FAC_ZIP_3) INITRANS 10 NOLOGGING PARALLEL 32 LOCAL COMPRESS 3

ONLINE



Plan hash value: 3405290185



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

| Id | Operation | Name |
E-Rows |

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

| 0 | CREATE INDEX STATEMENT |
| |

| 1 | PX COORDINATOR |
| |

| 2 | PX SEND QC (RANDOM) | :TQ10000
| 749K|

| 3 | PX PARTITION LIST ALL |
| 749K|

| 4 | INDEX BUILD NON UNIQUE (LOCAL)| BI_ADD_FAILED_PIECE_SCAN_D_I7
| |

| 5 | SORT CREATE INDEX |
| 749K|

| 6 | TABLE ACCESS FULL | BI_ADD_FAILED_PIECE_SCAN_DLY
| 749K|

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



Note

-----

- Degree of Parallelism is 32 because of table property

- Warning: basic plan statistics not available. These are only collected
when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to 'ALL', at session or system
level



12c DOC

https://docs.oracle.com/database/121/SQLRF/statements_1012.htm#SQLRF00805

*ONLINE Clause*

Specify ONLINE to allow DML operations on the table or partition during
rebuilding of the index.

*Restrictions on Online Indexes *Online indexes are subject to the
following restrictions:

· Parallel DML is not supported during online index building. If
you specify ONLINE and subsequently issue parallel DML statements, then
Oracle Database returns an error.

· You cannot specify ONLINE for a bitmap join index or a cluster
index.

· For a nonunique secondary index on an index-organized table, the
number of index key columns plus the number of primary key columns that are
included in the logical rowid in the index-organized table cannot exceed
32. The logical rowid excludes columns that are part of the index key.

11g DOC


https://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN11730


Creating an Index Online

You can create and rebuild indexes online. This enables you to update base
tables at the same time you are building or rebuilding indexes on that
table. You can perform DML operations while the index build is taking
place, but DDL operations are not allowed. Parallel execution is not
supported when creating or rebuilding an index online.

Thanks
Ramsankar Cheruvattath (Ram)
Andy Sayer
2018-09-13 16:35:54 UTC
Permalink
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
Ramsankar Cheruvattath
2018-09-13 16:54:39 UTC
Permalink
Thanks Andrew! I already checked parallel query servers availability.
There were plenty available, but it was not using it.

Thanks
Ramsankar Cheruvattath (Ram)
4072096276
Post by Andy Sayer
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
Ramsankar Cheruvattath
2018-09-13 17:31:34 UTC
Permalink
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> @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
***@HOST1 (TNS SYS$USERS WAITED 0 ***@HOST1
(TNS


V1-V3)
SHORT TIME V1-V3)





SQL> @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 <
Post by Ramsankar Cheruvattath
Thanks Andrew! I already checked parallel query servers availability.
There were plenty available, but it was not using it.
Thanks
Ramsankar Cheruvattath (Ram)
4072096276
Post by Andy Sayer
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
Jonathan Lewis
2018-09-13 18:21:48 UTC
Permalink
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-***@freelists.org <oracle-l-***@freelists.org> on behalf of Ramsankar Cheruvattath <***@gmail.com>
Sent: 13 September 2018 18:31:34
To: ***@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> @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 ***@HOST1 (TNS SYS$USERS WAITED 0 ***@HOST1 (TNS
V1-V3) SHORT TIME V1-V3)


SQL> @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 <***@gmail.com<mailto:***@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 <***@gmail.com<mailto:***@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
Ramsankar Cheruvattath
2018-09-13 18:43:39 UTC
Permalink
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
Post by Jonathan Lewis
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
________________________________________
Sent: 13 September 2018 18:31:34
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.
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
V1-V3)
SHORT TIME V1-V3)
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 <
Thanks Andrew! I already checked parallel query servers availability.
There were plenty available, but it was not using it.
Thanks
Ramsankar Cheruvattath (Ram)
4072096276
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
Jonathan Lewis
2018-09-14 06:53:28 UTC
Permalink
This post might be inappropriate. Click to display it.
Ramsankar Cheruvattath
2018-09-14 11:30:59 UTC
Permalink
Thanks Jonathan. I understand. I will try the things you have suggested.

Thanks
Ramsankar Cheruvattath (Ram)
4072096276
Post by Jonathan Lewis
Ramsankar,
It's always a little difficult to work out exactly why things appear to be
misbehaving without having all the details in front of you - and it's not
always easy to know what details to ask for. The full execution plan -
including the "parallel" and "partitioned" columns would probably help. The
parallel would show us any P->P, S->P etc distribution.
Given the "45 minutes then it went parallel" sounds like you're doing the
tablescan serially and using parallel slaves to create the index. But maybe
that's because you're asking Oracle to create a parallel index, not to run
the create index as a parallel statement.
Two Options - I'd be interested in know if either, or both, work.
1) create /*+ parallel(32) */ index ...
2) Alter table XXX parallel (degree 32) ... before trying "create index .... parallel 32"
I don't really want to try modelling what your doing because there are so
many parallel parameters that produce very different effects, and one
effect would be parallelism not appearing for very small objects, another
would be the degree and behaviour varying with the number of populated
partitions (and their current stats).
Regards
Jonathan Lewis
Regards
Jonathan Lewis
________________________________________
Sent: 13 September 2018 19:43:39
To: Jonathan Lewis
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 <
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
________________________________________
Sent: 13 September 2018 18:31:34
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.
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
V1-V3)
SHORT TIME V1-V3)
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 <
Thanks Andrew! I already checked parallel query servers availability.
There were plenty available, but it was not using it.
Thanks
Ramsankar Cheruvattath (Ram)
4072096276
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
Ramsankar Cheruvattath
2018-09-14 11:35:39 UTC
Permalink
We are on 12.1.0.2

Thanks
Ramsankar Cheruvattath (Ram)
4072096276
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.
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
________________________________________
Sent: 13 September 2018 19:43:39
To: Jonathan Lewis
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 <
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
________________________________________
Sent: 13 September 2018 18:31:34
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.
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
V1-V3)
SHORT TIME V1-V3)
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 <
Thanks Andrew! I already checked parallel query servers availability.
There were plenty available, but it was not using it.
Thanks
Ramsankar Cheruvattath (Ram)
4072096276
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
Ramsankar Cheruvattath
2018-09-14 11:41:36 UTC
Permalink
At the very least, this and some of the other responses that I have seen
confirms that under normal behavior both PARALLEL and ONLINE works together
well. I have a SR opened with Oracle and I am getting different answers at
different times from different analysts as to whether an ONLINE index
creation can be done in parallel. That is why I decided to post the
question here. Thank you for all the responses.

Thanks
Ramsankar Cheruvattath (Ram)
4072096276
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.
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
________________________________________
Sent: 13 September 2018 19:43:39
To: Jonathan Lewis
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 <
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
________________________________________
Sent: 13 September 2018 18:31:34
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.
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
V1-V3)
SHORT TIME V1-V3)
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 <
Thanks Andrew! I already checked parallel query servers availability.
There were plenty available, but it was not using it.
Thanks
Ramsankar Cheruvattath (Ram)
4072096276
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
Stefan Koehler
2018-09-14 10:28:17 UTC
Permalink
Hello Ram,
you can trace the PX behavior by setting "_px_trace" (e.g. SQL> alter session set "_px_trace"=high,all;).

I have also written a blog post about troubleshooting PX several years ago: https://blogs.sap.com/2014/02/10/oracle-troubleshooting-parallel-executions-px-what-the-heck-why-is-the-sql-not-executed-in-parallel/

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Post by Jonathan Lewis
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.
--
http://www.freelists.org/webpage/oracle-l
Ramsankar Cheruvattath
2018-09-14 11:37:11 UTC
Permalink
Thanks Stefan. I will try that.

Thanks
Ramsankar Cheruvattath (Ram)
4072096276
Post by Stefan Koehler
Hello Ram,
you can trace the PX behavior by setting "_px_trace" (e.g. SQL> alter
session set "_px_trace"=high,all;).
I have also written a blog post about troubleshooting PX several years
https://blogs.sap.com/2014/02/10/oracle-troubleshooting-parallel-executions-px-what-the-heck-why-is-the-sql-not-executed-in-parallel/
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Post by Ramsankar Cheruvattath
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.
Post by Ramsankar Cheruvattath
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.
Loading...