Do SPM baselines retain DOP?

From: Rich J <rjoralist3_at_society.servebeer.com>
Date: Fri, 03 Aug 2018 14:40:54 -0500
Message-ID: <4332737f4b77f3441a809f8c2e1031a1_at_society.servebeer.com>



Hey all,

In 11.2.0.3 I have a vendor-supplied SQL that needs temporary band-aiding. I'd like to have it run as a PQ, but don't want it to run with the default DOP of 32 for this instance. Enter SPM baselines.

I've identified the SQL ID and loaded it into SPM from cursor cache. I then artificially set PARALLEL_DEGREE_LIMIT to "4" for a different session, ran the SQL with a PARALLEL hint, and confirmed the parallel limit via V$PQ_SESSSTAT. I then loaded that plan into SPM, altered the original baseline to use it and set that plan to "fixed".

I then verified the original statement in a new session uses only 4 PQ slaves, again by using V$PQ_SESSSTAT. Yay, it works!

However, is this specifically documented anywhere? I couldn't find any docs where it stated a plan's DOP was retained in an SPM baseline or if it could be overridden by session settings.

Thoughts?

TIA,
Rich

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Aug 03 2018 - 21:40:54 CEST

Original text of this message