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...
Take a look at the following script:
tried it and....
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...
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....
..... 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:
and not sample_io(5,1);
Exactly! Good catch, amar.