select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value, a.LOGON_TIME from v$session a,v$process b,v$sesstat c where c.sid=a.sid and a.paddr=b.addr order by value desc; 这个是监控CPU使用多的用户。同上也想了解下相关字段的意思。
还想问下,在执行上面的语句的时候可不可以及时监控网络中那些个用户在运行什么语句? 可是我在运行后好象不行,今天我运行第一个语句得到的结果如下: OSUSER USERNAME SQL_TEXT LOGON_TIME 1 user LYC update a_yfd set FPHM='65290',sfkp='Y' where lsh='173333' 2007-01-31 07:38:45 2 BEGIN sys.dbms_ijob.remove(:job); END; 2007-01-31 07:58:56 3 BEGIN sys.dbms_ijob.remove(:job); END; 2007-01-31 07:58:56 4 BEGIN sys.dbms_ijob.remove(:job); END; 2007-01-31 07:58:56 5 BEGIN sys.dbms_ijob.remove(:job); END; 2007-01-31 07:58:56 6 cheng LYC select a.khlsh,a.zdr,b.dwmc dwmc,c.mc wzmc,c.GG GG,a.yfs sl,a.sf 2007-01-31 07:40:09 7 cheng LYC s sfs,a.jsj jg,a.ch ch,a.yfdbh dh,a.bz, a.jsj*a.yfs je,a.jsj*a. 2007-01-31 07:40:09 8 cheng LYC sfs sfje,a.sfkp sfkp,a.ywylsh,a.shj from a_yfd a,a_wldw b,a_zyw 2007-01-31 07:40:09 9 cheng LYC zb c where a.sybz='Y' and a.khlsh=b.lsh and a.wzlsh=c.lsh and b 2007-01-31 07:40:09 10 cheng LYC .mkdm='05' and to_char(a.yxqqssj,'yyyy-mm-dd') between '2007-0 2007-01-31 07:40:09 11 cheng LYC 1-01' and '2007-01-30' order by a.yfdbh,a.wzlsh,a.khlsh 2007-01-31 07:40:09 12 cwc12 LYC select * from a_clxst a,a_clxsd b,a_gyckwzb c,a_wldw d where a.l 2007-01-31 07:56:55 13 cwc11 LYC select * from a_clxst a,a_clxsd b,a_gyckwzb c,a_wldw d where a.l 2007-01-31 07:45:40 14 cwc12 LYC sh=b.dtlsh and b.wzlsh=c.lsh and a.dwlsh=d.lsh and c.mkdm='01 2007-01-31 07:56:55 15 cwc11 LYC sh=b.dtlsh and b.wzlsh=c.lsh and a.dwlsh=d.lsh and c.mkdm='01 2007-01-31 07:45:40 16 cwc12 LYC 03' and to_char(a.kdsj,'yyyy-mm-dd') between '2007-01-01' and 2007-01-31 07:56:55 17 cwc11 LYC 03' and to_char(a.kdsj,'yyyy-mm-dd') between '2007-01-01' and 2007-01-31 07:45:40 18 cwc12 LYC '2007-01-31' and a.djzt='1' 2007-01-31 07:56:55 19 cwc11 LYC '2007-01-31' and a.djzt='1' 2007-01-31 07:45:40 20 dbf LYC select b.mc wzmc,sum(THSL) yfs,sum(mz) mz,sum(pz) pz,sum(DRLJTHS 2007-01-31 07:26:40 21 dbf LYC L) sfs from a_ftd a,a_zywzb b,a_wldw c where a.thdzt='2' and a 2007-01-31 07:26:40 22 dbf LYC .LYSHDWLSH=c.lsh and a.wzlsh=b.lsh and to_char(a.THSJ,'yyyy-mm 2007-01-31 07:26:40 23 dbf LYC -dd') between '2006-12-31' and '2007-01-31' group by b.mc 2007-01-31 07:26:40 24 SYSTEM select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su 2006-12-09 13:25:28 25 SYSTEM bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1 2006-12-09 13:25:28 26 gyk1 LYC select * from A_CGJHT a,A_CGJHD b where a.lsh=b.cgjhtlsh and 2007-01-31 07:39:22 27 gyk1 LYC b.wzdm in(select wzdm from a_gyckwzb where mkdm like '01%') an 2007-01-31 07:39:22 28 gyk1 LYC d to_char(a.kdsj,'yyyy-mm-dd') between '2007-01-01' and '2007-01 2007-01-31 07:39:22 29 gyk1 LYC -31' order by a.djbh 2007-01-31 07:39:22 30 SYSTEM select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe 2006-12-09 13:25:28 31 SYSTEM re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0 2006-12-09 13:25:28 32 Administrator LYC select a.*,b.* from a_yfd a,a_wldw b where a.sybz='Y' and a.kh 2007-01-31 07:41:17 33 Administrator LYC lsh=b.lsh and b.mkdm='04' and to_char(a.yxqqssj,'yyyy-mm-dd') b 2007-01-31 07:41:17 34 Administrator LYC etween '2006-09-29' and '2007-01-31' order by a.yfdbh,a.khlsh, 2007-01-31 07:41:17 35 Administrator LYC a.wzlsh 2007-01-31 07:41:17 36 mhg LYC select * from A_CGJHT a,A_CGJHD b where a.lsh=b.cgjhtlsh and 2007-01-31 07:47:19 37 mhg LYC b.wzdm in(select wzdm from a_gyckwzb where mkdm like '0101%') 2007-01-31 07:47:19 38 mhg LYC and to_char(a.kdsj,'yyyy-mm-dd') between '2007-01-01' and '2007- 2007-01-31 07:47:19 39 mhg LYC 01-31' order by a.djbh 2007-01-31 07:47:19 40 gyk LYC select a.lsh,a.ckdm,a.rkdbh,a.kdsj,c.wzdm,c.wzmc,c.gg,d.dwmc,a. 2007-01-31 07:55:12 41 gyk LYC cgjhdh,e.xm from a_rkdt a,a_rkdd b,a_gyckwzb c,a_wldw d,a_ry e w 2007-01-31 07:55:12 42 gyk LYC here a.gydwlsh=d.lsh and a.lsh=b.rkdtlsh and a.kdrlsh=e.lsh and 2007-01-31 07:55:12 43 gyk LYC b.wzlsh=c.lsh and to_char(a.kdsj,'yyyy-mm-dd') between '2007-01 2007-01-31 07:55:12 44 gyk LYC -01' and '2007-01-31' and a.djzt='0' and a.KDRLSH='1214' orde 2007-01-31 07:55:12 45 gyk LYC r by a.kdsj 2007-01-31 07:55:12 46 gjk LYC COMMIT WORK 2007-01-31 07:40:27 47 Administrator LYC select sysdate, dual.ROWID from dual 2007-01-31 07:47:35 48 cwc12 LYC select sysdate, dual.ROWID from dual 2007-01-31 07:57:06 49 cwc11 LYC select sysdate, dual.ROWID from dual 2007-01-31 07:52:37 50 user LYC select sysdate, dual.ROWID from dual 2007-01-31 07:39:04 51 gjk LYC select sysdate, dual.ROWID from dual 2007-01-31 07:40:39 52 cheng LYC select sysdate, dual.ROWID from dual 2007-01-31 07:41:35 53 ylk LYC select distinct yt from a_lldt 2007-01-31 07:40:56 54 Administrator LYC SELECT osuser, username, sql_text,a.LOGON_TIME from v$session a, 2007-01-31 07:55:07 55 Administrator LYC v$sqltext b where a.sql_address =b.address order by address 2007-01-31 07:55:07 56 Administrator LYC , piece 2007-01-31 07:55:07 57 cwc11 LYC select mkmc from a_mkhf where mkdm='16' 2007-01-31 07:45:40 58 Administrator LYC select value from v$sesstat where sid = :sid order by statistic# 2007-01-31 07:54:49 59 Administrator LYC 2007-01-31 07:54:49 只有59条记录,可是明显我的网络中的使用很频繁的。
from v$session a,v$process b,v$sesstat c
where c.sid=a.sid and a.paddr=b.addr order by value desc;
这个是监控CPU使用多的用户。同上也想了解下相关字段的意思。
可是我在运行后好象不行,今天我运行第一个语句得到的结果如下:
OSUSER USERNAME SQL_TEXT LOGON_TIME
1 user LYC update a_yfd set FPHM='65290',sfkp='Y' where lsh='173333' 2007-01-31 07:38:45
2 BEGIN sys.dbms_ijob.remove(:job); END; 2007-01-31 07:58:56
3 BEGIN sys.dbms_ijob.remove(:job); END; 2007-01-31 07:58:56
4 BEGIN sys.dbms_ijob.remove(:job); END; 2007-01-31 07:58:56
5 BEGIN sys.dbms_ijob.remove(:job); END; 2007-01-31 07:58:56
6 cheng LYC select a.khlsh,a.zdr,b.dwmc dwmc,c.mc wzmc,c.GG GG,a.yfs sl,a.sf 2007-01-31 07:40:09
7 cheng LYC s sfs,a.jsj jg,a.ch ch,a.yfdbh dh,a.bz, a.jsj*a.yfs je,a.jsj*a. 2007-01-31 07:40:09
8 cheng LYC sfs sfje,a.sfkp sfkp,a.ywylsh,a.shj from a_yfd a,a_wldw b,a_zyw 2007-01-31 07:40:09
9 cheng LYC zb c where a.sybz='Y' and a.khlsh=b.lsh and a.wzlsh=c.lsh and b 2007-01-31 07:40:09
10 cheng LYC .mkdm='05' and to_char(a.yxqqssj,'yyyy-mm-dd') between '2007-0 2007-01-31 07:40:09
11 cheng LYC 1-01' and '2007-01-30' order by a.yfdbh,a.wzlsh,a.khlsh 2007-01-31 07:40:09
12 cwc12 LYC select * from a_clxst a,a_clxsd b,a_gyckwzb c,a_wldw d where a.l 2007-01-31 07:56:55
13 cwc11 LYC select * from a_clxst a,a_clxsd b,a_gyckwzb c,a_wldw d where a.l 2007-01-31 07:45:40
14 cwc12 LYC sh=b.dtlsh and b.wzlsh=c.lsh and a.dwlsh=d.lsh and c.mkdm='01 2007-01-31 07:56:55
15 cwc11 LYC sh=b.dtlsh and b.wzlsh=c.lsh and a.dwlsh=d.lsh and c.mkdm='01 2007-01-31 07:45:40
16 cwc12 LYC 03' and to_char(a.kdsj,'yyyy-mm-dd') between '2007-01-01' and 2007-01-31 07:56:55
17 cwc11 LYC 03' and to_char(a.kdsj,'yyyy-mm-dd') between '2007-01-01' and 2007-01-31 07:45:40
18 cwc12 LYC '2007-01-31' and a.djzt='1' 2007-01-31 07:56:55
19 cwc11 LYC '2007-01-31' and a.djzt='1' 2007-01-31 07:45:40
20 dbf LYC select b.mc wzmc,sum(THSL) yfs,sum(mz) mz,sum(pz) pz,sum(DRLJTHS 2007-01-31 07:26:40
21 dbf LYC L) sfs from a_ftd a,a_zywzb b,a_wldw c where a.thdzt='2' and a 2007-01-31 07:26:40
22 dbf LYC .LYSHDWLSH=c.lsh and a.wzlsh=b.lsh and to_char(a.THSJ,'yyyy-mm 2007-01-31 07:26:40
23 dbf LYC -dd') between '2006-12-31' and '2007-01-31' group by b.mc 2007-01-31 07:26:40
24 SYSTEM select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su 2006-12-09 13:25:28
25 SYSTEM bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1 2006-12-09 13:25:28
26 gyk1 LYC select * from A_CGJHT a,A_CGJHD b where a.lsh=b.cgjhtlsh and 2007-01-31 07:39:22
27 gyk1 LYC b.wzdm in(select wzdm from a_gyckwzb where mkdm like '01%') an 2007-01-31 07:39:22
28 gyk1 LYC d to_char(a.kdsj,'yyyy-mm-dd') between '2007-01-01' and '2007-01 2007-01-31 07:39:22
29 gyk1 LYC -31' order by a.djbh 2007-01-31 07:39:22
30 SYSTEM select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe 2006-12-09 13:25:28
31 SYSTEM re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0 2006-12-09 13:25:28
32 Administrator LYC select a.*,b.* from a_yfd a,a_wldw b where a.sybz='Y' and a.kh 2007-01-31 07:41:17
33 Administrator LYC lsh=b.lsh and b.mkdm='04' and to_char(a.yxqqssj,'yyyy-mm-dd') b 2007-01-31 07:41:17
34 Administrator LYC etween '2006-09-29' and '2007-01-31' order by a.yfdbh,a.khlsh, 2007-01-31 07:41:17
35 Administrator LYC a.wzlsh 2007-01-31 07:41:17
36 mhg LYC select * from A_CGJHT a,A_CGJHD b where a.lsh=b.cgjhtlsh and 2007-01-31 07:47:19
37 mhg LYC b.wzdm in(select wzdm from a_gyckwzb where mkdm like '0101%') 2007-01-31 07:47:19
38 mhg LYC and to_char(a.kdsj,'yyyy-mm-dd') between '2007-01-01' and '2007- 2007-01-31 07:47:19
39 mhg LYC 01-31' order by a.djbh 2007-01-31 07:47:19
40 gyk LYC select a.lsh,a.ckdm,a.rkdbh,a.kdsj,c.wzdm,c.wzmc,c.gg,d.dwmc,a. 2007-01-31 07:55:12
41 gyk LYC cgjhdh,e.xm from a_rkdt a,a_rkdd b,a_gyckwzb c,a_wldw d,a_ry e w 2007-01-31 07:55:12
42 gyk LYC here a.gydwlsh=d.lsh and a.lsh=b.rkdtlsh and a.kdrlsh=e.lsh and 2007-01-31 07:55:12
43 gyk LYC b.wzlsh=c.lsh and to_char(a.kdsj,'yyyy-mm-dd') between '2007-01 2007-01-31 07:55:12
44 gyk LYC -01' and '2007-01-31' and a.djzt='0' and a.KDRLSH='1214' orde 2007-01-31 07:55:12
45 gyk LYC r by a.kdsj 2007-01-31 07:55:12
46 gjk LYC COMMIT WORK 2007-01-31 07:40:27
47 Administrator LYC select sysdate, dual.ROWID from dual 2007-01-31 07:47:35
48 cwc12 LYC select sysdate, dual.ROWID from dual 2007-01-31 07:57:06
49 cwc11 LYC select sysdate, dual.ROWID from dual 2007-01-31 07:52:37
50 user LYC select sysdate, dual.ROWID from dual 2007-01-31 07:39:04
51 gjk LYC select sysdate, dual.ROWID from dual 2007-01-31 07:40:39
52 cheng LYC select sysdate, dual.ROWID from dual 2007-01-31 07:41:35
53 ylk LYC select distinct yt from a_lldt 2007-01-31 07:40:56
54 Administrator LYC SELECT osuser, username, sql_text,a.LOGON_TIME from v$session a, 2007-01-31 07:55:07
55 Administrator LYC v$sqltext b where a.sql_address =b.address order by address 2007-01-31 07:55:07
56 Administrator LYC , piece 2007-01-31 07:55:07
57 cwc11 LYC select mkmc from a_mkhf where mkdm='16' 2007-01-31 07:45:40
58 Administrator LYC select value from v$sesstat where sid = :sid order by statistic# 2007-01-31 07:54:49
59 Administrator LYC 2007-01-31 07:54:49
只有59条记录,可是明显我的网络中的使用很频繁的。