Does Having TIMED_STATISTICS on Affect Performance?

I was reading through an Oracle publication called Oracle 9i Database Performance Planning recently and saw the following:

In order to get meaningful database statistics, the TIMED_STATISTICS parameter must be enabled for the database instance. The performance impact of having TIMED_STATISTICS enabled is minimal compared to instance performance. The performance improvements and debugging value of a complete set of statistics make this parameter crucial to effective performance analysis.

I decided to check this out on my son's PC, which has 4 gigabytes of RAM and a 4 core CPU. The PC has Windows Vista and Oracle 11.2 installed.

I ran the first test below with TIMED_STATISTICS turned off. It recorded the start time, created a table, added several rows to it, did a full table scan, dropped the table, recorded the end time then checked what statistics were available:

SQL> conn / as sysdba
Connected.
SQL> alter session set statistics_level = basic
  2  /

Session altered.

SQL> alter session set timed_statistics = false
  2  /

Session altered.

SQL> select to_char(sysdate,'hh24:mi:ss') start_time
  2  from dual
  3  /

START_TIME
----------
18:30:03

SQL> create table tab1 tablespace users
  2  as select * from dba_tables
  3  /

Table created.

SQL> begin
  2   for a in 1..9 loop
  3    insert into tab1 select * from tab1;
  4   end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select count(*) from tab1 where owner = 'SYS'
  2  /

  COUNT(*)
----------
    489472

SQL> drop table tab1
  2  /

Table dropped.

SQL> select to_char(sysdate,'hh24:mi:ss') end_time
  2  from dual
  3  /

END_TIME
--------
18:30:51

SQL> select a.value/100
  2  from v$mystat a, v$sysstat b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'CPU used by this session'
  5  /

A.VALUE/100
-----------
        .06

SQL> select * from
  2  (select event, time_waited/100 seconds
  3   from v$session_event
  4   where wait_class != 'Idle'
  5   and sid = (select distinct sid from v$mystat)
  6   order by 2 desc)
  7  where rownum < 6
  8  /

EVENT                                          SECONDS
--------------------------------------------- --------
db file sequential read                              0
Disk file operations I/O                             0
SQL*Net message to client                            0

SQL>

It took 48 seconds to run and, as suggested in the Oracle publication, produced no statistics. I repeated the test with TIMED_STATISTICS turned on:

SQL> conn / as sysdba
Connected.
SQL> alter session set statistics_level = typical
  2  /

Session altered.

SQL> alter session set timed_statistics = true
  2  /

Session altered.

SQL> select to_char(sysdate,'hh24:mi:ss') start_time
  2  from dual
  3  /

START_TIME
----------
18:33:00

SQL> create table tab1 tablespace users
  2  as select * from dba_tables
  3  /

Table created.

SQL> begin
  2   for a in 1..9 loop
  3    insert into tab1 select * from tab1;
  4   end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select count(*) from tab1 where owner = 'SYS'
  2  /

  COUNT(*)
----------
    489472

SQL> drop table tab1
  2  /

Table dropped.

SQL> select to_char(sysdate,'hh24:mi:ss') end_time
  2  from dual
  3  /

END_TIME
--------
18:33:52

SQL> select a.value/100
  2  from v$mystat a, v$sysstat b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'CPU used by this session'
  5  /

A.VALUE/100
-----------
       7.77

SQL> select * from
  2  (select event, time_waited/100 seconds
  3   from v$session_event
  4   where wait_class != 'Idle'
  5   and sid = (select distinct sid from v$mystat)
  6   order by 2 desc)
  7  where rownum < 6
  8  /

EVENT                                          SECONDS
--------------------------------------------- --------
db file scattered read                              19
log file switch (checkpoint incomplete)             10
enq: RO - fast object reuse                          8
log buffer space                                     5
db file sequential read                              2

SQL>

It took 52 seconds to run, which was slightly longer than the first test, but, at the end, I could see that proper statistics had been recorded in views such as V$MYSTAT and V$SESSION_EVENT.

I repeated the two tests four more times and put the results in the table below:

Tests  TIMED_STATISTICS Off   TIMED_STATISTICS On

            (seconds)              (seconds)

 1+2           48                     52
 3+4           50                     55
 5+6           51                     60
 7+8           56                     59
 9+10          56                     50