Maintaining an Oracle database
The SDL Tridion installation media includes a number of SQL scripts in the \Database\TDM\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
- 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;