ArcSDE DBTUNE and disk configuration in Oracle (Bathymetry Solution)
DBTUNE storage parameters allow you to control how ArcSDE technology creates objects within a geodatabase in Oracle. You can determine how to allocate space to a table or index, in which tablespace a table or index is created, and other Oracle-specific storage attributes. They also allow you to specify one of the available storage formats for the geometry of a spatial column.
The DBTUNE storage parameters are stored in the DBTUNE table. The DBTUNE table, along with all other metadata tables, is created in the database when the Create Enterprise Geodatabase or Enable Enterprise Geodatabase tool is executed.
If a large number of database connections are accessing the same files in the same location on the disk, database performance will be slow, because the connections are competing with one another for the same resources. To reduce this competition, you can store database files in different locations on the disk.
Standard GIS storage recommendations favor keeping index and log files separate from vector and tabular business tables. For performance reasons, it is better to position the business, feature, and spatial index tables separately and position tablespace data files based on their usage pattern. For a multiversioned, highly active editing geodatabase, database files of the VERSIONS tablespace may be separated and dispersed across available disks to avoid input/output contention.
Disk configuration
Large production enterprise geodatabase systems should employ a hardware striping solution. Your best disk and data organization strategies involve spreading your data across multiple disks.
With data spread across multiple disks, more spindles actively search for it. This can increase disk read time and decrease disk contention; however, too many disks can slow down a query. There are two main ways of achieving striping: tablespaces and redundant array of independent disks (RAID). You can also combine the two by creating tablespaces within disk arrays. You can employ data segregation strategies; keeping tables from indexes or certain types of tables from other tables will improve performance and alleviate administrative burdens.
The suggested Oracle optimal configuration is as follows:
- DISK 0—Oracle/Application Software
- DISK 1—SYSTEM, Control File 1
- DISK 2—RBS, TEMP, Control File 2
- DISK 3—REDO 1, 2, 3, Export Files
- DISK 4—Feature data tables
- DISK 5—Spatial index data tables
- DISK 6—Attribute data/business tables
- DISK 7—Oracle indexes
Reducing disk input/output contention
As a rule, you should create database files as large as possible based on the maximum amount of data you estimate the database will contain to accommodate future growth. By creating large files, you can avoid file fragmentation and gain better database performance. In many cases, you can allow data files to grow automatically; just be sure to limit autogrowth by specifying a maximum growth size that leaves some available hard disk space. By putting different tablespaces on different disks, you can also help eliminate physical fragmentation of your files as they grow.
To configure data and log files for best performance, follow these best practices:
- To avoid disk contention, do not put data files on the same drive that contains the operating system files.
- Put redo, undo, and temp data files on a drive separate from data files. This gives you the best performance by reducing disk contention between data and redo, undo, and temp data files.
- Use partitioning on large tables. Partitioning allows you to split a table across multiple tablespaces by using partitions; you can place a subset of a table or index on a designated tablespace. This capability allows you to separate specific pieces of a table or index onto individual tablespaces and effectively manage file I/O for volatile tables. Partitions allow you to easily manage archival routines and data loading operations.
The following is a suggested design to reduce disk input/output contention:
File Type | Database Activity | Move File to Disk With |
|---|---|---|
Redo log | Frequent edits | Relatively low input/output |
Redo log | Few or no edits | Moderate input/output |
Undo log files | Frequent edits | Low input/output but separate from redo log files |
System data | Frequent edits | Moderate input/output |
Temporary tablespace | Few edits | High input/output |