Eventually it's all about data

Archive for May, 2014

Amending statistics list for autotrace

Posted by sqlmdx on May 24, 2014

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.
  2. 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.

  3. Use queries on v$sesstat, v$statname in another session and find difference between executions.
  4. 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 or autonomous transactions.

  5. Use widely known package runstats_pkg by Tom Kyte.
  6. 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.
Read the rest of this entry »

Posted in Oracle | Tagged: | Leave a Comment »

Recursive calls and statistics

Posted by sqlmdx on May 11, 2014

There are many cases when executing some statement (either SQL or PL/SQL) may invoke execution of another SQL or PL/SQL statement.
However it’s not at all obvious how execution statistics for cursors are aggregated.
Read the rest of this entry »

Posted in Oracle | Tagged: , | Leave a Comment »