To collect CPU stats from Server following code needs to be setup. ( Step By Step )
Step-1
======
Create a user named PERFSTAT/PERFSTAT. This is not for statspack, we only need 3 tables and package for this settings. Give appropriate default TS for same.
Step-2
======
create tables with following definitons:
CREATE TABLE STATS$VMSTAT2
(
START_DATE DATE,
DURATION NUMBER,
SERVER_NAME VARCHAR2(20),
RUNQUE_WAITS NUMBER,
BUSY_WAITS NUMBER,
RUNNABLE_WAITS NUMBER,
SWAP NUMBER,
FREE NUMBER,
PAGE_RECLAIMS NUMBER,
MINOR_FAULTS NUMBER,
PAGE_IN NUMBER,
PAGE_OUT NUMBER,
PAGE_FREE NUMBER,
DESTROYED NUMBER,
SCAN_RATE NUMBER,
DEVICE_INTERRUPT NUMBER,
SYSTEM_CALLS NUMBER,
CONTEXT_SWITCHES NUMBER,
USER_CPU NUMBER,
SYSTEM_CPU NUMBER,
IDLE_CPU NUMBER,
WAIT_CPU NUMBER
)
TABLESPACE CS_DATA
PCTUSED 60
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 5000
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;
CREATE INDEX STATS$VMSTAT_STDTE_I ON STATS$VMSTAT2
(START_DATE)
LOGGING
TABLESPACE CS_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 2M
NEXT 2080K
MINEXTENTS 1
MAXEXTENTS 500
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE TABLE STATS$USERSESSIONS
(
START_DATE DATE,
SYSTEM_SESSIONS NUMBER,
ACTIVE_USERS NUMBER,
INACTIVE_USERS NUMBER,
DEDICATED_SERVERS NUMBER,
USER_SESSIONS NUMBER,
TRANSACTIONS NUMBER
)
TABLESPACE CS_DATA
PCTUSED 60
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;
CREATE INDEX STATS$USERSESSIONS_STDTE_I ON STATS$USERSESSIONS
(START_DATE)
LOGGING
TABLESPACE CS_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 500
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE TABLE STATS$TOTALUSERS
(
MEASURED_DATE DATE,
TOTAL_USERS NUMBER,
ACTIVE_USERS NUMBER,
WEB_USERS NUMBER,
BLOOMORA_USERS NUMBER
)
TABLESPACE CS_DATA
PCTUSED 60
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 500
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;
You can modify this by changing for appropriate tablespace.
Step-3
======
Create Procedure in PERFSTAT user.
CREATE OR REPLACE PROCEDURE PERFSTAT.vmstat3
(START_DATE DATE,
DURATION NUMBER,
SERVER_NAME VARCHAR2,
RUNQUE NUMBER,
BUSY_WAITS NUMBER,
RUNNABLE_WAITS NUMBER,
SWAP NUMBER,
FREE NUMBER,
PAGE_RECLAIMS NUMBER,
MINOR_FAULTS NUMBER,
PAGE_IN NUMBER,
PAGE_OUT NUMBER,
PAGE_FREE NUMBER,
DESTROYED NUMBER,
SCAN_RATE NUMBER,
DEVICE_INTERRUPT NUMBER,
SYSTEM_CALLS NUMBER,
CONTEXT_SWITCHES NUMBER,
USER_CPU NUMBER,
SYSTEM_CPU NUMBER,
IDLE_CPU NUMBER,
WAITS_CPU NUMBER)
AS
active_users number;
total_users number;
web_users number;
bloomora_users number;
BEGIN
INSERT INTO PERFSTAT.STATS$VMSTAT2 VALUES(START_DATE,DURATION,SERVER_NAME,RUNQUE,BUSY_WAITS,RUNNABLE_WAITS, SWAP, FREE, PAGE_RECLAIMS, MINOR_
FAULTS, PAGE_IN, PAGE_OUT, PAGE_FREE, DESTROYED, SCAN_RATE, DEVICE_INTERRUPT, SYSTEM_CALLS, CONTEXT_SWITCHES,
USER_CPU, SYSTEM_CPU, IDLE_CPU,WAITS_CPU);
select count(*) into active_users From v$session where username <> ‘ ‘ and status = ‘ACTIVE’;
select count(*) into total_users From v$session where username <> ‘ ‘ ;
select count(*) into web_users From v$session where username <> ‘ ‘ and status = ‘ACTIVE’ and username like ‘WEB%’;
select count(*) into bloomora_users From v$session where username <> ‘ ‘ and status = ‘ACTIVE’ and username like ‘BLOOMORA%’;
INSERT INTO PERFSTAT.STATS$TOTALUSERS
(measured_date,total_users,active_users,web_users,bloomora_users)
values
(START_DATE,total_users,active_users,web_users,bloomora_users);
INSERT INTO PERFSTAT.STATS$USERSESSIONS select sysdate,sum(decode(type,’BACKGROUND’,1,0)) system_sessions,
sum(decode(type,’BACKGROUND’,0,decode(status,’ACTIVE’,1,0))) active_users,
sum(decode(type,’BACKGROUND’,0,decode(status,’ACTIVE’,0,1))) inactive_users,
sum(decode(type,’BACKGROUND’,0,decode(server,’DEDICATED’,1,0))) dedicated_servers,
sum(decode(type,’BACKGROUND’,0,1)) user_sessions,
sum(decode(taddr,”,0,decode(status,’ACTIVE’,1,0))) transactions
from v$session;
commit;
END;
/
Step-4
======
Now we created package and tables in PERFSTAT Schema. Now,
GRANT execute on PERFSTAT.vmstat3 to OPS$ORACLE.
This is needed as we are executing procedure as oracle os user.
Note: if OPS$ORACLE is not there then need to create this user.
create user ops$ORACLE identified externally;
grant connect to ops$ORALCE;
Step-5
======
Now execute this package using following backgraound job. Actually this is a Shell program named get_vmstat.sh.
$ nohup get_vmstat.sh WEBP 1>$HOME/paresh/tmp/get_vmstat_WEBP.log 2>&1 &
Step-6
======
Periodically you can check following tables for data gathering. U can change the snapshot interval in this get_vmstat.sh script. General rule of thumb is that IDLE CPU should not be less that 35%. Else need to re-consider serious tuning or Capacity level of Server Hardware.