1 2 Previous Next 24 Replies Latest reply: Apr 13, 2013 1:48 AM by Justin_Mungal RSS

    Re: Buffer hit ratio

    1001554
      Someone please help... I am trying to use the below code within a stored procedure to calculate the buffer cache hit ratio and getting the following error


      Warning: execution completed with warning
      PROCEDURE BUFFERCACHEHITRATIO Compiled.
      9/22 PL/SQL: ORA-00942: table or view does not exist



      SET SERVEROUTPUT ON
      CREATE OR REPLACE PROCEDURE BUFFERCACHEHITRATIO
      AS
      CHECKRATIO NUMBER;
      BEGIN

      SELECT ROUND(((1-(SUM(DECODE(NAME, 'physical reads', value, 0)) /
      (SUM(DECODE(NAME, 'db block gets', value, 0))+
      (SUM(DECODE(NAME, 'CONSISTENT GETS', value, 0))))))*100), 2) || '%' BCHR
      INTO CHECKRATIO FROM V$SYSSTAT;

      IF(CHECKRATIO < .95)
      THEN
      dbms_output.put_line('CACHE HIT RATIO IS LOW ' + CHECKRATIO);
      END IF;
      END BUFFERCACHEHITRATIO;
      /
        • 1. Re: Buffer hit ratio
          sb92075
          998551 wrote:
          Someone please help... I am trying to use the below code within a stored procedure to calculate the buffer cache hit ratio and getting the following error


          Warning: execution completed with warning
          PROCEDURE BUFFERCACHEHITRATIO Compiled.
          9/22 PL/SQL: ORA-00942: table or view does not exist



          SET SERVEROUTPUT ON
          CREATE OR REPLACE PROCEDURE BUFFERCACHEHITRATIO
          AS
          CHECKRATIO NUMBER;
          BEGIN

          SELECT ROUND(((1-(SUM(DECODE(NAME, 'physical reads', value, 0)) /
          (SUM(DECODE(NAME, 'db block gets', value, 0))+
          (SUM(DECODE(NAME, 'CONSISTENT GETS', value, 0))))))*100), 2) || '%' BCHR
          INTO CHECKRATIO FROM V$SYSSTAT;

          IF(CHECKRATIO < .95)
          THEN
          dbms_output.put_line('CACHE HIT RATIO IS LOW ' + CHECKRATIO);
          END IF;
          END BUFFERCACHEHITRATIO;
          /
          >
          Someone please help... I am trying to use the below code within a stored procedure to calculate the buffer cache hit ratio and getting the following error


          Warning: execution completed with warning
          PROCEDURE BUFFERCACHEHITRATIO Compiled.
          9/22 PL/SQL: ORA-00942: table or view does not exist



          SET SERVEROUTPUT ON
          CREATE OR REPLACE PROCEDURE BUFFERCACHEHITRATIO
          AS
          CHECKRATIO NUMBER;
          BEGIN

          SELECT ROUND(((1-(SUM(DECODE(NAME, 'physical reads', value, 0)) /
          (SUM(DECODE(NAME, 'db block gets', value, 0))+
          (SUM(DECODE(NAME, 'CONSISTENT GETS', value, 0))))))*100), 2) || '%' BCHR
          INTO CHECKRATIO FROM V$SYSSTAT;

          IF(CHECKRATIO < .95)
          THEN
          dbms_output.put_line('CACHE HIT RATIO IS LOW ' + CHECKRATIO);
          END IF;
          END BUFFERCACHEHITRATIO;
          /
          1) do not hijack somebody else's thread
          2)How do I ask a question on the forums?
          SQL and PL/SQL FAQ

          3) BCHR is a 100% & TOTALLY MEANINGLESS indicator of performance.
          • 2. Re: Buffer hit ratio
            1001554
            So is the procedure working when you tried it? As it is not. Its still saying table or view does not exits

            Edited by: 998551 on Apr 7, 2013 1:30 PM
            • 3. Re: Buffer hit ratio
              sb92075
              998551 wrote:
              So is the procedure working when you tried it? As it is not. Its still saying table or view does not exits
                1  SELECT ROUND(((1-(SUM(DECODE(NAME, 'physical reads', value, 0)) /
                2  (SUM(DECODE(NAME, 'db block gets', value, 0))+
                3  (SUM(DECODE(NAME, 'CONSISTENT GETS', value, 0))))))*100), 2) || '%' BCHR
                4* FROM V$SYSSTAT
              SQL> /
              
              BCHR
              -----------------------------------------
              91.75%
              
              SQL> 
              • 4. Re: Buffer hit ratio
                1001554
                I get it whenever it is not is the procedure but how i want to use it is within a procedure. What is wrong why it is not recognizing the table from within the procedure?
                • 5. Re: Buffer hit ratio
                  sb92075
                  998551 wrote:
                  I get it whenever it is not is the procedure but how i want to use it is within a procedure. What is wrong why it is not recognizing the table from within the procedure?
                  privileges acquired via ROLE do NOT apply within named PL/SQL procedure.
                  direct GRANT (like below) is required.


                  GRANT SELECT ON V_$SYSSTAT TO USER_A;
                  • 6. Re: Buffer hit ratio
                    1001554
                    o.k. because i tried it outside of the procedure and the result is:

                    BUFFERCACHEHITRATIO
                    -----------------------------------------
                    51.09%

                    would this be considered a good or bad hit?

                    I however want to use it within the procedure so i will be able to put it in a package.
                    • 7. Re: Buffer hit ratio
                      sb92075
                      998551 wrote:
                      o.k. because i tried it outside of the procedure and the result is:

                      BUFFERCACHEHITRATIO
                      -----------------------------------------
                      51.09%

                      would this be considered a good or bad hit?
                      BCHR is a 100% & TOTALLY MEANINGLESS indicator of performance.
                      BCHR is a 100% & TOTALLY MEANINGLESS indicator of performance.
                      BCHR is a 100% & TOTALLY MEANINGLESS indicator of performance.
                      BCHR is a 100% & TOTALLY MEANINGLESS indicator of performance.
                      BCHR is a 100% & TOTALLY MEANINGLESS indicator of performance.
                      • 8. Re: Buffer hit ratio
                        1001554
                        What do you mean by:
                        BCHR is a 100% & TOTALLY MEANINGLESS indicator of performance.
                        • 9. Re: Buffer hit ratio
                          sybrand_b
                          Please read the thread you hijacked, especially the contribution by Mark D Powell. It contains the answer.

                          ----------------
                          Sybrand Bakker
                          Senior Oracle DBA
                          • 10. Re: Buffer hit ratio
                            1001554
                            Thank you.

                            I still don't know why when i place the script within a procedure its saying table or view don't exist. Can you explain? please.

                            Edited by: 998551 on Apr 7, 2013 3:33 PM
                            • 11. Re: Buffer hit ratio
                              sb92075
                              998551 wrote:
                              Thank you.

                              I still don't know why when i place the script within a procedure its saying table or view don't exist. Can you explain? please.

                              Edited by: 998551 on Apr 7, 2013 3:33 PM
                              You can lead some folks to knowledge; but you can't make them think!


                              privileges acquired via ROLE do NOT apply within named PL/SQL procedure.
                              direct GRANT (like below) is required.


                              GRANT SELECT ON V_$SYSSTAT TO USER_A;
                              • 12. Re: Buffer hit ratio
                                1001554
                                Ok. Thank you. I did see that and tried it. and tried it


                                SQL> grant select on v$sysstat to PharmacyDBAdmin;
                                grant select on v$sysstat to PharmacyDBAdmin
                                *
                                ERROR at line 1:
                                ORA-01031: insufficient privileges.

                                this was done in the system user.


                                get this other error after trying it elsewhere :-Can only select from fixed tables/views

                                Edited by: 998551 on Apr 7, 2013 3:52 PM
                                • 13. Re: Buffer hit ratio
                                  sb92075
                                  998551 wrote:
                                  Ok. Thank you. I did see that and tried it. and tried it


                                  SQL> grant select on v$sysstat to PharmacyDBAdmin;
                                  grant select on v$sysstat to PharmacyDBAdmin
                                  *
                                  ERROR at line 1:
                                  ORA-01031: insufficient privileges.

                                  this was done in the system user.
                                  It appears that COPY & PASTE are broken for you; since that is not what I posted.
                                    1* select owner from dba_objects where object_name = 'V_$SYSSTAT'
                                  SQL> /
                                  
                                  OWNER
                                  ------------------------------
                                  SYS
                                  Besides the view is owned by SYS; not SYSTEM.
                                  • 14. Re: Buffer hit ratio
                                    sybrand_b
                                    sb92075 already posted the correct statement in this thread two times.
                                    Also, as SYS is the owner, and not SYSTEM, you need SYS as SYSDBA not SYSTEM.

                                    ----------
                                    Sybrand Bakker
                                    Senior Oracle DBA
                                    1 2 Previous Next