|
DocuShare Database Optimization
Database optimization is a maintenance task that is performed by the Database Administrator (DBA) and usually is scheduled on a periodic basis. Certain DocuShare operations may require the database to be optimized before the next scheduled database optimization. Optimization is recommended after upgrading from earlier 3.x versions, migrating from DocuShare 2.2, or completing large volume updates. For the most benefit, perform database optimization after these changes and before running the dsindex index_all command.
Consult your DBA before attempting to run these commands. The DBA will determine the appropriate settings and configurations for your environment. Only the DBA has the security permissions to run the database optimization.
NOTE: Depending on the size and usage of your DocuShare site, performance may be slow during database optimization. We recommend that you use Site Management/Access and set Site Access Authority to Administrator.
The following commands are recommendations for various database applications. Changes may be necessary to fit your specific environment. All commands in the examples run on the database server, which may or may not be the same server that is running your DocuShare site.
Microsoft SQL Server Desktop Engine (MSDE)
NOTE: This database optimization procedure assumes that the default installation of DocuShare
is on a Windows server.
To optimize the database:
1. Open a command prompt window.
2. Use the CD (change directory) to navigate to the Binn directory where MSDE is installed. For example; CD C:\Xerox\DocuShare\MSDE\Binn.
3. From the Binn directory, run the osql.exe command by entering the following line,replacing all bracketed names with the correct values for your installation:
osql -S [servername]\[MSDE_InstanceName] -d [DocushareDatabaseName] -U
SA -P [SA_Password]
4. Press Enter. The command displays the OSQL prompt 1>.
5. At the 1> prompt, enter UPDATE STATISTICS DSObject_table WITH FULLSCAN;
6. Press Enter.
7. At the 2> prompt, enter UPDATE STATISTICS ACL_table WITH FULLSCAN;
8. Press Enter.
9. At the 3> prompt, enter UPDATE STATISTICS Link_table WITH FULLSCAN;
10. Press Enter.
11. At the 4> prompt, enter UPDATE STATISTICS dsprop_table WITH FULLSCAN;
12. At the 5> prompt, enter go.
13. Press Return. When the optimization processing is complete, OSQL displays a 1> prompt.
SQL Server
An SQL server Database Administrator may use the SQL Query Analyzer tool to update statistics for all DocuShare tables and indexes. The SQL Query Analyzer is part of the Microsoft SQL Server Enterprise Manager, and only members of sysadmin can access the database management tools. Optionally, the DBA may choose to use the SQL Query
Analyzer window to run the commands database optimization commands shown in Microsoft SQL Server Desktop Engine (MSDE).
CAUTION: Only an experienced Microsoft SQL Server DBA should use the analyzer.
Creating unnecessary indexes can severely degrade database performance.
PostgreSQL
You may optimize a PostgreSQL database by running vacuumdb. Depending on the underlying operating system, you may run vacuumdb in either a common prompt window or in a shell window while logged in either as root or as the Postgre user.
Run the command line vacuumdb -d [yourdatabase] -f -z -e, replacing the bracketed
name with the name of your DocuShare database file.
CAUTION: To preserve your PostgreSQL indexes, do not use the PostgreSQL cluster
command. Cluster will physically reorder your index information.
Oracle
You can optimize your Oracle database by keeping database statistics current. Run dbms_stats.gather_schema_stats to gather database statistics for the DocuShare schema, or alternatively run dbms_stats.gather_table_stats to gather statistics on individual tables.
NOTE: For best optimization results, we recommend that you run only dbms_stats.gather_table_stats.
A sample script to gather statistics for the whole schema:
exec dbms_stats.gather_schema_stats(ownname => upper('&schema_name'), cascade
=> TRUE, options => 'GATHER');
A sample script to gather statistics for individual tables:
execute dbms_stats.gather_table_stats( ownname => 'dshare41_fic', tabname =>
'DSObject_table', estimate_percent =>dbms_stats.auto_sample_size, cascade => TRUE,
method_opt => 'FOR ALL INDEXED COLUMNS');
execute dbms_stats.gather_table_stats( ownname => 'dshare41_fic', tabname =>
'ACL_table', estimate_percent =>dbms_stats.auto_sample_size, cascade => TRUE,
method_opt => 'FOR ALL INDEXED COLUMNS');
execute dbms_stats.gather_table_stats( ownname => 'dshare41_fic', tabname =>
'Link_table', estimate_percent =>dbms_stats.auto_sample_size, cascade => TRUE,
method_opt => 'FOR ALL INDEXED COLUMNS');
execute dbms_stats.gather_table_stats( ownname => 'dshare41_fic', tabname =>
'DSProp_table', estimate_percent => dbms_stats.auto_sample_size, cascade => TRUE,
method_opt => 'FOR ALL INDEXED COLUMNS');
| 
