The basis of very large Oracle database design and optimization

Abstract: Based on the Oracle database design features, parallel server applications, partitioning strategy, the design of the physical structure of the database <<large-scale database analysis and DESIGN "> key sections of the study were summarized and discussed, and Oracle environment from large databases Analysis of four different levels of adjustment, analyzes the Oracle system structure and working mechanism, from a more comprehensive summary of the different aspects of the optimal adjustment of ORACLE database program.

Abstract: This paper summarized and discussed the character of basic design, application of OPS, strategy of partition, and design of physical structure of Oracle data base in the important chapters of "Analysis and Design of Large Data Base", analyzed the principle of system architecture and work from the four-grade adjustment of large Oracle data base; and also summarized the optimization design plan of Oracle data base from different aspects.

Keywords: Oracle database, environmental adjustment, optimization design, program
Keywords:: Oracle data base, environment adjustment, optimization design, plan

1 Introduction
For very large systems, we often use double or multiple machine cluster system. In this article, we discuss the design approach used in very large databases, and to use the database to ORACLE 8.0.6 parallel server instance.

2 ORACLE parallel server applications into policy
ORACLE parallel server applications into the following four ways: �� in accordance with the function of the module to distinguish between different nodes running different applications, �� according to the user to distinguish between different types of users running on different nodes, �� according to the data to distinguish, for the same data or index does not use a different access node, �� in accordance with the time to distinguish between different applications to run at different times.

3, the design of the physical structure of the database
3.1 Estimation of the table and index data and physical storage parameters set. Table and index storage parameters, including initial and the next is set to equal, pctincrease set to 0.

3.2 Table space design. In the general design of the table space for the following considerations: �� general large table or index is assigned a separate table space. �� Read only object or objects into a group of Read mostly, there is a corresponding table space. �� If tablespace read only the objects are all objects can be set to read only tablespace mode, the backup, read-only table space backup only once. �� high-frequency insert into a set of objects, there is a corresponding table space . �� add, delete, change the object into a group, there is a corresponding table space. �� tables and indexes are stored in different tablespaces. �� stored in a table space with a table (or index of the extent size of the best into multiple relations is conducive to reuse and reduce space debris.

3.3 DB BLOCK SIZE. Very large database DB BLOCK SIZE in general 4KB to 64KB, while the most commonly used is 8KB, 16KB or 32KB. Use a larger DB
BLOCK SIZE INDEX can reduce the height will also improve IO efficiency.

3.4 Redo Log Files ORACLE using a dedicated process redo log writer (LGWR) to write the log file log.

3.5 Data file size.

We should use the standard file, this can reduce the space maintenance time.

3.6 rollback. Rollback usually built in a dedicated table space. INSTANCE instance each have their own rollback segment.

3.7 temporary table space. Temporary table space is generally built in a dedicated table space.

Links to Research Papers Download http://www.hi138.com 4 division and distribution system hard drive
Consider the following situation: DRD services on host 1 1, the service has 1,2,13,35,67 corresponding data files, etc., then if the host 2, on the need to read the data file 13 INSTANCE2 by DRD service scheduling, host a visit by DRD service data files on the disk array 13, the data needed to INSTANCE2 read memory, and then the data to the host MEMORY IO 2 INSTANCE2. read write operation is the inverse process.

5 backup and recovery strategies to consider
5.1 ORACLE backup method: �� physical backup (for the disk media is damaged or corrupted data files .1 physical cold backup (offline backup.2 physical hot backup (online backup.3 archive log file backup (archived log file backup. �� logical backup ( for some objects in the database is destroyed or user errors. backup strategy considerations, mainly in the following three aspects: 1 storage space, 2 for the impact of operating system, the 3 aspects of recovery time.

5.2 ORACLE recovery method using physical backup and recovery. The following three areas of recovery is provided by the ORACLE: �� the database level recovery, �� table space recovery, �� data file recovery. The database-level restore operation in the following conditions: Close the database but the Mount state. However, to restore the table spaces and data files, you can run the database.

5.3 The use of logical backup and recovery of its application conditions are: First, the user's operational errors led to the destruction of data, and the second, in the database, destroy a certain object.

6 Optimization
6.1 The structure of database optimization free OFA (Optimal flexible Architecture) optimized structure is in the database free to the distribution of any logical data object. Therefore, we should object to the database according to their use of logical methods and by the physical structure of the database to the logical object classification.

6.2 take full advantage of the system global area SGA (SYSTEM GLOBAL AREA
SGA has the following three aspects: �� dictionary buffer. �� data block buffer. �� SQL shared pool. �� redo log buffer.

6.3 Specification and anti-specification design database specification principle is gradually in the data dependent part of the solution is not appropriate, between the mode of each model to a certain extent "of a place" model. But sometimes in order to improve the number of query or application performance, standardized rules to vandalism, retention of non-norm constraints, that is anti-normative.

6.4 Optimal Design Index index first, the use of cluster. Clustered together in the code value according to the number of table data is stored in an Oracle block, so, Oracle blocks obtained at the same time were two tables of data . Second, the management organization index, for large tables in the query when the index data will probably run out of buffer space data blocks, ORACL must always be to get through the disk read and write data, so the partitioning of large tables, we must follow partitions to create partitions on the index.

6.5 Optimal use of the database connection and SQL optimization program first, using a direct OLE DB database connection. Through ADO, we can use the traditional two ways ODBC and OLE DB to connect to the database. Second, the use of Connection Pool mechanism . The principle is in the IIS + ASP system to maintain a connection pool, based on this, the next time a user access to directly access a database in which the connection, while avoiding the re-connection to the database. Third, efficient design of the SQL statement. First, you can query the database the number of (system resource request) decreased, and the second, during the inquiry should be very similar as far as possible using SQL statements, the three dynamic can use SQL constraints.

References:
[1] Zhou Yu Bin. Based on a large database of technical seminars ORACLE8i one database optimization papers [J]. Computer Programming Skills and maintenance, 2002.

[2] urn is Branch, Wang Xinying. Oracle 8.X For Windows NT practical tutorial [M]. Tsinghua University Press, 2001. Links http://www.hi138.com Research Papers Download