Monitoring the average value of used buffers per objec

monitoring the average value of used buffers per object


i created a view displaying the buffer cache on a per userobject basis. i’m planning to monitor this view over time and build up a time based metric. if there are peaks, that is great differences from the average value of used buffers per object, this might a) point to ineffective sql running sometimes b) point out that this object might be a good candidate for the recycle buffer. do you agree?

create or replace view dba_cached_buffers_per_userobj as
select owner, object_name, object_type, object_buffers, (object_buffers * 100 / total_buffers) percent_ot_total
from (
SELECT owner, data_object_id, object_name, object_type
FROM dba_objects
WHERE object_type in (‘TABLE’, ‘INDEX’)
SELECT obj object_id, count(*) object_buffers
FROM x$bh
group by obj
SELECT value total_buffers
FROM v$parameter
WHERE name = ‘db_block_buffers’
WHERE data_object_id = object_id


From: Oracle, Thelonius Chestang 23-Oct-03 22:04
Subject: Re : monitoring the average value of used buffers per object


I’m not sure if that first query will work or not.

However, it all amounts to a lot of work to get a tiny bit of the information already provided in many other diagnostic tools.

One tool is the SQL Analyzer in Oracle Enterprise Manager.

Another one that is well favoured in Oracle Support is the Statspack.

It has this information an more in just one section of the report (the top sql section just after the wait events).

NOTE.94224.1 FAQ- Statspack Complete Reference
>> How to setup Statspack

NOTE.228913.1 Systemwide Tuning using STATSPACK Reports
>> How to use statspack

This is where I’d start. You need to know which sql are high consumers relative to other sql at that moment in time… as well as concurrent information in the database.

Rather than spend alot of time creating your own tools, I’d rather see you get accustomed to using the existing tools… which are very, very good. Of course, that is all up to you.

Very soon the Performance Tuning Assistant will be available to the public via Metalink. An excellent means of analyzing a statspack report… particularly when the performance signs are not so obvious.



From: Markus Zwettler 24-Oct-03 15:32
Subject: Re : monitoring the average value of used buffers per object

hi chess,

the view is working well. statspack doesn’t provide me with used buffer statistics nor does it point out with objects should be placed in another buffer. statspack is just another performance analysis toolkit. other tools like sql analyze can’t be used to create time based metrics.



From: Oracle, Thelonius Chestang 24-Oct-03 23:05
Subject: Re : monitoring the average value of used buffers per object


I tested the query on on internal systems.

I got a lot of sys owned objects, so I think you may want to modify the query to eliminate those. Also, I’m concerned that the query doesn’t take into account the “touch count” for the block associated with each object.

Check out note 136312.1 “Select database objects which are candidates for the recycle buffer cache”.

You may want to modify the query shown in that document to get same “filtering” provided by your query.



From: Markus Zwettler 28-Oct-03 14:53
Subject: Re : monitoring the average value of used buffers per object

thanks for the hint to the “select objects for recycle buffer” document. the query works fine but points to main entities of my crm application (base tables – customer, address, …). i am not sure if i want to have them in the recycle buffer.

a customer might call only once a week or even a month. so, its data might be flushed out of the buffer after his call. but the customer table for example includes 47 rows/block in average. the query showed around 9400 used buffers by this table, resulting in 47 x 9400 = 441800 cached customer records. i think this will improve the application performance as customers are searched by id, name, address or other personal attributes when they are calling. its an entry point into our crm application.

of course, the recycle buffer is also “just” a buffer cache in principle working on a LRU basis. therefore, i guess its more a philosophical question what should be included. i could also put the
hottest data in it. but as its name implies it should include recycleable cold data. i don’t think that the base tables above are cold ones and therefore candidates for the recycle buffer. ok?



From: Oracle, Thelonius Chestang 05-Nov-03 22:21
Subject: Re : monitoring the average value of used buffers per object


The RECYCLE POOL is intended for objects that don’t have as much chance of getting reused in a short (relative to your instance) period of time. Typically, large objects that would take up undesirable amounts of buffers that wouldn’t really be reused would go here. The goal of the recycle pool is to eliminate blocks from the Buffer Cache as soon as the transaction has completed.

“Hot” may be a relative term in your system as usage period change according to processing parameters.

Objects that are truly “HOT” should probably be in the KEEP pool….
or just cache them.


Or you may want to run CATPARR.sql to populate v$cache.

select owner#, name, count(*) blocks from v$cache
group by owner#, name;

(probably will need to modify this to exclude SYS/SYSTEM owned stuff)

If you sum the # of blocks for all objects and divide by 4, this gives you a “rough” estimate of how to size the recycle pool.

I hope this is helpful.

Oracle E-Support


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, PLSQL TUNING. Bookmark o link permanente.

Deixe um comentário

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

Logotipo do

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

Foto do Google+

Você está comentando utilizando sua conta Google+. 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 )


Conectando a %s