Database Tuning (MOSC)

MOSC Banner

Oracle ver 19.14 SQL Profile exists but was ignored after upgrading from 12c to 19.14.

edited Apr 22, 2022 10:10AM in Database Tuning (MOSC) 4 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center