Oracle provides fairly simple maintenance procedures, just as SQL Server does. PL/SQL scripts can be run, or the DBMS_STATS package can be used, to compute statistics on all tables and indexes in the database. Either method will notify the Oracle optimizer of any changes to your data and subsequently help to eliminate the possibility of inefficient query performance.
Sample at 100% for all tables and indexes when updating statistics.
The itemdata, itemlc, archivedqueue, and chart tables can contain a skewed data distribution. Column level histogram information is typically necessary to ensure optimal query performance. When updating statistics for the itemdata, itemlc, archivedqueue, and chart tables, include the for all indexed columns clause to create a histogram.
The chart table is only used in a Medical Records implementation. If an OnBase system does not use this module, there is no data in the chart table, and it is not necessary to create a histogram for this table.
When using Oracle's DBMS_STATS package, you can use the gather_schema_stats option to gather statistics for all objects in the schema. To create the histograms, you can execute the gather_table_stats option for the itemdata, itemlc, archivedqueue, and chart tables.
Alternatively, gather_table_stats can be run against each individual table, and the histogram can be created only for itemdata, itemlc, archivedqueue, and chart. However, this approach requires an up-to-date list of tables in the database.
Please consult Oracle documentation for more information on this topic.