I am trying to do fine tuning in oracle. One of the method from the documentation which i read from the oracle is to separate a heavily accessed tables and low-accessed tables. To do this, i need to know the table statistics. So far i can only find two oracle system tables, V$FILESTAT which contains the physical reads & writes for datafiles (i can get the tablespace statistics from this) and another one is V$SEGMENT_STATISTICS which only available in oracle 9i.
Is these the correct tables to get the statistics ?
When is the physical reads value updated ?
Is getting the table hits is the best way to define how many tablespace i need and which table is supposed to be stored on it ?
You can monitor tables using 'alter table x monitoring' and looking at dba_tab_modifications. This will show you how many inserts/updates/deletes occur since the last time the table was analyzed. It wont show how many selects were done though.
As for tuning your database rather than looking at how heavily accessed a table is, it might be better to categorise your tables as small lookup tables, medium sized tables, and large tables. The small lookup tables (which probably would be heavily used anyway) should be assigned to the keep buffer pool (do a search on oracle buffer_pool keep for more info), assign medium sized tables to the default pool and large tables to the recycle pool.
As for tablespace organisation the key thing is to spread the load across all the disks, which can be done by spreading your tablespaces across the disks and or using RAID striping. But dont forget your redo logs, so you might need to use OS utilities to see the disk load across all disks.
its in 8.1.7 and in 9i for sure and I think it came in at 8.1.5. What version of oracle are you running? Anyway try the 'alter table x monitoring', if it brings up an error it isnt in your version of oracle.