SQL> SQL> set linesize 200 SQL> col PLAN_TABLE_OUTPUT for a100 SQL> SQL> alter session set events 'sql_trace[sql:9pzmsyh5t14bb]'; Session altered. SQL> SQL> set timing on SQL> SQL> -- cache data SQL> select --+ index(test) 2 count(pad) 3 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:02.76 SQL> SQL> -- we start here SQL> -- default _rowsource_statistics_sampfreq SQL> select --+ index(test) 2 count(pad) 3 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:02.79 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 2 ------------------------------------- select --+ index(test) count(pad) from test Plan hash value: 4182611088 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:02.79 | 41401 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:02.79 | 41401 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:10.71 | 41401 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:03.78 | 23555 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.03 SQL> SQL> alter session set "_rowsource_statistics_sampfreq"=0; Session altered. Elapsed: 00:00:00.00 SQL> SQL> select --+ index(test) 2 count(pad) 3 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:02.63 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 2 ------------------------------------- select --+ index(test) count(pad) from test Plan hash value: 4182611088 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 41401 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 41401 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:00.01 | 41401 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:00.01 | 23555 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.04 SQL> SQL> SQL> alter session set "_rowsource_statistics_sampfreq"=128; Session altered. Elapsed: 00:00:00.00 SQL> SQL> select --+ index(test) 2 count(pad) 3 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:02.70 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 2 ------------------------------------- select --+ index(test) count(pad) from test Plan hash value: 4182611088 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:02.70 | 41401 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:02.70 | 41401 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:10.28 | 41401 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:03.63 | 23555 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.03 SQL> SQL> alter session set "_rowsource_statistics_sampfreq"=16; Session altered. Elapsed: 00:00:00.00 SQL> SQL> select --+ index(test) 2 count(pad) 3 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:03.33 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 2 ------------------------------------- select --+ index(test) count(pad) from test Plan hash value: 4182611088 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:03.33 | 41401 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:03.33 | 41401 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:10.36 | 41401 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:03.71 | 23555 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.05 SQL> SQL> alter session set "_rowsource_statistics_sampfreq"=8; Session altered. Elapsed: 00:00:00.00 SQL> SQL> select --+ index(test) 2 count(pad) 3 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:04.32 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 2 ------------------------------------- select --+ index(test) count(pad) from test Plan hash value: 4182611088 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:04.31 | 41401 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:04.31 | 41401 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:10.78 | 41401 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:03.92 | 23555 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.05 SQL> SQL> alter session set "_rowsource_statistics_sampfreq"=4; Session altered. Elapsed: 00:00:00.00 SQL> SQL> select --+ index(test) 2 count(pad) 3 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:05.28 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 2 ------------------------------------- select --+ index(test) count(pad) from test Plan hash value: 4182611088 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:05.28 | 41401 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:05.28 | 41401 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:10.24 | 41401 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:03.68 | 23555 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.04 SQL> SQL> alter session set "_rowsource_statistics_sampfreq"=2; Session altered. Elapsed: 00:00:00.00 SQL> SQL> select --+ index(test) 2 count(pad) 3 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:07.94 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 2 ------------------------------------- select --+ index(test) count(pad) from test Plan hash value: 4182611088 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:07.94 | 41401 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:07.94 | 41401 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:10.33 | 41401 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:03.73 | 23555 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.02 SQL> SQL> alter session set "_rowsource_statistics_sampfreq"=1; Session altered. Elapsed: 00:00:00.00 SQL> SQL> select --+ index(test) 2 count(pad) 3 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:13.07 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 2 ------------------------------------- select --+ index(test) count(pad) from test Plan hash value: 4182611088 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:13.08 | 41401 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:13.08 | 41401 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:10.22 | 41401 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:03.67 | 23555 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.01 SQL> SQL> spool off