FirebirdSQL logo

Example

Below is a sample profile session and queries for data analysis.

  1. Preparation — create table and routines that will be analyzed

    create table tab (
        id integer not null,
        val integer not null
    );
    
    set term !;
    
    create or alter function mult(p1 integer, p2 integer) returns integer
    as
    begin
        return p1 * p2;
    end!
    
    create or alter procedure ins
    as
        declare n integer = 1;
    begin
        while (n <= 1000)
        do
        begin
            if (mod(n, 2) = 1) then
                insert into tab values (:n, mult(:n, 2));
            n = n + 1;
        end
    end!
    
    set term ;!
  2. Start profiling

    select rdb$profiler.start_session('Profile Session 1') from rdb$database;
    
    set term !;
    
    execute block
    as
    begin
        execute procedure ins;
        delete from tab;
    end!
    
    set term ;!
    
    execute procedure rdb$profiler.finish_session(true);
    
    execute procedure ins;
    
    select rdb$profiler.start_session('Profile Session 2') from rdb$database;
    
    select mod(id, 5),
           sum(val)
      from tab
      where id <= 50
      group by mod(id, 5)
      order by sum(val);
    
    execute procedure rdb$profiler.finish_session(true);
  3. Data analysis

    set transaction read committed;
    
    select * from plg$prof_sessions;
    
    select * from plg$prof_psql_stats_view;
    
    select * from plg$prof_record_source_stats_view;
    
    select preq.*
      from plg$prof_requests preq
      join plg$prof_sessions pses
        on pses.profile_id = preq.profile_id and
           pses.description = 'Profile Session 1';
    
    select pstat.*
      from plg$prof_psql_stats pstat
      join plg$prof_sessions pses
        on pses.profile_id = pstat.profile_id and
           pses.description = 'Profile Session 1'
      order by pstat.profile_id,
               pstat.request_id,
               pstat.line_num,
               pstat.column_num;
    
    select pstat.*
      from plg$prof_record_source_stats pstat
      join plg$prof_sessions pses
        on pses.profile_id = pstat.profile_id and
           pses.description = 'Profile Session 2'
      order by pstat.profile_id,
               pstat.request_id,
               pstat.cursor_id,
               pstat.record_source_id;

RDB$TIME_ZONE_UTIL

A package of time zone utility functions and procedures.

Function DATABASE_VERSION

RDB$TIME_ZONE_UTIL.DATABASE_VERSION returns the version of the time zone database.

Return type: VARCHAR(10) CHARACTER SET ASCII.

Example
select rdb$time_zone_util.database_version()
from rdb$database;

Returns:

DATABASE_VERSION
================
2023c

Procedure TRANSITIONS

RDB$TIME_ZONE_UTIL.TRANSITIONS returns the set of rules between the start and end timestamps for a named time zone.

Input parameters
  • RDB$TIME_ZONE_NAME type CHAR(63)

  • RDB$FROM_TIMESTAMP type TIMESTAMP WITH TIME ZONE

  • RDB$TO_TIMESTAMP type TIMESTAMP WITH TIME ZONE

Output parameters:

RDB$START_TIMESTAMP

type TIMESTAMP WITH TIME ZONE — The start timestamp of the transition

RDB$END_TIMESTAMP

type TIMESTAMP WITH TIME ZONE — The end timestamp of the transition

RDB$ZONE_OFFSET

type SMALLINT — The zone’s offset, in minutes

RDB$DST_OFFSET

type SMALLINT — The zone’s DST offset, in minutes

RDB$EFFECTIVE_OFFSET

type SMALLINT — Effective offset (ZONE_OFFSET + DST_OFFSET)

Example
select *
  from rdb$time_zone_util.transitions(
    'America/Sao_Paulo',
    timestamp '2017-01-01',
    timestamp '2019-01-01');

Returns (RDB$ prefix left off for brevity):

             START_TIMESTAMP                END_TIMESTAMP ZONE_OFFSET DST_OFFSET EFFECTIVE_OFFSET
============================ ============================ =========== ========== ================
2016-10-16 03:00:00.0000 GMT 2017-02-19 01:59:59.9999 GMT       -180        60             -120
2017-02-19 02:00:00.0000 GMT 2017-10-15 02:59:59.9999 GMT       -180         0             -180
2017-10-15 03:00:00.0000 GMT 2018-02-18 01:59:59.9999 GMT       -180        60             -120
2018-02-18 02:00:00.0000 GMT 2018-10-21 02:59:59.9999 GMT       -180         0             -180
2018-10-21 03:00:00.0000 GMT 2019-02-17 01:59:59.9999 GMT       -180        60             -120