ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Oracle数据库DBA高级工程师培训视频
Oracle数据库培训-备份恢复-性能优化-集群容灾
mysql数据库视频教程

【Oracle九大性能视图】之9.v$sysstat_所有instance的统计信息

内容发布:风哥| 发布时间:2013-12-26 17:03:22
【Oracle九大性能视图】之9.v$sysstat_所有instance的统计信息

1、表结构
SQL> desc v$sysstat
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
STATISTIC#                                         NUMBER
NAME                                               VARCHAR2(64)
CLASS                                              NUMBER
VALUE                                              NUMBER
STAT_ID                                            NUMBER

2、查询结果
select * from v$sysstat
    STATISTIC# NAME CLASS VALUE STAT_ID

1 0 logons cumulative 1 288 2666645286
2 1 logons current 1 18 3080465522
3 2 opened cursors cumulative 1 99107 85052502
4 3 opened cursors current 1 33 2301954928
5 4 user commits 1 2293 582481098
6 5 user rollbacks 1 0 3671147913
7 6 user calls 1 9129 2882015696
8 7 recursive calls 1 1755269 2656001462
9 8 recursive cpu usage 1 6612 4009879262
10 9 session logical reads 1 2813235 3143187968
11 10 session stored procedure space 1 0 895486831
12 11 CPU used when call started 128 2204 572264820
13 12 CPU used by this session 1 7673 24469293
14 13 DB time 1 1362669 3649082374
15 14 cluster wait time 1 0 2432034337
16 15 concurrency wait time 1 542 3868577743
17 16 application wait time 1 2 1099569955
18 17 user I/O wait time 1 18062 3332107451
19 18 session connect time 1 0 1678473874
20 19 process last non-idle time 128 0 2049556447
21 20 session uga memory 1 949200603856 1856888586
22 21 session uga memory max 1 296171192 3840343119
23 22 messages sent 128 8203 2486045308
24 23 messages received 128 8207 1396153242
25 24 background timeouts 128 39729 961017212
26 25 session pga memory 1 352660600 4148600571
27 26 session pga memory max 1 573779064 507777907
28 27 enqueue timeouts 4 0 2425496215
29 28 enqueue waits 4 10 2307006529
30 29 enqueue deadlocks 4 0 472183780
31 30 enqueue requests 4 137838 2440542518
32 31 enqueue conversions 4 2553 57745673
33 32 enqueue releases 4 137825 2553146675
34 33 global enqueue gets sync 32 0 1338475854
35 34 global enqueue gets async 32 0 2892637759
36 35 global enqueue get time 32 0 3744090840
37 36 global enqueue releases 32 0 1516404026
38 37 physical read total IO requests 8 33681 3343375620
39 38 physical read total multi block requests 8 3978 2007302071
40 39 physical read total bytes 8 700127232 2572010804
41 40 physical write total IO requests 8 21442 1315894329
42 41 physical write total multi block requests 8 6091 3540174003
43 42 physical write total bytes 8 325118464 2495644835
44 43 IPC CPU used by this session 32 0 4247517299
45 44 gcs messages sent 32 0 2765451804
46 45 ges messages sent 32 0 1145425433
47 46 global enqueue CPU used by this session 32 0 3469911798
48 47 db block gets 8 291092 1480709069
49 48 db block gets from cache 8 290553 4017839461
50 49 db block gets direct 8 539 95128520
51 50 consistent gets 8 2522143 4162191256
52 51 consistent gets from cache 8 2522142 2839918855
53 52 consistent gets - examination 8 1106506 1701530557
54 53 consistent gets direct 8 1 420374750
55 54 physical reads 8 71089 2263124246
56 55 physical reads cache 8 71065 4171507801
57 56 physical reads direct 8 24 2589616721
58 57 physical read IO requests 8 26489 789768877
59 58 physical read bytes 8 582361088 523531786
60 59 db block changes 8 347216 916801489
61 60 consistent changes 8 3898 3876379665
62 61 recovery blocks read 8 0 3327333035
63 62 physical writes 8 7380 1190468109
64 63 physical writes direct 8 549 2699895516
65 64 physical writes from cache 8 6831 163083034
66 65 physical write IO requests 8 3056 2904164198
67 66 physical write bytes 8 60456960 3131337131
68 67 physical writes non checkpoint 8 5724 2602029796
69 68 summed dirty queue length 8 12394 1683565474
70 69 DBWR checkpoint buffers written 8 5204 1208600358
71 70 DBWR thread checkpoint buffers written 8 0 3905787588
72 71 DBWR tablespace checkpoint buffers written 8 0 2649259263
73 72 DBWR parallel query checkpoint buffers written 8 0 1768645316
74 73 DBWR object drop buffers written 8 0 658143835
75 74 DBWR transaction table writes 8 315 2146120386
76 75 DBWR undo block writes 8 2884 111270822
77 76 DBWR revisited being-written buffer 8 0 2773697723
78 77 DBWR make free requests 8 0 1237874725
79 78 DBWR lru scans 8 0 2139101792
80 79 DBWR checkpoints 8 1 1732023165
81 80 DBWR fusion writes 40 0 2313150541
82 81 prefetch clients - keep 8 0 574469659
83 82 prefetch clients - recycle 8 0 4053781837
84 83 prefetch clients - default 8 0 1612053064
85 84 prefetch clients - 2k 8 0 396625644
86 85 prefetch clients - 4k 8 0 3252900252
87 86 prefetch clients - 8k 8 0 1402775714
88 87 prefetch clients - 16k 8 0 792304463
89 88 prefetch clients - 32k 8 0 2351177679
90 89 change write time 8 513 2838938797
91 90 redo synch writes 8 244 1439995281
92 91 redo synch time 8 139 4215815172
93 92 exchange deadlocks 8 0 14632483
94 93 free buffer requested 8 78091 3411924934
95 94 dirty buffers inspected 8 1120 1344569897
96 95 pinned buffers inspected 8 2 833456521
97 96 hot buffers moved to head of LRU 8 8202 2030212224
98 97 free buffer inspected 8 62694 941676439
99 98 commit cleanout failures: write disabled 8 0 2063165199
100 99 commit cleanout failures: block lost 8 12 781179189
101 100 commit cleanout failures: cannot pin 8 0 176423721
102 101 commit cleanout failures: hot backup in progress 8 0 2500023621
103 102 commit cleanout failures: buffer being written 8 0 1626141486
104 103 commit cleanout failures: callback failure  8 61 3086716748
105 104 commit cleanouts 8 18352 2934869719
106 105 commit cleanouts successfully completed 8 18279 3554314084
107 106 recovery array reads 8 0 2617845348
108 107 recovery array read time 8 0 2996696667
109 108 CR blocks created 8 1585 516801181
110 109 current blocks converted for CR 8 0 3142330676
111 110 switch current to new buffer 8 1391 1312802324
112 111 write clones created in foreground 8 0 4237693830
113 112 write clones created in background 8 0 2988965544
114 113 write clones created for recovery 8 0 4053425429
115 114 physical reads cache prefetch 8 44600 297908839
116 115 physical reads prefetch warmup 8 385 3102888545
117 116 prefetched blocks aged out before use 8 5 1457461390
118 117 prefetch warmup blocks aged out before use 8 61 3250306695
119 118 prefetch warmup blocks flushed out before use 8 0 2218846789
120 119 physical reads retry corrupt 8 0 531193461
121 120 physical reads direct (lob) 8 1 2564935310
122 121 physical reads direct temporary tablespace 8 4 2663793346
123 122 physical writes direct (lob) 8 131 3308932835
124 123 physical writes direct temporary tablespace 8 408 996415569
125 124 cold recycle reads 8 0 657271764
126 125 shared hash latch upgrades - no wait 8 15376 2488550671
127 126 shared hash latch upgrades - wait 8 1 3380476499
128 127 physical reads for flashback new 8 0 473165409
129 128 calls to kcmgcs 128 916 2775936802
130 129 calls to kcmgrs 128 0 4210206824
131 130 calls to kcmgas 128 22631 4072914524
132 131 calls to get snapshot scn: kcmgss 32 257884 3128650623
133 132 redo blocks read for recovery 2 0 2679943069
134 133 redo entries 2 171454 3488821837
135 134 redo size 2 51349660 1236385760
136 135 redo buffer allocation retries 2 2 1446958922
137 136 redo wastage 2 1558576 3462806146
138 137 redo writer latching time 2 0 2166056472
139 138 redo writes 2 5486 1948353376
140 139 redo blocks written 2 107181 2391431605
141 140 redo write time 2 1557 3094453259
142 141 redo log space requests 2 2 1985754937
143 142 redo log space wait time 2 19 252430928
144 143 redo log switch interrupts 2 0 674283274
145 144 redo ordering marks 2 2671 2104561012
146 145 redo subscn max counts 2 4199 449106517
147 146 gc cr blocks served 40 0 1075941831
148 147 gc cr block build time 40 0 467525985
149 148 gc cr block flush time 40 0 552470873
150 149 gc cr block send time 40 0 2395315974
151 150 gc current blocks served 40 0 42062110
152 151 gc current block pin time 40 0 324533635
153 152 gc current block flush time 40 0 4091964965
154 153 gc current block send time 40 0 2750158241
155 154 gc cr blocks received 40 0 2877738702
156 155 gc cr block receive time 40 0 1759426133
157 156 gc current blocks received 40 0 326482564
158 157 gc current block receive time 40 0 1388758753
159 158 gc local grants 40 0 3656029643
160 159 gc remote grants 40 0 1406613639
161 160 gc blocks lost 40 0 500461751
162 161 gc claim blocks lost 40 0 1966230523
163 162 gc blocks corrupt 40 0 2288508531
164 163 gc CPU used by this session 40 0 4093034494
165 164 total number of slots 8 0 2223809671
166 165 instance recovery database freeze count 32 0 3674920298
167 166 background checkpoints started 8 1 3048007007
168 167 background checkpoints completed 8 1 2450922463
169 168 number of map operations 8 0 337177656
170 169 number of map misses 8 0 3618703100
171 170 flashback log writes 2 0 3123176560
172 171 serializable aborts 1 0 1793250745
173 172 transaction lock foreground requests 128 0 185785666
174 173 transaction lock foreground wait time 128 0 4033661655
175 174 transaction lock background gets 128 0 3977359732
176 175 transaction lock background get time 128 0 3492664117
177 176 undo change vector size 128 18492520 1465971540
178 177 transaction tables consistent reads - undo records applied 128 0 1054055970
179 178 transaction tables consistent read rollbacks 128 0 1396293204
180 179 data blocks consistent reads - undo records applied 128 1743 2915445793
181 180 no work - consistent read gets 128 1327232 2814375799
182 181 cleanouts only - consistent read gets 128 233 2904875805
183 182 rollbacks only - consistent read gets 128 1582 3133064500
184 183 cleanouts and rollbacks - consistent read gets 128 2 1083903169
185 184 RowCR attempts 128 0 2297882326
186 185 RowCR hits 128 0 3455911385
187 186 RowCR - row contention 128 0 1175104080
188 187 RowCR - resume 128 0 2583359573
189 188 rollback changes - undo records applied 128 6 3616249137
190 189 transaction rollbacks 128 0 3988650402
191 190 immediate (CURRENT) block cleanout applications 128 3541 2668233561
192 191 immediate (CR) block cleanout applications 128 235 3318186802
193 192 deferred (CURRENT) block cleanout applications 128 11757 3388736327
194 193 commit txn count during cleanout 128 629 1405359431
195 194 active txn count during cleanout 128 672 3378035478
196 195 cleanout - number of ktugct calls 128 1021 2577316246
197 196 immediate CR cleanouts (index blocks) 128 0 79013953
198 197 deferred CUR cleanouts (index blocks) 128 0 3827443394
199 198 Commit SCN cached 128 9 2460709063
200 199 Cached Commit SCN referenced 128 2031 2374582560
201 200 auto extends on undo tablespace 128 0 3140365462
202 201 drop segment calls in space pressure 128 0 1500291109
203 202 total number of undo segments dropped 128 0 3623640507
204 203 doubling up with imu segment 128 0 723921798
205 204 tune down retentions in space pressure 128 0 142917891
206 205 steps of tune down ret. in space pressure 128 0 3986010614
207 206 space was found by tune down 128 0 342698422
208 207 space was not found by tune down 128 0 849561940
209 208 commit batch/immediate requested 1 0 640321576
210 209 commit batch requested 1 0 2338940602
211 210 commit immediate requested 1 0 796663742
212 211 commit batch/immediate performed 1 0 3711642721
213 212 commit batch performed 1 0 1391516579
214 213 commit immediate performed 1 0 3494177237
215 214 commit wait/nowait requested 1 0 3800399491
216 215 commit nowait requested 1 0 3697788123
217 216 commit wait requested 1 0 2843300285
218 217 commit wait/nowait performed 1 0 1541239326
219 218 commit nowait performed 1 0 485928497
220 219 commit wait performed 1 0 8047752
221 220 global undo segment hints helped 128 0 1791494885
222 221 global undo segment hints were stale 128 0 3309048233
223 222 local undo segment hints helped 128 0 1047863356
224 223 local undo segment hints were stale 128 0 2070200837
225 224 undo segment header was pinned 128 0 248401831
226 225 total number of times SMON posted 128 40 399827284
227 226 SMON posted for undo segment recovery 128 0 2117898593
228 227 SMON posted for txn recovery for other instances 128 0 1263842513
229 228 SMON posted for instance recovery 128 0 4003178738
230 229 SMON posted for undo segment shrink 128 5 579492169
231 230 SMON posted for dropping temp segment 128 0 3600495721
232 231 SMON posted for supplemental logging cleanup 128 0 1356155743
233 232 IMU commits 128 2238 1914489094
234 233 IMU Flushes 128 46 2099506212
235 234 IMU contention 128 1 2909373607
236 235 IMU recursive-transaction flush 128 0 2591100633
237 236 IMU undo retention flush 128 0 2087226422
238 237 IMU ktichg flush 128 1 1206609541
239 238 IMU bind flushes 128 0 2756376339
240 239 IMU mbu flush 128 0 3723686946
241 240 IMU pool not allocated 128 0 659017805
242 241 IMU CR rollbacks 128 2 2225124543
243 242 IMU undo allocation size 128 7562384 244193920
244 243 IMU Redo allocation size 128 218964 3945654623
245 244 IMU- failed to get a private strand 128 0 2412863545
246 245 Misses for writing mapping 128 0 2725329757
247 246 table scans (short tables) 64 23553 345141973
248 247 table scans (long tables) 64 55 1042655239
249 248 table scans (rowid ranges) 64 0 1303412790
250 249 table scans (cache partitions) 64 0 133545664
251 250 table scans (direct read) 64 0 3355423586
252 251 table scan rows gotten 64 8293109 1400824662
253 252 table scan blocks gotten 64 266436 3741388076
254 253 table fetch by rowid 64 721993 681815839
255 254 table fetch continued row 64 516 1413702393
256 255 cluster key scans 64 180201 3566804345
257 256 cluster key scan block gets 64 573032 2006808371
258 257 rows fetched via callback 64 386770 3299365556
259 258 queue update without cp update 128 0 528828405
260 259 index crx upgrade (prefetch) 64 0 1907142657
261 260 index crx upgrade (found) 64 0 1444049043
262 261 index crx upgrade (positioned) 64 13950 2632952759
263 262 leaf node splits 128 233 1417124053
264 263 leaf node 90-10 splits 128 51 1285138251
265 264 branch node splits 128 0 399027615
266 265 failed probes on index block reclamation 128 0 2687444660
267 266 recursive aborts on index block reclamation 128 0 3075437189
268 267 native hash arithmetic execute 64 0 321852432
269 268 native hash arithmetic fail 64 0 1937851790
270 269 lob reads 8 1795 3626914479
271 270 lob writes 8 1170 2682192071
272 271 lob writes unaligned 8 1170 4224694283
273 272 index fast full scans (full) 64 145 12081473
274 273 index fast full scans (rowid ranges) 64 0 1555464060
275 274 index fast full scans (direct read) 64 0 1635662180
276 275 index fetch by key 128 606208 2808364230
277 276 index scans kdiixs1 128 210759 3589793292
278 277 queue splits 128 0 1168925905
279 278 queue flush 128 0 3150060076
280 279 queue position update 128 0 2568542507
281 280 queue single row 128 0 2713404457
282 281 queue ocp pages 128 0 3899522644
283 282 queue qno pages 128 0 2998782417
284 283 heap block compress 64 940 2326757257
285 284 sql area purged 64 107 2763387941
286 285 sql area evicted 64 2781 3224599022
287 286 CCursor + sql area evicted 64 0 938276153
288 287 session cursor cache hits 64 120994 3678609077
289 288 session cursor cache count 64 4023 568260813
290 289 java call heap total size 1 0 1649812842
291 290 java call heap total size max 1 0 580075922
292 291 java call heap used size 1 0 187277223
293 292 java call heap used size max 1 0 625960724
294 293 java call heap live size 1 0 2431389341
295 294 java call heap live size max 1 0 1298432410
296 295 java call heap object count 1 0 3472185722
297 296 java call heap object count max 1 0 1862536587
298 297 java call heap live object count 1 0 632577394
299 298 java call heap live object count max 1 0 2964139762
300 299 java call heap gc count 1 0 2284697213
301 300 java call heap collected count 1 0 2510746206
302 301 java call heap collected bytes 1 0 3585877838
303 302 java session heap used size 1 0 1661140639
304 303 java session heap used size max 1 0 1726629032
305 304 java session heap live size 1 0 2257986540
306 305 java session heap live size max 1 0 1960804148
307 306 java session heap object count 1 0 4072938679
308 307 java session heap object count max 1 0 469647992
309 308 java session heap live object count 1 0 225051972
310 309 java session heap live object count max 1 0 3755633366
311 310 java session heap gc count 1 0 3363147808
312 311 java session heap collected count 1 0 3795934475
313 312 java session heap collected bytes 1 0 3976434298
314 313 cursor authentications 128 685 4069981174
315 314 queries parallelized 32 0 3315614405
316 315 DML statements parallelized 32 0 3791923855
317 316 DDL statements parallelized 32 0 4115134636
318 317 DFO trees parallelized 32 0 2753131890
319 318 Parallel operations not downgraded 32 0 2410168744
320 319 Parallel operations downgraded to serial 32 0 4286011915
321 320 Parallel operations downgraded 75 to 99 pct 32 0 322858058
322 321 Parallel operations downgraded 50 to 75 pct 32 0 3281923117
323 322 Parallel operations downgraded 25 to 50 pct 32 0 3330456527
324 323 Parallel operations downgraded 1 to 25 pct 32 0 3721245209
325 324 PX local messages sent 32 0 506655048
326 325 PX local messages recv'd 32 0 1437798390
327 326 PX remote messages sent 32 0 2567415317
328 327 PX remote messages recv'd 32 0 2537140420
329 328 buffer is pinned count 72 1189536 1300470380
330 329 buffer is not pinned count 72 1754440 2670431739
331 330 no buffer to keep pinned count 72 1 3528454686
332 331 table lookup prefetch client count 8 0 1926456670
333 332 workarea memory allocated 64 0 2433935387
334 333 workarea executions - optimal 64 22991 3211650785
335 334 workarea executions - onepass 64 0 798730793
336 335 workarea executions - multipass 64 0 3804491469
337 336 parse time cpu 64 1215 206905303
338 337 parse time elapsed 64 4257 1431595225
339 338 parse count (total) 64 62282 63887964
340 339 parse count (hard) 64 4212 143509059
341 340 parse count (failures) 64 53 1118776443
342 341 frame. signature mismatch 64 0 959148951
343 342 execute count 64 161712 2453370665
344 343 bytes sent via SQL*Net to client 1 8436496 2967415760
345 344 bytes received via SQL*Net from client 1 994774 161936656
346 345 SQL*Net roundtrips to/from client 1 6628 159846526
347 346 bytes sent via SQL*Net to dblink 1 0 1622773540
348 347 bytes received via SQL*Net from dblink 1 0 1983609624
349 348 SQL*Net roundtrips to/from dblink 1 0 2420448902
350 349 sorts (memory) 64 44908 2091983730
351 350 sorts (disk) 64 0 2533123502
352 351 sorts (rows) 64 5921565 3757672740
353 352 OTC commit optimization attempts 128 0 2480822770
354 353 OTC commit optimization hits 128 0 4064864054
355 354 OTC commit optimization failure - setup 128 0 3633344886
356 355 Workload Capture: size (in bytes) of recording 1 0 2146126248
357 356 Workload Capture: dbtime 1 0 3533966785
358 357 Workload Capture: user calls 1 0 1228651202
359 358 Workload Capture: user calls flushed 1 0 2236307043
360 359 Workload Capture: unreplayable user calls 1 0 3283547595
361 360 Workload Capture: user txns 1 0 3034767909
362 361 Workload Capture: user logins 1 0 1296083012
363 362 Workload Capture: unsupported user calls 1 0 2224534227
364 363 Workload Capture: errors 1 0 1278172735



上一篇:【Oracle九大性能视图】之8.v$sort_usage_temp表空间的使用情况
下一篇:【Oracle九大性能视图】之4.v$sesstat_根据session id查session的统计信息
189070296,150201289

专业提供Oracle数据库服务、主机、存储、备份、中间件等相关技术支持服务,QQ号:176140749
关注ITPUX技术网微信公众号itpux_com  ,了解本站最新技术资料的分享.

欢迎加QQ群,提供超多高质量Oracle/Unix/Linux技术文档与视频教程的下载。

Oracle/MySQL/Linux群4-5:189070296  150201289  
Oracle/MySQL/Linux群6-8:244609803   522261684   522651731
备注:请勿重复加群,另请注明 from itpux

加群分享视频教程部分如下:

1、公开课视频:Oracle/MySQL数据库工程师职业发展前景讲解(免费)
http://edu.51cto.com/course/7015.html

2、51CTO学院Oracle数据库高级工程师培训(高薪就业.课程介绍)
http://edu.51cto.com/px/train/131?xiaotu

3、Oracle DBA数据库高级工程师培训视频课程1.1(系列78套+七大阶段+上千案例)
套餐视频地址: http://edu.51cto.com/topic/1121.html

4、MySQL数据库(终身门徒)套餐:http://edu.51cto.com/sd/1e1a6

回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表