How to set an Oracle Database _FIX_CONTROL (18c)

Configurare noua (How To)

Situatie

_FIX_CONTROL is a special hidden dynamic parameter introduced in 10.2.0.2 which can be used to enable or disable certain bug fixes. This is typically used in the optimizer area to allow specific fixes to be turned on or off as some fixes can have a significant effect on execution plans.

The VALUE column shows the current state of the bug fix, where 1=ON and 0=OFF.
They can be set on system level or on session level.

Solutie

Syntax: 
# session level 
ALTER SESSION SET "_fix_control"='<Bug_number>:<Value>';
ALTER SESSION SET "_fix_control"='<Bug_number>:<Value>','<Bug_number>:<Value>';
# system level 
ALTER SYSTEM SET "_fix_control"='<Bug_number>:<Value>';
ALTER SYSTEM SET "_fix_control"='<Bug_number>:<Value>','<Bug_number>:<Value>';
ALTER SYSTEM RESET "_fix_control";

In order to activate the new settings a restart is needed.

SHUTDOWN IMMEDIATE
STARTUP
Example : 
sqlplus / as sysdba
ALTER SYSTEM SET "_FIX_CONTROL"=
'5099019:ON','6055658:OFF','6120483:OFF','6399597:ON','6430500:ON','6972291:ON',
'7324224:OFF','7658097:ON','8932139:ON','8937971:ON','9196440:ON','9495669:ON',
'29687220:ON','29930457:ON','30195773:ON','30231086:ON'
COMMENT='RECOMMENDED SETTINGS by Sorina on 06.08.2020'
SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
Output :
--- snip ---
[oracle@myhost]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Thu Aug 6 16:40:34 2020
Version 18.11.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.11.0.0.0

SQL> 
ALTER SYSTEM SET "_FIX_CONTROL"=
'5099019:ON','6055658:OFF','6120483:OFF','6399597:ON','6430500:ON','6972291:ON',
'7324224:OFF','7658097:ON','8932139:ON','8937971:ON','9196440:ON','9495669:ON',
'29687220:ON','29930457:ON','30195773:ON','30231086:ON'
COMMENT='RECOMMENDED SETTINGS by Sorina on 06.08.2020'
SCOPE=SPFILE;

System altered.

SQL> 
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 490729568 bytes
Fixed Size 8659040 bytes
Variable Size 423624704 bytes
Database Buffers 50331648 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.
SQL>

--- snip ---

Tip solutie

Permanent

Voteaza

(31 din 63 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?