1 2 3 Previous Next 33 Replies Latest reply: Jul 10, 2007 1:40 AM by 477483 Go to original post RSS
      • 15. Re: performance issue - buffer cache hit ratio below 50%
        563198
        I feel you should give more details like

        1. What type of Application are you using. DSS or OLTP.
        2. What is your OS and Memory available on OS and allocated to Oracle.
        3. SGA Size.
        4. Are you using KEEP, RECYCLE buffer pools
        5. Are you getting any wait time on DB_FILE_SEQUENTIAL_READ or DB_FILE_SCATTER_READ.
        6. How may CPU do you have?
        7. What is LRU latches parameter.

        The effectiveness of a buffer cache is measured in terms of the Cache Hit Ratio or the Buffer's Hit Ratio, defined as the percentage of times a desired data block was found in the buffer cache.

        You can calculate the buffer hit ratio from the statistics available in the V$SYSSTAT dynamic performance view as follows:
        Cache hit ratio = 100*(1- 'physical reads'/ 'Logical reads')
        Logical reads is further derived as the following:
        Logical Reads = 'db block gets' + 'consistent gets'

        These statistics from the V$SYSSTAT view give you the combined statistics for the buffer cache as a whole. If you've defined KEEP and RECYCLE buffer pools, it's strongly recommended that you evaluate the hit ratios for them separately.

        The V$BUFFER_POOL dynamic performance view gives physical reads, block gets, and consistent gets statistics for each buffer pool.

        select 100*(1 - (select value from v$sysstat where name = 'db block gets')/(select sum(value) from v$sysstat where name in ('db block gets', 'consistent gets'))) "cache hit ratio" from dual;

        select ((sum(decode(name, 'consistent gets', Value , 0))+ sum(decode(name, 'db block gets', value ,0)) - sum(decode(name, 'physical reads', value ,0))) / (sum(decode(name, 'consistent gets', value ,0)) + sum(decode(name, 'db block gets', value ,0))) *100) "Hit Ratio" from v$sysstat

        After this try increase your database buffers and see ratio. If there is no siginificant change, tune you application.
        • 16. Re: performance issue - buffer cache hit ratio below 50%
          user480060
          Application is OLTP
          OS - LINUX
          SGA DETAILS
          ----------------------
          buffer cache size = 32 mb
          shared pool = 76 mb
          redolog buffer - 1.25 mb
          lib cache = 4.37 mb
          data dict. cache = 4.08
          sql area size= 4.95
          CPu's = 2


          Now what to do?
          • 17. Re: performance issue - buffer cache hit ratio below 50%
            108476
            Now what to do?
            Read and follow the instructions that everyone sent you . . . .
            • 18. Re: performance issue - buffer cache hit ratio below 50%
              Laurent Schneider
              Application is OLTP
              OS - LINUX
              SGA DETAILS
              ----------------------
              buffer cache size = 32 mb
              shared pool = 76 mb
              redolog buffer - 1.25 mb
              lib cache = 4.37 mb
              data dict. cache = 4.08
              sql area size= 4.95
              CPu's = 2


              Now what to do?
              add more memory! such a small configuration hardly make sense for something else than your pocket calculator !

              well, your sga is really small... why not trying to put buffercache=100Mb and sharedpool=100mb and see if it performs better?
              • 19. Re: performance issue - buffer cache hit ratio below 50%
                user480060
                Dear burleson,

                your intructions are really good but it takes a lot of time to read and then do the stuff.

                Anyway thanks to all. I have solved the preformance of the database. Now the cpu usage is not much, is normal and buffer hit ratio is 96%.

                Sometimes when working onsite at the client, you need to do the stuff a little faster and not go into reading some docs or something like that.

                Anyway, once again thanks to all and sorry for silly questions
                regards
                SL

                null
                • 20. Re: performance issue - buffer cache hit ratio below 50%
                  jgarry
                  So, what changes did you make, and why?
                  • 21. Re: performance issue - buffer cache hit ratio below 50%
                    3520
                    Are your users also happy or just you because of ratio? :)

                    Gints Plivna
                    http://www.gplivna.eu
                    • 22. Re: performance issue - buffer cache hit ratio below 50%
                      108476
                      Hi SL,
                      but it takes a lot of time to read and then do the stuff.
                      Yes, but I was not being "mean" by not telling you the answer; I wanted you to learn and understand it yourself!

                      Hopefully, it makes sense to you now.

                      Finally, run a STATSPACK report through http://www.statspackanalyzer.com and see if you have chosen an optimal cache size. . . .
                      • 23. Re: performance issue - buffer cache hit ratio below 50%
                        William Robertson
                        > your intructions are really good but it takes a lot of time to read and then do the stuff.
                        ...
                        Sometimes when working onsite at the client, you need to do the stuff a little faster and not go into reading some docs or something like that.

                        Reading up on the subject makes you a more effective consultant. Presumably your clients hope you are an expert on this stuff.
                        • 24. Re: performance issue - buffer cache hit ratio below 50%
                          Mohammed Taj
                          Hi William,

                          Reading up on the subject makes you a more effective consultant. Presumably your clients hope you are an expert on this stuff.

                          Nice comment !!!

                          regards
                          Taj
                          • 25. Re: performance issue - buffer cache hit ratio below 50%
                            35818
                            As a general rule of thumb I set db_cahce_size to 10% of physical data size then I monitor v$bh to see how many free buffers remain at peak activity. If there are no free buffers shown in v$bh then you need to up the size of db)chace_size, if thre are always free buffers you can reduce it.

                            select status,count(*) number_buffers from v$bh group by status;

                            Note that the above select assumes you only have the default buffer defined, if you have other caches (2, 4, 8, 16, 32K) then it becomes a bit more complex.

                            select
                            inst_id,
                            '32k '||status as status,
                            count(*) as num
                            from
                            gv$bh
                            where file# in(
                            select file_id
                            from dba_data_files
                            where tablespace_name in (
                            select tablespace_name
                            from dba_tablespaces
                            where block_size=32768))
                            group by inst_id,'32k '||status
                            union
                            select
                            inst_id,
                            '16k '||status as status,
                            count(*) as num
                            from
                            gv$bh
                            where
                            file# in(
                            select file_id
                            from dba_data_files
                            where tablespace_name in (
                            select tablespace_name
                            from dba_tablespaces
                            where block_size=16384))
                            group by inst_id,'16k '||status
                            union
                            select
                            inst_id,
                            '8k '||status as status,
                            count(*) as num
                            from
                            gv$bh
                            where
                            file# in(
                            select file_id
                            from dba_data_files
                            where tablespace_name in (
                            select tablespace_name
                            from dba_tablespaces
                            where block_size=8192))
                            group by inst_id,'8k '||status
                            union
                            select
                            inst_id,
                            '4k '||status as status,
                            count(*) as num
                            from
                            gv$bh
                            where
                            file# in(
                            select file_id
                            from dba_data_files
                            where tablespace_name in (
                            select tablespace_name
                            from dba_tablespaces
                            where block_size=4096))
                            group by inst_id,'4k '||status
                            union
                            select
                            inst_id,
                            '2k '||status as status,
                            count(*) as num
                            from
                            gv$bh
                            where
                            file# in(
                            select file_id
                            from dba_data_files
                            where tablespace_name in (
                            select tablespace_name
                            from dba_tablespaces
                            where block_size=2048))
                            group by inst_id,'2k '||status
                            union
                            select
                            inst_id,
                            'free '||status,
                            count(*) as num
                            from
                            gv$bh
                            where status='free'
                            group by inst_id,'free '||status
                            order by 1,2
                            /

                            Mike
                            • 26. Re: performance issue - buffer cache hit ratio below 50%
                              94799
                              Outer join seems rather more efficient, did I miss something?
                              Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
                              
                              SQL> SELECT bh.inst_id, dt.block_size / 1024 block_size_kb, bh.status, COUNT (*)
                                2  FROM   gv$bh bh, dba_data_files ddf, dba_tablespaces dt
                                3  WHERE  dt.tablespace_name(+) = ddf.tablespace_name
                                4  AND    ddf.file_id(+) = bh.file#
                                5  GROUP BY bh.inst_id, dt.block_size, bh.status;
                              
                                 INST_ID BLOCK_SIZE_KB STATUS    COUNT(*)
                              ---------- ------------- ------- ----------
                                       1             8 xcur          8195
                                       1             8 cr              97
                              
                              SQL>
                              • 27. Re: performance issue - buffer cache hit ratio below 50%
                                APC
                                Reading up on the subject makes you a more effective consultant. Presumably
                                your clients hope you are an expert on this stuff.
                                Nah. Everybody knows that clients pay most for the consultants who wear the nicest suits. (disclosure: I work for a consultancy)

                                Cheers, APC
                                • 28. Re: performance issue - buffer cache hit ratio below 50%
                                  SomeoneElse
                                  Everybody knows that clients pay most for the consultants who wear the nicest suits.
                                  True.

                                  I think Scott Adams (of Dilbert fame) wrote "it's not you that gets promoted, it's your clothing".
                                  • 29. Re: performance issue - buffer cache hit ratio below 50%
                                    108476
                                    Hi APC,
                                    Everybody knows that clients pay most for the consultants who wear the nicest suits.
                                    Yes, it's the same here too, dude:

                                    http://www.dba-oracle.com/dress_code.htm