データディクショナリビュー
_ 
データディクショナリビュー
ステータス情報
v$sysstat を参照する事で、キャッシュヒット率等の確認が出来る。
SQL> select * from v$sysstat ;
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 0 logons cumulative 1 5435 2666645286 1 logons current 1 21 3080465522 2 opened cursors cumulative 1 428905 85052502 3 opened cursors current 1 233 2301954928 4 user commits 1 26337 582481098 5 user rollbacks 1 29 3671147913 6 user calls 1 237324 2882015696 7 recursive calls 1 6136597 2656001462 8 recursive cpu usage 1 94185 4009879262 9 session logical reads 1 6481451 3143187968 10 session stored procedure space 1 0 895486831
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 11 CPU used when call started 128 78667 572264820 12 CPU used by this session 1 99098 24469293 13 DB time 1 50125438 3649082374 14 cluster wait time 1 0 2432034337 15 concurrency wait time 1 3262 3868577743 16 application wait time 1 350 1099569955 17 user I/O wait time 1 22544 3332107451 18 session connect time 1 1195537018 1678473874 19 process last non-idle time 128 1195607942 2049556447 20 session uga memory 1 1.1768E+13 1856888586 21 session uga memory max 1 2409083940 3840343119
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 22 messages sent 128 74726 2486045308 23 messages received 128 74731 1396153242 24 background timeouts 128 547957 961017212 25 session pga memory 1 2882949400 4148600571 26 session pga memory max 1 3233649564 507777907 27 enqueue timeouts 4 0 2425496215 28 enqueue waits 4 1 2307006529 29 enqueue deadlocks 4 0 472183780 30 enqueue requests 4 1864393 2440542518 31 enqueue conversions 4 34589 57745673 32 enqueue releases 4 1864382 2553146675
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 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 53167 3343375620 38 physical read total multi block requests 8 1855 2007302071 39 physical read total bytes 8 790517760 2572010804 40 physical write total IO requests 8 249073 1315894329 41 physical write total multi block requests 8 47095 3540174003 42 physical write total bytes 8 3451066368 2495644835 43 IPC CPU used by this session 32 0 4247517299
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 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 819696 1480709069 48 db block gets from cache 8 819468 4017839461 49 db block gets direct 8 228 95128520 50 consistent gets 8 5661755 4162191256 51 consistent gets from cache 8 5661755 2839918855 52 consistent gets - examination 8 2198547 1701530557 53 consistent gets direct 8 0 420374750 54 physical reads 8 29331 2263124246
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 55 physical reads cache 8 29300 4171507801 56 physical reads direct 8 31 2589616721 57 physical read IO requests 8 19517 789768877 58 physical read bytes 8 240279552 523531786 59 db block changes 8 1038345 916801489 60 consistent changes 8 10777 3876379665 61 recovery blocks read 8 0 3327333035 62 physical writes 8 50536 1190468109 63 physical writes direct 8 248 2699895516 64 physical writes from cache 8 50288 163083034 65 physical write IO requests 8 31420 2904164198
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 66 physical write bytes 8 413990912 3131337131 67 physical writes non checkpoint 8 12720 2602029796 68 summed dirty queue length 8 2238 1683565474 69 DBWR checkpoint buffers written 8 47371 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 0 658143835 74 DBWR transaction table writes 8 4956 2146120386 75 DBWR undo block writes 8 14511 111270822 76 DBWR revisited being-written buffer 8 0 2773697723
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 77 DBWR make free requests 8 0 1237874725 78 DBWR lru scans 8 0 2139101792 79 DBWR checkpoints 8 4 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
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 88 prefetch clients - 32k 8 0 2351177679 89 change write time 8 1131 2838938797 90 redo synch writes 8 113192 1439995281 91 redo synch time 8 1369 4215815172 92 exchange deadlocks 8 0 14632483 93 free buffer requested 8 47978 3411924934 94 dirty buffers inspected 8 1336 1344569897 95 pinned buffers inspected 8 0 833456521 96 hot buffers moved to head of LRU 8 22671 2030212224 97 free buffer inspected 8 26565 941676439 98 commit cleanout failures: write disabled 8 0 2063165199
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 99 commit cleanout failures: block lost 8 46 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 255 3086716748 104 commit cleanouts 8 147835 2934869719 105 commit cleanouts successfully completed 8 147534 3554314084 106 recovery array reads 8 0 2617845348 107 recovery array read time 8 0 2996696667 108 CR blocks created 8 5831 516801181 109 current blocks converted for CR 8 0 3142330676
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 110 switch current to new buffer 8 3274 1312802324 111 write clones created in foreground 8 9 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 9814 297908839 115 physical reads prefetch warmup 8 393 3102888545 116 prefetched blocks aged out before use 8 0 1457461390 117 prefetch warmup blocks aged out before use 8 42 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
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 121 physical reads direct temporary tablespace 8 3 2663793346 122 physical writes direct (lob) 8 8 3308932835 123 physical writes direct temporary tablespace 8 220 996415569 124 cold recycle reads 8 0 657271764 125 shared hash latch upgrades - no wait 8 1002307 2488550671 126 shared hash latch upgrades - wait 8 0 3380476499 127 physical reads for flashback new 8 0 473165409 128 calls to kcmgcs 128 2724 2775936802 129 calls to kcmgrs 128 0 4210206824 130 calls to kcmgas 128 58071 4072914524 131 calls to get snapshot scn: kcmgss 32 1528044 3128650623
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 132 redo blocks read for recovery 2 0 2679943069 133 redo entries 2 384784 3488821837 134 redo size 2 178288464 1236385760 135 redo buffer allocation retries 2 18 1446958922 136 redo wastage 2 12444128 3462806146 137 redo writer latching time 2 0 2166056472 138 redo writes 2 45519 1948353376 139 redo blocks written 2 385504 2391431605 140 redo write time 2 2811 3094453259 141 redo log space requests 2 18 1985754937 142 redo log space wait time 2 20 252430928
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 143 redo log switch interrupts 2 0 674283274 144 redo ordering marks 2 81 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
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 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 4 3048007007
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 165 background checkpoints completed 8 4 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 67188732 1465971540 175 transaction tables consistent reads - undo records applied 128 0 1054055970
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 176 transaction tables consistent read rollbacks 128 0 1396293204 177 data blocks consistent reads - undo records applied 128 10013 2915445793 178 no work - consistent read gets 128 2318594 2814375799 179 cleanouts only - consistent read gets 128 261 2904875805 180 rollbacks only - consistent read gets 128 5816 3133064500 181 cleanouts and rollbacks - consistent read gets 128 15 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 494 3616249137
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 187 transaction rollbacks 128 83 3988650402 188 immediate (CURRENT) block cleanout applications 128 15125 2668233561 189 immediate (CR) block cleanout applications 128 276 3318186802 190 deferred (CURRENT) block cleanout applications 128 113314 3388736327 191 commit txn count during cleanout 128 1660 1405359431 192 active txn count during cleanout 128 1764 3378035478 193 cleanout - number of ktugct calls 128 2557 2577316246 194 immediate CR cleanouts (index blocks) 128 0 79013953 195 deferred CUR cleanouts (index blocks) 128 0 3827443394 196 Commit SCN cached 128 6 2460709063 197 Cached Commit SCN referenced 128 543 2374582560
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 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 83 640321576 207 commit batch requested 1 2 2338940602 208 commit immediate requested 1 81 796663742
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 209 commit batch/immediate performed 1 83 3711642721 210 commit batch performed 1 2 1391516579 211 commit immediate performed 1 81 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
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 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 95 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 43 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 23941 1914489094
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 231 IMU Flushes 128 72376 2099506212 232 IMU contention 128 44 2909373607 233 IMU recursive-transaction flush 128 54 2591100633 234 IMU undo retention flush 128 0 2087226422 235 IMU ktichg flush 128 3 1206609541 236 IMU bind flushes 128 0 2756376339 237 IMU mbu flush 128 0 3723686946 238 IMU pool not allocated 128 5523 659017805 239 IMU CR rollbacks 128 2807 2225124543 240 IMU undo allocation size 128 92548484 244193920 241 IMU Redo allocation size 128 21375860 3945654623
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 242 IMU- failed to get a private strand 128 5523 2412863545 243 Misses for writing mapping 128 0 2725329757 244 table scans (short tables) 64 83762 345141973 245 table scans (long tables) 64 10 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 20864022 1400824662 250 table scan blocks gotten 64 795367 3741388076 251 table fetch by rowid 64 1485654 681815839 252 table fetch continued row 64 9261 1413702393
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 253 cluster key scans 64 132317 3566804345 254 cluster key scan block gets 64 340521 2006808371 255 rows fetched via callback 64 561946 3299365556 256 queue update without cp update 128 0 528828405 257 leaf node splits 128 1113 1417124053 258 leaf node 90-10 splits 128 381 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 16427 3626914479 263 lob writes 8 252 2682192071
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 264 lob writes unaligned 8 252 4224694283 265 index fast full scans (full) 64 1008 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 1010299 2808364230 269 index scans kdiixs1 128 942266 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
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 275 queue qno pages 128 0 2998782417 276 heap block compress 64 1314 2326757257 277 sql area purged 64 67 2763387941 278 sql area aged 64 0 3156625321 279 session cursor cache hits 64 330114 3678609077 280 session cursor cache count 64 46429 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
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 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
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 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 12686 4069981174 306 queries parallelized 32 0 3315614405 307 DML statements parallelized 32 0 3791923855
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 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 0 506655048 317 PX local messages recv'd 32 0 1437798390 318 PX remote messages sent 32 0 2567415317
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 319 PX remote messages recv'd 32 0 2537140420 320 buffer is pinned count 72 1517647 1300470380 321 buffer is not pinned count 72 3141862 2670431739 322 no buffer to keep pinned count 72 1 3528454686 323 table lookup prefetch client count 8 0 1926456670 324 workarea memory allocated 64 0 2433935387 325 workarea executions - optimal 64 115887 3211650785 326 workarea executions - onepass 64 0 798730793 327 workarea executions - multipass 64 0 3804491469 328 parse time cpu 64 7377 206905303 329 parse time elapsed 64 10196 1431595225
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 330 parse count (total) 64 305052 63887964 331 parse count (hard) 64 36193 143509059 332 parse count (failures) 64 38 1118776443 333 frame signature mismatch 64 3 959148951 334 execute count 64 911434 2453370665 335 bytes sent via SQL*Net to client 1 20603790 2967415760 336 bytes received via SQL*Net from client 1 22918226 161936656 337 SQL*Net roundtrips to/from client 1 204596 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
STATISTIC# NAME CLASS VALUE STAT_ID ---------- ----------------------------------------------------------- ---------- ---------- ---------- 341 sorts (memory) 64 187784 2091983730 342 sorts (disk) 64 0 2533123502 343 sorts (rows) 64 8965837 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
本日のツッコミ(全5件) [ツッコミを入れる]
_ num682306flebno [mks682306ngkyt c2NKNzf NVby S0VJCzf]
_ nam2925701flebno [mys2925701uttjr 9Z0dSBM vZhJ WxQ8bbY]
_ num1891429tetcher [mys1891429rtjuny MGqVyUc C0uK ZGBEsRi]
_ num1891429tetcher [mis1891429ngkyt rVV1vK0 JfsV o8eaYfq]
_ num5061128krya [mss5061128ngkyt v4r3uZy iCX2 mQ4Hq9q]