Oracle structure design skills (accessing the database is as fast as accessing the memory)

Although the Oracle system has already provided several techniques for tuning system performance, if the database design itself is problematic, especially in terms of structure, what can you do if you have the power to do? Therefore, it is absolutely necessary for Oracle Database designers to figure out how to create a solid Oracle Data Structure (starting from the project, it ensures maintainability and scalability and obtains information from the database as quickly as possible.

Let's put aside the complex theories and obscure technical things that are shrouded in the periphery of the database technology. To put it bluntly, you only need to remember one thing, this is also the most important and simple factor involved in database performance: disk I/O. Disk I/O is the Oracle database operation that consumes the most from the system. When designing the data architecture, Oracle design experts must remember that they must try their best to minimize the disk access volume when obtaining the required information!

This article presents some skills in the design of Oracle Database Data Architecture. With these skills, we can create a design scheme that is easy to maintain and expand while keeping the database above the optimal performance level.

Structure design skills

Oracle has a number of tools to reduce the disk I/O volume of SQL queries. The following are some structural design issues that can greatly improve the performance of Oracle Systems.

Data blocks of various sizes

You can map different tables and index structures to tablespaces of different sizes. This allocation scheme is designed based on the average length of data rows in the table and the data access mode in the entire database. Oracle9i provides you with the following options: 2 K, 4 K, 16 K or even 32 K tablespace size. The essence of this move is that it takes only one disk I/O for tables and indexes to obtain information of all associated data rows.

Pre-computed complex SQL queries

Oracle provides specific views and varray tables, through which you can pre-create complex queries and aggregate them into a single-row table, so that real-time information retrieval is much faster. Real-time collection is an excellent Oracle design.

Memory Data Buffer

You should know that Oracle9i allows a large memory area to buffer the information of common index rows. The buffer of common index information should be a major professional design goal, because the memory access speed is at least 10 thousand times the disk access speed. The larger the Oracle data block buffer, the faster the SQL query execution speed. The size of the memory data buffer has a direct impact on Oracle performance. If the data buffer buffers all data systems, it can achieve the fastest running speed.

Buy faster processors

The CPU speed of the Oracle database server has a direct impact on the database performance. High-performance 64-bit CPUs usually run 10 times faster than 32-bit processors. Currently, almost all major platforms can use 64-bit processors, including:

• Windows-Intel anteng Processor
• HP-PA-8000 Processor
• Solaris-500-mhz ultrasparc-IIE Processor
• Ibm aix-RS/6000 PowerPC Processor

Oracle with 64-bit version

We strongly recommend that you install and run a 64-bit Oracle database system on a dedicated server platform equipped with a 64-bit CPU architecture. The 64-bit version of Oracle allows you to create large-scale SGA regions and large-scale projects that usually require a data buffer of more than 20 GB memory. A serious drawback of a 32-bit Oracle database is that SGA can only open up to 1.7 GB.

Use big data blocks for indexing to minimize disk I/O

Oracle index access has the best performance when the Oracle database size is 16 K and 32 K. You should query the corresponding applicationsProgramAnd the operating system documentation to create the largest index tablespace for your computing environment.

Use Oracle Parallel Query

All data access should be adjusted to avoid large-scale table scans or full table scans. However, in many cases, such queries are required. What should I do? You may wish to ensure that all full table scans fully utilize the Oracle Parallel query mechanism to improve query performance.

Select appropriate SQL Optimization

The optimizer mode has a critical impact on Oracle SQL Performance. For Oracle9i, about half of all queries run faster under rule-based optimization conditions, and the other half run fastest under overhead-based optimization conditions.

Fixed package

The dbms_shared_pool.keep process should be fixed to the shared pool for all frequently referenced PL/SQL packages. This will greatly speed up the execution of Oracle PL/SQL.

Design some data access in the Stored Procedure

One of the most important design problems is to access all databasesCodeStored in PL/SQL.

Stored Procedure design skills

One of Oracle's design goals is to encapsulate all Oracle processing code into the stored procedure as much as possible. In this way, considerable benefits can be obtained, mainly related to performance and maintainability. You should focus your work on this goal.

Data coupling with behavior

Many database administrators use the oracle8 member method to tightly couple stored procedures with database objects. Others are used to naming conventions. For example, assume that all behaviors related to the customer table are prefixed with the table name (customer. hire, customer. you can query the data dictionary to list all the actions associated with a table (select * From dba_objects where owner = 'customer ') you can easily identify and reuse code.

Code isolation

Because all SQL statements are migrated from external programs to stored procedures, the application only involves calling stored procedures. Because of this, it is very easy to exchange a database inside and outside.

Faster SGA access

Why are stored procedures and trigger functions running faster than traditional database code? The main cause is Oracle SGA. After a process is loaded into the SGA shared pool, it will remain "stuck" until the transferred memory makes room for other stored procedures. The principle of calling out the process memory is the so-called LRUAlgorithm. Once the memory area of the Sharing pool is loaded, the process can be executed much faster. The trick here is to try to prevent the sharing pool from being overloaded, because many stored procedures compete for a limited stock in the shared pool. Reiterate: as long as the stored procedure is loaded into the shared pool, it will wait until the memory is called up. Continuous execution of stored procedures is faster than external code.

Summary

One of the symbols of Oracle designers is the ability to create a solid, maintainable, and efficient comprehensive architecture. Today's Oracle design experts need to design a system that supports thousands of transactions per second, while achieving fast response time, simple maintenance, and scalability. As long as you fully understand the features of the Oracle9i database and use the techniques recommended in this article, you will certainly be able to establish an appropriate data model structure to support the needs of end users.