If your PRPC application uses an Oracle database, you may experience poor behavior with some SQL queries. The issue may be related to a character-length limitation in the Oracle database: On Oracle 11g, queries which use a key where the first 32 characters are identical will not use a database index defined on that key.

PRPC provides a highly flexible data model for building objects. Classes follow an inheritance model which allows many levels in a hierarchy. PRPC class names can be 96 characters in length. Due to inheritance, it is possible that a large number of classes in an application start with an identical set of characters. Class names are also often used as the leading key in the definition of a work item. This can lead to the first 32 characters of a key column to be identical for many database entries.

Workaround

Oracle uses histograms (statistics about distinct values in columns) to determine how unique, and therefore how "selective", the data in a given column is. The histograms are calculated on the first 32 characters of a column. If a column has all its values identical in the first 32 characters, the histogram distribution for this column is set to 1, which means "non-selective".

When determining the query plan for a SQL statement that uses this column in a WHERE clause, the database optimizer will choose not to select a perfectly valid index due to the histogram reporting it as non-selective.

The solution for this issue on Oracle 11g is to set the statistics gathering process to not gather histogram statistics on tables that show this behavior. You can disable histogram stats on a table by table with the following command:

SQL> exec dbms_stats.set_table_prefs(ownname=>'PRPC',-
tabname=>'pr_index_reference',-
pname=>’METHOD_OPT’,-
pvalue => 'FOR ALL COLUMNS SIZE 1' );

For Oracle 10g you can achieve similar results by using the SQL statement below:

SQL> exec dbms_stats.gather_table_stats(ownname=>'PRPC',-
tabname=>'pr_index_reference',-
estimate_percent => 100,-
cascade=>true,-
method_opt => 'FOR ALL COLUMNS SIZE 1' );

In this case however, be sure to include this statement in a job that is scheduled to run after regularly scheduled statistics have been run, as scheduled statistics will override the settings in the SQL statement.

A DBA may choose to use the SQL Plan management feature of Oracle Enterprise Manager to set the SQL plan for a statement that is affected by this issue.

Oracle provides extensive documentation in their Metalink site. DBA's working with an Oracle database have access to the site. In particular, these Metalink articles are relevant:

• Best Practices for automatic statistics collection (ID 377152.1)
• Recommendations for Gathering Optimizer Statistics on 10g (ID 605439.1)
• Oracle bug number 6934892 discusses the issue covered in this article