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
- Use dbms_session.set_identifier and dbms_monitor.client_id_stat_enable/dbms_monitor.client_id_stat_disable.
- Use queries on v$sesstat, v$statname in another session and find difference between executions.
- Use widely known package runstats_pkg by Tom Kyte.
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.
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.
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.