PK22150: (REP) ORACLE DEFAULT STORAGE OPTION FOR CLOBS RESULTS IN WORSE P ERFORMANCE THAN LONG LOBS

APAR status

• Closed as program error.

Error description

• Environment:
  - OS : windows
   - Product name: ClearQuest 2003.06.14
         - DB Vendor: Oracle 9.2, CLOB storage
  Reproducible Y/N: yes
  Description of problem:  Different queries have worse
  performance than LONG lobs when using default storage option for
  CLOBs. ClearQuest is using default options.
  Query for a record type family took less than 5 seconds with
  LONG lob, take about 50 seconds with CLOB and default storage
  settings.
  Tests show that  performance is like with LONG lobs again if the
  "Disable Storage in row" is selected for the CLOBS.
  Step to reproduce:
  A database with 2,3 stated record types in Oracle with lob_type
  LONG.
  Create a record family
  Load with a lot of stated record types, more records, more
  visible the difference. Add text smaller than 4K to all the
  multiline fields.
  Run different queries over the record family
  Run different queries over the redord types
  Move the database to a db with CLOBS
  Run the same queries
  -> worse performance than with lob_type LONG
  Create tables in the db with CLOBS so that the CLOB storage is
  "Disable storage in row"
  Run the same queries
  -> better performance
  Workaround:
  After the move to CLOBS, define new tables that contains CLOBS
  with the option "Disable storage in row" and move the data to
  this tables.
  

Local fix

Problem summary

• It is not possible to use the DISABLE STORAGE IN ROW option
  for CLOB fields in Oracle database.
  

Problem conclusion

• Fixed in ClearQuest 2003.06.16-2007C, 7.0.0.1-ifix04, and
  7.0.1.0-ifix01.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  CLEARQUEST WIN

• Fixed component ID

  5724G3600

Applicable component levels

• R603 PSN

     UP