Oracle Parameter Optimization

The Oracle Database Upgrade Assistant (DBUA) Configuration tool includes a Command Option to automatically expand system files, which can be upgraded from oracleexpress (XE or free version) to other versions.

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.

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 . Ora
2. spfile. ora
3. init . 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 for a session sort_area_size.


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.