Re: How Oracle sets PARALLEL_QUERY_DEFAULT_DOP

From: Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
Date: Fri, 30 Jul 2021 07:43:57 +0300
Message-ID: <CA+riqSXR5mNWj5o6poVfOohLZ6Fnqm_xbMHXUM7JMpySfo+KAQ_at_mail.gmail.com>



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> 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 - 06:43:57 CEST

Original text of this message