Top Oracle 9i init.ora Parameters Affecting Performance

PURPOSE
——-

This note describes Oracle 9i new init.ora parameters that affect database
performance. This note is continuation of Note 100709.1 which talks about 8i
parameters that affect database performance

SCOPE & APPLICATION
——————-

This article detail information on 9i new init.ora parameters that affect
performance.

Top Oracle 9i init.ora parameters affect performance.
——————————————————–

The parameters listed below are new 9i parameters that affect database
performance and should be tuned properly to get the optimal database performance.


cursor_sharing
db_cache_size
db_keep_cache_size
db_recycle_cache_size
db_16k_cache_size
db_2k_cache_size
db_32k_cache_size
db_4k_cache_size
db_8k_cache_size
db_cache_advice
pga_aggregate_target
sga_max_size
statistics_level
workarea_size_policy

– CURSOR_SHARING

The parameter is not a 9i new parameter , but in 9i it can be set to new values.
This parameter determines what kind of SQL statements can share the same cursors.

Values:

FORCE

Forces statements that may differ in some literals, but are otherwise identical,
to share a cursor, unless the literals affect the meaning of the statement.

SIMILAR

Causes statements that may differ in some literals, but are otherwise identical,
to share a cursor, unless the literals affect either the meaning of the statement
or the degree to which the plan is optimized.

EXACT

Only allows statements with identical text to share the same cursor.

Setting this parameter to FORCE or SIMILAR will cause similar SQL statement to
be shared and will reduce the parsing overhead for parsing similar SQL statement
that differs only in literal values.

Setting this parameter also can have negative side effects, Replacing literals
with bind variable will cause the optimizer to choose different execution plan
that may make some queries perform slower than before , so you should be careful
setting this parameter and you should do intensive testing to your application
before setting it , also consult your application vendor before setting it.

– DB_CHACHE SIZE

DB_CACHE_SIZE specifies the size of the DEFAULT buffer pool for buffers with the
primary block size (the block size defined by the DB_BLOCK_SIZE parameter).
The value must be at least the size of one granule (smaller values are automatically
rounded up to the granule size). A value of zero is illegal because zero is the
size of the DEFAULT pool for the standard block size, which is the block size for
the SYSTEM tablespace.

This parameter will replace DB_BLOCK_BUFFERS parameter, which will be
deprecated in future versions. Oracle recommends that you use DB_CACHE_SIZE
instead.

The value of this parameter will determine the size of the default buffer cache
which also affect the buffer cache hit ratio

Please refer to Note 62172.1 for further information on Tuning the Database
Buffer Cache.

– DB_KEEP_CACHE_SIZE & DB_RECYCLE_CACHE_SIZE

These 2 parameters replace the BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE
parameters that were used in Oracle 8i

DB_KEEP_CACHE_SIZE & DB_RECYCLE_CACHE_SIZE specifies the size of the KEEP &
RECYCLE buffer pool. The size of the buffers in the KEEP & RECYCLE buffer pool
is the primary block size (the block size defined by the DB_BLOCK_SIZE parameter).

– DB_2K_CACHE_SIZE, DB_4K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_16K_CACHE_SIZE,
DB_32_CACHE_SIZE

These are new parameters in 9i. In Oracle9i, the database buffer cache
can be composed of 7 sub-caches. DB_nK_CACHE_SIZE specifies the size of the cache
for the nK buffers. You can set this parameter only when DB_BLOCK_SIZE has a
value other than nK. For example, if DB_BLOCK_SIZE=4096, it is illegal to
specify the parameter DB_4K_CACHE_SIZE (because the size for the 4 KB block cache
is already specified by DB_CACHE_SIZE).

So in oracle 9i we can create tablespaces with different block sizes and each
tablespace will use the Sub-buffer cache that match it’s block size. this will
also mean that we can have OLTP and DSS applications sharing the same Database
each application is using the proper Blocks size and also has it’s own buffer
cache . also this feature will make transporting tablespace between databases
with different block size possible and easy to do

– DB_CACHE_ADVICE

DB_CACHE_ADVICE enables or disables statistics gathering used for predicting
behavior with different cache sizes through the V$DB_CACHE_ADVICE performance
view. This parameter will be replaced in oracle 9.2.0 with the STATISTIC_LEVEL
parameter

Values:

OFF

Advisory is turned off and the memory for the advisory is not allocated.

READY

Advisory is turned off but the memory for the advisory remains allocated.
Allocating the memory before the advisory is actually turned on avoids the risk
of an error when you switch the parameter to ON.

If the parameter is switched to this state from ON, the contents of the view are
preserved and the memory for the advisory is retained. If the parameter is
switched to this state from OFF, you may get an error.

ON

Advisory is turned on. CPU and memory overheads are incurred. Attempting
to set the parameter to this state when it is already in the OFF state may
result in an error. Otherwise, the view (V$DB_CACHE_ADVICE) is reset and statistics
are gathered to the newly refreshed view. If the parameter is in the READY state,
you can set it to ON without any errors because the memory is already allocated.
The view is reset and statistics are displayed in the newly refreshed view.

For more information about cache advisory please refer to
Note 148511.1 “Oracle9i NF: Dynamic Buffer Cache Advisory”

– PGA_AGGREGATE_TARGET

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to
all server processes attached to the instance. You must set this parameter to
enable the automatic sizing of SQL working areas used by memory-intensive SQL
operators such as sort, group-by, hash-join, bitmap merge, and bitmap create.
Oracle uses this parameter as a target for PGA memory. Use this parameter to
determine the optimal size of each work area allocated in AUTO mode (in other
words, when WORKAREA_SIZE_POLICY is set to AUTO.

Oracle attempts to keep the amount of private memory below the target specified
by this parameter by adapting the size of the work areas to private memory. When
increasing the value of this parameter, you indirectly increase the memory allotted
to work areas. Consequently, more memory-intensive operations are able to run
fully in memory and less will work their way over to disk.

When setting this parameter, you should examine the total memory on your system
that is available to the Oracle instance and subtract the SGA. You can assign the
remaining memory to PGA_AGGREGATE_TARGET.
pga_aggregate_target

Please Note that if you create a new 9i Database, the default value for this parameter will be 25 MB,
which wil not be sufficient for most Production Databases.

So start with an approximate value of say 50MB based on how much total memory is available to your instance.

Then using the statspack reports and the Pga advisory views( as mentioend below) monitor
the PGA usage and then accordingly increase or decrease the PGA_AGGREGATE_TARGET.

Leaving this parameter at default value on a heavily loaded system might cause excessive swapping
and may even lead to running the box out of memory if it is not properly tuned.

This could severely impact performance.

For more information about cache advisory please refer to the following notes

Note 147806.1 Oracle9i New Feature Automated SQL Execution Memory Management
Note 148346.1 Oracle9i Monitoring Automated SQL Execution Memory Management
Note 223730.1 Automatic PGA Memory Managment in 9i

If PGA_AGGREGATE_TARGET is set in the init.ora and Dedicated Server connection is used,
then SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_bitmap_AREA_SIZE are ignored.
If Shared server connection is used then PGA_AGGREGATE_TARGET will be ignored and the *_AREA_SIZE
parameter will take precedence.

If PGA_AGGREGATE_TARGET is set in init.ora, then WORKAREA_SIZE_POLICY defaults
to AUTO.

Note:

The PGA_AGGREGATE_TARGET parameter is not supported on OpenVMS, for more info please
refer to the following note

Note 244163.1 Database Initialization Parameters that are Not Supported on OpenVMS

– SGA_MAX_SIZE

SGA_MAX_SIZE specifies the maximum size of SGA for the lifetime of the instance.
In Oracle8i Database Administrators (DBAs) do not have any control over the
SGA size once the instance is started. Oracle9i allows a DBA to modify the SGA
size dynamically. This provides an SGA that will grow and shrink in response to
a DBA command.

This feature implements an infrastructure to allow the Oracle server to change
the SGA configuration without shutting down the instance. The Oracle Server
can modify the physical address space use to respond to the operating system’s
use of physical memory

For more information about cache advisory please refer to the following note

Note 148495.1 Oracle9i New Feature Dynamic SGA

– STATISTICS_LEVEL

STATISTICS_LEVEL sets the statistics collection level of the database.

Oracle9i Release 2 introduces a comprehensive set of advisories including shared
pool sizing advisor, SQL Execution Memory (PGA) Memory Advisor and Recovery Cost
Estimator. All the advisories in Oracle9i Release 2 including the Buffer Cache
Advisor are controlled by a newly introduced parameter STATISTICS_LEVEL. The
Parameter DB_CACHE_ADVICE has, therefore, been deprecated in Oracle9i Release 2.
By default, the STATISTICS_LEVEL parameter is set to TYPICAL thereby enabling
all the advisories. V$STATISTICS_LEVEL lists the status of the statistics or
advisories controlled by the STATISTICS_LEVEL initialization parameter. Each row
of V$STATISTICS_LEVEL represents one of these statistics or advisories. For more
details on the STATISTICS_LEVEL parameter, please refer to Oracle9i Release 2
Performance Tuning Guide and Reference.

Note 259394.1 Init.ora Parameter “STATISTICS_LEVEL” Reference Note

– WORKAREA_SIZE_POLICY

WORKAREA_SIZE_POLICY specifies the policy for sizing work areas. This parameter
controls the mode in which working areas are tuned.

Values:

AUTO

Work areas used by memory-intensive operators are sized automatically, based
on the PGA memory used by the system, the target PGA memory set in PGA_
AGGREGATE_TARGET, and the requirement of each individual operator. You can
specify AUTO only when PGA_AGGREGATE_TARGET is defined.

MANUAL

The sizing of work areas is manual and based on the values of the *_AREA_
SIZE parameter corresponding to the operation (for example, a sort uses SORT_
AREA_SIZE). Specifying MANUAL may result in sub-optimal performance and
poor PGA memory utilization.

For more information about cache advisory please refer to the following notes

Note 147806.1 Oracle9i New Feature Automated SQL Execution Memory Management
Note 148346.1 Oracle9i Monitoring Automated SQL Execution Memory Management

RELATED DOCUMENTS
—————–

Note 100709.1 Top 8 init.ora Parameters Affecting Performance
Note 148511.1 Oracle9i New Feature: Dynamic Buffer Cache Advisory
Note 147806.1 Oracle9i New Feature Automated SQL Execution Memory Management
Note 148346.1 Oracle9i Monitoring Automated SQL Execution Memory Management
Note 148495.1 Oracle9i New Feature Dynamic SGA
Note 259394.1 Init.ora Parameter “STATISTICS_LEVEL” Reference Note

.

Bookmarks Admin Profile Feedback Sign Out Help

Copyright © 2006, Oracle. All rights reserved. Legal Notices and Terms of Use | Privacy Statement

Anúncios

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em PARAMETROS, PLSQL TUNING e marcado . Guardar link permanente.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s