|
|
This chapter includes the following sections:
Oracle database makes use of initialization parameters to
Some of these parameters can have a significant impact on the performance of a WLI application. This section describes how some of the more important parameters can be tuned to get better performance with WLI.
The compatible initialization parameter can be used to set down the compatibility of the instance to a prior release of Oracle. Compatibility should be set to 10.2.0 for use with this tuning guide.
This parameter sets the cache size for data contained in multiple block size tablespaces. Applications can make use of multiple block size tablespaces to reduce I/O for larger data objects like large objects (LOBs) and index segments. Values for n (in nK) can be 4, 8, 16, or 32 and must be a multiple of the default block size set by DB_BLOCK_SIZE. This parameter should only be set when using multiple block size tablespaces. See Multiple Block Size Buffer Caches, or Caching JPD BLOB Data in WLI Schema Tuning for more information on setting this parameter.
This is a deprecated parameter. The DB_BLOCK_SIZE parameter should be used instead. Use of this parameter will disable the use of multiple block size tablespaces.
This parameter sets the default block size of the database. It can only be set during the creation of the Oracle database. The setting of this parameter for a WLI database should be based on the characteristics of the application created using WLI.
| Note: | A small default block size can cause ORA-01450 errors for indexes with large key lengths. If the database is shared with another application that has an index with a large key length, the index will have to be moved to a multiple block-size tablespace with a block size large enough to accommodate the larger index key length. |
This parameter sets the size of the default buffer pool for the Oracle database. The default buffer pool is used to cache highly utilized data in memory for faster access. This area should be set appropriately to accommodate 90% of all requests for information from the database as measured by the buffer hit % ratio in Statspack. See Oracle Statspack for more information on using Statspack.
This parameter sets the number of blocks Oracle will request from the I/O subsystem for a sequential read such as a full table scan. This value should be set to 16 for use with WLI. Values greater than 16 increase the likelihood of the Oracle optimizer choosing full scans over index lookups.
This parameter sets the size of the KEEP buffer pool. The KEEP buffer pool is an alternate buffer pool for default block size data objects. This buffer pool can be used to segregate cached data objects from the default buffer pool such as lookup tables that could possibly be aged out of the default buffer pool by more dynamic data. This parameter should be set only if using the KEEP pool. See Caching JPD BLOB Data in WLI Schema Tuning for more information on using alternate buffer pools with WLI.
This parameter sets the size of RECYCLE buffer pool. The RECYCLE buffer pool is an alternate buffer pool for default block size data objects. This buffer pool can be used to segregate cached data objects from the default buffer pool such as highly dynamic data that could possibly age data out of the default buffer pool. This parameter should be set only if using the RECYCLE pool. See Caching JPD BLOB Data in WLI Schema Tuning for more information on using this buffer pool with WLI.
This parameter sets the maximum number of simultaneous DML operations that can occur from all concurrent transactions in the database. On very-high-volume transactional database systems (such as WLI), the default value (4 X TRANSACTIONS) may not be enough and can be set to a higher static value. This limit can alternatively be removed completely by setting the value to 0 (zero) but it has the consequence of disabling DROP TABLE, CREATE INDEX, and explicit LOCK statements.
For most WLI implementations, the default value for this parameter is adequate. On systems where a high number of enqueue waits are observed and all other methods of tuning for enqueue waits have been exhausted, this value should be altered. Consult the DBA before altering the default value.
This parameter is deprecated in Oracle9i and should not be set. Setting this parameter overrides the use of FAST_START_MTTR_TARGET, which is the Oracle-recommended method of limiting instance recovery time in Oracle9i.
This parameter limits the mean time to recovery (MTTR) after a database instance crash. Use of this feature (although seemingly advantageous) can hinder performance on some systems due to the increased contention for I/O while dirty buffers are continuously flushed to disk. On some WLI databases, where I/O is identified as a performance problem, lowering the value for FAST_START_MTTR_TARGET can enhance the performance to a great extent.
WLI performance is slightly improved when hash joins are disabled. This parameter should be set to FALSE when not needed by another application running in the same database instance.
This parameter sets the amount of memory Oracle uses to buffer entries written to the online REDO log. WLI applications that have a high volume of transactions should set the value of this parameter higher than the default of 512 KB. Values of 1 - 2 MB provide good performance for high volume WLI applications.
Setting this parameter will interfere with the correct operation of FAST_START_MTTR_TARGET (the Oracle recommended method of limiting instance recovery time). This parameter should be set to 0 (zero) to allow the checkpoint interval to be controlled by FAST_START_MTTR_TARGET.
Setting this parameter will interfere with the correct operation of FAST_START_MTTR_TARGET (the Oracle recommended method of limiting instance recovery time). This parameter should be set to 0 (zero) to the allow checkpoint interval to be controlled by FAST_START_MTTR_TARGET.
The Oracle optimizer is responsible for generating the most efficient access paths to data. It can operate in a number of modes including: CHOOSE, RULE, FIRST_ROWS, and ALL_ROWS. WLI performance is greatly improved when the optimizer runs in the CHOOSE mode and database statistics have been gathered on all database objects. See Database Statistics for more information on gathering database statistics.
This parameter sets the target memory size for the Program Global Area (PGA) in Oracle. Use of this parameter in conjunction with WORK_AREA_SIZE_POLICY set to AUTO can increase performance dramatically for memory-intensive SQL operations such as sort and group by.
In order to have Oracle manage this area of memory automatically, the following parameters must be unset: BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE, HASH_AREA_SIZE, and SORT_AREA_SIZE.
Common values of this parameter for WLI are 32 MB and 64 MB. The value of this parameter can be fine tuned by looking at the PGA Memory Advisory section of the Oracle Statspack report. For information about running and using Oracle Statspack, see Oracle Statspack.
This parameter sets the max number of operating system user processes in Oracle and should be set to a minimum value of 600 for WLI database applications.
This parameter sets the amount of memory Oracle dedicates to caching shared cursors, stored procedures and control structures. A common setting of this parameter for WLI is 32 MB. To tune this parameter for optimal performance, see the Shared Pool Advisory section of the Oracle Statspack report. For more information on Oracle Statspack, see Oracle Statspack.
This parameter sets the amount, in seconds, of UNDO information to be retained in UNDO tablespaces. The retention of large amounts of undo information on a heavily loaded WLI database can place a substantial additional strain on the I/O subsystem. WLI does not use undo retention. Unless the database is being shared with other applications that do make use of this feature, it should be turned off (set to 0).
The default setting of this parameter is AUTO. Oracle recommends that this parameter be left as default to allow for the use of automatic SQL work area memory management.
The Oracle database uses an optimizer to create the most efficient access plans for retrieving data. The ability of the optimizer to select the best plan is strongly influenced by the amount of information (statistics) Oracle has about the underlying data and the performance of the system that will access the data. To give the optimizer the best chance of creating efficient data access plans, statistics should be gathered at the database, schema, and system levels.
This section details various database statistics that can be gathered.
Statistics gathered at database level capture information about the data structures and data for the entire database, including the SYSTEM and SYS schemas. Database level statistics should be gathered after database creation and periodically over the lifetime of the database.
Database level statistics can be gathered using the following script by a user with the SYSDBA system privilege.
-- gather database level statistics
begin
dbms_stats.gather_database_stats
(
estimate_percent => dbms_stats.auto_sample_size,
block_sample => FALSE,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
degree => NULL,
granularity => 'ALL',
cascade => TRUE,
stattab => NULL,
statid => NULL,
options => 'GATHER',
statown => NULL,
gather_sys => TRUE,
no_invalidate => FALSE,
gather_temp => FALSE
);
end;
/
Statistics gathered at the schema level only collect statistics on the objects within the target schema. Statistics should be gathered frequently for the WLI schema: at least once per week on low-volume systems and once daily on high volume systems. The need for frequent statistics gathering in the WLI schema is due to the highly dynamic nature of some WLI data structures.
Schema-level statistics can be gathered using the following script by the WLI schema owner or another user with the privileges.
-- gather schema level statistics
begin
dbms_stats.gather_schema_stats
(
ownname => 'WLI_SCHEMA',
estimate_percent => dbms_stats.auto_sample_size,
block_sample => FALSE,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
degree => NULL,
granularity => 'ALL',
cascade => TRUE,
stattab => NULL,
statid => NULL,
options => 'GATHER',
statown => NULL,
no_invalidate => FALSE,
gather_temp => FALSE
);
end;
/
Statistics gathered at the system level collect information about the performance characteristics of the database host OS and its subsystems. In particular, statistics are gathered on I/O performance; CPU performance; and system utilization. These statistics should be gathered while the database is under a typical workload using WLI.
System level statistics can be gathered using the following script by a user with the SYSDBA system privilege.
begin
dbms_stats.gather_system_stats
(
gathering_mode => 'INTERVAL',
interval => 60, -- time in minutes
stattab => NULL,
statid => NULL,
statown => NULL
);
end;
/
Normally, the slowest part of an Oracle database is its access to persisted data - disk I/O. To increase performance and concurrency for disk I/O, Oracle recommends separating I/O with distinct access characteristics onto separate disks or using high performance storage subsystems that have very high I/O bandwidth. This section addresses these recommendations.
Oracle recommends separating I/O into seven distinct I/O channels by data type:
This recommendation would require a minimum of seven disks to run Oracle. Adding redundancy would double this number. This recommendation is a not always practical. Smaller database systems do not generally have more than four disks. Many database systems do not exhibit the same need for these seven distinct I/O channels.
A better way of separating I/O is to identify the distinct access patterns of a database system based on the application running on it. WLI applications have access patterns that are similar to Online Transaction Processing (OLTP) systems with most of the requests for data being small and answered by in-memory data buffers. These data buffers are loaded into memory at first request and then remain in memory until they are aged out by other more frequently used data. This behavior does not put much stress on read I/O for application data or application indexes.
However, WLI does stress the I/O subsystem in the number and type of data writes. These writes are for REDO logs (and archive logs when running in archive log mode), LOB data, UNDO data, application data and application indexes.
For WLI, it is recommended the following data types be stored on physically separate disks or logical units (LUNs) when possible. They are listed in order of importance for separation. Systems that have fewer disks should attempt to separate the earlier data types first.
There are a variety of high performance storage subsystems that can increase performance of the Oracle database. These systems achieve very high I/O bandwidth rates by using large striped arrays (RAID 0); redundancy (RAID 1); high-speed connections like Fibre channel; and advanced load balancing algorithms within the storage system. Recommendations for these storage systems are beyond the scope of this document. However, WLI applications have shown increased performance when using an Oracle database with a high-performance storage subsystem. I/O performance can be evaluated in the “File I/O Stats” section of an Oracle Statspack report. See Oracle Statspack for more information on using Oracle Statspack.
Many database tables have primary or unique keys based on a sequence. These keys are usually indexed by b-tree indexes which, by nature, store the indexed values in order. This behavior of sequential storage gives this type of index the name of “monotonic” or “right-growing” index. These types of indexes can become performance bottlenecks on high-volume transactional systems because of serialization that occurs when inserting values into the leaf-blocks of these indexes.
To avoid this serialization, reverse-key indexes can be used. A reverse-key index stores indexed values in reverse-bit order. So, where the values (234, 235, 236) are stored sequentially and contiguously in a normal b-tree index, they are stored out of sequence and non-adjacent (236, 234, 235) for the reverse-key index (see Table 2-3). Over a larger set, this reversing of the key distributes the indexed values across the leaf-node blocks of the index, thereby eliminating the serialization on sequential inserts.
| Note: | Some caution should be used when choosing to use reverse-key indexes. Once an index is built in REVERSE, it can not be used for index range scans. This means that Oracle will have to use table scans to answer predicates that define a range of values, as in the following SQL statement: |
| Note: | WHERE salary > 100,000 |
| Note: | AND salary < 200,000 |
| Note: | / |
| Note: | To create a reverse-key index, the REVERSE keyword must be used to create or rebuild the index. |
| Note: | The following code sample shows how to create and rebuild an index. |
| Note: | -- create the index |
| Note: | CREATE UNIQUE INDEX table_pk |
| Note: | ON table (column) |
| Note: | REVERSE |
| Note: | COMPUTE STATISTICS |
| Note: | / |
| Note: | --rebuild the index |
| Note: | ALTER INDEX table_pk |
| Note: | REBUILD |
| Note: | REVERSE |
| Note: | COMPUTE STATISTICS |
| Note: | See WLI_PROCESS_INSTANCE_INFO Table in WLI Schema Tuning for more information on using reverse-key indexes with WLI. |
Oracle9i introduced a new feature that allowed a single instance of the database to have data structures with multiple block sizes. This feature is useful for databases that need the flexibility of using a small block size for transaction processing applications (OLTP); and a larger block size to support batch processing applications, decision support systems (DSS), or data warehousing. It can also be used to support more efficient access to larger data types like LOBs.
To create a multiple block size tablespace, the keyword BLOCKSIZE must be used when creating the tablespace, as shown in the following code sample.
-- create wli_lob_data tablespace
CREATE TABLESPACE wli_lob_data
LOGGING
DATAFILE '/oracle/oradata/perfdb01/wli_lob_data_01.dbf'
SIZE 1000M REUSE
BLOCKSIZE 16K
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 50M
SEGMENT SPACE MANAGEMENT AUTO
/
See JPD Tables in WLI Schema Tuning for more information on using multiple block size tablespaces with WLI.
| Note: | A multiple block size buffer cache must be created before a multiple block size tablespace can be created. See Multiple Block Size Buffer Caches for information on multiple block size buffer caches. |
To cache multiple block size data, Oracle9i has multiple block size buffer caches. These caches are used to buffer reads for data contained in multiple block size tablespaces.
Multiple block size caches can be created by running the following statement by a user with privileges, as shown in the following code sample.
-- create 16K block size cache
ALTER SYSTEM
SET db_16k_cache_size = 64M
SCOPE = BOTH
/
See JPD Tables in WLI Schema Tuning for more information on using multiple block size tablespaces with WLI.
LOB tuning includes tuning caching and setting appropriate physical storage parameters.
By default, LOB data is not cached in Oracle. Caching LOB data can have a significant positive effect on LOB access performance. However, caching LOB data in the DEFAULT pool can cause other application data to be quickly aged out. It is recommended that you cache LOB data in an alternate pool such as the RECYCLE or KEEP pools, or in a multiple block size cache when using a multiple block size tablespace.
LOB caching can be enabled by creating or altering a table to use it, as shown in the following code sample.
-- create table foo with LOB caching
CREATE TABLE foo
(
bar NUMBER(16),
baz BLOB
)
TABLESPACE wli_data
LOB (baz)
STORE AS
(
CACHE
)
/
-- alter table foo to use LOB caching
ALTER TABLE foo
MODIFY LOB (baz) (CACHE)
/
Setting the CHUNK parameter and disabling STORAGE IN ROW improve the database performance.
The CHUNK parameter sets the amount of data to be operated on at one time for a LOB in bytes. This value has to be set to a multiple of the block size for the LOB. Depending on the average data size of the LOBs stored, this value should be set as large as possible or until it exceeds the average size of the data stored for the LOB column.
To find the average LOB length for a table, use the following SQL statement.
-- get the average length (in bytes) of the bas LOB column in table foo
SELECT AVG(DBMS_LOB.GETLENGTH(baz)) avg_lob_len
FROM foo
/
AVG_LOB_LEN
-----------
13171.712
In the preceding example, the average LOB length for table foo is 13171.712 bytes, or ~13K. Setting the CHUNK size to 16K would make the average number of I/Os per request for LOB data from foo ~1.
LOB data can be stored in-line with the table's row (in the same segment) or can be stored in its own segment. Storing LOB data in its own segment can increase the efficiency and performance of data access, particularly when coupled with the storage of LOB data in a larger block size tablespace.
See JPD Tables in WLI Schema Tuning for more information on using LOB tuning with WLI.
The Oracle9i database has a feature whereby tables can be partitioned into smaller manageable pieces. Each of these pieces is stored in a separate physical data segment. Partitioning is transparent to the application and partitioned tables can be treated the same as standard non-partitioned tables. There are three basic methods by which a table can be partitioned: range, hash, and list. Only hash partitioning will be described in this document.
With hash partitioning, a table is sub-divided into a specified number of partitions by the hash of a key value found in the table. This partitioning, with the selection of a good value for the hash, serves to equally distribute data across the partitions of the table. In a busy table that suffers resource contention problems (high row lock waits, buffer busy waits) this type of tuning can have a very positive effect on performance.
To partition an existing table, a new partitioned table must be created and the data from the old table must be copied to the new table, as shown in the following code sample.
-- create non-partitioned table
CREATE TABLE foo
(
bar NUMBER(16),
baz BLOB,
CONSTRAINT foo_pk
PRIMARY KEY (bar)
)
TABLESPACE users
/
-- create new partitioned version of foo with data from foo
CREATE TABLE new_foo
(
bar,
baz,
CONSTRAINT new_foo_pk
PRIMARY KEY (bar)
)
TABLESPACE users
PARTITION BY HASH (bar)
PARTITIONS 32
AS SELECT * FROM foo
/
-- drop the original foo table
DROP TABLE foo
/
-- rename new_foo table to foo
RENAME new_foo TO foo
/
See WLI_PROCESS_INSTANCE_INFO Table in WLI Schema Tuning for more information on using partitioning with WLI.
|