Oracle Parallel Server How To...

---

OPS Tuning - Reduce pinging (i.e. contention)

---

Pinging occurs when both instances need access to the same table or index. The following query will give an indication of the amount of pinging occurring:

Select (a.value / b.value) * 10
    From v$sysstat a, v$sysstat b
    Where a.name = 'DBWR cross instance writes'
    And b.name = 'physical writes';

The nearer to zero the better. Any figure approaching or above 10% indicates that pinging is a problem.

Identifying the causes of contention

To find the number of pings per file:

@catparr.sql
Select ts_name, "Tablespace", file_name, frequency from file_ping;

The most common causes of file contention are:

1. Free list contention, which occurs when inserting into a table. This can be recognised by a single block having multiple copies in the SGA. If the block is the second block in the table, it is probably free list contention that is happening. Increase the values of the ‘FREELIST GROUPS’ and ‘FREELISTS’ parameters, e.g.

Alter table <table> storage (freelists 4);
Alter table <table> storage (freelist groups 2);

The number of freelist groups should be set to the number of instances that are concurrently inserting into the same table.

2. Index contention, which is caused by inserts or deletes on an indexed table. Query v$ping to see the number of data blocks in the first extent of the index that have multiple copies in the SGA and have a high number of pints. If the problem is substantial, it can usually only be solved by partitioning the usage of the index, perhaps by adding an instance specify column to the leading part of the index key.

---

These notes have been tested with Oracle Parallel Server 8.0.5 running under NT 4.

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.