Data … as usual

All things about data by Laurent Leturgez

Control bug fixes activation in Oracle

When you upgrade oracle database version, you can be faced to a problem of CBO that changes its behaviour.

To do this, Oracle have introduced an undocumented parameter : “_fix_control” (Since 10.2.0.2). With this parameter, you can unable or disable a bug fixe to see, for example, if the CBO behaviour is controlled by a bug fix or not.

Off course, this kind of tests have to be implemented for testing purposes only !

To see what bug fix is implemented on your database, oracle have implemented two views V$SYSTEM_FIX_CONTROL and V$SESSION_FIX_CONTROL.

In these views, you will find information about the bug number, a short description, and the value of the parameter optimizer_features_enabled in which the bug fix is activated.

SQL> select bugno,value,description,optimizer_feature_enable,is_default from v$system_fix_control;

BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE IS_DEFAULT
---------- ---------- ---------------------------------------------------------------- ------------------------- ----------
3834770 1 Lift restriction on unnest subquery with a view 8.0.0 1
3746511 1 do not combine predicates from LNNVL 8.0.0 1
4519016 1 Pick view card from view qb instead of parent qb 9.2.0 1
3118776 1 Check for obj# for named view estimated card 9.2.0.8 1
4488689 1 ignore IS NOT NULL predicate as an index filter 10.2.0.2 1
2194204 0 disable push predicate driven by func. index into partition view 1
2660592 1 do not trigger bitmap plans if no protential domain index driver 8.1.7 1
2320291 1 push into table with RLS 9.2.0 1
2324795 1 add(remove) cluster index for push view 8.1.7 1
4308414 1 outer query must have more than one table unless lateral view 10.1.0.5 1
3499674 0 enable tiny index improvements: consider small indexes as cachhe 1
4569940 1 Use index heuristic for join pred being pushed 10.1.0.5 1
4631959 1 Refine criteria for additional phase in JPPD 10.2.0.2 1
4519340 1 Generate distinct view in SU if candidate for JPPD 10.2.0.2 1
4550003 1 do not consider no sel predicates in join selectivity sanity 10.1.0 1

.../...

In this example, we can see that the bug fix #4488689 is activated in our database (because of the value parameter set to 1).

We can see too that some og the buf fixes are not activated by default (ex: 2194204)

You can have a session view by querying the V$SESSION_FIX_CONTROL.

Now, if you want to test to desactivate a bug fix, you can do it by using the _fix_control undocumented parameter.

To disable a bug fix, you have to specify the bug number completed by ON or OFF.

For example:

-- To deactivate a single bug fix:
SQL> alter session set "_fix_control"='4488689:OFF';
-- To deactivate many bug fixes:
SQL> alter session set "_fix_control"='4488689:OFF','4631959:OFF','4519340:OFF';
-- To activate it, you have to use ON instead of OFF
SQL> alter session set "_fix_control"='4488689:ON';

If we control the V$SESSION_FIX_CONTROL view on these specific bug fixes, we can view that they are desactivated (value column equals 0).

SQL> select SESSION_ID,BUGNO,VALUE,OPTIMIZER_FEATURE_ENABLE,IS_DEFAULT
 2 from V$SESSION_FIX_CONTROL
 3 where session_id=sys_context('USERENV','SID') and bugno in (4488689,4631959,4519340);
 SESSION_ID BUGNO VALUE OPTIMIZER_FEATURE_ENABLE IS_DEFAULT
 ---------- ---------- ---------- ------------------------- ----------
 22 4488689 0 10.2.0.2 0
 22 4631959 0 10.2.0.2 0
 22 4519340 0 10.2.0.2 0

 

Disassembling V$ views

If you are instesting by Oracle Internals, maybe you have already tried to use the DBMS_METADATA Package to get the definition code of an Oracle static view :

SQL> set long 5000
SQL> set pages 5000
SQL> select dbms_metadata.get_ddl('VIEW','DBA_DATA_FILES') from dual;
DBMS_METADATA.GET_DDL('VIEW','DBA_DATA_FILES')
---------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_DATA_FILES" ("FILE_NAME", "F
ILE_ID", "TABLESPACE_NAME", "BYTES", "BLOCKS", "STATUS", "RELATIVE_FNO
", "AUTOEXTENSIBLE", "MAXBYTES", "MAXBLOCKS", "INCREMENT_BY", "USER_BY
TES", "USER_BLOCKS", "ONLINE_STATUS") AS
select v.name, f.file#, ts.name,
ts.blocksize * f.blocks, f.blocks,
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
ts.blocksize * f.maxextend, f.maxextend, f.inc,
ts.blocksize * (f.blocks - 1), f.blocks - 1,
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NULL
and f.ts# = ts.ts#
and fe.fenum = f.file#
union all
select
.../...


But if you try to get the definition code of the “V$” dynamic views, you will obtain a strange output which mentioned a call to V$DATAFILE (at the end)

SQL> select dbms_metadata.get_ddl('VIEW','V_$DATAFILE') from dual;
DBMS_METADATA.GET_DDL('VIEW','V_$DATAFILE')
----------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."V_$DATAFILE" ("FILE#", "CREATION
_CHANGE#", "CREATION_TIME", "TS#", "RFILE#", "STATUS", "ENABLED", "CHE
CKPOINT_CHANGE#", "CHECKPOINT_TIME", "UNRECOVERABLE_CHANGE#", "UNRECOV
ERABLE_TIME", "LAST_CHANGE#", "LAST_TIME", "OFFLINE_CHANGE#", "ONLINE_
CHANGE#", "ONLINE_TIME", "BYTES", "BLOCKS", "CREATE_BYTES", "BLOCK_SIZ
E", "NAME", "PLUGGED_IN", "BLOCK1_OFFSET", "AUX_NAME", "FIRST_NONLOGGE
D_SCN", "FIRST_NONLOGGED_TIME", "FOREIGN_DBID", "FOREIGN_CREATION_CHAN
GE#", "FOREIGN_CREATION_TIME", "PLUGGED_READONLY", "PLUGIN_CHANGE#", "
PLUGIN_RESETLOGS_CHANGE#", "PLUGIN_RESETLOGS_TIME") AS
select "FILE#","CREATION_CHANGE#","CREATION_TIME","TS#","RFILE#","ST
ATUS","ENABLED","CHECKPOINT_CHANGE#","CHECKPOINT_TIME","UNRECOVERABLE_
CHANGE#","UNRECOVERABLE_TIME","LAST_CHANGE#","LAST_TIME","OFFLINE_CHAN
GE#","ONLINE_CHANGE#","ONLINE_TIME","BYTES","BLOCKS","CREATE_BYTES","B
LOCK_SIZE","NAME","PLUGGED_IN","BLOCK1_OFFSET","AUX_NAME","FIRST_NONLO
GGED_SCN","FIRST_NONLOGGED_TIME","FOREIGN_DBID","FOREIGN_CREATION_CHAN
GE#","FOREIGN_CREATION_TIME","PLUGGED_READONLY","PLUGIN_CHANGE#","PLUG
IN_RESETLOGS_CHANGE#","PLUGIN_RESETLOGS_TIME" from v$datafile

In fact, dynamic performance view definition are stored in the V$FIXED_VIEW_DEFINITION.
As in 10g and 11g, the V$ views are results of GV$ view, if you want to get the definition view of the V$DATAFILE, a better way is to query this view with the GV$DATAFILE definition.

SELECT view_definition FROM
2 v$fixed_view_definition
3 WHERE view_name='V$DATAFILE'
4 /
VIEW_DEFINITION
----------------------------------------------------------------------
select FILE# , CREATION_CHANGE# , CREATION_TIME , TS# , RFILE# , STAT
US , ENABLED , CHECKPOINT_CHANGE# , CHECKPOINT_TIME, UNRECOVERABLE_CHA
NGE#, UNRECOVERABLE_TIME, LAST_CHANGE# , LAST_TIME , OFFLINE_CHANGE# ,
ONLINE_CHANGE# , ONLINE_TIME , BYTES , BLOCKS , CREATE_BYTES , BLOCK_
SIZE , NAME, PLUGGED_IN, BLOCK1_OFFSET , AUX_NAME , FIRST_NONLOGGED_SC
N, FIRST_NONLOGGED_TIME, FOREIGN_DBID, FOREIGN_CREATION_CHANGE#, FOREI
GN_CREATION_TIME, PLUGGED_READONLY, PLUGIN_CHANGE#, PLUGIN_RESETLOGS_C
HANGE#, PLUGIN_RESETLOGS_TIME from GV$DATAFILE where inst_id = USERENV
('Instance')
SELECT view_definition FROM
2 v$fixed_view_definition
3 WHERE view_name='GV$DATAFILE'
4 /
VIEW_DEFINITION
----------------------------------------------------------------------
select fe.inst_id,fe.fenum,to_number(fe.fecrc_scn), to_date(fe.fecrc_t
im,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), fe.fetsn,fe.ferfn,
decode(fe.fetsn,0,decode(bitand(fe.festa,2),0,'SYSOFF','SYSTEM'), d
ecode(bitand(fe.festa,18),0,'OFFLINE',2,'ONLINE','RECOVER')), decode(f
e.fedor,2,'READ ONLY', decode(bitand(fe.festa, 12), 0,'DISABLED'
,4,'READ ONLY',12,'READ WRITE','UNKNOWN')), to_number(fe.fecps), to_da
te(fe.fecpt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), to_number
(fe.feurs), to_date(fe.feurt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Grego
rian'), to_number(fe.fests), decode(fe.fests,NULL,to_date(NULL), to_d
ate(fe.festt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')), to_numb
er(fe.feofs),to_number(fe.feonc_scn), to_date(fe.feonc_tim,'MM/DD/RR H
H24:MI:SS','NLS_CALENDAR=Gregorian'), fh.fhfsz*fe.febsz,fh.fhfsz,fe.fe
csz*fe.febsz,fe.febsz,fn.fnnam, fe.fefdb, fn.fnbof, decode(fe.fepax,
0, 'UNKNOWN', 65535, 'NONE', fnaux.fnnam), to_number(fh.fhfirstunr
ecscn), to_date(fh.fhfirstunrectime,'
MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), fe.fepdi, fe.fefcrs, f
e.fefcrt, decode(fe.fefdb, 1, 'YES', 'NO'), fe.feplus, fe.feprls, fe.
feprlt from x$kccfe fe, x$kccfn fn, x$kccfn fnaux, x$kcvfh fh where
((fe.fe
pax!=65535 and fe.fepax!=0 and fe.fepax=fnaux.fnnum) or
((fe.fepax=65535 or fe.fepax=0) and fe.fenum=fnaux.fnfno
and fnaux.fntyp=4 and fnaux.fnnam is not null and
bitand(fnaux.fnflg, 4) != 4
and fe.fefnh=fnaux.fnnum))
and fn.fnfno=fe.fenum and fn.fnfno=fh.hxfil an
d fe.fefnh=fn.fnnum and fe.fedup!=0 and fn.fntyp=4 and fn.f
nnam is not null and bitand(fn.fnflg, 4) != 4 order by fe.fenum

Now you know that GV$DATAFILE view is using x$kccfe, x$kccfn, x$kcvfh fixed tables.

Convert a virtualBox disk to plug it into Vmware

Oracle VirtualBox is a powerful virtualization software used on workstation.
The software is fitted with a set of powerful tools that can be used to migrate your disks to VMWare.

If you want to migrate a hard disk initially built with Oracle Virtual Box (VDI) to a VM Ware hard disk. There’s a very easy and it can be done in only two steps:

– first step, you convert the vdi to a raw disk. To do it, you will use “vboxmanage” command from virtualbox

G:\WM_disks> "c:\Program Files\Sun\VirtualBox\vboxmanage" internalcommands \
 converttoraw linux_1_d1.vdi linux_1_d1.raw

– and last step 🙂 you convert the raw disk to a VMDK format disk. In this step, you can still use “vboxmanage” command from virtualbox

G:\WM_disks> "c:\Program Files\Sun\VirtualBox\vboxmanage" convertfromraw `\
linux_1_d1.raw linux_1_d1.vmdk -format VMDK

The conversion is a little bit long and depends on the size of the disk.

To finish, you can add the converted disk to an existing or new VM ware virtual machine.

I’m Back

A quick post to inform readers that I was a little bit busy last month because of my removal and because I have started a new job on a French Oracle consulting company DIGORA.

So I will write new posts next days !!!

See you soon !

Good news

EU has officially validated the acquisition of Sun Microsystems by Oracle.
That’s a really good news for Sun hardware and Software.