19c - SPM and Evolution phase

Nov 18, 2020 4:27PM

Hello everyone

Based on what we read / learned from this forum, SQL PLAN MANAGEMENT has 3 phases

Capture - Creation of BASELINE

Selection - Ensures only ACCEPTED plans are used

Evolution - Evaluation of UNACCEPTED plans and converting those to ACCEPTED plans ONLY if it shows performance improvement

A couple of question on this

#1) When we have a BASELINE for a particlar SQL, Oracle will only generate a new plan for that SQL if that SQL is HARD PARSED. Correct?

Or are there other criterias that could cause Oracle to generate new plans for the same SQL?

#2) In 11g, the plan evolution was manual and had to be done manually. In 12c, it then became part of one of the overnight job. What about 19c?

Database Tuning (MOSC)