Previous PageTable Of ContentsNext Page

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)

Previous PageTable Of ContentsNext Page