Saturday, June 02, 2007

DBA_HIST_FILEMETRIC_HISTORY

There are so many new views in 10G that you begin to wonder if many of them are
actually populated correctly. For example there are quite a few bugs with some of the DBA_HIST* views not being populated correctly. One such view DBA_HIST_TBSPC_SPACE_USAGE is notorious for incorrect values .But the view DBA_HIST_FILEMETRIC_HISTORY which supposedly stores alerts only for 7 days does not even seem to be getting populated.


So on a 10.1.0.5 database

SQL> select count(*) from dba_hist_filemetric_history;

COUNT(*)
----------
0

If you have a look at the definition of the view from catawrvw.sql

create or replace view DBA_HIST_FILEMETRIC_HISTORY
(SNAP_ID, DBID, INSTANCE_NUMBER, FILEID, CREATIONTIME, BEGIN_TIME,
END_TIME, INTSIZE, GROUP_ID, AVGREADTIME, AVGWRITETIME, PHYSICALREAD,
PHYSICALWRITE, PHYBLKREAD, PHYBLKWRITE)
as
select fm.snap_id, fm.dbid, fm.instance_number,
fileid, creationtime, begin_time,
end_time, intsize, group_id, avgreadtime, avgwritetime,
physicalread, physicalwrite, phyblkread, phyblkwrite
from wrm$_snapshot sn, WRH$_FILEMETRIC_HISTORY fm
where sn.snap_id = fm.snap_id
and sn.dbid = fm.dbid
and sn.instance_number = fm.instance_number
and sn.status = 0


SQL> select count(*) from WRH$_FILEMETRIC_HISTORY;

COUNT(*)
----------
0

I have tried various ways to get this base table populated but
am not sure when information is flushed to WRH$_FILEMETRIC_HISTORY.
This behaviour is also consistent in 10.2.0.3

5 comments:

Peter K said...

Upcoming 11g feature? ;)

Andrey said...

Is it a bug 5478351?

Regards,
Andrey Goryunov

Fairlie Rego said...

Yes ... This bug was logged by me :-)

Andrey said...

I traced the session with
exec dbms_workload_repository.create_snapshot('ALL');
and found the sql that does insert:
PARSING IN CURSOR #15 len=440 dep=1 uid=0 oct=2 lid=0 tim=23625183113164 hv=1555563702 ad='9ec56bf0'
insert into wrh$_filemetric_history (snap_id, dbid, instance_number, fileid, creationtime, begin_time, end_time, intsize, group_
id, avgreadtime, avgwritetime, physicalread, physicalwrite, phyblkread, phyblkwrite) select :snap_id, :dbid, :instance_number,
fileid, creationtime, begtime, endtime, intsize_csec, groupid, avrdtime, avwrtime, phyread, phywrite, phybkrd, phybkwr f
rom x$kewmflmv
END OF STMT
PARSE #15:c=0,e=78,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=23625183113158
BINDS #15:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0000 frm=00 csi=00 siz=72 off=0
kxsbbbfp=ffffffff7dbc0700 bln=22 avl=03 flg=05
value=192
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=24
kxsbbbfp=ffffffff7dbc0718 bln=22 avl=06 flg=01
value=2142263721
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=48
kxsbbbfp=ffffffff7dbc0730 bln=22 avl=02 flg=01
value=1
EXEC #15:c=0,e=485,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=23625183113768
STAT #15 id=1 cnt=0 pid=0 pos=1 obj=0 op='FIXED TABLE FULL X$KEWMFLMV (cr=0 pr=0 pw=0 time=152 us)'

but the data did not appear in wrh$_ table even there are some rows in the source table.
but I was able to insert data to wrh$ table manually under sys (of course it is just test database)
but for some reason it does not work within a procedure.

Regards,
Andrey Goryunov

Andrey said...

moreover the internal insert statement doesn't insert anything

since r=0 in EXEC statement.

interesting thing...


Regards,
Andrey Goryunov