Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 11.5 DRITA Functions

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

11.5 DRITA Functions

11.5.1 get_snaps()

The get_snaps() function returns a list of the current snapshots. The signature is:

      get_snaps()

The following example demonstrates using the get_snaps() function to display a list of snapshots:

edb=# SELECT * FROM get_snaps();
          get_snaps           
------------------------------
 1  11-FEB-10 10:41:05.668852
 2  11-FEB-10 10:42:27.26154
 3  11-FEB-10 10:45:48.999992
 4  11-FEB-10 11:01:58.345163
 5  11-FEB-10 11:05:14.092683
 6  11-FEB-10 11:06:33.151002
 7  11-FEB-10 11:11:16.405664
 8  11-FEB-10 11:13:29.458405
 9  11-FEB-10 11:23:57.595916
 10 11-FEB-10 11:29:02.214014
 11 11-FEB-10 11:31:44.244038
(11 rows)

The first column in the list of snapshots contains the session identifier; the DRITA functions use the session identifier to operate on a specific snapshot.

11.5.2 sys_rpt()

The sys_rpt() function returns system wait information. The signature is:

      sys_rpt(beginning_id, ending_id, top_n)

Parameters

beginning_id

beginning_id is an integer value that represents the beginning session identifier.

ending_id

ending_id is an integer value that represents the ending session identifier.

top_n

top_n represents the number of rows to return

This example demonstrates a call to the sys_rpt()function:

edb=# SELECT * FROM sys_rpt(9, 10, 10);
                                   sys_rpt                                   
-----------------------------------------------------------------------------
 WAIT NAME                                COUNT      WAIT TIME       % WAIT
 ---------------------------------------------------------------------------
 wal write                                21250      104.723772      36.31
 db file read                             121407     72.143274       25.01
 wal flush                                84185      51.652495       17.91
 wal file sync                            712        29.482206       10.22
 infinitecache write                      84178      15.814444       5.48
 db file write                            84177      14.447718       5.01
 infinitecache read                       672        0.098691        0.03
 db file extend                           190        0.040386        0.01
 query plan                               52         0.024400        0.01
 wal insert lock acquire                  4          0.000837        0.00
(12 rows)

11.5.3 sess_rpt()

The sess_rpt() function returns session wait information. The signature is:

      sess_rpt(beginning_id, ending_id, top_n)

Parameters

beginning_id

beginning_id is an integer value that represents the beginning session identifier.

ending_id

ending_id is an integer value that represents the ending session identifier.

top_n

top_n represents the number of rows to return

The following example demonstrates a call to the sess_rpt()function:

SELECT * FROM sess_rpt(18, 19, 10);

                              sess_rpt                                       
-----------------------------------------------------------------------------
ID    USER       WAIT NAME              COUNT TIME(ms)   %WAIT SES  %WAIT ALL
 ----------------------------------------------------------------------------

 17373 enterprise db file read           30   0.175713   85.24      85.24
 17373 enterprise query plan             18   0.014930   7.24       7.24
 17373 enterprise wal flush              6    0.004067   1.97       1.97
 17373 enterprise wal write              1    0.004063   1.97       1.97
 17373 enterprise wal file sync          1    0.003664   1.78       1.78
 17373 enterprise infinitecache read     38   0.003076   1.49       1.49
 17373 enterprise infinitecache write    5    0.000548   0.27       0.27
 17373 enterprise db file extend         190  0.04.386   0.03       0.03
 17373 enterprise db file write          5    0.000082   0.04       0.04
 17373 enterprise wal write lock acquire 0    0.000000   0.00       0.00
 17373 enterprise bgwriter comm lock ac  0    0.000000   0.00       0.00
(13 rows)

11.5.4 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

beginning_id is an integer value that represents the beginning session identifier.

ending_id

ending_id is an integer value that represents the ending session identifier.

backend_id

backend_id is an integer value that represents the backend identifier.

The following code sample demonstrates a call to sessid_rpt():

SELECT * FROM sessid_rpt(18, 19, 17373);

                                sessid_rpt                                 
-----------------------------------------------------------------------------
 ID    USER       WAIT NAME             COUNT TIME(ms)  %WAIT SES   %WAIT ALL
 ----------------------------------------------------------------------------
 17373 enterprise db file read           30   0.175713  85.24       85.24
 17373 enterprise query plan             18   0.014930  7.24        7.24
 17373 enterprise wal flush              6    0.004067  1.97        1.97
 17373 enterprise wal write              1    0.004063  1.97        1.97
 17373 enterprise wal file sync          1    0.003664  1.78        1.78
 17373 enterprise infinitecache read     38   0.003076  1.49        1.49
 17373 enterprise infinitecache write    5    0.000548  0.27        0.27
 17373 enterprise db file extend         190  0.040386  0.03        0.03
 17373 enterprise db file write          5    0.000082  0.04        0.04
 17373 enterprise wal write lock acquire 0    0.000000  0.00        0.00
 17373 enterprise bgwriter comm lock ac  0    0.000000  0.00        0.00
(13 rows)

11.5.5 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

snapshot_id is an integer value that identifies the snapshot.

session_id

session_id is an integer value that represents the session.

The following example demonstrates a call to the sesshist_rpt()function:

edb=# SELECT * FROM sesshist_rpt (9, 5531);
                              sesshist_rpt                                                   
----------------------------------------------------------------------------
 ID    USER       SEQ  WAIT NAME                
   ELAPSED(ms)   File  Name                 # of Blk   Sum of Blks 
 ----------------------------------------------------------------------------
 5531 enterprise 1     db file read 
   18546        14309  session_waits_pk     1          1           
 5531 enterprise 2     infinitecache read       
   125          14309  session_waits_pk     1          1           
 5531 enterprise 3     db file read             
   376          14304  edb$session_waits    0          1           
 5531 enterprise 4     infinitecache read       
   166          14304  edb$session_waits    0          1           
 5531 enterprise 5     db file read             
   7978         1260   pg_authid            0          1           
 5531 enterprise 6     infinitecache read       
   154          1260   pg_authid            0          1           
 5531 enterprise 7     db file read             
   628          14302  system_waits_pk      1          1           
 5531 enterprise 8     infinitecache read       
   463          14302  system_waits_pk      1          1           
 5531 enterprise 9     db file read             
   3446         14297  edb$system_waits     0          1           
 5531 enterprise 10    infinitecache read       
   187          14297  edb$system_waits     0          1           
 5531 enterprise 11    db file read             
   14750        14295  snap_pk              1          1           
 5531 enterprise 12    infinitecache read       
   416          14295  snap_pk              1          1           
 5531 enterprise 13    db file read             
   7139         14290  edb$snap             0          1           
 5531 enterprise 14    infinitecache read       
   158          14290  edb$snap             0          1           
 5531 enterprise 15    db file read             
   27287        14288  snapshot_num_seq     0          1           
 5531 enterprise 16    infinitecache read       
   180          14288  snapshot_num_seq     0          1           
 5531 enterprise 17    query plan               
   26           0      N/A                  0          0           
 5531 enterprise 18    db file read             
   84552        16396  pgbench_accounts     4358       1           
 5531 enterprise 19    infinitecache read       
   226          16396  pgbench_accounts     4358       1           
 5531 enterprise 20    db file read             
   334838       16401  pgbench_accounts_pke 7792       1           
 5531 enterprise 21    infinitecache read       
   213          16401  pgbench_accounts_pke 7792       1           
 5531 enterprise 22    db file read             
   52619        16396  pgbench_accounts     24829      1           
 5531 enterprise 23    infinitecache read       
   210          16396  pgbench_accounts     24829      1           
 5531 enterprise 24    infinitecache read       
   216          16401  pgbench_accounts_pke 13460      1           
 5531 enterprise 25    db file read             
   13925        16396  pgbench_accounts     27695      1           
(27 rows)

11.5.6 purgesnap()

The purgesnap() function purges a range of snapshots from the snapshot tables. The signature is:

      purgesnap(beginning_id, ending_id)

Parameters

beginning_id

beginning_id is an integer value that represents the beginning session identifier.

ending_id

ending_id is 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 have been purged from the snapshot tables:

edb=# SELECT * FROM get_snaps();
          get_snaps           
------------------------------
 1  11-FEB-10 10:41:05.668852
 2  11-FEB-10 10:42:27.26154
 3  11-FEB-10 10:45:48.999992
 4  11-FEB-10 11:01:58.345163
 5  11-FEB-10 11:05:14.092683
 10 11-FEB-10 11:29:02.214014
 11 11-FEB-10 11:31:44.244038
(7 rows)

11.5.7 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 have been removed from the snapshot tables:

SELECT * FROM get_snaps
 get_snaps 
-----------
(0 rows)

Previous PageTable Of ContentsNext Page