Oracle9i has brought some surprising complexity for the Oracle database engine. It has introduced many new internal features, including Bitmap Free Lists, REDO LOG-based replication, dynamic SGA, and one may be the most important feature, can support multiple block size. When you ignore all of the advanced features, Oracle's job is to transmit data, I / O management and adjustment of disk is an important part of any Oracle database. Any work that reduces disk I / O has a positive impact on the Oracle database system. If we look at a variety of ways to adjust Oracle database performance, it will find that in fact, the common goals of Oracle performance adjustments are I / O of the disk. For example, an operation that adjusts a SQL statement to remove a full table search can make the query run faster because this can directly reduce the data block that needs to be read on the disk. Adjust the parameters of the instance, such as DB_CACHE_SIZE, can also reduce the overhead of the disk. To understand how to use multiple blocks to enhance the performance of the Oracle database, we first check the basic features of disk I / O. When we accessed an Oracle data block on a disk, we usually see three delays. The first is also the most important latency originated from the mobile time of the write head. This time is reading - the time required to position itself to the correct cylinder. There is also a time to choose a delay. This is the reading - the writing is waiting for the corresponding block appears below it, the third delay is derived from the data from the disk to Oracle SGA. In fact, 99% of the delay is happening before truly accessed the actual data block, then we can know that the time you need to read a 32K block is not much different from the block of reading a 2K size. That is, the delay time of the disk is similar, and it is independent of the size of the block. Therefore, we can conclude that if each I / O can read the largest block, the overall I / O required for the Oracle database will be less. The principle of buffering is not unique to the Oracle database. The access to memory is calculated in nanosecond, and access to the disk is usually calculated in milliseconds. If we can put Oracle's data blocks into disk buffers, performance will have a lot of improvement. As Oracle matures and memory become cheap, we will find that the size of the overall space space (SGA) of the Oracle database is usually more than 10GB. This has great improvements to the performance of the Oracle database, because when reading, the Oracle data blocks are in memory, which will be more than 10,000 times relative to the data blocks that need to be obtained by the disk. The memory buffer retains the data block accessed by Oracle, and the data read speed in the buffer is more than 14,000 times faster than reading from the disk. The memory data buffer has been developed via a single buffer of Oracle7 to three data buffers in Oracle8i. Their names are Keep Pool, Recycle Pool and Default Pool (shown in Figure 1). *************** Figure 1 **************
Oracle Data Buffer In Oracle9i, we still have three data buffers, but we can create a data buffer for each block supported by Oracle Server (shown in Figure 2). *********** Figure 2-Oracle9i 8 data buffer ******* In each data buffer, the hit rate by the data buffer can know in memory in memory. The possibility. Assign a memory page between the data buffer to ensure that the optimal utilization of memory buffers is the work of Oracle administrators. Sometimes there is a significant effect by adding some buffers (see Figure 3) ************ Figure three ************** (Add to a small Data buffer memory pages) With the increase of memory buffering, the benefits of increasing the page will fall (as shown in Figure 4). ************* Figure 4 *************** In large data buffers, the benefits of falling indexes and large Data blocks in front of Oracle9i, Oracle expert finds that the I / O of the disk can be reduced by moving the entire database to a larger data block, which can reduce the performance of the entire system. This seems that this is a bit uncommon, people may ask "If I only need a 80-byte line, what is the benefit of reading 16K block?" The answer and index of this problem are related. Most of the well-tunable Oracle databases have indexes with a different size of table data. For an index, large data blocks can undoubtedly reduce I / O so that the performance of the entire database can be improved. Therefore, the first thing to do if the Oracle9i database administrator may create a 32K table space, a corresponding 32K size data buffer, and then transplant all indexes in the system into this 32K table space. Through this, the Oracle9i database can read a considerable number of index nodes branches in a disk I / O, so that the pressure of the system can be reduced and the overall performance can be improved.
Assign objects into multiple block buffers In this way, we can create multiple data buffers in the Oracle database, then how do we decide what data is to these data buffers? Let us first look at some more common skills. Full Table Search for Isolation - For largest tables to be a full table search, they will benefit from the largest block size, they should be placed in a table space using the maximum size. Carefully set db_recycle_cache_size - If you don't set DB_CACHE_SIZE to support 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 all the big tables that often need to perform full menu search to the largest cache. Data Dictionary uses the default buffer - you should make sure that the data dictionary (for example, your System tablespace) is often all buffered into a data buffer pool. To remember, make sure that the data buffer of the System tablespace has sufficient memory to buffer all of the data dictionary blocks is important than the block size of the data dictionary. Isolation Indexing - In many cases, Oracle's SQL statement will search for indexing through an index range search, according to the SQL statement, through the BTMAP index to search for a certain range of values. Therefore, it is good to put as much index as possible. One of the Oracle 9i database administrators first to do is to transfer all of their Oracle indexs to a table space using large data blocks, and index performance will be beneficial in large blocks. Isolation Random Access Reading - For databases that are randomly read from the disk, Oracle DBA can move these types of tables to a 2K table space. We have to remember, although the disk is getting cheaper, but doing this will read some content that is unrelated to the query to memory, this is what we don't want to see. Therefore, for small, random access tables, Oracle DBA usually uses small block sizes. Table of Isolated LOB Columns - For those tables containing RAW, Long Raw, or In-Line Lobs, they will greatly enhance the performance of disk I / O in a large data block. Experienced DBA will check DBA_TABLES.AVG_ROW_LEN to ensure that the block size is larger than the average. This will reduce the occurrence of row chaining, while the entire LOB can read in a disk I / O, avoiding Oracle must read a plurality of blocks. Isolation full table search - In Oracle8i first launched Recycle Pool, its idea is that the data blocks of full table are usually re-read by other transactions), so they can be quickly cleared from Oracle SGA. This allows valuable memory to be used on data blocks that have greater opportunities by other transactions. In Oracle9i, you can set the Recycle Pool to use a smaller block size. Check the average lecture - the block size of the table space is larger than the average leader of the table (DBA_TABLES.AVG_ROW_LEN). If it is smaller than average, Rows Chaining and excessive disk I / O occur. Use large blocks as data sorting - your TEMP table space will benefit from the largest support. Such a disk sort sentence can occur in a large block, thereby reducing disk I / O. The process of viewing the data buffer usage is very simple to put the Oracle object in a separate data buffer. Oracle9i provides some tools. Many Oracle administrators don't realize that these blocks in the data buffer consume an asymmetric data space, while Oracle9i provides a lot of scripts to let you see which objects are often in the data buffer. The following query is the number of blocks used to calculate all segments in the current buffer. According to your buffer size, there is a lot of sort 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.object_id = bh.objdAND o.owner! = 'SYS'GROUP BY o. Object_nameorder by 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,273ORDER_IDX 112, 492CUSTOMER 83, 272.. OEM_EXT 701
It is very simple to create a separate data buffer in Oracle9i, which is simple to assign tables or index blocks to different data block size. When you create a table space, we use a new blocksize parameter. In the following example, we created a 32K table space in the Oracle database.
Create TableSpace 32k_tablespacedatafile '/u01/oradata/mysid/32k_file.dbf'size 100Mblocksize 32K;
Once we create a table space, the next step is to set a database buffer based on the blocksize above. To remember, Oracle 9i no longer uses the init.ora file, so we have to create buffers with names through 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 SESTEM SET DB_16K_CACHE_SIZE = 1600m;
Once we created memory buffer and table space with name, we can transfer Oracle objects to new tablespaces. For a variety of methods, there are many ways, and many of the Oracle administrators have been used to mobile forms using the Create Table As Select OR CTAM. For Index, you can use ALTER INDEX REBUILD to another table space. Conclusion For many new features of Oracle9i, many experienced DBAs believe that the size of the block is most important to adjust the Oracle database. Administrators can now use up to 7 independent and different datasets to make greater control over the number of data buffers used by each data object. By examining different buffer access characteristics, the disk I / O can be greatly reduced, which greatly enhances the performance of the database.
