SQL Performance (MOSC)

MOSC Banner

SQL changes execution plan

edited Mar 25, 2019 5:02AM in SQL Performance (MOSC) 9 commentsAnswered

Hello.

DB is 12.2.0.1 SE

OS is RHEL 7

I have a SQL that changed execution plan to take a bad one 4 times during the last 2 weeks.

Everytime it takes a bad plan, I run this and it immediately takes the plan I want:

declare

pls number;

begin

pls := dbms_spm.load_plans_from_cursor_cache( sql_id => 'theSQLID, plan_hash_value => thePlanHashValue, fixed=> 'YES', enabled => 'YES');

dbms_output.put_line ('Resultat : ' || pls);

end;

/

But when I look into DBA_SQL_PLAN_BASELINES,  I can see that the column last execution is empty, which means the baseline is not used.

Parameter

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