HP 3000 Manuals HP 3000 Manuals
Estimating Shared Memory Requirements (DBA)
Each time a multi-user DBEnvironment session is started with either the
START DBE statement or the first CONNECT statement, if the autostart
option is set to on, a block of shared memory is reserved for this
ALLBASE/SQL session. Until the DBEnvironment session is ended, all users
and programs accessing the DBEnvironment share this allocated memory.
This memory remains reserved until the DBEnvironment is stopped, at which
point the memory is made available for re-use by the system. If
AUTOSTART is ON, memory remains reserved until the last DBE session open
against the DBEnvironment terminates. If AUTOSTART is OFF, memory
remains reserved until the DBA issues the STOP DBE statement.
Shared memory is a pool of memory composed of three types of buffers:
* Runtime control block buffer.
* Data buffer.
* Log buffer.
You can specify how much shared memory is to be allocated to each buffer
when you create the DBEnvironment with the START DBE NEW statement. The
parameters you specify for ControlBlocks, DataBufferPages, and
LogBufferPages are stored in the DBECon file. A DBA can temporarily
override several of the parameters that comprise shared memory by using
the START DBE statement, or alter each of the shared memory parameters by
using the SQLUtil ALTDBE command. Initial memory allocation and system
configuration is performed before installation of the product.
Estimating Runtime Control Block Buffer Pages
Each type of DBCore service requires a specific number and size of
control block buffer pages. The number and type needed at any one time
depends on such factors as the number, duration, and type of concurrent
transactions, the amount of[REV BEG] row or[REV END] page level locking,
and the amount of update activity occurring. Whether or not a runtime
control block buffer page is available for re-use by the system depends
on the type of runtime control blocks being used. Transaction lock
control block pages may not be available for re-use until after the
transaction successfully terminates with either a COMMIT WORK or a
ROLLBACK WORK statement.
Lock management is the single greatest user of shared memory. The
greater the number of concurrent locks held, the greater the number of
runtime control block buffer pages needed to manage these locks.
Consequently, a program designed to have shorter transactions, coarser
lock granularity, or more efficient concurrency practices is less likely
to deplete the amount of shared memory available.[REV BEG]
Effects of Page and Row Level Locking.
Page level locking uses more runtime control blocks than table level
locking, since each page must be locked. Row level locking uses even
more runtime control blocks than page level locking, since each row must
be locked individually. This can cause the allocation of a considerable
amount of shared memory. The following indicates the maximum number of
locks associated with table, page, and row level locking:
Table 1-1. Maximum Numbers of Locks Obtained at Different Granularities
---------------------------------------------------------------------------------------------
| | |
| Locking Level | Maximum Number of Locks |
| | |
---------------------------------------------------------------------------------------------
| | |
| Table Level | 1 |
| | |
---------------------------------------------------------------------------------------------
| | |
| Page Level | n + 1 |
| | |
---------------------------------------------------------------------------------------------
| | |
| Row Level | m + (n + 1) |
| | |
---------------------------------------------------------------------------------------------
| | |
| | where n is the number of pages in the table |
| | and m is the number of rows in the table. |
| | |
---------------------------------------------------------------------------------------------
Table level locking requires a single lock. Page level locking requires
up to n page level locks plus one intention lock at the table level. Row
level locking requires up to m row level locks plus up to (n + 1)
intention locks at the page and table levels. Because row level locking
on a large table can consume a tremendous number of runtime control
blocks, the use of the PUBLICROW table type on large tables is
discouraged. Large tables for which maximum read/write concurrency is
desired should generally be defined as PUBLIC. The PUBLICROW table type
should generally be reserved for use on small tables.
As an illustration, consider a table that occupies 500 pages in a
DBEFile. Assume that each page holds 20 rows. Suppose that 40% of all
rows are affected by concurrent activity using index scans at the RR
isolation level on this table--that is, at any one time about 40% of all
rows are being read or updated. Further assume that these rows are
spread out among 80% of the pages in the table.
With page level locking, the number of runtime control blocks used is as
follows:
RCB = 500 * .8 = 400
Adding one for the table level intent lock, the total is as follows:
Total = 400 + 1 = 401
If each RCB occupies 90 bytes, the total memory required would be 36,090
bytes, or 9 runtime control block buffer pages.
With row level locking, the number of runtime control blocks used is as
follows:
RCB = 500 * 20 * .4 = 4,000
In addition to row locks, you need to add the number of page locks
(intent locks) from the earlier calculation. Finally, you should add one
intent lock for the table. The total is as follows:
Total = 4,000 + 400 + 1 = 4,401
Then, if each RCB occupies 90 bytes, the total memory required would be
396,090 bytes, or 97 runtime control block buffer pages.
In addition to the shared memory required for locks, row level locking
also requires additional CPU time to fetch and release the locks.[REV
END]
Running out of Shared Memory.
DBCore allocates memory for runtime control blocks in 4096-byte pages.
When DBCore cannot obtain the required number of runtime control block
buffer pages, the transaction requesting the additional shared memory is
rolled back and ALLBASE/SQL returns the error code -4008. Because the
conditions that caused the DBEnvironment to run out of shared memory may
not exist if the transaction is simply restarted, the programmatic user
can check for this error return code value and re-execute the program a
finite number of times if it occurs. The ISQL user can simply re-execute
the transaction. If a DBEnvironment consistently runs out of shared
memory, you can increase the number of runtime control block buffer pages
by 20 percent and re-try the affected transactions.[REV BEG] If you are
using large PUBLICROW tables, you can use the ALTER TABLE statement to
convert to PUBLIC mode.[REV END] With large LOAD and INSERT operations,
use the LOCK TABLE statement with the EXCLUSIVE option to avoid depleting
shared memory.
A minimum of 17 runtime control block pages is required with the default
value being 37 pages. You can request as many as 800 pages. The number
of 4096-byte pages in the runtime control block buffer is set using the
START DBE NEW statement or the SQLUtil ALTDBE command. When you specify
values for these parameters in START DBE and START DBE NEWLOG, you do not
update the value stored in the DBECon file but change the value for the
current DBEnvironment session only.