The Oracle tutorial being looked at is the design and development of Oracle Platform application database system. Oracle is currently the most widely used database system. A complete database system includes system hardware, operating system, Network layer, DBMS (database management System), application and data, each part is interdependent, each part must be reasonably configured, designed and optimized to achieve High-performance database system. This paper discusses the author's experiences and suggestions on the application of Oracle in the development of LAN and small database, the selection and use of system hardware, the design and development of database system. The application database system contains both application database and application database, which is the production database and the system database.
Selection and use of system hardware
The hardware that is closely related to the database system is CPU, memory, cluster, storage device and so on, the cluster is not discussed here.
1, CPU
The number and speed of CPUs directly affect the speed of database operations, Oracle database provides parallel query options, allowing SQL operations to be implemented in a coordinated manner on multiple CPUs, to a large extent to maximize the performance of multiple CPUs. Before adding CPUs to the system, you first need to optimize the application's SQL code, improve the efficiency of the application, and the poor quality of the application may cause unnecessary consumption of CPU resources; second, understand the operating system limits on the number of CPUs and the scalability of the system. When the system CPU resources have been set, we should analyze all kinds of applications, and put the application of CPU resource into the system relative idle while ensuring the key application running normally. Good job scheduling can effectively reduce the competitive use of the CPU and speed up the response time of the system.
2, Memory
In a database system, you should ensure that you have large enough memory. In a UNIX system, if the system's physical memory is less than 1GB, the swap area can be set to 4 times times the size of memory, otherwise it can be set to twice times the memory, and the swap area should be placed on the disk's fastest hard drive. Oracle's SGA area size is directly related to the performance of database operations, in general, the size of the SGA area can be set to 55% of the system's available memory to the 57%,SGA area too much consumption of system memory will degrade performance. In the operation of the application system, we should regularly monitor the memory usage of the system, analyze the key applications and adjust the size of the SGA area according to the application. Oracle9i can modify the parameters of the SGA area without restarting the database and change the size of the SGA area in real time.
3, Storage equipment
In the network age, the accumulation and wide application of information resources put forward more challenges to the development of data storage technology, and the data storage mode has entered the network storage mode from the traditional bus connection mode. But storage devices remain hard drives, tapes (with libraries), disk arrays, and traditional storage patterns remain dominant in medium and small database applications.
Disk I/O is one of the bottlenecks of database operation, and the reasonable selection and use of disk is very important in database system. In the initial database system planning, should fully consider the system capacity and expected growth, as far as possible for future expansion sufficient space. In the selection and use of hard disk and disk arrays, the following points should be noted:
• Select the hard drive that supports hot-swappable, so that when the design and development of the hard disk Oracle Platform application database system fails, the hard drive can be replaced when the system is running normally.
• Do not choose too large a hard disk, remember that for Oracle applications, 1~4GB size of the hard disk is more appropriate, it is best to purchase a large number of small and medium hard drives, so that in the configuration of RAID can provide greater flexibility;
• If a disk array is selected, the RAID 0+1 is the best configuration method for Oracle database applications, if the funding is to be supported. In RAID 5, read operation performance has been improved, but write performance loss is great, if a hard drive fails, hard disk rebuild workload is very large, RAID 5 is suitable for DSS (decision support System) application, the OLTP (the on-line thing processing) application is not very suitable;
• When implementing RAID, to properly select the size of the stripe, the three main factors that determine the size of the bar are the characteristics of the application (DSS, OLTP, Batch), the operating system and database block size, and the number of hard disks in the disk array. The data block size of the database should be an integer multiple of the size of the operating system block, and the same stripe size must be an integer multiple of the size of the operating system data block. If you are using a bare device, the stripe size should be the size of the operating system physical data block. The strips may be carried out in a horizontal direction or in a vertical direction. Horizontal bar across each hard disk controller, vertical bar across the entire hard drive collection, the number of members in the stripe collection should not be greater than the number of hard disk controllers. OLTP applications, the amount of data access data is small, the general choice of 32KB or 64KB bar size, and the DSS application access to a large amount of data, you can consider the use of 64KB, 128KB or 256KB bar size.
Design and development of application database
After the Oracle database software is installed and configured, it has entered the design phase of the application database, and the application database design includes logic design and physical design. Reasonable logic design can greatly improve the performance of database and enhance the maintainability of database. In the design, the entity Relationship model is abstracted from the application, the entity diagram is mapped to the relational model (database object) of standardization (data integrity, application-independent, storage optimization), and some auxiliary tools (Oracle Designer, etc.) are currently implemented to map the entity diagram to SQL code. The physical design of the database is the storage design of the database object, that is, how to allocate storage space for the database object.
Before the database object design, the database management and the developer to the application and the application data and the application relations need to have a thorough understanding, according to the application carries on the database object's planning and the design, probably contains the following several aspects:
• Identify the database users that need to be established, define user's system permissions and table space limits, and design resource constraints profile for users;
• Determine the number of application data should be divided into the table design, each table belongs to the user, each user of the operation of the table of authority;
• Define the structure of each table and determine the primary key and constraints of the table;
• Identify which tables are the key tables for application operations and which are the transaction tables;
• Analyze which tables are primary tables, which tables are from tables, determine the foreign key constraints between tables and tables, and select the appropriate tables to use as table-attached driver tables;
• According to the application, determine which tables on which columns to establish a suitable index;
• Determine the table space and rollback segments to be created based on the table and index design, select the appropriate disk for tablespace and rollback segments, and create locally managed tablespaces as much as possible, reducing work on database space management;
• Identify the triggers and processes that need to be written;
• Select a backup and recovery policy for the database object.
In the database design phase, it is not always possible to fully determine the characteristics of the appropriate database objects, application design and development may also find inappropriate places, need to go back to adjust and modify. But the more detailed the design phase, the less likely it is to have problems and the higher the efficiency of the work.
When the database object is created, the database object classification is stored according to the characteristics of the database object, the size, quantity and speed of the storage device, so as to eliminate or reduce the resource competition. You should mainly follow the following guidelines when creating database objects:
• Application data should be placed in a separate tablespace, do not put application data in the system table space, to prevent unintentional use of system tablespace, the application user's system table space limit is set to 0.
• Indexes and tables should be placed in different table spaces on different hard disks, which can increase the speed of database operations.
• Tables that need to be accessed at the same time are stored separately to facilitate concurrent access implementation.
• If you have a limited number of disks, you can place tables that are infrequently federated to the same disk.
• The principle of pre-distribution. When creating database objects (table spaces, rollback segments, tables, indexes, and so on), it is important to set appropriate storage parameters based on the object's circumstances. When you create an object design, you have an estimate of the object's capacity and expected growth to determine the size of the storage parameter. In general, database objects such as tables and indexes should be allocated sufficient space in advance, and database segments should not be dynamically extended because of the impact on database performance. A segment (segment) is ideally composed of one region (extent), initial parameters can be slightly larger, and if possible, set to the maximum size, initial must be an integer multiple of the Db_block_size, and the next parameter is set to be more flexible, According to the application of the corresponding settings, but also must be db_block_size integer times; In order to reduce the production of database fragmentation, the Pctincrease parameter should be set to 0, the update operation is less than the segment, Pctfree to set smaller, the update operation of a lot of segments to be set larger ; The values of Inittrans and freelists are equal, and the size is related to the number of concurrent transactions.
• Rule of divide and conquer. Large database tables and indexes can be considered for partition storage, and different partitions can be located on different disks to better balance I/O. Oracle can query only certain partitions of a table, which can increase the speed of queries, delete, mount, and move partitions, and have more flexibility in managing and controlling tables, and more policy choices for better backup and recovery operations. Note that after some partitions have been manipulated, the global indexes must be rebuilt under Oracle8i.
• Tables with similar size and growth trends are best placed in the same table space, which can effectively control the production of hard disk fragments and improve the reusability of free blocks.
• Tables with the same backup and recovery policies are best placed in the same table space, which helps with the completion of backup and recovery efforts.
• The objects accessed by the demanding application of response time are placed on a fast disk.
