SQL> exec runStats_pkg.rs_start; PL/SQL procedure successfully completed. SQL> column filename format a50; SELECT e.snap_id, e.instance_number, e.filename, DECODE( SIGN(e.phyrds - b.phyrds),-1,0, (e.phyrds - b.phyrds) ) AS delta_phyrds FROM dba_hist_filestatxs b, dba_hist_filestatxs e WHERE e.snap_id = b.snap_id + 1 AND b.filename = e.filename AND b.dbid = e.dbid AND b.instance_number = e.instance_number and e.snap_id between 83578 and 83590 and e.tsname = 'DATA_TBS' and e.instance_number = 2 ORDER BY e.snap_id / SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 SNAP_ID INSTANCE_NUMBER FILENAME DELTA_PHYRDS ---------- --------------- -------------------------------------------------- ------------ 83578 2 +DATA/data_tbs_01.dbf 10 83579 2 +DATA/data_tbs_01.dbf 7 83580 2 +DATA/data_tbs_01.dbf 0 83581 2 +DATA/data_tbs_01.dbf 28 83582 2 +DATA/data_tbs_01.dbf 0 83583 2 +DATA/data_tbs_01.dbf 7 83584 2 +DATA/data_tbs_01.dbf 0 83585 2 +DATA/data_tbs_01.dbf 22 83586 2 +DATA/data_tbs_01.dbf 0 83587 2 +DATA/data_tbs_01.dbf 1 83588 2 +DATA/data_tbs_01.dbf 2 83589 2 +DATA/data_tbs_01.dbf 0 83590 2 +DATA/data_tbs_01.dbf 1 13 rows selected. SQL> SQL> exec runStats_pkg.rs_middle; PL/SQL procedure successfully completed. SQL> column filename format a50; WITH g_fileio AS ( select fs.snap_id, fs.dbid, fs.instance_number, fs.tsname, fs.filename, fs.phyrds - LAG(fs.phyrds,1,0) OVER( PARTITION BY fs.dbid, fs.instance_number, fs.tsname, fs.filename ORDER BY fs.snap_id ) as delta_phyrds from dba_hist_filestatxs fs ) SELECT e.snap_id, e.instance_number, e.filename, e.delta_phyrds FROM g_fileio e WHERE e.snap_id between 83578 and 83590 and e.tsname = 'DATA_TBS' and e.instance_number = 2 ORDER BY e.snap_id / SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 SNAP_ID INSTANCE_NUMBER FILENAME DELTA_PHYRDS ---------- --------------- -------------------------------------------------- ------------ 83578 2 +DATA/data_tbs_01.dbf 10 83579 2 +DATA/data_tbs_01.dbf 7 83580 2 +DATA/data_tbs_01.dbf 0 83581 2 +DATA/data_tbs_01.dbf 28 83582 2 +DATA/data_tbs_01.dbf 0 83583 2 +DATA/data_tbs_01.dbf 7 83584 2 +DATA/data_tbs_01.dbf 0 83585 2 +DATA/data_tbs_01.dbf 22 83586 2 +DATA/data_tbs_01.dbf 0 83587 2 +DATA/data_tbs_01.dbf 1 83588 2 +DATA/data_tbs_01.dbf 2 83589 2 +DATA/data_tbs_01.dbf 0 83590 2 +DATA/data_tbs_01.dbf 1 13 rows selected. SQL> SQL> exec runStats_pkg.rs_stop; Run1 ran in 8296 hsecs Run2 ran in 1913 hsecs run 1 ran in 433.66% of the time Name Run1 Run2 Diff STAT...messages sent 1 0 -1 STAT...global enqueue get time 3 2 -1 STAT...pinned buffers inspected 0 1 1 STAT...calls to get snapshot scn: kcmgss 8 7 -1 STAT...redo entries 14 15 1 STAT...gc cr blocks received 0 1 1 STAT...Batched IO (bound) vector count 1 0 -1 STAT...table scans (short tables) 3 2 -1 STAT...index scans kdiixs1 1 0 -1 STAT...HSC Heap Segment Block Changes 14 15 1 STAT...Heap Segment Array Inserts 14 15 1 STAT...session cursor cache count 2 1 -1 STAT...workarea executions - optimal 7 8 1 STAT...parse time cpu 3 2 -1 STAT...sorts (memory) 2 3 1 LATCH.IPC other latch 1 0 -1 LATCH.FAL Queue 3 2 -1 LATCH.managed standby latch 3 2 -1 LATCH.job workq parent latch 2 1 -1 LATCH.JS broadcast autostart latch 1 0 -1 STAT...ges messages sent 78 76 -2 STAT...active txn count during cleanout 6 8 2 STAT...cleanout - number of ktugct calls 6 8 2 LATCH.KTF sga latch 2 0 -2 LATCH.job_queue_processes free list latc 2 0 -2 LATCH.hash table modification latch 0 2 2 LATCH.SQL memory manager latch 3 1 -2 LATCH.ksv allocation latch 3 0 -3 LATCH.archive process latch 9 6 -3 LATCH.dml lock allocation 3 0 -3 LATCH.corrupted undo seg latch 3 0 -3 LATCH.deferred cleanup latch 3 0 -3 LATCH.cp sga latch 3 0 -3 LATCH.temporary table state object alloc 3 0 -3 LATCH.ncodef allocation latch 3 0 -3 LATCH.ASM network state latch 3 0 -3 LATCH.Real-time plan statistics latch 385 382 -3 STAT...recursive cpu usage 10 6 -4 STAT...global enqueue gets sync 100 95 -5 STAT...global enqueue releases 98 93 -5 STAT...db block changes 75 70 -5 STAT...consistent changes 60 55 -5 STAT...sql area evicted 0 5 5 LATCH.ksuosstats global area 6 1 -5 LATCH.Result Cache: SO Latch 5 0 -5 STAT...physical read total multi block r 6 0 -6 LATCH.KSXR large replies 6 0 -6 STAT...enqueue releases 11 4 -7 LATCH.temp lob duration state obj alloca 7 0 -7 STAT...enqueue requests 12 4 -8 LATCH.parallel query alloc buffer 13 5 -8 LATCH.KWQP Prop Status 10 2 -8 LATCH.bq:time manger info latch 12 4 -8 LATCH.JS mem alloc latch 10 0 -10 LATCH.JS queue access latch 11 1 -10 STAT...db block gets 58 47 -11 STAT...db block gets from cache 58 47 -11 STAT...undo change vector size 3,272 3,284 12 LATCH.gcs resource scan list 1 13 12 STAT...table scan blocks gotten 522 509 -13 LATCH.file cache latch 19 1 -18 STAT...redo size 4,428 4,408 -20 LATCH.KMG MMAN ready and startup request 27 7 -20 LATCH.global KZLD latch for mem in SGA 26 6 -20 LATCH.Change Notification Hash table lat 27 6 -21 LATCH.Event Group Locks 29 7 -22 LATCH.compile environment latch 29 6 -23 STAT...Batched IO same unit count 26 0 -26 LATCH.ksim membership request latch 48 18 -30 LATCH.bufq statistics 40 8 -32 LATCH.gcs remaster request queue 39 3 -36 LATCH.io pool granule list 46 10 -36 LATCH.ASM network background latch 46 10 -36 STAT...Batched IO vector read count 9 46 37 LATCH.resmgr:actses change group 48 11 -37 STAT...Batched IO (full) vector count 7 45 38 LATCH.resmgr:actses change state 46 7 -39 LATCH.session timer 50 11 -39 LATCH.process group creation 56 13 -43 LATCH.object stats modification 58 15 -43 LATCH.ksxp shared latch 56 13 -43 LATCH.ksxp so latch 56 13 -43 LATCH.OS process: request allocation 56 13 -43 LATCH.ksz_so allocation latch 56 13 -43 LATCH.ges process table freelist 56 13 -43 LATCH.process allocation 58 13 -45 LATCH.parameter table management 58 13 -45 LATCH.resmgr:free threads list 58 13 -45 LATCH.channel handle pool latch 65 20 -45 LATCH.dummy allocation 59 14 -45 LATCH.ASM Keyed state latch 131 75 -56 LATCH.ges resource scan list 67 7 -60 LATCH.session state list latch 73 13 -60 LATCH.loader state object freelist 73 12 -61 LATCH.ASM db client latch 74 12 -62 LATCH.Memory Queue 77 13 -64 STAT...cluster wait time 69 4 -65 LATCH.FOB s.o list latch 109 44 -65 LATCH.resmgr group change latch 84 19 -65 LATCH.KWQMN job cache list latch 83 16 -67 LATCH.OS process allocation 92 19 -73 STAT...bytes received via SQL*Net from c 1,998 2,073 75 LATCH.OS process 97 22 -75 LATCH.io pool granule metadata list 113 37 -76 LATCH.qmn task queue latch 104 24 -80 LATCH.IPC stats buffer allocation latch 114 33 -81 LATCH.JS broadcast load blnc latch 108 27 -81 LATCH.JS Sh mem access 109 28 -81 STAT...gc CPU used by this session 90 8 -82 STAT...workarea memory allocated 74 -9 -83 LATCH.job_queue_processes parameter latc 110 27 -83 LATCH.JS broadcast add buf latch 111 27 -84 LATCH.JS broadcast drop buf latch 111 27 -84 LATCH.ges timeout list 108 18 -90 LATCH.KFMD SGA 238 140 -98 STAT...no buffer to keep pinned count 112 8 -104 LATCH.ASM map operation freelist 197 90 -107 LATCH.session switching 144 36 -108 LATCH.ksv class latch 151 37 -114 LATCH.longop free list parent 152 35 -117 STAT...shared hash latch upgrades - no w 5,967 5,834 -133 LATCH.shared pool simulator 313 449 136 LATCH.Lsod array latch 276 136 -140 LATCH.list of block allocation 170 17 -153 LATCH.sort extent pool 214 59 -155 LATCH.client/application info 514 351 -163 LATCH.KFK SGA Libload latch 175 0 -175 STAT...gc local grants 3,507 3,691 184 LATCH.k2q global data latch 249 61 -188 STAT...dirty buffers inspected 28 220 192 LATCH.ktm global data 310 95 -215 LATCH.call allocation 278 62 -216 STAT...physical read total IO requests 6,050 5,832 -218 STAT...physical read IO requests 6,050 5,832 -218 LATCH.kks stats 159 379 220 LATCH.kwqbsn:qsga 307 49 -258 STAT...CPU used when call started 352 87 -265 STAT...CPU used by this session 351 86 -265 LATCH.name-service pending queue 408 108 -300 LATCH.Shared B-Tree 414 69 -345 LATCH.parameter list 435 80 -355 LATCH.resmgr:active threads 551 128 -423 STAT...table scan rows gotten 31,615 31,180 -435 LATCH.Result Cache: RC Latch 296 768 472 LATCH.Consistent RBA 1,703 1,230 -473 LATCH.lgwr LWN SCN 1,715 1,232 -483 LATCH.lob segment hash table latch 683 196 -487 LATCH.mostly latch-free SCN 1,813 1,283 -530 LATCH.pesom_free_list 625 83 -542 LATCH.pesom_hash_node 625 83 -542 STAT...min active SCN optimization appli 598 46 -552 LATCH.post/wait queue 1,166 1,729 563 LATCH.name-service memory objects 792 202 -590 LATCH.transaction branch allocation 616 17 -599 LATCH.Result Cache: MB Latch 100 752 652 STAT...free buffer requested 6,528 5,843 -685 STAT...physical reads 6,518 5,832 -686 STAT...physical reads cache 6,518 5,832 -686 STAT...hot buffers moved to head of LRU 507 1,301 794 STAT...gcs messages sent 3,011 2,142 -869 STAT...gc remote grants 3,011 2,141 -870 STAT...free buffer inspected 5,385 6,307 922 LATCH.name-service request queue 1,233 281 -952 LATCH.active checkpoint queue latch 1,445 438 -1,007 LATCH.global tx hash mapping 2,449 1,365 -1,084 LATCH.KJC message pool free list 2,239 1,108 -1,131 LATCH.redo allocation 5,275 3,751 -1,524 LATCH.lob segment dispenser latch 2,380 803 -1,577 LATCH.space background task latch 2,314 687 -1,627 LATCH.name-service namespace bucket 2,345 588 -1,757 LATCH.gcs opaque info freelist 3,294 1,514 -1,780 LATCH.message pool operations parent lat 439 2,280 1,841 LATCH.cache table scan latch 2,674 551 -2,123 LATCH.k2q lock allocation 4,860 2,711 -2,149 LATCH.ges value block free list 4,930 2,730 -2,200 LATCH.redo writing 6,674 4,181 -2,493 LATCH.object queue header heap 4,735 1,765 -2,970 LATCH.resmgr:schema config 4,233 973 -3,260 LATCH.SQL memory manager workarea list l 5,232 1,420 -3,812 LATCH.multiblock read objects 5,638 1,322 -4,316 STAT...physical reads cache prefetch 1,383 5,783 4,400 STAT...Batched IO block miss count 943 5,785 4,842 STAT...Batched IO vector block count 924 5,830 4,906 LATCH.active service list 9,435 4,261 -5,174 LATCH.messages 14,242 8,789 -5,453 STAT...buffer is not pinned count 19,153 24,750 5,597 STAT...rows fetched via callback 13 5,847 5,834 STAT...sorts (rows) 5,860 11,707 5,847 STAT...user I/O wait time 6,568 382 -6,186 STAT...non-idle wait time 6,641 389 -6,252 STAT...Elapsed Time 8,304 1,919 -6,385 STAT...DB time 6,960 470 -6,490 LATCH.gcs shadows freelist 8,560 1,918 -6,642 LATCH.sequence cache 10,968 1,950 -9,018 LATCH.cache buffer handles 13,152 3,516 -9,636 LATCH.ges process hash list 11,989 2,155 -9,834 LATCH.gcs resource freelist 14,865 4,662 -10,203 LATCH.ges deadlock list 15,556 4,574 -10,982 STAT...non-idle wait count 13,259 1,939 -11,320 STAT...table fetch by rowid 59 11,642 11,583 LATCH.JS queue state obj latch 21,384 5,346 -16,038 STAT...consistent gets from cache (fastp 19,613 2,000 -17,613 STAT...no work - consistent read gets 19,696 1,984 -17,712 LATCH.cache buffers lru chain 33,406 14,987 -18,419 LATCH.AQ dequeue txn counter latch 29,714 6,847 -22,867 LATCH.session allocation 34,918 10,419 -24,499 LATCH.channel operations parent latch 64,173 90,880 26,707 LATCH.ges resource table freelist 46,872 13,694 -33,178 LATCH.enqueues 46,170 12,389 -33,781 LATCH.PL/SQL warning settings 45,745 10,780 -34,965 LATCH.checkpoint queue latch 65,685 21,443 -44,242 LATCH.transaction allocation 59,824 13,825 -45,999 STAT...buffer is pinned count 56,978 4,382 -52,596 LATCH.kokc descriptor allocation latch 74,372 17,176 -57,196 LATCH.gcs remastering latch 83,700 23,246 -60,454 STAT...index fetch by key 76,011 5,847 -70,164 LATCH.KJCT flow control latch 109,836 35,141 -74,695 STAT...session uga memory 130,976 0 -130,976 STAT...consistent gets - examination 151,115 17,482 -133,633 LATCH.gc element 219,812 77,684 -142,128 LATCH.DML lock allocation 198,766 49,086 -149,680 STAT...consistent gets 170,829 19,482 -151,347 STAT...consistent gets from cache 170,829 19,482 -151,347 STAT...session logical reads 170,887 19,529 -151,358 LATCH.ASM map operation hash table 215,250 61,951 -153,299 LATCH.session idle bit 225,463 55,923 -169,540 STAT...session pga memory 262,144 0 -262,144 LATCH.enqueue hash chains 454,576 114,668 -339,908 LATCH.gcs resource hash 617,159 161,627 -455,532 LATCH.JS slv state obj latch -701,720 1 701,721 LATCH.gcs partitioned table hash 1,052,104 273,491 -778,613 LATCH.object queue header operation 1,010,890 191,055 -819,835 LATCH.ges caches resource lists 1,403,509 333,283 -1,070,226 LATCH.shared pool 1,544,904 367,558 -1,177,346 STAT...session pga memory max 1,900,544 0 -1,900,544 LATCH.simulator hash latch 2,789,859 588,707 -2,201,152 STAT...session uga memory max 2,526,904 0 -2,526,904 LATCH.ges domain table 5,953,806 1,405,820 -4,547,986 LATCH.ges enqueue table freelist 5,977,611 1,413,057 -4,564,554 LATCH.undo global data 6,750,345 1,329,706 -5,420,639 STAT...cell physical IO interconnect byt 53,395,456 47,775,744 -5,619,712 STAT...physical read total bytes 53,395,456 47,775,744 -5,619,712 STAT...physical read bytes 53,395,456 47,775,744 -5,619,712 LATCH.ges resource hash list 8,925,458 2,108,690 -6,816,768 LATCH.ges group table 8,939,270 2,111,447 -6,827,823 LATCH.ges process parent latch 11,947,686 2,824,413 -9,123,273 LATCH.row cache objects 13,616,105 2,480,533 -11,135,572 LATCH.cache buffers chains 61,226,868 12,636,624 -48,590,244 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 133,264,232 28,933,036-104,331,196 460.60% PL/SQL procedure successfully completed. SQL>