Re: Curiosity: single-column index on sparse data cannot be built in parallel

> I see "Parallel operations downgraded 75 to 99 pct" (according to the AWR report). Correlating this to v$pq_tqstat, I see 302 rows during "off
> hours" (runs in 5 minutes) but only 8 rows during working hours (4 Producers, 2 Consumers, 1 Ranger and 1 Final QC Consumer), taking 90 minutes this
> morning.

I think the best way to determine what is happening in your case would be a PX trace. I have written a blog post about this PX trace facility (in case of resource manager downgrades), but the trace itself also reveals the other scenarios: http://tinyurl.com/oxcmyp2

Maybe you can trace the root cause for the downgrade in your system with that.

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Charles Schultz <sacrophyte_at_gmail.com> hat am 15. Juli 2015 um 19:27 geschrieben:
>
> Jonathan, your blog post looks quite lucid and helpful - I'll start to utilize some of your observations in my ongoing testss. Thanks for wrapping
> it up in a way for the rest of us to see.
>
> Just an update on the little mystery. It seems when I create the index during "off hours", the kernel allocates plenty of parallel slaves, but when
> I run after 9:00 am and before 4:00 pm local time, I see "Parallel operations downgraded 75 to 99 pct" (according to the AWR report). Correlating
> this to v$pq_tqstat, I see 302 rows during "off hours" (runs in 5 minutes) but only 8 rows during working hours (4 Producers, 2 Consumers, 1 Ranger
> and 1 Final QC Consumer), taking 90 minutes this morning.
>
> It is possible this is pure coincedence. I will be doing more tests to see if this pattern holds up. One other factor is that this database is
> currently on ZFS (solaris) with a snapshot (copy on write), and the back-end SAN is shared across our enterprise for all developement work. I just
> find it exceptionally odd that this one index seems to be the sticking point.
>
> Thanks for helping me think this out loud. :)

--
http://www.freelists.org/webpage/oracle-l