Postgres Plus Advanced Server Oracle Compatibility Developer's Guide
11.9 Catalog Views
The following DRITA catalog views provide access to performance information relating to system waits.
11.9.1 edb$system_waits
The edb$system_waits view summarizes the number of waits and the total wait time per session for each wait named. It also displays the average and max wait times. edb$system_waits summarizes the following information:
Column | Type | Modifiers | Definition ------------+---------------+-----------+------------------ edb_id | numeric | |identifier dbname | text | |database name wait_name | text | |name of the event wait_count | numeric | |number of times the event occurs avg_wait | numeric(50,6) | |average wait time in microseconds max_wait | numeric | |maximum wait time in microseconds total_wait | numeric | |total wait time in microseconds wait_name | text | |name of the event
The following example shows the result of a SELECT statement on the edb$system_waits view:
select * from sys.edb$system_waits; edb_id | dbname |wait_name | wait_count |avg_wait | max_wait | totalwait --------+--------+-----------+------------+---------+----------+---------- 1 | edb |db fileread| 301 |0.011516 | 0.629986 | 2.742500 1 | edb |wal flush | 26 |0.010364 | 0.085380 | 0.269452 1 | edb |wal write | 26 |0.010355 | 0.085371 | 0.269232 1 | edb |query plan | 277 |0.001367 | 0.049425 | 0.192442 2 | edb |wal flush | 28 |0.040443 | 0.095150 | 0.431984 2 | edb |wal write | 28 |0.040434 | 0.095093 | 0.431698 2 | edb |query plan | 299 |0.001479 | 0.049425 | 0.262596
11.9.2 edb$session_waits
The edb$session_waits view summarizes the number of waits and the total wait time per session for each wait named and identified by backend ID. It also displays the average and max wait times. edb$session_waits summarizes the following information:
Column | Type | Modifiers |Definition
-----------------+---------------+-----------+----------------
backend_id | bigint | |session identifier
wait_count | bigint | |number of times the event
occurs
avg_wait_time | numeric | |average wait time in
microseconds
max_wait_time | numeric(50,6) | |maximum wait time in
microseconds
total_wait_time | numeric(50,6) | |total wait time in
microseconds
wait_name | text | |name of the event
The following code sample shows the result of a SELECT statement on the edb$session_waits view:
SELECT * FROM sys.edb$session_waits; edb_id | dbname | backend_id | wait_name | wait_count | avg_wait_time | max_wait_time| total_wait_time | usename | current_query --------+--------+------------+---------------+------------+---------------+--------------+-----------------+--------------+--------------------------- 1 | edb | 22935 | db file read | 175 | 0.008399 | 0.629986 | 1.469887 | enterprisedb | <IDLE> 1 | edb | 22988 | db file read | 116 | 0.009556 | 0.040627 | 1.108438 | enterprisedb | select * from edbsnap(); 1 | edb | 22988 | wal flush | 26 | 0.010364 | 0.085380 | 0.269452 | enterprisedb | select * from edbsnap(); (3 rows)
11.9.3 edb$session_wait_history
The edb$session_wait_history view contains the last 25 wait events for each backend ID active during the session. The edb$session_wait_history view includes the following information:
Column | Type | Modifiers | Definition
------------+--------+-----------+--------------------------
edb_id | numeric| |identifier
dbname | text | |database name
backend_id | bigint | |session identifier
seq | bigint | |number between 1 and 25
wait_name | text | |name of the event
elapsed | bigint | |elapsed time in microseconds
p1 | bigint | |variable #1- meaning dependent on
event
p2 | bigint | |variable #2- meaning dependent on
event
p3 | bigint | |variable #3- meaning dependent on
event
The following code sample shows the result of a SELECT statement on the edb$session_wait_history view:
SELECT * FROM sys.edb$session_wait_history; edb_id | dbname | backend_id | seq | wait_name | elapsed | p1 | p2 | p3 --------+--------+------------+-----+---------------+---------+----+----+---- 1 | edb | 22935 | 1 | query plan | 54 | 0 | 0 | 0 1 | edb | 22935 | 2 | db file read | 1116 |2689| 8 | 1 1 | edb | 22935 | 3 | db file read | 983 |1255| 32 | 1 1 | edb | 22935 | 4 | db file read | 13717 |2691| 19 | 1 1 | edb | 22935 | 5 | query plan | 75 | 0| 0 | 0 1 | edb | 22935 | 6 | db file read | 11053 |1255| 7 | 1 1 | edb | 22935 | 7 | db file read | 404 |2689| 4 | 1 (7 rows)
