RE: [EXTERNAL] Re: How Oracle sets PARALLEL_QUERY_DEFAULT_DOP

From: Hameed, Amir <amir.hameed_at_sleepnumber.com>
Date: Wed, 4 Aug 2021 15:15:06 +0000
Message-ID: <CH0PR14MB5012DEED0EE82D8A385231DCE0F19_at_CH0PR14MB5012.namprd14.prod.outlook.com>

Thank you for your replies. I validated that all parallel-related database parameter settings were the same across all RAC instances. What I did find was that for one of the indexes, DOP was set to 8, which I believe had been set as part of rebuilding that index. After resetting the DOP of the index the PARALLEL_QUERY_DEFAULT_DOP went away. I repeated the test and every time I set the DOP to a higher value and ran the statement, PARALLEL_QUERY_DEFAULT_DOP was reported in the optimizer environment for the SQL ID.

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Powell, Mark Sent: Friday, July 30, 2021 10:28 AM
Cc: ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org> Subject: [EXTERNAL] Re: How Oracle sets PARALLEL_QUERY_DEFAULT_DOP

CAUTION: External source

Amir, check all the database instance parameter settings for parallel such as if parallel_automatic_tuning and parallel_degree_policy.

Mark Powell
Database Administration
(313) 592-5148

---

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Laurentiu Oprea <laurentiu.oprea06_at_gmail.com<mailto:laurentiu.oprea06_at_gmail.com>> Sent: Friday, July 30, 2021 12:43 AM
To: amir.hameed_at_sleepnumber.com<mailto:amir.hameed_at_sleepnumber.com> <amir.hameed_at_sleepnumber.com<mailto:amir.hameed_at_sleepnumber.com>> Cc: ORACLE-L (oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>) <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: Re: How Oracle sets PARALLEL_QUERY_DEFAULT_DOP

Hello,

You need to check the indexes on the tables as well.

On Fri, Jul 30, 2021, 01:39 Hameed, Amir <amir.hameed_at_sleepnumber.com<mailto:amir.hameed_at_sleepnumber.com>> wrote:

Hi,

This is a three-node RAC environment running an Oracle EBS database. The database version is 11.2.0.4 and the OS is Solaris 11. The While investigating the execution plan of a SQL statement (a standard EBS statement), I ran the following statement for a particular SQL_ID:

SELECT    inst_id,

   sql_id,

   child_number,

   id,

   name,

   isdefault,

   value

FROM    gv$sql_optimizer_env

WHERE    sql_id = '&m_sql_id' AND

   isdefault = 'NO'

ORDER BY    inst_id,

   name

;

   INST_ID SQL_ID        CHILD_NUMBER         ID NAME                                     ISD VALUE

---------- ------------- ------------ ---------- ---------------------------------------- --- -------------------------

         1 308zt411qcsyq            0         49 _b_tree_bitmap_plans                     NO  false

                                    0         60 _fast_full_scan_enabled                  NO  false

                                    0         92 _like_with_bind_as_equality              NO  true

                                    0         25 _pga_max_size                            NO  2097152 KB

                                    0         18 _sort_elimination_cost_ratio             NO  5

                                    0        167 optimizer_secure_view_merging            NO  false

                                    0        273 parallel_force_local                     NO  true

                                    0        256 parallel_query_default_dop               NO  28



         3 308zt411qcsyq            0         49 _b_tree_bitmap_plans                     NO  false

                                    0         60 _fast_full_scan_enabled                  NO  false

                                    0         92 _like_with_bind_as_equality              NO  true

                                    0         25 _pga_max_size                            NO  2097152 KB

                                    0         18 _sort_elimination_cost_ratio             NO  5

                                    0        167 optimizer_secure_view_merging            NO  false

                                    0        273 parallel_force_local                     NO  true

15 rows selected.

I have checked all the tables involved in the query and none of them have any DOP set. I am curious to know how Oracle is setting this parameter?

Thanks,
Amir

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

Received on Wed Aug 04 2021 - 17:15:06 CEST