Select Magazine - April 1998
Setting Some Difficult INIT.ORA Parameters
Steve Adams
From this, it is a simple matter to see how much headroom you have, and whether you are about to bump your head. You can then decide how much headroom you want, and set these parameters accordingly.
The first attempt at working out how many of the various resources were actually being used was to monitor the relevant v$ views using the enclosed database monitoring package. This involved customizing the package to execute a simple query for the usage of each resource every 15 minutes. For example, the query to determine the current usage of ENQUEUE_RESOURCES was:
SELECT COUNT(*) FROM V$RESOURCE;This technique was certainly helpful in identifying some impending problems. I, however, was left with an uneasy feeling that I might be lulled into a false sense of security in some cases. For example, the enclosed monitoring software might be telling me that I normally have about 20 concurrent TRANSACTIONS, with peaks of up to 28 - comfortably short of the maximum of 40. But brief peaks into the high 30s might be occurring while the monitoring software is not looking.
One simple response to such uncertainty would be to allow excessive headroom and set all these parameters to, say, twice the observed maximum usage. One might reasonably worry, however, about how much SGA memory that would waste. Upon investigation, this concern is mostly warranted. Sometimes, though, this is not the case. There appear to be two types of structures in the SGA which are limited in size by an init.ora parameter. Most are statically allocated arrays, but some dynamically allocated.
For example, the DISTRIBUTED_TRANSACTIONS parameter limits the size of the global transaction mapping table in the SGA. The global transaction mapping table while limited, however, is not fixed in size. Rather, memory is allocated from the shared pool for each distributed transaction as required and this memory may later become available for reuse. On the other hand, parameters such as PROCESSES and SESSIONS set the size of SGA arrays that are created at instance startup, and are fixed in size. If the array fills up, it cannot be dynamically extended and any unused space cannot be reallocated for any other purpose.
The amount of memory "wasted" by unused elements in these arrays turns out to be small, but worth saving. The following figures relate to version 8.0.3 running under HP-UX 10.20:
PROCESSES 752 bytes per entry SESSIONS 3784 bytes per entry TRANSACTIONS 864 bytes per entry ENQUEUE_RESOUCES 72 bytes per entry _ENQUEUE_LOCKS 60 bytes per entry DML_LOCKS 116 bytes per entry MAX_ROLLBACK_SEGMENTS 116 bytes per entry These figures are not so big that you need to worry about a few extra slots in each array, but you will not want to have thousands of them. To avoid such memory wastage on one hand, and to avoid the risk of running out of slots on the other, it is necessary to know the peak requirement for space in each of these arrays. That is, you need to be able to determine the high-water-mark for each array.
As we have already seen, one can monitor the v$ views to get snapshots of the usage of these arrays. But to get high-water-marks, it is necessary to query the underlying x$ tables directly. When querying these x$ tables, it soon becomes evident that a number of rows at the end of each x$ table have zero values for almost all columns, presumably because they were initialized to zero at instance startup. It is also interesting to note that it is possible to distinguish rows that have never been used and have all zero values, from rows that were once used and now have a non-zero status or timestamp column value. This makes it possible to determine the high-water-mark for the usage of these tables. One version of the query to do so is shown at the end of this article. It needs to be slightly different for different versions of Oracle, so if you are not confident to work it out for yourself, email me at [email protected] telling me your version, and I will return a suitable script. Here is a sample of the output on a small version 7.3 test instance.
I run a variant of this query automatically from my pre-shutdown script (which is called from my customized version of dbshut) or weekly on instances that are not shut down routinely. Note that although this information gives you the ability to fine tune the settings of each of these parameters, it is normally satisfactory to just set the PROCESSES and MAX_ROLLBACK_SEGMENTS parameters and allow the rest to default. But in some cases, those defaults are either inadequate or excessive, and you will now be able to see that, and work out a more appropriate setting for your instance.FIXED TABLE HIGH-WATER-MARK USAGE SINCE INSTANCE STARTUP ======================================================== TABLE PARAMETER CURRENT HIGH-WATER PERCENT NAME NAME SETTING MARK USED -------- --------------------- ---------- ---------- ------- X$KSQEQ _ENQUEUE_LOCKS 467 9 2% X$KTADM DML_LOCKS 140 5 4% X$KSQRS ENQUEUE_RESOURCES 160 11 7% X$KTURD MAX_ROLLBACK_SEGMENTS 30 3 10% X$KSUPR PROCESSES 25 8 32% X$KSUSE SESSIONS 32 11 34% X$KDNSSF TEMPORARY_TABLE_LOCKS 32 1 3% X$KTCXB TRANSACTIONS 35 4 11%About the AuthorSELECT 'X$KSQEQ' TABLE_NAME, '_ENQUEUE_LOCKS' PARAMETER, COUNT(*) SETTING, COUNT(DECODE(KSQLKCTIM, 0, NULL, 0)) HWM, TO_CHAR( 100 * COUNT(DECODE(KSQLKCTIM, 0, NULL, 0)) / COUNT(*), '99999' ) || '%' USAGE FROM X$KSQEQ UNION ALL SELECT 'X$KTADM' TABLE_NAME, 'DML_LOCKS' PARAMETER, COUNT(*) SETTING, COUNT(DECODE(KSQLKCTIM, 0, NULL, 0)) HWM, TO_CHAR( 100 * COUNT(DECODE(KSQLKCTIM, 0, NULL, 0)) / COUNT(*), '99999' ) || '%' USAGE FROM X$KTADM UNION ALL SELECT 'X$KSQRS' TABLE_NAME, 'ENQUEUE_RESOURCES' PARAMETER, COUNT(*) SETTING, COUNT(DECODE(KSQRSFLG, 0, NULL, 0)) HWM, TO_CHAR( 100 * COUNT(DECODE(KSQRSFLG, 0, NULL, 0)) / COUNT(*), '99999' ) || '%' USAGE FROM X$KSQRS UNION ALL SELECT 'X$KTURD' TABLE_NAME, 'MAX_ROLLBACK_SEGMENTS' PARAMETER, COUNT(*) SETTING, COUNT(DECODE(KTURDEXT, 0, NULL, 0)) HWM, TO_CHAR( 100 * COUNT(DECODE(KTURDEXT, 0, NULL, 0)) / COUNT(*), '99999' ) || '%' USAGE FROM X$KTURD UNION ALL SELECT 'X$KSUPR' TABLE_NAME, 'PROCESSES' PARAMETER, COUNT(*) SETTING, COUNT(DECODE(KSUPAFLG, 0, NULL, 0)) HWM, TO_CHAR( 100 * COUNT(DECODE(KSUPAFLG, 0, NULL, 0)) / COUNT(*), '99999' ) || '%' USAGE FROM X$KSUPR UNION ALL SELECT 'X$KSUSE' TABLE_NAME, 'SESSIONS' PARAMETER, COUNT(*) SETTING, COUNT(DECODE(KSUSEFLG, 0, NULL, 0)) HWM, TO_CHAR( 100 * COUNT(DECODE(KSUSEFLG, 0, NULL, 0)) / COUNT(*), '99999' ) || '%' USAGE FROM X$KSUSE UNION ALL SELECT 'X$KDNSSF' TABLE_NAME, 'TEMPORARY_TABLE_LOCKS' PARAMETER, COUNT(*) SETTING, COUNT(DECODE(KSQLKCTIM, 0, NULL, 0)) HWM, TO_CHAR( 100 * COUNT(DECODE(KSQLKCTIM, 0, NULL, 0)) / COUNT(*), '99999' ) || '%' USAGE FROM X$KDNSSF UNION ALL SELECT 'X$KTCXB' TABLE_NAME, 'TRANSACTIONS' PARAMETER, COUNT(*) SETTING, COUNT(DECODE(KTCXBFLG, 0, NULL, 0)) HWM, TO_CHAR( 100 * COUNT(DECODE(KTCXBFLG, 0, NULL, 0)) / COUNT(*), '99999' ) || '%' USAGE FROM X$KTCXB /
Steve Adams is a UNIX and Oracle performance specialist in Sydney, Australia. He is a regular contributor to comp.databases.oracle.server, and hopes to publish a book, or set up a web site later this year to share some of his insights into Oracle internals and advanced performance tuning.
This is a copy of an article published @ http://www.ioug.org/