SQL> ---------------------------------------- SQL> -- new SQL*Plus session starts here SQL> ---------------------------------------- SQL> ---------------------------------------------------------------------- SQL> -- setup SQL> ---------------------------------------------------------------------- SQL> create table t1( key number, c1 number ) nologging storage( buffer_pool keep ) ; Table created. SQL> create table t2( key number, c1 number ) nologging storage( buffer_pool keep ) ; Table created. SQL> create table t3( key number, c1 number ) nologging storage( buffer_pool keep ) ; Table created. SQL> insert /*+ APPEND */ into t1 2 select level, null from dual connect by level <= 10000 ; 10000 rows created. SQL> commit; Commit complete. SQL> insert /*+ APPEND */ into t2 select * from t1 ; 10000 rows created. SQL> insert /*+ APPEND */ into t3 select * from t1 ; 10000 rows created. SQL> commit; Commit complete. SQL> -- drop old metrics saved in prior runs SQL> execute ssth_ctrl.initialize_metrics ; PL/SQL procedure successfully completed. SQL> -- gather stats SQL> execute dbms_stats.gather_schema_stats(null); PL/SQL procedure successfully completed. SQL> ---------------------------------------- SQL> -- new SQL*Plus session starts here SQL> ---------------------------------------- SQL> -- try a bunch of things to set the instance SQL> -- to the same state for each run SQL> SQL> alter system checkpoint ; System altered. SQL> alter system flush shared_pool ; System altered. SQL> alter system flush buffer_cache ; System altered. SQL> alter tablespace USERS offline ; Tablespace altered. SQL> alter tablespace USERS online ; Tablespace altered. SQL> ---------------------------------------- SQL> -- new SQL*Plus session starts here SQL> ---------------------------------------- SQL> SQL> -- this inserts some V_$ stats into a global temp table SQL> SQL> execute ssth_ctrl.start_test( 'Run 1' ); PL/SQL procedure successfully completed. SQL> -- run the statement to be benchmarked SQL> SQL> update t1 set c1 = 0 ; 10000 rows updated. SQL> commit; Commit complete. SQL> -- this inserts the V_$ stats into anoter global temp table SQL> -- and calculate the difference between these stats SQL> -- and the ones collected earlier SQL> SQL> execute ssth_ctrl.stop_test( 'Run 1' ); PL/SQL procedure successfully completed. SQL> ---------------------------------------- SQL> -- new SQL*Plus session starts here SQL> ---------------------------------------- SQL> -- try a bunch of things to set the instance SQL> -- to the same state for each run SQL> SQL> alter system checkpoint ; System altered. SQL> alter system flush shared_pool ; System altered. SQL> alter system flush buffer_cache ; System altered. SQL> alter tablespace USERS offline ; Tablespace altered. SQL> alter tablespace USERS online ; Tablespace altered. SQL> ---------------------------------------- SQL> -- new SQL*Plus session starts here SQL> ---------------------------------------- SQL> SQL> execute ssth_ctrl.start_test( 'Run 2' ); PL/SQL procedure successfully completed. SQL> -- run the statement to be benchmarked SQL> SQL> update t2 set c1 = 0 ; 10000 rows updated. SQL> commit; Commit complete. SQL> execute ssth_ctrl.stop_test( 'Run 2' ); PL/SQL procedure successfully completed. SQL> ---------------------------------------- SQL> -- new SQL*Plus session starts here SQL> ---------------------------------------- SQL> -- try a bunch of things to set the instance SQL> -- to the same state for each run SQL> SQL> alter system checkpoint ; System altered. SQL> alter system flush shared_pool ; System altered. SQL> alter system flush buffer_cache ; System altered. SQL> alter tablespace USERS offline ; Tablespace altered. SQL> alter tablespace USERS online ; Tablespace altered. SQL> ---------------------------------------- SQL> -- new SQL*Plus session starts here SQL> ---------------------------------------- SQL> SQL> execute ssth_ctrl.start_test( 'Run 3' ); PL/SQL procedure successfully completed. SQL> -- run the statement to be benchmarked SQL> SQL> update t3 set c1 = 0 ; 10000 rows updated. SQL> commit; Commit complete. SQL> execute ssth_ctrl.stop_test( 'Run 3' ); PL/SQL procedure successfully completed. SQL> ---------------------------------------- SQL> -- new SQL*Plus session starts here SQL> ---------------------------------------- SQL> SQL> alter session set recyclebin = OFF ; Session altered. SQL> drop table t1 ; Table dropped. SQL> drop table t2 ; Table dropped. SQL> drop table t3 ; Table dropped. =--------------------------------------------------------------------= = System Config =--------------------------------------------------------------------= V$SGA: NAME VALUE ---------------------------------------- ---------------- Fixed Size 1,288,820 Variable Size 159,384,972 Database Buffers 436,207,616 Redo Buffers 2,904,064 =--------------------------------------------------------------------= = Statistics that differ by more than 100 =--------------------------------------------------------------------= Attempt 1: METRIC_NAME Run 1 Run 2 Run 3 ------------------------------ ------------ ------------ ------------ buffer is pinned count 28,389 9,984 9,984 db block changes 74,061 20,145 20,144 db block gets 54,394 10,235 10,233 db block gets from cache 54,394 10,235 10,233 free buffer requested 318 141 140 physical read bytes 196,608 180,224 180,224 physical read total bytes 196,608 180,224 180,224 redo entries 36,895 10,010 10,010 redo size 7,645,992 2,328,832 2,328,704 rollback changes - undo record 8,433 0 0 session logical reads 54,538 10,331 10,329 session uga memory max 254,380 261,964 254,380 table scan rows gotten 28,557 10,000 10,000 undo change vector size 2,042,836 761,880 761,880 Attempt 2: METRIC_NAME Run 1 Run 2 Run 3 ------------------------------ ------------ ------------ ------------ buffer is pinned count 27,079 21,951 23,727 db block changes 68,798 48,202 55,333 db block gets 49,108 28,421 35,584 db block gets from cache 49,108 28,421 35,584 physical read bytes 204,800 212,992 212,992 physical read total bytes 204,800 212,992 212,992 redo entries 34,269 23,992 27,550 redo size 7,130,744 5,114,376 5,812,536 rollback changes - undo record 7,121 1,986 3,764 session logical reads 49,250 28,556 35,721 session uga memory max 254,380 254,380 261,964 table scan rows gotten 27,243 22,644 23,958 undo change vector size 1,943,140 1,552,936 1,688,048 =--------------------------------------------------------------------= = Latch Gets that differ by more than 100 =--------------------------------------------------------------------= Attempt 1: METRIC_NAME Run 1 Run 2 Run 3 ------------------------------ ------------ ------------ ------------ cache buffers chains 238,484 50,843 50,860 cache buffers lru chain 350 156 158 object queue header operation 614 254 255 redo allocation 36,915 10,018 10,018 row cache objects 25,563 245 257 session allocation 604 602 792 simulator hash latch 482 673 683 simulator lru latch 438 665 664 ------------ ------------ ------------ sum 303,450 63,456 63,687 Attempt 2: METRIC_NAME Run 1 Run 2 Run 3 ------------------------------ ------------ ------------ ------------ cache buffers chains 218,715 141,318 168,113 object queue header operation 584 471 515 redo allocation 34,289 24,006 27,587 row cache objects 21,627 6,222 11,556 simulator hash latch 484 1,470 3,004 simulator lru latch 436 1,433 2,961 ------------ ------------ ------------ sum 276,135 174,920 213,736