| The following is a condensation of a paper presented at the Oracle
users conference (1986) by Greg Adams of Glenn A. Barber and Associates
in Studio City Calif.
observed practical limits for a moderate size Oracle application
Processor Number of Oracle Users,(physical memory-MB)
--------- ----------------------
725/730 2 (with 3-5 MB)
750 6-10 (with 6-10MB)
Micro Vax II 8-12 (with 8-12MB)
780 10-18 (with 10-15MB)
785 15-25 (with 15MB or more)
The rule of thumb developed is there should be 1M of memory for
the first 12 users and 1/2M of memory for each user after that.
Once there is sufficient physical memory other VMS parameters
can be adjusted to get better performance out of the system.
The paper reccomends the following values based upon the observation
that large IAP applications require 2000 - 3000 pages.
WSDEFAULT 200 TO 500 pages
WSQUOTA 1000 to 2000 pages
WSEXTENT 2000 to 5000 pages use Authorize to change.
WSINC = 10% to 20% of the diference between the average wsdefault
and wsquota. ~150 to 250 to correspond to the above numbers.
WSMAX should ber as large as the largest extent authorized
WSMAX and WSINC should be adjusted using SYSGEN OR AUTOGEN
It uses system buffers and uses 8K segments so MAXBUF should be set to
8192 bytes.
This parameter should be set using AUTOGEN or pool starvation and
other nasty things could occur on loaded systems.
Other things that can be done are to increase the number of buffers
in the init.ora and adjust buff_hash_bkts so the ratio of
buffers falls between 4 and 8, the max value of buff_hash_bkts is 32.
buff_hash_bkts is going to affect length of search list.
Adjusting init.ora parameters can affect the size of the ORACLE SGA(system
global area) increasing the number of global pages needed. We cut
the disk IO by 50% 42 /sec to 20/sec by just increasing buffers and
buff_hash_bkts. Be sure you link ORACLE at the beginning with an ample
SGA size. When the size of the SGA outgrows the size you've linked ORACLE
to originally, all the applications have to be relinked as well as ORACLE
of course if you're going to increase parameters like buffers. You don't
need to use all of the SGA size that you've linked ORACLE to. We've got
86 and 8700 and 780's in a cluster with a shared database. The machines
with more memory have a larger installed image (higher values of buffers,
processes etc in the init.ora for those nodes, while the 780's use
smaller values with the smaller memory.
Placing the before image file and database file on different disks
can help, particularing in update active environment. Also in
the FROM clauses on joins place the tables in decreasing order by
number of rows,eg. FROM table_with_most_rows,some_rows_table,fewest_rows_
table. Make sure the queries make goo use of indexes, use noncompressed
indexes if you can afford the space. Constructing good space definitions
will help keep the too many extents (internal to oracle) from happening.
Be aware of operator precedence in constructing queries.
--Tim
|