Skip to main content

Recommendations and Best Practices for Gathering Statistics

Article # 304104 - Page views: 1090



Applies To

ERP System: All ERP Systems

MarkView Version: All Versions


  • Performance issues can occur for various reasons, but one of the primary causes is statistics not being run correctly or being run infrequently. For any MarkView solution, you must gather statistics on the MarkView schema and Kofax Analytics for MarkView schema as applicable on a regular basis (usually weekly), in order to maintain optimal database performance.

    If statistics have not been gathered for the MarkView or Advisor schema or Kofax Analytics for MarkView schema, or if statistics for some objects have been deleted or do not exist, the Cost-Based Optimizer (CBO) may not determine the most-optimal execution plan for the query which can lead to performance issues.

You can check to see when statistics were last gathered by checking the last_analyzed column in the following query where the SchemaOwner should be set to either the MarkView or Advisor Schema or Kofax Analytics for MarkView schema:

select owner, table_name, last_analyzed from dba_tables where owner = '&SchemaOwner';

The Performance Information page in MarkView Support Tools can also be used, as it shows a Last Analyzed column for tables and indexes in the MarkView Schema(Kofax Analytics for MarkView schema as applicable) and colors each cell in red, where the object may not have had statistics run recently enough.


  • In general we defer to the DBA on the best method for gathering statistics in customer specific environments. However the following script is one example that can be executed to gather statistics. SchemaOwner refers to the Schema name of the MarkView Schema. If you are utilizing MarkView Advisor this should also be executed against the Advisor schema. If Kofax Analytics for MarkView is installed this should be executed against the Kofax Analytics for MarkView schema.
dbms_stats.gather_schema_stats(ownname => '&SchemaOwner',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
cascade => true);

NOTE: We have seen certain scenarios where there is a requirement to gather statistics with a higher estimate_percent parameter value in the MarkView schema.

These situations are often related to performance issues querying the sf_pending_user_items / sf_pending_user_queues / sf_pending_function_queues views which can affect the end user when clicking Get Next or when logging into their Web Inbox, Working Folder (Oracle Applications) or Console ( SAP ).

In these situations, the attached GatherPendingStats100Time_v2.sql and GatherNonPendingStats60Time_v2.sql should be run to address the problem.

Note: If any MarkView table stats are locked, you can simply unlock them via: DBMS_STATS.unlock_schema_stats('MARKVIEW'); .... when MARKVIEW is the schema name.

Note-Icon.png Note: We recommend gathering statistics on a 'quiet' system'.

If the problem persists after using these scripts, please take the steps described in the Troubleshooting Get Next performance Issues article.


Keywords: stats , working folder , performance , slow performance