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>
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>
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>
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)
3+4 50 55
5+6 51 60
7+8 56 59
9+10 56 50
