我眼中的linux命令用于oracle的问题诊断——TOP命令

接触 linux 的人对于 top 命令可能不会陌生(不同系统名字可能不一样,如 IBM 的 aix 中叫 topas ),它的作用主要用来监控系统实时负载率、进程的资源占用率及其它各项系统状态属性是否正常,如下,便是一个比较繁忙的oracle生产系统的TOP截取

top - 23:10:16 up 51 days, 16 min,  1 user,  load average: 7.98, 9.34, 11.26
Tasks: 1020 total,  35 running, 984 sleeping,   0 stopped,   1 zombie
Cpu(s): 92.3%us,  0.7%sy,  0.0%ni,  6.9%id,  0.1%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  132139384k total, 131282624k used,   856760k free,   514032k buffers
Swap: 20479992k total,        0k used, 20479992k free, 75053592k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                        
61041 oracle    20   0 48.2g  21m  18m R 76.6  0.0   0:11.50 oracle                         
61105 oracle    20   0 48.2g  19m  17m S 75.6  0.0   0:11.26 oracle                         
61049 oracle    20   0 48.2g  21m  18m S 75.3  0.0   0:11.44 oracle                         
61045 oracle    20   0 48.2g  21m  18m R 74.6  0.0   0:11.49 oracle                         
61103 oracle    20   0 48.2g  21m  18m R 74.6  0.0   0:11.28 oracle                         
61109 oracle    20   0 48.2g  19m  17m R 74.3  0.0   0:11.13 oracle                         
61067 oracle    20   0 48.2g  21m  18m R 73.7  0.0   0:11.38 oracle                         
58694 oracle    20   0 48.2g  21m  18m R 73.4  0.0   2:01.71 oracle                         
61059 oracle    20   0 48.2g  21m  18m R 73.0  0.0   0:11.55 oracle                         
61133 oracle    20   0 48.2g  19m  17m R 73.0  0.0   0:10.65 oracle                         
61065 oracle    20   0 48.2g  21m  18m S 72.4  0.0   0:11.67 oracle                         
61083 oracle    20   0 48.2g  21m  18m R 72.4  0.0   0:11.00 oracle                         
61121 oracle    20   0 48.2g  21m  18m S 72.4  0.0   0:11.17 oracle                         
61095 oracle    20   0 48.2g  21m  18m S 72.1  0.0   0:11.25 oracle                         
61057 oracle    20   0 48.2g  21m  18m R 71.7  0.0   0:11.55 oracle                         
61101 oracle    20   0 48.2g  20m  18m R 71.7  0.0   0:11.26 oracle                         
61063 oracle    20   0 48.2g  21m  18m R 71.4  0.0   0:11.13 oracle                         
61073 oracle    20   0 48.2g  21m  18m R 71.4  0.0   0:11.67 oracle                         
61131 oracle    20   0 48.2g  21m  18m R 71.1  0.0   0:11.18 oracle                         
61141 oracle    20   0 48.2g  19m  17m R 70.8  0.0   0:10.89 oracle                         
58840 oracle    20   0 48.2g  22m  18m R 70.5  0.0   0:05.06 oracle                         
61061 oracle    20   0 48.2g  21m  18m R 70.5  0.0   0:11.49 oracle                         
61069 oracle    20   0 48.2g  20m  17m R 70.1  0.0   0:11.48 oracle                         
61081 oracle    20   0 48.2g  21m  18m R 69.5  0.0   0:11.32 oracle                         
61119 oracle    20   0 48.2g  21m  18m R 69.2  0.0   0:11.14 oracle                         
61055 oracle    20   0 48.2g  21m  18m R 68.9  0.0   0:11.19 oracle                         
61051 oracle    20   0 48.2g  21m  18m S 68.2  0.0   0:11.37 oracle                         
61053 oracle    20   0 48.2g  21m  18m R 68.2  0.0   0:11.49 oracle                         
61099 oracle    20   0 48.2g  19m  17m R 68.2  0.0   0:10.98 oracle                         
61079 oracle    20   0 48.2g  21m  18m R 67.9  0.0   0:11.37 oracle                         
61113 oracle    20   0 48.2g  19m  17m S 67.9  0.0   0:10.83 oracle                         
61089 oracle    20   0 48.2g  19m  17m R 67.6  0.0   0:11.22 oracle                         
61097 oracle    20   0 48.2g  19m  17m R 67.6  0.0   0:11.41 oracle                         
61115 oracle    20   0 48.2g  21m  18m R 67.2  0.0   0:11.19 oracle                         
61085 oracle    20   0 48.2g  19m  17m R 66.9  0.0   0:11.12 oracle                         
61125 oracle    20   0 48.2g  21m  18m R 66.9  0.0   0:10.87 oracle                         
58678 oracle    20   0 48.2g  21m  18m S 65.3  0.0   1:58.10 oracle                         
61043 oracle    20   0 48.2g  21m  18m R 61.5  0.0   0:11.27 oracle                         
58680 oracle    20   0 48.2g  21m  18m R 57.3  0.0   1:53.57 oracle                         
61047 oracle    20   0 48.2g  21m  18m R 56.3  0.0   0:11.15 oracle                         
58668 oracle    20   0 48.2g  21m  18m R 53.7  0.0   2:01.78 oracle                         
58842 oracle    20   0 48.2g  22m  18m S 30.9  0.0   0:01.04 oracle 

top - 23:10:16                           
当前时间
up 51 days, 16 min                       
总的运行时间
1 user                                   
当前登录用户数
load average: 7.98, 9.34, 11.26          
系统的负载,即任务队列的平均长度。三个数值分别为 1分钟、5分钟、15分钟前到现在的平均值。
注意:这三个值可以用来判定系统是否负载过高——如果值持续大于系统 cpu 个数,就需要优化你的程序或者架构了。

Tasks: 29 total 进程总数
1 running 正在运行的进程数
28 sleeping 睡眠的进程数
0 stopped 停止的进程数
0 zombie 僵尸进程数
Cpu(s): 0.3% us 用户空间占用CPU百分比
1.0% sy 内核空间占用CPU百分比
0.0% ni 用户进程空间内改变过优先级的进程占用CPU百分比
98.7% id 空闲CPU百分比
0.0% wa 等待输入输出的CPU时间百分比
0.0% hi Hardware IRQ
0.0% si Software IRQ

Mem: 191272k total 物理内存总量
173656k used 使用的物理内存总量
17616k free 空闲内存总量
22052k buffers 用作内核缓存的内存量
Swap: 192772k total 交换区总量
0k used 使用的交换区总量
192772k free 空闲交换区总量
123988k cached 缓冲的交换区总量。
内存中的内容被换出到交换区,而后又被换入到内存,但使用过的交换区尚未被覆盖,
该数值即为这些内容已存在于内存中的交换区的大小。
相应的内存再次被换出时可不必再对交换区写入。
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
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]#

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值