高性能负载脚本
CREATE TABLE "SYS"."HIGHLOAD_HISTORY"
(
"SAMPLE_TIME" DATE,
"SPID" NUMBER(10,0),
"LOAD" VARCHAR2(7 BYTE),
"SID" VARCHAR2(30 BYTE),
"USERNAME" VARCHAR2(40 BYTE),
"MACHINE" VARCHAR2(64 BYTE),
"PROGRAM" VARCHAR2(48 BYTE),
"SQL_ID" VARCHAR2(13 BYTE),
"SQL_FULLTEXT" CLOB,
"INST_ID" NUMBER(2,0),
"STATUS" VARCHAR2(8 BYTE)
)
ps aux|grep $ORACLE_SID|awk
'{ if($3>=0.3)
print "insert into highload_history02 select
sysdate rec_time,
"$2,",
\47"$3"%\47",",
ss.sid,
ss.username,
ss.machine,
ss.program,
ss.sql_id,
(select sql_fulltext from v$sqlarea sq where sq.sql_id=ss.sql_id),
(select instance_number from v$instance),
ss.status
from v$session ss,v$process pr
where pr.addr=ss.paddr and pr.spid=",$2";"}' | sqlplus / as sysdba
SQL> select * from HIGHLOAD_HISTOR
SAMPLE_TI SPID LOAD SID USERNAME MACHINE PROGRAM SQL_ID SQL_FULLTEXT INST_ID STATUS
--------- ---------- -------- ----- -------- ---------- ------------------------------ ------------- -------------------------------------------------- ---------- --------
10-SEP-14 2230 5.8% 1 SYS Oracle10g sqlplus@Oracle10g (TNS V1-V3) 1 INACTIVE
10-SEP-14 2230 5.6% 1 SYS Oracle10g sqlplus@Oracle10g (TNS V1-V3) 1 INACTIVE
10-SEP-14 2606 0.9% 22 SYS Oracle10g sqlplus@Oracle10g (TNS V1-V3) 1tc94vh92f68b select count(*) from sys.obj$,sys.tab$,sys.col$ 1 ACTIVE
10-SEP-14 2230 5.6% 1 SYS Oracle10g sqlplus@Oracle10g (TNS V1-V3) 1 INACTIVE
10-SEP-14 2606 1.2% 22 SYS Oracle10g sqlplus@Oracle10g (TNS V1-V3) 1tc94vh92f68b select count(*) from sys.obj$,sys.tab$,sys.col$ 1 ACTIVE
[oracle@Oracle10g ~]$ ps aux
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
oracle 2160 0.0 1.8 628244 18924 ? Ss 09:25 0:00 ora_pmon_prod
oracle 2162 0.7 1.5 625996 15508 ? Ss 09:25 1:44 ora_vktm_prod
oracle 2166 0.0 1.5 625996 15628 ? Ss 09:25 0:01 ora_gen0_prod
oracle 2168 0.0 1.5 625996 15456 ? Ss 09:25 0:00 ora_diag_prod
oracle 2170 0.0 2.3 626516 23872 ? Ss 09:25 0:01 ora_dbrm_prod
oracle 2172 0.0 1.5 625996 15688 ? Ss 09:25 0:01 ora_psp0_prod
oracle 2174 0.0 1.8 626508 18972 ? Ss 09:25 0:02 ora_dia0_prod
oracle 2176 0.0 3.2 625996 33432 ? Ss 09:25 0:01 ora_mman_prod
\47"$3"%\47","
反斜杠47双引号美元符3双引号百分号反斜杠47双引号逗号双引号
\47=/
/$3%/,
[oracle@Oracle10g ~]$ ps aux |grep $ORACLE_SID|awk '{ if($3>=0.0) print $3 }'
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
[oracle@Oracle10g ~]$ ps aux |grep $ORACLE_SID|awk '{ if($3>=0.0) print "\47"$3"%\47"}'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.0%'
'0.1%'
'0.0%'
'0.6%'
'0.0%'
还是一步一步看吧
[oracle@Oracle10g ~]$ ps aux
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
root 1 0.0 0.0 10368 736 ? Ss 09:23 0:00 init [5]
oracle 2846 0.0 3.7 631676 38412 ? Ss 10:52 0:03 oracleprod (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))s
[oracle@Oracle10g ~]$ ps aux |grep $ORACLE_SID
oracle 2846 0.0 3.7 631676 38412 ? Ss 10:52 0:02 oracleprod (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
[oracle@Oracle10g ~]$ ps aux |grep $ORACLE_SID|awk '{ if($3>=0.0) print "\47"$3"%\47"}'
'0.0%'
[oracle@Oracle10g ~]$ ps aux |grep $ORACLE_SID|awk '{ if($3>=0.0) print "\47"$4"%\47"}'
'3.7%'
其中第三项 是 CPU使用率 第四项是 内存使用率
