RE: Unexpected behavior with V$FILESTAT

 I searched Metalink and didn't find anything that talks about this. I filed a tar. I'll tell you how it turns out. Jim

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kennedy, Jim Sent: Friday, August 26, 2005 12:11 PM
To: oracle-l_at_freelists.org
Subject: Unexpected behavior with V$FILESTAT

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
--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 26 2005 - 15:23:38 CDT