SQL> SELECT o.owner, o.object_type, COUNT(*) 2 FROM all_my_objects o 3 GROUP BY CUBE (o.object_type, o.owner); OWNER OBJECT_TYPE COUNT(*) ------------------------------ ------------------- ---------- 327 rows selected. SQL> exec runStats_pkg.rs_middle; PL/SQL procedure successfully completed. SQL> SELECT o.owner, o.object_type, COUNT(*) 2 FROM all_my_objects o 3 GROUP BY o.object_type, o.owner 4 UNION ALL 5 SELECT NULL, o.object_type, COUNT(*) 6 FROM all_my_objects o 7 GROUP BY o.object_type 8 UNION ALL 9 SELECT o.owner, NULL, COUNT(*) 10 FROM all_my_objects o 11 GROUP BY o.owner 12 UNION ALL 13 SELECT NULL, NULL, COUNT(*) 14 FROM all_my_objects o; OWNER OBJECT_TYPE COUNT(*) ------------------------------ ------------------- ---------- 327 rows selected. SQL> exec runStats_pkg.rs_stop; Run1 ran in 5314 hsecs Run2 ran in 10253 hsecs run 1 ran in 51.83% of the time Name Run1 Run2 Diff LATCH.threshold alerts latch 1 2 1 LATCH.resmgr:actses change gro 2 3 1 LATCH.Event Group Locks 2 3 1 LATCH.cp sga latch 1 2 1 LATCH.SQL memory manager latch 2 3 1 LATCH.ncodef allocation latch 1 2 1 LATCH.archive control 1 2 1 LATCH.dml lock allocation 1 2 1 LATCH.flashback archiver latch 0 1 1 LATCH.ktm global data 0 1 1 LATCH.deferred cleanup latch 1 2 1 LATCH.kks stats 4 5 1 LATCH.session switching 2 3 1 LATCH.ksv allocation latch 1 2 1 LATCH.queued dump request 0 1 1 STAT...parse count (hard) 2 1 -1 STAT...HSC Heap Segment Block 13 14 1 STAT...Heap Segment Array Inse 13 14 1 STAT...redo ordering marks 1 0 -1 STAT...redo subscn max counts 1 0 -1 STAT...calls to kcmgas 2 1 -1 STAT...redo entries 13 14 1 STAT...application wait time 0 1 1 LATCH.kwqbsn:qsga 2 4 2 LATCH.JS Sh mem access 1 3 2 LATCH.user lock 2 4 2 LATCH.resmgr:actses change sta 2 4 2 LATCH.Shared B-Tree 3 5 2 LATCH.compile environment latc 2 4 2 LATCH.kokc descriptor allocati 2 4 2 LATCH.list of block allocation 2 4 2 LATCH.sort extent pool 1 3 2 LATCH.process group creation 2 4 2 LATCH.parameter table manageme 6 8 2 LATCH.channel handle pool latc 3 5 2 LATCH.OS process: request allo 2 4 2 LATCH.ksz_so allocation latch 2 4 2 STAT...workarea executions - o 10 8 -2 STAT...table fetch continued r 2 0 -2 STAT...recursive cpu usage 1 3 2 STAT...enqueue waits 1 3 2 LATCH.JS slv state obj latch 4 7 3 LATCH.PL/SQL warning settings 6 9 3 LATCH.job_queue_processes para 1 4 3 LATCH.sequence cache 3 6 3 LATCH.process allocation 3 6 3 LATCH.ksuosstats global area 4 7 3 STAT...session cursor cache co 1 -2 -3 STAT...cursor authentications 5 2 -3 STAT...table scans (long table 1 4 3 STAT...table scans (direct rea 1 4 3 STAT...active txn count during 6 9 3 STAT...cleanout - number of kt 6 9 3 STAT...calls to kcmgcs 6 9 3 LATCH.session state list latch 4 8 4 LATCH.FAL subheap alocation 1 5 4 LATCH.FAL request queue 1 5 4 LATCH.dummy allocation 5 9 4 LATCH.FIB s.o chain latch 4 0 -4 LATCH.resmgr:free threads list 4 8 4 STAT...rows fetched via callba 4 0 -4 LATCH.resmgr group change latc 5 10 5 STAT...db block gets 27 32 5 STAT...db block gets from cach 27 32 5 STAT...enqueue requests 4 9 5 STAT...enqueue releases 4 9 5 LATCH.object stats modificatio 1 7 6 LATCH.post/wait queue 1 7 6 STAT...db block changes 39 46 7 STAT...consistent changes 25 32 7 STAT...shared hash latch upgra 7 0 -7 LATCH.qmn task queue latch 8 16 8 LATCH.FOB s.o list latch 8 0 -8 STAT...buffer is pinned count 8 0 -8 LATCH.message pool operations 4 13 9 LATCH.OS process 10 19 9 STAT...enqueue conversions 3 12 9 LATCH.library cache load lock 12 2 -10 LATCH.resmgr:schema config 10 22 12 LATCH.parallel query alloc buf 5 17 12 STAT...workarea memory allocat -12 0 12 STAT...cluster key scans 12 0 -12 STAT...cluster key scan block 12 0 -12 LATCH.session idle bit 72 85 13 STAT...physical reads cache 14 1 -13 STAT...free buffer requested 15 1 -14 STAT...calls to kcmgrs 51 65 14 LATCH.buffer pool 16 1 -15 LATCH.Change Notification Hash 18 34 16 LATCH.KMG MMAN ready and start 18 34 16 STAT...index fetch by key 16 0 -16 LATCH.session timer 19 36 17 LATCH.transaction branch alloc 19 36 17 LATCH.longop free list parent 20 37 17 LATCH.Consistent RBA 20 39 19 STAT...sorts (memory) 20 0 -20 LATCH.mostly latch-free SCN 22 43 21 LATCH.lgwr LWN SCN 22 43 21 STAT...parse count (total) 30 9 -21 LATCH.archive process latch 19 41 22 LATCH.OS process allocation 24 46 22 LATCH.Memory Management Latch 24 1 -23 STAT...index scans kdiixs1 25 0 -25 LATCH.client/application info 27 53 26 STAT...session cursor cache hi 36 9 -27 LATCH.resmgr:active threads 24 52 28 LATCH.Real-time plan statistic 35 63 28 STAT...calls to get snapshot s 53 24 -29 STAT...opened cursors cumulati 42 10 -32 STAT...table fetch by rowid 42 10 -32 LATCH.space background task la 40 75 35 LATCH.ASM db client latch 36 71 35 STAT...execute count 47 10 -37 LATCH.MinActiveScn Latch 24 63 39 STAT...consistent gets from ca 74 34 -40 STAT...consistent gets - exami 63 9 -54 LATCH.In memory undo latch 19 73 54 LATCH.undo global data 32 87 55 LATCH.redo allocation 65 126 61 STAT...CPU used when call star 65 128 63 STAT...CPU used by this sessio 61 126 65 LATCH.cache table scan latch 384 451 67 LATCH.object queue header heap 120 188 68 LATCH.shared pool simulator 298 377 79 LATCH.active checkpoint queue 126 211 85 STAT...undo change vector size 3,116 3,204 88 STAT...consistent gets from ca 156 44 -112 STAT...buffer is not pinned co 133 20 -113 LATCH.shared pool 592 712 120 LATCH.active service list 108 234 126 LATCH.multiblock read objects 772 906 134 STAT...redo size 4,060 4,200 140 LATCH.redo writing 214 370 156 STAT...bytes received via SQL* 1,517 1,744 227 STAT...physical read total IO 111 389 278 STAT...physical read IO reques 111 389 278 STAT...physical read total mul 95 380 285 STAT...Number of read IOs issu 97 388 291 LATCH.channel operations paren 299 597 298 STAT...recursive calls 444 37 -407 LATCH.JS queue state obj latch 360 792 432 LATCH.row cache objects 780 1,228 448 LATCH.session allocation 711 186 -525 LATCH.messages 670 1,244 574 LATCH.checkpoint queue latch 859 1,536 677 LATCH.simulator lru latch 3,732 4,584 852 LATCH.SQL memory manager worka 1,158 2,110 952 LATCH.enqueues 868 1,862 994 LATCH.enqueue hash chains 926 2,000 1,074 STAT...bytes sent via SQL*Net 8,833 10,287 1,454 LATCH.simulator hash latch 8,631 10,618 1,987 LATCH.transaction allocation 2,391 4,781 2,390 LATCH.cache buffers lru chain 19,794 24,594 4,800 STAT...Elapsed Time 5,381 10,256 4,875 STAT...user I/O wait time 1,663 6,948 5,285 STAT...DB time 1,720 7,114 5,394 LATCH.object queue header oper 39,393 48,737 9,344 LATCH.DML lock allocation 10,597 21,199 10,602 LATCH.cache buffers chains 50,849 63,250 12,401 STAT...consistent gets 6,181 24,144 17,963 STAT...session logical reads 6,208 24,176 17,968 STAT...no work - consistent re 6,101 24,110 18,009 STAT...physical reads 6,039 24,101 18,062 STAT...table scan blocks gotte 6,025 24,100 18,075 STAT...consistent gets direct 6,025 24,100 18,075 STAT...physical reads direct 6,025 24,100 18,075 STAT...session pga memory 327,680 -65,536 -393,216 STAT...sorts (rows) 412,191 0 -412,191 STAT...table scan rows gotten 411,120 1,644,480 1,233,360 STAT...Effective IO time 15,987,517 69,457,684 53,470,167 STAT...physical read total byt 49,471,488 197,435,392 147,963,904 STAT...physical read bytes 49,471,488 197,435,392 147,963,904 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 145,519 194,304 48,785 74.89% PL/SQL procedure successfully completed. SQL> spool off