How Oracle sets PARALLEL_QUERY_DEFAULT_DOP
From: Hameed, Amir <amir.hameed_at_sleepnumber.com>
Date: Thu, 29 Jul 2021 22:38:39 +0000
Message-ID: <CH0PR14MB5012CF52884C4094A7612C46E0EB9_at_CH0PR14MB5012.namprd14.prod.outlook.com>
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
;
Date: Thu, 29 Jul 2021 22:38:39 +0000
Message-ID: <CH0PR14MB5012CF52884C4094A7612C46E0EB9_at_CH0PR14MB5012.namprd14.prod.outlook.com>
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-lReceived on Fri Jul 30 2021 - 00:38:39 CEST