Oracle Object Statistics Test

This test is used for finding waits that are associated with a specific Oracle table. The most important of these object-level wait events will give you clues to the source of the contention. The Oracle Object Statistics test monitors these waits and reports the number and type of waits.

Note:

To perform this test , you have to set the STATISTICS_LEVEL parameter in the Init parameter file (in the <ora_home>\ora<oracle_version>\database\ directory by default) either to Typical or ALL. By default it is Typical. Otherwise the Oracle will not be able to collect the Object level statistics.

This test is disabled by default. To enable the test, go to the enable / disable tests page using the menu sequence : Agents -> Tests -> Enable/Disable, pick Oracle Database as the Component type, Performance as the Test type, choose this test from the disabled tests list, and click on the << button to move the test to the ENABLED TESTS list. Finally, click the Update button.

Note:

This test is applicable only for PDB (Pluggable Database) configuration of an Oracle Database with Multi-tenant support.

Target of the test : An Oracle server (9i and 10g)

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every username.objectname configured.

Configurable parameters for the test
Parameter Description

Test period

How often should the test be executed

Host

The host for which the test is to be configured.

Port

The port on which the server is listening.

Username

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.

Password

Specify the password of the specified database user.

Confirm Password

Confirm the Password by retyping it here.

Object Name

Specify a comma-separated list of objects - i.e., tables and/or indexes - that need to be checked for fragmentation. Every object name should be specified in the following format: <DisplayName>:<schema_name>.<object_name>, where schema_name refers to the name of the object owner, and object_name refers to the name of the table/index you want to monitor. The DisplayName in your specification will appear as the descriptor of this test. For instance, to monitor the fragmentation-levels of alarm and history tables owned by user admin, your specification would be: AlarmMon1:admin.alarm,AlarmMon2:admin.history. To monitor all objects in a schema, the specification would be of the following format: <DisplayName>:<schema_name>.*. For example, to monitor all the objects in the admin schema, your specification would be: AlarmMon:admin.*.

You can also configure the Object Name to indicate what percentage of records in a table are to be considered by this test for running fragmentation checks. To achieve this, your Object Name specification should be of the following format: <DisplayName>:<schema_name>.<table_name>@<Percentage_of_records_in_the table>. For instance, say that you want to configure this test to monitor the fragmentation level of 20% of the alarm table and 30% of the history table. The Object Name specification in this case will be: AlarmMon:admin.alarm@20,AlarmMon1:admin.history@30. It is recommended that you keep this ‘percentage value’ small, as higher values will make this test that much more resource-intensive.

Note:

Make sure that you configure the Object Name parameter with only table names and/or index names, and not view names. This is because, tables and indexes alone get fragmented, and not views.

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.

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.

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.

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.

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.

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

Keystore File

This parameter is applicable only if the target Oracle database is SSL-enabled, if not, set this parameter to none.

Keystore contains the private keys for the certificates that the client can provide to the server upon request. eG agent requires access to the keystore where client certificate is stored to send that to the server so that the server validates the certificate against the one contained in its trustore. For this purpose, first create the client certificate in the following default location /opt/egurkha/jre/lib/security/egmqsslstore.jks.

Keystore Password

This parameter is applicable only if the target Oracle database is SSL-enabled, if not, set this parameter to none.

If a Keystore File name or file path is provided, then, in this text box, provide the password that is used to obtain the associated certificate details from the Keystore File.

Confirm Password

Confirm the Password for Keystore by retyping it here.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

ITL waits:

 

Indicates the number of times ITL waits have occurred during the last measurement period.

 

Number

 

In oracle, when a row is locked by a transaction, that information is placed in the block header where the row is located. When another transaction wishes to acquire the lock on the same row, it has to travel to the block containing the row anyway, and upon reaching the block, it can easily tell that the row is locked from the block header. There is no need to queue up for some single resource like a lock manager.

ITL is the portion of the block header that contains information on locking. It is a simple data structure called “Interested Transaction List” (ITL), a linked list data structure that maintains information on transaction address and rowid. ITL contains several slots or place holders for transactions. When a row in the block is locked for the first time, the transaction places a lock in one of the slots with the rowid of the row that is locked. In other words, the transaction makes it known that it is interested in the row.

During the table creation, the INITRANS parameter defines how many slots are initially created in the ITL. When the transactions exhaust all the available slots and a new transaction comes in to lock a row, the ITL grows to create another slot. The ITL can grow up to the number defined by the MAXTRANS parameter of the table, provided there is space in the block.

Sometimes, transaction may not be able to find a free slot to place its lock information. This can occur because either (i) the block is so packed that the ITL cannot grow to create a free slot, or (ii) the MAXTRANS has already been reached.

To overcome this, do the following:

  • Reorganizing the table by setting a high value of INITRANS will make sure that there are enough free slots in the ITL, and there will be minimal or no dynamic extension of the ITL.
  • The other option is to make sure the data is less packed so that ITL can grow enough to accommodate the surges in ITL.

Buffer busy waits:

Indicates the number of times Buffer Busy waits that have occurred during the last measurement period.

Number

Buffer busy waits occur within Oracle when a task goes to fetch a data block, but it must wait because another task has control of the data block in the buffer.  A buffer busy wait is often caused by contention on an Oracle table header block because multiple tasks are waiting their turn to grab a freelist to place their new data rows.

This buffer busy wait condition happens for two reasons:

  • Another session has the buffer block locked in a mode that is incompatible with the waiting session’s request.
  • The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.

Ideally, value of this measure should be low. If this measure is high, you can reduce the buffer busy wait events by doing the following:

  • By tuning the SQL to access this object’s rows with fewer block reads by adding indexes;
  • Adding freelists to tables and indexes, or by adding this object to keep cache.

Row lock waits:

Indicates the number of times Row Lock Waits have occurred on this object during the last measurement period.

Number

 

Physical reads:

Indicates the number of physical reads that occurred on this object during the last measurement period.

Number

Physical reads/writes – whether direct or not – increase the processing overheads incurred by the database. Therefore, ideally, the value of these measures should be kept at a minimum at all times.

In the event of an unusually high number of Buffer busy waits, you might want to take a look at the values of these measures, to identify the bottleneck.

Direct physical reads:

Indicates the number of times direct physical reads occurred on this object during the last measurement period.

Number

Physical writes:

Indicates the number of physical writes that occurred on this object during the last measurement period.

Number

Direct physical writes:

Indicates the number of times direct physical writes occurred on this object during the last measurement period.

Number

Logical reads:

Indicates the number of logical reads that occurred on this object during the last measurement period.

Number