When applying PSEs, PSUs, BP's, Etc the only thing that worries the customer
is if a regression will happen, and in particular, caused by an optimizer fix.
They do not know if a regression may happen or not because they have no way
to know if any of the CBO fixes in the bundle apply to their queries.
There is no record, not even in the 10053, that a fix was applied to a SQL or
that it will be applied if enabled.
By a different motivation but with same feeling of "unknown", when a system
is going to be migrated to exadata the cus would want to know if smart scan
would apply to their SQLs and the solution was to provide cell_offload_plan_display .
Simil to cell_offload_plan_display there should be a feature such that :
1. When enabled at query level should show a list of fixes ,and all optimizer
features in general, were applied to the SQL or that were not applied because
the fix is disabled (this is to be "Eligible". Means "I reach code where I
consider to use it or not and decided not because it is off.")
This should be enabled explicitly by the user (not by EXPLAIN PLAN because
it has limitations Or make it in EXPLAIN PLAN and finally someone remove all
the limitations it has and make it produce a true output, but that is a separate idea)
2. When enabled at system level should keep track of the fixes ,and all
optimizer features in general, that were applied to user SQL , and to data
dictionary SQL (in separate columns) and which were not because they are not
explicitly enabled.
This should be enabled by default and kept in the data dictionary as a
simple counts. The counts should not wrap. When reaching a maximum number
then would stay as maximum but should be a manual way to reset them.
3) Every CBO fix including the wrong results ones will have to be tracked.
4) If a fix is disabled by a PSE/BP/PSU then would query the fix tracking to
know if this fix was used in the past and record in the alert log that the
fix was used and need to be explicitly enabled in the spfile.
The following are Questions in the customer's mind (assuming this feature is present)
and the (hypothetical) answers in documentation or provided by support on how to use this
new feature :
1) "What will be the impact of applying this BP to my system" ?
Answer) The BP includes a list of CBO fixes but none are enabled. The README
of the BP include a list of fixes or you can use getBugsforBundle to see the
list which you can query in dba_fix_control_tracking after you application
has worked for some time.
if the fixes are relevant to any of your SQL the count on "ELIGIBLE" column
would show you how relevant the fix is.
Be aware that enabling an "Eligible" fix does not mean it will be applied
unconditionally.
It only means the CBO will now consider all the factors to see if the fix
should be applied and make the decision to apply it or not.
The tracking will not record if the fix is enabled and not applied.
2) "You are recommending to set _fix_control=1234{0/1}/_underscore_param as a
workaround/as a fix. What will be the impact of setting this in my system ?"
Answer) You can query dba_fix_control_tracking and see USED_USER and USED_DD
, ELIGIBLE to know if the fix is widely used or not or could be used.
If it is used or could be used you can use
DBMS_FIX_TRACKING.RESET_COUNTER(1234) and query again after some time.
If you need to know in a more granular way then you can do this procedure
- EXEC DBMS_FIX_TRACKING.CREATE_TRACKING_TABLE('FIXTRK')
- EXEC DBMS_FIX_TRACKING.TRACK_FIX(1234,'FIXTRK',MAXSQLIDS=>1024,MAXTIMEMINUTES=>60);
- EXEC DBMS_FIX_TRACKING.TRACK_FIX('_subquery_unnesting','FIXTRK',MAXSQLIDS=>1024,MAXTIMEMINUTES=>60);
After 1hr you can query FIXTRK to know in which SQLIDs the fix was applied or
was not applied because it was disabled.
The tracking will turn off itself after 1hr or if it reaches 1024 SQLIDs.
3) "I want to know if this fix is really relevant to my query/ If it is really going to fix my issue / if it fixed my issue"
Answer) You can do EXPLAIN PLAN FOR and
DBMS_XPLAN.DISPLAY(...FORMAT=>'+FIXES')
or
You can enable tracking on the SQLID and see if the fix was applied in
USED_USER and USED_DD or "ELIGIBLE" will be used
EXEC DBMS_FIX_TRACKING.CREATE_TRACKING_TABLE('FIXTRKSQLID')
EXEC DBMS_FIX_TRACKING.TRACK_FIX_IN_SQLID('asqwrfdsdgsd');
4) The alert.log shows at startup time "fix 1234 was used in this system and it is currently disabled" what does it means ?
Answer) If you recently applied a PSU the instructions indicate to execute
DBMS_FIX_TRACKING.SyncSPfileandBugsforBundle but probably you skipped this.
The RDBMS noticed that fix 1234 was enabled by default before but it is not
anymore by default as well.
If you want it to be enabled again then you need to run
DBMS_FIX_TRACKING.SyncSPfileandBugsforBundle or set the _fix_control in the
spfile explicitly which is what the DBMS_FIX_TRACKING.syncBugsforBundle
procedure does.
or you can track the fix to see if it is still being used
or you can reset the count of the fix tracking and see if it is still being
used or if it is eligible to be used.
This idea has been filed under enhancement request Bug 23628345