SQL> SQL> set pagesize 999 SQL> set linesize 200 SQL> col PLAN_TABLE_OUTPUT for a100 SQL> SQL> alter session set sql_trace=true; Session altered. Elapsed: 00:00:00.00 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:03.66 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:03.48 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 0 ------------------------------------- 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.40 | 41405 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:03.40 | 41405 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:20.00 | 41405 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:00.04 | 23560 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.02 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:03.28 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 0 ------------------------------------- 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 | 41405 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 41405 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:00.01 | 41405 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:00.01 | 23560 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.01 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:03.53 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 0 ------------------------------------- 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.45 | 41405 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:03.45 | 41405 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:20.00 | 41405 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:00.04 | 23560 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.00 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:05.24 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 0 ------------------------------------- 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.12 | 41405 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:05.12 | 41405 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:20.00 | 41405 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:00.32 | 23560 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.01 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:07.18 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 0 ------------------------------------- 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.02 | 41405 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:07.02 | 41405 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:20.00 | 41405 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:00.63 | 23560 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.00 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:11.25 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 0 ------------------------------------- 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:10.99 | 41405 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:10.99 | 41405 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:20.00 | 41405 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:01.29 | 23560 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.01 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:19.00 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 0 ------------------------------------- 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:18.57 | 41405 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:18.57 | 41405 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:20.00 | 41405 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:02.57 | 23560 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.01 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:34.04 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9pzmsyh5t14bb, child number 0 ------------------------------------- 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:33.27 | 41405 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:33.27 | 41405 | | 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 10M| 10M|00:00:30.00 | 41405 | | 3 | INDEX FULL SCAN | IDX | 1 | 10M| 10M|00:00:05.06 | 23560 | ----------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:00.01 SQL> SQL> spool off