From the objective of OSS-DB Exam Gold
- Monitoring Performance - Access statistics
Sample Question
1.11
Choose two accurate statements related to tatistics collection views: pg_stat_database and pg_stat_all_tables.
-
Data is collected when the ANALYZE command is executed.
-
Data is collected periodically by the stats collector process.
-
These views collect the state of the data within tables, such as row counts, maximum/minimum values, and data distribution.
-
The statistics in these views are used by the planner to create optimal execution plans for SQL queries.
-
They are used to investigate overall system throughput and identify performance problems.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2025/05/30
Answer and Explanation
Databases provide various mechanisms for collecting statistical information. These statistics are used to maintain and improve performance, and to identify performance issues.
Access statistics (also known as execution statistics) are collected by the stats collector process every 500 milliseconds by default and can be viewed through various statistics views.
For example, pg_stat_database provides information such as the number of transactions committed and rolled back, the number of rows retrieved, inserted, updated, and deleted, etc. This information can be used to analyze overall system performance and identify potential bottlenecks.
The information used by the planner to create optimal SQL execution plans is also considered statistical information, but it differs from access statistics described above. Specifically, static information such as the number of rows in a table, minimum and maximum values for columns, and data distribution are collected when the ANALYZE command is executed and stored in system catalog tables like pg_class and pg_statistic.
Therefore, the correct answers are B and E.