5.3 Simulating Statspack AWR Reports

Table of Contents Previous Next


5 Dynamic Runtime Instrumentation Tools Architecture (DRITA) : 5.3 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.
The edbreport() function includes data from the other reporting functions, plus additional system information. The signature is:
beginning_id is an integer value that represents the beginning session identifier.
ending_id is an integer value that represents the ending session identifier.
The call to the edbreport() function returns a composite report that contains system information and the reports returned by the other statspack functions. :
The information displayed in the Top 10 Relations by pages section includes:
The information displayed in the Top 10 Indexes by pages section includes:
The information displayed in the Top 10 Relations by DML section includes:
The information displayed in the DATA from pg_stat_database section of the report includes:
The information displayed in the DATA from pg_buffercache section of the report includes:
Note: In order to obtain the report for DATA from pg_buffercache, the pg_buffercache module must have been installed in the database. Perform the installation with the CREATE EXTENSION command.
For more information on the CREATE EXTENSION command please see the PostgreSQL Core documentation at:
The information displayed in the DATA from pg_stat_all_tables ordered by seq scan section includes:
The information displayed in the DATA from pg_stat_all_tables ordered by rel tup read section includes:
The information displayed in the Data from pg_statio_all_tables section includes:
The information displayed in the DATA from pg_stat_all_indexes section includes:
The information displayed in the DATA from pg_statio_all_indexes section includes:
The information displayed in the System Wait Information section includes:
The information displayed in the Database Parameters from postgresql.conf section includes:
stat_db_rpt(beginning_id, ending_id)
beginning_id is an integer value that represents the beginning session identifier.
ending_id is an integer value that represents the ending session identifier.
The information displayed in the DATA from pg_stat_database section of the report includes:
function_name(beginning_id, ending_id, top_n, scope)
beginning_id is an integer value that represents the beginning session identifier.
ending_id is an integer value that represents the ending session identifier.
top_n represents the number of rows to return
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 stat_tables_rpt() function returns a two-part report. The first portion of the report contains:
The information displayed in the DATA from pg_stat_all_tables ordered by seq scan section includes:
The information displayed in the DATA from pg_stat_all_tables ordered by rel tup read section includes:
statio_tables_rpt(beginning_id, ending_id, top_n, scope)
beginning_id is an integer value that represents the beginning session identifier.
ending_id is an integer value that represents the ending session identifier.
top_n represents the number of rows to return
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 statio_tables_rpt() function returns a report that contains:
The information displayed in the Data from pg_statio_all_tables section includes:
stat_indexes_rpt(beginning_id, ending_id, top_n, scope)
beginning_id is an integer value that represents the beginning session identifier.
ending_id is an integer value that represents the ending session identifier.
top_n represents the number of rows to return
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 stat_indexes_rpt() function returns a report that contains:
The information displayed in the DATA from pg_stat_all_indexes section includes:
statio_indexes_rpt(beginning_id, ending_id, top_n, scope)
beginning_id is an integer value that represents the beginning session identifier.
ending_id is an integer value that represents the ending session identifier.
top_n represents the number of rows to return
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 statio_indexes_rpt()function returns a report that contains:
The information displayed in the DATA from pg_statio_all_indexes report includes:

5 Dynamic Runtime Instrumentation Tools Architecture (DRITA) : 5.3 Simulating Statspack AWR Reports

Table of Contents Previous Next