From the objective of OSS-DB Exam Gold
- Monitoring Performance - Access statistics
Sample Question
1.04
Select the correct SQL statement to display on table access statistics.
-
SELECT * FROM pg_stat_activity;
-
SELECT * FROM pg_stat_database;
-
SELECT * FROM pg_stat_bgwriter;
-
SELECT * FROM pg_stat_all_tables;
-
SELECT * FROM pg_stat_all_indexes;
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2025/05/30
Answer and Explanation
The correct answer is "D. SELECT * FROM pg_stat_all_tables;".
pg_stat_all_tables is a view that provides per-table statistics. It displays information such as the number of sequential scans and the number of valid rows retrieved by those scans.
testdb=# SELECT schemaname, relname, seq_scan, seq_tup_read FROM pg_stat_all_tables where relname = 'tbl01';
-[ RECORD 1 ]+----------------
schemaname | public
relname | tbl01
seq_scan | 3112
seq_tup_read | 4629
This example shows the number of sequential scans (seq_scan) and the number of valid rows read during sequential scans (seq_tup_read). Other statistics that can be displayed include the number of index scans, the number of valid rows read during index scans, the number of data insertions, the number of data updates, and the number of data deletions.
Each option is explained below.
A. SELECT * FROM pg_stat_activity;.
Incorrect.
pg_stat_activity is a view that displays information about the activity of each server process.
B. SELECT * FROM pg_stat_database;
Incorrect.
pg_stat_database is a view that displays database-wide statistics on a per-database basis.
C. SELECT * FROM pg_stat_bgwriter;
Incorrect.
pg_stat_bgwriter is a view that displays information about the background writer process.
D. SELECT * FROM pg_stat_all_tables;
Correct.
E. SELECT * FROM pg_stat_all_indexes;
Incorrect.
pg_stat_all_indexes is a view that displays statistics about index access for each index in the database.