How to measure the performance of 1.processes, 2.open_cursors 3.java_pool_size 4.large_pool_size

How to measure the performance of the following parameters.

1.processes, 2.open_cursors 3.java_pool_size 4.large_pool_size

is there any quries to measure the performance of the parameters like cache hit ratio for db_cache_size parameter.

How to know whether i have to increase the values of these parameters or not.

Please help me in this regard.

Thanks & REgards
Raghavender Rao Kodumuri

——————————————————————————–

From: Andrew Allen 21-Apr-06 13:40
Subject: Pt1: Re : Measuring the performance

save is a script called something like shared_pool_advice.sql

set echo off
set feedback off
set linesize 96

COLUMN a HEADING ‘ ‘
COLUMN est_mb FOR 99,999 HEADING ‘Shared Pool|Size (MB)’
COLUMN multiplier FOR 9.99 HEADING ‘ | |Multilpier’
COLUMN LC_MB FOR 9,999 HEADING ‘Lib. Cache|(MB)’
COLUMN LC_OBJ FOR 999,999 HEADING ‘Lib. Cache|Objects’
COLUMN LC_TS FOR 999,999 HEADING ‘Parse Time|Saved (Sec)’
COLUMN LC_TSF FOR 999.99 HEADING ‘Parse Time|Saved Factor’
COLUMN LC_OH FOR 999,999,999 HEADING ‘Lib. Obj.|Hits’
COLUMN sys_id NOPRINT new_value system_id
COLUMN sys_startup NOPRINT new_value startup_date

TTITLE Left ‘DB :’ system_id –
Center ‘Shared Pool Tuning Estimates’ Skip 1 –
Left ‘Up Since: ‘ startup_date –
Center ‘~~~~~~~~~~~~~~~~~~~~~~~~~~~~’ Skip 2

select d.name AS sys_id,
TO_CHAR(i.startup_time,’dd Mon yyyy hh24:mi’) as sys_startup,
p.SHARED_POOL_SIZE_FOR_ESTIMATE AS est_MB,
p.SHARED_POOL_SIZE_FACTOR AS multiplier,
p.ESTD_LC_SIZE AS LC_MB,
p.ESTD_LC_MEMORY_OBJECTS AS LC_OBJ,
p.ESTD_LC_TIME_SAVED AS LC_TS,
p.ESTD_LC_TIME_SAVED_FACTOR AS LC_TSF,
p.ESTD_LC_MEMORY_OBJECT_HITS AS LC_OH
from V$SHARED_POOL_ADVICE p,
v$database d,
v$instance i;

@@shared_pool_reserved
@@shared_pool_advice_d

——————————————————————————–

From: Andrew Allen 21-Apr-06 13:41
Subject: Pt 2: Re : Measuring the performance

Save in a file called shared_pool_reserved.sql

set echo off
set feedback off
set heading off

TTITLE Center ‘Shared Pool Reserved’ Skip 1 –
Center ‘~~~~~~~~~~~~~~~~~~~~’ Skip 2

select ‘shared_pool_reserved_size init.ora paramater : ‘||to_char(value,’999,999,999’)
from v$parameter where name = ‘shared_pool_reserved_size’
union all
select ‘Freespace on reserved list : ‘||to_char(FREE_SPACE,’999,999,999’)
from v$shared_pool_reserved
union all
select ‘Avg. size of free memory : ‘ || to_char(AVG_FREE_SIZE,’999,999,999’)
from v$shared_pool_reserved
union all
select ‘Num. of free places on reserd list : ‘|| to_char(FREE_COUNT,’999,999,999’)
from v$shared_pool_reserved
union all
select ‘Largest piece of free memory on reserved list : ‘ || to_char(MAX_FREE_SIZE,’999,999,999’)
from v$shared_pool_reserved
union all
select ‘Total used memory on reserved list : ‘ ||to_char(USED_SPACE,’999,999,999’)
from v$shared_pool_reserved
union all
select ‘Average size of used memory on reserved list : ‘||to_char(AVG_USED_SIZE,’999,999,999’)
from v$shared_pool_reserved
union all
select ‘Number of used pieces of memory on reserved list : ‘|| to_char(USED_COUNT,’999,999,999’)
from v$shared_pool_reserved
union all
select ‘Largest piece of used memory on reserved lsit : ‘ || to_char(MAX_USED_SIZE,’999,999,999’)
from v$shared_pool_reserved
union all
select ‘Number of times reserved list searched for free memory : ‘|| to_char(REQUESTS,’999,999,999’)
from v$shared_pool_reserved
union all
select ‘- – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – ‘
from dual
union all
select ‘Number of times could reserved list has to start flushing LRU : ‘ || to_char(REQUEST_MISSES,’999,999,999’)
from v$shared_pool_reserved
union all
select ‘Size of last memory request miss : ‘ || to_char(LAST_MISS_SIZE,’999,999,999’)
from v$shared_pool_reserved
union all
select ‘Size of the largest memory request miss : ‘ ||to_char(MAX_MISS_SIZE,’999,999,999’)
from v$shared_pool_reserved
union all
select ‘Number of times no memory found (ORA-04031) : ‘||to_char(REQUEST_FAILURES,’999,999,999’)
from v$shared_pool_reserved
union all
select ‘Size of the last no memory found (ORA-04031) : ‘|| to_char(LAST_FAILURE_SIZE,’999,999,999’)
from v$shared_pool_reserved
union all
select ‘Minimum size of request that causes an ORA-04031 W/O flushing LRU: ‘|| to_char(ABORTED_REQUEST_THRESHOLD,’999,999,999,999’)
from v$shared_pool_reserved
union all
select ‘Number of requests that signaled ORA-04031 without flushing LRU : ‘|| to_char(ABORTED_REQUESTS,’999,999,999’)
from v$shared_pool_reserved
union all
select ‘Size of last request that signaled ORA-04031 : ‘|| to_char(LAST_ABORTED_SIZE,’999,999,999’)
from v$shared_pool_reserved;

TTITLE off
set feedback on
set heading on

——————————————————————————–

From: Andrew Allen 21-Apr-06 13:42
Subject: Pt 3: Re : Measuring the performance

save in a file called shared_pool_advice_d.sql

set underline off
set feedback off
COLUMN TEXT FOR a96 HEADING ”

SELECT ‘V$SHARED_POOL_ADVICE displays information about estimated parse time savings in the shared pool for different sizes.’ ||
CHR(10) || ‘.’ ||
CHR(10) || ‘SHARED POOL SIZE : Shared pool size for the estimate (in MB).’ ||
CHR(10) || ‘MULTIPLIER : Size factor with respect to the current shared’ ||
CHR(10) || ‘ pool size. 1.00 = current shared pool size.’ ||
CHR(10) || ‘LIB. CACHE (MB) : Est. memory in use by the library cache (in MB)’ ||
CHR(10) || ‘LIB. CACHE OBJECTS: Est. number of library cache memory objects in’ ||
CHR(10) || ‘ the shared pool of the specified size.’ ||
CHR(10) || ‘PARSE TIME SAVED : Est. elapsed parse time saved (in seconds), owing’||
CHR(10) || ‘ to library cache memory objects being found in a’ ||
CHR(10) || ‘ shared pool of the specified size. This is the ‘ ||
CHR(10) || ‘ time that would have been spent in reloading the’ ||
CHR(10) || ‘ required objects in the shared pool had they been’||
CHR(10) || ‘ aged out due to insufficient amount of available’ ||
CHR(10) || ‘ free memory.’ ||
CHR(10) || ‘PARSE TIME SAVED FACTOR: Estimated parse time saved factor with respect’ ||
CHR(10) || ‘ to the current shared pool size.’ ||
CHR(10) || ‘LIB. OBJ. HITS : Est. number of times a library cache memory object’ ||
CHR(10) || ‘ was found in a shared pool of the specified size.’
as text
FROM dual;

set underline ‘-‘

TTITLE off
set feedback on

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, PARAMETROS, PLSQL TUNING, SGA 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