Amending statistics list for autotrace

I wonder why Oracle has not added possibility to change the list of statistics for autotrace but unfortunately that’s a fact.

You cannot change the default format of the statistics report.

However there are at least three different ways to view the extended list of statistics for given session

1. Use dbms_session.set_identifier and dbms_monitor.client_id_stat_enable/dbms_monitor.client_id_stat_disable.

First drawback is that enabling and disabling of stats collecting must be executed in another session and second drawback is that it collects only 27 statistics.
Advantage is the ease of monitoring several sessions with the same client_id.

2. Use queries on v$sesstat, v$statname in another session and find difference between executions.

The disadvantage is the need to run service queries in second session to avoid side affects and positive aspect is that all existed statistics can be reported.
It’s not necessary to create another session manually though. It’s possible to implicitly execute some statements in another session, for example by using dbms_job/dbms_scheduler.

3. Use widely known package runstats_pkg by Tom Kyte.

The drawbacks are the same as for previous approach: either you have to run runstats in another session or results are not absolutely precise.

Even though documentation says that we cannot change the default format of the statistics report, it’s in fact possible to amend it.
The only restriction is that number of statistics cannot exceed 11.

In order to do that we can open sqlplus.exe with HEX editor and replace

WHERE NAME IN ('recursive calls','db block gets','consistent gets','physical reads','redo size','bytes sent via SQL*Net to client','bytes received via SQL*Net from client','SQL*Net roundtrips to/from client','sorts (memory)','sorts (disk)') ORDER BY

with

WHERE NAME IN (select statname from sqlplus_statname                                                                                                                                                                                           ) ORDER BY

Where sqlplus_statname is

create table sqlplus$statname as
select column_value statname
  from table(sys.odcivarchar2list('recursive calls',
                                  'db block gets',
                                  'consistent gets',
                                  'physical reads',
                                  'redo size',
                                  'bytes sent via SQL*Net to client',
                                  'bytes received via SQL*Net from client',
                                  'SQL*Net roundtrips to/from client',
                                  'sorts (memory)',
                                  'sorts (disk)',
                                  'rows processed'));
                                  
create public synonym sqlplus_statname for sqlplus$statname;

Let’s change for instance ‘redo size’ with ‘table scan rows gotten’.

update sqlplus$statname set statname = 'table scan rows gotten' where statname = 'redo size';
commit;

The output is following:

SQL> create table t as select rownum id from dual connect by level <= 1234;

Table created.

SQL> select max(id) m from t;

         M
----------
      1234

SQL> set autot on stat
SQL> select max(id) m from t;

         M
----------
      1234


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
       1234  table scan rows gotten
        416  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Binary file was edited using free tool McAfee FileInsight.
You can download amended sqlplus (rename .doc to .exe).

Even though it’s possible to amend statistics list for autotrace one drawback makes it less functional than other ways to analyze execution statistics. Unfortunately autotrace doesn’t display anything if statement fails however it might be quite useful to check how much redo was generated or how many blocks were got for instance.