Table has to be created for capturing the CPU Utilization and save it to Database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
CREATE TABLE "SCOTT"."UTILIZATIONS" ("DATE_ID" DATE DEFAULT SYSDATE, "TIME_ID" VARCHAR2(15 BYTE), "CPU" VARCHAR2(5 BYTE), "USER_PCT" NUMBER(4,2), "NICE_PCT" NUMBER(4,2), "SYSTEM_PCT" NUMBER(4,2), "IOWAIT_PCT" NUMBER(4,2), "STEAL_PCT" NUMBER(4,2), "IDLE_PCT" NUMBER(4,2), "INST_NAME" VARCHAR2(10 BYTE) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SCOTT_DATA" ; sar.ctl is the control file for loading the Text file (Utilizations) to Database. cat <strong>/u01/app/oracle/DBA/tasks/sardata/sar.ctl </strong> load data infile '/u01/app/oracle/DBA/tasks/sardata/sar.txt' append into table UTILIZATIONS fields terminated by "," trailing nullcols( DATE_ID DATE "DD-MM-YYYY", TIME_ID, CPU, USER_PCT, NICE_PCT, SYSTEM_PCT, IOWAIT_PCT, STEAL_PCT, IDLE_PCT, INST_NAME CONSTANT ORCL12) $ cat <strong>/u01/app/oracle/DBA/tasks/sardata/sarcollect.sh</strong> cd /u01/app/oracle/DBA/tasks/sardata cd /u01/app/oracle/DBA/tasks/sardata export ORACLE_SID=orcl12 export ORACLE_HOME=/u01/app/oracle/product/12102 export PATH=/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/u01/app/oracle/product/12102/bin export LC_TIME="POSIX" ####(For A Particular Date Use the following Line by multiplying 86400 * No.of days ##################sar -f /var/log/sa/sa19 | grep -v 'Average'|awk '{ print strftime("%d/%m/%Y",systime()-172800), $0; fflush() }' > /u01/app/oracle/DBA/tasks/sardata/sa.txt sar -f /var/log/sa/sa$(date +%d -d yesterday) | grep -v 'Average'|awk '{ print strftime("%d/%m/%Y",systime()-86400), $0; fflush() }' > /u01/app/oracle/DBA/tasks/sardata/sa.txt sed -i -e 1,3d /u01/app/oracle/DBA/tasks/sardata/sa.txt sed -i -e '$d' /u01/app/oracle/DBA/tasks/sardata/sa.txt sed 's/ \{1,\}/,/g' /u01/app/oracle/DBA/tasks/sardata/sa.txt > /u01/app/oracle/DBA/tasks/sardata/sar.txt cp /u01/app/oracle/DBA/tasks/sardata/sar.txt /u01/app/oracle/DBA/tasks/sardata/log/sar`date -dy '+%m%d%y'`.log sqlldr scott/tiger123 control=sar.ctl bindsize=512000 rows=100000 errors=10000 rm sar.txt Query for getting CPU Utilization from Database Query. SELECT DATE_ID,TIME_ID,(100-IDLE_PCT) AS UTILIZED_CPU_PCT FROM SEPHORA.UTILIZATIONS WHERE INST_NAME='ORCL12' ORDER BY 1,2; The same can be used for multi node RAC Database, for different time frames to see how the utilization is. |
Note: Please test scripts in Non Prod before trying in Production.
(35 votes, average: 4.91 out of 5)
Loading...
Vinod
Thanks Layadhar for sharing excellent information….
sai
This is new information which is not know most of DBA’s . thanks for sharing
Swetha
Good Data
Balvant
Awesome.