Introduction to Oracle application database system design and development (1)

Oracle is recognized as the most secure database system. Enterprise-level Oracle database application systems include system hardware, operating systems, network layers, and DBMS database management systems), 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 Oracle database application system includes application databases and applications. The application database is the production database, which is opposite to the system database.

The selection and use of system hardware are closely related to the database system, including 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, tape libraries, and disk arrays. Traditional storage modes still play a dominant role 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 select 1 ~ for 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 Support Systems) and is not suitable for OLTP online transaction processing;

· When implementing RAID, you must correctly select the size of the shard and determine the size of the shard. The three main factors 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.

Design and Development of Oracle database applications

After the Oracle database application 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 relationship diagram is mapped to a relational model database object that is complete, irrelevant to the application, and optimized for storage ), currently, there are some auxiliary tools such as Oracle Designer to map the object relationship graph to the 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 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 in which application data should be designed. 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.