Creating Spatial Index is doing Full Table Scan in Parallel when parallel degree is not defined - 12c

(Doc ID 2214010.1)

Last updated on JANUARY 19, 2017

Applies to:

Oracle Spatial and Graph - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

Creating a Spatial index in serial mode is doing a full table scan in parallel.

Create a Spatial index with session level tracing .
 

alter session set timed_statistics=true;
alter session set max_dump_file_size = unlimited;
alter session set events'10046 trace name context forever, level 12';

CREATE INDEX cola_spatial_idx ON cola_markets(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

alter session set events '10046 trace name context off';

 

Raw Trace file

<>

PARSING IN CURSOR #140644726014048 len=79 dep=1 uid=129 oct=3 lid=129 tim=7223033924886 hv=3696963168 ad='652d0280' sqlid='16vmugzf5qbm0'
SELECT /*+ PARALLEL(a) */ a.rowid, a."SHAPE" FROM "SPATIAL_TEST".COLA_MARKETS a
END OF STMT
PARSE #140644726014048:c=2000,e=2097,p=0,cr=48,cu=0,mis=1,r=0,dep=1,og=1,plh=623999392,tim=7223033924886
WAIT #140644726014048: nam='PX Deq: Join ACK' ela= 148 sleeptime/senderid=268500992 passes=1 p3=1658591056 obj#=-1 tim=7223033925467
WAIT #140644726014048: nam='PX Deq: Join ACK' ela= 101 sleeptime/senderid=268500993 passes=1 p3=1658582608 obj#=-1 tim=7223033925654
WAIT #140644726014048: nam='PX Deq: Join ACK' ela= 58 sleeptime/senderid=268500994 passes=1 p3=1658586832 obj#=-1 tim=7223033925784
WAIT #140644726014048: nam='PX Deq: Join ACK' ela= 46 sleeptime/senderid=268500995 passes=1 p3=1658589648 obj#=-1 tim=7223033925875

<>

 

Formatted Trace - tkprof

<>

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 129 (SPATIAL_TEST) (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 PX COORDINATOR (cr=3 pr=0 pw=0 time=20179 us)
0 0 0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=2 size=14 card=1)
0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=2 size=14 card=1)
0 0 0 TABLE ACCESS FULL COLA_MARKETS (cr=0 pr=0 pw=0 time=0 us cost=2 size=14 card=1)

<>

Note : Auto parallelism is not configured and no parallel degree defined while creating index.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms