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

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Wed, 15 Jul 2015 18:48:26 -0500
Message-ID: <CAPZQniVZMLx3OrY+Eeq=4hSS5TmNanTU0N7QFA7tAOmiUtiNUw_at_mail.gmail.com>

Stefan, this was exceptionally helpful in getting me a ton of information. Almost too much. :)

Thanks to your suggestion of "_px_trace"=high,all, I have narrowed down to *where* the problem occurs, but it does not yet help me understand *why* it occurs.

*Example where DOP of 100 is denied:*
2015-07-15 15:35:20.002663*:PX_Messaging:kxfp.c_at_18900:kxfpclinfo():

        inst(load
:user:pct:fact:servtarget:queued:started:granted:active:active(free)): aff

        1 (2.00:0 :100:50 :8 :0 :0 :0 :0 :0 )

        defDOP=2, tpc=2
2015-07-15 15:35:20.002663*:PX_Messaging:kxfp.c_at_17863:kxfpLoadAdDOP():

         we are over target...

*Example where DOP of 100 is successfully granted:* 2015-07-15 14:47:31.703953*:PX_Messaging:kxfp.c_at_18900:kxfpclinfo():

        inst(load
:user:pct:fact:servtarget:queued:started:granted:active:active(free)): aff

        1 (0.00:0 :100:100 :8 :0 :0 :0 :0 :0 )

        defDOP=2, tpc=2
2015-07-15 14:47:31.703953*:PX_Messaging:kxfp.c_at_17967:kxfpLoadAdDOP():

         granted dop: 100

The only major difference is the load, pct and fact. But I do not understand what those figures mean, nor how they affect the determination that "we are over target" in the first example. Any ideas?

On Wed, Jul 15, 2015 at 2:06 PM, Stefan Koehler <contact_at_soocs.de> wrote:

> 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.
>
> 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

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

Received on Thu Jul 16 2015 - 01:48:26 CEST