Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 11.6 Simulating Statspack AWR Reports

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

11.6 Simulating Statspack AWR Reports

The functions described in this section return information comparable to the information contained in an Oracle Statspack/AWR (Automatic Workload Repository) report. When taking a snapshot, performance data from system catalog tables is saved into history tables. The reporting functions listed below report on the differences between two given snapshots.

      stat_db_rpt()

      stat_tables_rpt()

      statio_tables_rpt()

      stat_indexes_rpt()

      statio_indexes_rpt()

The reporting functions can be executed individually or you can execute all five functions by calling the edbreport() function.

11.6.1 edbreport()

The edbreport() function includes data from the other reporting functions, plus additional system information. The signature is:

      edbreport(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.

The following code sample demonstrates a call to the edbreport() function:

edb=# SELECT * FROM edbreport(9, 10);
                                                                       edbreport                                                                       
----------------------------------------------------------------------------------------------------------------------------------------------------------
 EnterpriseDB Report for database edb        11-FEB-10
 Version: EnterpriseDB 8.4.0.1 on i686-pc-linux-gnu, compiled by GCC gcc (Ubuntu 4.3.2-1ubuntu12) 4.3.2, 32-bit
 
      Begin snapshot: 9 at 11-FEB-10 11:23:57.595916
      End snapshot:   10 at 11-FEB-10 11:29:02.214014
 
 Size of database edb is 909 MB
      Tablespace: pg_default Size: 925 MB Owner: jeevan
      Tablespace: pg_global Size: 448 kB Owner: jeevan
 
 Schema: public                         Size: 900 MB          Owner: jeevan              
 Schema: pg_toast_temp_1                Size: 0 bytes         Owner: jeevan              
 
                Top 10 Relations by pages
 
 TABLE                                        RELPAGES  
 ----------------------------------------------------------------------------
 pgbench_accounts                              95282     
 pg_proc                                       107       
 pg_depend                                     71        
 pg_attribute                                  48        
 edb$statio_all_indexes                        47        
 edb$stat_all_indexes                          47        
 pgbench_tellers                               45        
 edb$statio_all_tables                         36        
 edb$stat_all_tables                           35        
 pg_description                                22        
 
                Top 10 Indexes by pages
 
 INDEX                                        RELPAGES  
 ----------------------------------------------------------------------------
 pgbench_accounts_pkey                         19769     
 pg_depend_depender_index                      60        
 pg_depend_reference_index                     58        
 pg_proc_proname_args_nsp_index                45        
 pg_attribute_relid_attnam_index               16        
 pg_description_o_c_o_index                    16        
 pg_proc_oid_index                             11        
 pg_attribute_relid_attnum_index               11        
 pg_operator_oprname_l_r_n_index               9         
 edb$stat_idx_pk                               8         
 

                Top 10 Relations by DML
 
 SCHEMA   RELATION                             UPDATES   DELETES   INSERTS   
 ----------------------------------------------------------------------------
 public   pgbench_accounts                     3153453   0         3000000   
 public   pgbench_tellers                      152534    0         300       
 public   pgbench_branches                     152520    0         30        
 public   pgbench_history                      0         0         152497    
 sys      edb$stat_all_indexes                 0         0         1287      
 sys      edb$statio_all_indexes               0         0         1287      
 sys      edb$stat_all_tables                  0         0         968       
 sys      edb$statio_all_tables                0         0         968       
 sys      edb$session_wait_history             0         0         325       
 sys      edb$session_waits                    0         0         67        
 

   DATA from pg_stat_database

DATABASE NUM       XACT    XACT      BLKS   BLKS    BLKS    HIT    ICACHE
         BACKENDS  COMMIT  ROLLBACK  READ   HIT     ICACHE  RATIO  HIT RATIO
-----------------------------------------------------------------------------
edb      1         21      0         92928  101217  301     52.05      0.15                
 

   DATA from pg_buffercache
 
 RELATION                            BUFFERS   
 ----------------------------------------------------------------------------
 pgbench_accounts                    14549     
 pgbench_accounts_pkey               68        
 edb$statio_all_indexes              49        
 edb$statio_all_tables               38        
 edb$stat_all_indexes                27        
 pg_proc                             26        
 edb$stat_all_tables                 20        
 pg_statistic                        20        
 pg_operator                         18        
 pg_attribute                        16        
 




   DATA from pg_stat_all_tables ordered by seq scan
 
SCHEMA     RELATION                 SEQ  REL      IDX  IDX       INS UPD  DEL 
                                    SCAN TUP READ SCAN TUP READ 
-----------------------------------------------------------------------------pg_catalog pg_namespace             39   500      0    0         0   0     0     
pg_catalog pg_class                 38   8954     17   17        0   0     0     
pg_catalog pg_database              5    15       5    5         0   0     0     
pg_catalog pg_index                 4    468      4    4         0   0     0     
sys        edb$snap                 1    9        0    0         1   0     0     
pg_catalog pg_authid                1    1        0    0         0   0     0     
public     pgbench_accounts         1    3000000  2    10998     0   10989 0     
sys        edb$session_wait_history 0    0        0    0         25  0     0     
sys        edb$session_waits        0    0        0    0         11  0     0     
sys        edb$stat_all_indexes     0    0        0    0         117 0     0     



 


   DATA from pg_stat_all_tables ordered by rel tup read
 
SCHEMA     RELATION                 SEQ  REL      IDX  IDX       INS UPD  DEL 
                                    SCAN TUP READ SCAN TUP READ 
-----------------------------------------------------------------------------
public     pgbench_accounts         1    3000000  2    10998     0   10998  0     
pg_catalog pg_class                 38   8954     17   17        0   0      0     
pg_catalog pg_namespace             39   500      0    0         0   0      0     
pg_catalog pg_index                 4    468      4    4         0   0      0     
pg_catalog pg_database              5    15       5    5         0   0      0     
sys        edb$snap                 1    9        0    0         1   0      0     
pg_catalog pg_authid                1    1        0    0         0   0      0     
sys        edb$session_wait_history 0    0        0    0         25  0      0     
sys        edb$session_waits        0    0        0    0         11  0      0     
sys        edb$stat_all_indexes     0    0        0    0         117 0      0     



 









   DATA from pg_statio_all_tables

 
SCHEMA     RELATION             HEAP     HEAP     HEAP     IDX      IDX      IDX      TOAST    TOAST    TOAST    TIDX     TIDX     TIDX    
                                READ     HIT      ICACHE   READ     HIT      ICACHE   READ     HIT      ICACHE   READ     HIT      ICACHE  
                                                  HIT                        HIT                        HIT                        HIT     
-----------------------------------------------------------------------------------------------------------------------------------------------------
public     pgbench_accounts     92766    67215    288      59       32126    9        0        0        0        0        0        0       
pg_catalog pg_class             0        296      0        3        16       0        0        0        0        0        0        0       
sys        edb$stat_all_indexes 8        125      0        4        233      0        0        0        0        0        0        0       
sys        edb$statio_all_index 8        125      0        4        233      0        0        0        0        0        0        0       
sys        edb$stat_all_tables  6        91       0        2        174      0        0        0        0        0        0        0       
sys        edb$statio_all_table 6        91       0        2        174      0        0        0        0        0        0        0       
pg_catalog pg_namespace         3        72       0        0        0        0        0        0        0        0        0        0       
sys        edb$session_wait_his 1        24       0        4        47       0        0        0        0        0        0        0       
pg_catalog pg_opclass           3        13       0        2        0        0        0        0        0        0        0        0       
pg_catalog pg_trigger           0        12       0        1        15       0        0        0        0        0        0        0       
 


   DATA from pg_stat_all_indexes
 
 SCHEMA     RELATION     INDEX                             IDX   IDX  IDX 
                                                           SCAN  TUP  TUP
                                                                 READ FETCH
 --------------------------------------------------------------------------- 
 pg_catalog pg_cast      pg_cast_source_target_index       30    7    7              
 pg_catalog pg_class     pg_class_oid_index                15    15   15             
 pg_catalog pg_trigger   pg_trigger_tgrelid_tgname_index   12    12   12            
 pg_catalog pg_attribute pg_attribute_relid_attnum_index   7     31   31             
 pg_catalog pg_statistic pg_statistic_relid_att_index      7     0    0              
 pg_catalog pg_database  pg_database_oid_index             5     5    5              
 pg_catalog pg_proc      pg_proc_oid_index                 5     5    5              
 pg_catalog pg_operator  pg_operator_oprname_l_r_n_index   3     1    1              
 pg_catalog pg_type      pg_type_typname_nsp_index         3     1    1              
 pg_catalog pg_amop      pg_amop_opr_fam_index             2     3    3              
 







   DATA from pg_statio_all_indexes
 
 SCHEMA     RELATION               INDEX                           
        IDX BLKS READ   IDX BLKS HIT    IDX BLKS ICACHE HIT 
----------------------------------------------------------------------------- 
 public     pgbench_accounts       pgbench_accounts_pkey               
        59              32126           9                   
 sys        edb$stat_all_indexes   edb$stat_idx_pk                     
        4               233             0                   
 sys        edb$statio_all_indexes edb$statio_idx_pk                   
        4               233             0                   
 sys        edb$stat_all_tables    edb$stat_tab_pk                     
        2               174             0                   
 sys        edb$statio_all_tables  edb$statio_tab_pk                   
        2               174             0                   

...skipping one line

 pg_catalog pg_cast                pg_cast_source_target_index         
        1               29              0                   
 pg_catalog pg_trigger             pg_trigger_tgrelid_tgname_index     
        1               15              0                   
 pg_catalog pg_class               pg_class_oid_index                  
        1               14              0                   
 pg_catalog pg_statistic           pg_statistic_relid_att_index        
        2               12              0                   
 



    System Wait Information
 
 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
 


    Database Parameters from postgresql.conf 
 
 PARAMETER                         SETTING                                      
                                       CONTEXT     MINVAL       MAXVAL       
-----------------------------------------------------------------------------
 add_missing_from                   off                                      
                                       user                                 
 allow_system_table_mods            off                                       
                                       postmaster                           
 archive_command                    (disabled)                               
                                       sighup                               
 archive_mode                       off                                      
                                       postmaster                           
 archive_timeout                    0                                        
                                       sighup      0            2147483647  
 array_nulls                        on                                       
                                       user                                 
 authentication_timeout             60                                       
                                       sighup      1            600         
 autovacuum                         on                                       
                                       sighup                               
 autovacuum_analyze_scale_factor    0.1                                      
                                       sighup      0            100         
 autovacuum_analyze_threshold       50                                       
                                       sighup      0            2147483647  
 autovacuum_freeze_max_age          200000000                                
                                       postmaster  100000000    2000000000  
 autovacuum_max_workers             3                                        
                                       postmaster  1            536870911   
 autovacuum_naptime                 60                                       
                                       sighup      1            2147483     
 autovacuum_vacuum_cost_delay       20                                       
                                       sighup      -1           100         
 autovacuum_vacuum_cost_limit       -1                                       
                                       sighup      -1           10000       
 autovacuum_vacuum_scale_factor     0.2                                      
                                       sighup      0            100         
 autovacuum_vacuum_threshold        1000                                     
                                       sighup      0            2147483647  
 backslash_quote                    safe_encoding                            
                                       user                                 
 bgwriter_delay                     200                                      
                                       sighup      10           10000       
 bgwriter_lru_maxpages              100                                      
                                       sighup      0            1000        
 bgwriter_lru_multiplier            2                                        
                                       sighup      0            10          
 block_size                         8192                                     
                                       internal    8192         8192        
 bonjour_name                                                                
                                       postmaster                           
 check_function_bodies              on                                       
                                       user                                 
 checkpoint_completion_target       0.5                                      
                                       sighup      0            1           
 checkpoint_segments                64                                       
                                       sighup      1            2147483647  
 checkpoint_timeout                 300                                      
                                       sighup      30           3600        
 checkpoint_warning                 30                                       
                                       sighup      0            2147483647  
 client_encoding                    UTF8                                     
                                       user                                 
 client_min_messages                notice                                   
                                       user                                 
 commit_delay                       0                                        
                                       user        0            100000      
 commit_siblings                    5                                         
                                       user        1            1000        
 config_file                        /home/jeevan/edbas84_20100210/data/postg 
                                       postmaster                           
 constraint_exclusion               partition                                
                                       user                                 
 cpu_index_tuple_cost               0.005                                    
                                       user        0            1.79769e+308
 cpu_operator_cost                  0.0025                                   
                                       user        0            1.79769e+308
 cpu_tuple_cost                     0.01                                     
                                       user        0            1.79769e+308
 cursor_tuple_fraction              0.1                                      
                                       user        0            1           
 custom_variable_classes                                                     
                                       sighup                               
 data_directory                     /home/jeevan/edbas84_20100210/data       
                                       postmaster                           
 DateStyle                          Redwood, SHOW_TIME                       
                                       user                                 
 db_dialect                         redwood                                  
                                       user                                 
 db_user_namespace                  off                                      
                                       sighup                               
 deadlock_timeout                   1000                                     
                                       sighup      1            2147483     
 debug_assertions                   on                                       
                                       user                                 
 debug_pretty_print                 on                                       
                                       user                                 
 debug_print_parse                  off                                      
                                       user                                 
 debug_print_plan                   off                                      
                                       user                                 
 debug_print_rewritten              off                                      
                                       user                                 
 default_heap_fillfactor            100                                      
                                       user        10           100         
 default_statistics_target          100                                      
                                       user        1            10000       
 default_tablespace                                                          
                                       user                                 
 default_text_search_config         pg_catalog.english                       
                                       user                                 
 default_transaction_isolation      read committed                           
                                       user                                 
 default_transaction_read_only      off                                      
                                       user                                 
 default_with_oids                  off                                      
                                       user                                 
 default_with_rowids                off                                      
                                       user                                 
 dynamic_library_path               $libdir                                  
                                       superuser                            
 edb_audit                          none                                     
                                       sighup                               
 edb_audit_connect                  failed                                   
                                       sighup                               
 edb_audit_directory                edb_audit                                
                                       sighup                               
 edb_audit_disconnect               none                                     
                                       sighup                               
 edb_audit_filename                 audit-%Y%m%d_%H%M%S                      
                                       sighup                               
 edb_audit_rotation_day             every                                    
                                       sighup                               
 edb_audit_rotation_seconds         0                                        
                                       sighup      0            2147483647  
 edb_audit_rotation_size            0                                        
                                       sighup      0            5000        
 edb_audit_statement                ddl, error                               
                                       sighup                               
 edb_connectby_order                off                                      
                                       user                                 
 edb_dynatune                       66                                       
                                       postmaster  0            100         
 edb_dynatune_profile               oltp                                     
                                       postmaster                           
 edb_enable_icache                  on                                       
                                       postmaster                           
 edb_icache_compression_level       0                                        
                                       superuser   0            9           
 edb_icache_servers                 localhost                                
                                       postmaster                           
 edb_redwood_date                   on                                       
                                       user                                 
 edb_redwood_strings                on                                       
                                       user                                 
 edb_stmt_level_tx                  off                                      
                                       user                                 
 effective_cache_size               95937                                    
                                       user        1            2147483647  
 effective_io_concurrency           1                                        
                                       user        0            1000        
 enable_bitmapscan                  on                                       
                                       user                                 
 enable_hashagg                     on                                       
                                       user                                 
 enable_hashjoin                    on                                       
                                       user                                 
 enable_hints                       on                                       
                                       user                                 
 enable_indexscan                   on                                       
                                       user                                 
 enable_mergejoin                   on                                       
                                       user                                 
 enable_nestloop                    on                                       
                                       user                                 
 enable_seqscan                     on                                       
                                       user                                 
 enable_sort                        on                                       
                                       user                                 
 enable_tidscan                     on                                       
                                       user                                 
 escape_string_warning              on                                       
                                       user                                 
 external_pid_file                                                             
                                       postmaster                           
 extra_float_digits                 0                                        
                                       user        -15          2           
 from_collapse_limit                8                                        
                                       user        1            2147483647  
 fsync                              on                                       
                                       sighup                               
 full_page_writes                   on                                       
                                       sighup                               
 geqo                               on                                       
                                       user                                 
 geqo_effort                        5                                        
                                       user        1            10          
 geqo_generations                   0                                        
                                       user        0            2147483647  
 geqo_pool_size                     0                                        
                                       user        0            2147483647  
 geqo_selection_bias                2                                        
                                       user        1.5          2           
 geqo_threshold                     12                                       
                                       user        2            2147483647  
 gin_fuzzy_search_limit             0                                        
                                       user        0            2147483647  
 hba_file                           /home/jeevan/edbas84_20100210/data/pg_hb 
                                       postmaster                           
 ident_file                         /home/jeevan/edbas84_20100210/data/pg_id 
                                       postmaster                           
 ignore_system_indexes              off                                      
                                       backend                              
 integer_datetimes                  off                                      
                                       internal                             
 IntervalStyle                      postgres                                 
                                       user                                 
 join_collapse_limit                8                                        
                                       user        1            2147483647  
 krb_caseins_users                  off                                      
                                       sighup                               
 krb_server_keyfile                 FILE:/home/jeevan/edbas84_20100210/insta 
                                       sighup                               
 krb_srvname                        postgres                                 
                                       sighup                               
 lc_collate                         en_IN                                    
                                       internal                             
 lc_ctype                           en_IN                                    
                                       internal                             
 lc_messages                        en_IN                                    
                                       superuser                            
 lc_monetary                        en_IN                                    
                                       user                                 
 lc_numeric                         en_IN                                    
                                       user                                 
 lc_time                            en_IN                                    
                                       user                                 
 listen_addresses                   localhost                                
                                       postmaster                           
 local_preload_libraries                                                     
                                       backend                              
 log_autovacuum_min_duration        -1                                       
                                       sighup      -1           2147483     
 log_checkpoints                    off                                      
                                       sighup                               
 log_connections                    off                                      
                                       backend                              
 log_destination                    stderr                                   
                                       sighup                               
 log_directory                      pg_log                                   
                                       sighup                               
 log_disconnections                 off                                      
                                       backend                              
 log_duration                       off                                      
                                       superuser                            
 log_error_verbosity                default                                  
                                       superuser                            
 log_executor_stats                 off                                       
                                       superuser                            
 log_filename                       postgresql-%Y-%m-%d_%H%M%S.log           
                                       sighup                               
 logging_collector                  off                                      
                                       postmaster                           
 log_hostname                       off                                      
                                       sighup                               
 log_line_prefix                                                             
                                       sighup                               
 log_lock_waits                     off                                      
                                       superuser                            
 log_min_duration_statement         -1                                       
                                       superuser   -1           2147483     
 log_min_error_statement            error                                    
                                       superuser                            
 log_min_messages                   warning                                  
                                       superuser                            
 log_parser_stats                   off                                      
                                       superuser                            
 log_planner_stats                  off                                      
                                       superuser                            
 log_rotation_age                   1440                                     
                                       sighup      0            35791394    
 log_rotation_size                  10240                                    
                                       sighup      0            2097151     
 log_statement                      none                                     
                                       superuser                            
 log_statement_stats                off                                      
                                       superuser                            
 log_temp_files                     -1                                       
                                       superuser   -1           2147483647  
 log_timezone                       Asia/Kolkata                             
                                       sighup                               
 log_truncate_on_rotation           off                                      
                                       sighup                               
 maintenance_work_mem               17807                                    
                                       user        1024         2097151     
 max_connections                    100                                      
                                       postmaster  1            536870911   
 max_files_per_process              1000                                     
                                       postmaster  25           2147483647  
 max_function_args                  256                                      
                                       internal    256          256         
 max_identifier_length              63                                       
                                       internal    63           63          
 max_index_keys                     32                                       
                                       internal    32           32          
 max_locks_per_transaction          64                                       
                                       postmaster  10           2147483647  
 max_prepared_transactions          0                                        
                                       postmaster  0            536870911   
 max_stack_depth                    2048                                     
                                       superuser   100          2097151     
 nls_date_format                    Redwood, SHOW_TIME                       
                                       user                                 
 nls_language                       en_IN                                    
                                       superuser                            
 nls_length_semantics               byte                                     
                                       superuser                            
 odbc_lib_path                                                               
                                       postmaster                           
 optimizer_mode                     CHOOSE                                   
                                       user                                 
 oracle_home                                                                 
                                       postmaster                           
 password_encryption                on                                       
                                       user                                 
 pl_dialect                         edb-spl                                  
                                       user                                 
 port                               5444                                     
                                       postmaster  1            65535       
 post_auth_delay                    0                                        
                                       backend     0            2147483647  
 pre_auth_delay                     0                                        
                                       sighup      0            60          
 query_rewrite_enabled              false                                    
                                       user                                 
 query_rewrite_integrity            enforced                                 
                                       superuser                            
 random_page_cost                   4                                        
                                       user        0            1.79769e+308
 regex_flavor                       advanced                                 
                                       user                                 
 search_path                        "$user",public                           
                                       user                                 
 segment_size                       131072                                   
                                       internal    131072       131072      
 seq_page_cost                      1                                        
                                       user        0            1.79769e+308
 server_encoding                    UTF8                                     
                                       internal                             
 server_version                     8.4.0.1                                  
                                       internal                             
 server_version_num                 80400                                    
                                       internal    80400        80400       
 session_replication_role           origin                                   
                                       superuser                            
 shared_buffers                     15007                                    
                                       postmaster  16           1073741823  
 shared_preload_libraries           $libdir/plugins/plugin_debugger,$libdir/ 
                                       postmaster                           
 silent_mode                        off                                      
                                       postmaster                           
 sql_inheritance                    on                                       
                                       user                                 
 ssl                                off                                      
                                       postmaster                           
 ssl_ciphers                        ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH        
                                       postmaster                           
 standard_conforming_strings        off                                      
                                       user                                 
 statement_timeout                  0                                        
                                       user        0            2147483647  
 stats_temp_directory               pg_stat_tmp                              
                                       sighup                               
 superuser_reserved_connections     3                                        
                                       postmaster  0            536870911   
 synchronize_seqscans               on                                       
                                       user                                 
 synchronous_commit                 on                                       
                                       user                                 
 syslog_facility                    local0                                   
                                       sighup                               
 syslog_ident                       postgres                                 
                                       sighup                               
 tcp_keepalives_count               0                                        
                                       user        0            2147483647  
 tcp_keepalives_idle                0                                        
                                       user        0            2147483647  
 tcp_keepalives_interval            0                                        
                                       user        0            2147483647  
 temp_buffers                       1024                                     
                                       user        100          1073741823  
 temp_tablespaces                                                            
                                       user                                 
 timed_statistics                   on                                       
                                       user                                 
 TimeZone                           Asia/Kolkata                             
                                       user                                 
 timezone_abbreviations             Default                                  
                                       user                                 
 trace_hints                        off                                      
                                       user                                 
 trace_notify                       off                                      
                                       user                                 
 trace_sort                         off                                      
                                       user                                 
 track_activities                   on                                       
                                       superuser                            
 track_activity_query_size          1024                                     
                                       postmaster  100          102400      
 track_counts                       on                                       
                                       superuser                            
 track_functions                    none                                     
                                       superuser                            
 transaction_isolation              read committed                           
                                       user                                 
 transaction_read_only              off                                      
                                       user                                 
 transform_null_equals              off                                      
                                       user                                 
 unix_socket_directory                                                       
                                       postmaster                           
 unix_socket_group                                                           
                                       postmaster                           
 unix_socket_permissions            511                                      
                                       postmaster  0            511         
 update_process_title               on                                       
                                       superuser                            
 vacuum_cost_delay                  0                                        
                                       user        0            100         
 vacuum_cost_limit                  200                                      
                                       user        1            10000       
 vacuum_cost_page_dirty             20                                       
                                       user        0            10000       
 vacuum_cost_page_hit               1                                        
                                       user        0            10000       
 vacuum_cost_page_miss              10                                       
                                       user        0            10000       
 vacuum_freeze_min_age              50000000                                 
                                       user        0            1000000000  
 vacuum_freeze_table_age            150000000                                
                                       user        0            2000000000  
 wal_block_size                     8192                                     
                                       internal    8192         8192        
 wal_buffers                        256                                      
                                       postmaster  4            2147483647  
 wal_segment_size                   16777216                                 
                                       internal    0            2147483647  
 wal_sync_method                    fdatasync                                
                                       sighup                               
 wal_writer_delay                   200                                      
                                       sighup      1            10000       
 work_mem                           1795                                     
                                       user        64           2097151     
 xmlbinary                          base64                                   
                                       user                                 
 xmloption                          content                                  
                                       user                                 
 zero_damaged_pages                 off                                      
                                       superuser                            
(402 rows)

11.6.2 stat_db_rpt()

The signature is:

      stat_db_rpt(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.

The following example demonstrates the stat_db_rpt() function:

SELECT * FROM stat_db_rpt(9, 10);
                               stat_db_rpt                                                           
-----------------------------------------------------------------------------
   DATA from pg_stat_database
 
 DATABASE   NUMBACKENDS  XACT COMMIT  XACT ROLLBACK   BLKS READ  BLKS HIT 
        BLKS ICACHE HIT      HIT RATIO      ICACHE HIT RATIO    
-----------------------------------------------------------------------------
 edb        1            21           0               92928      101217    
        301                  52.05          0.15

11.6.3 stat_tables_rpt()

The signature is:

      function_name(beginning_id, ending_id, top_n, scope)

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

scope

scope determines which tables the function returns statistics about. Specify SYS, USER or ALL:

      SYS indicates that the function should return information about system defined tables. A table is considered a system table if it is stored in one of the following schemas: pg_catalog, information_schema, sys, or dbo.

      USER indicates that the function should return information about user-defined tables.

      ALL specifies that the function should return information about all tables.

The following code sample demonstrates the stat_tables_rpt() function:

SELECT * FROM stat_tables_rpt(18, 19, 10, 'ALL');

stat_tables_rpt                                                      
-----------------------------------------------------------------------------
DATA from pg_stat_all_tables ordered by seq scan

SCHEMA        RELATION                       
    SEQ SCAN   REL TUP READ IDX SCAN   IDX TUP READ   INS    UPD    DEL   
-----------------------------------------------------------------------------
pg_catalog    pg_class                       
    8          2952         78         65             0      0      0     
pg_catalog    pg_index                       
    4          448          23         28             0      0      0     
pg_catalog    pg_namespace                   
    4          76           1          1              0      0      0     
pg_catalog    pg_database                    
    3          6            0          0              0      0      0     
pg_catalog    pg_authid                      
    2          1            0          0              0      0      0     
sys           edb$snap                       
    1          15           0          0              1      0      0     
public        accounts                       
    0          0            0          0              0      0      0     
public        branches                       
    0          0            0          0              0      0      0     
sys           edb$session_wait_history       
    0          0            0          0              25     0      0     
sys           edb$session_waits              
    0          0            0          0              10     0      0     

DATA from pg_stat_all_tables ordered by rel tup read

SCHEMA       RELATION                       
    SEQ SCAN   REL TUP READ IDX SCAN   IDX TUP READ INS    UPD    DEL   
-----------------------------------------------------------------------------
pg_catalog   pg_class                       
    8          2952         78         65           0      0      0     
pg_catalog   pg_index                       
    4          448          23         28           0      0      0     
pg_catalog   pg_namespace                   
    4          76           1          1            0      0      0     
sys          edb$snap                       
    1          15           0          0            1      0      0     
pg_catalog   pg_database                    
    3          6            0          0            0      0      0     
pg_catalog   pg_authid                      
    2          1            0          0            0      0      0     
public       accounts                       
    0          0            0          0            0      0      0     
public       branches                       
    0          0            0          0            0      0      0     
sys          edb$session_wait_history       
    0          0            0          0            25     0      0     
sys          edb$session_waits              
    0          0            0          0            10     0      0     
(29 rows)

11.6.4 statio_tables_rpt()

The signature is:

      statio_tables_rpt(beginning_id, ending_id, top_n, scope)

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

scope

scope determines which tables the function returns statistics about. Specify SYS, USER or ALL:

      SYS indicates that the function should return information about system defined tables. A table is considered a system table if it is stored in one of the following schemas: pg_catalog, information_schema, sys, or dbo.

      USER indicates that the function should return information about user-defined tables.

      ALL specifies that the function should return information about all tables.

The following example demonstrates the statio_tables_rpt() function:

edb=# SELECT * FROM statio_tables_rpt(9, 10, 10, 'SYS');

                               statio_tables_rpt                                                            -----------------------------------------------------------------------------
   DATA from pg_statio_all_tables
 
 SCHEMA      RELATION             HEAP     HEAP     HEAP     IDX      IDX
                                  READ     HIT      ICACHE   READ     HIT     
                                                    HIT                   

             IDX      TOAST    TOAST    TOAST    TIDX     TIDX    TIDX    
             ICACHE   READ     HIT      ICACHE   READ     HIT     ICACHE  
             HIT                      HIT                        HIT   
-----------------------------------------------------------------------------
 public      pgbench_accounts     92766    67215    288      59       32126 
             9        0        0        0        0        0        0       
 pg_catalog  pg_class             0        296      0        3        16  
             0        0        0        0        0        0        0       
 sys         edb$stat_all_indexes 8        125      0        4        233  
             0        0        0        0        0        0        0       
 sys         edb$statio_all_index 8        125      0        4        233   
             0        0        0        0        0        0        0       
 sys         edb$stat_all_tables  6        91       0        2        174   
             0        0        0        0        0        0        0       
 sys         edb$statio_all_table 6        91       0        2        174   
             0        0        0        0        0        0        0       
 pg_catalog  pg_namespace         3        72       0        0        0     
             0        0        0        0        0        0        0       
 sys         edb$session_wait_his 1        24       0        4        47    
             0        0        0        0        0        0        0       
 pg_catalog  pg_opclass           3        13       0        2        0      
             0        0        0        0        0        0        0       
 pg_catalog  pg_trigger           0        12       0        1        15     
             0        0        0        0        0        0        0       
(16 rows)

11.6.5 stat_indexes_rpt()

The signature is:

      stat_indexes_rpt(beginning_id, ending_id, top_n, scope)

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

scope

scope determines which tables the function returns statistics about. Specify SYS, USER or ALL:

      SYS indicates that the function should return information about system defined tables. A table is considered a system table if it is stored in one of the following schemas: pg_catalog, information_schema, sys, or dbo.

      USER indicates that the function should return information about user-defined tables.

      ALL specifies that the function should return information about all tables.

The following code sample demonstrates the stat_indexes_rpt() function:

edb=# SELECT * FROM stat_indexes_rpt(9, 10, 10, 'ALL');
                                                      
                            stat_indexes_rpt                                                      
-----------------------------------------------------------------------------
   DATA from pg_stat_all_indexes
 
 SCHEMA        RELATION        INDEX                               
                          IDX SCAN    IDX TUP READ    IDX TUP FETCH  
-----------------------------------------------------------------------------
 pg_catalog    pg_cast         pg_cast_source_target_index         
                          30          7               7              
 pg_catalog    pg_class        pg_class_oid_index                  
                          15          15              15             
 pg_catalog    pg_trigger      pg_trigger_tgrelid_tgname_index     
                          12          12              12             
 pg_catalog    pg_attribute    pg_attribute_relid_attnum_index      
                          7           31              31             
 pg_catalog    pg_statistic    pg_statistic_relid_att_index        
                          7           0               0              
 pg_catalog    pg_database     pg_database_oid_index               
                          5           5               5              
 pg_catalog    pg_proc         pg_proc_oid_index             
                          5           5               5              
 pg_catalog    pg_operator     pg_operator_oprname_l_r_n_index     
                          3           1               1              
 pg_catalog    pg_type         pg_type_typname_nsp_index        
                          3           1               1              
 pg_catalog    pg_amop         pg_amop_opr_fam_index        
                          2           3               3              
(14 rows)

11.6.6 statio_indexes_rpt()

The signature is:

      statio_indexes_rpt(beginning_id, ending_id, top_n, scope)

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

scope

scope determines which tables the function returns statistics about. Specify SYS, USER or ALL:

      SYS indicates that the function should return information about system defined tables. A table is considered a system table if it is stored in one of the following schemas: pg_catalog, information_schema, sys, or dbo.

      USER indicates that the function should return information about user-defined tables.

      ALL specifies that the function should return information about all tables.

The following example demonstrates the statio_indexes_rpt() function:

edb=# SELECT * FROM statio_indexes_rpt(9, 10, 10, 'SYS');

                            statio_indexes_rpt                                                            
-----------------------------------------------------------------------------
   DATA from pg_statio_all_indexes
 
 SCHEMA      RELATION        INDEX                               
                        IDX BLKS READ   IDX BLKS HIT    IDX BLKS ICACHE HIT 
-----------------------------------------------------------------------------
public               pgbench_accounts          pgbench_accounts_pkey         
                        59              32126           9                   
 sys                  edb$stat_all_indexes      edb$stat_idx_pk          
                        4               233             0                   
 sys                  edb$statio_all_indexes    edb$statio_idx_pk         
                        4               233             0                   
 sys                  edb$stat_all_tables       edb$stat_tab_pk            
                        2               174             0                   
 sys                  edb$statio_all_tables     edb$statio_tab_pk           
                        2               174             0                   
 sys                  edb$session_wait_history  session_waits_hist_pk     
                        4               47              0                   
 pg_catalog           pg_cast                   pg_cast_source_target_index 
                        1               29              0                   
 pg_catalog           pg_trigger                pg_trig_tgrelid_tgname_index 
                        1               15              0                   
 pg_catalog           pg_class                  pg_class_oid_index        
                        1               14              0                   
 pg_catalog           pg_statistic              pg_statistic_relid_att_index 
                        2               12              0                   
(14 rows)

Previous PageTable Of ContentsNext Page