Can We Tune Row Cache!

PURPOSE
——-
To determine what options we have to tune Row Cache in Oracle7 through
Oracle9i

SCOPE & APPLICATION
——————-

This article is intended for Oracle Support Analysts, Oracle Consultants and
Database Administrators

What is Row Cache?
——————

Row Cache is another name of Dictionary Cache which is the part of the Shared
Pool that contains Data Dictionary objects definitions available in memory to speed
up Dicionary operations. Before Oracle7, every Dictionary object had a seperate init.ora
parameter that controls it, these parameters were known as the “DC” parameters,
but starting from Oracle7 all Dictionary cache objects are automatically controlled
within the shared pool by the one parameter Shared_Pool_Size.

Monitoring Row Cache
——————–

The V$ROWCACHE view contains statistics for each single type of the data dictionary
items. These statistics reflect all data dictionary activity since the most
recent instance startup. These columns in the V$ROWCACHE view reflect the efficiency
of the Row Cache:

PARAMETER
Identifies a particular data dictionary item. For each row, the value in this
column is the item prefixed by dc_.

GETS
Shows the total number of requests for information on the corresponding item.

GETMISSES
Shows the number of data requests which were not satisfied by the cache.

MODIFICATIONS
Shows the number of times data in the dictionary cache was updated.

Use the following query to monitor the statistics in the V$ROWCACHE view over
a period of time while your application is running:

SELECT parameter
, sum(gets)
, sum(getmisses)
, 100*sum(gets – getmisses) / sum(gets) pct_succ_gets
, sum(modifications) updates
FROM V$ROWCACHE
WHERE gets > 0
GROUP BY parameter;

Another important thing to watch is Row Cache Objects Latch, this latch comes
into play when a process attempts to access or update an entry in the Dictionary
Cache. This latch contention may cause slow performance or Database operations
to hang.

How to Tune Row Cache
———————

Tuning Row cache is so limited, some resources point to it as non-tunable,so
if the pct_succ_gets above is not getting closer to “1” or if you detect
a high “Row Cache Objects” Latch contention in performance tuning reports then
you need to add more shared pool by either increasing SHARED_POOL_SIZE or avoid
Sharde Pool Fragmentation.Alternatively, configuring the library cache to an
acceptable size usually ensures that the data dictionary cache is also properly
sized. So tuning Library Cache will tune Row Cache indirectly. However , the
following tips have direct and important effect on tuning Row Cache:

1. Use Locally Managed tablespaces for your application objects especially
indexes, this will decrease Row Cache locks in a surprising fashion and
consequently avoid common hanging problems.

2. Review and amend your database logical design , a good example is to merge or
decrease the number of indexes on tables with heavy inserts.

RELATED DOCUMENTS
—————–

Oracle8i Designing and Tuning for Performance
Oracle9i Performance Guide and Reference

.

Bookmarks Admin Profile Feedback Sign Out Help

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

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 BUFFERS, STATSPACK e marcado , . Guardar link permanente.

Deixe uma resposta

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