Oracle 查看 session 当前统计信息 脚本 说明

By | 06月25日
Advertisement

Oracle 查看 session 当前统计信息 脚本 说明

http://blog.csdn.net/tianlesoftware/article/details/7689450

一.相关说明

当我们在一个session 上进行事务操作时,如果我们想查看这个事务的相关统计信息,那么可以通过v$mystat ,v$sysstat, v$sesstat, v$statname 字典来查看。 在Oracle 11g中,通过这2个视图,我么可以查看到588个相关的统计信息。

[sql] view plaincopyprint?

  1. SQL> select * from v$version;
  2. BANNER
  3. --------------------------------------------------------------------------------
  4. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
  5. PL/SQL Release 11.2.0.1.0 - Production
  6. CORE 11.2.0.1.0 Production
  7. TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
  8. NLSRTL Version 11.2.0.1.0 - Production
  9. SQL> SELECT COUNT (*)
  10. 2 FROM v$mystat vm, v$sysstat vs
  11. 3 WHERE vm.statistic# = vs.statistic#;
  12. COUNT(*)
  13. ----------
  14. 588

SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 64-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> SELECT COUNT (*) 2 FROM v$mystat vm, v$sysstat vs 3 WHERE vm.statistic# = vs.statistic#; COUNT(*) ---------- 588

v$mystat 字典只能查看当前session 的信息,而v$sesstat 可以查看所有session 的信息,v$sysstat和v$statname 是对统计信息的一个说明。如果不是SYS 用户来执行,那么就需要单独的对这些用户进行赋权操作,具体命令如下:

[sql] view plaincopyprint?

  1. grant select on v_$mystat to anqing;
  2. grant select on v_$statname to anqing;
  3. grant select on v_$sesstat to anqing;
  4. grant select on v_$sysstat to anqing;

grant select on v_$mystat to anqing; grant select on v_$statname to anqing; grant select on v_$sesstat to anqing; grant select on v_$sysstat to anqing;

有关Oracle 视图的相关说明参考:

Oracle 性能相关的几个 视图 和 参数

http://blog.csdn.net/tianlesoftware/article/details/5867276

Oracle 动态性能视图

http://blog.csdn.net/tianlesoftware/article/details/5863191

二.查看当前session 的统计信息:v$mystat 和v$sysstat

我们可以通过如果SQL 来查看当前session 的具体统计信息,从而来帮助我们分析SQL 语句:

[sql] view plaincopyprint?

  1. /* Formatted on 2012/6/25 10:57:20 (QP5 v5.185.11230.41888) */
  2. SELECT vm.sid, vs.name, vm.VALUE
  3. FROM v$mystat vm, v$sysstat vs
  4. WHERE vm.statistic# = vs.statistic#
  5. AND vs.name IN
  6. ('cleanouts only - consistent read gets',
  7. 'session logical reads',
  8. 'physical reads',
  9. 'physical reads direct',
  10. 'redo size');

/* Formatted on 2012/6/25 10:57:20 (QP5 v5.185.11230.41888) */ SELECT vm.sid, vs.name, vm.VALUE FROM v$mystat vm, v$sysstat vs WHERE vm.statistic# = vs.statistic# AND vs.name IN ('cleanouts only - consistent read gets', 'session logical reads', 'physical reads', 'physical reads direct', 'redo size');

这个操作需要注意相关的权限问题。

三.查看所有session的统计信息:v$sessstat 和 v$statname

[sql] view plaincopyprint?

  1. /* Formatted on 2012/6/25 12:39:51 (QP5 v5.185.11230.41888) */
  2. SELECT a.sid, b.name, a.VALUE
  3. FROM v$sesstat a, v$statname b
  4. WHERE a.statistic# = b.statistic#
  5. AND b.name IN
  6. ('cleanouts only - consistent read gets',
  7. 'session logical reads',
  8. 'physical reads',
  9. 'physical reads direct',
  10. 'redo size');

/* Formatted on 2012/6/25 12:39:51 (QP5 v5.185.11230.41888) */ SELECT a.sid, b.name, a.VALUE FROM v$sesstat a, v$statname b WHERE a.statistic# = b.statistic# AND b.name IN ('cleanouts only - consistent read gets', 'session logical reads', 'physical reads', 'physical reads direct', 'redo size');

这里我们也可以加上SID 条件,只查看某个session的的统计信息,获取SID的方法参考:

Oracle 查看当前会话 SESSION ID 方法 说明

http://blog.csdn.net/tianlesoftware/article/details/7240085

[sql] view plaincopyprint?

  1. SELECT a.sid, b.name, a.VALUE
  2. FROM v$sesstat a, v$statname b
  3. WHERE a.statistic# = b.statistic#
  4. AND sid = 139
  5. AND b.name IN
  6. ('cleanouts only - consistent read gets',
  7. 'session logical reads',
  8. 'physical reads',
  9. 'physical reads direct',
  10. 'redo size');

SELECT a.sid, b.name, a.VALUE FROM v$sesstat a, v$statname b WHERE a.statistic# = b.statistic# AND sid = 139 AND b.name IN ('cleanouts only - consistent read gets', 'session logical reads', 'physical reads', 'physical reads direct', 'redo size');

四.所有的统计信息列表

在Oracle 11gR2中总共有588个相关的统计,我们可以根据自己的需要来查看相关的信息,这588个统计的所有列表如下:

[sql] view plaincopyprint?

  1. SQL> SELECT vm.sid, vs.name, vm.VALUE
  2. 2 FROM v$mystat vm, v$sysstat vs
  3. 3 WHERE vm.statistic# = vs.statistic#;
  4. SID NAME VALUE
  5. ---------- ---------------------------------------------------------------- ----------
  6. 139 OS CPU Qt wait time 0
  7. 139 logons cumulative 1
  8. 139 logons current 1
  9. 139 opened cursors cumulative 90
  10. 139 opened cursors current 2
  11. 139 user commits 0
  12. 139 user rollbacks 0
  13. 139 user calls 1180
  14. 139 recursive calls 957
  15. 139 recursive cpu usage 2
  16. 139 pinned cursors current 1
  17. 139 session logical reads 242
  18. 139 session stored procedure space 0
  19. 139 CPU used when call started 36
  20. 139 CPU used by this session 36
  21. 139 DB time 57
  22. 139 cluster wait time 0
  23. 139 concurrency wait time 0
  24. 139 application wait time 0
  25. 139 user I/O wait time 21
  26. 139 scheduler wait time 0
  27. 139 non-idle wait time 21
  28. 139 non-idle wait count 1184
  29. 139 session connect time 1340593250
  30. 139 process last non-idle time 1340593250
  31. 139 session uga memory 1524440
  32. 139 session uga memory max 1975392
  33. 139 messages sent 2
  34. 139 messages received 0
  35. 139 background timeouts 0
  36. 139 remote Oradebug requests 0
  37. 139 session pga memory 2387672
  38. 139 session pga memory max 2846424
  39. 139 recursive system API invocations 0
  40. 139 enqueue timeouts 0
  41. 139 enqueue waits 0
  42. 139 enqueue deadlocks 0
  43. 139 enqueue requests 16
  44. 139 enqueue conversions 0
  45. 139 enqueue releases 15
  46. 139 global enqueue gets sync 0
  47. 139 global enqueue gets async 0
  48. 139 global enqueue get time 0
  49. 139 global enqueue releases 0
  50. 139 physical read total IO requests 14
  51. 139 physical read total multi block requests 0
  52. 139 physical read requests optimized 0
  53. 139 physical read total bytes 114688
  54. 139 physical write total IO requests 0
  55. 139 physical write total multi block requests 0
  56. 139 physical write total bytes 0
  57. 139 cell physical IO interconnect bytes 114688
  58. 139 spare statistic 1 0
  59. 139 spare statistic 2 0
  60. 139 spare statistic 3 0
  61. 139 spare statistic 4 0
  62. 139 IPC CPU used by this session 0
  63. 139 gcs messages sent 0
  64. 139 ges messages sent 0
  65. 139 global enqueue CPU used by this session 0
  66. 139 max cf enq hold time 0
  67. 139 total cf enq hold time 0
  68. 139 total number of cf enq holders 0
  69. 139 db block gets 6
  70. 139 db block gets from cache 6
  71. 139 db block gets from cache (fastpath) 0
  72. 139 db block gets direct 0
  73. 139 consistent gets 236
  74. 139 consistent gets from cache 236
  75. 139 consistent gets from cache (fastpath) 119
  76. 139 consistent gets - examination 106
  77. 139 consistent gets direct 0
  78. 139 physical reads 14
  79. 139 physical reads cache 14
  80. 139 physical read flash cache hits 0
  81. 139 physical reads direct 0
  82. 139 physical read IO requests 14
  83. 139 physical read bytes 114688
  84. 139 db block changes 8
  85. 139 consistent changes 0
  86. 139 recovery blocks read 0
  87. 139 recovery blocks read for lost write detection 0
  88. 139 recovery blocks skipped lost write checks 0
  89. 139 physical writes 0
  90. 139 physical writes direct 0
  91. 139 physical writes from cache 0
  92. 139 physical write IO requests 0
  93. 139 flash cache inserts 0
  94. 139 physical reads direct temporary tablespace 0
  95. 139 physical writes direct temporary tablespace 0
  96. 139 physical write bytes 0
  97. 139 flash cache eviction: invalidated 0
  98. 139 flash cache eviction: buffer pinned 0
  99. 139 flash cache eviction: aged out 0
  100. 139 flash cache insert skip: not current 0
  101. 139 flash cache insert skip: DBWR overloaded 0
  102. 139 flash cache insert skip: exists 0
  103. 139 flash cache insert skip: not useful 0
  104. 139 flash cache insert skip: modification 0
  105. 139 flash cache insert skip: corrupt 0
  106. 139 db corrupt blocks detected 0
  107. 139 db corrupt blocks recovered 0
  108. 139 physical writes non checkpoint 0
  109. 139 summed dirty queue length 0
  110. 139 DBWR checkpoint buffers written 0
  111. 139 DBWR thread checkpoint buffers written 0
  112. 139 DBWR tablespace checkpoint buffers written 0
  113. 139 DBWR parallel query checkpoint buffers written 0
  114. 139 DBWR object drop buffers written 0
  115. 139 DBWR transaction table writes 0
  116. 139 DBWR undo block writes 0
  117. 139 DBWR revisited being-written buffer 0
  118. 139 DBWR lru scans 0
  119. 139 DBWR checkpoints 0
  120. 139 DBWR fusion writes 0
  121. 139 prefetch clients - keep 0
  122. 139 prefetch clients - recycle 0
  123. 139 prefetch clients - default 0
  124. 139 prefetch clients - 2k 0
  125. 139 prefetch clients - 4k 0
  126. 139 prefetch clients - 8k 0
  127. 139 prefetch clients - 16k 0
  128. 139 prefetch clients - 32k 0
  129. 139 change write time 0
  130. 139 redo synch writes 1
  131. 139 redo synch time 0
  132. 139 exchange deadlocks 0
  133. 139 free buffer requested 15
  134. 139 dirty buffers inspected 0
  135. 139 pinned buffers inspected 0
  136. 139 hot buffers moved to head of LRU 0
  137. 139 free buffer inspected 0
  138. 139 commit cleanout failures: write disabled 0
  139. 139 commit cleanout failures: block lost 0
  140. 139 commit cleanout failures: cannot pin 0
  141. 139 commit cleanout failures: hot backup in progress 0
  142. 139 commit cleanout failures: buffer being written 0
  143. 139 commit cleanout failures: callback failure 0
  144. 139 commit cleanouts 2
  145. 139 commit cleanouts successfully completed 2
  146. 139 recovery array reads 0
  147. 139 recovery array read time 0
  148. 139 CR blocks created 0
  149. 139 current blocks converted for CR 0
  150. 139 switch current to new buffer 1
  151. 139 write clones created in foreground 0
  152. 139 write clones created in background 0
  153. 139 write clones created for recovery 0
  154. 139 recovery block gets from cache 0
  155. 139 physical reads cache prefetch 0
  156. 139 physical reads prefetch warmup 0
  157. 139 prefetched blocks aged out before use 0
  158. 139 prefetch warmup blocks aged out before use 0
  159. 139 prefetch warmup blocks flushed out before use 0
  160. 139 physical reads retry corrupt 0
  161. 139 physical reads direct (lob) 0
  162. 139 physical writes direct (lob) 0
  163. 139 cold recycle reads 0
  164. 139 shared hash latch upgrades - no wait 3
  165. 139 shared hash latch upgrades - wait 0
  166. 139 physical reads for flashback new 0
  167. 139 calls to kcmgcs 11
  168. 139 calls to kcmgrs 0
  169. 139 calls to kcmgas 3
  170. 139 calls to get snapshot scn: kcmgss 94
  171. 139 redo blocks read for recovery 0
  172. 139 redo k-bytes read for recovery 0
  173. 139 redo k-bytes read for terminal recovery 0
  174. 139 redo entries 4
  175. 139 redo size 1756
  176. 139 redo entries for lost write detection 0
  177. 139 redo size for lost write detection 0
  178. 139 redo size for direct writes 0
  179. 139 redo buffer allocation retries 0
  180. 139 redo wastage 0
  181. 139 redo writes 0
  182. 139 redo blocks written 0
  183. 139 redo write time 0
  184. 139 redo blocks checksummed by FG (exclusive) 0
  185. 139 redo blocks checksummed by LGWR 0
  186. 139 redo log space requests 0
  187. 139 redo log space wait time 0
  188. 139 redo ordering marks 0
  189. 139 redo subscn max counts 0
  190. 139 redo write broadcast ack time 0
  191. 139 redo write broadcast ack count 0
  192. 139 redo k-bytes read total 0
  193. 139 redo k-bytes read (memory) 0
  194. 139 redo k-bytes read total by LNS 0
  195. 139 redo k-bytes read (memory) by LNS 0
  196. 139 file io service time 0
  197. 139 file io wait time 210653
  198. 139 gc cr blocks served 0
  199. 139 gc cr block read wait time 0
  200. 139 gc cr block build time 0
  201. 139 gc cr block flush time 0
  202. 139 gc cr block send time 0
  203. 139 gc current blocks served 0
  204. 139 gc current block pin time 0
  205. 139 gc current block flush time 0
  206. 139 gc current block send time 0
  207. 139 gc cr blocks received 0
  208. 139 gc cr block receive time 0
  209. 139 gc current blocks received 0
  210. 139 gc current block receive time 0
  211. 139 gc local grants 0
  212. 139 gc remote grants 0
  213. 139 gc kbytes sent 0
  214. 139 gc kbytes saved 0
  215. 139 gc blocks compressed 0
  216. 139 gc blocks lost 0
  217. 139 gc claim blocks lost 0
  218. 139 gc blocks corrupt 0
  219. 139 gc CPU used by this session 0
  220. 139 gc reader bypass grants 0
  221. 139 total number of slots 0
  222. 139 Effective IO time 0
  223. 139 Number of read IOs issued 0
  224. 139 background checkpoints started 0
  225. 139 background checkpoints completed 0
  226. 139 number of map operations 0
  227. 139 number of map misses 0
  228. 139 flashback log writes 0
  229. 139 flashback log write bytes 0
  230. 139 cell physical IO bytes saved during optimized file creation 0
  231. 139 cell physical IO bytes saved during optimized RMAN file restore 0
  232. 139 cell physical IO bytes eligible for predicate offload 0
  233. 139 cell physical IO bytes saved by storage index 0
  234. 139 cell smart IO session cache lookups 0
  235. 139 cell smart IO session cache hits 0
  236. 139 cell smart IO session cache soft misses 0
  237. 139 cell smart IO session cache hard misses 0
  238. 139 cell smart IO session cache hwm 0
  239. 139 cell num smart IO sessions in rdbms block IO due to user 0
  240. 139 cell num smart IO sessions in rdbms block IO due to big payload 0
  241. 139 cell num smart IO sessions using passthru mode due to user 0
  242. 139 cell num smart IO sessions using passthru mode due to cellsrv 0
  243. 139 cell num smart IO sessions using passthru mode due to timezone 0
  244. 139 cell num smart file creation sessions using rdbms block IO mode 0
  245. 139 cell physical IO interconnect bytes returned by smart scan 0
  246. 139 cell session smart scan efficiency 0
  247. 139 Batched IO vector read count 0
  248. 139 Batched IO vector block count 0
  249. 139 Batched IO single block count 0
  250. 139 Batched IO zero block count 0
  251. 139 Batched IO block miss count 0
  252. 139 Batched IO double miss count 0
  253. 139 Batched IO (full) vector count 0
  254. 139 Batched IO (space) vector count 0
  255. 139 Batched IO (bound) vector count 0
  256. 139 Batched IO same unit count 0
  257. 139 Batched IO buffer defrag count 0
  258. 139 Batched IO slow jump count 0
  259. 139 shared io pool buffer get success 0
  260. 139 shared io pool buffer get failure 0
  261. 139 temp space allocated (bytes) 0
  262. 139 serializable aborts 0
  263. 139 transaction lock foreground requests 0
  264. 139 transaction lock foreground wait time 0
  265. 139 transaction lock background gets 0
  266. 139 transaction lock background get time 0
  267. 139 undo change vector size 492
  268. 139 transaction tables consistent reads - undo records applied 0
  269. 139 transaction tables consistent read rollbacks 0
  270. 139 data blocks consistent reads - undo records applied 0
  271. 139 no work - consistent read gets 119
  272. 139 cleanouts only - consistent read gets 0
  273. 139 rollbacks only - consistent read gets 0
  274. 139 cleanouts and rollbacks - consistent read gets 0
  275. 139 RowCR attempts 0
  276. 139 RowCR hits 0
  277. 139 RowCR - row contention 0
  278. 139 RowCR - resume 0
  279. 139 rollback changes - undo records applied 0
  280. 139 transaction rollbacks 0
  281. 139 immediate (CURRENT) block cleanout applications 0
  282. 139 immediate (CR) block cleanout applications 0
  283. 139 deferred (CURRENT) block cleanout applications 1
  284. 139 commit txn count during cleanout 0
  285. 139 active txn count during cleanout 0
  286. 139 cleanout - number of ktugct calls 0
  287. 139 immediate CR cleanouts (index blocks) 0
  288. 139 deferred CUR cleanouts (index blocks) 0
  289. 139 Commit SCN cached 0
  290. 139 Cached Commit SCN referenced 0
  291. 139 Block Cleanout Optim referenced 0
  292. 139 min active SCN optimization applied on CR 0
  293. 139 auto extends on undo tablespace 0
  294. 139 drop segment calls in space pressure 0
  295. 139 total number of undo segments dropped 0
  296. 139 doubling up with imu segment 0
  297. 139 tune down retentions in space pressure 0
  298. 139 steps of tune down ret. in space pressure 0
  299. 139 space was found by tune down 0
  300. 139 space was not found by tune down 0
  301. 139 commit batch/immediate requested 0
  302. 139 commit batch requested 0
  303. 139 commit immediate requested 0
  304. 139 commit batch/immediate performed 0
  305. 139 commit batch performed 0
  306. 139 commit immediate performed 0
  307. 139 commit wait/nowait requested 0
  308. 139 commit nowait requested 0
  309. 139 commit wait requested 0
  310. 139 commit wait/nowait performed 0
  311. 139 commit nowait performed 0
  312. 139 commit wait performed 0
  313. 139 global undo segment hints helped 0
  314. 139 global undo segment hints were stale 0
  315. 139 local undo segment hints helped 0
  316. 139 local undo segment hints were stale 0
  317. 139 undo segment header was pinned 0
  318. 139 IMU commits 0
  319. 139 IMU Flushes 0
  320. 139 IMU contention 0
  321. 139 IMU recursive-transaction flush 0
  322. 139 IMU undo retention flush 0
  323. 139 IMU ktichg flush 0
  324. 139 IMU bind flushes 0
  325. 139 IMU mbu flush 0
  326. 139 IMU pool not allocated 0
  327. 139 IMU CR rollbacks 0
  328. 139 IMU undo allocation size 144
  329. 139 IMU Redo allocation size 0
  330. 139 IMU- failed to get a private strand 0
  331. 139 Misses for writing mapping 0
  332. 139 segment dispenser load tasks 0
  333. 139 segemnt dispenser load empty 0
  334. 139 segment dispenser allocations 0
  335. 139 segment cfs allocations 0
  336. 139 segment chunks allocation from disepnser 0
  337. 139 segment total chunk allocation 0
  338. 139 TBS Extension: tasks created 0
  339. 139 TBS Extension: tasks executed 0
  340. 139 TBS Extension: files extended 0
  341. 139 TBS Extension: bytes extended 0
  342. 139 total number of times SMON posted 0
  343. 139 SMON posted for undo segment recovery 0
  344. 139 SMON posted for txn recovery for other instances 0
  345. 139 SMON posted for instance recovery 0
  346. 139 SMON posted for undo segment shrink 0
  347. 139 SMON posted for dropping temp segment 0
  348. 139 segment prealloc tasks 0
  349. 139 segment prealloc ops 0
  350. 139 segment prealloc bytes 0
  351. 139 segment prealloc time (ms) 0
  352. 139 segment prealloc ufs2cfs bytes 0
  353. 139 table scans (short tables) 4
  354. 139 table scans (long tables) 0
  355. 139 table scans (rowid ranges) 0
  356. 139 table scans (cache partitions) 0
  357. 139 table scans (direct read) 0
  358. 139 table scan rows gotten 80
  359. 139 table scan blocks gotten 3
  360. 139 table fetch by rowid 58
  361. 139 table fetch continued row 0
  362. 139 cluster key scans 16
  363. 139 cluster key scan block gets 16
  364. 139 rows fetched via callback 4
  365. 139 cell scans 0
  366. 139 cell blocks processed by cache layer 0
  367. 139 cell blocks processed by txn layer 0
  368. 139 cell blocks processed by data layer 0
  369. 139 cell blocks processed by index layer 0
  370. 139 cell commit cache queries 0
  371. 139 cell transactions found in commit cache 0
  372. 139 cell blocks helped by commit cache 0
  373. 139 cell blocks helped by minscn optimization 0
  374. 139 cell blocks skipped due to chained rows 0
  375. 139 cell simulated physical IO bytes eligible for predicate offload 0
  376. 139 cell simulated physical IO bytes returned by predicate offload 0
  377. 139 cell simulated session smart scan efficiency 0
  378. 139 cell CUs sent uncompressed 0
  379. 139 cell CUs sent compressed 0
  380. 139 cell CUs sent head piece 0
  381. 139 cell CUs processed for uncompressed 0
  382. 139 cell CUs processed for compressed 0
  383. 139 cell IO uncompressed bytes 0
  384. 139 queue update without cp update 0
  385. 139 index crx upgrade (prefetch) 0
  386. 139 index crx upgrade (found) 0
  387. 139 index crx upgrade (positioned) 0
  388. 139 leaf node splits 0
  389. 139 leaf node 90-10 splits 0
  390. 139 branch node splits 0
  391. 139 root node splits 0
  392. 139 failed probes on index block reclamation 0
  393. 139 recursive aborts on index block reclamation 0
  394. 139 index reclamation/extension switch 0
  395. 139 native hash arithmetic execute 0
  396. 139 native hash arithmetic fail 0
  397. 139 lob reads 0
  398. 139 lob writes 0
  399. 139 lob writes unaligned 0
  400. 139 cell index scans 0
  401. 139 index fast full scans (full) 0
  402. 139 index fast full scans (rowid ranges) 0
  403. 139 index fast full scans (direct read) 0
  404. 139 index fetch by key 24
  405. 139 index scans kdiixs1 44
  406. 139 queue splits 0
  407. 139 queue flush 0
  408. 139 queue position update 0
  409. 139 queue single row 0
  410. 139 queue ocp pages 0
  411. 139 queue qno pages 0
  412. 139 heap block compress 0
  413. 139 HSC OLTP Space Saving 0
  414. 139 HSC OLTP Compressed Blocks 0
  415. 139 HSC IDL Compressed Blocks 0
  416. 139 HSC Compressed Segment Block Changes 0
  417. 139 HSC Heap Segment Block Changes 2
  418. 139 HSC OLTP Non Compressible Blocks 0
  419. 139 HSC OLTP positive compression 0
  420. 139 HSC OLTP negative compression 0
  421. 139 HSC OLTP recursive compression 0
  422. 139 HSC OLTP inline compression 0
  423. 139 HSC OLTP Drop Column 0
  424. 139 HSC OLTP Compression skipped rows 0
  425. 139 HSC OLTP compression block checked 0
  426. 139 Heap Segment Array Inserts 0
  427. 139 Heap Segment Array Updates 0
  428. 139 securefile allocation bytes 0
  429. 139 securefile allocation chunks 0
  430. 139 securefile direct read bytes 0
  431. 139 securefile direct write bytes 0
  432. 139 securefile direct read ops 0
  433. 139 securefile direct write ops 0
  434. 139 securefile inode read time 0
  435. 139 securefile inode write time 0
  436. 139 securefile inode ioreap time 0
  437. 139 securefile bytes non-transformed 0
  438. 139 securefile number of non-transformed flushes 0
  439. 139 securefile bytes encrypted 0
  440. 139 securefile bytes cleartext 0
  441. 139 securefile compressed bytes 0
  442. 139 securefile uncompressed bytes 0
  443. 139 securefile bytes deduplicated 0
  444. 139 securefile create dedup set 0
  445. 139 securefile destroy dedup set 0
  446. 139 securefile add dedupd lob to set 0
  447. 139 securefile rmv from dedup set 0
  448. 139 securefile reject deduplication 0
  449. 139 securefile dedup prefix hash match 0
  450. 139 securefile number of flushes 0
  451. 139 securefile dedup flush too low 0
  452. 139 securefile dedup callback oper final 0
  453. 139 securefile dedup hash collision 0
  454. 139 securefile dedup fits inline 0
  455. 139 CC CUs Compressed 0
  456. 139 CC Query Low CUs Compressed 0
  457. 139 CC Query High CUs Compressed 0
  458. 139 CC Archive CUs Compressed 0
  459. 139 CC Compressed Length Compressed 0
  460. 139 CC Decompressed Length Compressed 0
  461. 139 CC Rows Compressed 0
  462. 139 CC Rows Not Compressed 0
  463. 139 CC CU Row Pieces Compressed 0
  464. 139 CC CUs Decompressed 0
  465. 139 CC Query Low CUs Decompressed 0
  466. 139 CC Query High CUs Decompressed 0
  467. 139 CC Archive CUs Decompressed 0
  468. 139 CC Compressed Length Decompressed 0
  469. 139 CC Decompressed Length Decompressed 0
  470. 139 CC Columns Decompressed 0
  471. 139 CC Total Columns for Decompression 0
  472. 139 CC Total Rows for Decompression 0
  473. 139 CC Pieces Buffered for Decompression 0
  474. 139 CC Total Pieces for Decompression 0
  475. 139 CC DML CUs Decompressed 0
  476. 139 CC Scan CUs Decompressed 0
  477. 139 CC Turbo Scan CUs Decompressed 0
  478. 139 CC Rowid CUs Decompressed 0
  479. 139 CC Analyze CUs Decompressed 0
  480. 139 CC Dump CUs Decompressed 0
  481. 139 CC Check CUs Decompressed 0
  482. 139 CC Analyzer Calls 0
  483. 139 sql area purged 2
  484. 139 sql area evicted 0
  485. 139 CCursor + sql area evicted 0
  486. 139 No. of Encrypt ops 0
  487. 139 No. of Decrypt ops 0
  488. 139 No. of XS Sessions Created 0
  489. 139 No. of XS Sessions Attached 0
  490. 139 No. of Namespaces Created 0
  491. 139 No. of User Callbacks Executed 0
  492. 139 No. of Roles Enabled or Disabled 0
  493. 139 No. of Principal Cache Misses 0
  494. 139 No. of Principal Invalidations 0
  495. 139 DX/BB enqueue lock foreground requests 0
  496. 139 DX/BB enqueue lock foreground wait time 0
  497. 139 DX/BB enqueue lock background gets 0
  498. 139 DX/BB enqueue lock background get time 0
  499. 139 Clusterwide global transactions 0
  500. 139 Clusterwide global transactions spanning RAC nodes 0
  501. 139 Forwarded 2PC commands across RAC nodes 0
  502. 139 GTX processes spawned by autotune 0
  503. 139 GTX processes stopped by autotune 0
  504. 139 session cursor cache hits 36
  505. 139 session cursor cache count 20
  506. 139 java call heap total size 0
  507. 139 java call heap total size max 0
  508. 139 java call heap used size 0
  509. 139 java call heap used size max 0
  510. 139 java call heap live size 0
  511. 139 java call heap live size max 0
  512. 139 java call heap object count 0
  513. 139 java call heap object count max 0
  514. 139 java call heap live object count 0
  515. 139 java call heap live object count max 0
  516. 139 java call heap gc count 0
  517. 139 java call heap collected count 0
  518. 139 java call heap collected bytes 0
  519. 139 java session heap used size 0
  520. 139 java session heap used size max 0
  521. 139 java session heap live size 0
  522. 139 java session heap live size max 0
  523. 139 java session heap object count 0
  524. 139 java session heap object count max 0
  525. 139 java session heap live object count 0
  526. 139 java session heap live object count max 0
  527. 139 java session heap gc count 0
  528. 139 java session heap collected count 0
  529. 139 java session heap collected bytes 0
  530. 139 cursor authentications 8
  531. 139 queries parallelized 0
  532. 139 DML statements parallelized 0
  533. 139 DDL statements parallelized 0
  534. 139 DFO trees parallelized 0
  535. 139 Parallel operations not downgraded 0
  536. 139 Parallel operations downgraded to serial 0
  537. 139 Parallel operations downgraded 75 to 99 pct 0
  538. 139 Parallel operations downgraded 50 to 75 pct 0
  539. 139 Parallel operations downgraded 25 to 50 pct 0
  540. 139 Parallel operations downgraded 1 to 25 pct 0
  541. 139 PX local messages sent 0
  542. 139 PX local messages recv'd 0
  543. 139 PX remote messages sent 0
  544. 139 PX remote messages recv'd 0
  545. 139 buffer is pinned count 9
  546. 139 buffer is not pinned count 194
  547. 139 no buffer to keep pinned count 0
  548. 139 workarea memory allocated 1245
  549. 139 workarea executions - optimal 17
  550. 139 workarea executions - onepass 0
  551. 139 workarea executions - multipass 0
  552. 139 LOB table id lookup cache misses 0
  553. 139 parse time cpu 3
  554. 139 parse time elapsed 26
  555. 139 parse count (total) 87
  556. 139 parse count (hard) 8
  557. 139 parse count (failures) 2
  558. 139 parse count (describe) 0
  559. 139 frame signature mismatch 0
  560. 139 execute count 97
  561. 139 bytes sent via SQL*Net to client 384449
  562. 139 bytes received via SQL*Net from client 20349
  563. 139 SQL*Net roundtrips to/from client 1164
  564. 139 bytes sent via SQL*Net to dblink 0
  565. 139 bytes received via SQL*Net from dblink 0
  566. 139 SQL*Net roundtrips to/from dblink 0
  567. 139 bytes via SQL*Net vector to client 0
  568. 139 bytes via SQL*Net vector from client 0
  569. 139 bytes via SQL*Net vector to dblink 0
  570. 139 bytes via SQL*Net vector from dblink 0
  571. 139 sorts (memory) 39
  572. 139 sorts (disk) 0
  573. 139 sorts (rows) 21
  574. 139 OTC commit optimization attempts 0
  575. 139 OTC commit optimization hits 0
  576. 139 OTC commit optimization failure - setup 0
  577. 139 cell flash cache read hits 0
  578. 139 Workload Capture: size (in bytes) of recording 0
  579. 139 Workload Capture: dbtime 0
  580. 139 Workload Capture: user calls 0
  581. 139 Workload Capture: user calls flushed 0
  582. 139 Workload Capture: unreplayable user calls 0
  583. 139 Workload Capture: user txns 0
  584. 139 Workload Capture: user logins 0
  585. 139 Workload Capture: unsupported user calls 0
  586. 139 Workload Capture: errors 0
  587. 139 Workload Replay: dbtime 0
  588. 139 Workload Replay: network time 0
  589. 139 Workload Replay: think time 0
  590. 139 Workload Replay: time gain 0
  591. 139 Workload Replay: time loss 0
  592. 139 Workload Replay: user calls 0
  593. 139 Workload Replay: deadlocks resolved 0
  594. 588 rows selected.
  595. SQL>

Similar Posts:

  • oracle表分析-收集统计信息

    1.分析更新表的统计信息,,有可能导致执行计划改变.. 2.以的analyze table abc compute statistics;这条为例,生成的统计信息会存在于user_tables这个视图, select * from user_tables where table_name='ABC'; 观察一下NUM_ROWS,BLOCKS,AVG_SPACE,AVG_ROW_LEN几列你就会明白,这就是变化.分析完表之后,会发现DBA_tables 视图中,以前很多列值是空的,现在开始有数据了

  • oracle 查看session

    1.v$session:连接将产生会话,当前会话的信息保存在v$session中,连接断开后消失: 2.v$session_wait:将当前会话中正在等待的会话状态复制一份到视图,断开后消失(等待会话生命周期最后1次等待): 3.v$session_wait_history:保存每个活动session在v$session_wait中最近10次的等待事件(等待会话生命周期最后10次等待): 4.ASH(v$active_session_history):在内存中保存,每秒从v$session_wa

  • oracle 10g_10.2.0.5如何合理高效使用dbms_stats收集统计信息呢_part2

    前言: 上篇,我们主要测试了dbms_stats过程create_stat_table,export_table_stats,import_table_stats的用法,本文我们继续学习dbms_stats包的过程delele_table_stats,restore_table_stats 测试目标: 理解与掌握dbms_stats包的过程delele_table_stats,restore_table_stats的用法及应用场景. 测试步骤: 1,创建测试表并插入数据 SQL> conn tb

  • Oracle 10g统计信息增加恢复功能(二)

    从10g开始,Oracle可以恢复错误的统计信息. 这篇简单恢复统计信息的相关函数和过程. 上一篇简单介绍了恢复统计信息的方法,这一篇介绍相关的视图和函数. 在上一篇提到了如果要恢复统计信息,必须提供恢复的TIMESTAMP,而这个时间戳通过USER_TABLES视图是无法获得准确信息的,因此对于表而言,可以查询USER_TAB_STATS_HISTORY视图,而对于SCHEMA和数据库级的恢复时间戳可以查询DBA_OPTSTAT_OPERATIONS视图: SQL> SELECT OPERAT

  • Oracle 10g Statistic数据统计

    http://www.ej38.com/showinfo/Oracle-108709.html Oracle 10g statistic数据统计,Oracle会根据这些统计信息来决定是走RBO(Rule-BasedOptimization),还是走CBO(Cost-BasedOptimization),会去选择哪种执行计划更划算,影响是否走相关的索引等.如果是CBO的话,它依靠准确的(或者说比较准确的)统计信息来产生优化的执行路径,如果没有做过统计,CBO也就没有做cost评估的依据,所以虽然是

  • 04 系统和对象统计信息

    本章提要 ---------------------------------------------------- 工具包 dbms_stats 系统统计 对象统计 通用服务 ---------------------------------------------------- 实际上, 仅知道要处理的SQL语句和它引用的对象结构, 查询优化器还是无法提供高效的执行计划, 优化器还必须要量化待处理的数据量. 4.1 工具包 dbms_stats 简介 过去, 对象统计是通过SQL语句ANALY

  • Oracle里收集与查看统计信息的方法

    -- oracle 8.1.5 之前 analyze 收集统计信息 analyze table tt1 estimate statistics sample 15 percent for table -估算模式收集表统计信息 analyze table tt1 compute statistics for table --- 计算模式收集表统计信息 analyze table tt1 comupte statistics for column object_id --对指定列收集统计信息 ana

  • 查看统计信息_脚本

    我们要去 check 为啥过期了 ----看一下 为啥 统计信息过期了,简化了,自己考虑复杂情况 ---- select * from all_tab_modifications where table_owner in (select object_owner from plan_table) and table_name in (select object_name from plan_table ) and (inserts > 0 or updates > 0 or deletes &

  • 如何在sqlplus中查看oracle数据库sql语句执行计划,执行时间和统计信息

    #设置屏幕行宽度 SQL>set linesize 190 #设置sqlplus打印执行计划和统计信息 SQL>set autotrace on #设置sqlplus打印执行时间 SQL>set timing on

  • oracle查看sql执行计划和统计信息

    --获取sql的执行计划以及统计信息,不显示查询信息 SQL> set autotrace traceonly; SQL> select * from test; 执行计划 ---------------------------------------------------------- Plan hash value: 1357081020 --------------------------------------------------------------------------

Tags: