Oracle Tablespaces Test
This test tracks both the disk space usage per tablespace, as well as the rates at which data is written to and read from a tablespace.
Target of the test : An Oracle server
Agent deploying the test : An internal agent
Outputs of the test : One set of results for every SID monitored.
|
Measurement | Description | Measurement Unit | Interpretation | ||||||
---|---|---|---|---|---|---|---|---|---|
Current usage |
Indicates the current actual usage with respect to the current allocated size (Current_size) |
Percent |
As a rule of thumb, at any time, about 20% of the space allocated to a tablespace should be available. In case of auto-extensible tablespaces, even if this percentage touches 100%, there would be no cause for concern. However, if a tablespace is not auto-extensible, then when the percentage disk space usage reaches 100%, all statements that attempt to acquire new space in the tablespace will fail. Under such circumstances, the underlying datafiles of the tablespace may need to be resized or reorganized. Alternately, additional datafiles could be mapped to the tablespace. |
||||||
Physical reads |
Indicates the rate of physical reads happening on this tablespace. |
Reads/Sec |
A sudden increase in the rate of data accesses may indicate a change in application characteristics. At any stage, if more than 50% of the total reads for a database instance happen to be on a particular tablespace, this may result in performance degradation. |
||||||
Physical writes |
Indicates the rate of physical writes happening on this tablespace. |
Writes/Sec |
More than 50% of the total writes for a database instance happening on a particular tablespace may be indicative of a problem scenario that needs further investigation. |
||||||
Auto extensible |
Indicates whether this tablespace has the capability to grow automatically or not
|
|
If the tablespace is auto-extensible, then this measure will report the value Yes. If it is not extensible, then the value of this measure will be No. The numeric values that correspond to the measure values discussed above are as follows:
Note: By default, the measure reports the Measure Values listed in the table above to indicate whether/not a tablespace is auto-extensible. In the graph of this measure however, the same is represented using the numeric equivalents only. |
||||||
Max size |
Indicates the maximum extent (in MB) up to which this tablespace can grow.
|
MB |
|
||||||
Current size |
Indicates the current allocated size of this tablespace.
|
MB |
If a tablespace is not auto-extensible, then its Current size will be equal to the Max size. For auto-extensible tablespaces though, the values of the Current size and Max size measures could be different. |
||||||
Free space |
Indicates the amount of unused space in this tablespace. This is computed using the formula: Max size - Current actual usage, where Current actual usage is arrived at by applying the Current usage percentage on the Current size (current allocated size) measure. For example, assume that the Max size of a tablespace is 2500 MB and its Current size is 1000 MB. Also, note that nearly 30% of the Current size has already been utilized. Therefore, the Current actual usage of the tablespace will be 30% of 1000MB, which is 300 MB. The available Free space will hence be, 2500-300, i.e. 2200 MB. Note that the Current actual size is inclusive of the Recycle bin size measure for all tablespaces other than the undo and temp tablespaces. |
MB |
If this value is very low, then it indicates over-utilization of the tablespace.
|
||||||
Percent free space |
Indicates the space available for overall growth expressed as a ratio of Free_space with respect to the Max_size of the tablespace. The formula used is: Free_space/Max_size*100 |
Percent |
If this value is very low, then it indicates over-utilization of the tablespace.
|
||||||
Biggest extent |
Indicates the size of the biggest extent in this tablespace. |
MB |
From both these values, you can figure out how space allocation, fragmentation, etc. have been performed on the tablespace.
|
||||||
Smallest extent |
Indicates the size of the smallest extent in this tablespace. |
MB |
|||||||
Recycle bin size |
Indicates the current recycle bin size of this tablespace. |
MB |
|
||||||
Remaining extents |
Indicates the number of extents that can be added to this tablespace. |
Number |
If this value is low and the tablespace is not auto-extensible, then it indicates that the tablespace requires resizing. In the case of auto-extensible tablespaces, this phenomenon is not a cause for concern. This measure is not applicable to tablespaces that have dictionary based extent management and allocation type is user. |