文件名称：获取top前10个Oracle的进程(支持多实例)
文件大小：1.65MB
文件格式：RAR
更新时间：2016-08-11 04:07:02
Oracle top
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]#
【文件预览】：
monitor.json
connect.json
Readme.txt
CpuUsage.jar
RunApp.cmd