Home » RDBMS Server » Performance Tuning » Buffer Pool
Buffer Pool [message #246761] Thu, 21 June 2007 15:53 Go to next message
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 #248560 is a reply to message #246761] Fri, 29 June 2007 09:07 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Could you someone please guide me?
I really apprecited your help.

Thanks,
Re: Buffer Pool [message #248582 is a reply to message #248560] Fri, 29 June 2007 11:08 Go to previous messageGo to next message
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 messageGo to next message
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

Re: Buffer Pool [message #251179 is a reply to message #248586] Thu, 12 July 2007 10:29 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks.
Previous Topic: ORA-1652: unable to extend temp segment by 16 in TEMP TS
Next Topic: statspack table fetch continued row
Goto Forum:
  


Current Time: Sat Nov 23 08:10:42 CST 2024