Sample Questions of OSS-DB Exam Gold "Monitoring Performance"

[Monitoring Performance - Statistics for tables and columns]

2.11

Choose two correct statements regarding table/column statistics.

  1. pg_class handles information on views and indexes as well as tables.

  2. pg_class always contains the latest information.

  3. "reltuples", a column in pg_class, stores the estimated number of rows in the table.

  4. pg_stats is a table that displays column statistics.

  5. Because some of the information accessible through pg_stats includes actual data, it cannot be accessed by regular users.


[Monitoring Performance - Access statistics]

2.10

Select all the INCORRECT statements regarding pg_stat_database.

  1. Only one row of operational statistics for the entire database cluster is stored.

  2. "blks_hit" stores the number of blocks retrieved from the buffer cache (cache hits).

  3. "blks_read" is the sum of the number of blocks read from disk and the number of blocks read from the buffer cache.

  4. By default, the "track_counts" parameter is "off", so no activity statistics are collected.

  5. "tup_fetched" contains the number of rows fetched using index scans.


[Monitoring Performance - Access statistics]

2.09

Select all the statements that are correct regarding the standard statistics views.

  1. From pg_stat_all_tables, the number of disk blocks read from the TOAST table can be obtained.

  2. From pg_stat_activity, the start time of the current query execution can be obtained.

  3. From pg_stat_database, the number of errors that have occurred in the target database can be obtained.

  4. From pg_statio_all_tables, the number of buffer hits for the target table can be obtained.


[Monitoring Performance - Access statistics]

2.08

Select all the statements that are correct regarding the standard statistics views.

  1. From pg_stat_all_tables, the number of disk blocks read from the TOAST table can be obtained.

  2. From pg_stat_activity, the start time of the current query execution can be obtained.

  3. From pg_stat_database, the number of errors that have occurred in the target database can be obtained.

  4. From pg_statio_all_tables, the number of buffer hits for the target table can be obtained.


[Monitoring Performance - Execution plans]

2.07

The EXPLAIN command can be used to display the execution plan of a query.
Select all the SQL commands that are correct as the target of the EXPLAIN command.

  1. DELETE

  2. DROP TABLE

  3. REINDEX

  4. PREPARE

  5. EXECUTE


[Monitoring Performance - Execution plans]

2.06

Select all the formats that are INCORRECT as valid output options for the EXPLAIN command.

  1. JSON

  2. HTML

  3. CSV

  4. YAML

  5. XML


[Monitoring Performance - Access statistics]

2.05

Select all the objects that can be targets of locks, which can be confirmed using the pg_locks view.

  1. Database

  2. Relation

  3. Tuple

  4. Column

  5. Parameter


[Monitoring Performance - Access statistics]

2.04

Select the correct SQL statement to display on table access statistics.

  1. SELECT * FROM pg_stat_activity;

  2. SELECT * FROM pg_stat_database;

  3. SELECT * FROM pg_stat_bgwriter;

  4. SELECT * FROM pg_stat_all_tables;

  5. SELECT * FROM pg_stat_all_indexes;


[Monitoring Performance - Access statistics]

2.03

Choose two correct statements regarding pg_class.

  1. The relpages column stores the number of rows in the table.

  2. pg_class stores only information about tables.

  3. The latest information is always stored in pg_class.

  4. The value in the relpages column is an estimate.

  5. Statistics stored in pg_class are updated by certain DDL commands.


[Monitoring Performance - Other performance monitoring]

2.02

Which of the following statements is a correct description of pg_stat_statements?

  1. It records execution time spent for SQL statements that exceed a specified threshold.

  2. It identifies transactions and target tables currently waiting for locks.

  3. It records execution statistics for all executed SQL statements.

  4. It displays overall database statistics, with one row per database.

  5. It displays table access statistics, with one row per table in the current database.


[Monitoring Performance - Execution plans]

2.01

Select three correct statements regarding the following EXPLAIN output.

postgres=# EXPLAIN SELECT relname,nspname FROM pg_class left join pg_namespace ON (pg_class.relnamespace = pg_namespace.oid);

                               QUERY PLAN

-------------------------------------------------------------------------

 Hash Left Join (cost=1.14..15.97 rows=288 width=128)

   Hash Cond: (pg_class.relnamespace = pg_namespace.oid)

   -> Seq Scan on pg_class (cost=0.00..10.88 rows=288 width=68)

   -> Hash (cost=1.06..1.06 rows=6 width=68)

         -> Seq Scan on pg_namespace (cost=0.00..1.06 rows=6 width=68)

(5 rows)

  1. Executing this EXPLAIN command actually executes the SQL statement specified in the argument.

  2. The descriptions "Hash Left Join", "Seq Scan on pg_class", and "Seq Scan on pg_namespace" in the plan nodes represent the optimal data processing algorithms chosen automatically by the query planner.

  3. The "cost=..." portion represents the actual time required for processing.

  4. The "rows=..." portion represents the estimated number of rows that each plan node will process.

  5. The "width=..." portion represents the estimated average width (in bytes) of a row based on statistics.