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 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 4870
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 #571768 is a reply to message #571764] Fri, 30 November 2012 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just query V$DB_CACHE_ADVICE

Regards
Michel
Re: suggestion for increasing the buffer cache [message #571770 is a reply to message #571768] Fri, 30 November 2012 04:01 Go to previous messageGo to next message
savugar
Messages: 33
Registered: February 2012
Member
Hi Mike, below is the buffer cache advice

        ID NAME                 BLOCK_SIZE ADV SIZE_FOR_ESTIMATE SIZE_FACTOR BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
---------- -------------------- ---------- --- ----------------- ----------- -------------------- ------------------------- -------------------
         3 DEFAULT                    8192 ON                 32        .125                 3970                4.3008      9407671281
         3 DEFAULT                    8192 ON                 64         .25                 7940                2.1928      4796606649
         3 DEFAULT                    8192 ON                 96        .375                11910                1.7663      3863704440
         3 DEFAULT                    8192 ON                128          .5                15880                 1.605      3510813429
         3 DEFAULT                    8192 ON                160        .625                19850                1.5234      3332303984
         3 DEFAULT                    8192 ON                192         .75                23820                1.4653      3205174694
         3 DEFAULT                    8192 ON                224        .875                27790                1.2036      2632825753
         3 DEFAULT                    8192 ON                256           1                31760                 1          2187409105
         3 DEFAULT                    8192 ON                288       1.125                35730                 .8224      1798855220
         3 DEFAULT                    8192 ON                320        1.25                39700                 .6893      1507748027
         3 DEFAULT                    8192 ON                352       1.375                43670                 .5966      1304960028
         3 DEFAULT                    8192 ON                384         1.5                47640                 .5217      1141185390
         3 DEFAULT                    8192 ON                416       1.625                51610                 .4633      1013406050
         3 DEFAULT                    8192 ON                448        1.75                55580                 .4147       907084286
         3 DEFAULT                    8192 ON                480       1.875                59550                 .3775       825667041
         3 DEFAULT                    8192 ON                512           2                63520                 .3497       765008931
         3 DEFAULT                    8192 ON                544       2.125                67490                  .322       704408945
         3 DEFAULT                    8192 ON                576        2.25                71460                 .2901       634596390
         3 DEFAULT                    8192 ON                608       2.375                75430                 .2588       566098381
         3 DEFAULT                    8192 ON                640         2.5                79400               .23           503196775
Re: suggestion for increasing the buffer cache [message #571771 is a reply to message #571770] Fri, 30 November 2012 04:03 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 4870
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 #571774 is a reply to message #571770] Fri, 30 November 2012 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The advice view tells that if you increase your cache by 2.5 you decrease the physical reads by 4.
So it is worth to do it... after having done the analysis John pointed you to.

Regards
Michel
Re: suggestion for increasing the buffer cache [message #571775 is a reply to message #571772] Fri, 30 November 2012 04:28 Go to previous messageGo to next message
savugar
Messages: 33
Registered: February 2012
Member
How to check which statement causing this latch free wait?
Re: suggestion for increasing the buffer cache [message #571776 is a reply to message #571775] Fri, 30 November 2012 04:31 Go to previous messageGo to next message
John Watson
Messages: 4870
Registered: January 2010
Location: Global Village
Senior Member
You are still going at the problem (if there is one) from the wrong end. For the third time, what SQLs, if any, are running too slowly?
Re: suggestion for increasing the buffer cache [message #573009 is a reply to message #571776] Wed, 19 December 2012 13:00 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: suggestion for increasing the buffer cache [message #573901 is a reply to message #573891] Fri, 04 January 2013 00:59 Go to previous message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query the buffer cache and even more global cache (gv$bh) has a high performance impact.

Regards
Michel
Previous Topic: SQL Plan Baseline Trouble!!!
Next Topic: CURRENT_OBJ# -1 in v$active_session_history
Goto Forum:
  


Current Time: Sat Dec 27 19:05:26 CST 2014

Total time taken to generate the page: 0.08467 seconds