Initial Configuration and adjustment of ArcSDE for Oracle

I. Main Content
There are many factors that affect the performance of ArcSDE, but in the final analysis, there are two aspects: the configuration of Oracle and ArcSDE in the background. Oracle not only includes database component size definition, storage parameters, but also the initialization parameters of Oracle instances and Oracle statistics analysis. ArcSDE includes dbtune storage parameters, spatial indexes, and statistics updates. As a spatial database engine built on the back-end database system, the performance of ArcSDE depends largely on the Performance of oracle. In other words, without the support of an Oracle System with good performance, an efficient ArcSDE Data Access Engine cannot be established.

Ii. Reduce Oracle disk I/O
Typical Oracle installation is convenient for database installation, but it will not achieve the expected good performance. Oracle systems that have not undergone careful configuration and adjustment will not be able to achieve satisfactory efficiency. In the Oracle system, disk I/O conflicts cause most obvious performance bottlenecks. In addition to purchasing faster disk drives and additional NICs, the solution is to balance the disk I/O in the file system and minimize it as much as possible, reduces the possibility of I/O waiting between processes.
Principles for rational placement of database Components
If you have estimated the size of the data files, you can determine how to place them in the file system. This section lists some related instructions that do not need to be copied completely, because you need to consider the number and size of your own disk drive. In principle, these guidance are sorted in ascending order of importance. After the database is used for a period of time, a normal usage mode is established. frequently accessed data files need to be isolated from individual disks.
Separate tables and Indexes

Each time Oracle accesses an index to locate a record row, it must simultaneously access the table to retrieve the row to which it points. In this case, if the table and index are stored on the same disk, the head needs to move between the index and the table. Try not to store indexes and tables in the same tablespace. Always store the data files of the table and the data files of the index separately. When the data blocks of the table and its indexes are on the same disk drive, this can reduce the movement of the head.
File placement by activity

Store online redo log files on their own disk drives. Regular edit operations (inserts, updates, or
Deletes) Oracle databases have very active online redo log files. Physically separate online redo log files from other data files that require frequent I/O operations. If possible, create a log file on your own disk drive or store it with other relatively static files. In a database that is frequently edited, online redo log files are the most active part for I/O. If they cannot be arranged on their own independent disk drive, they will be placed together with those files with less I/O operations.
The rollback segment data file must be separated from the redo log file. When the database is edited, the rollback segment is frequently accessed. Try to separate these data files from other highly active files. This can improve the efficiency of Oracle transaction processing.
After the index is created, if the sort_area_size parameter has been set to the recommended kb, the temporary tablespace is no longer used by ArcSDE. Therefore, if other applications do not use temporary tablespace, the temporary tablespace can be placed together with other active files.
Place the system tablespace data files with other highly active data files. The partition rate of the system tablespace data dictionary tables is relatively low because their data is cached in the Shared Pool (shared
Pool) and high-speed buffer (Buffer
Cache.
Place business tables and index data files based on the expected I/O access conditions. If a specific data file is expected to have high I/O access, try to place it separately on your own disk drive or with other less active or moderate data files.

Iii. ArcSDE storage Parameters

Like the Oracle System, the typical Installation Process of ArcSDE is very convenient, but it will not achieve the expected good performance. ArcSDE without configuration and adjustment will not achieve satisfactory efficiency.

Dbtune table

As mentioned above, the performance of ArcSDE depends largely on the efficiency of oracle, so how can we reflect the previously mentioned principle of reducing I/O conflicts and bottlenecks in Oracle to the ArcSDE configuration process? That is, how to control the storage configuration of ArcSDE tables and indexes in Oracle? We can achieve this through the storage parameters defined in dbtune.

ArcSDE reads stored parameters from the dbtunt table to define the physical data parameters of the ArcSDE table and index. These storage parameters are grouped by configuration keywords. When the ArcSDE client creates Data Objects (tables and indexes), these data objects are assigned configuration keywords.

Before ArcSDE, the configuration keyword is stored in a file named "dbtune. Sde" and located in the etc directory of ArcSDE. When executing the sdesetupora8 * command, ArcSDE uses the dbtune. Sde file to initialize the storage parameters. The configuration parameters are read from the dbtune. Sde file and written to the dbtune data table.

It should be noted that ArcSDE has simplified the storage parameters. ArcSDE does not match the same ArcSDE parameter for each Oracle storage parameter. The storage parameter of ArcSDE has evolved to the configured string, reflecting the overall storage configuration of tables and indexes.

Storage parameters of earlier versions of ArcSDE can be automatically converted to storage parameters of the new and simpler ArcSDE version. The storage parameters of ArcSDE retain the Oracle create
Table or create
All storage parameters in the index statement.

ArcSDE generates the dbtune table during installation. If the dbtune. Sde file does not exist or is empty, sdesetupora8 * creates and generates a dbtune table using the default configuration keyword (minimum configuration of ArcSDE. In almost all cases, you need to use the specified storage parameters to create tables for the database.

2. Edit the dbtune table

Although you can use SQL statements to edit the content of the dbtune table, such as SQL * Plus, however, the sdedbtune Command introduced in ArcSDE provides an easy way for the ArcSDE administrator to manage and maintain the dbtune table. Run the sdedbtune command to record and ArcSDE in the dbtune table.
Import or export files in the etc directory.

The transferred file can be modified in the Text Editor ("Vi" under UNIX, or Windows
"Notepad" in NT "). In the following example, the dbtune table is transferred to the dbtune. Out file, and the file is edited by Vi before being transferred to the table:

$
Sdedbtune-O export-F dbtune. Out-u Sde-P Fredericton

$ Vi
Dbtune. Out

$ Sdedbtune-O import-F dbtune. Out-u Sde-P Fredericton
-N

The sdedbtune management tool always transfers files to the etc directory of ArcSDE. You cannot change or specify the file storage location to another path. By disallowing other directories, you can ensure that only the owner of the ArcSDE management directory can execute the sdedbtune command.

3. dbtune modifies an instance

For example, we have created the tablespace sdedata used to store space data in Oracle and the sdeindx tablespace used to store the index respectively. Based on the principle of "separate storage of indexes and Data, build them on different physical drives. Then, we can modify the parameters in the default keyword in dbtune as follows:

# Defaults

Geometry_storage
"Sdebinary"

Attribute_binary "longraw"

B _storage "pctfree 10
Pctused 90 initrans 4 tablespace sdedata

Storage (freelists 4 minextents
1 pctincrease 0 )"

B _index_rowid "pctfree 10 initrans 4 tablespace
Sdeindx

Storage (freelists 4 minextents 1 pctincrease 0)
Nologging"

B _index_shape "pctfree 10 initrans 4 tablespace
Sdeindx

Storage (freelists 4 minextents 1 pctincrease 0)
Nologging"

B _index_user "pctfree 10 initrans 4 tablespace
Sdeindx

Storage (freelists 4 minextents 1 pctincrease 0)
Nologging"

.............

In this example, data in the business table is stored in the sdedata tablespace, and other indexes are stored in the sdeindx tablespace. Of course, other storage parameters, such as initial size, maximum value, and growth rate, can be adjusted as needed.

4. ArcSDE spatial index

Basic Concepts

To improve the performance of spatial queries, ArcSDE uses the spatial index mechanism. Is a two-dimensional index covering the entire element category, similar to the index grid on a general road map. ArcSDE can assign a three-tier spatial index mesh, with each grid layer having its own grid size. The first layer grid is required, and its grid size is the smallest. The second and third layers are optional. Their grids can be set to 0 to make them invalid. If valid, the second-level grid must be at least three times larger than the first-level grid, and the third-level grid must be at least three times larger than the second-level grid.

Create a spatial index

Each time you add a feature category to a business table, a spatial index is automatically created for it. The ArcSDE server manages its spatial indexes throughout the lifecycle of the element class. Spatial indexes are automatically updated when you insert, update, or delete elements. In load-only mode, the management of spatial indexes is disabled until the loading of data is complete. This method fully improves the loading performance and is required when loading data in large batches. The load-only mode does not allow query operations other than SQL.

After the data is loaded, the normal mode is returned, and the spatial index is available. From normal
The transformation from I/O mode to load-only I/O mode will reconstruct the spatial index. In normal
In I/O mode, the inserted, updated, or deleted elements update the spatial index.

ArcSDE first covers the range of each element to the grid at the lowest layer to obtain the number of grids. If the element contains more than four grids, ArcSDE promotes the element to a higher level grid (if a higher level grid is defined ). ArcSDE continues to promote the element to a higher level grid until the element is located within the four grids or has reached the highest level grid level. On the top of a grid, a graph can be indexed by more than four grids.

ArcSDE maps the grid of elements and the corresponding shape.
ID and element range are added to the spatial index table. The grid hierarchy and each grid are encoded. For example, the element class has two grid layers. The surface image 101 is located in the grid No. 1st on the 4th layer. Therefore, a record is added to the spatial index table because the element is within the four grids (actually a grid ). The area 102 ranges from 1 to 8 on the first grid. Because the range of the element exceeds the four grids, the element is promoted to the second layer. In the second layer, the element is located within the two grids. Therefore, element 102 is indexed by the second-layer mesh and two records are added to the spatial index table.

Spatial Query and spatial index

Spatial queries, such as searching all lakes within a State boundary, use spatial indexes. Spatial indexes will be used unless the search order in the se_stream_set_spatial_constraints function is set to se_attribute_first. When the search order is set to se_attribute_first, ArcSDE ignores the spatial index and uses the attribute condition as the WHERE clause to find records in the element class that meet the conditions. When using spatial indexes, ArcSDE generally performs queries according to the following process:

Define the range. The range can be defined by applications, such as ArcMap zoom.
The scope defined by the In tool. In addition, the scope can also be determined by the scope of other elements.

Join the spatial index table and element table to return the grid with the intersection of the range.

Join the element table and business table to apply the WHERE clause in the attribute condition for further query.

Adjust spatial indexes

The grid size affects the size of the spatial index table. Setting a spatial index is to find the equilibrium point of the grid size. A small grid will make each element correspond to a large number of grids, which requires more records in the spatial index table. Because the characteristics of client applications and spatial data vary greatly between systems, there is no single spatial index suitable for all situations, sometimes you need to configure different grid sizes and grid layers.

The sdelayer command has several operation options to optimize the spatial index by changing the grid size and adding a new grid hierarchy ('alter 'operation option ). The 'stats' and 'si _ stats' operations show the current spatial data features and spatial indexes.

The following describes how to improve the query performance:

Consider how many grid layers are required, and remember that the ArcSDE server scans the spatial index table for each grid layer once. Often, a grid layer is the best solution for a element class. Even if you think that the ry is distributed across multiple different grid layers, you can reduce the entry of the spatial index table.

For element classes of the pure point type, use a grid hierarchy and consider increasing the grid size. Space query generally processes point images faster than other types.

Monitor spatial indexes. If the data changes frequently, it is very difficult to adjust the spatial index. The adjustment depends on the structure of the spatial data. When spatial data changes, you can regularly access spatial indexes.

Create a spatial index based on the specific application. Match the size of the application window with the size of the spatial index mesh.

For an unknown or changed application window, a grid hierarchy is defined. The grid size is three times the average range of elements. You can use the following query to obtain the average size of elements:

Select
(AVG (emaxx-emedia) + AVG (emaxy-eminy)/2 from F

(Where is the Layer
Number of the feature
Class)

Design element classes based on data types, such as types, geometric sizes, and distribution. Sometimes well-designed element classes fully improve the performance of spatial queries.

View spatial index statistics

The spatial index statistics operation 'si _ stats' of the sdelayer command can help determine the size of the optimized spatial index mesh. The size of the optimized Grid depends on the Space Range of all element ry, the difference in the range of element ry, and the type of query to be performed on this element class. The following is an example of si_stats output:

$
Sdelayer-O si_stats-l Victoria, parcels-u AV-P Mo-I
Sde81

The output result shows the statistics of each grid layer:

Grid hierarchy and grid size

Total number of spatial index records at the current grid level

Total number of images stored in the current grid hierarchy

Spatial index ratio of each Graph

The number and percentage of images in the group. It indicates how the graph space index is grouped at the grid level.

Average number of images on each grid

Maximum number of grids

Percentage of images completely in a grid

V. Summary

This article does not cover all the ArcSDE performance adjustment and configuration content, nor discuss all factors that affect its performance. This article only introduces some key principles for configuration and adjustment based on the most critical disk I/O and spatial indexes. Configuration and adjustment of other factors, such as Oracle instance initialization parameters, Oracle database component size, Oracle database statistics updates, and other storage parameters of ArcSDE, you can refer to the relevant Guide.