Oracle System Tuning

In Oracle database systems, the parameters that play the role of tuning are called initialization parameters, which are recorded in the Initsid.ora file in Oracle 8i and previous versions, while Oracle 9i/10g/ These parameters are recorded in the Spfilesid.ora binary in 11g

Basic parameters: A set of adjustable parameters, such as Control_files,db_block_size,process

Advanced parameters: A set of finely tuned parameters, such as Shared_servers

Main system Bar Optimization parameters

Parameters	Description
Buffer_pool_keep	The reserved pool size (allocated from db_block_buffers) is intended to keep objects in memory to reduce I/O
Buffer_pool_recycle	The Loop pool size (allocated from db_block_buffers) is intended to be used to clear the object so that memory can be reused
Control_file_record_keep_time	The minimum time (in days) that records in the reusable section of the control file must be retained
Cursor_space_for_time	When a cursor references a shared SQL region, determines whether to keep the SQL zone in the shared pool or to expire the processing by expiration
Db_block_buffers	The number of Oracle blocks in the buffer cache that significantly affects the total SGA size of a routine
Db_keep_cache_size	Specifies the number of buffers in the keep buffer pool, and the buffer size in the keep buffer pool is the primary block size (db_block_size)
Db_recycle_cache_size	Specifies the size of the recycle buffer pool, the size of the buffer in the recycle pool is the primary block size (db_block_size)
Java_max_sessionsapce_size	Specifies, in bytes, the maximum amount of memory used by Java programs that are available to run on the server. It is used to store each database Invokes the state of Java. If a user's session lasts longer than this value, the session is terminated due to insufficient memory
Java_pool_size	Specifies, in bytes, the Java storage pool size, which is used to store Java methods and class definitions in shared memory representation and Java objects ported to Java session space at the end of the call
Large_pool_size	Specifies that the allocation heap for a large pool is large, which can be used by the shared server as the session memory, as a message buffer for parallel execution, and Disk I/O buffers for Rman backup and recovery
Log_buffer	Specifies, in bytes, the amount of memory used to cache these entries before LGWR writes redo log entries to the Redo log files. Redo entries retain a record of changes made to the database block. If the value is greater than 65536, you can reduce the redo log file I/O. Especially on systems that have long-time processing of things or large amounts of transaction processing
Log_checkpoint_interval	Specifies the number of OS blocks (not database blocks) that must be written to the redo log file before the checkpoint occurs. Regardless of the value, Checkpoints occur when a log is toggled. Lower values can shorten the time required for routine recovery, but may cause too many disk operations
Log_checkpoint_timeout	Specifies the maximum time interval (in seconds) to occur from the next checkpoint. Specifying this time as 0 disables time-based Check points. Lower values can shorten routine recovery time, but may result in excessive disk operation
Max_dump_file_size	Specifies the maximum size of each trace file. You can change this limit if you are concerned that the trace file is taking up too much space. If the dump file Can reach the maximum size allowed by the operating system, set to "unlimited"
Object_cache_max_size_percent	Specifies that the cache growth of a session object exceeds the percentage of the best cache that is laid, the maximum size equals the optimal size plus the Percent with the best size of the score. If the cache size exceeds this maximum size, the system view is resized to the optimal size
Optimizer_index_caching	Adjust the assumed value of the cost-based optimizer, that is, the percentage of index blocks expected to be used in the buffer cache for nested loop joins It affects the cost of nested loops connected using the index. Set this parameter to a higher value, which can be a nested loop connection relative to the Lower cost to optimize programs
Optimizer_index_cost_adj	You can use it to optimize the performance of your program if you consider too many or too few index access paths. The lower the value, the more easily the optimizer Select an index. That is, if the value is set to 50, the cost of the index access path is half the normal case
Query_rewrite_enabled	Enables or disables query rewriting of the manifested view. A specific manifested view is enabled only under the following conditions: Session parameters and individual entities The view is enabled, and cost-based optimization is enabled
Read_only_open_delayed	Used to speed up certain operations, such as launching a large database where most of the databases are stored in a read-only tablespace. If Set to True to first access data files in the read-only tablespace when data is read from the table space
Shared_pool_reserved_size	Specifies the amount of space to reserve for large contiguous shared pool memory requests to avoid performance degradation caused by fragmentation. The size of the pool should match Such conditions can be stored to prevent objects that are generally required to refresh from a shared pool for all large processes and packages
Shared_pool_size	Specifies the size of the shared pool, in bytes. Shared pools include such as: Shared cursors, stored procedures, control structures, and parallel message buffering Area and other objects. Larger values improve the performance of multi-user systems
Sort_area_size	Specifies, in bytes, the maximum amount of memory used by the sort. When the sort is complete, the rows are returned, and the memory is freed. Increase the Value can improve the efficiency of large sorting, and if the amount of memory is exceeded, temporary disk segments will be used

You can use the ALTER system (the entire DB instance)/alter session (current session) for system optimization

You can use the show parameter+ parameter name to view

System global Area (SGA) optimization

Although 11g increases the automatic adjustment of memory

Adjusting memory Allocations

The general memory adjustment is done after the application and SQL statements have been adjusted. It is also recommended that the user adjust the memory allocation before adjusting the I/O.

Adjusting the log buffers

How do I log files?

Method One: The use of logical records, that is, descriptive statements to record the entire process of change.

For example, for an update, only delete old values and insert new values are logged

Advantages: Space Saving

OK: Once recovery is needed, it is very resource-intensive

Mode two: The use of physical records, that is, each block of changes before the change of the image and the mirror after the record down

Advantages: Fast Recovery,

Cons: Takes up a lot of disk space

Oracle uses a combination of logic and physics in the way it records logs.

Querying the log buffer size of the current Oracle instance

SELECT distinct Lebsz as log buffer size from x$kccle-- must be logged in as Sys

Insert Knowledge Introduction:x$ table

The x$ table contains information about the various aspects of a particular instance, which is likely to be different in versions of Oracle, when the Oracle database runs based on the current configuration information, the session that connects the instance, and the performance information that is rich in value. It's not a permanent or temporary table in a database file

　　The x$ table resides only in memory and is dynamically created by the Oracle application when the instance is started and maintained in real time in memory. Most of them require at least a database that is mounted or already open. x$ tables are owned by the SYS user, read-only, x$ tables are important for the database, so users outside of SYSDBA are not allowed direct access, and the displayed authorizations are not allowed

Adjust a shared pool

The main components are:

Library cache: Information about primary cache shared SQL and PL/SQL statements

Data dictionary cache: Cache data dictionary table (dba_tables,dba_users) for interpreting permissions, table structure, etc.

UGA (User Globa area): In Shared server mode, when a large pool is not configured (large_pool_size=0) UGA consumes the shared pool

In the SGA, you should first consider tuning the shared pool, which should first be focused on the library cache when you adjust the shared pool. If the shared pool is very small, it consumes a lot of CPU resources and competes, and if the pool is large, it consumes a lot of memory resources, and the buffer is much larger, and the lookup becomes slower.

Such as: Query the current instance of the library cache in the call phase "request access number" and "number of misses" through V$librarycache

Select SUMJ (Pins) Request access number, SUM (reloads) number of misses from V$librarycache;

In general, the ratio of the total number of cache misses to the total number of accesses should be close to 0. When the ratio is close to or greater than 1%, measures should be taken immediately to reduce this miss, usually in the following two ways:

1. Increase the initialization parameter shared_pool_size, increase the amount of memory available in the library cache, and, for good results, increase the value of the initialization parameter open_cursors to increase the number of cursors allowed by the dialog. It is important to note that allocating too much memory for the library cache may cause paging or swapping

2 write the equivalent SQL statement, so that the SQL statement and the PL/SQL block share a single area, as much as possible, to reduce the library cache misses. The text of an SQL statement or PL/s block must be equivalent to each character, including case and space

V$rowcache Querying data dictionary cache activity status

Such as: Query the data dictionary cache of the current instance through the V$rowcache dynamic performance View the "number of requests and accesses" and "misses" in the call phase

Select sum (gets) request access number, SUM (getmisses) number of misses from V$rowcache;

In general, the data dictionary cache total number of misses and total access to the ratio should be close to 0, if the ratio is greater than 10%, even in the application of the ratio is still growing, it should be immediately by increasing the shared_pool_size to increase the amount of memory available in the data dictionary, thereby reducing this miss.

V$sesstat v$statname Query The total memory statistics used by Oracle to collect conversation information

Example: Displays the number of memory currently assigned to all sessions

Select sum (value) | | ' Bytes ' Current amount of memory allocated to all sessions
From V$sesstat,v$statname
where name= ' session UGA memory ' and v$sesstat.statistic#=v$statname.statistic#
/

This example: 5273276 bytes

Session UGA memory used to display the number of bytes allocated to a conversation

Example: Displays the maximum amount of memory that has ever been allocated to all sessions

Select sum (value) | | The maximum amount of memory that ' bytes ' has ever allocated to all sessions
From V$sesstat,v$statname
Where Name= 'session UGA memory Max' and v$sesstat.statistic#=v$statname.statistic#
/

This example: 9894388 bytes

Tuning the database buffers

After Oracle starts, it collects and counts data access and stores it in V$sysstat.

Yes:

DB block gets: The statistic value is the total number of data requests

Consistent gets: This statistic is the request that can be satisfied by the memory buffer access

Physical reads: This statistic is the total number of disk file accesses

such as: query for a period of time in the V$sysstat table of statistical information

Select Name,value from v$sysstat where name in (' db block gets ', ' consistent gets ', ' physical reads ')
/

Calculate the hit ratio of the buffer cache can be used

1-physical reads/(db block gets+consistent gets)

If the hit rate is good, you can reduce the size of the cache by minimizing the value of the initialization parameter db_block_buffers, which can be used to save memory for other Oracle memory structures. If the hit rate is less than 70%, this can cause performance degradation, and the buffer cache size should be increased immediately by increasing the value of the initialization parameter db_block_buffers (max 65535)

Sorting area optimization

The system uses a dedicated memory area for sorting data, which is the sort area. In Oracle, user data can be sorted using two regions: one is the memory sort area, the other is the disk temporary segment, the system prioritizes using the memory sort area, and if memory is not enough, Oracle automatically uses the disk temp table space to sort, in order to improve the speed of database sorting, It is recommended that you try to use the memory sort area instead of the temporary segment. The sort_area_size is used to set the sorting area size.

Increasing the sort area increases the performance of large-scale sorting, because it is possible to perform sorting in memory during query processing. The size of the sort area is important because there is only one sort area for each connection at a time. The default value for this Init.ora parameter is typically 6-8 blocks in size, which is typically used primarily for OLTP operations, and should be increased to perform decision support operations, bulk operations, or a large number of index-related operations, such as rebuilding an index. To do this, you should adjust the following Init.ora parameters:

sort_area_size=65535

sort_area_retained_size=65535

Sort activity

Sorting is a small aspect of SQL syntax, but it is important that it is often overlooked in Oracle tuning. When you use the Create Index,order by, a group BY statement, the Oracle database automatically performs the sort operation. Typically, Oracle sorts operations in the following situations:

1. When you create an index

2. Using ORDER BY

3. Use GROUP BY

4. When a table join occurs, the SQL optimizer calls the merge sort because of insufficient existing indexes

When a session is established in an Oracle instance, a private sort area is assigned to the session in memory. If the connection is a private connection, a PGA is allocated in memory based on the size of the Sort_area_size parameter in Init.ora. If the connection is established through a multithreaded server, the sort space is allocated in Large_pool. However, the amount of memory used to sort all sessions must be consistent, and an additional sort area cannot be allocated for operations that require a larger ordering. As a result, designers must strike a balance between allocating enough sort areas to avoid a large sort of task when the disk is sorted, and for tasks that do not require a very large ordering, there is a waste. Of course, when the ordered space requirement exceeds the size set by Sort_area_size, the disk will be sorted in the temp table space by paging. Disk sort is about 14,000 times times slower than memory sort

The private sort area size is determined by the Sort_area_size parameter in Init.ora. The size occupied by each sort is determined by the Sort_area_retained_size parameter in Init.ora. When a sort cannot be completed in the allocated space, a disk sort method is used, that is, in the temporal tablespace in the Oracle instance

The cost of disk sorting is large, mainly for the following reasons: First, the disk sort is very slow compared to the memory sort, and its disk ordering consumes the resources of the temporary tablespace. Second, Oracle must allocate a buffer pool to maintain blocks in the temporary table space. Whenever memory ordering is better than disk sorting, the disk ordering will slow down the task and affect the execution of the current task of the Oracle instance. In addition, excessive disk sorting causes the idle cache to wait (free buffer waits) to become very high, so that the data blocks of other tasks are moved by the buffer.

Adjustment of the sort area in private mode

Allocation of different parts within the PGA

Memory Area	Proprietary services
Session Memory	Privately owned
Permanently located area	Pga
SELECT statement Run area	Pga
DML/DDL Operating Area	Pga

Parameters:

Sort_area_size:

It is recommended to use Pga_aggregate_target for automatic PGA memory management unless this parameter is generally recommended in shared mode. If you need to set this parameter, consider setting the 1-3MB between

Sort_area_retained_size:

It specifies that after the sort operation is complete, the maximum size of the memory in the UGA (User Global Zone) is maintained to maintain the in-memory ordering until all data rows are returned for release (Sort_area_size is released before the sort is complete and data rows are returned) back to UGA (will not be recycled by the operating system).

Sort_area_retained_size is allocated from the SGA in shared services (because the UGA is allocated from the SGA), is allocated from the PGA in the private service mode, and Sort_area_size is allocated from the PGA regardless of the mode

This parameter is not valid when Pga_aggregate_target is also set

When the connection is idle, the background process Pmon every once in a while, the user's connection is tested, and if the connection is broken, Pmon cleans the site and releases the associated resources. A private connection is equivalent to a one-to-one connection that responds quickly to a user's request. Of course, when connecting, you first create the PGA, the parameter Pga_aggregate_target determines the total amount of memory that can be used by all server processes, and the parameter Workarea_size_policy decides whether to use manual or automatic management

How to connect: private and shared connections

Adjustment of the sort area in shared mode

For long transactions or large transactions, the use of private connections, can effectively improve the performance of the system, reduce user waiting and transaction queuing, improve the utilization of the system; for ultra-short transactions and short transactions, small transactions, use private connections, and for Web sites, you can use shared connections.

In a shared connection, Sort_area_size will be allocated in the large_pool of the SGA

Shared connections and long transactions run counter to (long and large OLTP)

Oracle System Tuning