Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2016
    Posts
    2

    Unanswered: buffer_cache_hit

    Hi,
    please help with buffer_cache_hit, how can I improve it?

    DB1:
    Code:
    SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
          FROM v$sysstat cur, v$sysstat con, v$sysstat phy
         WHERE cur.name = 'db block gets'
           AND con.name = 'consistent gets'
           AND phy.name = 'physical reads'
        /
    
    76,91
    Code:
                                  Estd Phys        Estd Phys
      Cache Size (m)      Buffers Read Factor            Reads
    ---------------- ------------ ----------- ----------------
                  32        3,992        1.05    1,548,778,510
                  64        7,984        1.04    1,539,346,433
                  96       11,976        1.02    1,510,914,406
                 128       15,968        1.01    1,500,279,639
                 160       19,960        1.01    1,487,484,614
                 192       23,952        1.00    1,481,061,443
                 224       27,944        1.00    1,480,009,921
                 256       31,936        1.00    1,479,009,857
                 288       35,928        1.00    1,478,861,418
                 320       39,920        1.00    1,478,762,774
                 352       43,912        1.00    1,478,660,805
                 356       44,411        1.00    1,478,651,429
                 384       47,904        1.00    1,478,585,777
                 416       51,896        1.00    1,478,517,388
                 448       55,888        1.00    1,478,463,291
                 480       59,880        1.00    1,478,420,126
                 512       63,872        1.00    1,478,378,701
                 544       67,864        1.00    1,474,430,137
                 576       71,856         .99    1,465,027,334
                 608       75,848         .97    1,434,253,265
     
                                    Estd Phys        Estd Phys
      Cache Size (m)      Buffers Read Factor            Reads
    ---------------- ------------ ----------- ----------------
                 640       79,840         .89    1,315,817,922
     
    21 rows selected.


    DB2:
    Code:
    SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
          FROM v$sysstat cur, v$sysstat con, v$sysstat phy
         WHERE cur.name = 'db block gets'
           AND con.name = 'consistent gets'
           AND phy.name = 'physical reads'
        /
    
    16,19
    Code:
                                   Estd Phys        Estd Phys
      Cache Size (m)      Buffers Read Factor            Reads
    ---------------- ------------ ----------- ----------------
                  48        5,988        1.00   20,033,894,142
                  96       11,976        1.00   20,028,017,503
                 144       17,964        1.00   20,025,222,347
                 192       23,952        1.00   20,023,271,133
                 240       29,940        1.00   20,021,833,471
                 288       35,928        1.00   20,020,639,703
                 336       41,916        1.00   20,019,488,451
                 384       47,904        1.00   20,018,511,059
                 432       53,892        1.00   20,017,577,617
                 480       59,880        1.00   20,016,869,222
                 492       61,377        1.00   20,016,721,378
                 528       65,868        1.00   20,016,277,804
                576       71,856        1.00   20,015,765,423
                 624       77,844        1.00   20,015,318,361
                 672       83,832        1.00   20,014,891,455
                 720       89,820        1.00   20,014,401,215
                 768       95,808        1.00   20,013,921,811
                 816      101,796        1.00   20,013,321,969
                 864      107,784        1.00   20,012,768,739
                 912      113,772         .99   19,806,949,405
     
                                    Estd Phys        Estd Phys
      Cache Size (m)      Buffers Read Factor            Reads
    ---------------- ------------ ----------- ----------------
                 960      119,760         .96   19,270,591,135
     
    21 rows selected.
    Click image for larger version. 

Name:	db20.jpg 
Views:	0 
Size:	26.9 KB 
ID:	17042Click image for larger version. 

Name:	db211.jpg 
Views:	0 
Size:	36.1 KB 
ID:	17043Click image for larger version. 

Name:	db212.jpg 
Views:	0 
Size:	24.5 KB 
ID:	17044

    DB3:
    Code:
    SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
          FROM v$sysstat cur, v$sysstat con, v$sysstat phy
         WHERE cur.name = 'db block gets'
           AND con.name = 'consistent gets'
           AND phy.name = 'physical reads'
        /
    
    67,26
    Code:
         Estd Phys    Estd Phys
    Cache Size (MB)      Buffers Read Factor        Reads
    ---------------- ------------ ----------- ------------
                 560       69,265        1.02   ##########
               1,120      138,530        1.02   ##########
               1,680      207,795        1.01   ##########
               2,240      277,060        1.00   ##########
               2,800      346,325        1.00   ##########
               3,360      415,590        1.00   ##########
               3,920      484,855        1.00   ##########
               4,480      554,120        1.00   ##########
               5,040      623,385        1.00   ##########
               5,600      692,650        1.00   ##########
               5,744      710,461        1.00   ##########
               6,160      761,915        1.00   ##########
               6,720      831,180        1.00   ##########
               7,280      900,445        1.00   ##########
               7,840      969,710        1.00   ##########
               8,400    1,038,975        1.00   ##########
              8,960    1,108,240         .99   ##########
               9,520    1,177,505         .98   ##########
              10,080    1,246,770         .98   ##########
              10,640    1,316,035         .97   ##########
     
                                    Estd Phys    Estd Phys
    Cache Size (MB)      Buffers Read Factor        Reads
    ---------------- ------------ ----------- ------------
              11,200    1,385,300         .96   ##########
     
    21 rows selected.
    Click image for larger version. 

Name:	db3.jpg 
Views:	0 
Size:	33.2 KB 
ID:	17045Click image for larger version. 

Name:	db31.jpg 
Views:	0 
Size:	18.8 KB 
ID:	17046

    DB4:
    Code:
    SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
          FROM v$sysstat cur, v$sysstat con, v$sysstat phy
         WHERE cur.name = 'db block gets'
           AND con.name = 'consistent gets'
           AND phy.name = 'physical reads'
        /
    
    68,71
    Code:
                                Estd Phys        Estd Phys
      Cache Size (m)      Buffers Read Factor            Reads
    ---------------- ------------ ----------- ----------------
                  48        6,006        2.63    8,208,942,975
                  96       12,012        2.25    7,028,766,665
                 144       18,018        1.94    6,062,692,522
                 192       24,024        1.66    5,168,957,747
                 240       30,030        1.39    4,319,696,565
                 288       36,036        1.21    3,765,563,450
                 336       42,042        1.12    3,485,335,321
                 384       48,048        1.06    3,307,320,022
                 432       54,054        1.02    3,174,896,684
                 456       57,057        1.00    3,118,989,607
                 480       60,060         .98    3,066,534,683
                 528       66,066         .95    2,958,570,488
                 576       72,072         .92    2,857,382,962
                 624       78,078         .89    2,769,846,608
                 672       84,084         .86    2,692,653,991
                 720       90,090         .84    2,623,265,745
                 768       96,096         .82    2,559,950,432
                 816      102,102         .80    2,503,195,465
                 864      108,108         .79    2,451,650,440
                 912      114,114         .77    2,398,114,751
     
                                    Estd Phys        Estd Phys
      Cache Size (m)      Buffers Read Factor            Reads
    ---------------- ------------ ----------- ----------------
                 960      120,120         .74    2,318,323,751
     
    21 rows selected.
    Click image for larger version. 

Name:	db4.jpg 
Views:	0 
Size:	57.7 KB 
ID:	17040Click image for larger version. 

Name:	db41.jpg 
Views:	0 
Size:	19.0 KB 
ID:	17041

    DB5:
    Code:
    SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
          FROM v$sysstat cur, v$sysstat con, v$sysstat phy
         WHERE cur.name = 'db block gets'
           AND con.name = 'consistent gets'
           AND phy.name = 'physical reads'
        /
    
    7,81
    Click image for larger version. 

Name:	db5.jpg 
Views:	0 
Size:	50.0 KB 
ID:	17037Click image for larger version. 

Name:	db51.jpg 
Views:	0 
Size:	22.8 KB 
ID:	17038Click image for larger version. 

Name:	db52.jpg 
Views:	0 
Size:	21.1 KB 
ID:	17039

    Thanks for any sugestions.

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Without knowing what your database does (is it OLTP? Datawarehouse? DSS? etc) it's impossible for us to guess what you're trying to achieve with your database's overall performance. Bear in mind that tuning one thing doesn't neccesarily make everything work better and may lead to problems elsewhere. I suggest you read up on performance tuning, then you will have a better idea of why you want to tune your database & what bits of it need tuning to best suit your needs (and more importantly, how to check that you've not made things worse!).
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •