Design and development of Oracle platform application database system

  • 2020-05-30 21:14:13
  • OfStack

The ORACLE tutorial you are looking at is: design and development of an application database system for the Oracle platform. 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, and each part must be properly configured, designed and optimized to achieve a high-performance database system. This paper discusses the author using Oracle to develop LAN, small database applications, the selection and use of system hardware, application database system design and development of some tips and Suggestions. The application database system includes the application database and the application two aspects of the content, the application database is the production database, and the system database opposite.

Selection and use of system hardware

The hardware closely related to the database system mainly includes CPU, memory, cluster, storage devices, etc., which is not discussed here.

1, CPU

The number and speed of CPU have a direct impact on the speed of database operations. The Oracle database provides the option of parallel query, allowing SQL operations to be performed in a coordinated manner on multiple CPU, which can greatly exert the performance of CPU. Before adding CPU to the system, it is necessary to optimize the SQL code of the application to improve the efficiency of the application. Poor application quality may cause the unnecessary consumption of CPU resources. Second, understand the operating system's limitation on the number of CPU and the scalability of the system. In the case that system CPU resources have been determined, various applications should be analyzed. On the premise of ensuring the normal operation of key applications, the applications that occupy a large amount of CPU resources should be placed in the relatively idle time of the system as much as possible. Good work scheduling can effectively reduce the competitive use of CPU and speed up the response time of the system.

2, memory,

In the database system, there should be enough memory. In an UNIX system, if the physical memory of the system is less than 1GB, the swap area can be set to 4 times the memory, otherwise, it can be set to 2 times the memory, and the swap area should be placed on the fastest hard disk. The size of the SGA area of Oracle is directly related to the performance of database operations. Generally speaking, the size of the SGA area can be set to 55% to 57% of the system's available memory. During the operation of the application system, the memory usage of the system should be monitored regularly, the key applications should be analyzed, and the size of each part of the SGA area should be adjusted timely according to the application. Oracle9i can modify the parameters of the SGA section without restarting the database, changing the size of the SGA section in real time.

3. Storage devices

In the network era, the accumulation and wide application of information resources have posed a greater challenge to the development of data storage technology. The data storage mode has entered into the network storage mode from the traditional bus connection mode. However, the storage devices are still hard disk, tape (with library), disk array, and the traditional storage mode is still dominant in medium and small database applications.

Disk I/O is one of the bottleneck of database operation. The reasonable selection and use of disk is very important in database system. In the initial database system planning, should fully consider the capacity of the system and the expected growth, as much as possible for the future expansion of space. In the selection and use of hard disks and disk arrays, the following points should be noted:

· select a hard disk that supports hot-plug function, so that if the design and development of the hard disk Oracle platform application database system fails, the hard disk can be replaced under the normal operation of the system;

· do not choose a large hard disk. Remember that for Oracle applications, 1-4GB hard disks are suitable. It is better to buy a large number of small and medium-sized hard disks, so as to provide more flexibility when configuring RAID.

· if the disk array is selected, RAID 0+1 is the best configuration method for Oracle database application if the funds can support it. In RAID 5, the performance of read operation has been improved by a certain amount, but the performance of write operation has suffered a great loss. If a certain hard disk fails, the workload of hard disk reconstruction is very large. RAID 5 is suitable for the application of DSS (decision support system), but not suitable for the application of OLTP (online business processing).

· when implementing RAID, to correctly select the size of the bar, the three main factors that determine the size of the bar are: the characteristics of the application (DSS, OLTP, batch processing), the data block size of the operating system and database, 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 data block, and the bar size should also be an integer multiple of the size of the operating system data block. If a bare device is used, the bar size should be the size of the physical data block of the operating system. The strip can be divided in the horizontal direction or in the vertical direction. Horizontal striping is carried out across each hard disk controller, and vertical striping is carried out across the whole hard disk set. The number of members in the striping set shall not be greater than the number of hard disk controllers. For the application of OLTP, the amount of data accessed is not large. You can choose the strip size of 32KB or 64KB, while the amount of data accessed by the application of DSS is large. You can consider the strip size of 64KB, 128KB or 256KB.

Design and development of application database

After the installation and configuration of Oracle database software, it enters the design stage of application database, which includes logical design and physical design. Reasonable logical design will greatly improve the performance of the database and enhance the maintainability of the database. In the design, the entity relationship model is abstrused according to the application, and the entity relationship diagram is mapped into a standardized (data integrity, application independence, storage optimization) relational model (database object). Currently, some auxiliary tools (Oracle Designer, etc.) are available to realize the mapping from the entity relationship diagram to the SQL code. The physical design of database is the storage design of database objects, that is, how to allocate storage space for database objects.

Before the design of database objects, the database management and developers should have a detailed understanding of the application and the application data and its application relationship. The planning and design of database objects based on the application should probably include the following aspects:

· determine the database users to be established, specify the system permissions and table space limits of users, and design profile resource limits for users;

· determine how many tables the application data should be divided into for design. Each table belongs to the user, and each user has the right to operate each table;

· define the structure of each table and determine the primary key and constraints of the table;

· identify which tables are the key tables for the application to run and which are the transaction tables;

· analyze which tables are master tables and which tables are subordinate tables, determine the foreign key constraints between tables and select the appropriate table as the driving table for table join;

· determine which columns are properly indexed on which tables, depending on the application;

· according to the design of table and index, determine the table space and rollback segment to be created, select the appropriate disk for the table space and rollback segment, and create locally managed table Spaces as much as possible to reduce the work of database space management;

· identify triggers and procedures that need to be written;

· select backup and restore policies for database objects.

At the stage of database design, sometimes the characteristics of the appropriate database objects may not be completely determined, and the application design and development may also find inappropriate places that need to be adjusted and modified. However, the more detailed the work at the design stage, the less likely there will be problems and the higher the efficiency of the work.

When creating database objects, according to the characteristics of database objects, combined with the size, quantity and speed of storage devices, database object classification storage, to eliminate or reduce resource competition to the maximum extent. The following principles should be followed when creating database objects:

· application data should be placed in a separate table space, and application data should not be placed in the system table space. In order to prevent inadvertent use of the system table space, the system table space quota of application users is set to 0.

· indexes and tables should be placed in different table Spaces on different hard disks to speed up database operations.

· tables that need to be accessed at the same time should be stored separately to facilitate concurrent access.

· if you have a limited number of disks, you can put infrequently federated tables on the same disk.

· principle of pre-allocation. When creating a database object (table space, rollback segment, table, index, etc.), it is important to set the appropriate storage parameter for the object. When you create an object design, you have an estimate of the size of the object and its expected growth so that you can determine the size of the storage parameters. In general, you should allocate enough space in advance to database objects such as tables and indexes, and database segments should not be dynamically extended too much, as this will affect database performance. A segment (segment) consisting of a block (extent) is ideal. The initial parameter can be slightly larger by 1 point. If possible, it can be set to the maximum capacity. The setting of next parameter is flexible and can be set according to the application, but it must be an integer multiple of db_block_size. In order to reduce the generation of database fragmentation, the pctincrease parameter should be set to 0 as much as possible. For the segment with few update operations, pctfree should be set 1 point smaller; for the segment with many update operations, pctfree should be set 1 point larger. The values of inittrans and freelists should be the same, and the size is related to the number of concurrent transactions.

· the principle of divide and rule. Large database tables and indexes can be considered for partitioning, with different partitions on different disks to better balance I/O. Oracle can query only certain partitions of the table, which will speed up the query. It can delete and load the data on the partition, and can also move the partition. It has more flexibility in the management and control of the table. You can have more policy options and perform better backup and restore operations. Note that after operating on some partitions, the global index must be rebuilt under Oracle8i.

· tables with similar size and growth trend are best placed in the same table space, which can effectively control the generation of hard disk fragmentation and improve the reusability of free blocks.

· tables with the same backup and recovery strategy are best placed in the same table space to facilitate the completion of backup and recovery.

· objects accessed by applications that require a high response time are placed on fast disks.

Related articles: