Oracle ver 19.14 SQL Profile exists but was ignored after upgrading from 12c to 19.14.
Hi
After upgrade to 19c oracle ignore all created profiles and uses bad plans
For example '4xn5rfh1rr1x5' has profile with good PHV 1874713107 but this profile is ignored and oracle uses PHV 226519249
Why it happened?
WITH
p AS (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE sql_id = TRIM('4xn5rfh1rr1x5')
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE sql_id = TRIM('4xn5rfh1rr1x5')
AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
SUM(elapsed_time)/SUM(executions) avg_et_secs