Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: getting table hits information

    Hi all,

    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 ?

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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.

    Alan

  3. #3
    Join Date
    Oct 2003
    Posts
    2
    Thank you for explanation Alan,

    I tried to search for the DBA_TAB_MODIFICATIONS table...but i coudn't find it. It's not on the DBA_TABLES list or ALL_TABLES list. Which schema is it under ?

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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.

    Alan

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Any thing beginning with ALL_ , DBA_, V$, USER_ are views ownerd by SYS. But providing you login to a DBA user just do select * from dba_tab_modification.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •