By Vaibhav Agarwal
There are several parameters to look for bad performing oracle. If you are lucky then for most of the DB related problems you will receive some kind of ORA errors, else it’s up to you to find the culprit.
Mostly sessions, processes, and memory allocation to different pools and cache of DB are some of the areas to tune.
If the database is in shared server mode then look for number of Dispatchers available. (Optional background processes, present only when a shared server configuration is used. At least one dispatcher process is created for every communication protocol in use (D000, . . ., Dnnn). Each dispatcher process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes)
For setting up the optimal values for all the best way is to use Automatic Shared Memory Management feature of oracle.
Oracle DB consists of SGA (system global area). SGA comprises several memory areas, including the buffer cache, shared pool, Java pool, large pool, and redo log buffers.
These pools occupy fixed amounts of memory in the operating system’s memory space; their sizes are specified by the DBA in the initialization parameter files (pfile and spfile).
Alternatively SGA is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance’s SGA is shared among the users. Consequently, the SGA is sometimes referred to as the shared global area.
Values of different parameter can be changed in pfile and then spfile needs to be created from the changed pfile, as DB reads from spfile not from pfile.
Setting up Automatic Shared Memory Management
Let’s see how this works. First, determine the total size of the SGA. You can estimate this value by determining how much memory is allocated right now.
SQL> select sum(value)/1024/1024 from v$sga;
SUM(VALUE)/1024/1024
——————–
500
The current total size of the SGA right now is approximately 500MB, which will become the value of SGA_TARGET. Next, issue the statement:
alter system set sga_target = 500M scope=both;
This approach obviates the need to set individual values for the pools; thus, you’ll need to make their values zero in the parameter file or remove them completely.
shared_pool_size = 0
large_pool_size = 0
java_pool_size = 0
db_cache_size = 0
Recycle the database to make the values take effect.
This manual process can also be implemented via Enterprise Manager 10g. From the database home page, choose the “Administration” tab and then “Memory Parameters.” For manually configured memory parameters, the button marked “Enable” will be displayed, along with the values of all manually configured pools. Click the “Enable” button to turn Automatic Shared Memory Management on. Enterprise Manager does the rest.
After the automatic memory allocations are configured, you can check their sizes with the following:
SQL> select current_size from v$buffer_pool;
POOL MBYTES
———— ———-
java pool 4
large pool 4
shared pool 148
Which Pools are Not Affected?
Some pools in SGA are not subject to dynamic resizing, and must be specified explicitly.
Examples of these pools are block size and log buffer. Their sizes will remain constant; they will not shrink or expand based on load. (In 10g, a new type of pool can also be defined in the SGA: Streams pool, set with parameter streams_pool_size. This pool is also not subject to automatic memory tuning.)
