Hi Charles,
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.
Charles Schultz <sacrophyte@xxxxxxxxx> 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. :)
