set feedback on set pagesize 1000 ---------------------------------------------------------------------- -- drop old metrics from prior runs ---------------------------------------------------------------------- execute ssth_ctrl.drop_all_metrics ; PL/SQL procedure successfully completed. ---------------------------------------------------------------------- -- run each technique in a separate session -- -- each run selects 0 rows from an indexed 46,000+ row table ---------------------------------------------------------------------- spool off ---------------------------------------------------------------------- -- new SQL*Plus session starts here ---------------------------------------------------------------------- ------------------------------------------------------------ -- setup ------------------------------------------------------------ create table t2 as select * from t; Table created. insert into t2 with ta as ( select * from t2 ) select ta1.key, ta1.c1, ta1.c2, ta1.c3, ta1.c4 from ta ta1, ta ta2, ta ta3, ta ta4, ta ta5, ta ta6 ; 46656 rows created. commit; Commit complete. select count(*) from t2 ; COUNT(*) ---------- 46662 1 row selected. create index t2i on t2( c1 ) ; Index created. ------------------------------------------------------------ -- start the test ------------------------------------------------------------ execute ssth_ctrl.start_test( 'Method 1' ); PL/SQL procedure successfully completed. variable v_c1 varchar2(6) execute :v_c1 := 'Z' PL/SQL procedure successfully completed. set feedback off column c_footer noprint new_value v_footer repfooter left v_footer select case sign( nvl( tb.row_count, 0 ) - 1 ) when 1 then chr(10) || tb.row_count || ' Items Found' -- plural when 0 then chr(10) || tb.row_count || ' Item Found' -- singular else '** No Items Found **' end as c_footer , tb.key, tb.c1, tb.c2, tb.c3, tb.c4 from ( select 1 from dual ) ta left outer join ( select to_char( count(*) over () ) as row_count, t2.* from t2 where c1 like :v_c1 ) tb on (1=1) ; KEY C1 C2 C3 C4 ---------- ------ ---------- ---------- ---------- ** No Items Found ** execute ssth_ctrl.stop_test( 'Method 1' ); drop table t2; spool off ---------------------------------------------------------------------- -- new SQL*Plus session starts here ---------------------------------------------------------------------- ------------------------------------------------------------ -- setup ------------------------------------------------------------ create table t2 as select * from t; Table created. insert into t2 with ta as ( select * from t2 ) select ta1.key, ta1.c1, ta1.c2, ta1.c3, ta1.c4 from ta ta1, ta ta2, ta ta3, ta ta4, ta ta5, ta ta6 ; 46656 rows created. commit; Commit complete. select count(*) from t2 ; COUNT(*) ---------- 46662 1 row selected. create index t2i on t2( c1 ) ; Index created. ------------------------------------------------------------ -- start the test ------------------------------------------------------------ execute ssth_ctrl.start_test( 'Method 2' ); PL/SQL procedure successfully completed. variable v_c1 varchar2(6) execute :v_c1 := 'Z' PL/SQL procedure successfully completed. select to_char(key) as key, c1, c2, c3, c4 from t2 where c1 like :v_c1 union all select '** No Items Found **' as key , null, null, null, null from dual where not exists ( select * from t2 where c1 like :v_c1 ) ; KEY C1 C2 C3 C4 ---------------------------------------- ------ ---------- ---------- ---------- ** No Items Found ** 1 row selected. execute ssth_ctrl.stop_test( 'Method 2' ); PL/SQL procedure successfully completed. drop table t2; Table dropped. spool off ---------------------------------------------------------------------- -- report the results ---------------------------------------------------------------------- set echo off =--------------------------------------------------------------------= = Statistics =--------------------------------------------------------------------= SQL Snippets METRIC_NAME Method Other Method ------------------------------ ------------ ------------ Elapsed Time (1/100 sec) 4 10 session pga memory 131,072 196,608 redo size 46,696 46,416 table scan rows gotten 13,439 31,386 IMU Redo allocation size 11,036 11,036 physical read bytes 8,192 8,192 physical read total bytes 8,192 8,192 undo change vector size 2,344 2,276 bytes received via SQL*Net fro 2,252 2,071 sorts (rows) 2,074 2,074 bytes sent via SQL*Net to clie 1,753 1,694 IMU undo allocation size 880 880 session logical reads 123 184 consistent gets 76 142 consistent gets from cache 76 142 table scan blocks gotten 58 116 buffer is not pinned count 58 116 no work - consistent read gets 58 116 db block gets 47 42 db block gets from cache 47 42 db block changes 44 41 redo entries 32 30 user calls 19 19 recursive calls 18 26 calls to get snapshot scn: kcm 18 25 SQL*Net roundtrips to/from cli 13 13 calls to kcmgcs 11 10 parse count (total) 10 12 opened cursors cumulative 10 12 execute count 9 11 enqueue requests 9 9 enqueue releases 9 9 commit cleanouts successfully 6 6 commit cleanouts 6 6 workarea executions - optimal 6 6 heap block compress 6 6 deferred (CURRENT) block clean 6 6 parse count (hard) 5 5 DB time 4 10 CPU used when call started 3 7 CPU used by this session 3 5 recursive cpu usage 3 5 consistent gets - examination 3 4 session cursor cache hits 3 3 shared hash latch upgrades - n 2 4 index scans kdiixs1 2 4 opened cursors current 2 2 sorts (memory) 2 1 table scans (short tables) 1 2 messages sent 1 1 physical read total IO request 1 1 IMU Flushes 1 1 user commits 1 1 physical reads 1 1 physical reads cache 1 1 redo synch writes 1 1 physical read IO requests 1 1 free buffer requested 1 1 calls to kcmgas 1 1 redo synch time 1 1 active txn count during cleano 1 0 cleanout - number of ktugct ca 1 0 user I/O wait time 0 1 =--------------------------------------------------------------------= = Latch Gets =--------------------------------------------------------------------= SQL Snippets METRIC_NAME Method Other Method ------------------------------ ------------ ------------ cache buffers chains 352 451 row cache objects 213 249 shared pool 186 174 library cache 163 186 library cache pin 81 93 library cache lock 58 70 session idle bit 40 40 redo allocation 38 35 enqueue hash chains 18 18 enqueues 14 14 kks stats 14 14 session allocation 12 12 simulator hash latch 7 7 simulator lru latch 7 7 undo global data 7 6 SQL memory manager workarea li 6 6 In memory undo latch 5 5 PL/SQL warning settings 5 5 messages 3 3 redo writing 3 3 session state list latch 3 3 object queue header operation 3 1 shared pool simulator 2 12 library cache lock allocation 2 2 dml lock allocation 2 2 compile environment latch 2 2 cache buffers lru chain 2 1 post/wait queue 1 2 Consistent RBA 1 1 mostly latch-free SCN 1 1 lgwr LWN SCN 1 1 ------------ ------------ sum 1,252 1,426 Techniques that use a small number of latches scale better than techniques that use a large number of latches.