Use multiple block sizes in Oracle9i

Oracle9i brings surprising complexity to the Oracle database engine. It introduces many new internal features, including bitmap free lists, redo log-based replication, dynamic SGA, and one of the most important features, multiple block sizes are supported ).

When you ignore all the advanced features, Oracle's job is to transfer data. Disk I/O management and adjustment are an important part of any Oracle database. Any work that can reduce disk I/O has a positive impact on the Oracle database system.

If we look at a variety of methods to adjust the performance of Oracle databases, we will find that the common goal of Oracle performance adjustment is to reduce disk I/O. For example, adjusting an SQL statement to remove a full table search operation can make the query run faster, because this can directly reduce the data blocks that need to be read from the disk. Adjusting instance parameters, such as db_cache_size, can also reduce disk overhead.

To understand how to use multiple block sizes to Improve the Performance of Oracle databases, first let's look at the basic features of disk I/O. At any time when an ORACLE data block is accessed from a disk, we usually see three latencies. The first and most important latency comes from the moving time of the read-write header. This time refers to the time required for the read-write header to locate itself to the correct cylinder. Also, select the delay time. This is the read-write header waiting for the corresponding block to appear under it. The third delay comes from transmitting data from the disk to the Oracle SGA.

In fact, 99% of the latency occurs before actually accessing the actual data block, so we can know that the time required to read a 32 K block is actually not much different from reading a 2 K block. That is to say, the delay time of the disk is similar, and it has nothing to do with the block size. Therefore, we can conclude that if I/O can read the largest block each time, the overall I/O required by the Oracle database will be reduced.

The buffer principle is not unique to Oracle databases. Memory Access is calculated in nanoseconds, while disk access is usually calculated in milliseconds. If we can put all ORACLE data blocks into the disk buffer, the performance will be greatly improved. As Oracle matures and memory becomes cheaper, we will find that the global space (SGA) of the Oracle database system usually exceeds 10 Gb. In this way, the performance of the Oracle database is greatly improved, because the Oracle data blocks are in the memory at the time of reading, compared to the data blocks that need to be obtained from the disk, this will speed up to thousands of times.

The data block accessed by Oracle is saved in the memory buffer, and the data read speed in the buffer is 14,000 times faster than that in the disk. The memory data buffer has evolved from a single oracle7 buffer to three data buffers in Oracle8i. Their names are keep pool, recycle pool, and default pool (1 ).

***************

　　Oracle Data Buffer

We still have three data buffers in Oracle9i, but we can also create a data buffer for each block size supported by the Oracle server (2 ).

*******

In each data buffer, the hit rate of the data buffer shows the possibility that a data block is in the memory. Allocate memory pages between data buffers to ensure optimal utilization of memory buffers is the work of the Oracle administrator. Sometimes it has obvious effect by adding some buffering (see figure 3)

***************
(Added to a small Data Buffer Memory Page)

As the memory buffer increases, the benefits of adding pages will decrease (4 ).

***************

This reduces the benefits of large data buffering.

　　Indexes and big data blocks

Before Oracle9i, Oracle experts found that by porting the entire database to a larger data block, we can reduce disk I/O and improve the performance of the entire system. On the surface, it seems a bit unreasonable. People may ask, "If I only need an 80-byte row, what are the advantages of reading 16 K blocks? "

The answer to this question is related to the index. Most well-tuned Oracle databases have indexes of the same size as table data. For indexes, large data blocks can undoubtedly reduce I/O, thus improving the performance of the entire database.

Therefore, the first thing the Oracle9i database administrator must do is create a 32 K tablespace and a 32 K data buffer, then Port all the indexes in their system to the 32 K tablespace. In this way, the Oracle9i database can read a considerable number of index node branches in one disk I/O, thus reducing the system pressure and improving the overall performance.

　　Allocate objects to multiple block buffers

In this way, we can create multiple data caches in the Oracle database. How can we decide what data to store in these data caches?

Let's first look at some more common techniques.

　　Isolate full table search for large tables-- Large tables to be searched for in the entire table will benefit from the maximum block size. They should be placed in the tablespace with the maximum block size.

　　Set db_recycle_cache_size carefully-- If you do not set db_cache_size to the maximum supported block size for the server, you should not use the db_recycle_cache_size parameter. Instead, you will create a db_32k_cache_size (or the maximum value you set) and allocate the large tables that frequently need full table search to the maximum cache.

　　Data Dictionary uses the default buffer-- Make sure that the data dictionary (such as your system tablespace) is always buffered into a data buffer pool. Make sure that the data buffer in the system tablespace has enough memory to buffer all the data dictionary blocks, which is more important than the size of the data dictionary blocks.

　　Isolate Indexes-- In many cases, the SQL statement of Oracle uses an index range search to obtain the index information. According to the conditions of the SQL statement, search for a certain range of values through the B-tree or bitmap index. Therefore, it is advantageous to put as many indexes as possible in the memory. One of the first tasks for Oracle 9i Database administrators is to transfer all their oracle indexes to a tablespace that uses large data blocks, index performance will benefit from large blocks.

　　Isolate Random Access reading-- Oracle DBA can move these types of tables to a 2 k tablespace for databases that Randomly Read a small number of rows from the disk. We should remember that although the disk is getting cheaper and cheaper, this will read some content irrelevant to the query into the memory, which we do not want to see. Therefore, Oracle DBA usually uses a small block size for small and randomly accessed tables.

　　Isolate tables with lob Columns-- For tables that contain raw, long raw, or In-line lobs, moving them to large data blocks will greatly improve disk I/O performance. Experienced DBAs will check dba_tables.avg_row_len to ensure that the block size is larger than the average row size. This will reduce the occurrence of row chaining, and the entire lob can be read in a single disk I/O, avoiding the overhead caused by Oracle having to read multiple blocks.

　　Isolate large tables for full table search-- The recycle pool was launched for the first time in Oracle8i. Its idea is that data blocks searched in the entire table are usually not re-read by other transactions ), so that they can be quickly cleared from Oracle SGA, so that valuable memory can be used on data blocks that have a greater chance of being re-read by other transactions. In Oracle9i, you can set the recycle pool to use a smaller block size.

　　Check average president-- The Block Size of the tablespace is larger than the average length of the table in the table (dba_tables.avg_row_len ). If it is smaller than the average duration, rows chaining and excessive disk I/O will occur.

　　Use large blocks for data sorting-- Your temp tablespace will benefit from the largest supported blocks. In this way, the disk sorting statement can occur in large blocks to reduce disk I/O.

　　Tools used to view data buffer usage

The process of placing Oracle Objects in an independent data buffer is very simple. Oracle9i also provides some tools for assistance. Many Oracle administrators do not realize that these blocks in the data buffer consume an asymmetric data space, and Oracle9i provides a large number of scripts for you to check which objects are often in the data buffer.

The following query is used to calculate the number of all segments in the current buffer. Depending on your buffer size, this may require a lot of sorting space.

Column object_name format A40 Column number_of_blocks format 999,999,999,999. Column object_name format A40 Column number_of_blocks format 999,999,999,999. Select O. object_name, Count (1) number_of_blocks From Dba_objects O, V $ BH Where O. object_id = BH. objd And O. Owner! = 'Sys' Group O. object_name Order Count (1) DESC;

Let's take a look at the number of object names and data blocks in the buffer.

Object_name number_of_blocks -------------------------------------------------------- Order_table 123,273. Order_id x 112,492 Client 83,272 ... Oem_ext701

　　Create independent data buffering

In Oracle9i, it is very easy to allocate tables or index blocks to tablespaces of different data blocks. When creating a tablespace, we will use a new blocksize parameter. In the following example, a 32 K tablespace is created in the Oracle database.

Create tablespace 32k_tablespace Datafile '/U01/oradata/mysid/32k_file.dbf' Size 100 m Blocksize 32 K ;

Once the tablespace is created, the next step is to set a database Buffer Based on the blocksize above. Remember that Oracle 9i no longer uses the init. ora file, so we need to dynamically create a buffer with a name through the alter database statement.

Alter system set db_2k_cache_size = 200 m; Alter system set db_4k_cache_size = 500 m; Alter system set db_8k_cache_size = 800 m; Alter system set db_16k_cache_size = 1600 m;

Once we create a memory buffer and tablespace with a name, we can transfer the Oracle object to the new tablespace. There are multiple ways to move objects from one tablespace to another, and many Oracle administrators are used to moving tables using the create table as select or CTAs syntax. For index, you can use alter index rebuild to transfer to another tablespace.

　　Conclusion

For many new features of Oracle9i, many experienced DBAs believe that the block size is the most important thing to adjust the Oracle database. The administrator can now use up to seven independent data pools to control the number of data buffer blocks used by each data object. By examining different buffer access features, you can greatly reduce disk I/O and greatly improve database performance.