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:
Alter table <table> storage (freelists 4); Alter table <table> storage (freelist groups 2);
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.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.