怎么获取ORACLE的系统信息？
要想对ORACLE所在主机的CPU占用率，内存占用，磁盘利用率进行监控，能否直接通过ORACLE的select语句获得这些数据啊？哪位大虾指点一下，在下感激不尽。 try "select * from v$sysstat;"
这些信息，数据字典里面有。查看一下"v$"开头的数据库视图。 请教猫，以下是结果,该如何用这些数据计算磁盘利用率啊?
STATISTIC#,NAME,CLASS,VALUE,STAT_ID
0,logons cumulative,1,1121,2666645286
1,logons current,1,29,3080465522
2,opened cursors cumulative,1,61191,85052502
3,opened cursors current,1,50,2301954928
4,user commits,1,2870,582481098
5,user rollbacks,1,20,3671147913
6,user calls,1,25786,2882015696
7,recursive calls,1,662664,2656001462
8,recursive cpu usage,1,37628,4009879262
9,session logical reads,1,10012156,3143187968
10,session stored procedure space,1,0,895486831
11,CPU used when call started,128,39471,572264820
12,CPU used by this session,1,40490,24469293
13,DB time,1,2888011,3649082374
14,cluster wait time,1,0,2432034337
15,concurrency wait time,1,743,3868577743
16,application wait time,1,13,1099569955
17,user I/O wait time,1,168,3332107451
18,session connect time,1,1196298246,1678473874
19,process last non-idle time,128,1196327538,2049556447
20,session uga memory,1,2108863897440,1856888586
21,session uga memory max,1,556512424,3840343119
22,messages sent,128,9679,2486045308
23,messages received,128,9683,1396153242
24,background timeouts,128,99154,961017212
25,session pga memory,1,518733388,4148600571
26,session pga memory max,1,638288080,507777907
27,enqueue timeouts,4,1300,2425496215
28,enqueue waits,4,27,2307006529
29,enqueue deadlocks,4,0,472183780
30,enqueue requests,4,281429,2440542518
31,enqueue conversions,4,6343,57745673
32,enqueue releases,4,280117,2553146675
33,global enqueue gets sync,32,0,1338475854
34,global enqueue gets async,32,0,2892637759
35,global enqueue get time,32,0,3744090840
36,global enqueue releases,32,0,1516404026
37,physical read total IO requests,8,24708,3343375620
38,physical read total multi block requests,8,1236,2007302071
39,physical read total bytes,8,406911488,2572010804
40,physical write total IO requests,8,41639,1315894329
41,physical write total multi block requests,8,4299,3540174003
42,physical write total bytes,8,675805184,2495644835
43,IPC CPU used by this session,32,0,4247517299
44,gcs messages sent,32,0,2765451804
45,ges messages sent,32,0,1145425433
46,global enqueue CPU used by this session,32,0,3469911798
47,db block gets,8,103001,1480709069
48,db block gets from cache,8,102757,4017839461
49,db block gets direct,8,244,95128520
50,consistent gets,8,9909155,4162191256
51,consistent gets from cache,8,9909153,2839918855
52,consistent gets - examination,8,6237099,1701530557
53,consistent gets direct,8,2,420374750
54,physical reads,8,17649,2263124246
55,physical reads cache,8,17611,4171507801
56,physical reads direct,8,37,2589616721
57,physical read IO requests,8,10233,789768877
58,physical read bytes,8,144580608,523531786
59,db block changes,8,125128,916801489
60,consistent changes,8,1009,3876379665
61,recovery blocks read,8,728,3327333035
62,physical writes,8,10065,1190468109
63,physical writes direct,8,252,2699895516
64,physical writes from cache,8,9813,163083034
65,physical write IO requests,8,7075,2904164198
66,physical write bytes,8,82452480,3131337131
67,physical writes non checkpoint,8,4095,2602029796
68,summed dirty queue length,8,0,1683565474
69,DBWR checkpoint buffers written,8,9023,1208600358
70,DBWR thread checkpoint buffers written,8,0,3905787588
71,DBWR tablespace checkpoint buffers written,8,0,2649259263
72,DBWR parallel query checkpoint buffers written,8,0,1768645316
73,DBWR object drop buffers written,8,28,658143835
74,DBWR transaction table writes,8,813,2146120386
75,DBWR undo block writes,8,2244,111270822
76,DBWR revisited being-written buffer,8,0,2773697723
77,DBWR make free requests,8,0,1237874725
78,DBWR lru scans,8,0,2139101792
79,DBWR checkpoints,8,26,1732023165
80,DBWR fusion writes,40,0,2313150541
81,prefetch clients - keep,8,0,574469659
82,prefetch clients - recycle,8,0,4053781837
83,prefetch clients - default,8,0,1612053064
84,prefetch clients - 2k,8,0,396625644
85,prefetch clients - 4k,8,0,3252900252
86,prefetch clients - 8k,8,0,1402775714
87,prefetch clients - 16k,8,0,792304463
88,prefetch clients - 32k,8,0,2351177679
89,change write time,8,66,2838938797
90,redo synch writes,8,8757,1439995281
91,redo synch time,8,605,4215815172
92,exchange deadlocks,8,0,14632483
93,free buffer requested,8,20738,3411924934
94,dirty buffers inspected,8,0,1344569897
95,pinned buffers inspected,8,0,833456521
96,hot buffers moved to head of LRU,8,0,2030212224
97,free buffer inspected,8,1,941676439
98,commit cleanout failures: write disabled,8,0,2063165199
99,commit cleanout failures: block lost,8,0,781179189
100,commit cleanout failures: cannot pin,8,0,176423721
101,commit cleanout failures: hot backup in progress,8,0,2500023621
102,commit cleanout failures: buffer being written,8,0,1626141486
103,commit cleanout failures: callback failure ,8,41,3086716748
104,commit cleanouts,8,15600,2934869719
105,commit cleanouts successfully completed,8,15559,3554314084
106,recovery array reads,8,0,2617845348
107,recovery array read time,8,0,2996696667
108,CR blocks created,8,982,516801181
109,current blocks converted for CR,8,0,3142330676
110,switch current to new buffer,8,62,1312802324
111,write clones created in foreground,8,0,4237693830
112,write clones created in background,8,0,2988965544
113,write clones created for recovery,8,0,4053425429
114,physical reads cache prefetch,8,7416,297908839
115,physical reads prefetch warmup,8,1521,3102888545
116,prefetched blocks aged out before use,8,0,1457461390
117,prefetch warmup blocks aged out before use,8,0,3250306695
118,prefetch warmup blocks flushed out before use,8,0,2218846789
119,physical reads retry corrupt,8,0,531193461
120,physical reads direct (lob),8,0,2564935310
121,physical reads direct temporary tablespace,8,7,2663793346
122,physical writes direct (lob),8,244,3308932835
123,physical writes direct temporary tablespace,8,0,996415569
124,cold recycle reads,8,0,657271764
125,shared hash latch upgrades - no wait,8,936788,2488550671
126,shared hash latch upgrades - wait,8,0,3380476499
127,physical reads for flashback new,8,0,473165409
128,calls to kcmgcs,128,763,2775936802
129,calls to kcmgrs,128,0,4210206824
130,calls to kcmgas,128,8363,4072914524
131,calls to get snapshot scn: kcmgss,32,127254,3128650623
132,redo blocks read for recovery,2,14334,2679943069
133,redo entries,2,59912,3488821837
134,redo size,2,26609500,1236385760
135,redo buffer allocation retries,2,0,1446958922
136,redo wastage,2,950128,3462806146
137,redo writer latching time,2,0,2166056472
138,redo writes,2,3585,1948353376
139,redo blocks written,2,55622,2391431605
140,redo write time,2,1759,3094453259
141,redo log space requests,2,0,1985754937
142,redo log space wait time,2,0,252430928
143,redo log switch interrupts,2,0,674283274
144,redo ordering marks,2,1174,2104561012
145,redo subscn max counts,2,0,449106517
146,gc cr blocks served,40,0,1075941831
147,gc cr block build time,40,0,467525985
148,gc cr block flush time,40,0,552470873
149,gc cr block send time,40,0,2395315974
150,gc current blocks served,40,0,42062110
151,gc current block pin time,40,0,324533635
152,gc current block flush time,40,0,4091964965
153,gc current block send time,40,0,2750158241
154,gc cr blocks received,40,0,2877738702
155,gc cr block receive time,40,0,1759426133
156,gc current blocks received,40,0,326482564
157,gc current block receive time,40,0,1388758753
158,gc blocks lost,40,0,500461751
159,gc claim blocks lost,40,0,1966230523
160,gc blocks corrupt,40,0,2288508531
161,gc CPU used by this session,40,0,4093034494
162,total number of slots,8,0,2223809671
163,instance recovery database freeze count,32,0,3674920298
164,background checkpoints started,8,0,3048007007
165,background checkpoints completed,8,0,2450922463
166,number of map operations,8,0,337177656
167,number of map misses,8,0,3618703100
168,flashback log writes,2,0,3123176560
169,serializable aborts,1,0,1793250745
170,transaction lock foreground requests,128,0,185785666
171,transaction lock foreground wait time,128,0,4033661655
172,transaction lock background gets,128,0,3977359732
173,transaction lock background get time,128,0,3492664117
174,undo change vector size,128,8284784,1465971540
175,transaction tables consistent reads - undo records applied,128,0,1054055970
176,transaction tables consistent read rollbacks,128,0,1396293204
177,data blocks consistent reads - undo records applied,128,1009,2915445793
178,no work - consistent read gets,128,2708085,2814375799
179,cleanouts only - consistent read gets,128,149,2904875805
180,rollbacks only - consistent read gets,128,975,3133064500
181,cleanouts and rollbacks - consistent read gets,128,7,1083903169
182,RowCR attempts,128,0,2297882326
183,RowCR hits,128,0,3455911385
184,RowCR - row contention,128,0,1175104080
185,RowCR - resume,128,0,2583359573
186,rollback changes - undo records applied,128,6,3616249137
187,transaction rollbacks,128,3,3988650402
188,immediate (CURRENT) block cleanout applications,128,2712,2668233561
189,immediate (CR) block cleanout applications,128,156,3318186802
190,deferred (CURRENT) block cleanout applications,128,9888,3388736327
191,commit txn count during cleanout,128,534,1405359431
192,active txn count during cleanout,128,536,3378035478
193,cleanout - number of ktugct calls,128,895,2577316246
194,immediate CR cleanouts (index blocks),128,0,79013953
195,deferred CUR cleanouts (index blocks),128,0,3827443394
196,Commit SCN cached,128,0,2460709063
197,Cached Commit SCN referenced,128,0,2374582560
198,auto extends on undo tablespace,128,0,3140365462
199,drop segment calls in space pressure,128,0,1500291109
200,total number of undo segments dropped,128,0,3623640507
201,doubling up with imu segment,128,0,723921798
202,tune down retentions in space pressure,128,0,142917891
203,steps of tune down ret. in space pressure,128,0,3986010614
204,space was found by tune down,128,0,342698422
205,space was not found by tune down,128,0,849561940
206,commit batch/immediate requested,1,3,640321576
207,commit batch requested,1,0,2338940602
208,commit immediate requested,1,3,796663742
209,commit batch/immediate performed,1,3,3711642721
210,commit batch performed,1,0,1391516579
211,commit immediate performed,1,3,3494177237
212,commit wait/nowait requested,1,0,3800399491
213,commit nowait requested,1,0,3697788123
214,commit wait requested,1,0,2843300285
215,commit wait/nowait performed,1,0,1541239326
216,commit nowait performed,1,0,485928497
217,commit wait performed,1,0,8047752
218,global undo segment hints helped,128,0,1791494885
219,global undo segment hints were stale,128,0,3309048233
220,local undo segment hints helped,128,0,1047863356
221,local undo segment hints were stale,128,0,2070200837
222,undo segment header was pinned,128,0,248401831
223,total number of times SMON posted,128,23,399827284
224,SMON posted for undo segment recovery,128,0,2117898593
225,SMON posted for txn recovery for other instances,128,0,1263842513
226,SMON posted for instance recovery,128,0,4003178738
227,SMON posted for undo segment shrink,128,3,579492169
228,SMON posted for dropping temp segment,128,0,3600495721
229,SMON posted for supplemental logging cleanup,128,0,1356155743
230,IMU commits,128,2346,1914489094
231,IMU Flushes,128,393,2099506212
232,IMU contention,128,1,2909373607
233,IMU recursive-transaction flush,128,2,2591100633
234,IMU undo retention flush,128,0,2087226422
235,IMU ktichg flush,128,0,1206609541
236,IMU bind flushes,128,0,2756376339
237,IMU mbu flush,128,0,3723686946
238,IMU pool not allocated,128,0,659017805
239,IMU CR rollbacks,128,0,2225124543
240,IMU undo allocation size,128,7179308,244193920
241,IMU Redo allocation size,128,1871368,3945654623
242,IMU- failed to get a private strand,128,0,2412863545
243,Misses for writing mapping,128,0,2725329757
244,table scans (short tables),64,13218,345141973
245,table scans (long tables),64,3,1042655239
246,table scans (rowid ranges),64,0,1303412790
247,table scans (cache partitions),64,0,133545664
248,table scans (direct read),64,0,3355423586
249,table scan rows gotten,64,16212949,1400824662
250,table scan blocks gotten,64,421914,3741388076
251,table fetch by rowid,64,549111,681815839
252,table fetch continued row,64,541,1413702393
253,cluster key scans,64,935300,3566804345
254,cluster key scan block gets,64,1991828,2006808371
255,rows fetched via callback,64,164668,3299365556
256,queue update without cp update,128,0,528828405
257,leaf node splits,128,172,1417124053
258,leaf node 90-10 splits,128,59,1285138251
259,branch node splits,128,0,399027615
260,native hash arithmetic execute,64,0,321852432
261,native hash arithmetic fail,64,0,1937851790
262,lob reads,8,237,3626914479
263,lob writes,8,191,2682192071
264,lob writes unaligned,8,191,4224694283
265,index fast full scans (full),64,173,12081473
266,index fast full scans (rowid ranges),64,0,1555464060
267,index fast full scans (direct read),64,0,1635662180
268,index fetch by key,128,6167635,2808364230
269,index scans kdiixs1,128,936356,3589793292
270,queue splits,128,0,1168925905
271,queue flush,128,0,3150060076
272,queue position update,128,0,2568542507
273,queue single row,128,0,2713404457
274,queue ocp pages,128,0,3899522644
275,queue qno pages,128,0,2998782417
276,heap block compress,64,282,2326757257
277,sql area purged,64,217,2763387941
278,sql area aged,64,0,3156625321
279,session cursor cache hits,64,31833,3678609077
280,session cursor cache count,64,8425,568260813
281,java call heap total size,1,0,1649812842
282,java call heap total size max,1,0,580075922
283,java call heap used size,1,0,187277223
284,java call heap used size max,1,0,625960724
285,java call heap live size,1,0,2431389341
286,java call heap live size max,1,0,1298432410
287,java call heap object count,1,0,3472185722
288,java call heap object count max,1,0,1862536587
289,java call heap live object count,1,0,632577394
290,java call heap live object count max,1,0,2964139762
291,java call heap gc count,1,0,2284697213
292,java call heap collected count,1,0,2510746206
293,java call heap collected bytes,1,0,3585877838
294,java session heap used size,1,0,1661140639
295,java session heap used size max,1,0,1726629032
296,java session heap live size,1,0,2257986540
297,java session heap live size max,1,0,1960804148
298,java session heap object count,1,0,4072938679
299,java session heap object count max,1,0,469647992
300,java session heap live object count,1,0,225051972
301,java session heap live object count max,1,0,3755633366
302,java session heap gc count,1,0,3363147808
303,java session heap collected count,1,0,3795934475
304,java session heap collected bytes,1,0,3976434298
305,cursor authentications,128,1550,4069981174
306,queries parallelized,32,0,3315614405
307,DML statements parallelized,32,0,3791923855
308,DDL statements parallelized,32,0,4115134636
309,DFO trees parallelized,32,0,2753131890
310,Parallel operations not downgraded,32,0,2410168744
311,Parallel operations downgraded to serial,32,0,4286011915
312,Parallel operations downgraded 75 to 99 pct,32,0,322858058
313,Parallel operations downgraded 50 to 75 pct,32,0,3281923117
314,Parallel operations downgraded 25 to 50 pct,32,0,3330456527
315,Parallel operations downgraded 1 to 25 pct,32,0,3721245209
316,PX local messages sent,32,1604,506655048
317,PX local messages recv'd,32,1604,1437798390
318,PX remote messages sent,32,0,2567415317
319,PX remote messages recv'd,32,0,2537140420
320,buffer is pinned count,72,12434310,1300470380
321,buffer is not pinned count,72,2795942,2670431739
322,no buffer to keep pinned count,72,21,3528454686
323,table lookup prefetch client count,8,0,1926456670
324,workarea memory allocated,64,0,2433935387
325,workarea executions - optimal,64,12694,3211650785
326,workarea executions - onepass,64,0,798730793
327,workarea executions - multipass,64,0,3804491469
328,parse time cpu,64,36067,206905303
329,parse time elapsed,64,36064,1431595225
330,parse count (total),64,52300,63887964
331,parse count (hard),64,6818,143509059
332,parse count (failures),64,86,1118776443
333,frame signature mismatch,64,3,959148951
334,execute count,64,91692,2453370665
335,bytes sent via SQL*Net to client,1,4371876,2967415760
336,bytes received via SQL*Net from client,1,3678511,161936656
337,SQL*Net roundtrips to/from client,1,21157,159846526
338,bytes sent via SQL*Net to dblink,1,0,1622773540
339,bytes received via SQL*Net from dblink,1,0,1983609624
340,SQL*Net roundtrips to/from dblink,1,0,2420448902
341,sorts (memory),64,23371,2091983730
342,sorts (disk),64,0,2533123502
343,sorts (rows),64,376517,3757672740
344,OTC commit optimization attempts,128,0,2480822770
345,OTC commit optimization hits,128,0,4064864054
346,OTC commit optimization failure - setup,128,0,3633344886
347,OS User time used,16,0,3628421385
348,OS System time used,16,0,1159355779
349,OS Maximum resident set size,16,0,838789841
350,OS Integral shared text size,16,0,2409964096
351,OS Integral unshared data size,16,0,56092924
352,OS Integral unshared stack size,16,0,2957272649
353,OS Page reclaims,16,0,800986673
354,OS Page faults,16,0,3353033775
355,OS Swaps,16,0,1982803996
356,OS Block input operations,16,0,953749291
357,OS Block output operations,16,0,756247347
358,OS Socket messages sent,16,0,1887989827
359,OS Socket messages received,16,0,2122589827
360,OS Signals received,16,0,210375991
361,OS Voluntary context switches,16,0,2422402766
362,OS Involuntary context switches,16,0,3316937952
[[i] 本帖最后由 Flyinmorning 于 2007-11-29 18:17 编辑 [/i]]
明白你的意思了
对于Oracle所在主机的磁盘、CPU、内存等信息，在猫看来Oracle作为一个数据库软件是不关心的。LZ需要监控主机信息对吧？猫当年作网管的时候常常这么做：
磁盘 df -h <-- linux下用-h比-k好；
CPU top
内存 vmstat
当然，这个具体用什么命令要什么结果，什么使用率就该报警了，各个单位的定义不一样，就不详述了。
=======================================================================
其实，个人感觉一个sar命令就可以搞定这些。
sar 命令行的常用格式： sar [options] [-A] [-o file] t [n]
在命令行中，n 和t 两个参数组合起来定义采样间隔和次数，t为采样间隔，是必须有的参数，n为采样次数，是可选的，默认值是1，-o file表示将命令结果以二进制格式存放在文件中，file 在此处不是关键字，是文件名。options 为命令行选项，sar命令的选项很多，下面只列出常用选项：
-A：所有报告的总和 -u：CPU利用率 -v：进程、i节点、文件和锁表状态 -d：硬盘使用报告 -r：没有使用的内存页面和硬盘块 -g：串口I/O的情况 -b：缓冲区使用情况 -a：文件读写情况 -c：系统调用情况 -R：进程的活动情况 -y：终端设备活动情况 -w：系统交换活动
是不是很全面了？做系统监控的时候，用一个sar命令不同的参数，将那些信息都收集起来，然后脚本分析也好，人工分析也好。猫认为还算方便。
[[i] 本帖最后由 9命怪猫 于 2007-11-30 18:29 编辑 [/i]] 谢谢猫兄的回复，其实我不是要在本地作监控，而是远程监控oracle所在的机器
在另一台服务器上，跑着web oam，监控整个系统的多台主机信息，oracle数据库机器(AIX)只是其中一台，我是想如果oracle本身就能提供主机信息，那我就不用在IBM AIX上自己写程序收集信息并向OAM提供数据了，让OAM自己用sql语句查好了。现在看来这个想法要泡汤了....... SQL Server可以做到部分你说的功能的。Oracle，确实不精通...
:L 好牛啊猫,羡慕啊 难度较高
页:
[1]