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.

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The host for which the test is to be configured
  3. Port - The port on which the server is listening
  4. User – In order to monitor an Oracle database server, a special database user account has to be created in every Oracle database instance that requires monitoring. A Click here hyperlink is available in the test configuration page, using which a new oracle database user can be created. Alternatively, you can manually create the special database user. When doing so, ensure that this user is vested with the select_catalog_role and create session privileges.

    The sample script we recommend for user creation (in Oracle database server versions before 12c) for eG monitoring is:

    create user oraeg identified by oraeg

    create role oratest;

    grant create session to oratest;

    grant select_catalog_role to oratest;

    grant oratest to oraeg;

    The sample script we recommend for user creation (in Oracle database server 12c) for eG monitoring is:

    alter session set container=<Oracle_service_name>;

    create user <user_name>identified by <user_password> container=current default tablespace <name_of_default_tablespace> temporary tablespace <name_of_temporary_tablespace>;

    Grant create session to <user_name>;                                

    Grant select_catalog_role to <user_name>;

    The name of this user has to be specified here.

  5. Password – Password of the specified database user

    This login information is required to query Oracle’s internal dynamic views, so as to fetch the current status / health of the various database components.

  6. Confirm password – Confirm the password by retyping it here.
  7. alternate view – In large environments, where the volume of transactions to the Oracle database server is generally very high, this test may take time to execute and retrieve the desired results. To ensure that the test is faster and is resource-efficient, administrators of such environments can create an alternate ‘view’ on the target Oracle database server, and grant select privileges to the view to the special database user mentioned above. Once the view is created, the test should be configured to use the alternate view for metrics collection; to achieve this, specify the name of the view in the alternate view text box. By default, this text box is set to none, which implies that the alternate view is not used by default.

    This alternate ‘view’ should be created with the following structure:

    CREATE OR REPLACE VIEW <VIEW_NAME> (

    TABLESPACE_NAME,

    FILE_ID,

    BLOCK_ID,

    BYTES,

    BLOCKS,

    RELATIVE_FNO

    ) AS

    select /*+ use_hash (tsfi, fet2) */ tsfi.tablespace_name,

    tsfi.file_id,

    fet2.block_id,

    tsfi.blocksize * fet2.blocks,

    fet2.blocks,

    tsfi.relfile#

    from (select /*+ use_hash (ts, fi) */ ts.name tablespace_name,

    fi.file# file_id,

    ts.BLOCKSIZE,

    fi.relfile#,

    ts.ts#

    from sys.ts$ ts,

    sys.file$ fi

    where ts.ts# = fi.ts#

    and ts.online$ in (1,4)) Tsfi,

    (select f.block# block_id,

    f.length blocks,

    f.file# file_id,

    f.ts#

    from sys.fet$ f

    union all

    select f.ktfbfebno block_id,

    f.ktfbfeblks blocks,

    f.ktfbfefno,

    ktfbfetsn

    from sys.x$ktfbfe f) Fet2

    where fet2.file_id = tsfi.relfile#

    and fet2.ts# = tsfi.ts# ;

  1. ISPASSIVE – If the value chosen is yes, then the Oracle server under consideration is a passive server in an Oracle cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable'' by the agent if the server is not up.
Measurements made by the test
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 a 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 a 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 the 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:

Measure Value Numeric Value

Yes

1

No

0

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) upto which a tablespace can grow

 

MB

 

Current size:

Indicates the current allocated size of the 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 the 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.

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 the 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 the tablespace

MB

Remaining extents:

Indicates the number of extents that can be added to a 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.