Documentation Center

Maintaining an Oracle database

The SDL Web installation media includes a number of SQL scripts in the \Database\Scripts\oracle\Tools folder that you can use to maintain a Content Manager Oracle database.

These scripts can be run in SQL*Plus. The user running them must be the Content Manager database user because the scripts operate on the Oracle Schema to which you logged in. Run these tools at times of low database activity to prevent unreliable results.

Statistics analysis

Script: AnalyzeStats.sql

Regularly running AnalyzeStats.sql updates the table, column, and index statistics. The cost-based optimizer uses these statistics to select an optimal query plan. Update statistics to ensure that the optimal query plan is chosen. To see when tables, columns, and index statistics were analyzed last, use ShowTableStats.sql, ShowColumnStats.sql, and ShowIndexStats.sql.

Statistics scripts

Scripts:

  • ShowTableStats.sql
  • ShowColumnStats.sql
  • ShowIndexStats.sql

These scripts provide information about the existing table, column or index statistics, as well as the last time these statistics were analyzed.

Rebuilding the indexes

Script: RebuildIndexes.sql

Run RebuildIndexes.sql after substantial changes are made to the database. For example, you should run this script after a bulk import, after using the Purge Tool, or after working with the system for an extended period of time. This script rebuilds the Content Manager indexes. It is necessary to rebuild these indexes to remove deleted rows and restructure the index. To determine if it is necessary to rebuild indexes, use AnalyzeStatsEx.sql.

Extended index statistics

Scripts:

  • PrepareAnalyzeStatsEx.sql (user who runs this script must have been granted CREATE TABLE right)
  • AnalyzeStatsEx.sql

Use PrepareAnalyzeStatsEx.sql and AnalyzeStatsEx.sql to gather extended index statistics. You can use these statistics to retrieve information about the current quality of the index. For example, use it to retrieve the number of deleted rows in the index. PrepareAnalyzeStatsEx.sql creates a table called PLSQL_INDEX_STATS in the current Oracle Schema, which AnalyzeStatsEx.sql then uses to gather index statistics and collect the statistics in a table.

AnalyzeStatsEx.sql fills the table but does not perform queries on the table. You can perform queries on the table that will provide valuable information about the index, such as:

SELECT * FROM PLSQL_INDEX_STATS
ORDER BY NAME;
SELECT * FROM PLSQL_INDEX_STATS
WHERE DEL_LF_ROWS > 0
ORDER BY DEL_LF_ROWS DESC;
SELECT * FROM PLSQL_INDEX_STATS
WHERE BLKS_GETS_PER_ACCESS > 5
ORDER BY BLKS_GETS_PER_ACCESS DESC;
Reclaiming unused space

Script: ShrinkLobs.sql

Use SchrinkLobs.sql to reclaim space used by LOB columns for deleted rows. Note that Oracle does not perform this task by itself.

Keeping track of registered sessions and session cleanup

Script: ShowTcmSessions.sql

Use ShowTcmSessions.sql to see all registered sessions. Note that installing SDL Web creates and runs an Oracle Scheduler job called CLEANUP_OLD_SESSION_DATA which, by default, cleans up your sessions hourly. If you have a pressing reason to do so, you can change this interval.