Home » RDBMS Server » Performance Tuning » Buffer Pool
Buffer Pool [message #246761] |
Thu, 21 June 2007 15:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
poratips
Messages: 345 Registered: April 2005 Location: IL
|
Senior Member |
|
|
Hi,
I have few question about Tuning Buffer Cache to put objects in Multi Buffer pool,
currently we have only 'DEFAULT' Buffer Pool configured and i am thinking to
create KEEP and RECYCLE Pool too and I ran the following query after reading the
docs but somwhow confused to determine the conclusion.
I have collected block buffer hit ratio from V$BUFFER_POOL_STATISTICS
(different time) and it's gives me negative, What it should be?
In development DB is like 91.
1)block buffer hit ratio - percentage of data blocks being accessed from the memory to
that of the disk
SELECT NAME, 100 – round ((PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))*100,2) HitRatio
FROM V$BUFFER_POOL_STATISTICS;
NAME HITRATIO
DEFAULT -27.43
-102.63
[2] I am running following queries to identified objects for KEEP and RECYCLE buffer pool.
I just consider those objects from both query and put in a respective pool?
What criteria I should consider?
Please see the query 2a, 2b and 2c.
2a)KEEP Buffer Pool Objects Query?
SELECT o.owner, object_name, object_type, COUNT(1) buffers
--FROM SYS.x$bh, dba_objects o
FROM x$bh, dba_objects o
WHERE tch > 10
AND lru_flag = 8
AND obj = o.object_id
AND o.owner not in ('SYSTEM','SYS')
GROUP BY o.owner, object_name, object_type
ORDER BY buffers;
2b)KEEP Buffer Pool Objects Query?
SELECT owner, object_name, object_type, obj, count(file#)
FROM x$bh, dba_objects o
WHERE x$bh.obj = O.object_id
and o.owner not in ('SYS','SYSTEM')
and status != 'free'
GROUP BY o.owner, object_name, object_type, obj
order by count(file#) DESC;
2c) Recycle Buffer Pool Objects Query?
SELECT o.owner, object_name, object_type, COUNT(1) buffers
FROM x$bh, dba_objects o
WHERE (tch = 1 OR (tch = 0 AND lru_flag < 8))
AND obj = o.object_id
AND o.owner not in ('SYSTEM','SYS')
GROUP BY o.owner, object_name, object_type
ORDER BY buffers;
4) We have Buffer Cache Hit Ratio ==> 93 ( So it's ok, right?)
5) I have also run the query from v$buffer_pool_statistics and what I should take in consider
(which field / criteria) to tune?
select * from v$buffer_pool_statistics
6) I have also run the query from V$DB_CACHE_ADVICE and what I should take in consider
(which field / criteria) to tune?
select * from V$DB_CACHE_ADVICE
7)I also ran following query:
select table_name, blocks from dba_tables where owner = <OWNER>
8) SELECT value "total buffers" FROM v$parameter WHERE name = 'db_cache_size';
Total Buffers
419430400
9) then I also ran following query and calculated buffer Ratio, What I should consider here?
SELECT data_object_id, o.owner, object_name, object_type, COUNT(1) buffers, COUNT(1) /419430400
FROM x$bh, dba_objects o
WHERE obj = o. object_id
AND o.owner NOT IN ('SYSTEM','SYS')
GROUP BY data_object_id,o.owner, object_name, object_type
ORDER BY buffers;
419430400 is a Total Buffers from following query:
SELECT value "total buffers" FROM v$parameter WHERE name = 'db_cache_size';
10) I read in a docs that
The block size for a tables' tablespace should always be greater than the average row length
for the table (dba_tables.avg_row_len).
Not it is smaller than the average row length, rows chaining occurs and excessive disk I/O is incurred.
is it block size means DB_BLOCK_SIZE, right?
11)Finaly I ran the following query to analysis of those objects in the data buffers
means irts lists the tables and indexes that reside inside the data buffer but little confused
that what should be consider?
SELECT t1.owner "OWNER",
object_name "Object_Name",
CASE WHEN object_type = 'TABLE PARTITION' THEN 'TAB PART'
WHEN object_type = 'INDEX PARTITION' THEN 'IDX PART'
ELSE object_type END Object_Type,
SUM(num_blocks) "Num of Blocks in Buffer Cache",
(SUM(num_blocks)/GREATEST(SUM(blocks), .001))*100 "% of Blocks in Buffer Cache",
BUFFER_POOL "Buffer Pool",
SUM(bytes)/SUM(blocks) "Block Size"
FROM
(SELECT o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
COUNT(DISTINCT FILE# || BLOCK#) num_blocks
FROM dba_objects o, v$bh bh
WHERE o.data_object_id = bh.objd AND o.owner NOT IN ('SYS','SYSTEM')
AND bh.status != 'free'
GROUP BY o.owner, o.object_name, o.subobject_name, o.object_type) T1
, dba_segments S
WHERE s.segment_name = t1.object_name
AND s.owner = t1.owner
AND s.segment_type = t1.object_type
AND NVL(s.partition_name,'-') = NVL(t1.subobject_name,'-')
GROUP BY t1.owner, object_name, object_type, BUFFER_POOL
HAVING SUM(num_blocks) > 10
ORDER BY SUM(num_blocks) DESC
Thanks in advance for your valuable time and help!
[Updated on: Thu, 21 June 2007 22:59] by Moderator Report message to a moderator
|
|
|
|
Re: Buffer Pool [message #248582 is a reply to message #248560] |
Fri, 29 June 2007 11:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
Why do you think that multiple buffer pool will resolve any of your performance problems?
And if you don't have performance problems - why do you need it?
Michael
|
|
|
Re: Buffer Pool [message #248586 is a reply to message #246761] |
Fri, 29 June 2007 11:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
poratips,
>i am thinking to create KEEP and RECYCLE Pool
What make you think that moving SQL from one bucket in the SGA to another bucket will have ANY measurable impact on performance?
It appears you suffer from Compulsive Tuning Disorder.
I suggest you find a relaxing hobby to take your mind away from meaningless metrics.
HAND!
[Updated on: Fri, 29 June 2007 11:33] by Moderator Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Fri Sep 06 15:44:22 CDT 2024
|