Have you ever heard: "My Oracle WebCenter Content instance is running slow. I checked the memory and CPU usage of the application server and it has plenty of resources. What could be going wrong?
An Oracle WebCenter Content instance runs on an application server and relies on a database server on the back end. If your application server tier is running fine, chances are that your database server tier may host the root of the problem. While many things could cause performance problems, on active Enterprise Content Management systems, keeping database statistics updated is extremely important.

The Oracle Database have a set of built-in optimizer utilities that can help make database queries more efficient. It is strongly recommended to update or re-create the statistics about the physical characteristics of a table and the associated indexes in order to maximize the efficiency of optimizers. These physical characteristics include:

• Number of records
• Number of pages
• Average record length

The frequency with which you need to update statistics depends on how quickly the data is changing. Typically, statistics should be updated when the number of new items since the last update is greater than ten percent of the number of items when the statistics were last updated. If a large amount of documents are being added or removed from the system, the a post step should be added to gather statistics upon completion of this massive data change. In some cases, you may need to collect statistics in the middle of the data processing to expedite its execution. These proceses include but are not limited to: data migration, bootstrapping of a new system, records management disposition processing (typically at the end of the calendar year), etc. A DOCUMENTS table with a ten million rows will often generate a very different plan than a table with just a thousand.

A quick check of the statistics for the WebCenter Content (WCC) Database could be performed via the below query:

SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN,
TO_CHAR(LAST_ANALYZED, 'MM/DD/YYYY HH24:MI:SS')
FROM DBA_TABLES
WHERE TABLE_NAME='DOCUMENTS';

OWNER                          TABLE_NAME                       NUM_ROWS
------------------------------ ------------------------------ ----------
    BLOCKS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ
---------- ----------- -------------------
ATEAM_OCS                      DOCUMENTS                            4172
        46          61 04/06/2012 11:17:51

This output will return not only the date when the WCC table DOCUMENTS was last analyzed, but also it will return the <DATABASE SCHEMA OWNER> for this table in the form of <PREFIX>_OCS.

This database username could later on be used to check on other objects owned by the WCC <DATABASE SCHEMA OWNER> as shown below:

SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN,
TO_CHAR(LAST_ANALYZED, 'MM/DD/YYYY HH24:MI:SS')
FROM DBA_TABLES
WHERE OWNER='ATEAM_OCS'
ORDER BY NUM_ROWS ASC;

...

OWNER                          TABLE_NAME                       NUM_ROWS
------------------------------ ------------------------------ ----------
    BLOCKS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ
---------- ----------- -------------------
ATEAM_OCS                      REVISIONS                            2051
        46         141 04/09/2012 22:00:22

ATEAM_OCS                      DOCUMENTS                            4172
        46          61 04/06/2012 11:17:51

ATEAM_OCS                      ARCHIVEHISTORY                       4908
       244         218 04/06/2012 11:17:49


OWNER                          TABLE_NAME                       NUM_ROWS
------------------------------ ------------------------------ ----------
    BLOCKS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ
---------- ----------- -------------------
ATEAM_OCS                      DOCUMENTHISTORY                      5865
       110          72 04/06/2012 11:17:50

ATEAM_OCS                      SCHEDULEDJOBSHISTORY                10131
       244         131 04/06/2012 11:17:54

ATEAM_OCS                      SCTACCESSLOG                        10204
       496         268 04/06/2012 11:17:54

...


The Oracle Database allows to collect statistics of many different kinds as an aid to improving performance. The DBMS_STATS package is concerned with optimizer statistics only. The database sets automatic statistics collection of this kind on by default, DBMS_STATS package is intended for only specialized cases.

The following subprograms gather certain classes of optimizer statistics:
GATHER_DATABASE_STATS Procedures
GATHER_DICTIONARY_STATS Procedure
GATHER_FIXED_OBJECTS_STATS Procedure
GATHER_INDEX_STATS Procedure
GATHER_SCHEMA_STATS Procedures
GATHER_SYSTEM_STATS Procedure
GATHER_TABLE_STATS Procedure

The DBMS_STATS.GATHER_SCHEMA_STATS PL/SQL Procedure gathers statistics for all objects in a schema.

DBMS_STATS.GATHER_SCHEMA_STATS (
   ownname          VARCHAR2,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type
                                                (get_param('ESTIMATE_PERCENT')),
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL,
   statid           VARCHAR2 DEFAULT NULL,
   options          VARCHAR2 DEFAULT 'GATHER',
   objlist          OUT      ObjectTab,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
  force             BOOLEAN DEFAULT FALSE);

There are several values for the OPTIONS parameter that we need to know about:

• GATHER reanalyzes the whole schema    
• GATHER EMPTY only analyzes tables that have no existing statistics
• GATHER STALE only reanalyzes tables with more than 10 percent modifications (inserts, updates,   deletes)
• GATHER AUTO will reanalyze objects that currently have no statistics and objects with stale statistics. Using GATHER AUTO is like combining GATHER STALE and GATHER EMPTY.

Example:

exec dbms_stats.gather_schema_stats( -
  ownname          => '<PREFIX>_OCS', -
  options          => 'GATHER AUTO' -
);