1、功能
A、获取top前10个Oracle的进程，并打印出(根据实际进程情况列出 R/S 状态)
B、对这些进行进行PS检查资源占有情况，并确定是那个数据库实例的进程
C、根据当前目录下的connect.json配置数据库连接数据库访问，打印执行SQL
2、作用
A、避免手工操作的多个代码输入检查
传统检查操作如下：
X、通过top找出占用资源的Oracle进程
Y、通过ps确定这个Oracle进程所说实例
Z、连接对应的Oracle实例找出执行SQL
B、进行了占用内存大小自动转换，快速查看占有大小是G、M、K
3、将来版本
A、提供Socket侦听，使用telnet直接可以查看
B、提供telnet扩展命令，直接查看性能与语句
C、支持远程进行的直接Kill功能
D、支持SQL直接执行相关请求，并返回结果
E、提供REST外部请求服务功能
[root@fj43 tmp]# ./RunApp.sh
/var/tmp/connect.json
Config loaded...
cpu used:98.0%
11362 oracle 20 0 2231m 485m 482m R 98.4 1.6 2737:26 oracle <----top输出
12780 oracle 20 0 2230m 30m 27m S 1.9 0.1 27:33.12 oracle <----top输出
13673 oracle 20 0 2248m 78m 75m S 1.9 0.3 0:01.29 oracle <----top输出
13980 oracle 20 0 2248m 93m 90m S 1.9 0.3 0:01.55 oracle <----top输出
18135 oracle 20 0 2251m 364m 357m S 1.9 1.2 2:20.23 oracle <----top输出
25677 oracle 20 0 2231m 31m 31m S 1.9 0.1 8:27.59 oracle <----top输出
PROC=11362 PerCPU=99.4 PerMEM=1.6 VMEM=2.0G RMEM=485.0M TIME=2737:26 CMD=[ora_j002_MG01] <----ps输出
PROC=12780 PerCPU=0.0 PerMEM=0.1 VMEM=2.0G RMEM=30.0M TIME=27:33 CMD=[ora_dia0_CSHQ] <----ps输出
PROC=13673 PerCPU=0.1 PerMEM=0.2 VMEM=2.0G RMEM=78.0M TIME=0:01 CMD=[ora_j001_CSGFC] <----ps输出
PROC=13980 PerCPU=0.2 PerMEM=0.3 VMEM=2.0G RMEM=93.0M TIME=0:01 CMD=[ora_j000_CSGFC] <----ps输出
PROC=18135 PerCPU=0.0 PerMEM=1.2 VMEM=2.0G RMEM=364.0M TIME=2:20 CMD=[ora_cjq0_FTFIN] <----ps输出
PROC=25677 PerCPU=0.0 PerMEM=0.1 VMEM=2.0G RMEM=31.0M TIME=8:27 CMD=[ora_pmon_CENTER] <----ps输出
jdbcUrl=jdbc:oracle:thin:@193.0.10.110:1521:CSHQ username=DBUSRBMS password=**** count=1 <----数据库连接
not sql runing <----当前未执行SQL
jdbcUrl=jdbc:oracle:thin:@193.0.10.110:1521:MG01 username=DBUSROMS1 password=**** count=1 <----数据库连接
DELETE FROM SELLMAIL WHERE SMKEY = :B1 <----当前正执行SQL
jdbcUrl=jdbc:oracle:thin:@193.0.10.110:1521:CENTER username=DBUSRDAT password=**** count=1 <----数据库连接
not sql runing <----当前未执行SQL
jdbcUrl=jdbc:oracle:thin:@193.0.10.110:1521:CSGFC username=DBUSRDMS password=**** count=2 <----数据库连接
not sql runing <----当前未执行SQL
not sql runing <----当前未执行SQL
jdbcUrl=jdbc:oracle:thin:@193.0.10.110:1521:FTFIN username=DBUSRFIN password=**** count=1 <----数据库连接
not sql runing <----当前未执行SQL
[root@fj43 tmp]#
改进版输出情况：
[root@dxs1 tmp]# RunApp.sh
/var/tmp/connect.json
Config loaded...
cpu used:99.1%
12041 oracle 25 0 3467m 157m 152m R 99.0 1.0 2:03.69 oracle
12144 oracle 15 0 3463m 95m 93m S 9.9 0.6 0:00.38 oracle
12167 oracle 15 0 3463m 37m 34m S 4.0 0.2 0:00.04 oracle
PROC=12041 PerCPU=87.1 PerMEM=0.9 VMEM=3.0G RMEM=157.0M TIME=2:03 CMD=[oracleMG01 (LOCAL=NO)]
PROC=12144 PerCPU=9.5 PerMEM=0.6 VMEM=3.0G RMEM=96.0M TIME=0:00 CMD=[oracleMG01 (LOCAL=NO)]
PROC=12167 PerCPU=4.0 PerMEM=0.2 VMEM=3.0G RMEM=37.0M TIME=0:00 CMD=[oracleMG01 (LOCAL=NO)]
jdbcUrl=jdbc:oracle:thin:@193.0.0.77:1521:MG01 username=DBUSROMS1 password=**** count=3
SELECT RAWTOHEX(TB.SQL_ADDRESS) SQL_ADDRESS,TB.SQL_HASH_VALUE,TB.SID
FROM v$process TA INNER JOIN v$session TB
ON TA.addr=TB.paddr
WHERE TA.spid='12167'
AND TB.sql_address!='00'
not sql runing
SELECT RAWTOHEX(TB.SQL_ADDRESS) SQL_ADDRESS,TB.SQL_HASH_VALUE,TB.SID
FROM v$process TA INNER JOIN v$session TB
ON TA.addr=TB.paddr
WHERE TA.spid='12041'
AND TB.sql_address!='00'
session id=13
SELECT SQL_TEXT
FROM V$SQLTEXT_WITH_NEWLINES
WHERE ADDRESS = HEXTORAW('000000007C9641F0')
AND HASH_VALUE = 2890562617
ORDER BY PIECE
/* concatenate */
Update goods a Set (goodsid,obligatestr1,goodsflag,fgoodsid,code
number,codegrade)=
(Select goodsid,obligatestr1,goodsflag,fgood
sid,codenumber,codegrade From goods_20130720 b Where a.goodsid=b
.goodsid)
SELECT RAWTOHEX(TB.SQL_ADDRESS) SQL_ADDRESS,TB.SQL_HASH_VALUE,TB.SID
FROM v$process TA INNER JOIN v$session TB
ON TA.addr=TB.paddr
WHERE TA.spid='12144'
AND TB.sql_address!='00'
session id=475
SELECT SQL_TEXT
FROM V$SQLTEXT_WITH_NEWLINES
WHERE ADDRESS = HEXTORAW('000000007C973940')
AND HASH_VALUE = 3246036779
ORDER BY PIECE
/* concatenate */
INSERT INTO "DBUSROTHER"."FJ_SALEDETAIL" ("SDMKTNO","SDCOUNTER"
,"SDBILLNO","SDROWNO","SDPOSNO","SDINVNO","SDDATE","SDSWAPDATE",
"SDSELLER","SDSELLERNM","SDGDID","SDBARCODE","SDGNM","SDUID","SD
UNIT","SDSPEC","SDSALEQNT","SDSALEPRICE","SDSALEAMT","SDSWAPPRIC
E","SDSWAPAMT","SDPOPDISAMT","SDPOPDISPARTAKE","SDPOPBILLNO","SD
CUSTDISAMT","SDGRANTZK","SDGRANTNO","SDSORT","SDANALCODE","SDOLD
DATE","SDOLDPOSNO","SDOLDINVNO","SDRETREASON","SDTAXRATE","SDINP
BARCODE","SDDISTOTAL","SDMULTOTAL","SDKJKCBZ","SDSPML","SDBATCHN
O","SDPROMPRICE") SELECT "A2"."SDMKTNO","A2"."SDCOUNTER",TO_CHAR
("A2"."SDSWAPDATE",'yymmdd')||SUBSTRB("A2"."SDMKTNO",(-3))||SUBS
TRB("A2"."SDPOSNO",(-2))||SUBSTRB(TO_CHAR("A2"."SDINVNO",'000000
00'),(-4)),"A2"."SDROWNO","A2"."SDPOSNO","A2"."SDINVNO","A2"."SD
DATE","A2"."SDSWAPDATE","A2"."SDSELLER","A2"."SDSELLERNM","A2"."
SDGDID","A2"."SDBARCODE","A2"."SDGNM","A2"."SDUID","A2"."SDUNIT"
,"A2"."SDSPEC","A2"."SDSALEQNT","A2"."SDSALEPRICE","A2"."SDSALEA
MT","A2"."SDSWAPPRICE","A2"."SDSWAPAMT","A2"."SDPOPDISAMT","A2".
"SDPOPDISPARTAKE","A2"."SDPOPBILLNO","A2"."SDCUSTDISAMT","A2"."S
DGRANTZK","A2"."SDGRANTNO","A2"."SDSORT","A2"."SDANALCODE","A2".
"SDOLDDATE","A2"."SDOLDPOSNO","A2"."SDOLDINVNO","A2"."SDRETREASO
N","A2"."SDTAXRATE","A2"."SDINPBARCODE","A2"."SDDISTOTAL","A2"."
SDMULTOTAL","A2"."SDKJKCBZ","A2"."SDSPML",DECODE(TRIM("A2"."SDBA
TCHNO"),'N',NULL,"A2"."SDBATCHNO"),"A2"."SDPROMPRICE" FROM "POS"
."SALEDETAIL"@! "A2" WHERE TO_CHAR("A2"."SDSWAPDATE",'yymmdd')||
SUBSTRB("A2"."SDMKTNO",(-3))||SUBSTRB("A2"."SDPOSNO",(-2))||SUBS
TRB(TO_CHAR("A2"."SDINVNO",'00000000'),(-4))='130720002043323'
[root@dxs1 tmp]# RunApp.sh
/var/tmp/connect.json
Config loaded...
cpu used:196.6%
12041 oracle 25 0 3467m 168m 163m R 98.9 1.1 5:10.29 oracle
12287 oracle 19 0 3465m 102m 97m R 98.9 0.6 0:05.46 oracle
PROC=12041 PerCPU=94.3 PerMEM=1.0 VMEM=3.0G RMEM=168.0M TIME=5:10 CMD=[oracleMG01 (LOCAL=NO)]
PROC=12287 PerCPU=91.8 PerMEM=0.6 VMEM=3.0G RMEM=102.0M TIME=0:05 CMD=[ora_j001_MG01]
jdbcUrl=jdbc:oracle:thin:@193.0.0.77:1521:MG01 username=DBUSROMS1 password=**** count=2
SELECT RAWTOHEX(TB.SQL_ADDRESS) SQL_ADDRESS,TB.SQL_HASH_VALUE,TB.SID
FROM v$process TA INNER JOIN v$session TB
ON TA.addr=TB.paddr
WHERE TA.spid='12041'
AND TB.sql_address!='00'
session id=13
SELECT SQL_TEXT
FROM V$SQLTEXT_WITH_NEWLINES
WHERE ADDRESS = HEXTORAW('000000007C9641F0')
AND HASH_VALUE = 2890562617
ORDER BY PIECE
/* concatenate */
Update goods a Set (goodsid,obligatestr1,goodsflag,fgoodsid,code
number,codegrade)=
(Select goodsid,obligatestr1,goodsflag,fgood
sid,codenumber,codegrade From goods_20130720 b Where a.goodsid=b
.goodsid)
SELECT RAWTOHEX(TB.SQL_ADDRESS) SQL_ADDRESS,TB.SQL_HASH_VALUE,TB.SID
FROM v$process TA INNER JOIN v$session TB
ON TA.addr=TB.paddr
WHERE TA.spid='12287'
AND TB.sql_address!='00'
session id=327
SELECT SQL_TEXT
FROM V$SQLTEXT_WITH_NEWLINES
WHERE ADDRESS = HEXTORAW('0000000081A93490')
AND HASH_VALUE = 2457621776
ORDER BY PIECE
/* concatenate */
INSERT INTO SELLPAYMENT SELECT TO_NUMBER(SPBILLNO), SPPAYCODE, S
UM(SPPAYMENTAMT), SPRATE, SPNO, SPPAYERID FROM SALEPAYMENT WHERE
SPBILLNO = :B3 AND SPPOSNO = :B2 AND SPINVNO = :B1 GROUP BY SPB
ILLNO, SPPAYCODE, SPRATE, SPNO, SPPAYERID
[root@dxs1 tmp]#
4820
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
