Jonathan,
Thanks for the continued feedback, this is excellent information and
insight. I will be running more tests today, since my test late last night
proved that sometimes the index can indeed be built using parallel slaves.
As you pointed out, sometimes my query against v$pq_tqstat returns way "too
many" rows - even though I only ever request parallel 100, sometimes I get
302 rows returned. I have not yet analyzed the rows (but I did save them in
an output file).
And yes, we are licensed for AWR, and I have started to take snapshots
between each test to better isolate the stats. Once I reproduce the problem
(that's always fun, trying to get a bad run), I will take a closer look at
those "parallel downgraded" messages. Your observation about most slaves
having nothing to do does make sense, but I have a hard time explaining why
it runs in 4 with parallel slaves and 50+ minutes with no apparent parallel
slaves running.
More to follow later in the day. Thanks again.
On Tue, Jul 14, 2015 at 11:48 PM, Jonathan Lewis <
jonathan@xxxxxxxxxxxxxxxxxx> wrote:
Charles,
Apologies for the delay - I had to shut down for the day before your reply
came through.
I've written a blog note about the anomaly - planned to re-read it and
polish it a little this morning.
Your non-null data is 3% of your index, and a parallel build would mean
RANGE distribution of the data, so if your build ran anything less than
parallel 33 (approx) the point would come where only one slave (in the
second set) would be doing anything because it would be the one that got
all the real data and had to sort and build. That's why you (I believe)
you might see a point where only one process seemed to be doing any work.
There is still benefit in running parallel, of course, since you have to
scan and discard the 97% of the data that is NULL and can't go into the
index.
The anomaly is that when I examined v$pq_tqstat after creating my 310,000
row index from my 10,000,000 row table (running parallel 4) the first set
of slaves had passed 10 million rows to the second set of slaves whiich
means they must have been passing all the (null, rowid) entries rather than
discarding them.
Are you licensed to dig back into the ASH history ? Can you find out if
the one running process was a Pnnn process or a normal shadow process ? If
it was a normal shadow the problem might simply be that at the moment the
create index started all the PX slaves were involved in building other
indexes. If you can run an AWR report for the time around the start of
build you might get a clue from the statistics about "Parallel operations
downgraded to ..."
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
------------------------------
*From:* Charles Schultz [sacrophyte@xxxxxxxxx]
*Sent:* 15 July 2015 01:05
*To:* Jonathan Lewis
*Cc:* ORACLE-L
*Subject:* Re: Curiosity: single-column index on sparse data cannot be
built in parallel
I didn't check $pq_tqstat, but I show results when I next do the test.
I say it was not running in parallel because only one session was active
via Enterprise Manager (yeah, I was being lazy, I know) and the creation
took over 1 hour the first test, 51 minutes the next test, whereas all my
other indexes (mix of local and non-partitioned) on the same table were
taking less than 20 minutes (some less than 10).
Jonathan, can you elaborate a little more on the "interesting anomaly"?
:) It is entirely possible I am hitting that, but would like to learn more.
As far as parallel 100, I was just trying to open the doors to see where
my bottlenecks are. The extent sizes are included in the index ddl (5m);
again, I don't have issues with the other indexes, but there is something
about this being on a single column, so you may be on the right track with
large extents and small volumes of data per slave. You are making me
curious now. :) I love the anthromorphic angle on "refusing to cooperate".
*grin*
PS - in case it is not obvious, this is a vendor supplied table and
index.
PS2 - tests currently executing on Sun T5440, for those that care about
such things.
PS3 - as to "why"; I got a lot of indexes to build in a short time, so I
am looking for the bounds. :)
On Tue, Jul 14, 2015 at 4:12 PM, Jonathan Lewis <
jonathan@xxxxxxxxxxxxxxxxxx> wrote:
I'm not going to try for 500M rows, but I just built a table with 10M
rows with your pattern of data and ran a parallel create index.
There is an interesting anomaly to the create index (11.2.0.4) that might
make the build look like a serial build - how are you showing that the
build isn't parallel ?
I queried v$pq_tqstat after the build.
Parallel 100 seems a little optimistic for such a "small" index - 17M
rows at about 13 bytes per row gives about 220MB or 2MB per slave to build.
What's your default extent size - perhaps there's something about large
extents and small volumes of data per slave that makes Oracle refuse to
co-operate.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Charles Schultz [sacrophyte@xxxxxxxxx]
*Sent:* 14 July 2015 21:59
*To:* ORACLE-L
*Subject:* Curiosity: single-column index on sparse data cannot be built
in parallel
Good day,
I am trying to find the technical reason for why Oracle cannot use
parallel slaves to build a single-column index on a sparse column with few
distinct values:
F COUNT(*)
- ----------
538937561
Y 51464
N 17486819
Just by playing around, I discovered that if I put this column as the
leading edge on an index with many columns, it can be built in parallel.
According to the relevant documentation
<http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel007.htm#i1009131>,
I would expect that the base table is sampled, but perhaps the random
sampling returns 0 keys due to the sparse nature of the column?
Facts:
Oracle Enterprise Edition 11.2.0.4
table is partitioned, but not on this key (lol)
parallel_max_servers=3600
ddl extracted via datapump:
CREATE INDEX "FIMSMGR"."FGBTRND_ENCD_INDEX" ON "FIMSMGR"."FGBTRND"
("FGBTRND_DEFER_GRANT_IND")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 5242880 NEXT 5242880 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "FIN_LARGE_INDX" PARALLEL 100 ;
--
Charles Schultz
--
Charles Schultz
