SQL> @spid SID SERIAL# PID SPID ---------- ---------- ---------- ------------ 141 85 22 2069 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. SQL> SQL> set timing on SQL> SQL> -- cache data SQL> select count(pad) 2 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:00.76 SQL> SQL> -- we start here SQL> -- default _rowsource_statistics_sampfreq SQL> select count(pad) 2 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:00.65 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 5ymq7bbd9da47, child number 1 ------------------------------------- select count(pad) from test Plan hash value: 1950795681 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.64 | 17856 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.64 | 17856 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:00.01 | 17856 | ------------------------------------------------------------------------------------- 14 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 count(pad) 2 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:00.59 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 5ymq7bbd9da47, child number 1 ------------------------------------- select count(pad) from test Plan hash value: 1950795681 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 17856 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 17856 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:00.01 | 17856 | ------------------------------------------------------------------------------------- 14 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 count(pad) 2 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:00.67 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 5ymq7bbd9da47, child number 1 ------------------------------------- select count(pad) from test Plan hash value: 1950795681 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.65 | 17856 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.65 | 17856 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:00.01 | 17856 | ------------------------------------------------------------------------------------- 14 rows selected. Elapsed: 00:00:00.02 SQL> SQL> alter session set "_rowsource_statistics_sampfreq"=16; Session altered. Elapsed: 00:00:00.00 SQL> SQL> select count(pad) 2 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:01.13 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 5ymq7bbd9da47, child number 1 ------------------------------------- select count(pad) from test Plan hash value: 1950795681 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.10 | 17856 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.10 | 17856 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:00.01 | 17856 | ------------------------------------------------------------------------------------- 14 rows selected. Elapsed: 00:00:00.03 SQL> SQL> alter session set "_rowsource_statistics_sampfreq"=8; Session altered. Elapsed: 00:00:00.00 SQL> SQL> select count(pad) 2 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:01.68 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 5ymq7bbd9da47, child number 1 ------------------------------------- select count(pad) from test Plan hash value: 1950795681 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.65 | 17856 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.65 | 17856 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:00.01 | 17856 | ------------------------------------------------------------------------------------- 14 rows selected. Elapsed: 00:00:00.02 SQL> SQL> alter session set "_rowsource_statistics_sampfreq"=4; Session altered. Elapsed: 00:00:00.00 SQL> SQL> select count(pad) 2 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:02.50 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 5ymq7bbd9da47, child number 1 ------------------------------------- select count(pad) from test Plan hash value: 1950795681 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:02.44 | 17856 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:02.44 | 17856 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:00.01 | 17856 | ------------------------------------------------------------------------------------- 14 rows selected. Elapsed: 00:00:00.03 SQL> SQL> alter session set "_rowsource_statistics_sampfreq"=2; Session altered. Elapsed: 00:00:00.00 SQL> SQL> select count(pad) 2 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:04.44 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 5ymq7bbd9da47, child number 1 ------------------------------------- select count(pad) from test Plan hash value: 1950795681 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:04.34 | 17856 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:04.34 | 17856 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:00.01 | 17856 | ------------------------------------------------------------------------------------- 14 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 count(pad) 2 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:08.19 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 5ymq7bbd9da47, child number 1 ------------------------------------- select count(pad) from test Plan hash value: 1950795681 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:08.00 | 17856 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:08.00 | 17856 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:00.01 | 17856 | ------------------------------------------------------------------------------------- 14 rows selected. Elapsed: 00:00:00.01 SQL> SQL> spool off