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