While MDS provides database indexes, they might not be used as expected due to a lack of schema statistics. If performance is an issue with MDS operations such as accessing or updating metadata in the database repository, the database administrator must ensure that the statistics are available and current.

The following example shows one way that the Oracle database schema statistics can be collected:

execute dbms_stats.gather_schema_stats(ownname => '<username>',
estimate_percent => dbms_stats.auto_sample_size, method_opt=> 'for all 
columns size auto', cascade=>true);

If performance does not improve after statistics collection, then try to flush the database shared pool to clear out the existing SQL plans by using the following command:

alter system flush shared_pool;

In general, the database must be configured with automatic statistics recollection. For additional information on gathering statistics, see Automatic Performance Statistics in Oracle Database Performance Tuning Guide.

The size of the redo log files can influence performance because the behavior of the database writer and archiver processes depend on the redo log sizes. Generally, larger redo log files provide better performance. Undersized log files increase checkpoint activity and can reduce performance.

For more information, see Sizing Redo Log Files in Oracle Database Performance Tuning Guide.

While manual and auto-purge operations delete the metadata content from the repository, the database may not immediately reclaim the space held by tables and indexes. This may result in the disk space that is consumed by MDS schema to grow. Database administrators can manually rebuild the indexes and shrink the tables to increase performance and to reclaim disk space.

For more information, see Reclaiming Unused Space in Oracle Database Performance Tuning Guide.

Database administrators must monitor the database (for example, by generating automatic workload repository (AWR) reports for Oracle database) to observe lock contention, I/O usage and take appropriate action to address the issues.

See:

• Generating Automatic Workload Repository Reports in Oracle Database Performance Tuning Guide.

• Monitoring Performance in Oracle Database Performance Tuning Guide.

In addition to the main MDS cache, MDS uses a document cache with each metadata store to store thumbnail information about metadata documents (base document and customization documents) in memory. The entry for each document is small (<100 bytes) and the cache size limit is specified in terms of the number of document entries. MDS calculates an appropriate default size limit for the document cache based on the configured maximum size of the MDS Cache, as follows:

• If MDS cache is disabled, MDS defaults to having no document cache.

• If MDS cache is enabled, MDS defaults the document cache size to one document entry per KB of document cache configured.

• If cache-config is not specified, MDS defaults to 10000 document entries.

• If MDS cache is set to a small value, MDS uses a minimum size of 500 for document cache.

In general, the defaults must be sufficient usually. However, insufficient document cache size might impact performance. Set document cache size by adding this entry to the adf-config.xml file prior to packaging the Enterprise ARchive (EAR) file:

<metadata-store-usage id="db1">
  <metadata-store …>
    <property name = …/>
  </metadata-store>
  <document-cache max-entries="10000"/>
</metadata-store-usage>

The DMS metric IOs Per Document Get (visible in Enterprise Manager, see Monitoring Oracle Metadata Service Performance) must be less than 1. If not, consider increasing the document cache size.

The auto-purge interval can be configured or changed post deployment through MBeans. This element maps to the AutoPurgeTimeToLive attribute of the MDSAppConfig MBean. If your application uses the database store for MDS, you can set auto-purge by adding this entry in the adf-config.xml file prior to packaging the EAR:

<persistence-config>
  <auto-purge seconds-to-live="T"/>
</persistence-config>

In the example above, the auto-purge is executed every T seconds and removes versions that are older than the specified time T (in seconds). For more information, see Changing MDS Configuration Attributes for Deployed Applications in Administering Oracle Fusion Middleware.

When you suspect that the database is running out of space or performance is becoming slower, you can manually purge existing version history by using the WLST command or through Oracle Enterprise Manager. Manual purging may impact performance, so plan to purge during a maintenance window or when the system is not busy.

See Purging Metadata Version History in Administering Oracle Fusion Middleware.