Unexpected behavior with V$FILESTAT

From: Kennedy, Jim <jim_kennedy_at_mentor.com>
Date: Fri, 26 Aug 2005 12:10:59 -0700
Message-ID: <EF25DB6D87DD1A469C80A312C63C3B4C044D2354@SVR-ORW-EXC-07.mgc.mentorg.com>

I am trying to collect some file IO statistics to get some IO rates over time. I noticed something unexpected or maybe I am crazy.

create table testio( numrow number, some_text varchar2(80)) tablespace tools;

select * from v$filestat where file#=122 ; -- record the phywrts

• file 122 is the file for my tools tablespace and I only have one file for that tablespace.

insert into testio(numrow,some_text) values(1,'hello world');
insert into testio(numrow,some_text) values(2,'hello world');
insert into testio(numrow,some_text) values(3,'hello world');
insert into testio(numrow,some_text) values(4,'hello world');
insert into testio(numrow,some_text) values(5,'hello world');

commit;

insert into testio(numrow,some_text) values(1,'hello world');
insert into testio(numrow,some_text) values(2,'hello world');
insert into testio(numrow,some_text) values(3,'hello world');
insert into testio(numrow,some_text) values(4,'hello world');
insert into testio(numrow,some_text) values(5,'hello world');

commit;

insert into testio(numrow,some_text) values(1,'hello world');
insert into testio(numrow,some_text) values(2,'hello world');
insert into testio(numrow,some_text) values(3,'hello world');
insert into testio(numrow,some_text) values(4,'hello world');
insert into testio(numrow,some_text) values(5,'hello world');

commit;

select * from v$filestat where file#=122 ; -- record the phywrts Okay this number is larger than the one above.

• so far so good.

Truncate table testio; -- leave the storage alone, don't unallocate the space.

select * from v$filestat where file#=122 ; -- record the phywrts

insert into testio(numrow,some_text) values(1,'hello world');
insert into testio(numrow,some_text) values(2,'hello world');
insert into testio(numrow,some_text) values(3,'hello world');
insert into testio(numrow,some_text) values(4,'hello world');
insert into testio(numrow,some_text) values(5,'hello world');

commit;

select * from v$filestat where file#=122 ; -- record the phywrts the number is unchanged. I would have expected the number to bump up. I think it will if I insert enough rows.(it does)

Any ideas? It appears that phywrts only count if the segment extends past its high water mark?

I am running 9.2 on Solaris.
Jim
--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 26 2005 - 14:13:05 CDT