[Oracle Utility] Mystats Utility by Adrian Billington (www.oracle-developer.net)
Adrian Billington 在他的个人网站www.oracle-developer.net上给出了一个很有意思的小工具，Mystats Utility, 用来帮助我们查看某个操作消耗的系统资源。
下面是他的代码 (mystats_pkg), 其实也很简单，主要就是在待考察操作执行前后各采一个样(snapshot),然后比较这两个snapshot统计数据的差值，就可以得出待考察操作消耗的资源了。
Snapshot的数据主要来源于两个动态视图v$mystat和v$latch （如下）, 由于这两个视图统计数据是累计的，因此需要求出两个snapshot之间的差值才能得出某个操作带来的性能影响。
1: select 'STAT..' || a.name
2: , b.value
3: from v$statname a
4: , v$mystat b5: where a.statistic# = b.statistic#
6: union all
7: select 'LATCH..' || name
8: , gets 9: from v$latch;
-- Adrian Billionton 的 Mystats Utility 代码，
1: 2: create package mystats_pkg authid current_user as
3: 4: /*
5: || ----------------------------------------------------------------------------
6: ||
7: || Name: MYSTATS_PKG
8: ||
9: || Description: PL/SQL-only version of Jonathan Lewis's SNAP_MY_STATS package.
10: || This package is used to output the resource usage as recorded
11: || in V$MYSTAT and V$LATCH.
12: ||
13: || Key Differences
14: || ---------------
15: ||
16: || a) All logic is encapsulated in a single PL/SQL package
17: || (no stats view);
18: ||
19: || b) This uses invoker rights and dynamic SQL to workaround
20: || the sites where developers cannot get explicit grants
21: || on the required V$ views, but instead have access via
22: || roles or other privileges;
23: ||
24: || c) This includes latch statistics and makes use of Tom
25: || Kyte's RUNSTATS method for distinguising between stats
26: || and latches;
27: ||
28: || d) This requires at least version 9.2 to run because it
29: || makes use of string-indexed and multi-level associative
30: || arrays to store the intermediate stats.
31: ||
32: || Usage: 1. Output all statistics
33: || -------------------------------------------------------------
34: || exec mystats_pkg.ms_start;
35: || --<do some work>--
36: || exec mystats_pkg.ms_stop;
37: ||
38: || 2. Output statistics with delta values >= 1,000
39: || -------------------------------------------------------------
40: || exec mystats_pkg.ms_start;
41: || --<do some work>--
42: || exec mystats_pkg.ms_stop(1000);
43: ||
44: || See http://www.jlcomp.demon.co.uk/snapshot.html for original
45: || version.
46: ||
47: || Notes: 1. Serveroutput must be on (and set higher than default);
48: ||
49: || 2. Jonathan Lewis recommends that calls to the snapshot package
50: || are made independently to ensure all stats are recorded
51: || between calls (see link above for more detail).
52: ||
53: || Adrian Billington, www.oracle-developer.net.
54: ||
55: || ----------------------------------------------------------------------------
56: */
57: 58: procedure ms_start;
59: 60: procedure ms_stop( p_threshold in integer default 0 );
61: 62: end mystats_pkg;
63: / 64: 65: 66: create package body mystats_pkg as
67: 68: /*
69: || A range of (sub)types for capturing statistics information...
70: */
71: subtype st_statname is varchar2(80);
72: subtype st_output is varchar2(255);
73: 74: type aat_statistic is table of integer
75: index by st_statname;
76: 77: type aat_mystats is table of aat_statistic
78: index by pls_integer;
79: 80: /*
81: || This is the "mystats array" to hold two snapshots...
82: */
83: ga_mystats aat_mystats; 84: 85: /*
86: || Array offsets into the main mystats array, used to
87: || determine the start and end points of a run...
88: */
89: c_run1 constant pls_integer := 1; 90: c_run2 constant pls_integer := 2; 91: 92: /*
93: || Globals for elapsed time calculation...
94: */
95: g_start_time date;
96: g_end_time date;
97: 98: ------------------------------------------------------------------------------
99: procedure ms_snap( p_run in pls_integer,
100: p_mystats in out nocopy aat_mystats,
101: p_time in out date ) is
102: 103: type rt_statistic is record
104: ( name st_statname105: , value integer );
106: 107: rc_stat sys_refcursor; 108: r_stat rt_statistic; 109: 110: begin
111: 112: p_time := sysdate; 113: 114: /*
115: || Dynamic SQL (combined with invoker rights in the spec) works around
116: || the need to have explicit select granted on the referenced v$ views.
117: || Of course, we still need access granted via a role or other privilege
118: || but I've always been able to get the latter and rarely the former...
119: */
120: open rc_stat for 'select ''STAT..'' || a.name
121: , b.value 122: from v$statname a 123: , v$mystat b 124: where a.statistic# = b.statistic# 125: union all126: select ''LATCH.'' || name
127: , gets 128: from v$latch'; 129: loop130: fetch rc_stat into r_stat;
131: exit when rc_stat%notfound;
132: p_mystats(p_run)(r_stat.name) := r_stat.value;
133: end loop;
134: close rc_stat;
135: end ms_snap;
136: 137: ------------------------------------------------------------------------------
138: procedure ms_report( p_threshold in pls_integer ) is
139: 140: v_name st_statname; --<-- offset for varchar2 associative arrays
141: v_indx pls_integer; --<-- offset for pls_integer associative arrays
142: v_value number; --<-- snapshot value for a statistic
143: 144: /*
145: || Downside of using associative arrays is that we have to sort
146: || the output. So here's a couple of types and a variable to enable us
147: || to do that...
148: */
149: type aat_mystats_output is table of varchar2(255)
150: index by st_statname;
151: type aat_mystats_sorted is table of aat_mystats_output
152: index by pls_integer;
153: aa_mystats_sorted aat_mystats_sorted; 154: 155: /*
156: || Procedure to add a statistic to the sorted mystats array...
157: */
158: procedure sort ( p_statname in st_statname,
159: p_value in number ) is
160: v_offset pls_integer; 161: v_output st_output;162: begin
163: /*
164: || Workaround the offset limits of a PLS_INTEGER associative array...
165: */
166: v_offset := least(abs(p_value),2147483647);167: v_output := rpad(p_statname,60) || lpad(to_char(p_value,'999,999,999,999'),18);
168: aa_mystats_sorted(v_offset)(p_statname) := v_output;169: end sort;
170: 171: begin
172: 173: /*
174: || Output the elapsed snapshot time in seconds...
175: */
176: dbms_output.put_line(rpad('-',78,'-'));
177: dbms_output.put_line('Session statistics report : ' ||
178: to_char(sysdate,'dd-MON-YYYY hh24:mi:ss'));
179: dbms_output.put_line('Snapshot interval : ' ||
180: to_char((g_end_time-g_start_time)*86400) || ' seconds');
181: 182: /*
183: || Now sort the output according to difference. A fudge is that we have to sort
184: || it manually and also work around the offset limits of an associative array...
185: */
186: v_name := ga_mystats(c_run1).first;
187: while v_name is not null loop
188: 189: /*
190: || Calculate the value of the current statistic...
191: */
192: v_value := ga_mystats(c_run2)(v_name) - ga_mystats(c_run1)(v_name); 193: 194: /*
195: || If it's greater than the threshold, then output it. The downside of using
196: || purely associative arrays is that we don't have any easy way of sorting.
197: || So we have to do it ourselves...
198: */
199: if abs(v_value) >= p_threshold then
200: /*
201: || Fix for bug 1713403. If redo goes over 2Gb then it is reported as a negative
202: || number. Recommended workaround (prior to fix in 10g) is to use redo blocks written
203: || but this seems to be 0 in V$MYSTAT or V$SESSTAT. Output a bug message...
204: */
205: if v_name = 'STAT..redo size' and v_value < 0 then
206: sort('BUG...redo size > 2gb gives -ve value. Use redo blocks written',0);
207: else
208: sort(v_name, v_value);209: end if;
210: end if;
211: 212: /*
213: || Next statname please...
214: */
215: v_name := ga_mystats(c_run1).next(v_name);
216: 217: end loop;
218: 219: /*
220: || Now we can output the sorted snapshot...
221: */
222: dbms_output.put_line(chr(10));223: dbms_output.put_line(rpad('Statistic Name',60) || lpad('Value',18));
224: dbms_output.put_line(rpad('-',60,'-') || ' ' || lpad('-',16,'-'));
225: 226: v_indx := aa_mystats_sorted.first;
227: while v_indx is not null loop
228: 229: v_name := aa_mystats_sorted(v_indx).first;
230: while v_name is not null loop
231: dbms_output.put_line( aa_mystats_sorted(v_indx)(v_name) );232: v_name := aa_mystats_sorted(v_indx).next(v_name);
233: end loop;
234: 235: v_indx := aa_mystats_sorted.next(v_indx);
236: 237: end loop;
238: 239: end ms_report;
240: 241: ------------------------------------------------------------------------------
242: procedure ms_reset is
243: begin
244: ga_mystats.delete;
245: g_start_time := null;
246: g_end_time := null;
247: end ms_reset;
248: 249: ------------------------------------------------------------------------------
250: procedure ms_start is
251: begin
252: ms_reset; 253: ms_snap(c_run1, ga_mystats, g_start_time);254: end ms_start;
255: 256: ------------------------------------------------------------------------------
257: procedure ms_stop( p_threshold in integer default 0 ) is
258: begin
259: if g_start_time is not null then
260: ms_snap(c_run2, ga_mystats, g_end_time); 261: ms_report(p_threshold); 262: ms_reset;263: else
264: raise_application_error(265: -20000, 'Error: must call ms_start before ms_stop.'
266: );267: end if;
268: end ms_stop;
269: 270: ------------------------------------------------------------------------------
271: end mystats_pkg;
272: /
这段代码中，我觉得有意思的是下面这个procedure, 主要是用来把最后的结果保存在一个集合中，用来最后打印出来。由于是把值作为索引，因此最后打印出来的时候按照这个索引从小到大输出，就实现了“排序”的效果！
1: /*
2: || Procedure to add a statistic to the sorted mystats array...
3: */
4: procedure sort ( p_statname in st_statname,
5: p_value in number ) is
6: v_offset pls_integer; 7: v_output st_output;8: begin
9: /*
10: || Workaround the offset limits of a PLS_INTEGER associative array...
11: */
12: v_offset := least(abs(p_value),2147483647);13: v_output := rpad(p_statname,60) || lpad(to_char(p_value,'999,999,999,999'),18);
14: aa_mystats_sorted(v_offset)(p_statname) := v_output;15: end sort;
下面是一个简单的测试，查看对表T采集数据消耗的资源，
1: set serveroutput on size unlimited;
2: 3: exec MYSTATS_PKG.ms_start;
4: 5: BEGIN
6: DBMS_STATS.GATHER_TABLE_STATS(USER, 'T');
7: END;
8: 9: exec MYSTATS_PKG.ms_stop;
得到的结果如下，
------------------------------------------------------------------------------Session statistics report : 21-JAN-2010 22:00:04Snapshot interval : 26.00000000000000000000000000000000000001 secondsStatistic Name Value------------------------------------------------------------ ----------------LATCH.AQ Propagation Scheduling Proc Table 0
LATCH.AQ Propagation Scheduling System Load 0LATCH.ASM Volume SGA latch 0
LATCH.ASM Volume process latch 0
LATCH.ASM allocation 0
LATCH.ASM attribute latch 0
LATCH.ASM file allocation latch 0LATCH.ASM file locked extent latch 0LATCH.ASM map headers 0LATCH.ASM map load waiting list 0
LATCH.ASM map operation freelist 0
LATCH.ASM network SGA latch 0
LATCH.ASM network background latch 0
LATCH.ASM rollback operations 0LATCH.ASM scan context latch 0
LATCH.AW SGA latch 0
LATCH.AWR Alerted Metric Element list 0
LATCH.Block new check invariant rollback SCN latch 0
LATCH.Bloom Filter SGA latch 0
LATCH.Bloom filter list latch 0
LATCH.Change Notification Latch 0
LATCH.DMON Network Error List Latch 0
LATCH.DMON Work Queues Latch 0LATCH.EMON slave state object latch 0
LATCH.FAL request queue 0
LATCH.FAL subheap alocation 0
LATCH.FIB s.o chain latch 0
LATCH.Fast-Start Failover State Latch 0
LATCH.File IO Stats segmented array latch 0
LATCH.JS broadcast LW Job latch 0
LATCH.JS broadcast add buf latch 0LATCH.JS broadcast drop buf latch 0LATCH.JS broadcast kill buf latch 0LATCH.JS broadcast load blnc latch 0LATCH.KFA SGA latch 0
LATCH.KFC LRU latch 0
LATCH.KFC SGA latch 0
LATCH.KFCL BX Freelist 0
LATCH.KFCL Instance Latch 0
LATCH.KFK SGA Libload latch 0
LATCH.KFM allocation 0
LATCH.KFMD SGA 0
LATCH.KJC destination ctx free list 0LATCH.KJC global post event buffer 0LATCH.KJC global resend message queue 0LATCH.KJC receiver ctx free list 0LATCH.KJC receiver queue access list 0
LATCH.KJC snd proxy ctx free list 0LATCH.KJC snd proxy queue access list 0
LATCH.KJCT receiver queue access 0
LATCH.KKCN grp data latch 0LATCH.KKCN grp reg latch 0
LATCH.KKCN reg stat latch 0
LATCH.KMG resize request state object freelist 0
LATCH.KPON ksr channel latch 0
LATCH.KQF runtime table column alloc 0
LATCH.KSFQ 0
LATCH.KSXR large replies 0LATCH.KTF sga latch 0
LATCH.KWQMN job cache list latch 0
LATCH.KWQMN to-be-Stopped Buffer list Latch 0LATCH.KWQP Prop Status 0
LATCH.KWQS pqsubs latch 0
LATCH.LGWR NS Write 0LATCH.MQL Tracking Latch 0
LATCH.Managed Standby Recovery State 0LATCH.Media rcv so alloc latch 0
LATCH.Minimum flashback SCN latch 0
LATCH.NLS data objects 0LATCH.NSV command ID generation latch 0
LATCH.NSV creation/termination latch 0
LATCH.ODM-NFS:Global file structure 0
LATCH.OFS SGA Latch 0
LATCH.OLS label cache 0
LATCH.OS file lock latch 0LATCH.PC and Classifier lists for WLM 0
LATCH.Policy Hash Table Latch 0LATCH.Policy Refresh Latch 0
LATCH.QOL Name Generation Latch 0
LATCH.RSM process latch 0
LATCH.Real time apply boundary 0
LATCH.Real-time plan statistics latch 0
LATCH.Request id generation latch 0
LATCH.Reserved Space Latch 0LATCH.Result Cache: Latch 0LATCH.Result Cache: SO Latch 0LATCH.Role grants to users 0
LATCH.Role graph 0LATCH.SGA blob lock 0LATCH.SGA heap creation lock 0
LATCH.SGA kcrrgap latch 0
LATCH.SGA kcrrlac latch 0
LATCH.SGA kcrrlatmscnl latch 0
LATCH.SGA kcrrpinfo latch 0
LATCH.SGA kcrrssncpl latch 0
LATCH.SGA mapping latch 0
LATCH.SGA pool creation lock 0
LATCH.SGA slab metadata lock 0
LATCH.SGA table lock 0LATCH.STREAMS LCR 0
LATCH.STREAMS Pool Advisor 0
LATCH.Sage HT Latch 0
LATCH.Security Class Hashtable 0LATCH.TXN SGA 0
LATCH.Transportable DB Context Latch 0
LATCH.Undo Hint Latch 0
LATCH.WCR: kecu cas mem 0
LATCH.WCR: ticker cache 0
LATCH.X$KSFQP 0
LATCH.XDB Byte Lock SGA Latch 0
LATCH.XDB Config-1 0
LATCH.XDB Config-2 0
LATCH.XDB Config-3 0
LATCH.XDB Mcache SGA Latch 0
LATCH.XDB NFS Stateful SGA Latch 0
LATCH.XDB PL/SQL Support 0LATCH.XML DB Events 0
LATCH.address list 0
LATCH.alert log latch 0
LATCH.begin backup scn array 0
LATCH.block corruption recovery state 0LATCH.block media rcv so alloc latch 0
LATCH.bq:time manger info latch 0LATCH.buffer pin latch 0
LATCH.bufq statistics 0LATCH.cache protection latch 0
LATCH.cached attr list 0
LATCH.change tracking consistent SCN 0
LATCH.change tracking optimization SCN 0
LATCH.change tracking state change latch 0LATCH.channel anchor 0
LATCH.connection pool sga data lock 0
LATCH.constraint object allocation 0
LATCH.corrupted undo seg lock 0
LATCH.cost function 0LATCH.cp cmon array latch 0LATCH.cp cso latch 0
LATCH.cp holder latch 0
LATCH.cp pool array latch 0LATCH.cp server array latch 0LATCH.datapump attach fixed tables latch 0
LATCH.datapump job fixed tables latch 0
LATCH.dbkea msgq latch 0
LATCH.device information 0
LATCH.dictionary lookup 0LATCH.direct msg latch 0
LATCH.dispatcher info 0
LATCH.domain validation update latch 0
LATCH.dynamic channels 0LATCH.end-point list 0LATCH.enqueue sob latch 0
LATCH.error message lists 0
LATCH.event range base latch 0
LATCH.file cache latch 0LATCH.file deallocation SCN cache 0LATCH.file number translation table 0
LATCH.first spare latch 0LATCH.fixed table rows for x$hs_session 0
LATCH.flashback FBA barrier 0
LATCH.flashback SCN barrier 0
LATCH.flashback hint SCN barrier 0
LATCH.flashback marker cache 0
LATCH.flashback sync request 0
LATCH.fourth spare latch 0
LATCH.gc checkpoint 0LATCH.gcs domain validate latch 0LATCH.gcs opaque info freelist 0
LATCH.gcs remaster request queue 0
LATCH.gcs remastering latch 0
LATCH.gcs resource validate list 0
LATCH.generalized trace enabling latch 0
LATCH.ges caches resource lists 0
LATCH.ges deadlock list 0
LATCH.ges process table freelist 0LATCH.ges s-lock bitvec freelist 0
LATCH.ges statistic table 0LATCH.ges synchronous data 0LATCH.ges timeout list 0
LATCH.global KZLD latch for mem in SGA 0
LATCH.global ctx hash table latch 0
LATCH.global hanganlyze operation 0
LATCH.hint flashback FBA barrier 0
LATCH.hot latch diags 0
LATCH.i/o slave adaptor 0
LATCH.image handles of buffered messages latch 0LATCH.instance enqueue 0
LATCH.instance information 0
LATCH.io pool granule list 0
LATCH.job queue sob latch 0
LATCH.k2q global data latch 0
LATCH.kgb latch 0
LATCH.kkae edition name cache 0
LATCH.kmcpvec latch 0
LATCH.kpon job info latch 0
LATCH.kpon sga structure 0LATCH.kpplsSyncStateListSga: lock 0
LATCH.krbmrosl 0
LATCH.ksbxic instance latch 0
LATCH.kse signature 0
LATCH.ksfv subheap 0
LATCH.ksim membership request latch 0
LATCH.ksir sga latch 0
LATCH.kspoltest latch 0
LATCH.kssmov protection latch 0
LATCH.ksupkttest latch 0
LATCH.ksv instance latch 0
LATCH.ksv remote inst ops 0
LATCH.ksxp shared latch 0
LATCH.ktm global data 0
LATCH.kupp process latch 0
LATCH.kwqbcco:cco 0
LATCH.kwqbsgn:msghdr 0
LATCH.kwqbsn:qsga 0
LATCH.kwqbsn:qxl 0
LATCH.kwqi:kchunk latch 0
LATCH.lock new checkpoint scn during media recovery 0
LATCH.logical standby view 0LATCH.managed standby latch 0
LATCH.media recovery process out of buffers 0
LATCH.message enqueue sync latch 0
LATCH.multiple dbwriter suspend 0
LATCH.name-service entry 0
LATCH.name-service memory objects 0
LATCH.name-service memory recovery 0
LATCH.name-service namespace objects 0
LATCH.name-service pending queue 0
LATCH.name-service request 0
LATCH.name-service request queue 0
LATCH.parallel recoverable recovery 0
LATCH.parallel txn reco latch 0
LATCH.parameter list 0LATCH.pass worker exception to master 0
LATCH.pebof_rrv 0
LATCH.ping redo on-disk SCN 0
LATCH.policy information 0
LATCH.presentation list 0
LATCH.process 0
LATCH.qm_init_sga 0
LATCH.qmn state object latch 0
LATCH.qmn task context latch 0
LATCH.qmn task queue latch 0
LATCH.qmne Export Table Latch 0LATCH.qmtmrcsg_init 0
LATCH.query server freelists 0
LATCH.query server process 0
LATCH.queue sender's info. latch 0LATCH.queued dump request 0
LATCH.readable standby influx scn 0
LATCH.readable standby metadata redo cache 0
LATCH.readredo stats and histogram 0
LATCH.recovery domain freelist 0
LATCH.redo on-disk SCN 0
LATCH.reg$ timeout service time 0
LATCH.reid allocation latch 0
LATCH.reservation so alloc latch 0
LATCH.resmgr:incr/decr stats 0
LATCH.resmgr:vc list latch 0
LATCH.resumable state object 0
LATCH.rules engine aggregate statistics 0
LATCH.rules engine evaluation context statistics 0
LATCH.rules engine rule set statistics 0
LATCH.rules engine rule statistics 0
LATCH.segmented array pool 0
LATCH.server alert latch 0
LATCH.shared server configuration 0
LATCH.shared server info 0
LATCH.shrink stat allocation latch 0
LATCH.space background SGA latch 0
LATCH.space background state object latch 0
LATCH.spilled messages latch 0
LATCH.state object free list 0
LATCH.statistics aggregation 0
LATCH.temp lob duration state obj allocation 0
LATCH.test excl. non-parent l0 0
LATCH.test excl. non-parent lmax 0
LATCH.test shared non-parent l0 0
LATCH.threshold alerts latch 0
LATCH.trace latch 0
LATCH.vecio buf des 0
LATCH.virtual circuits 0
LATCH.xscalc freelist 0
LATCH.xssinfo freelist 0
STAT..Batched IO (bound) vector count 0
STAT..Batched IO (full) vector count 0
STAT..Batched IO (space) vector count 0
STAT..Batched IO block miss count 0
STAT..Batched IO buffer defrag count 0
STAT..Batched IO double miss count 0
STAT..Batched IO same unit count 0
STAT..Batched IO single block count 0
STAT..Batched IO slow jump count 0
STAT..Batched IO vector block count 0
STAT..Batched IO vector read count 0
STAT..Batched IO zero block count 0
STAT..Block Cleanout Optim referenced 0
STAT..CCursor + sql area evicted 0
STAT..CR blocks created 0
STAT..Cached Commit SCN referenced 0
STAT..Clusterwide global transactions 0
STAT..Clusterwide global transactions spanning RAC nodes 0
STAT..Commit SCN cached 0
STAT..DBWR checkpoint buffers written 0
STAT..DBWR checkpoints 0
STAT..DBWR fusion writes 0
STAT..DBWR lru scans 0
STAT..DBWR object drop buffers written 0
STAT..DBWR parallel query checkpoint buffers written 0
STAT..DBWR revisited being-written buffer 0
STAT..DBWR tablespace checkpoint buffers written 0
STAT..DBWR thread checkpoint buffers written 0
STAT..DBWR transaction table writes 0
STAT..DBWR undo block writes 0
STAT..DDL statements parallelized 0
STAT..DFO trees parallelized 0
STAT..DML statements parallelized 0
STAT..DX/BB enqueue lock background get time 0
STAT..DX/BB enqueue lock background gets 0
STAT..DX/BB enqueue lock foreground requests 0
STAT..DX/BB enqueue lock foreground wait time 0
STAT..Effective IO time 0
STAT..Forwarded 2PC commands across RAC nodes 0
STAT..GTX processes spawned by autotune 0
STAT..GTX processes stopped by autotune 0
STAT..HSC Compressed Segment Block Changes 0
STAT..HSC IDL Compressed Blocks 0
STAT..HSC OLTP Compressed Blocks 0
STAT..HSC OLTP Compression skipped rows 0
STAT..HSC OLTP Drop Column 0
STAT..HSC OLTP Non Compressible Blocks 0
STAT..HSC OLTP Space Saving 0
STAT..HSC OLTP compression block checked 0
STAT..HSC OLTP inline compression 0
STAT..HSC OLTP negative compression 0
STAT..HSC OLTP positive compression 0
STAT..HSC OLTP recursive compression 0
STAT..Heap Segment Array Updates 0
STAT..IMU CR rollbacks 0
STAT..IMU Flushes 0
STAT..IMU Redo allocation size 0
STAT..IMU bind flushes 0
STAT..IMU commits 0
STAT..IMU contention 0
STAT..IMU ktichg flush 0
STAT..IMU mbu flush 0
STAT..IMU pool not allocated 0
STAT..IMU recursive-transaction flush 0
STAT..IMU undo allocation size 0
STAT..IMU undo retention flush 0
STAT..IMU- failed to get a private strand 0
STAT..IPC CPU used by this session 0
STAT..LOB table id lookup cache misses 0
STAT..Misses for writing mapping 0
STAT..Number of read IOs issued 0
STAT..OTC commit optimization attempts 0
STAT..OTC commit optimization failure - setup 0
STAT..OTC commit optimization hits 0
STAT..PX local messages recv'd 0
STAT..PX local messages sent 0STAT..PX remote messages recv'd 0
STAT..PX remote messages sent 0
STAT..Parallel operations downgraded 1 to 25 pct 0STAT..Parallel operations downgraded 25 to 50 pct 0STAT..Parallel operations downgraded 50 to 75 pct 0STAT..Parallel operations downgraded 75 to 99 pct 0STAT..Parallel operations downgraded to serial 0STAT..Parallel operations not downgraded 0STAT..RowCR - resume 0
STAT..RowCR - row contention 0STAT..RowCR attempts 0
STAT..RowCR hits 0
STAT..SMON posted for dropping temp segment 0STAT..SMON posted for instance recovery 0STAT..SMON posted for txn recovery for other instances 0
STAT..SMON posted for undo segment recovery 0STAT..SMON posted for undo segment shrink 0STAT..SQL*Net roundtrips to/from dblink 0
STAT..TBS Extension: files extended 0
STAT..TBS Extension: tasks created 0
STAT..TBS Extension: tasks executed 0
STAT..Workload Capture: dbtime 0
STAT..Workload Capture: errors 0
STAT..Workload Capture: size (in bytes) of recording 0
STAT..Workload Capture: unreplayable user calls 0STAT..Workload Capture: unsupported user calls 0STAT..Workload Capture: user calls 0STAT..Workload Capture: user calls flushed 0STAT..Workload Capture: user logins 0STAT..Workload Capture: user txns 0STAT..Workload Replay: dbtime 0
STAT..Workload Replay: deadlocks resolved 0
STAT..Workload Replay: network time 0STAT..Workload Replay: think time 0STAT..Workload Replay: time gain 0STAT..Workload Replay: time loss 0STAT..Workload Replay: user calls 0STAT..application wait time 0STAT..auto extends on undo tablespace 0STAT..background checkpoints completed 0
STAT..background checkpoints started 0
STAT..background timeouts 0
STAT..blocks helped by sage commit cache 0
STAT..blocks sage cache can process 0
STAT..blocks sage data can process 0STAT..blocks sage skipped due to chained rows 0
STAT..blocks sage txn can process 0
STAT..branch node splits 0
STAT..bytes received via SQL*Net from dblink 0
STAT..bytes sent via SQL*Net to dblink 0
STAT..bytes via SQL*Net vector from client 0
STAT..bytes via SQL*Net vector from dblink 0
STAT..bytes via SQL*Net vector to client 0
STAT..bytes via SQL*Net vector to dblink 0
STAT..cleanouts and rollbacks - consistent read gets 0
STAT..cleanouts only - consistent read gets 0
STAT..cluster wait time 0STAT..cold recycle reads 0STAT..commit batch performed 0STAT..commit batch requested 0STAT..commit batch/immediate performed 0
STAT..commit batch/immediate requested 0
STAT..commit cleanout failures: block lost 0STAT..commit cleanout failures: buffer being written 0STAT..commit cleanout failures: callback failure 0STAT..commit cleanout failures: cannot pin 0STAT..commit cleanout failures: hot backup in progress 0
STAT..commit cleanout failures: write disabled 0
STAT..commit immediate performed 0
STAT..commit immediate requested 0
STAT..commit nowait performed 0STAT..commit nowait requested 0STAT..commit wait performed 0STAT..commit wait requested 0STAT..commit wait/nowait performed 0STAT..commit wait/nowait requested 0STAT..concurrency wait time 0STAT..consistent gets direct 0
STAT..current blocks converted for CR 0
STAT..cursor authentications 0STAT..data blocks consistent reads - undo records applied 0
STAT..db block gets direct 0
STAT..db corrupt blocks detected 0
STAT..db corrupt blocks recovered 0
STAT..deferred CUR cleanouts (index blocks) 0
STAT..dirty buffers inspected 0
STAT..doubling up with imu segment 0STAT..drop segment calls in space pressure 0
STAT..enqueue conversions 0
STAT..enqueue deadlocks 0
STAT..enqueue timeouts 0
STAT..enqueue waits 0
STAT..exchange deadlocks 0
STAT..failed probes on index block reclamation 0
STAT..flashback log writes 0
STAT..frame signature mismatch 0
STAT..free buffer inspected 0STAT..gc CPU used by this session 0
STAT..gc blocks corrupt 0
STAT..gc blocks lost 0
STAT..gc claim blocks lost 0
STAT..gc cr block build time 0STAT..gc cr block flush time 0STAT..gc cr block receive time 0STAT..gc cr block send time 0STAT..gc cr blocks received 0
STAT..gc cr blocks served 0
STAT..gc current block flush time 0
STAT..gc current block pin time 0
STAT..gc current block receive time 0
STAT..gc current block send time 0
STAT..gc current blocks received 0STAT..gc current blocks served 0STAT..gc local grants 0STAT..gc reader bypass grants 0
STAT..gc remote grants 0
STAT..gcs messages sent 0
STAT..ges messages sent 0
STAT..global enqueue CPU used by this session 0
STAT..global enqueue get time 0
STAT..global enqueue gets async 0STAT..global enqueue gets sync 0STAT..global enqueue releases 0STAT..global undo segment hints helped 0STAT..global undo segment hints were stale 0STAT..hot buffers moved to head of LRU 0
STAT..immediate (CR) block cleanout applications 0STAT..immediate CR cleanouts (index blocks) 0
STAT..index crx upgrade (found) 0
STAT..index crx upgrade (positioned) 0STAT..index crx upgrade (prefetch) 0STAT..index fast full scans (direct read) 0
STAT..index fast full scans (full) 0
STAT..index fast full scans (rowid ranges) 0
STAT..java call heap collected bytes 0STAT..java call heap collected count 0
STAT..java call heap gc count 0
STAT..java call heap live object count 0
STAT..java call heap live object count max 0
STAT..java call heap live size 0
STAT..java call heap live size max 0
STAT..java call heap object count 0
STAT..java call heap object count max 0
STAT..java call heap total size 0
STAT..java call heap total size max 0
STAT..java call heap used size 0
STAT..java call heap used size max 0
STAT..java session heap collected bytes 0STAT..java session heap collected count 0
STAT..java session heap gc count 0
STAT..java session heap live object count 0
STAT..java session heap live object count max 0
STAT..java session heap live size 0
STAT..java session heap live size max 0
STAT..java session heap object count 0
STAT..java session heap object count max 0
STAT..java session heap used size 0
STAT..java session heap used size max 0
STAT..local undo segment hints helped 0STAT..local undo segment hints were stale 0STAT..logons cumulative 0
STAT..logons current 0STAT..messages received 0
STAT..native hash arithmetic execute 0STAT..native hash arithmetic fail 0
STAT..no buffer to keep pinned count 0
STAT..number of map misses 0
STAT..number of map operations 0
STAT..opened cursors current 0STAT..parse count (failures) 0STAT..parse count (hard) 0STAT..parse time cpu 0STAT..parse time elapsed 0STAT..physical read total multi block requests 0STAT..physical reads direct 0STAT..physical reads direct (lob) 0STAT..physical reads direct temporary tablespace 0
STAT..physical reads retry corrupt 0STAT..physical write IO requests 0STAT..physical write bytes 0STAT..physical write total IO requests 0STAT..physical write total bytes 0STAT..physical write total multi block requests 0STAT..physical writes 0
STAT..physical writes direct 0
STAT..physical writes direct (lob) 0
STAT..physical writes direct temporary tablespace 0STAT..physical writes from cache 0STAT..physical writes non checkpoint 0STAT..pinned buffers inspected 0
STAT..prefetch clients - 16k 0
STAT..prefetch clients - 2k 0
STAT..prefetch clients - 32k 0
STAT..prefetch clients - 4k 0
STAT..prefetch clients - 8k 0
STAT..prefetch clients - default 0STAT..prefetch clients - keep 0
STAT..prefetch clients - recycle 0
STAT..prefetch warmup blocks aged out before use 0
STAT..prefetch warmup blocks flushed out before use 0
STAT..prefetched blocks aged out before use 0
STAT..process last non-idle time 0
STAT..queries parallelized 0
STAT..queue flush 0
STAT..queue ocp pages 0
STAT..queue position update 0
STAT..queue qno pages 0
STAT..queue single row 0STAT..queue splits 0
STAT..queue update without cp update 0
STAT..recovery array read time 0
STAT..recovery array reads 0
STAT..recovery block gets from cache 0STAT..recovery blocks read 0STAT..recovery blocks read for lost write detection 0
STAT..recovery blocks skipped lost write checks 0STAT..recursive aborts on index block reclamation 0
STAT..redo blocks checksummed by FG (exclusive) 0STAT..redo blocks checksummed by LGWR 0STAT..redo blocks read (memory) 0STAT..redo blocks read (memory) by LNS 0
STAT..redo blocks read for recovery 0
STAT..redo blocks read total 0STAT..redo blocks read total by LNS 0
STAT..redo blocks written 0
STAT..redo blocks written for direct writes 0STAT..redo buffer allocation retries 0
STAT..redo entries for lost write detection 0
STAT..redo log space requests 0STAT..redo log space wait time 0
STAT..redo size for lost write detection 0
STAT..redo synch time 0STAT..redo wastage 0
STAT..redo write time 0
STAT..redo writer latching time 0STAT..redo writes 0
STAT..rollback changes - undo records applied 0STAT..rollbacks only - consistent read gets 0
STAT..sage commit cache queries 0STAT..sage scans 0
STAT..securefile add dedupd lob to set 0
STAT..securefile allocation bytes 0
STAT..securefile allocation chunks 0
STAT..securefile bytes cleartext 0
STAT..securefile bytes deduplicated 0
STAT..securefile bytes encrypted 0
STAT..securefile bytes non-transformed 0
STAT..securefile compressed bytes 0
STAT..securefile create dedup set 0
STAT..securefile dedup callback oper final 0
STAT..securefile dedup flush too low 0
STAT..securefile dedup hash collision 0
STAT..securefile dedup prefix hash match 0
STAT..securefile destroy dedup set 0
STAT..securefile direct read bytes 0STAT..securefile direct read ops 0STAT..securefile direct write bytes 0STAT..securefile direct write ops 0STAT..securefile inode ioreap time 0STAT..securefile inode read time 0
STAT..securefile inode write time 0
STAT..securefile number of flushes 0STAT..securefile number of non-transformed flushes 0STAT..securefile reject deduplication 0
STAT..securefile rmv from dedup set 0
STAT..securefile uncompressed bytes 0
STAT..serializable aborts 0
STAT..session connect time 0
STAT..session cursor cache count 0
STAT..session pga memory max 0
STAT..session stored procedure space 0
STAT..session uga memory 0STAT..session uga memory max 0
STAT..shared hash latch upgrades - wait 0
STAT..shared io pool buffer get failure 0STAT..shared io pool buffer get success 0STAT..sorts (disk) 0STAT..space was found by tune down 0
STAT..space was not found by tune down 0
STAT..sql area evicted 0STAT..sql area purged 0STAT..steps of tune down ret. in space pressure 0
STAT..summed dirty queue length 0
STAT..table fetch continued row 0
STAT..table lookup prefetch client count 0
STAT..table scans (cache partitions) 0STAT..table scans (direct read) 0
STAT..table scans (long tables) 0STAT..table scans (rowid ranges) 0STAT..total bytes read and filtered by intelligent storage 0
STAT..total bytes returned by intelligent storage after filt 0
STAT..total number of slots 0STAT..total number of times SMON posted 0STAT..total number of undo segments dropped 0STAT..transaction lock background get time 0
STAT..transaction lock background gets 0STAT..transaction lock foreground requests 0STAT..transaction lock foreground wait time 0
STAT..transaction rollbacks 0STAT..transaction tables consistent read rollbacks 0
STAT..transaction tables consistent reads - undo records app 0
STAT..transactions found in sage commit cache 0
STAT..tune down retentions in space pressure 0
STAT..undo segment header was pinned 0
STAT..user commits 0STAT..user rollbacks 0STAT..workarea executions - multipass 0
STAT..workarea executions - onepass 0
STAT..workarea memory allocated 0
STAT..write clones created for recovery 0
STAT..write clones created in background 0
STAT..write clones created in foreground 0
LATCH.ASM map operation hash table 1
LATCH.File State Object Pool Parent Latch 1
LATCH.IPC stats buffer allocation latch 1
LATCH.In memory undo latch 1LATCH.JOX JIT latch 1
LATCH.JS Sh mem access 1
LATCH.JS broadcast autostart latch 1
LATCH.KFC FX Hash Latch 1
LATCH.KFC Hash Latch 1
LATCH.KFCL LE Freelist 1
LATCH.KFR redo allocation latch 1
LATCH.KGNFS-NFS:SHM structure 1LATCH.KGNFS-NFS:SVR LIST 1
LATCH.KJC message pool free list 1LATCH.KJCT flow control latch 1
LATCH.Locator state objects pool parent latch 1
LATCH.Memory Management Latch 1
LATCH.Memory Queue 1
LATCH.Memory Queue Message Subscriber #1 1
LATCH.Memory Queue Message Subscriber #2 1
LATCH.Memory Queue Message Subscriber #3 1
LATCH.Memory Queue Message Subscriber #4 1
LATCH.Memory Queue Subscriber 1
LATCH.Mutex 1
LATCH.Mutex Stats 1
LATCH.QMT 1
LATCH.SGA blob parent 1LATCH.SGA bucket locks 1
LATCH.SGA heap locks 1
LATCH.SGA pool locks 1
LATCH.SQL memory manager latch 1LATCH.Streams Generic 1
LATCH.Testing 1
LATCH.Token Manager 1
LATCH.Write State Object Pool Parent Latch 1
LATCH.XDB NFS Security Latch 1
LATCH.XDB unused session pool 1LATCH.XDB used session pool 1LATCH.buffer pool 1
LATCH.business card 1
LATCH.cas latch 1
LATCH.change notification client cache latch 1
LATCH.cp cmon/server latch 1
LATCH.cp pool latch 1
LATCH.cp server hash latch 1
LATCH.cp sga latch 1
LATCH.cv apply list lock 1
LATCH.cv free list lock 1LATCH.deferred cleanup latch 1LATCH.dml lock allocation 1
LATCH.done queue latch 1
LATCH.fifth spare latch 1
LATCH.flashback archiver latch 1
LATCH.flashback copy 1
LATCH.gc element 1
LATCH.gcs commit scn state 1
LATCH.gcs partitioned table hash 1LATCH.gcs pcm hashed value bucket hash 1LATCH.gcs resource freelist 1
LATCH.gcs resource hash 1
LATCH.gcs resource scan list 1
LATCH.gcs shadows freelist 1
LATCH.ges domain table 1
LATCH.ges enqueue table freelist 1LATCH.ges group table 1
LATCH.ges process hash list 1
LATCH.ges process parent latch 1
LATCH.ges resource hash list 1
LATCH.ges resource scan list 1
LATCH.ges resource table freelist 1LATCH.ges value block free list 1
LATCH.global tx hash mapping 1LATCH.granule operation 1LATCH.intra txn parallel recovery 1
LATCH.io pool granule metadata list 1
LATCH.job workq parent latch 1
LATCH.k2q lock allocation 1
LATCH.kdlx hb parent latch 1
LATCH.kgb parent 1
LATCH.ksfv messages 1
LATCH.ksv msg queue latch 1
LATCH.lob segment dispenser latch 1
LATCH.lob segment hash table latch 1LATCH.lob segment query latch 1
LATCH.lock DBA buffer during media recovery 1
LATCH.logical standby cache 1
LATCH.logminer context allocation 1
LATCH.logminer work area 1LATCH.mapped buffers lru chain 1
LATCH.msg queue latch 1
LATCH.name-service namespace bucket 1
LATCH.ncodef allocation latch 1
LATCH.parallel query stats 1
LATCH.peshm 1
LATCH.process queue 1
LATCH.process queue reference 1
LATCH.recovery domain hash list 1LATCH.redo copy 1
LATCH.resmgr:actses change state 1LATCH.rm cas latch 1
LATCH.second spare latch 1LATCH.session queue latch 1LATCH.sim partition latch 1
LATCH.tablespace key chain 1LATCH.test excl. parent l0 1
LATCH.test excl. parent2 l0 1
LATCH.third spare latch 1
LATCH.virtual circuit buffers 1
LATCH.virtual circuit holder 1
LATCH.virtual circuit queues 1
STAT..active txn count during cleanout 1STAT..cleanout - number of ktugct calls 1STAT..commit txn count during cleanout 1
STAT..leaf node 90-10 splits 1
STAT..leaf node splits 1
STAT..redo synch writes 1
LATCH.ksuosstats global area 2LATCH.loader state object freelist 2
LATCH.longop free list parent 2LATCH.session switching 2LATCH.temporary table state object allocation 2
STAT..SQL*Net roundtrips to/from client 2
LATCH.internal temp table object number allocation latch 3
LATCH.kmcptab latch 3
LATCH.post/wait queue 3
LATCH.resmgr:resource group CPU method 3LATCH.resmgr:schema config 3STAT..messages sent 3
LATCH.SGA IO buffer pool latch 4
LATCH.flashback mapping 4
STAT..Heap Segment Array Inserts 4STAT..user calls 4LATCH.job_queue_processes parameter latch 5LATCH.kss move lock 5
LATCH.parallel query alloc buffer 5
STAT..calls to kcmgcs 5LATCH.database property service latch 6LATCH.job_queue_processes free list latch 6LATCH.pesom_heap_alloc 6
STAT..change write time 6
STAT..heap block compress 6
LATCH.ksv class latch 7LATCH.sort extent pool 7
LATCH.Event Group Locks 8LATCH.KMG MMAN ready and startup request latch 8LATCH.OS process: request allocation 8
LATCH.archive process latch 8
LATCH.ksv allocation latch 8
LATCH.ksz_so allocation latch 8
LATCH.process group creation 8LATCH.user lock 8LATCH.Change Notification Hash table latch 9LATCH.compile environment latch 9
LATCH.session timer 9LATCH.shared pool sim alloc 9
LATCH.transaction branch allocation 9STAT..physical reads cache prefetch 9STAT..physical reads prefetch warmup 9LATCH.KWQS pqueue ctx latch 10
STAT..immediate (CURRENT) block cleanout applications 10
LATCH.Shared B-Tree 11
LATCH.resmgr:free threads list 12LATCH.active checkpoint queue latch 13LATCH.dummy allocation 13STAT..shared hash latch upgrades - no wait 13STAT..workarea executions - optimal 13
LATCH.JS mem alloc latch 14
LATCH.JS slv state obj latch 14LATCH.hash table modification latch 14LATCH.parameter table management 14
LATCH.JS queue access latch 15
LATCH.process allocation 15
LATCH.resmgr:actses change group 15STAT..deferred (CURRENT) block cleanout applications 15
STAT..lob reads 15STAT..switch current to new buffer 15
LATCH.message pool operations parent latch 16
LATCH.resmgr:method mem alloc latch 16
STAT..redo ordering marks 16
LATCH.ASM db client latch 17
STAT..physical reads for flashback new 17
STAT..sorts (memory) 17
LATCH.PL/SQL warning settings 18LATCH.channel handle pool latch 18
LATCH.list of block allocation 18LATCH.archive control 19
LATCH.space background task latch 19LATCH.resmgr:active threads 20
STAT..enqueue releases 21
STAT..enqueue requests 21
STAT..physical read IO requests 22STAT..physical read total IO requests 22STAT..table scans (short tables) 22LATCH.FOB s.o list latch 25
LATCH.cache table scan latch 26STAT..redo subscn max counts 26STAT..user I/O wait time 26
LATCH.commit callback allocation 27LATCH.session state list latch 27
LATCH.resmgr group change latch 28STAT..commit cleanouts 28STAT..commit cleanouts successfully completed 28LATCH.JOX SGA heap latch 30
STAT..physical reads 31STAT..physical reads cache 31STAT..calls to kcmgas 36LATCH.OS process allocation 44
LATCH.OS process 46
LATCH.object queue header heap 56LATCH.session idle bit 57
STAT..parse count (total) 57LATCH.Consistent RBA 61
LATCH.client/application info 62
LATCH.sequence cache 64LATCH.lgwr LWN SCN 65
LATCH.mostly latch-free SCN 65LATCH.hash table column usage latch 66
STAT..cluster key scans 67LATCH.kokc descriptor allocation latch 75LATCH.object stats modification 77STAT..cluster key scan block gets 79LATCH.flashback allocation 83
STAT..session cursor cache hits 83
LATCH.multiblock read objects 84STAT..bytes sent via SQL*Net to client 99
LATCH.active service list 108
STAT..execute count 126
STAT..opened cursors cumulative 127
STAT..rows fetched via callback 140LATCH.resmgr:plan CPU method 159LATCH.cache buffer handles 163
STAT..bytes received via SQL*Net from client 196
LATCH.kks stats 200
LATCH.resmgr:session queuing 201LATCH.checkpoint queue latch 215LATCH.redo writing 218
LATCH.redo allocation 219
LATCH.channel operations parent latch 220
LATCH.JS queue state obj latch 242STAT..index scans kdiixs1 274STAT..table fetch by rowid 286
LATCH.library cache load lock 367LATCH.undo global data 450
LATCH.messages 467
STAT..free buffer requested 526STAT..HSC Heap Segment Block Changes 529
STAT..db block gets from cache (fastpath) 545STAT..buffer is pinned count 556
STAT..index fetch by key 579
LATCH.pesom_free_list 583
LATCH.pesom_hash_node 583
LATCH.MinActiveScn Latch 598
LATCH.SQL memory manager workarea list latch 755STAT..lob writes 820
STAT..lob writes unaligned 820
LATCH.enqueues 885
STAT..redo entries 1,087
STAT..consistent gets - examination 1,117
LATCH.cache buffers lru chain 1,192
STAT..recursive calls 1,673LATCH.enqueue hash chains 1,730
LATCH.object queue header operation 1,799
STAT..recursive cpu usage 2,060
STAT..CPU used by this session 2,077
STAT..CPU used when call started 2,077
STAT..DB time 2,118STAT..consistent changes 2,251
STAT..buffer is not pinned count 2,893
LATCH.simulator lru latch 3,062
LATCH.simulator hash latch 3,143
STAT..calls to get snapshot scn: kcmgss 3,282
STAT..calls to kcmgrs 3,376LATCH.shared pool simulator 3,490
STAT..table scan blocks gotten 4,418STAT..db block changes 4,428
STAT..no work - consistent read gets 6,807
STAT..consistent gets from cache (fastpath) 7,914STAT..db block gets 8,100
STAT..db block gets from cache 8,100STAT..consistent gets 9,050
STAT..consistent gets from cache 9,050LATCH.shared pool 13,877
LATCH.transaction allocation 14,353STAT..session logical reads 17,150
LATCH.session allocation 35,061LATCH.row cache objects 45,830LATCH.cache buffers chains 60,213
LATCH.DML lock allocation 63,956
STAT..undo change vector size 118,384STAT..physical read bytes 253,952STAT..physical read total bytes 253,952STAT..redo size 314,008STAT..session pga memory 589,824STAT..table scan rows gotten 1,005,827
STAT..sorts (rows) 1,010,979
个人觉得这个package有点不是很如意的就是最后的输出结果是按照statistics 值从小到大输出的（这也没办法，associative array是按照索引从小到大输出的），如果能“倒序“排列就更好了，因为我们肯定是关心哪个数据最大，而不是最小。
其实可以把procedure Sort稍微修改下，
/*
|| Procedure to add a statistic to the sorted mystats array...
*/
procedure sort ( p_statname in st_statname,
p_value in number ) is
v_offset pls_integer;
v_output st_output;
begin
/*
|| Workaround the offset limits of a PLS_INTEGER associative array...
*/
v_offset := -1 * least(abs(p_value),2147483647);
v_output := rpad(p_statname,60) || lpad(to_char(p_value,'999,999,999,999'),18);
aa_mystats_sorted(v_offset)(p_statname) := v_output;
end sort;
这样可以使得最后的结果降序排列…
------------------------------------------------------------------------------Session statistics report : 21-JAN-2010 23:33:54Snapshot interval : 41.99999999999999999999999999999999999999 seconds Statistic Name Value------------------------------------------------------------ ----------------STAT..session pga memory 6,921,480STAT..session pga memory max 6,921,480
STAT..session uga memory 1,113,068STAT..session uga memory max 1,113,068
STAT..sorts (rows) 1,011,217STAT..table scan rows gotten 1,005,961
STAT..redo size 331,660STAT..physical read bytes 245,760STAT..physical read total bytes 245,760STAT..undo change vector size 126,888LATCH.cache buffers chains 33,446
STAT..session logical reads 18,002
STAT..consistent gets 9,874
STAT..consistent gets from cache 9,874STAT..db block gets 8,128
STAT..db block gets from cache 8,128STAT..consistent gets from cache (fastpath) 8,005STAT..no work - consistent read gets 6,885
LATCH.session allocation 4,471STAT..db block changes 4,455
STAT..table scan blocks gotten 4,411LATCH.shared pool 3,687
STAT..calls to get snapshot scn: kcmgss 3,561
STAT..buffer is not pinned count 3,467
STAT..calls to kcmgrs 3,364STAT..recursive calls 2,490LATCH.row cache objects 2,358STAT..consistent changes 2,258
STAT..DB time 2,088STAT..CPU used by this session 2,057
STAT..CPU used when call started 2,055
STAT..recursive cpu usage 2,040
STAT..consistent gets - examination 1,848
STAT..bytes received via SQL*Net from client 1,229
LATCH.object queue header operation 1,145
STAT..redo entries 1,097
--------------------------------------
Regards,
FangwenYu
浙公网安备 33010602011771号