Using DRITA functions v16
You can use DRITA functions to gather wait information and manage snapshots. DRITA functions are fully supported by EDB Postgres Advanced Server whether your installation is made compatible with Oracle databases or is in PostgreSQL-compatible mode.
Retrieving a list of current snapshots (get_snaps)
The get_snaps()
function returns a list of the current snapshots. The signature is:
get_snaps()
This example uses the get_snaps()
function to display a list of snapshots:
SELECT * FROM get_snaps();
get_snaps ------------------------------ 1 25-JUL-18 09:49:04.224597 2 25-JUL-18 09:49:09.310395 3 25-JUL-18 09:49:14.378728 4 25-JUL-18 09:49:19.448875 5 25-JUL-18 09:49:24.52103 6 25-JUL-18 09:49:29.586889 7 25-JUL-18 09:49:34.65529 8 25-JUL-18 09:49:39.723095 9 25-JUL-18 09:49:44.788392 10 25-JUL-18 09:49:49.855821 11 25-JUL-18 09:49:54.919954 12 25-JUL-18 09:49:59.987707 (12 rows)
The first column in the result list displays the snapshot identifier. The second column displays the date and time that the snapshot was captured.
Retrieving system wait information (sys_rpt)
The sys_rpt()
function returns system wait information. The signature is:
sys_rpt(<beginning_id>, <ending_id>, <top_n>)
Parameters
beginning_id
An integer value that represents the beginning session identifier.
ending_id
An integer value that represents the ending session identifier.
top_n
The number of rows to return.
This example shows a call to the sys_rpt()
function:
SELECT * FROM sys_rpt(9, 10, 10);
sys_rpt ----------------------------------------------------------------------------- WAIT NAME COUNT WAIT TIME % WAIT --------------------------------------------------------------------------- wal flush 8359 1.357593 30.62 wal write 8358 1.349153 30.43 wal file sync 8358 1.286437 29.02 query plan 33439 0.439324 9.91 db file extend 54 0.000585 0.01 db file read 31 0.000307 0.01 other lwlock acquire 0 0.000000 0.00 ProcArrayLock 0 0.000000 0.00 CLogControlLock 0 0.000000 0.00 (11 rows)
Results
The information displayed in the result set includes:
Column name | Description |
---|---|
WAIT NAME | The name of the wait |
COUNT | The number of times that the wait event occurred |
WAIT TIME | The time of the wait event in seconds |
% WAIT | The percentage of the total wait time used by this wait for this session |
Retrieving session wait information (sess_rpt)
The sess_rpt()
function returns session wait information. The signature is:
sess_rpt(<beginning_id>, <ending_id>, <top_n>)
Parameters
beginning_id
An integer value that represents the beginning session identifier.
ending_id
An integer value that represents the ending session identifier.
top_n
The number of rows to return.
This example shows a call to the sess_rpt()
function:
SELECT * FROM sess_rpt(8, 9, 10);
sess_rpt ------------------------------------------------------------------------------------- ID USER WAIT NAME COUNT TIME % WAIT SES % WAIT ALL ------------------------------------------------------------------------------------- 3501 enterprise wal flush 8354 1.354958 30.61 30.61 3501 enterprise wal write 8354 1.348192 30.46 30.46 3501 enterprise wal file sync 8354 1.285607 29.04 29.04 3501 enterprise query plan 33413 0.436901 9.87 9.87 3501 enterprise db file extend 54 0.000578 0.01 0.01 3501 enterprise db file read 56 0.000541 0.01 0.01 3501 enterprise ProcArrayLock 0 0.000000 0.00 0.00 3501 enterprise CLogControlLock 0 0.000000 0.00 0.00 (10 rows)
Results
The information displayed in the result set includes:
Column name | Description |
---|---|
ID | The processID of the session |
USER | The name of the user incurring the wait |
WAIT NAME | The name of the wait event |
COUNT | The number of times that the wait event occurred |
TIME | The length of the wait event in seconds |
% WAIT SES | The percentage of the total wait time used by this wait for this session |
% WAIT ALL | The percentage of the total wait time used by this wait for all sessions |
Retrieving ession ID information for a specified backend (sessid_rpt)
The sessid_rpt()
function returns session ID information for a specified backend. The signature is:
sessid_rpt(<beginning_id>, <ending_id>, <backend_id>)
Parameters
beginning_id
An integer value that represents the beginning session identifier.
ending_id
An integer value that represents the ending session identifier.
backend_id
An integer value that represents the backend identifier.
This example shows a call to sessid_rpt()
:
SELECT * FROM sessid_rpt(8, 9, 3501);
sessid_rpt ------------------------------------------------------------------------------------- ID USER WAIT NAME COUNT TIME % WAIT SES % WAIT ALL ------------------------------------------------------------------------------------- 3501 enterprise CLogControlLock 0 0.000000 0.00 0.00 3501 enterprise ProcArrayLock 0 0.000000 0.00 0.00 3501 enterprise db file read 56 0.000541 0.01 0.01 3501 enterprise db file extend 54 0.000578 0.01 0.01 3501 enterprise query plan 33413 0.436901 9.87 9.87 3501 enterprise wal file sync 8354 1.285607 29.04 29.04 3501 enterprise wal write 8354 1.348192 30.46 30.46 3501 enterprise wal flush 8354 1.354958 30.61 30.61 (10 rows)
Results
The information displayed in the result set includes:
Column name | Description |
---|---|
ID | The process ID of the wait |
USER | The name of the user that owns the session |
WAIT NAME | The name of the wait event |
COUNT | The number of times that the wait event occurred |
TIME | The length of the wait in seconds |
% WAIT SES | The percentage of the total wait time used by this wait for this session |
% WAIT ALL | The percentage of the total wait time used by this wait for all sessions |
Retrieving session wait information for a specified backend (sesshist_rpt)
The sesshist_rpt()
function returns session wait information for a specified backend. The signature is:
sesshist_rpt(<snapshot_id>, <session_id>)
Parameters
snapshot_id
An integer value that identifies the snapshot.
session_id
An integer value that represents the session.
This example shows a call to the sesshist_rpt()
function:
Note
The example was shortened. Over 1300 rows are actually generated.
SELECT * FROM sesshist_rpt (9, 3501);
sesshist_rpt ----------------------------------------------------------------------------- ---------- ID USER SEQ WAIT NAME ELAPSED File Name # of Blk Sum of Blks ----------------------------------------------------------------------------- --------- 3501 enterprise 1 query plan 13 0 N/A 0 0 3501 enterprise 1 query plan 13 0 edb_password_history 0 0 3501 enterprise 1 query plan 13 0 edb_password_history 0 0 3501 enterprise 1 query plan 13 0 edb_password_history 0 0 3501 enterprise 1 query plan 13 0 edb_profile 0 0 3501 enterprise 1 query plan 13 0 edb_profile_name_ind 0 0 3501 enterprise 1 query plan 13 0 edb_profile_oid_inde 0 0 3501 enterprise 1 query plan 13 0 edb_profile_password 0 0 3501 enterprise 1 query plan 13 0 edb_resource_group 0 0 3501 enterprise 1 query plan 13 0 edb_resource_group_n 0 0 3501 enterprise 1 query plan 13 0 edb_resource_group_o 0 0 3501 enterprise 1 query plan 13 0 pg_attribute 0 0 3501 enterprise 1 query plan 13 0 pg_attribute_relid_a 0 0 3501 enterprise 1 query plan 13 0 pg_attribute_relid_a 0 0 3501 enterprise 1 query plan 13 0 pg_auth_members 0 0 3501 enterprise 1 query plan 13 0 pg_auth_members_memb 0 0 3501 enterprise 1 query plan 13 0 pg_auth_members_role 0 0 . . . 3501 enterprise 2 wal flush 149 0 N/A 0 0 3501 enterprise 2 wal flush 149 0 edb_password_history 0 0 3501 enterprise 2 wal flush 149 0 edb_password_history 0 0 3501 enterprise 2 wal flush 149 0 edb_password_history 0 0 3501 enterprise 2 wal flush 149 0 edb_profile 0 0 3501 enterprise 2 wal flush 149 0 edb_profile_name_ind 0 0 3501 enterprise 2 wal flush 149 0 edb_profile_oid_inde 0 0 3501 enterprise 2 wal flush 149 0 edb_profile_password 0 0 3501 enterprise 2 wal flush 149 0 edb_resource_group 0 0 3501 enterprise 2 wal flush 149 0 edb_resource_group_n 0 0 3501 enterprise 2 wal flush 149 0 edb_resource_group_o 0 0 3501 enterprise 2 wal flush 149 0 pg_attribute 0 0 3501 enterprise 2 wal flush 149 0 pg_attribute_relid_a 0 0 3501 enterprise 2 wal flush 149 0 pg_attribute_relid_a 0 0 3501 enterprise 2 wal flush 149 0 pg_auth_members 0 0 3501 enterprise 2 wal flush 149 0 pg_auth_members_memb 0 0 3501 enterprise 2 wal flush 149 0 pg_auth_members_role 0 0 . . . 3501 enterprise 3 wal write 148 0 N/A 0 0 3501 enterprise 3 wal write 148 0 edb_password_history 0 0 3501 enterprise 3 wal write 148 0 edb_password_history 0 0 3501 enterprise 3 wal write 148 0 edb_password_history 0 0 3501 enterprise 3 wal write 148 0 edb_profile 0 0 3501 enterprise 3 wal write 148 0 edb_profile_name_ind 0 0 3501 enterprise 3 wal write 148 0 edb_profile_oid_inde 0 0 3501 enterprise 3 wal write 148 0 edb_profile_password 0 0 3501 enterprise 3 wal write 148 0 edb_resource_group 0 0 3501 enterprise 3 wal write 148 0 edb_resource_group_n 0 0 3501 enterprise 3 wal write 148 0 edb_resource_group_o 0 0 3501 enterprise 3 wal write 148 0 pg_attribute 0 0 3501 enterprise 3 wal write 148 0 pg_attribute_relid_a 0 0 3501 enterprise 3 wal write 148 0 pg_attribute_relid_a 0 0 3501 enterprise 3 wal write 148 0 pg_auth_members 0 0 3501 enterprise 3 wal write 148 0 pg_auth_members_memb 0 0 3501 enterprise 3 wal write 148 0 pg_auth_members_role 0 0 . . . 3501 enterprise 24 wal write 130 0 pg_toast_1255 0 0 3501 enterprise 24 wal write 130 0 pg_toast_1255_index 0 0 3501 enterprise 24 wal write 130 0 pg_toast_2396 0 0 3501 enterprise 24 wal write 130 0 pg_toast_2396_index 0 0 3501 enterprise 24 wal write 130 0 pg_toast_2964 0 0 3501 enterprise 24 wal write 130 0 pg_toast_2964_index 0 0 3501 enterprise 24 wal write 130 0 pg_toast_3592 0 0 3501 enterprise 24 wal write 130 0 pg_toast_3592_index 0 0 3501 enterprise 24 wal write 130 0 pg_type 0 0 3501 enterprise 24 wal write 130 0 pg_type_oid_index 0 0 3501 enterprise 24 wal write 130 0 pg_type_typname_nsp_ 0 0 (1304 rows)
Results
The information displayed in the result set includes:
Column name | Description |
---|---|
ID | The system-assigned identifier of the wait |
USER | The name of the user that incurred the wait |
SEQ | The sequence number of the wait event |
WAIT NAME | The name of the wait event |
ELAPSED | The length of the wait event in microseconds |
File | The relfilenode number of the file |
Name | If available, the name of the file name related to the wait event |
# of Blk | The block number read or written for a specific instance of the event |
Sum of Blks | The number of blocks read |
Purging a range of snapshots from the snapshot tables (purgesnap)
The purgesnap()
function purges a range of snapshots from the snapshot tables. The signature is:
purgesnap(<beginning_id>, <ending_id>)
Parameters
beginning_id
An integer value that represents the beginning session identifier.
ending_id
An integer value that represents the ending session identifier.
purgesnap()
removes all snapshots between beginning_id
and ending_id
, inclusive:
SELECT * FROM purgesnap(6, 9);
purgesnap ------------------------------------ Snapshots in range 6 to 9 deleted. (1 row)
A call to the get_snaps()
function after executing the example shows that snapshots 6
through 9
were purged from the snapshot tables:
SELECT * FROM get_snaps();
get_snaps ------------------------------ 1 25-JUL-18 09:49:04.224597 2 25-JUL-18 09:49:09.310395 3 25-JUL-18 09:49:14.378728 4 25-JUL-18 09:49:19.448875 5 25-JUL-18 09:49:24.52103 10 25-JUL-18 09:49:49.855821 11 25-JUL-18 09:49:54.919954 12 25-JUL-18 09:49:59.987707 (8 rows)
Deleting records from the snapshot table (truncsnap)
Use the truncsnap() function to delete all records from the snapshot table. The signature is:
truncsnap()
For example:
SELECT * FROM truncsnap();
truncsnap ---------------------- Snapshots truncated. (1 row)
A call to the get_snaps()
function after calling the truncsnap()
function shows that all records were removed from the snapshot tables:
SELECT * FROM get_snaps();
get_snaps ----------- (0 rows)
- On this page
- Retrieving a list of current snapshots (get_snaps)
- Retrieving system wait information (sys_rpt)
- Retrieving session wait information (sess_rpt)
- Retrieving ession ID information for a specified backend (sessid_rpt)
- Retrieving session wait information for a specified backend (sesshist_rpt)
- Purging a range of snapshots from the snapshot tables (purgesnap)
- Deleting records from the snapshot table (truncsnap)