Major root cause of performance issues in OLTP databases.:
Online transaction processing benchmarks are important workloads for the design and performance analysis of microprocessors and computer systems targeting the server market. Setting up and configuring an OLTP workload are nontrivial due to the complex interactions among the myriad of configuration parameters that need to be properly tuned for achieving good performance. Furthermore, the hardware costs of building a computer system with sufficient amount of system memory and disk I/O bandwidth can be substantial. Typically, researchers employ two types of setups for characterizing OLTP workloads: scaled or cached.
There are three areas where the settings for Oracle have a direct impact on the amount of disk I/O. The settings for the Oracle instance (init.ora) impact disk I/O, the settings for Oracle objects (tables and indexes) affect disk I/O, and the execution plans for Oracle SQL also have a direct impact on disk I/O.
1. Oracle Database Instance : There are several database instance parameters that have a direct impact on lowering physical disk I/O: Theses are :
Large db_block_size
Large db_block_buffers
Multiple database writers (DBWR) processes
Large sort_area_size
Large online redo logs
Large db_block_size - The block size of the database has a dramatic effect on the amount of disk I/O. As a general rule, the larger the block size, the less the disk I/O.
Large db_block_buffers- The greater the number of data buffers, the smaller the chance that Oracle will need to perform disk I/O.
Multiple database writers (DBWR) processes - Multiple database writer background processes allow for more efficient writing to the datafiles.
Large sort_area_size - he greater the sort_area_size in RAM, the less disk sorting will take place in the TEMP tablespace.
Large online redo logs - The larger the online redo logs, the less frequent the log switches.
2. Oracle objects - Inside the database, settings for table and indexes can reduce physical disk I/O
Low pctused - The smaller the value of pctused, the less I/O will occur on subsequent SQL inserts.
Low pctfree - If pctfree is set to allow all rows to expand without fragmenting, the less disk I./O will occur on subsequent SQL selects.
Reorganizing tables to cluster rows with indexes - If tables are placed in the same physical order as the most frequently used index, disk I/O will drop dramatically. This can be done via a cluster table or an IOT.
3. Oracle SQL - Within SQL statements, there are many techniques to reduce physical disk I/O:
Preventing unnecessary full table scans using indexes or hints - This is the most important way to reduce disk I/O because many SQL queries can use indexes to reduce disk I/O.
Using bitmapped indexes - The use of bitmapped indexes will reduce full table scans on tables with low-cardinality columns, thereby reducing disk I/O.
Applying SQL hints- Many hints make SQL run faster and with less disk I/O. For example, the USE_HASH hint will reduce disk I/O by performing joins within SGA memory, reducing calls for database blocks.
Now that we have reviewed some of the things that we can do within Oracle to reduce disk I/O, let's take a close look at the nature of disk I/O and examine the internal workings of the disk I/O subsystem.
How to calculate no. of DB Writers in OLTP databases?
The DB_WRITER_PROCESSES initialization parameter lets you configure multiple database writer processes (from DBW0 to DBW9 and from DBWa to DBWj). Configuring multiple DBWR processes distributes the work required to identify buffers to be written, and it also distributes the I/O load over these processes. Multiple db writer processes are highly recommended for systems with multiple CPUs (at least one db writer for every 8 CPUs) or multiple processor groups (at least as many db writers as processor groups).
The Default value is 1 if CPU_COUNT is less than 8 , and CPU_COUNT / 8 if CPU_COUNT is more than 8
Based upon the number of CPUs and the number of processor groups, Oracle Database either selects an appropriate default setting for DB_WRITER_PROCESSES or adjusts a user-specified setting.
The above configuration is worked for me. Before any change in production test in your pre-prod env. and check with Oracle support. Without any reason go with Oracle default setting.
Thanks ...Gouranga...
Online transaction processing benchmarks are important workloads for the design and performance analysis of microprocessors and computer systems targeting the server market. Setting up and configuring an OLTP workload are nontrivial due to the complex interactions among the myriad of configuration parameters that need to be properly tuned for achieving good performance. Furthermore, the hardware costs of building a computer system with sufficient amount of system memory and disk I/O bandwidth can be substantial. Typically, researchers employ two types of setups for characterizing OLTP workloads: scaled or cached.
There are three areas where the settings for Oracle have a direct impact on the amount of disk I/O. The settings for the Oracle instance (init.ora) impact disk I/O, the settings for Oracle objects (tables and indexes) affect disk I/O, and the execution plans for Oracle SQL also have a direct impact on disk I/O.
1. Oracle Database Instance : There are several database instance parameters that have a direct impact on lowering physical disk I/O: Theses are :
Large db_block_size
Large db_block_buffers
Multiple database writers (DBWR) processes
Large sort_area_size
Large online redo logs
Large db_block_size - The block size of the database has a dramatic effect on the amount of disk I/O. As a general rule, the larger the block size, the less the disk I/O.
Large db_block_buffers- The greater the number of data buffers, the smaller the chance that Oracle will need to perform disk I/O.
Multiple database writers (DBWR) processes - Multiple database writer background processes allow for more efficient writing to the datafiles.
Large sort_area_size - he greater the sort_area_size in RAM, the less disk sorting will take place in the TEMP tablespace.
Large online redo logs - The larger the online redo logs, the less frequent the log switches.
2. Oracle objects - Inside the database, settings for table and indexes can reduce physical disk I/O
Low pctused - The smaller the value of pctused, the less I/O will occur on subsequent SQL inserts.
Low pctfree - If pctfree is set to allow all rows to expand without fragmenting, the less disk I./O will occur on subsequent SQL selects.
Reorganizing tables to cluster rows with indexes - If tables are placed in the same physical order as the most frequently used index, disk I/O will drop dramatically. This can be done via a cluster table or an IOT.
3. Oracle SQL - Within SQL statements, there are many techniques to reduce physical disk I/O:
Preventing unnecessary full table scans using indexes or hints - This is the most important way to reduce disk I/O because many SQL queries can use indexes to reduce disk I/O.
Using bitmapped indexes - The use of bitmapped indexes will reduce full table scans on tables with low-cardinality columns, thereby reducing disk I/O.
Applying SQL hints- Many hints make SQL run faster and with less disk I/O. For example, the USE_HASH hint will reduce disk I/O by performing joins within SGA memory, reducing calls for database blocks.
Now that we have reviewed some of the things that we can do within Oracle to reduce disk I/O, let's take a close look at the nature of disk I/O and examine the internal workings of the disk I/O subsystem.
How to calculate no. of DB Writers in OLTP databases?
The DB_WRITER_PROCESSES initialization parameter lets you configure multiple database writer processes (from DBW0 to DBW9 and from DBWa to DBWj). Configuring multiple DBWR processes distributes the work required to identify buffers to be written, and it also distributes the I/O load over these processes. Multiple db writer processes are highly recommended for systems with multiple CPUs (at least one db writer for every 8 CPUs) or multiple processor groups (at least as many db writers as processor groups).
The Default value is 1 if CPU_COUNT is less than 8 , and CPU_COUNT / 8 if CPU_COUNT is more than 8
Based upon the number of CPUs and the number of processor groups, Oracle Database either selects an appropriate default setting for DB_WRITER_PROCESSES or adjusts a user-specified setting.
The above configuration is worked for me. Before any change in production test in your pre-prod env. and check with Oracle support. Without any reason go with Oracle default setting.
Thanks ...Gouranga...
No comments:
Post a Comment