Hardware:
- 4 x Dual core Intel Xeon processors
- 12 GB of physical RAM
- 2 x 74 GB SATA Disks configured as RAID-0 (RAID-1 removed to increase performance. Data security done using archiving and automated backups).
Despite having a high performance configuration, Oracle Database 10g R2 (Version 10.2.0.4) was reporting problems with speed. Typical user complaints were as follows
- Startup time for login screen is high.
- Transition between screens is slow.
- Execution of programs within a screen is fast.
- Time lag (Freeze) experienced (anything between 0.5 second to 5 seconds) while traversing between multiple records in one screen.
Analysis:
The Oracle Database was tuned manually. All the values for Pool size and SGA, PGA targets were provided manually in the SPFILE during installation.
Some of them were as follows:
- _db_cache_size=989855744
- _large_pool_size=25165824
- java_pool_size=512165824
- shared_pool_size=233554432
- sga_max_size=2500016000
- sga_target=2500016000
- pga_aggregate_target=262144000
The /3gb switch was turned on for the Operating system. oracle.exe process was able to acquire upto 2.5 GB physical memory. But the performance problems continued. The complete 12GB of physical RAM was also unutilised most of the time.
Solution:
Windows Address Windowing Extension (AWE) was used to increase the Oracle Data Buffer size. The Windows AWE is actually a set of extensions that allows applications like Oracle Database to manipulate physical memory greater than 4GB on 32-bit Operating systems. AWE allows fast memory management.
How To:
To use the Windows AWE, the following was done.
- Turn on /PAE (Physical Address Extension) and /3gb switch on the Operating System so that more than 2GB of addressable space is available to applications.
- Set the parameter AWE_WINDOW_MEMORY to a suitable value in the registry.
Parameter is set in HKEY_LOCAL_MACHINE\Software\Oracle\(Oracle_Home)
Value used: 536870912 - In the init.ora set USE_INDIRECT_DATA_BUFFERS=TRUE
- In the init.ora set DB_BLOCK_BUFFERS to a suitable value.
Value used: 850000 - The above should be sufficient for most databases. But we use poi Java utilities to import/export data to excel that requires a minimum of 500 MB Java Pool size.
Hence, in init.ora JAVA_POOL_SIZE=512165824 - An error was encountered on starting up the database that said that there was insufficient Streams Pool size. For which in init.ora, we set
STREAMS_POOL_SIZE=100M
Note: Oracle's Metalink note - 225349.1 has a detailed description on using AWE and the calculations needed to arrive at appropriate values for AWE_WINDOW_MEMORY and DB_BLOCK_BUFFERS.
On starting up the database, we can see that Oracle acquired upto 9GB of physical memory and all the performance related problems from users were addressed.
Problems encountered:
- If the DB_BLOCK_BUFFERS or AWE_WINDOW_MEMORY value is incorrect, we get an
ORA-27102: out of memory Error followed by
O/S-Error: (OS 8) Not enough storage is available to process this command
The init.ora used is attached herewith:
_db_block_lru_latches=32
audit_file_dest='C:\admin\(db_name)\adump'
background_dump_dest='C:\admin\(db_name)\bdump'
compatible='10.2.0.3.0'
control_files='D:\(db_name)\CONTROLFILE\(controlfilename).CTL'
core_dump_dest='C:\admin\(db_name)\cdump'
db_block_buffers=850000
db_block_size=8192
db_create_file_dest='D:\(db_name)'
db_domain=''
db_file_multiblock_read_count=16
db_name=(db_name)
dispatchers='(protocol=TCP)'
java_pool_size=512165824
job_queue_processes=10
log_buffer=8388608
open_cursors=3000
processes=150
remote_login_passwordfile='EXCLUSIVE'
shared_pool_size=1468006400
shared_servers=5
streams_pool_size=100M
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
use_indirect_data_buffers=TRUE
user_dump_dest='C:\admin\(db_name)\udump'
log_archive_format=Log_%s_%t_%r.arc
log_archive_dest='D:\(db_name)\ARCHIVE'
