Link: http://www.dba-oracle.com/art_tuning_instance.htm
An Oracle instance consists of the memory region of the database, called the System Global Area or SGA, and the Oracle background processes.
This instance-tuning phase of Oracle tuning examines the overall database and the instance-wide parameters that affect performance.
focus on those parameters that have the most impact on the performance of the Oracle database.
these are some of the most important:
shared_pool_size—The memory region allocated for the library cache and internal control structures
db_block_size—The size of each data block
db_block_buffers—The number of data buffers to allocate for the instance
sort_area_size—The amount of RAM reserved for each user to perform sorting operations
optimizer_mode—The default mode in which all SQL statements will be optimized for an execution plan
db_file_multiblock_read_count—The parameter that controls the number of blocks that are read asynchronously in full-table scan operations
Note that the db_file_multiblock_read_count has been automated in 10gr2 and beyond.
Starting in Oracle 10g release 2, Oracle recommends not setting the db_file_multiblock_read_count parameter, allowing Oracle to empirically determine the optimal setting.
We have to remember that all Oracle instances are generally constrained by I/O operations. I/O is the single most dramatic component of Oracle performance, and any initialization parameters that can be used in order to reduce I/O will improve the performance of the database.
For example, the db_block_size and db_cache_size initialization parameters are very important in terms of reducing I/O. As a general rule, the larger the Oracle block size, the more the database can read in a single disk I/O and the less I/O contention the system will have.
other Links in oracle instance tuning:
Instance Tuning Using Performance Views : http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm
Wait Events Statistics : http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#i18202
Performance Tuning In Nutshell : http://supradipdey20062007.blogspot.com/2010/04/performance-tuning-in-nutshell.html (shows with examples)
No comments:
Post a Comment