Do SPM baselines retain DOP?

[Rich J]

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

[Dominic Brooks]

Some old thoughts:
https://orastory.wordpress.com/2012/03/09/sql-patch-iii-plus-parallel-baselines/

Sent from my iPhone

On 3 Aug 2018, at 21:16, Rich J <***@society.servebeer.com<mailto:***@society.servebeer.com>> wrote:


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

[Rich J]

Post by Dominic Brooks
https://orastory.wordpress.com/2012/03/09/sql-patch-iii-plus-parallel-baselines/

Hey Dominic,

Perhaps it's my misunderstanding of SPM (likely) or maybe a bug
(hmmm...also likely), but I was able to successfully create a baseline
that preserved not only the "forced" parallelism, but the DOP as well.
I did use a slightly different method than you describe in your blog
post though. I first loaded the original plan into SPM using SQL_ID,
executed my parallel query with a session-specific
PARALLEL_DEGREE_LIMIE, then loaded that plan into SPM using it's SQL_ID
and PLAN_HASH_VALUE but with the original SQL's SQL_HANDLE from
DBA_SQL_PLAN_BASELINES. I then altered the new baseline to be fixed and
optionally dropped the original baseline.

It works, but I haven't seen anywhere it's documented that the parallel
plan sticks with the new baseline and, more importantly, the degree of
parallelism. It may help the optimizer in that the original SQL in my
case is doing four full table scans.

Thanks!
Rich