Re: How Oracle sets PARALLEL_QUERY_DEFAULT_DOP

From: Powell, Mark <mark.powell2_at_dxc.com>
Date: Fri, 30 Jul 2021 14:28:06 +0000
Message-ID: <BN6PR01MB2547A9A13F83AA7AE4F7822CCEEC9_at_BN6PR01MB2547.prod.exchangelabs.com>

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 <oracle-l-bounce_at_freelists.org> on behalf of Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> Sent: Friday, July 30, 2021 12:43 AM
To: amir.hameed_at_sleepnumber.com <amir.hameed_at_sleepnumber.com> Cc: ORACLE-L (oracle-l_at_freelists.org) <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 Fri Jul 30 2021 - 16:28:06 CEST