FirebirdSQL logo

SET PER_TABle_stats

Added in

Firebird 5.0

SET PER_TABle_stats [ON | OFF]

This command turns the display of per-table statistics on or off as desired.If no parameter is supplied to the command, it toggles the current state of the per-table statistics display.

When set to ON, isql shows per-table run-time statistics after query execution.It is set to OFF by default.This command is independent of the [isql-set-stats] command.The name PER_TABLE_STATS can be abbreviated up to PER_TAB.Tables in the output are sorted by their relation id’s.

Example (width reduced from original output):

-- check current value
SQL> SET;
...
Print per-table stats:   OFF
...

-- turn per-table stats on
SQL> SET PER_TABLE_STATS ON;
SQL> SELECT COUNT(*) FROM RDB$RELATIONS JOIN RDB$RELATION_FIELDS USING (RDB$RELATION_NAME);

                COUNT
=====================
534

Per table statistics:
--------------------+-------+-----+------+------+------+-------+-----+-------+
Table name          |Natural|Index|Insert|Update|Delete|Backout|Purge|Expunge|
--------------------+-------+-----+------+------+------+-------+-----+-------+
RDB$INDICES         |       |    3|      |      |      |       |     |       |
RDB$RELATION_FIELDS |       |  534|      |      |      |       |     |       |
RDB$RELATIONS       |     59|     |      |      |      |       |     |       |
RDB$SECURITY_CLASSES|       |    3|      |      |      |       |     |       |
--------------------+-------+-----+------+------+------+-------+-----+-------+

Note, some system tables are shown that were not listed in the query;the engine reads some additional metadata when preparing the query.

-- turn per-table stats off, using shortened name
SQL> SET PER_TAB OFF;
See also

[isql-set-stats]

SET PLAN

SET PLAN [ON | OFF]

This command determines whether isql displays the plan used to access the data for each statement executed.By default, ISQL does not display the plan.As with many other commands, not providing a parameter toggles the current state.

SQL> set plan on;

SQL> select emp_no, first_name, last_name
CON> from employee
CON> where emp_no = 107;

PLAN (EMPLOYEE INDEX (RDB$PRIMARY7))

 EMP_NO FIRST_NAME      LAST_NAME
======= =============== ====================
    107 Kevin           Cook

SQL> update employee
CON> set first_name = 'Norman'
CON> where last_name = 'Cook';

PLAN (EMPLOYEE INDEX (NAMEX))

SQL> select count(*) from employee;

PLAN (EMPLOYEE NATURAL)

       COUNT
============
          42

The execution plan is displayed before the output of a select statement.

Effects of Various Plan-Related Commands

Usage options

SET PLAN

simple plan + query execution

SET PLANONLY

simple plan, no query execution

SET PLAN + SET EXPLAIN

explained plan + query execution

SET PLAN + SET EXPLAIN + SET PLANONLY

explained plan, no query execution

SET EXPLAIN

explained plan + query execution

SET EXPLAIN + SET PLANONLY

explained plan, no query execution

SET PLANONLY

SET PLANONLY [ON | OFF]

This command prevents isql from actually executing the SQL statement, and instead only shows the plan that would be used to access the data.This command relies on the [isql-set-plan] command.If set plan off had been executed, this command would have no effect, so turning planonly on has the additional effect of executing set plan on implicitly.Executing set planonly off does not implicitly execute set plan off.

SQL> set planonly on;

SQL> select count(*) from employee;

PLAN (EMPLOYEE NATURAL)

As before, not supplying a parameter toggles the current setting.