Subject: Top 8 init.ora Parameters Affecting Performance Creation Date: 02-MAR-2000 PURPOSE ------- For analysts & consultants both, how init.ora parameters affect tuning. SCOPE & APPLICATION ------------------- This article is for analysts and consultants alike that require information on how certain init.ora parameters affect performance. How init.ora parameters affect performance. ----------------------------- The parameters listed below are what we consider to be the top init.ora parameters which per se, affect database tuning. If you have any comments/suggestions, please e-mail the author below. ===================== DB_BLOCK_BUFFERS SHARED_POOL_SIZE SORT_AREA_SIZE DBWR_IO_SLAVES ROLLBACK_SEGMENTS SORT_AREA_RETAINED_SIZE DB_BLOCK_LRU_EXTENDED_STATISTICS SHARED_POOL_RESERVE_SIZE ===================== DB_BLOCK_BUFFERS This parameter is available in all versions of Oracle, and is measured in Oracle blocks. The value of this parameter is very important for storing data into memory as users are requesting information from the system. DB_Block_Buffers are the number of buffers in the SGA cache that will be available for user data to be stored in memory. The size of the buffer cache, which is used to cache db blocks in the SGA, is specified by this parameter. Because the data is cached, this reduces the amount of physical I/O. In turn, the setting of this parameter has a large effect on the buffer cache hit ratio, which you generally want to be above 90%. The hit ratio can be dynamically determined based on the following query: select round(((1-(sum(decode(name, 'physical reads', value,0))/ (sum(decode(name, 'db block gets', value,0))+ (sum(decode(name, 'consistent gets', value, 0)))))) *100),2) || '%' "Buffer Cache Hit Ratio" from v$sysstat; The result of this query would be similar to the following: Buffer Cache Hit Ratio: 97.63% If the hit ratio is below 90%, then it would be advisable to increase DB_Block_Buffers until you obtain a hit ration above 90%. The implications of the DB_Block_Buffers being set too low are that the least recently used data will be flushed from memory. So guess what? If another query needs this data, back out to disk we go. I think we can see this will cause I/O and CPU resources to be used. If the value is set too high, swapping will began to occur on the OS and the system may come to a halt. SHARED_POOL_SIZE --------------- The Shared_Pool_Size is specific to all versions of Oracle and is measured in bytes. This is the memory that is allocated to Oracle for areas like the data dictionary, stored procedures, and statements. Comprising a large part of the SGA, the Shared_Pool_Size is comprised of the dictionary cache and library cache, and just like DB_Block_Buffers above, should not be set too low. If this parameter is set too low, you will not be able to take advantage of the memory that has been allocated by the DB_Block_Buffers, even if the DB_Block_Buffers has been set a reasonable value. The Shared_Pool_Size can be monitored through the data dictionary cache and the library cache. Both should be continuously monitored for an appropriate hit ratio. When a user processes a SQL statement, Oracle references the data dictionary several times. Reducing physical disk IO is very important, so the more information that is stored in memory, the less that needs to be read from disk. The data dictionary cache is very important in this respect because this is where the data dictionary components are buffered (think of it as a print buffer). The data dictionary component can be monitored via v$rowcache using the following select statement: select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from v$rowcache; You should see output similar to the following: Hit Ratio 95.40% You should aim to have this value above 90%. The exception is when the database is first started, the value will be somewhere around 85%. More information on the data dictionary cache can be found at Note:10687.1. The library cache consists of shared SQL and PL/SQL areas. When SQL is executed, the statement has to be parsed (or taken apart for further clarification). The library cache reduces the overhead of this by maintaining parsed SQL and PL/SQL in the library cache. As you can see, when there is a subsequent execution of this statement, there will not be the need to re-parse the statement. Essentially, you are reducing the work of the database. If, for example, the customer is on an OLTP system which consistently issues the same SQL, performance is enhanced. Of course, using bind variables in the sql statements always helps. We have discussed having the Shared_Pool_Size set too low, but adverse effects may comes from setting the Shared_Pool_Size too high as well. Having the shared pool set too large is a waste of memory, and may have adverse effects. You can query v$sgastat to show the available free memory. This will tell you memory is being wasted. As an example, let's look at the following problem: select name, bytes/1024/1024 "Size in MB" from v$sgastat where name='free memory'; You should see output similar to the following: NAME Size in MB Free memory 39.6002884 What this return would tell you is that there is 39 M of free memory in the shared pool, which would mean that the shared pool is being under utilized. If the shared pool was 70 M, over half of it would be under utilized. This memory could be allocated elsewhere. SORT_AREA_SIZE ------------------- The SORT_AREA_SIZE is an oft-misunderstood init.ora parameter. Many believe that this parameter is an amount allocated for the users as a whole in terms of sorting. Not true, the SORT_AREA_SIZE is what Oracle allocates per user process for sorting data. Unlike the two previous parameters, the SORT_AREA_SIZE is outside of the SGA. If the sort_area_size is too small, the process has to then be sorted on disk in the user's temporary tablespace. Where does this lead back? Physical disk IO. Because tuning in memory is always preferred over sorting to disk, continuously monitoring the SORT_AREA_SIZE is always a good idea. Having to sort in the temp tablespace means accessing temporary segments. Accessing temp segments artificially decreases the hit ratio, so should be avoided in OLTP applications. The SQL statements that typically generate sort activity are order by and group by clauses. Activities such as create index generate sort activity as well. BEWARE!!!-The SORT_AREA_SIZE can be set within an individual session, if they have ALTER SESSION privilege. What does this mean? A single use can run an ENTIRE system performance. DBWR_IO_SLAVES ------------------ Another oft-misunderstood init.ora parameter, DBWR_IO_SLAVES was introduced in Oracle 8. This parameter was to replace DB_WRITERS that was available before Oracle 8. The truth is that DB_WRITERS is still available in Oracle 8 under the name of DB_WRITER_PROCESSES as there are still a number of issues to work out with DBWR_IO_SLAVES. DBWR_IO_SLAVES was introduced in Oracle 8 to allow for asynchronous I/O of slave writer processes, if it is available on the OS Platform. As the name implies, there is a master process and slaves processes, which are defined by this parameter setting. There is a lot of information to know regarding DB_WRITERS and DBWR_IO_SLAVES. One of the most important things to realize is that DB_WRITERS and DBWR_IO_SLAVES cannot co-exist. There are issues with DBWR_IO_SLAVES still. I once had a customer on version 8 that was having many problems with this parameter (7445s), but the documentation stated that they should move to DBWR_IO_SLAVES for Oracle 8. In the end, my recommendation to the customer was that they remove this parameter, and go back to DB_WRITER_PROCESSES. So you can see that this is on a case-by-case basis. There is a good article in WEBIV, concerning DB Writer written by Bbomard.US located here. ROLLBACK_SEGMENTS ------------------- This parameter, available in all versions of Oracle, specifies the number of rollback segments that are explicitly brought online during instance startup. The rollback segment must already exist, or otherwise, Oracle will give you an ora-1534 regarding the rollback segment. The instance will then mount, but not start. Rollback segments are areas of the database that record transactions should the transactions need to be rolled back or (in Windows terms, 'undo'). The rollback segments consists of contiguous or adjoined extents. The extents are used in a circular (round-robin) order, and move from one extent to the next after the current extent is full. Rollback segments are important to the database, as they are used to provide read consistency, undo of transactions, and recovery. Read consistency is very important if you look at this from a business perspective. If user1 is running a query at the same time user2 is changing the data inside of a particular table, but user2 has incorrect information that he has yet to notice, you wouldn't want user1 seeing this data yet. So user1 can only see this data once it has been committed. There are numerous scenarios where this is important. Rolling back is of course if you make a mistake-the data has not been automatically committed. You can "rollback" and start all over again J. There is always an initial rollback segment of 'SYSTEM' which is created upon database creation, and which you CANNOT drop. An additional rollback segment should be created in the system tablespace for the purpose of creating additional tablespaces in the database. There is of course, a rollback tablespace which you should create and non-system rollback segments in. I've given a brief rundown of rbs, but what size should they be and how many should you have? As with every answer, it is relative and depends entirely on the normal transaction level of your database. I'm not sure that it helps much to tune during one busy period 11:30 pm at night. When sizing the rbs extents, Oracle generally has a recommendation for efficiency reasons that the extent sizes (Initial & Next). In determining the number of rollback segments, the goal is to avoid contention among the rollback segments. Every transaction is stored in a transaction table in the header of the RBS. Because every transaction has to update this table, contention is a possibility. A rule of thumb would be not to have more than one user using a rollback segment at any given time. Oracle recommends four concurrent transactions per rollback segment, but this to is relative. You can look at v$waitstat table in order to monitor contention between the rollback segments. The following query would be useful in examining contention among the rollback segments: Select a. name, b.extents, b.rssize, b.xacts, b.waits, b. gets, optsize, status From v$rollname a, v$rollstat b Where a.usn = b.usn; You should see results similar to the following: NAME EXTENTS RSSIZE XACTS WAITS GETS OPTSIZE STATUS SYSTEM 4 540672 1 0 51 ONLINE RB1 2 10240000 0 0 427 10240000 ONLINE RB2 2 10240000 1 0 425 10240000 ONLINE RB3 2 10240000 1 0 422 10240000 ONLINE RB4 2 10240000 0 0 421 10240000 ONLINE If you run the above query, and the column "xacts" (which are active transactions) are continually above 1 for the RBS's, you will probably need to increase the number of rollback segments to avoid the possibility of contention. If the waits are greater than zero, and this is normal processing, there may also be a need to increase the number of rollback segments. There is a lot more information that could be discussed for rollback segments, much too much for this page, but the following are useful documents: @ Note 10595.1 'Rollback Segment Extents' Note 10579.1 'How Many Rollback Segments To Have' Note 10581.1 'Managing Rollback Segments' SORT_AREA_RETAINED_SIZE ------------------------ Another one of the init.ora parameters which affect sorting on the system, SORT_AREA_RETAINED_SIZE is the size that the SORT_AREA_SIZE is actually reduced to once the sort is complete. This parameter should be set less than or equal to its sister parameter SORT_AREA_SIZE. The SORT_AREA_RETAINED_SIZE complements the SORT_AREA_SIZE because memory is held for the entire duration of the sort, and it allows multiple sort areas of memory if there are more than one sort within the same sql statement. On the downside, this memory is released not when the statement is finished, but when the session is exited. If you compare this to SORT_AREA_SIZE, the memory allocated to the SORT_AREA_SIZE gets released when the last row has been fetched from within the sort. The SORT_AREA_RETAINED_SIZE is, as any memory parameter, limited by physical memory. The recommended setting for both this parameter and SORT_AREA_SIZE is 65K-1MB. DB_BLOCK_LRU_EXTENDED_STATISTICS ----------------------------------- Obsolete in Oracle 8i, this parameter is used to enable or disable to compilation of statistics, which measures the effect of increasing the number of buffers in the buffer cache in the SGA. This allows the user to do an alter system rather than setting the changing the DB_BLOCk_BUFFERS in the SGA. The logic behind this parameter is that the system really has to be shut down to change theo DB_BLOCK_BUFFERS. The statistics are capture in the X$KCBRBH table. Setting this value greater than zero specifies the additional number of DB_BLOCK_BUFFERs, or simulates this additional setting. That sounds fine and dandy, but this parameter has been known to cause a lot of problems. There are warnings in bug 405233 not to set this in production systems. SHARED_POOL_RESERVE_SIZE Specifies the allocation of a certain amount of the shared pool for large objects. In effects, you are reserving some portion of the shared pool. The default setting of this parameter is 5% of your shared_pool_size setting. This parameter is measured in bytes. Setting this value to ensures that the majority of the area within the shared pool is not being used for large objects, but rather in a "separate area" RELATED DOCUMENTS ----------------- [NOTE:106285.1] - TROUBLESHOOTING GUIDE: Common Performance Tuning Issues [NOTE:62161.1] - Systemwide Tuning using UTLESTAT Reports in Oracle7/8