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 TABLEright) - 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_STATSin 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; - PrepareAnalyzeStatsEx.sql (user who runs this script must have been granted
- Reclaiming unused space
-
Script: ShrinkLobs.sql
Use
SchrinkLobs.sqlto 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.sqlto see all registered sessions. Note that installing SDL Web creates and runs an Oracle Scheduler job calledCLEANUP_OLD_SESSION_DATAwhich, by default, cleans up your sessions hourly. If you have a pressing reason to do so, you can change this interval.