SQL> exec runStats_pkg.rs_start; PL/SQL procedure successfully completed. SQL> WITH c1 as (SELECT owner, table_name, column_name, histogram, num_nulls, avg_col_len FROM all_my_tab_col_stats WHERE owner not in ('SYS','SYSMAN','SYSTEM','OUTLN','TSMSYS','DBSNMP','WMSYS','EXFSYS','CTXSYS','DMSYS','XDB','MDSYS','ORDSYS','OLAPSYS')) 2 SELECT distinct owner, table_name FROM ( 3 SELECT owner, table_name FROM c1 WHERE HISTOGRAM = 'FREQUENCY' 4 INTERSECT 5 SELECT owner, table_name FROM c1 WHERE NUM_NULLS > 1 6 INTERSECT 7 SELECT owner, table_name FROM c1 WHERE avg_col_len > 25 8 ) 9 ; OWNER TABLE_NAME ------------------------------ ------------------------------ SQL> exec runStats_pkg.rs_middle; PL/SQL procedure successfully completed. SQL> SELECT distinct owner, table_name 2 FROM all_my_tab_col_stats 3 WHERE 4 owner not in ('SYS','SYSMAN','SYSTEM','OUTLN','TSMSYS','DBSNMP','WMSYS','EXFSYS','CTXSYS','DMSYS','XDB','MDSYS','ORDSYS','OLAPSYS') 5 GROUP BY owner, table_name 6 HAVING SUM(case WHEN histogram = 'FREQUENCY' THEN 1 ELSE 0 END) > 0 AND 7 SUM(case WHEN NUM_NULLS > 0 THEN 1 ELSE 0 END) > 0 AND 8 SUM(case WHEN avg_col_len > 25 THEN 1 ELSE 0 END) > 0 9 ; OWNER TABLE_NAME ------------------------------ ------------------------------ SQL> exec runStats_pkg.rs_stop; Run1 ran in 4181 hsecs Run2 ran in 2597 hsecs run 1 ran in 160.99% of the time Name Run1 Run2 Diff LATCH.ncodef allocation latch 0 1 1 STAT...calls to kcmgcs 6 7 1 LATCH.threshold alerts latch 1 0 -1 STAT...shared hash latch upgra 1 0 -1 STAT...redo synch writes 1 0 -1 STAT...consistent changes 28 27 -1 STAT...enqueue waits 1 0 -1 STAT...table scans (direct rea 1 0 -1 STAT...table scans (long table 2 1 -1 STAT...cleanout - number of kt 6 7 1 STAT...active txn count during 6 7 1 STAT...parse time cpu 1 0 -1 STAT...sql area purged 1 0 -1 STAT...Heap Segment Array Inse 12 13 1 STAT...HSC Heap Segment Block 12 13 1 STAT...index fetch by key 1 0 -1 LATCH.ksv allocation latch 0 1 1 LATCH.session switching 1 2 1 STAT...bytes received via SQL* 1,678 1,679 1 STAT...parse count (hard) 2 1 -1 LATCH.dml lock allocation 0 1 1 LATCH.FAL request queue 1 0 -1 LATCH.FAL subheap alocation 1 0 -1 LATCH.archive control 1 0 -1 LATCH.cp sga latch 0 1 1 LATCH.deferred cleanup latch 0 1 1 LATCH.resmgr:actses change sta 2 1 -1 LATCH.resmgr:actses change gro 2 1 -1 LATCH.Event Group Locks 2 1 -1 LATCH.FOB s.o list latch 2 0 -2 LATCH.post/wait queue 3 1 -2 LATCH.user lock 2 0 -2 LATCH.ksz_so allocation latch 2 0 -2 STAT...recursive cpu usage 3 1 -2 STAT...parse time elapsed 2 0 -2 LATCH.process group creation 2 0 -2 LATCH.OS process: request allo 2 0 -2 LATCH.ksuosstats global area 3 1 -2 LATCH.job_queue_processes para 2 0 -2 LATCH.compile environment latc 2 0 -2 LATCH.resmgr:active threads 13 15 2 LATCH.PL/SQL warning settings 6 3 -3 LATCH.process allocation 3 0 -3 STAT...table scans (short tabl 3 0 -3 LATCH.session timer 13 10 -3 LATCH.internal temp table obje 3 0 -3 LATCH.message pool operations 4 1 -3 LATCH.transaction branch alloc 13 10 -3 LATCH.sequence cache 3 0 -3 LATCH.JS slv state obj latch 4 1 -3 STAT...sorts (memory) 3 0 -3 STAT...enqueue conversions 3 0 -3 LATCH.session state list latch 4 0 -4 LATCH.resmgr:schema config 9 5 -4 LATCH.loader state object free 4 0 -4 LATCH.list of block allocation 4 0 -4 LATCH.dummy allocation 5 1 -4 LATCH.parameter table manageme 4 0 -4 LATCH.resmgr:free threads list 4 0 -4 LATCH.channel handle pool latc 5 1 -4 STAT...messages sent 4 0 -4 STAT...calls to kcmgrs 50 54 4 LATCH.KMG MMAN ready and start 14 9 -5 LATCH.resmgr group change latc 5 0 -5 LATCH.object queue header heap 17 12 -5 STAT...workarea executions - o 10 4 -6 STAT...session cursor cache hi 10 4 -6 STAT...db block changes 47 41 -6 STAT...calls to kcmgas 8 1 -7 STAT...parse count (total) 11 4 -7 STAT...redo entries 20 13 -7 LATCH.sort extent pool 8 1 -7 LATCH.Change Notification Hash 15 8 -7 LATCH.archive process latch 16 9 -7 STAT...physical write total mu 8 0 -8 LATCH.KWQS pqueue ctx latch 8 0 -8 LATCH.Shared B-Tree 10 2 -8 LATCH.object stats modificatio 8 0 -8 STAT...db block gets from cach 38 29 -9 LATCH.OS process 10 1 -9 LATCH.cache table scan latch 5 15 10 STAT...index scans kdiixs1 10 0 -10 LATCH.active checkpoint queue 21 11 -10 STAT...physical write total IO 11 0 -11 STAT...consistent gets - exami 18 7 -11 STAT...buffer is not pinned co 11 0 -11 STAT...physical write IO reque 11 0 -11 LATCH.ASM db client latch 30 18 -12 LATCH.OS process allocation 20 8 -12 STAT...execute count 18 5 -13 STAT...opened cursors cumulati 19 5 -14 LATCH.space background task la 32 18 -14 STAT...physical read IO reques 31 16 -15 LATCH.session idle bit 31 15 -16 STAT...CPU used by this sessio 36 20 -16 LATCH.kks stats 19 3 -16 LATCH.commit callback allocati 17 1 -16 LATCH.In memory undo latch 19 1 -18 STAT...physical read total IO 36 16 -20 STAT...CPU used when call star 42 20 -22 LATCH.multiblock read objects 10 32 22 STAT...enqueue releases 24 1 -23 STAT...enqueue requests 24 1 -23 STAT...calls to get snapshot s 33 8 -25 STAT...Number of read IOs issu 26 0 -26 LATCH.client/application info 27 1 -26 STAT...physical read total mul 29 0 -29 LATCH.mostly latch-free SCN 40 9 -31 LATCH.lgwr LWN SCN 40 9 -31 LATCH.simulator hash latch 386 418 32 LATCH.Real-time plan statistic 32 0 -32 LATCH.Consistent RBA 40 8 -32 STAT...user I/O wait time 39 6 -33 LATCH.kokc descriptor allocati 40 0 -40 LATCH.active service list 96 50 -46 LATCH.undo global data 81 32 -49 STAT...DB time 74 24 -50 STAT...workarea memory allocat -43 43 86 LATCH.simulator lru latch 301 208 -93 LATCH.redo allocation 121 27 -94 LATCH.checkpoint queue latch 290 191 -99 LATCH.channel operations paren 248 139 -109 LATCH.redo writing 164 54 -110 LATCH.JS queue state obj latch 324 180 -144 STAT...physical reads cache 268 115 -153 STAT...free buffer requested 270 116 -154 STAT...undo change vector size 3,272 3,112 -160 STAT...physical reads cache pr 263 99 -164 STAT...recursive calls 210 2 -208 LATCH.messages 466 230 -236 LATCH.MinActiveScn Latch 248 3 -245 STAT...physical writes direct 268 0 -268 STAT...db block gets direct 268 0 -268 STAT...physical writes non che 268 0 -268 STAT...physical writes direct 268 0 -268 STAT...physical writes 268 0 -268 STAT...db block gets 306 29 -277 LATCH.enqueues 794 406 -388 LATCH.cache buffers lru chain 547 127 -420 LATCH.SQL memory manager worka 1,073 608 -465 LATCH.shared pool simulator 506 33 -473 LATCH.shared pool 618 83 -535 LATCH.session allocation 591 0 -591 LATCH.enqueue hash chains 1,027 412 -615 LATCH.object queue header oper 814 139 -675 STAT...consistent gets from ca 855 1,609 754 STAT...consistent gets from ca 831 1,586 755 STAT...table scan blocks gotte 2,395 1,590 -805 STAT...no work - consistent re 2,404 1,590 -814 STAT...consistent gets 2,445 1,609 -836 LATCH.row cache objects 1,169 234 -935 STAT...session logical reads 2,751 1,638 -1,113 STAT...Elapsed Time 4,182 2,597 -1,585 STAT...consistent gets direct 1,590 0 -1,590 STAT...physical reads direct 1,590 0 -1,590 STAT...redo size 5,700 4,100 -1,600 STAT...physical reads 1,858 115 -1,743 LATCH.transaction allocation 2,393 1 -2,392 LATCH.cache buffers chains 5,566 2,035 -3,531 STAT...sorts (rows) 6,660 0 -6,660 LATCH.DML lock allocation 10,737 1 -10,736 STAT...table scan rows gotten 236,528 140,460 -96,068 STAT...session uga memory -65,464 65,464 130,928 STAT...session pga memory -65,536 131,072 196,608 STAT...Effective IO time 363,831 0 -363,831 STAT...physical write total by 2,195,456 0 -2,195,456 STAT...physical write bytes 2,195,456 0 -2,195,456 STAT...physical read bytes 15,220,736 942,080 -14,278,656 STAT...physical read total byt 15,302,656 942,080 -14,360,576 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 29,348 5,983 -23,365 490.52% PL/SQL procedure successfully completed. SQL> spool off