Re: Unexpected behavior with V$FILESTAT

From: Terry Sutton <terrysutton_at_usa.net>
Date: Fri, 26 Aug 2005 14:45:27 -0700
Message-ID: <014201c5aa87$c8882680$61f5a8c0@TerrySutton>

Is it possible that dbwriter hadn't yet written the blocks to disk when you performed your query?

--Terry

• Original Message ----- From: "Kennedy, Jim" <jim_kennedy_at_mentor.com> To: <oracle-l_at_freelists.org> Sent: Friday, August 26, 2005 12:10 PM 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 - 16:49:43 CDT