Use a variety of block Size in Oracle9i

Oracle
　　Oracle9i has brought some surprising complexity to the Oracle database engine. 　　It introduces a number of new internal features, including bitmap free lists, replication based on redo log, dynamic SGA, and a potentially most important feature that can support multiple block sizes. When you ignore all the advanced features, Oracle's job is to transfer data, and disk I/O management and tuning is an important part of any Oracle database.　　Any work that can reduce disk I/O will have a positive impact on Oracle database systems. If we look at a variety of ways to adjust Oracle database performance, we will find that the common goal of Oracle performance tuning is to reduce disk I/O. For example, adjusting an SQL statement to remove a full table search can make the query run faster because it can directly reduce the blocks of data that need to be read by the disk.　　Adjusting the parameters of an instance, such as db_cache_size, can also reduce the cost of the disk. To understand how to use multiple block sizes to improve the performance of Oracle databases, we first look at the basic features of disk I/O. Whenever an Oracle block is accessed from disk, we usually meet with three kinds of latency. The first and most important delay comes from the time that the read-write header is moved. This time refers to the time required for the read-write head to position itself to the correct cylinder.　　There is also the time to select the delay, which is the read-write header waiting for the corresponding block to appear below it, and the third delay comes from sending the data back to the Oracle SGA. In fact, 99% of the latency occurs before the actual data block is actually accessed, so we know that the time required to read a 32K block is actually not quite the same as reading a block of 2K size. In other words, the disk delay time is the same, it and the size of the Block independent.　　So we can conclude that if you can read the largest block per I/O, there will be less overall I/O required by the Oracle database. The principle of buffering is not unique to Oracle databases. Access to memory is computed in nanoseconds, while access to the disk is usually computed in milliseconds. If we can put Oracle's data blocks into the disk buffer, performance will be greatly improved. As Oracle matures and memory becomes cheaper, we will find that the Oracle database's system global Space (SGA) is typically larger than 10GB.　　This greatly improves the performance of Oracle databases, because Oracle data blocks are in memory at read time, and are tens of thousands of times faster than the need to get blocks of data from disk. The memory buffer retains the block of data that Oracle has accessed, and the data in the buffer is read faster than theThe disk reads 14,000 times faster. The memory data buffer has been developed from a single buffer of Oracle7 to three data buffers in oracle8i. Their names are keep pool,recycle pool and DEFAULT pool (as shown in Figure 1). Figure A ***************

Oracle data buffering 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 (Figure 2). Figure 2-oracle9i 8 data buffering ******* in each data buffer, the probability that a block of data is in memory can be known by the hit rate of the data buffer. Allocating memory pages between data buffers to ensure optimal utilization of memory buffers is the work of Oracle administrators. Sometimes there is a noticeable effect by adding some buffering (see Figure 3) ************ Figure three *************** (add to a small data buffer memory page) as the memory buffer increases, the benefits of adding pages are reduced (as shown in Figure 4). Figure 4*************** in large data buffers, the benefits of this drop index and large data blocks before oracle9i, Oracle experts found that by porting the entire database to larger blocks of data, you can reduce disk I/O, Thus the performance of the whole system can be improved. From the surface it seems a bit counterintuitive, one might ask, "If I only need a 80-byte line, what's the benefit of reading 16K blocks?" "The answer to this question is related to the index. Most of the Oracle databases that have been well tuned have indexes that are about the same size as table data.　　For indexes, large chunks of data can undoubtedly reduce I/O, thereby enhancing the performance of the entire database. So the first thing a oracle9i database administrator might do is create a 32K tablespace, a corresponding 32K size buffer, and then migrate all the indexes in their system to this 32K tablespace. By doing so, the Oracle9i database can read a significant number of index node branches in one disk I/O, thereby reducing system pressure and improving overall performance.

　　Assigning objects to multiple block buffers in this way, we can create multiple data buffers in an Oracle database, so how do we decide what data to put into these data buffers?　　Let's look at some of the more common techniques first.　　Isolate a full table search for a large table--the large table for the full table search will benefit from the largest block size, which should be placed in a tablespace with a maximum block size. Carefully set db_recycle_cache_size--if you do not set Db_cache_size to the maximum block size supported 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 you set), and assign large tables that are often required for full table searches to the largest cache. The Data Dictionary (Dictionary) uses the default buffering-you should make sure that the data dictionary (for example, your system tablespace) is always buffered into a data buffer pool.　　Keep in mind that it is more important to ensure that the data buffering of the system table space has enough memory to buffer all the data dictionary blocks than the block size of the data dictionary. Quarantine index--In many cases, Oracle SQL statements will get indexed information through an index range search, and search for a range of values based on the conditions of the SQL statement through a B-tree or bitmap index. Therefore, it is advantageous to put as many indexes in memory as possible.　　One of the first things Oracle 9i database administrators should do is to move their entire Oracle index into a table space that uses large chunks of data, and indexing performance will benefit from large chunks. Isolate random access reads-for databases that are randomly read from disk, Oracle DBAs can move these types of tables into a 2K tablespace. We want to keep in mind that while the disk is getting cheaper, doing so will read some of the query-independent content into memory, something we don't want to see.　　As a result, Oracle DBAs typically use small block sizes for small, randomly accessed tables. Isolate tables for LOB columns-for tables that contain raw, long raw, or in-line lobs, moving them to large blocks of data 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.　　This will reduce the occurrence of row chaining, while the entire LOB can be read in one disk I/O, avoiding the overhead of Oracle having to read multiple blocks. The large table that isolates the full table search--The first launch of recycle pool in oracle8i, it's going toThe method is that a whole table-searching block of data is usually not reread by other transactions, so that they can be quickly purged by the Oracle SGA, so that valuable memory can be used on blocks of data that have a greater chance of being reread by other transactions.　　In Oracle9i, you can set the recycle pool to use a smaller block size. Check the average President--The block size of the table space is larger than the average length of the table (Dba_tables.avg_row_len).　　If it is smaller than the average governor, rows chaining and excessive disk I/O will occur. Use large blocks for data ordering-your temp table space will benefit from the most supported blocks.　　This allows the disk sort sentences to occur in large blocks, thereby reducing disk I/O. The tool for viewing data buffering usage the process of putting Oracle objects into separate data buffers is simple, and oracle9i also provides tools to assist.　　Many Oracle administrators are unaware that these blocks in the data buffer consume an asymmetric data space, and Oracle9i provides a number of scripts that let you see which objects are often in the data buffer. The following query is the number of blocks used to calculate all segment in the current buffer. Depending on the size of your buffer, this may require a lot of sorting space. Column object_name format a40column number_of_blocks format 999,999,999,999column object_name format A40column number_of _blocks format 999,999,999,999select o.object_name, COUNT (1) number_of_blocksfrom dba_objects o, v$bh bhWHERE o.objec　　t_id = Bh.objdand o.owner!= ' SYS ' GROUP by O.object_nameorder by Count (1) desc; Let's look at the number of object names and data blocks in the buffer object_name number_of_blocks--------------------------------------------------------Order _table 123,273order_idx 112,492customer 83,272. . . 　Oem_ext 701　Creating a separate data buffer in oracle9i, it is easy to assign a table or an index block to a table space of different block sizes. When creating a tablespace, we use a new blocksize parameter. In the following example, we created a 32K tablespace in the Oracle database.　　Create Tablespace 32k_tablespacedatafile '/u01/oradata/mysid/32k_file.dbf ' size 100Mblocksize 32k; Once we have created the table space, the next step is to set a database buffer based on the blocksize above. Keep in mind that Oracle 9i no longer uses init.ora files, so we're going to dynamically create a name buffer with the ALTER DATABASE statement. alter system set db_2k_cache_size=200m; alter system set db_4k_cache_size=500m; alter system set db_8k_cache_size=800m; 　　alter system set db_16k_cache_size=1600m; Once we create a memory buffer with a name and a tablespace, we can move the Oracle object to the new tablespace. There are several ways to move objects from one tablespace to another, and many Oracle administrators are accustomed to moving tables using the CREATE table as Select or CTAs syntax.　　For index, you can use ALTER INDEX rebuild to move to another table space. Conclusion for many of the new features of Oracle9i, many experienced DBAs consider block size to be the most important for tuning Oracle databases. Administrators can now use up to 7 separate and different data pools for greater control over the number of data buffer blocks used by each data object. By examining different buffering access features, disk I/O can be greatly reduced, thus greatly improving the performance of the database.