Home » RDBMS Server » Performance Tuning » suggestion for increasing the buffer cache (Oracle, 9.2.0.8, sunOS)
| suggestion for increasing the buffer cache [message #571764] |
Fri, 30 November 2012 03:43  |
 |
savugar
Messages: 33
Registered: February 2012
|
Member |
|
|
Hi,
I am facing below situation in my database.Considering the below factors, I am planning to increase the buffer cache value from 256Mb to 512Mb. I just want to know that increasing this buffer cache will be helpful to improve the performance.
1. Buffer cache hit ratio value is around 35% even in the normal period.
2. free buffer requested value is below during peak & normal hours below.
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
free buffer requested 54,694,995 15,226.9 2,523.7
free buffer requested 23,412,674 6,501.7 2,585.9
3. most of the top 5 physical reads & logical reads queries are well tuned and some of queries are doing FTS on small tables (table count min 1500 max 35000). SO indexing option is not required for these queires. But these queries getting executed frequently.
SQL> show sga
Total System Global Area 2148534928 bytes
Fixed Size 731792 bytes
Variable Size 1879048192 bytes
Database Buffers 268435456 bytes
Redo Buffers 319488 bytes
5.top 5 waitevents during db slow performance & high cpu utilization (>80%) issue.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free 1,848,898 153,793 52.00
buffer busy waits 395,280 87,201 29.49
db file scattered read 3,488,648 34,199 11.56
enqueue 4,052 10,897 3.68
CPU time 5,567 1.88
6. Top 5 waitvents during normal activities and CPU utilization is around 40%.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 1,860 45.32
db file scattered read 1,133,669 985 23.99
imm op 776 605 14.73
sbtinfo2 208 139 3.40
sbtbackup 2 123 3.00 |
|
|
|
| Re: suggestion for increasing the buffer cache [message #571766 is a reply to message #571764] |
Fri, 30 November 2012 03:54  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
The wait events you need to address are latch free and buffer busy wait. Increasing the size of your buffer cache will have minimal effect on these. You need to find out which latch sessions are waiting on, and which segments have the buffer busy waits. All this information is in the statspack report.
But you have not actually shown any problem. For example, are there some SQLs that are running too slowly?
|
|
|
|
|
|
|
|
| Re: suggestion for increasing the buffer cache [message #571771 is a reply to message #571770] |
Fri, 30 November 2012 04:03 |
|
savugar
Messages: 33
Registered: February 2012
|
Member |
|
|
"The wait events you need to address are latch free and buffer busy wait"
yes. I am planning to increase INITRANS value for some of the tables. is anyother thing I need to address to avoid this waits?
And I founf out the buffer busy segments in the report. But those are very small tables. the count is around 35ooo max.
So indexing is not required I believe. What else can be done to avoid this wait?
[Updated on: Fri, 30 November 2012 04:06] Report message to a moderator |
|
|
|
| Re: suggestion for increasing the buffer cache [message #571772 is a reply to message #571771] |
Fri, 30 November 2012 04:07 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:You need to find out which latch sessions are waiting on, and which segments have the buffer busy waits. All this information is in the statspack report.
initrans probably has nothing to do with it. You appear to be throwing possible solutions at an undefined problem without any sort of analysis. Slow down, man. First,what is the problem? And do not reply that the problem is some wait event. That is only a symptom of the problem. The problem will be some SQLs that are not responding fast enough.
|
|
|
|
|
|
|
|
|
|
| Re: suggestion for increasing the buffer cache [message #573009 is a reply to message #571776] |
Wed, 19 December 2012 13:00 |
 |
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
I reduced my physical I/O by a factor of 9 by increasing the buffer cache, creating a keep and recycle cache and caching the heaviest hit objects with reads in the keep and heaviest hit writes in the recycle. After 9.2.0.5, oracle does not recycle the buffers in the recycle cache so I use it to cache the objects that are heaviest hit on writes.
HOST INSTANCE UPDAYS BLKHIT PHYSRDS_PER_HOUR
--------------- -------- -------- ------- ----------------
proddb04 NWEBP1 55.584 99.90 107862 <== was around 1 million prior to increasing.
ENWEBP1P > list
1 select
2 host_name host,instance_name instance,sysdate-i.startup_time updays,
3 ROUND(((SUM(DECODE(Name, 'consistent gets', Value, 0))+
4 SUM(DECODE(Name, 'db block gets', Value, 0)) -
5 SUM(DECODE(Name, 'physical reads', Value, 0)) )/
6 (SUM(DECODE(Name, 'consistent gets',Value,0))+
7 SUM(DECODE(Name, 'db block gets', Value, 0)))) *100,2)
8 BlkHit,
9 round(SUM(DECODE(Name, 'physical reads',Value,0))/
10 to_number(sysdate-i.startup_time)/24) Physrds_per_hour
11 ,SUM(DECODE(Name, 'consistent gets',Value,0))/
12 to_number(sysdate-i.startup_time)*
13 8192/1024/1024/1024/24/60 Logical_GIG_PER_MIN
14 ,SUM(DECODE(Name, 'physical reads',Value,0)) Physrds
15 ,SUM(DECODE(Name, 'consistent gets',Value,0)) consistent
16 ,SUM(DECODE(Name, 'consistent gets',Value,0))/
17 to_number(sysdate-i.startup_time)/24 Con_per_hour
18 ,SUM(DECODE(Name, 'db block gets',Value,0)) Dbblock
19 ,SUM(DECODE(Name, 'db block gets',Value,0))/
20 to_number(sysdate-i.startup_time)/24 Dbblock_per_hour
21 from V$SYSSTAT,v$instance i
22* group by host_name,instance_name,(sysdate-i.startup_time)
[Updated on: Wed, 19 December 2012 13:02] Report message to a moderator |
|
|
|
| Re: suggestion for increasing the buffer cache [message #573891 is a reply to message #573009] |
Thu, 03 January 2013 19:49 |
|
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
I like to look at how may megabytes of each object is in memory and if you have a lot of objects that keep getting re-read into memory you will want to increase the buffer cache.
OBJECT_TYPE DB MEG_IN_MEMORY OBJECT_IN_MEMORY
----------- -------- ------------- -----------------------------------------
TABLE NWEBP2 501 SITE.VIDEO
TABLE NWEBP4 502 SITE.VIDEO
TABLE NWEBP1 502 SITE.VIDEO
INDEX NWEBP4 543 PROFILE.REG_EMAIL_UIX
INDEX NWEBP4 585 PROFILE.REGISTRATIONS_PK
INDEX NWEBP3 587 PROFILE.REGISTRATIONS_PK
INDEX NWEBP2 589 PROFILE.REGISTRATIONS_PK
TABLE NWEBP2 692 SITE.PERSON_SPLIT_STATS
TABLE NWEBP3 697 SITE.PERSON_SPLIT_STATS
TABLE NWEBP1 718 SITE.PERSON_SPLIT_STATS
TABLE NWEBP3 893 ALFC.NFL_ABSTRACT_CONTENT
TABLE NWEBP4 909 ALFC.NFL_ABSTRACT_CONTENT
TABLE NWEBP3 1087 CONTENT.NFL_DOCBASE_PROPERTIES1_S
TABLE NWEBP2 1377 ALFC.NFL_ABSTRACT_CONTENT
TABLE NWEBP4 1419 CONTENT.NFL_DOCBASE_PROPERTIES1_S
TABLE NWEBP1 1712 ALFC.NFL_ABSTRACT_CONTENT
TABLE NWEBP1 2826 PROFILE.REGISTRATIONS
TABLE NWEBP3 3763 PROFILE.REGISTRATIONS
TABLE NWEBP4 3943 PROFILE.REGISTRATIONS
TABLE NWEBP2 3995 PROFILE.REGISTRATIONS
-------------
sum 76707
ENWEBP1P > list
1 SELECT
2 o.object_type,i.instance_name db,COUNT(*)*8192/1024/1024 meg_in_memory,
3 o.owner||'.'||o.OBJECT_NAME Object_in_Memory
4 FROM DBA_OBJECTS o, gV$BH bh, gv$instance i
5 WHERE o.DATA_OBJECT_ID = bh.OBJD
6 and bh.status<>'free'
7 and bh.inst_id = i.inst_id
8 and o.object_name like upper('%')
9 GROUP BY o.owner||'.'||o.OBJECT_NAME,o.object_type,i.instance_name
10 having count(*)>=128
11* ORDER BY COUNT(*)
You can also look at what is doing the most I/O and these objects might be candidates for caching in memory.
DATE OBJECT_NAME TOTAL_PHYSICAL_READS_TODAY
---------- ------------------------- --------------------------
2013-01-03 PERSON_POSITIONS 239
2013-01-03 PERSON_TEAMS 240
2013-01-03 I_PERSON_TEAMS_2 249
2013-01-03 TICKET_URL 294
2013-01-03 NFL_PERSON 312
2013-01-03 REG_EMAIL_UIX 411
2013-01-03 SKIN_ASSET 483
2013-01-03 REGISTRATIONS_LOWER_USERN 527
2013-01-03 PHOTO_GALLERY_PHOTOS 552
2013-01-03 DEPTH_CHART_UPDATE_STATUS 582
2013-01-03 PK_VIDEO_PERSONS 619
2013-01-03 GAMECENTER_GAME_CONFIG 630
2013-01-03 WRH$_ACTIVE_SESSION_HISTO 637
2013-01-03 NFL_NETWORK 720
2013-01-03 BIG_PLAY 745
2013-01-03 PHOTO_GALLERY 790
2013-01-03 INSTANT_HIGHLIGHT 791
2013-01-03 PK_VIDEO_CHANNELS 1128
2013-01-03 SYS_LOB0000006331C00004$$ 1175
2013-01-03 VIDEO 1538
2013-01-03 CONTENT_TAG 2926
2013-01-03 PLAYEREXTRACT 18234
2013-01-03 TRANSACTIONTABLE 88512
2013-01-03 REGISTRATIONS 309676
--------------------------
sum 433680
ENWEBP1P > list
1 select
2 to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
3 a.object_name,
4 sum(b.PHYSICAL_READS_DELTA) total_physical_reads_today
5 from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
6 where a.object_id=b.OBJ#
7 and b.SNAP_ID >=
8 (select min(snap_id)
9 from sys.wRM$_SNAPSHOT
10 where BEGIN_INTERVAL_TIME >= trunc(sysdate))
11 and upper(a.object_name) like upper('%') and b.PHYSICAL_READS_DELTA>0
12 and c.instance_number=(select instance_number from v$instance)
13 and c.snap_id=b.snap_id
14 group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),a.object_name
15* order by 1,3
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jan 29 15:26:11 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
