SQL> @spid SID SERIAL# PID SPID ---------- ---------- ---------- ------------------------ 22 857 34 2946 SQL> SQL> set linesize 200 SQL> set pagesize 999 SQL> col PLAN_TABLE_OUTPUT for a100 SQL> SQL> --alter session set sql_trace; SQL> SQL> alter session set "_serial_direct_read"=never; Session altered. SQL> alter session set events 'sql_trace[sql:5ymq7bbd9da47]'; 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.78 SQL> SQL> -- we start here SQL> -- default _rowsource_statistics_sampfreq 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 0 ------------------------------------- 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 | 17874 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.65 | 17874 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:03.13 | 17874 | ------------------------------------------------------------------------------------- 14 rows selected. Elapsed: 00:00:00.25 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.57 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 5ymq7bbd9da47, child number 0 ------------------------------------- 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 | 17874 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 17874 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:00.01 | 17874 | ------------------------------------------------------------------------------------- 14 rows selected. Elapsed: 00:00:00.02 SQL> SQL> SQL> alter session set "_rowsource_statistics_sampfreq"=128; Session altered. Elapsed: 00:00:00.02 SQL> SQL> select count(pad) 2 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:00.63 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 5ymq7bbd9da47, child number 0 ------------------------------------- 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.63 | 17874 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.63 | 17874 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:03.15 | 17874 | ------------------------------------------------------------------------------------- 14 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 count(pad) 2 from test; COUNT(PAD) ---------- 10000000 Elapsed: 00:00:01.03 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 5ymq7bbd9da47, child number 0 ------------------------------------- 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.03 | 17874 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.03 | 17874 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:03.25 | 17874 | ------------------------------------------------------------------------------------- 14 rows selected. Elapsed: 00:00:00.02 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.32 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 5ymq7bbd9da47, child number 0 ------------------------------------- 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.32 | 17874 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.32 | 17874 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:03.10 | 17874 | ------------------------------------------------------------------------------------- 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.02 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 5ymq7bbd9da47, child number 0 ------------------------------------- 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.02 | 17874 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:02.02 | 17874 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:03.12 | 17874 | ------------------------------------------------------------------------------------- 14 rows selected. Elapsed: 00:00:00.02 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:03.41 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 5ymq7bbd9da47, child number 0 ------------------------------------- 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:03.41 | 17874 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:03.41 | 17874 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:03.14 | 17874 | ------------------------------------------------------------------------------------- 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:06.28 SQL> SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 5ymq7bbd9da47, child number 0 ------------------------------------- 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:06.29 | 17874 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:06.29 | 17874 | | 2 | TABLE ACCESS FULL| TEST | 1 | 10M| 10M|00:00:03.36 | 17874 | ------------------------------------------------------------------------------------- 14 rows selected. Elapsed: 00:00:00.01 SQL> SQL> spool off