Forum Stats

  • 3,826,571 Users
  • 2,260,666 Discussions
  • 7,897,004 Comments

Discussions

fix tracking (See enh req Bug 23628345)

amacias-Oracle
amacias-Oracle Member Posts: 5
edited Jun 27, 2018 6:45PM in Database Ideas - Ideas

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

amacias-OracleMautro PaganoUser_N17Q2Chris Crocker, Oracle-OracleJorge BarbaAlfredo Abatericarezendeuser713575-Oracle16729562669599Galo BaldaMike RipleyUser_UHR3GmUday-OracleIrishEjitCarlos SierraSven W.Filipe SibinelUser_NY8YWBPeaslandDBAStefan Koehlerblessed DBA
22 votes

Under Review - Voting Still Open · Last Updated