Today, the good news is that I have time to write this blog post. The less good news is that our basement got flooded and I have to stay home. The bad news is, my current client does not allow remote work \ud83d\ude41
\nSo, blog post it is…<\/p>\n
There are a number of reasons why a SQL Plan Baseline might not get used. Here’s one I was not fully aware of until recently (although it makes perfect sense when you think about it): ALTER SESSION FORCE PARALLEL QUERY [PARALLEL n].
\nIn the simplest of cases the outcome whether a SQL Plan Baseline is used depends on the following:<\/p>\n
The base of the test case is a simple table that I’m going to select:<\/p>\n
\r\ncreate table t1\r\nas\r\nselect rownum id, 'ABC' text\r\nfrom dual connect by level <= 100\r\n;\r\n<\/pre>\nFor every combination of interest we run through the following procedure:
\n1) open and configure a new session for the parse environment on which the plan baseline is based
\n2) run this query “select * from t1”
\n3) create a fixed plan baseline for the generated plan
\n4) re-connect and configure the parse environment for the executing session
\n5) run query from step 2) and collect cursor information
\ndo steps 4) and 5) for ENABLE PARALLEL, FORCE PARALLEL, and FORCE PARALLEL n<\/p>\nTest 1:<\/strong> object DEGREE 1, parallel_degree_policy = manual<\/p>\n
\n
\n parsing session (SPB)
\/
executing session<\/center><\/td>\nenable (serial plan)<\/th>\n force (parallel plan)<\/th>\n force 4 (parallel plan)<\/th>\n<\/tr>\n \n enable (default)<\/th>\n used<\/td>\n not used (*3)<\/td>\n not used (*3)<\/td>\n<\/tr>\n \n force<\/th>\n not used (*1)<\/td>\n used<\/td>\n used<\/td>\n<\/tr>\n \n force 4<\/th>\n not used (*2)<\/td>\n used<\/td>\n used<\/td>\n<\/tr>\n<\/table>\n *1) Degree of Parallelism is 8 because of table property
\n*2) Degree of Parallelism is 4 because of session
\n*3) No note in the plan about DOP or baseline
\nSummary for test 1:
\nIf you have a serial plan in the baseline and use any force parallel on the session the plan baseline is not used and you get a parallel plan.
\nIf you have a parallel plan in the baseline and run the query on a session with ENABLE PARALLEL QUERY (default settings) the plan baseline is not used and you get a serial plan.<\/p>\nTest 2:<\/strong> object DEGREE 1, parallel_degree_policy = limited<\/p>\n
\n
\n parsing session (SPB)
\/
executing session<\/center><\/td>\nenable (serial plan)<\/th>\n force (parallel plan)<\/th>\n force 4 (parallel plan)<\/th>\n<\/tr>\n \n enable (default)<\/th>\n used<\/td>\n used<\/td>\n used (*3)<\/td>\n<\/tr>\n \n force<\/th>\n not used (*1)<\/td>\n used<\/td>\n used (*3)<\/td>\n<\/tr>\n \n force 4<\/th>\n not used (*2)<\/td>\n used<\/td>\n used<\/td>\n<\/tr>\n<\/table>\n *1) automatic DOP: Computed Degree of Parallelism is 2
\n*2) Degree of Parallelism is 4 because of session
\n*3) Degree of Parallelism is 2 because of hint
\nSummary for test 2:
\nIf you have a serial plan in the baseline and use any force parallel on the session the plan baseline is not used and you get a parallel plan.
\nNow that we allow for auto DOP the session with ENABLE PARALLEL QUERY can use parallel plans in plan baselines.<\/p>\nTest 3:<\/strong> object DEGREE DEFAULT, parallel_degree_policy = limited<\/p>\n
\n
\n parsing session (SPB)
\/
executing session<\/center><\/td>\nenable (serial plan (*4))<\/th>\n force (parallel plan)<\/th>\n force 4 (parallel plan)<\/th>\n<\/tr>\n \n enable (default)<\/th>\n used<\/td>\n used<\/td>\n used (*3)<\/td>\n<\/tr>\n \n force<\/th>\n not used (*1)<\/td>\n used<\/td>\n used (*3)<\/td>\n<\/tr>\n \n force 4<\/th>\n not used (*2)<\/td>\n used<\/td>\n used<\/td>\n<\/tr>\n<\/table>\n *1) automatic DOP: Computed Degree of Parallelism is 2
\n*2) Degree of Parallelism is 4 because of session
\n*3) Interestingly, there is no note about DOP in the plan at all. But it uses the plan baseline.
\n*4) automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
\nSummary for test 3:
\nIf you have a serial plan in the baseline and use any force parallel on the session the plan baseline is not used and you get a parallel plan.
\nAgain, as we allow for auto DOP the session with ENABLE PARALLEL QUERY can use parallel plans in plan baselines. The result is the same as Test 2 but some the notes in the plans differ.<\/p>\nTest 4:<\/strong> object DEGREE DEFAULT, parallel_degree_policy = limited, fake stats so DOP > 1 for all plan baselines<\/p>\n
\n
\n parsing session (SPB)
\/
executing session<\/center><\/td>\nenable (parallel plan (*1))<\/th>\n force (parallel plan)<\/th>\n force 4 (parallel plan)<\/th>\n<\/tr>\n \n enable (default)<\/th>\n used<\/td>\n used<\/td>\n used<\/td>\n<\/tr>\n \n force<\/th>\n used<\/td>\n used<\/td>\n used<\/td>\n<\/tr>\n \n force 4<\/th>\n used<\/td>\n used<\/td>\n used<\/td>\n<\/tr>\n<\/table>\n *1) automatic DOP: Computed Degree of Parallelism is 2
\nSummary for test 4:
\nNaturally, now that we always have parallel plans in the plan baselines and the object statistics call for auto DOP > 1 the plan baselines get used in all cases.<\/p>\nWhy did I do this? See, there’s this batch job with a SQL that has a SQL Plan Baseline on it (serial plan). Now, every once in a while the run-time of this batch job goes through the roof and every time this happens I see that the query does not use the baseline (v$sql.sql_plan_baseline is NULL). Also, next to different PLAN_HASH_VALUEs I noticed different OPTIMIZER_ENV_HASH_VALUEs. Checking the session settings V$SES_OPTIMIZER_ENV showed that “parallel_query_forced_dop” was set to “default”, which means “ALTER SESSION FORCE PARALLEL QUERY” was run previously on that session.
\nBut why is it not deterministic? The tool that runs all the batch jobs uses a connection pool, some job steps force parallel and some don’t. We haven’t been able to technically confirm this but everything points towards that this session property is not cleared to default when a connection gets reused. So, sometimes this batch job just gets unlucky by the session it gets from the connection pool.
\nThe solution: Adding second SQL Plan Baseline. This plan is a parallel plan with the same structure as the original serial plan. Now, either one of the plan baselines (serial or parallel plan) is being used depending on the session configuration.<\/p>\nFootnote:
\nWhen you use “FORCE PARALLEL QUERY” you might get a serial plan. You’ll see this in the plan notes: “automatic DOP: Computed Degree of Parallelism is 1”. Obviously, this would change some of above results.<\/p>\n","protected":false},"excerpt":{"rendered":"Today, the good news is that I have time to write this blog post. The less good news is that our basement got flooded and I have to stay home. The bad news is, my current client does not allow remote work \ud83d\ude41 So, blog post it is… There are a number of reasons why […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,13],"tags":[],"class_list":["post-329","post","type-post","status-publish","format-standard","hentry","category-internals","category-things-that-happen"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/329","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=329"}],"version-history":[{"count":8,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/329\/revisions"}],"predecessor-version":[{"id":344,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/329\/revisions\/344"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=329"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=329"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=329"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}