RDB$PROFILER.START_SESSION starts a new profiler session, makes it the current session (of the given ATTACHMENT_ID) and returns its identifier.

If FLUSH_INTERVAL is different from NULL, auto-flush is set up in the same way as manually calling RDB$PROFILER.SET_FLUSH_INTERVAL.

If PLUGIN_NAME is NULL (the default), it uses the database configuration DefaultProfilerPlugin.

PLUGIN_OPTIONS are plugin specific options and currently should be NULL for the Default_Profiler plugin.

Return type: BIGINT NOT NULL.

RDB$PROFILER.CANCEL_SESSION cancels the current profiler session (of the given ATTACHMENT_ID).

All session data present in the profiler plugin is discarded and will not be flushed.

Data already flushed is not deleted automatically.

RDB$PROFILER.DISCARD removes all sessions (of the given ATTACHMENT_ID) from memory, without flushing them.

If there is an active session, it is cancelled.

RDB$PROFILER.FINISH_SESSION finishes the current profiler session (of the given ATTACHMENT_ID).

If FLUSH is TRUE, the snapshot tables are updated with data of the finished session (and old finished sessions not yet present in the snapshot), otherwise data remains only in memory for later update.

Calling RDB$PROFILER.FINISH_SESSION(TRUE) has the same semantics of calling RDB$PROFILER.FINISH_SESSION(FALSE) followed by RDB$PROFILER.FLUSH (using the same ATTACHMENT_ID).

RDB$PROFILER.FLUSH updates the snapshot tables with data from the profile sessions (of the given ATTACHMENT_ID) in memory.

After flushing, the data is stored in tables PLG$PROF_SESSIONS, PLG$PROF_STATEMENTS, PLG$PROF_RECORD_SOURCES, PLG$PROF_REQUESTS, PLG$PROF_PSQL_STATS and PLG$PROF_RECORD_SOURCE_STATS and may be read and analyzed by the user.

Data is updated using an autonomous transaction, so if the procedure is called in a snapshot transaction, data will not be directly readable in the same transaction.

Once flush happens, finished sessions are removed from memory.

RDB$PROFILER.PAUSE_SESSION pauses the current profiler session (of the given ATTACHMENT_ID), so the next executed statements statistics are not collected.

If FLUSH is TRUE, the snapshot tables are updated with data up to the current moment, otherwise data remains only in memory for later update.

Calling RDB$PROFILER.PAUSE_SESSION(TRUE) has the same semantics as calling RDB$PROFILER.PAUSE_SESSION(FALSE) followed by RDB$PROFILER.FLUSH (using the same ATTACHMENT_ID).

RDB$PROFILER.RESUME_SESSION resumes the current profiler session (of the given ATTACHMENT_ID), if it was paused, so the next executed statements statistics are collected again.

RDB$PROFILER.SET_FLUSH_INTERVAL turns periodic auto-flush on (when FLUSH_INTERVAL is greater than 0) or off (when FLUSH_INTERVAL is equal to 0).

FLUSH_INTERVAL is interpreted as number of seconds.

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;