how to find which tables and indexes are used most
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: how to find which tables and indexes are used most

  1. #1
    Hi there,
    A request came in - "Which are the top 10 used tables and indexes, files and tablespaces please".

    Ok, v$filestat gives me file and effectively tablespace as well....

    How the hell do I monitor segment usage ? I cant find anywhere that tells me how much io a segment is getting. Any ideas? I have even looked in capacity planner in OEM and cant find anything...

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Take a look at the following script:

    $oracle_home/rdbms/admin/catio.sql
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3

    tried it and....

    Hi jmodic,
    thanks for that, I tried the script (ran as SYS). I ran the sample_io procedure to gather the stats. First off im getting numeric/value errors... I locaed and fixed this by increasing the variable sizes from raw(4) to raw(20). I now dont get errors but then the one of tables that the final view is based upon contan no values.

    extent_to_object contains masses of values but io_histogram is empty. I ran the sample_io for 5 mins with 1 minute interval (exec sample_io(5,1) ) and at the same time made a point of running some nice heavy reports so I knew there was some activity....

    Im out of ideas (I can read the plsql but dont know enough about the internals of the sga to debug the code any further)....any ideas?

    I have attempted this on 7.3.5 and 8.1.7 and get the same errors and same results...

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Hi, bmycroft

    You are right, the procedure SAMPLE_IO needs to be modified (RAW(4) is not large enough for those local variables), but you must have encountered much more trivial *problem*: If you look at the code you'll notice there is no COMMIT inside the procedure. So you must either put a COMMIT inside the procedure or isue a COMMIT manually after you run the procedure. That way you'll make the inserted rows in the IO_HISTOGRAM visible to other sessions. The way you are running it now the results should be visible from the session where you actually run the procedure, but I guess you havent' checked the results from there....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Mar 2001
    Posts
    314
    ..... just one more thing

    the SAMPLE_IO procedure is defined with two parameters - duration and interval. The interval is in seconds - so to collect data for 5 min at 1 min intervals you would need to call it as:

    exec sample_io(5,60);

    and not sample_io(5,1);

    -amar

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Exactly! Good catch, amar.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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



Click Here to Expand Forum to Full Width