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 only by a SYS user 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# ;

  8. 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.
  9. SSL- By default, this flag is set to No, as the target Oracle database is not SSL-enabled by default. If the target database is SSL-enabled, then set this flag to Yes.
  10. SSL Cipher-This parameter is applicable only if the target Oracle database is SSL-enabled, if not, set this parameter to none. A cipher suite is a set of cryptographic algorithms that are used before a client application and server exchange information over an SSL/TLS connection. It consist of sets of instructions on how to secure a network through SSL (Secure Sockets Layer) or TLS (Transport Layer Security). In this text box, provide a comma-seperated list of cipher suites that are allowed for SSL/TLS connection to the target database. By default, this parameter is set to none.
  11. TRUSTSTORE FILE- This parameter is applicable only if the target Oracle database is SSL-enabled, if not, set this parameter to none. TrustStore is used to store certificates from Certified Authorities (CA) that verify and authenticate the certificate presented by the server in an SSL connection. Therefore, the eG agent should have access to the truststore where the certificates are stored to authenticate and connect with the target database and collect metrics. For this, first import the certificates into the following default location <eG_INSTALL_DIR>/lib/security/mytruststore.jks. To know how to import the certificate into the truststore, refer toPre-requisites for monitoring Oracle Cluster. Then, provide the truststore file name in this text box. For example: mytruststore.jks. By default, none is specified against this text box.
  12. TRUSTSTORE TYPE-This parameter is applicable only if the target Oracle database is SSL-enabled, if not, set this parameter to none.Specify the type of truststore that contains the certificates for server authentication in this text box. For eg.,JKS. By default, this parameter is set to the value none.

  13. TRUSSTORE PASSWORD-This parameter is applicable only if the target Oracle database is SSL-enabled, if not, set this parameter to none. If a Truststore File name is provided, then, in this text box, provide the password that is used to obtain the associated certificate details from the Truststore File. By default, this parameter is set to none.
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 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:

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) 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.