Using DRITA functions v16

Suggest edits

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();

Output

       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);

Output

                                  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);

Output

                                     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);

Output

                                     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);

Output

                                    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);

Output

            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();

Output

        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();

Output

     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();

Output

 get_snaps
-----------
(0 rows)

← Prev

Taking a snapshot

Performance tuning recommendations

Using DRITA functions v16

Retrieving a list of current snapshots (get_snaps)

Retrieving system wait information (sys_rpt)

Parameters

Results

Retrieving session wait information (sess_rpt)

Parameters

Results

Retrieving ession ID information for a specified backend (sessid_rpt)

Parameters

Results

Retrieving session wait information for a specified backend (sesshist_rpt)

Parameters

Note

Results

Purging a range of snapshots from the snapshot tables (purgesnap)

Parameters

Deleting records from the snapshot table (truncsnap)

← Prev

Next →