Data … as usual

All things about data by Laurent Leturgez

Category Archives: CBO

Monitor how are used your table’s columns

If you want to know how are used your columns when they are involved in sql queries, you can use a specific function in the dbms_stats package called REPORT_COL_USAGE.

This function will give you what operation have been executed on your table columns, e.g. :

SQL> set lines 150 pages 400 long 20000000 longchunksize 50000
SQL> select dbms_stats.report_col_usage('SH','SALES') from dual;
DBMS_STATS.REPORT_COL_USAGE('SH','SALES')
-------------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR SH.SALES
................................
1. AMOUNT_SOLD : EQ RANGE
###############################################################################

In the previous example, we can see the AMOUNT_SOLD column have been accessed with an equality and a range predicates.

If I execute a query which filter on the PROD_ID column, the report will be updated:

SQL> select count(*) from sh.sales where prod_id=400;
COUNT(*)
----------
 0
SQL> select dbms_stats.report_col_usage('SH','SALES') from dual;
DBMS_STATS.REPORT_COL_USAGE('SH','SALES')
--------------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR SH.SALES
................................
1. AMOUNT_SOLD : EQ RANGE
2. PROD_ID : EQ
###############################################################################

Another feature of this package will report joins that have been done on the table columns (You can have a look to the legend which mentions what the function can report):

SQL> select count(*) from sh.sales s, sh.products p where s.prod_id=p.prod_id;
COUNT(*)
----------
 918843
SQL> select dbms_stats.report_col_usage('SH','SALES') from dual;
DBMS_STATS.REPORT_COL_USAGE('SH','SALES')
--------------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR SH.SALES
................................
1. AMOUNT_SOLD : EQ RANGE
2. PROD_ID : EQ EQ_JOIN
###############################################################################

If you want to reset usage statistics, use the undocumented procedure RESET_COL_USAGE:

SQL> exec dbms_stats.reset_col_usage('SH','SALES');
PL/SQL procedure successfully completed.
SQL> select dbms_stats.report_col_usage('SH','SALES') from dual;
DBMS_STATS.REPORT_COL_USAGE('SH','SALES')
--------------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR SH.SALES
................................
###############################################################################

Note: If you execute a query using a function based index, your column name will be the virtual column name used for applying the function:

SQL> exec dbms_stats.reset_col_usage('SH','SALES');
PL/SQL procedure successfully completed.
SQL> drop index idx;
Index dropped.
SQL> create index idx on sh.sales(amount_sold*2);
Index created.
SQL> set autotrace trace
SQL> select count(*) from sh.sales where amount_sold*2>40;

Execution Plan
----------------------------------------------------------
Plan hash value: 875048923
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 25 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| IDX | 45873 | 582K| 25 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("AMOUNT_SOLD"*2>40)

Statistics
----------------------------------------------------------
 12 recursive calls
 0 db block gets
 1828 consistent gets
 1812 physical reads
 0 redo size
 528 bytes sent via SQL*Net to client
 524 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 7 sorts (memory)
 0 sorts (disk)
 1 rows processed
SQL> set autotrace off
SQL> select dbms_stats.report_col_usage('SH','SALES') from dual;
DBMS_STATS.REPORT_COL_USAGE('SH','SALES')
--------------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR SH.SALES
................................
1. SYS_NC00008$ : RANGE
###############################################################################

If you want to desactivate this feature because your database contains a lot of tables and columns and you don’t want to overload your system, you can set the undocumented parameter “_column_tracking_level” to 0 (default value = 1).

All results of the DBMS_STATS.REPORT_COL_USAGE are based on the COL_USAGE$ dictionary table.

Finally, you can use this method to decide if a column needs a histogram or if it’s an unindexed column that needs one.

Advertisement

Some things about Statistics …

Every Oracle DBA knows how statistics are very important for the CBO and for database performance.

Oracle have many kind of statistics :

– object statistics : statistics on tables, indexes …

– system statistics: statistics on the system where your database runs : CPU speed, time to perform a single I/O etc.

– dictionary statistics : statistics on the dictionary tables, indexes etc. (OBJ$, TAB$ tables … I_SOURCE1,  I_FILE1 indexes …)

– Fixed objects statistics : statistics on the fixed tables (X$BH etc. A non exhaustive list and explanation of X$ tables can be found here : http://yong321.freeshell.org/computer/x$table.html)

Each statistic is important for the performance of your database and so, it’s important to know where to find information about them.

  • Object statistics

Object statistics are the most known statistics. Information about object statistics can be found in the DBA_ views. For example, LAST_ANALYZED column of the DBA_TABLES, DBA_INDEXES, DBA_TAB_PARTITIONS, DBA_IND_PARTITIONS etc. told us when was last analyzed an object. Other columns like BLOCKS, NUM_ROWS etc. are populated after this analyze.

  • System statistics
System statistics informs the CBO about the performance of the system. For example, information collected are CPU Speed, time to perform a single I/O or a multiblock I/O operation etc.
System statistics are available in the table SYS.AUX_STATS$. System stats can be gathered in a “NOWORKLOAD” mode (default) or in a “WORKLOAD” mode. The No workload mode gathers basic information about the system. WORKLOAD mode gathers more information and give more precise information about the performance of the system.
It’s recommanded to gather system stats in a workload mode during a representative workload phase. For example, if your system have a representative load every tuesday during 9 AM to 12 AM, you can gather system stats during 3 hours every tuesday.
Ex: Content of the AUX_STATS$ table after a  noworkload statistics gathering:
SQL> select * from aux_stats$;

SNAME           PNAME                PVAL1 PVAL2
--------------- --------------- ---------- ------------------
SYSSTATS_INFO   STATUS                     COMPLETED
SYSSTATS_INFO   DSTART                     09-26-2011 11:47
SYSSTATS_INFO   DSTOP                      09-26-2011 11:47
SYSSTATS_INFO   FLAGS                    1
SYSSTATS_MAIN   CPUSPEEDNW         919,606
SYSSTATS_MAIN   IOSEEKTIM             8,17
SYSSTATS_MAIN   IOTFRSPEED       55914,355
SYSSTATS_MAIN   SREADTIM
SYSSTATS_MAIN   MREADTIM
SYSSTATS_MAIN   CPUSPEED
SYSSTATS_MAIN   MBRC
SYSSTATS_MAIN   MAXTHR
SYSSTATS_MAIN   SLAVETHR
Ex: Content of the AUX_STATS$ table after a  workload statistics gathering of 5 minutes:
SQL> exec dbms_stats.gather_system_stats(gathering_mode=>'INTERVAL',interval=>5);

SQL> select * from aux_stats$;

SNAME           PNAME                PVAL1 PVAL2
--------------- --------------- ---------- ------------------------------
SYSSTATS_INFO   STATUS                     COMPLETED
SYSSTATS_INFO   DSTART                     09-26-2011 11:50
SYSSTATS_INFO   DSTOP                      09-26-2011 11:55
SYSSTATS_INFO   FLAGS                    0
SYSSTATS_MAIN   CPUSPEEDNW         919,606
SYSSTATS_MAIN   IOSEEKTIM             8,17
SYSSTATS_MAIN   IOTFRSPEED       55914,355
SYSSTATS_MAIN   SREADTIM             1,537
SYSSTATS_MAIN   MREADTIM             1,818
SYSSTATS_MAIN   CPUSPEED               920
SYSSTATS_MAIN   MBRC                    19
SYSSTATS_MAIN   MAXTHR
SYSSTATS_MAIN   SLAVETHR

For more information about what mean PNAME values, just have a look here : http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#i41496

  • Dictionary Statistics
Dictionary statistics are similar to object statistics, but they are specific to SYS object that describe the dictionary. DBMS_STATS.GATHER_DICTIONARY_STATISTICS is the procedure used to gather statistics on these objects. To verify statistics on it, you can proceed like a classical objects except in the name of the object.
Ex:
SQL> select table_name,last_analyzed,blocks,num_rows from dba_tables where table_name in ('OBJ$','TAB$','IND$','FILE$');

TABLE_NAME LAST_ANALYZED                BLOCKS   NUM_ROWS
---------- ------------------------ ---------- ----------
TAB$       26/SEPT./2011 04PM:28:22       1236       2617
OBJ$       26/SEPT./2011 04PM:27:49        862      70094
IND$       26/SEPT./2011 04PM:28:21       1236       4103
FILE$      26/SEPT./2011 04PM:27:46          1          5

NB : Dictionary statistics are automatically gathered during DBMS_STATS.GATHER_DATABASE_STATS which is launched with the automatic optimizer statistic collection task.

  • Fixed objects
Fixed objects are a little bit particular. Indeed, a fixed table (X$) seems to be a kind of C structure mapped into a table. So, fixed tables exist but not really and are not referenced in the dictionary tables. (more information here : http://www.oracle-internals.com/?p=11)
To identify statistics on fixed objects, you have to query dictionary with this query. If there’s no fixed objects statistics gathered, the query will answer 0 line, otherwise you will have one line per fixed object.
SQL> exec dbms_stats.gather_fixed_objects_stats;
SQL> select fo.name, analyzetime, ROWCNT,samplesize
  2  from tab_stats$ t, (select OBJECT_ID,NAME from V$FIXED_TABLE) fo
  3  where t.obj#=fo.object_id
  4  order by 2;

NAME                           ANALYZETIME                  ROWCNT SAMPLESIZE
------------------------------ ------------------------ ---------- ----------
X$KGLJSIM                      26/SEPT./2011 05PM:44:27          4          4
X$KGLJMEM                      26/SEPT./2011 05PM:44:27         68         68
X$KGLAU                        26/SEPT./2011 05PM:44:27        800        800
X$KGLLC                        26/SEPT./2011 05PM:44:27         12         12
X$KGLDP                        26/SEPT./2011 05PM:44:27      14976      14976
X$KGLLK                        26/SEPT./2011 05PM:44:28        831        831
X$KGLMEM                       26/SEPT./2011 05PM:44:28         68         68
X$KGLNA1                       26/SEPT./2011 05PM:44:28      38695      38695
X$KGLNA                        26/SEPT./2011 05PM:44:28      38671      38671
X$KGLOB                        26/SEPT./2011 05PM:44:31      21054      21054
X$KGLPN                        26/SEPT./2011 05PM:44:32         38         38
X$KGLST                        26/SEPT./2011 05PM:44:32         68         68
X$KGLSIM                       26/SEPT./2011 05PM:44:32         14         14
X$KGLRD                        26/SEPT./2011 05PM:44:32       8788       8788
X$KGLSN                        26/SEPT./2011 05PM:44:32        173        173
X$KGLTR                        26/SEPT./2011 05PM:44:32       1206       1206
X$KGLXS                        26/SEPT./2011 05PM:44:33      13029      13029
X$KKSCS                        26/SEPT./2011 05PM:44:33       2311       2311
X$KKSAI                        26/SEPT./2011 05PM:44:33          0          0
X$KKSBV                        26/SEPT./2011 05PM:44:33       3116       3116
X$KQLFBC                       26/SEPT./2011 05PM:44:34       3116       3116
.../...

In a next post, I will show you how system and fixed objects statistics are very important for your database performance.

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.