|Oracle With Examples : Oracle Instance Tuning: SGA Tuning|
-> Oracle Instance Tuning: SGA Tuning
Oracle Instance Tuning: SGA Tuning
A “database” (=the files which store the data) is not accessible by itself. In order to access this information, a collection of allocated memory (SGA) and the running processes (like SMON, PMON, LGWR, and DBWR) is called an "instance". So, tuning an instance means to optimize these processes/ SGA memory in order to have a better response time for our database. Tuning the SGA means tuning each component of the SGA (System Global Area).
Tuning the System Global Area includes:
If the Buffer Cache is well tuned and no full table scans run in the database, this Ratio must be grater than 90%.
Until Oracle 8, db_block_lru_extended_statistics parameter was used to tune the Buffer Cache: If you set the db_block_lru_extended_statistics parameter to a positive number in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$recent_bucket. This view will contain the same number of rows as the setting of the db_block_lru_extended_statistics parameter. Each row will indicate how many additional buffer cache hits there might have been if the buffer cache were that much bigger.
Now (for 9i, 10g), to size the Buffer Cache V$DB_CACHE_ADVICE view is used. To populate this view DB_CACHE_ADVICE initialization parameter must be set to ON. Here is the information we have in this view:
Here is the information we receive from this view:
If the BC will be bigger than 224 Mb there is no gain on the database performance, so having a 224 Mb Buffer Cache could be a good solution for this system. The size of the Buffer Cache is managed by DB_CACHE_SIZE initialization parameter. This is a dynamic initialization parameter.
Data Dictionary is a part of the Shared Pool and holds information about:
Every time a statement is processed by Oracle, the Dictionary Cache is accessed for the relevant information in order that the statement can be properly executed. For this reason tuning this SGA area is very important.
The following statement return the Data Dictionary Cache Hit Ratio:
round ((1-(sum(getmisses)/(sum(gets) + sum(getmisses)))) * 100,2)
If Data Dictionary Cache Hit Ratio SHARED_POOL_SIZE initialization parameter should be increased (by small increments). This is a dynamic initialization parameter.
The library cache is the area in the shared pool in which SQL and PL/SQL statements are parsed (One time the SQL statement is parsed, Oracle knows what to do and how to do it, because the execution plan is created). If the SQL (or PL/SQL) statement is found in the Library Cache, the parsing is bypassed. However, to be found in the Library Cache, the statement must be identical (down to the number of spaces, tabs, capital or small letters) with another one which run before.
To monitor the performance of the Library Cache, there are 2 ways to do it:
(Must be little than 1%)
(Must be bigger than 95%)
If these 2 ratios have not good values, SHARED_POOL_SIZE initialization parameter should be increased (by small increments). This is a dynamic initialization parameter.
Before the information is written to the log file, Oracle write
first the data in redo log buffer. If the redo buffers are not large
enough, the Oracle LGWR process waits for space to become available.
This wait time becomes wait time for the end user. Larger Log Buffer
Cache values reduce log file I/O, but may increase the time OLTP users have to wait for write
To tune the value for
WHEREr.name='redo buffer allocation retries' AND e.name='redo entries';
This ratio must be < 1%.
The sort operations are done normally in the memory. However, if the Sort Memory is not big enough, the sorting will use the disk and the execution time will be longer. To test if the sort operations are done in the memory the following command could be used:
If the select return a value greater than 95% the SORT_AREA_SIZE parameter is well tuned; if the select return a value smaller than 95% the SORT_AREA_SIZE parameter must be increased.
-> Oracle Instance Tuning: SGA Tuning
Copyright (c) 2018 www.oracle-with-examples.com | Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.