Oracle DB version: 19.11
OS: RHEL 8.9
I have a Siebel production database which has the following setting
SQL> alter session set container = PDB_SIEBEL;
Session altered.
SQL> show parameter baseline
PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- -------
optimizer_capture_sql_plan_baselines boolean TRUE
optimizer_use_sql_plan_baselines boolean TRUE
SQL>
The default for optimizer_capture_sql_plan_baselines is FALSE.
For all the other production PDBs (around 60) in my shop, this parameter is set to FALSE.
From what I understand, with optimizer_capture_sql_plan_baselines set to TRUE, when a new query is executed, a baseline for that query is auto captured into the SQL Plan Baseline. From then onwards, the plan in the baseline is used.
This is in contrast to the usual way of optimizer generating and using the execution plans based on the optimizer statistics.
Is this setting recommended ?
This database was migrated from 12.1 standalone to 19c multitenant architecture 3 years back and the DBAs who did the migration have all left.
And we have at least one performance issue (SQL needing tuning) per week in Siebel.
My DBA colleague said that these baselines are not entirely static and they evolve periodically bringing a new plan. But, still, is setting optimizer_capture_sql_plan_baselines to TRUE a good idea ?