2 Replies Latest reply: Feb 24, 2010 7:22 AM by vittel RSS

    CPU wait

    vittel
      Hi,

      I'm in RAC database (10gR2/Redhat4).

      I need to store the real CPU wait every 1minute in a table for a month.

      For that I thing to query the GV$SYS_TIME_MODEL. As in this view we store the Amount of CPU time I tooth to query the view every (Please see the query).
      It have sens ?
      There is way more simple to do it?

      set serveroutput on
      DECLARE
      CPU_USED1_inst1 NUMBER;
      CPU_USED1_inst2 NUMBER;
      CPU_USED2_inst1 NUMBER;
      CPU_USED2_inst2 NUMBER;
      CPU_USED_inst1 NUMBER;
      CPU_USED_inst2 NUMBER;
      BEGIN
      select VALUE into CPU_USED1_inst1
      from  GV$SYS_TIME_MODEL
      WHERE STAT_NAME='DB CPU'
      AND INST_ID=1;

      select VALUE into CPU_USED1_inst2
      from  GV$SYS_TIME_MODEL
      WHERE STAT_NAME='DB CPU'
      AND INST_ID=2;

      dbms_lock.sleep(30);
      select VALUE into CPU_USED2_inst1
      from  GV$SYS_TIME_MODEL
      WHERE STAT_NAME='DB CPU'
      AND INST_ID=1;

      select VALUE into CPU_USED2_inst2
      from  GV$SYS_TIME_MODEL
      WHERE STAT_NAME='DB CPU'
      AND INST_ID=2;

      select CPU_USED2_inst1-CPU_USED1_inst1 into CPU_USED_inst1
      from dual;
      select CPU_USED2_inst2-CPU_USED1_inst2 into CPU_USED_inst2
      from dual;
      dbms_output.put_line('CPU_USED_inst1 '||CPU_USED_inst1);
      dbms_output.put_line('CPU_USED_inst2 '||CPU_USED_inst2);
      end;
      */*
      Regards and sorry for my poor english.

      Edited by: vittel on Feb 24, 2010 10:51 AM
        • 1. Re: CPU wait
          Charles Hooper
          I think that you may need to clarify what you mean by "real CPU wait". Technically, in Oracle when a session is "on the CPU" it is not in a wait event. The "DB CPU" statistic captures the accumulated time spent on the CPU by foreground processes - user sessions. This time does not include the time that the background processes (DBWR, LGWR, PMON, etc.) spend consuming CPU time. Additionally, the "DB CPU" statistic does not consider/accumulate CPU time consumed by processes that are not related to the database instance.

          I am not saying that this is the right way to do it, but you could do something like this to create a logging table:
          CREATE TABLE SYS_TIME_MODEL_CPU AS
          SELECT
            SYSDATE CHK_ID,
            INST_ID,
            SUM(DECODE(STAT_NAME,'DB CPU',VALUE,NULL)) DB_CPU,
            SUM(DECODE(STAT_NAME,'background cpu time',VALUE,NULL)) BACKGROUND_CPU
          FROM
            GV$SYS_TIME_MODEL
          WHERE
            0=1
          GROUP BY
            INST_ID;
          If you find a way to insert into the table once a minute, you could execute the following SQL statement once a minute:
          INSERT INTO SYS_TIME_MODEL_CPU
          SELECT
            SYSDATE CHK_ID,
            INST_ID,
            SUM(DECODE(STAT_NAME,'DB CPU',VALUE,NULL)) DB_CPU,
            SUM(DECODE(STAT_NAME,'background cpu time',VALUE,NULL)) BACKGROUND_CPU
          FROM
            GV$SYS_TIME_MODEL
          WHERE
            STAT_NAME IN ('DB CPU','background cpu time')
          GROUP BY
            INST_ID;
          DBMS_LOCK.SLEEP on some platforms may not wait exactly the specified number of seconds (it may wait slightly longer). A testing anonymous PL/SQL script might look like this:
          DECLARE
            STime DATE := SYSDATE;
          BEGIN
            WHILE (SYSDATE - STime) < 32 LOOP
              INSERT INTO SYS_TIME_MODEL_CPU
                SELECT
                  SYSDATE CHK_ID,
                  INST_ID,
                  SUM(DECODE(STAT_NAME,'DB CPU',VALUE,NULL)) DB_CPU,
                  SUM(DECODE(STAT_NAME,'background cpu time',VALUE,NULL)) BACKGROUND_CPU
                FROM
                  GV$SYS_TIME_MODEL
                WHERE
                  STAT_NAME IN ('DB CPU','background cpu time')
                GROUP BY
                  INST_ID;
          
                COMMIT;
                DBMS_LOCK.SLEEP(60);
            End Loop;
          End;
          /
          You will notice that the above does not calculate the change in the values, it only records the current values. You could then create a SQL statement like the following to see the delta values:
          SELECT
            TO_CHAR(CHK_ID,'YYYY-MM-DD HH24:MI') CHK_ID,
            INST_ID,
            DB_CPU-LAG(DB_CPU,1) OVER (PARTITION BY INST_ID ORDER BY CHK_ID) DB_CPU,
            BACKGROUND_CPU-LAG(BACKGROUND_CPU,1) OVER (PARTITION BY INST_ID ORDER BY CHK_ID) BACKGROUND_CPU
          FROM
            SYS_TIME_MODEL_CPU
          ORDER BY
            CHK_ID;
           
          CHK_ID              INST_ID     DB_CPU BACKGROUND_CPU
          ---------------- ---------- ---------- --------------
          2010-02-24 07:18          1
          2010-02-24 07:19          1   59990544          66070
          2010-02-24 07:20          1   59951475          66724
          2010-02-24 07:21          1   59985268          71768
          2010-02-24 07:22          1   60000569          63694
          2010-02-24 07:23          1   60002938          71639
          2010-02-24 07:24          1   59978651          63770
          2010-02-24 07:25          1   61487141          62785
          2010-02-24 07:26          1      24194          76990
          Before you begin, confirm what you mean by "real CPU wait".

          Charles Hooper
          Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
          http://hoopercharles.wordpress.com/
          IT Manager/Oracle DBA
          K&M Machine-Fabricating, Inc.
          • 2. Re: CPU wait
            vittel
            Thank you very much for your response.

            What I mean by real CPU wait it's the "*CPU used*" that the EM shows us in the performance graphics.