Design and Development of the Oracle platform application database system

The ORACLE tutorial is the design and development of the application database system on 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), applications, and data. Each part is mutually dependent, each part must be properly configured, designed, and optimized to achieve a high-performance database system. This article discusses some experiences and suggestions on the selection and use of system hardware and the design and development of application database systems in the local area network (LAN) and small database applications developed by the author. The application database system includes application databases and applications. The application database is the production database, which is opposite to the system database.

System Hardware Selection and Use

The hardware that is closely related to the database system mainly includes CPU, memory, cluster, and storage devices. We will not discuss the cluster 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 executed collaboratively on multiple CPUs, to maximize the performance of multiple CPUs. Before adding a CPU to the system, optimize the SQL code of the application to improve the efficiency of the application. Poor quality applications may cause unnecessary CPU resource consumption. Second, measure the test taker's knowledge about the limits on CPU usage and system scalability of the operating system. When the CPU resources of the system are determined, various applications should be analyzed to ensure the normal operation of key applications, place as many CPU-consuming applications as possible when the system is relatively idle. Good Job Scheduling can effectively reduce competition for CPU usage and speed up system response time.

2. Memory

In the database system, ensure that there is sufficient memory. In UNIX systems, if the system's physical memory is less than 1 GB, you can set the swap zone size to 4 times the memory size. Otherwise, you can set it to 2 times the memory size, the swap area should be placed on the disk with the fastest speed. The size of the SGA area of Oracle is directly related to the performance of database operations. Generally, the size of the SGA area can be set to 55% to 57% of the available system memory. Therefore, the SGA area consumes too much of the system memory to reduce the performance. During the operation of the application system, the memory usage of the system should be monitored regularly, key applications should be analyzed, and the size of each part of the SGA zone should be adjusted according to the application. Oracle9i can modify the parameters of the SGA zone without restarting the database, and change the size of the SGA zone in real time.

3. Storage Devices

In the Internet era, the accumulation and wide application of information resources pose a greater challenge to the development of data storage technology. The data storage mode has entered the network storage mode from the traditional bus connection mode. However, the storage devices are still hard disks, tapes (with libraries), and disk arrays. Traditional storage modes are still dominant in medium and small database applications.

Disk I/O is one of the bottlenecks in database operations. The rational selection and use of disks is particularly important in the database system. During initial database system planning, the system capacity and expected growth should be fully taken into account, so as to leave sufficient space for future expansion. When selecting and using hard disks and disk arrays, pay attention to the following points:

· Select a hard disk that supports hot swapping. In this way, when the design and development of the application database system on the hard disk Oracle platform fail, you can change the hard disk while the system is running normally;

· Do not select a hard disk that is too large. Remember to use Oracle Applications ~ A 4 GB hard disk is suitable. It is best to purchase a large number of small and medium hard disks, which provides greater flexibility when configuring RAID;

· If a disk array is selected, RAID 0 + 1 is the best configuration method for Oracle database applications. In RAID 5, the read operation performance has been improved to some extent, but the write performance is greatly reduced. If a hard disk fails, the workload of Hard Disk reconstruction will be huge, RAID 5 is suitable for DSS (decision-making support system) applications and is not suitable for OLTP (online transaction processing) applications;

· When implementing RAID, you must correctly select the size of each shard. The three main factors that determine the size of each shard are application features (DSS, OLTP, and batch processing) the size of data blocks in the operating system and database, and the number of hard disks in the disk array. The size of the data block of the database must be an integer multiple of the size of the data block of the operating system. The size of the data block must also be an integer multiple of the size of the data block of the operating system. If a bare device is used, the split size should be the size of the physical data block of the operating system. The entries can be divided either horizontally or vertically. Horizontal bars span each hard disk controller, and vertical bars span the entire hard disk set. The number of members in the set should not be greater than the number of hard disk controllers. For OLTP applications, the data access volume is small. Generally, the size of 32 kb or 64 kB entries can be selected, while the DSS application accesses a large amount of data, you can consider the size of a 64KB, 128KB, or 256KB shard.

Application database design and development

After the Oracle database software is installed and configured, it enters the design stage of the application database. The application database design includes the logical design and physical design. Reasonable logical design will greatly improve the database performance and enhance the maintainability of the database. In the design, an object relational model (database object) is abstracted Based on the application, and the object relational diagram is mapped to a standardized (data integrity, unrelated to the application, and storage optimization) relational model ), currently, there are some auxiliary tools (such as Oracle Designer) to map object relational graphs to SQL code. The physical design of databases is the storage design of database objects, that is, how to allocate storage space for database objects.

Before designing database objects, you must have a detailed understanding of database management and development personnel and application data and their application relationships. You can plan and design database objects based on applications, it includes the following aspects:

· Determine the database users to be created, specify the user's system permissions and tablespaces, and design a resource limit profile for the user;

· Determine the number of tables that the application data should be divided into. Each table belongs to the user and each user has operation permissions on each table;

· Define the structure of each table and determine the primary keys and constraints of the table;

· Identify which tables are key tables for Application Operation and which are transaction tables;

· Analyze which tables are the master tables and which are the slave tables, determine the foreign key constraints between the tables, and select the appropriate tables as the driving tables for table connection;

· Based on the application, determine on which tables to create appropriate indexes for which columns;

· Based on the table and index design, determine the tablespace and rollback segment to be created, select an appropriate disk for the tablespace and rollback segment, and create a locally managed tablespace as much as possible, reduce database space management;

· Define the triggers and processes to be compiled;

· Select backup and recovery policies for database objects.

In the database design phase, sometimes it is not necessary to fully determine the characteristics of the appropriate database objects. In application design and development, it is also possible to find an inappropriate place. You need to go back and make adjustments and modifications. However, the more detailed the work in the design phase, the less likely the problem may occur, and the higher the work efficiency.

When creating a database object, you need to store the database object categories based on the characteristics of the database object and the size, quantity, and speed of the storage device to eliminate or reduce resource competition to the maximum extent. The following principles should be followed when creating database objects:

· The application data should be placed in a separate tablespace. Do not place the application data in the system tablespace. To prevent unintentional use of the System tablespace, set the system tablespace quota of the Application User to 0.

· Indexes and tables should be placed in different tablespaces on different hard disks, which will speed up database operations.

· Tables that need to be accessed at the same time should be stored separately, facilitating the implementation of concurrent access.

· If the number of disks is limited, you can place tables that are not frequently accessed together on the same disk.

· Pre-allocation principle. When creating database objects (tablespace, rollback segments, tables, indexes, etc.), it is very important to set appropriate storage Parameters Based on the object situation. When creating an object design, there is an estimate of the object capacity and expected growth so that the storage parameter size can be determined. In general, you should allocate enough space to tables, indexes, and other database objects in advance, and do not perform dynamic expansion in database segments because it will affect the database performance. A segment consists of a partition (extent). The initial parameter can be slightly larger. If possible, it can be set to the maximum capacity, initial must be an integer multiple of db_block_size. The next parameter must be set flexibly Based on the application, but must also be an integer multiple of db_block_size. To reduce the generation of database fragments, the pctincrease parameter should be set to 0 as much as possible; the segment with few update operations should be set to a smaller value for pctfree, the segment with many update operations should be set to a greater value; the value of inittrans and freelists should be equal, the size is related to the number of concurrent transactions.

· The principle of separation and governance. For large database tables and indexes, you can consider storing partitions. different partitions can be located on different disks to better balance I/O. Oracle can only query certain partitions of a table, which increases the query speed. It can delete, load, and move partitions, table management and control are more flexible; more policy options are available for better backup and recovery operations. Note: After performing operations on certain partitions, you must re-create the global index under Oracle8i.

· Tables of the same size and growth trend should be placed in the same tablespace to effectively control the generation of Hard Disk fragments and improve the reusability of idle blocks.

· It is recommended that tables with the same backup and recovery policies be placed in the same tablespace to facilitate the completion of backup and recovery.

· Objects accessed by applications with demanding response time are placed on fast disks.