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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>
Sent: Friday, July 30, 2021 12:43 AM
To: amir.hameed@xxxxxxxxxxxxxxx <amir.hameed@xxxxxxxxxxxxxxx>
Cc: ORACLE-L (oracle-l@xxxxxxxxxxxxx) <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxx<mailto:amir.hameed@xxxxxxxxxxxxxxx>> 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
