Which initialization parameters most affect Oracle system performance
What is SGA (system global zone)
1. System Hide Area (SGA) is a control information memory segment assigned to Oracle's database containing an Oracle instance.
It mainly includes database cache (The Database Buffer Cache),
Reversing the Redo Log Buffer,
Shared pool,
Data Dictionary Cache and other information information.
DB_BLOCK_BUFFERS
1, data cache
2, the accessible data is placed in this memory area, the larger the parameter, the larger the possibility of finding the same data in memory, and the query speed is accelerated.
3, DB_BLOCK_BUFFERS in units, if db_block_size = 2k, db_block_buffers = 3200, the memory = 3200 * 2k = 6400K.
Share_pool_size
1, SQL shared buffer pool
2, this parameter is the cache of the library cache and data dictionary.
Log_buffer
1, repeat the log buffer
Sort_area_size
1, sort zone
Processes
1, the number of processes connected at the same time
Global_names
1. If the "Database Link Name" and the "database" it want to connect have the same name, set global_names = true,
Otherwise, set global_names = false
DB_BLOCK_SIZE
1, database block size
2, the Oracle default block is 2KB, too small, because if we have a 8KB data, the 2kb block database is read 4 times, in order to finish reading,
The 8KB block database is only read once, greatly reduces I / O operation.
3, after the database installation is complete, you cannot change the value of db_block_size, only re-establish the database.
And build a library, choose to manually install the database.
Open_LINKS
1, the number of links open at the same time
DML_LOCKS
1, the user can set the maximum number of locks on the table at a time
2. If there are three users to modify 6 tables, 18 DML locks need parallel operations, if the setting DML_LOCKS is not big enough, execute will be interrupted, you can use your application's operating size and maximum parallel use The number of users estimates the value of the DML_locks required by the system, but the size of this value has no effect on other performance of the database, so it is generally set to it, far exceeds the value that may be reached in actual, if the system is running The phenomenon of changing table locks often occurs, it should be considered to increase this value.
Open_CURSORS
1, open the number of light
2, this parameter can be owned by each user process, and the maximum value of this parameter is different from the operating system.
3. When you create a synthetic view containing a lot of tables, if this parameter is small, the view creation is unsuccessful.
DBWR_IO_SLAVES
1, the number of written processes
