Oracle Parameter Optimization

Oracle Parameter Optimization

1. Upgrade to 11gR2
The Oracle Database Upgrade Assistant (DBUA) Configuration tool includes a Command Option to automatically expand system files, which can be upgraded from oracle express (XE or free version) to its version.
The pre-upgrade script checks the following items:
1. invalid user or role
2. Invalid Data Type or object
3. Unsupported character sets
4. Collect statistical information
5. Sufficient resources (undo/rollback segments, tablespace and idle disk space)
6. The script required for the missing upgrade
7. Running listener
8. oracle database software is connected to the database vault Option
If the ORACLE_BASE environment variable is specified during installation, oracle uses this value to set the DIAGNOSTIC_DEST parameter, which includes all the ADR directories.
 
1.11g New Features
By default, the password is case sensitive.
SEC_CASE_SENSITIVE_LOGON is case sensitive by default.
SEC_MAX_FAILED_LOGIN_ATTEMPTS the default value is 10.
 
Alter user username account unlock;
 
2. Important oracle Parameters
MEMORY_TARGET
MEMORY_MAX_TARGET
SGA_TARGET
SGA_MAX_SIZE
PAG_AGGREGATE_TARGET
DB_CACHE_SIZE
SHARED_POOL_SIZE

The order in which parameter files are read by default.
1. spfile <SID>. ora
2. spfile. ora
3. init <SID>. ora
 
If you use the alter system command to modify only spfile, And the setting error is found at startup, the database will not start. In this case, you cannot use the alter system command to solve this problem. You need to create a pfile Based on the spfile, modify the pfile, and then use the pfile to start the database. Then you need to create a spfile and then use the spfile to restart the database.
 
In the V $ PARAMETER view, there are two key fields (V $ PARAMETER displays valid parameters at the session level, and V $ SYSTEM_PARAMETER displays valid parameters at the instance level ):
ISSES_MODIFIABLE: Indicates whether users with the alter session permission can modify this initialization parameter at their session level.
ISSYS_MODIFIABLE: Indicates whether users with the alter system permission can modify this parameter.
 
Select name, value, isdefault, isses_modifiable, issys_modifiable from V $ PARAMETER where issys_modifiable <> 'false' or isses_modifiable <> 'false' order by name;
Alter session set sort_area_size = 10000000;
Dynamic Modification of initialization parameters is very powerful for developers and DBAs. Therefore, if no limit is imposed, users with the alter session privilege can randomly allocate a memory larger than MB to the sort_area_size of a session.
 
 
3. Optimize DB_CACHE_SIZE to improve performance
Oracle 10g DB_BLOCK_BUFFERS becomes an implicit parameter and is enabled again at 11g. The default value is 0, meaning it will not be used unless it is set (replaced by DB_CACHE_SIZE ).
DB_CACHE_SIZE is the amount of memory initially allocated to cache or store data for the primary database. If MEMORY_TARGET or SGA_TARGET is set, this parameter is not required. Our goal should be to implement a database resident in the memory, at least put all the data to be queried into the memory.
If DB_CACHE_SIZE is set too low, oracle does not have enough memory to execute operations effectively regardless of how the system is optimized, and the system running status will also be bad. If the setting is too high, your system may use swap space or even shut down. DB_CACHE_SIZE is part of SGA and is used to store and process data and query access. If this parameter is set too low, the recently used data will be cleared from the memory. If another query calls the cleared data again, it must be read from the disk again (I/O and CPU resources will be used ).
MEMORY_TARGET, SGA_TARGET (if used), and DB_CACHE_SIZE (if the minimum value is set) are key parameters used to optimize the data cache hit rate: hit rate refers to the proportion of data blocks that can be accessed without performing physical read operations on the disk.
If the system load remains unchanged and the cache hit rate changes dramatically, you should immediately investigate the cause. Poor connections and indexes will also produce a very high hit rate due to reading many index blocks. Therefore, we must ensure that the hit rate is not increased due to these factors, but because the system has been well tuned. An unusually high hit rate usually implies that some code uses bad indexes or connections.
By comparing the hit rate with time changes, you can pay attention to the major changes that occur in the system one day.
 
4. Use V $ DB_CACHE_ADVICE to optimize DB_CACHE_SIZE
You can view the impact of DB_CACHE_SIZE Modification on the data cache hit rate in the following list.
Select name, size_for_estimate, size_factor, estd_pyhsical_read_factor from v $ db_cache_advice;
NAME size_for_estimate size_factor estd_pyhsical_read_factor
DEFAULT 1667 1.8322
DEFAULT 3333 1.0169
DEFAULT 12. 5 1.0085
DEFAULT 16. 6667 1
DEFAULT 20. 8333 1
DEFAULT 24 1 1
The current cache size is 24 M size_factor = 1
We can reduce the cache size to 16 MB and maintain the current cache hit rate, because PHYSICAL_READ_FACTOR is still 1 when SGA is reduced to 16 Mb.

Keep data cache hit rate above 95%
In some examples, increasing the hit rate from 95% to 98% can significantly improve performance-especially the 5% hit on the disk at last is the main latency of the system, or the disk cache is insufficient.

5. Monitor the V $ SQLAREA view to find slow queries
Although the hit rate lower than 95% usually indicates that DB_CACHE_SIZE is set too low. Hit rate distortion and non-DB_CACHE_SIZE problems include:
1. recursive call
2. Missing or suppressing Indexes
3. Data resident in memory
4. UNDO/rollback segments
5. Multiple logical reads
6. Physical reads that cause the system to use CPU
You can monitor the V $ SQLAREA view or Enterprise Manager to find slow queries.

 
6. Set DB_BLOCK_SIZE to reflect the data read size.
If the transaction processing throughput in the system is very high or the system memory is limited, you may consider setting the block size to less than 8 KB.
You can increase DB_BLOCK_SIZE to 8 K or 16 K, or set the value of DB_FILE_MULTIBLOCK_READ_COUNT to (maximum IO size) DB_BLOCK_SIZE. This increases the amount of data read from each IO to the memory.
If DB_FILE_MULTIBLOCK_READ_COUNT causes many full table scans (the OPTIMIZER_INDEX_COST_ADJ is set to 1 ~ because the optimizer_multiblock_read_count is determined to be used more quickly ~ In this way, you can force the index to be used more frequently.
 
If you increase the size of DB_BLOCK_SIZE, you must re-create the database. Increasing the size of DB_FILE_MULTIBLOCK_READ_COUNT allows you to read more blocks in one IO, which brings the same benefits as increasing the size of a large block.
 
7. Set SGA_MAX_SIZE to 25% of the primary memory size
If the SGA_MAX_SIZE parameter is used, it is generally used to allocate 20% to 25% of the primary memory at the beginning.
If SGA_MAX_SIZE <1G, the value of _ KSM_GRANULE_SIZE is 4 M.
If 1 <SGA_MAX_SIZE <8G, the value of _ KSM_GRANULE_SIZE is 16 M.
 
If SGA_MAX_SIZE is set to 2000 M and DB_CACHE_SIZE is set to 9 M, DB_CACHE_SIZE is rounded to 16 M (because the granularity is 16 M)
8. Optimize Shared_pool_size for optimal performance
Oracle uses the least recently used algorithm (LRU)
To use result set cache, set RESULT_CACHE_SIZE = <required size> and RESULT_CACHE_MODE = FORCE (set FORCE to automatically use this feature ).
To ensure optimal use of the shared SQL region, use stored procedures whenever possible because the parsed SQL statements are identical each time, so they can be shared.
SQL statements must be written in the same way before they can be reused.
PL/SQL converts each statement into uppercase and then sorts out spaces or line breaks.
If you set CURSOR_SHARING = FORCE, the query results for V $ SQLAREA will change, because oracle can build the statements shared by all the preceding statements internally. Currently, the sharing statement only contains a simple statement shared by all users.
Set shared_pool_size to make full use of db_cache_size.
Ensure that the data dictionary hit rate is higher than 95%
Select (1-(sum (getmisses)/(sum (gets) + sum (getmisses) * 100) "hit rats" from v $ rowcache where gets + getmisses <> 0;

You can use the modified query on the V $ ROWCACHE view to view how these parameters constitute the data dictionary cache, also known as row cache.
Select parameter, gets, getmisses, modifications, flushes, (getmisses/decode (gets, gets) getmiss_ratio, (case when (getmisses/decode (gets, gets)>. 1 then '* 'else' 'end) "" from v $ rowcache where gets + getmisses <> 0;

Ensure that the library cache overload rate is 0, and make the hit rate above 95%. If the load rate exceeds 1%, the parameter shared_pool_size may be increased;
There are two ways to monitor the database cache:
1. Generate a STATSPACK report
2. Use V $ LIBRARYCACHE
Makes the PIN hit rate of the library cache close to 100%
The data dictionary cache loss rate should be less than 10% ~ 15%
Oracle uses the SHARED_POOL_RESERVED_SIZE parameter to reserve space for emergency operations. The V $ SGASTAT view shows the memory consumption rate of the Shared Pool.
Use the X $ KSMSP table to view details in the shared pool.