Tuning Oracle Parameters

I. After upgrading to 11GR2

The Oracle Database Upgrade Assistant (DBUA) configuration tool includes a command option to automatically extend system files, which can be upgraded from Oracle Express (XE or Free edition ) to their versions.

The Pre-upgrade script examines the following:

1. Invalid user or role

2. Invalid data type or object

3. Unsupported Character Set

4. Collection of statistical information

5. Sufficient resources (Undo/rollback segment, tablespace, and free disk space)

6. Required scripts for missing upgrades

7. Running the Listener

8.oracle database software is connected to the DB Vault option

If you specify the ORACLE_BASE environment variable during installation, Oracle uses this value to set the Diagnostic_dest parameter, which includes all the ADR directories.

1.11g new Features

The password is case-sensitive when installed by default

Sec_case_sensitive_logon default is true case sensitive

The default value for Sec_max_failed_login_attempts is 10 set attempt times.

Alter user username account unlock;

Important parameters of 2.oracle

Memory_target

Memory_max_target

Sga_target

Sga_max_size

Pag_aggregate_target

Db_cache_size

Shared_pool_size

Default read order of parameter files

1.spfile<sid>.ora

2.spfile.ora

3.init<sid>.ora

If you use the ALTER SYSTEM command to modify only SPFile, and when you start to find a setup error, the database will not start. At this point, you cannot use the ALTER system command to solve this problem, you need to create a pfile based on SPFile, modify the Pfile, and then use this pfile to start the database. You then need to create the spfile and then use SPFile to restart the database.

There are two key fields in the V$parameter view (V$parameter shows the session-level valid parameters, v$system_parameter the parameters that are valid at the entire instance level):

Isses_modifiable: Indicates whether a user with ALTER session permission can modify this initialization parameter at their session level

Issys_modifiable: Indicates whether the user with 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;

Dynamically modifying initialization parameters is a very powerful feature for developers and DBAs. Therefore, if the restriction is not made, the user with alter session privilege can arbitrarily allocate more than 100M of memory for the sort_area_size of a session.

3. Optimize db_cache_size to improve performance

The Oracle 10g db_block_buffers becomes an implicit parameter and is enabled in 11g, which defaults to 0, meaning that it will not be used (instead of db_cache_size) unless it is set.

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, then this parameter is not required to be set. Our goal should be to implement a database that resides in memory, at least to put all the data that will be queried into memory.

If the db_cache_size setting is too low, no matter how the system is optimized, Oracle does not have enough memory to perform the operation effectively and the system will be in a bad state. If set too high, your system may use swap space, or even downtime. Db_cache_size is part of the SGA for storing and processing data and querying access. Settings are too low, the most recently used data is purged from memory, and if there is another query that calls back the purged data, it must be re-read from the disk (will use the I/O and CPU resources).

Memory_target,sga_target (if used) and db_cache_size (if the minimum value is set) are key parameters to optimize the data cache hit ratio: the hit rate refers to the scale of data blocks that can be accessed without performing physical reads from disk.

If the system load does not change, and the cache hit rate changes drastically, you should immediately investigate the cause of the occurrence. Bad connections and indexes can also produce very high hit ratios due to reading many index blocks, so make sure that the hit rate is not increased by these factors, but rather because the system is well tuned. Unusually high hit ratios often imply that the code is using a bad index or connection.

By comparing hit ratios over time, you can help you notice significant changes that occur one day in the system.

4. Optimize db_cache_size with V$db_cache_advice

You can use the following checklist to see the effect of modifying db_cache_size on the data cache hit ratio

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

DEFAULT4. 16671.8322

DEFAULT8. 33331.0169

DEFAULT. 51.0085

DEFAULT. 66671

DEFAULT. 83331

DEFAULT11

The current cache size is 24M size_factor=1

We can do it. The cache size is reduced to 16M and the current cache hit ratio is maintained because the Physical_read_factor remains 1 when the SGA is reduced to 16M

Keep data cache hit rate over 95%

In some cases, increasing the hit rate from 95% to 98% can significantly improve performance--especially if the last hit on the disk is 5% of the system's main delay, or the disk cache is not enough.

5. Monitor the V$sqlarea view to find slower queries

Although the hit rate below 95% usually indicates that the db_cache_size is set too low. Hit-ratio distortions and those non-db_cache_size issues include:

1. Recursive invocation

2. Missing indexes or suppressing indexes

3. Data residing in memory

4.undo/rollback Segment

5. Several times of logical reading

6. Physical reads that cause the system to use the CPU

You can find slower queries by monitoring the V$sqlarea view or Enterprise Manager.

6. Set Db_block_size to reflect the size of the data read

If the throughput of the transaction in the system is very high or the system memory is limited, you might consider setting the block size to less than 8K

You can increase the db_block_size to 8 K or 16K or set the Db_file_multiblock_read_count value to (max IO size) db_block_size. So You can increase the amount of data that is read to memory per IO.

If many full table scans are caused by db_file_multiblock_read_count (because the optimizer determines that performing a full table scan is faster, so decide to use it more), set Optimizer_index_cost_adj to 1 , this forces the index to be used more frequently.

If you increase the db_block_size, you must recreate the database and increase the db_file_multiblock_read_count to allow more blocks to be read in one IO, which can bring the same benefits as increasing the block size.

7. Set the sga_max_size to 25% to 50% of the primary memory size

If you use the Sga_max_size parameter, the general experience is to initially allocate 20% to 25% of the main memory to it.

If sga_max_size<1g, then the _ksm_granule_size value is 4M.

If 1<sga_max_size<8g, then the value of _ksm_granule_size is 16M

If the sga_max_size is set to 2000M and the db_cache_size is set to 9M, then db_cache_size is rounded to 16M (because the granularity is 16M)

8. Optimize Shared_pool_size for best performance

Oracle uses least recently used algorithms (LRU)

With the result set cache, you need to set result_cache_size=< to require size > and Result_cache_mode=force parameters (set to force to automatically use this feature).

To ensure optimal use of shared SQL zones, use stored procedures as much as possible, because the SQL that is parsed is identical every time, so it can be shared.

SQL must be written in exactly the same way that it can be reused.

PL/SQL converts each statement to uppercase and then organizes the spaces or line breaks.

If you set Cursor_sharing=force for V$sqlarea, the query results will change because Oracle can internally build statements that are shared by all previous statements. Now the shared statement contains only a simple statement shared by all users.

Set large enough shared_pool_size to ensure full use of db_cache_size

Guaranteed data dictionary hit rate higher than 95%

Select ((N (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 see how these parameters make up the data dictionary cache, also known as row caching

Select Parameter,gets,getmisses,modifications,flushes, (Getmisses/decode (gets,0,1,gets)) Getmiss_ratio, (case when ( Getmisses/decode (gets,0,1,gets)) >. 1 Then ' * ' Else ' end) ", from V$rowcache where gets + getmisses <> 0;

To ensure that the load rate of the library cache is 0, and the hit rate is more than 95%, the parameter shared_pool_size should be increased if the overload ratio exceeds 1%.

There are two ways to monitor the library cache:

1. Generate Statspack Report

2. Using V$librarycache

Close 100% pin hit rate for library cache

The data dictionary cache should lose less than 10%~15%

Oracle retains space for emergency operations with the Shared_pool_reserved_size parameter, and the V$sgastat view shows the speed at which shared pool memory is consumed.

Use the X$ksmsp table to see the details of the shared pool.

This article is from the "linux_oracle" blog, make sure to keep this source http://pankuo.blog.51cto.com/8651697/1631329

Tuning Oracle Parameters