Which three types of statistics are captured by statspack with snap level 6?
Statspack is a performance diagnostic tool provided by Oracle prior to the introduction of the Automatic Workload Repository (AWR). At snap level 6, Statspack captures the following types of statistics:
A (Correct): Parent and child latches are captured. Latch statistics provide information about contention for latches, which are low-level serialization mechanisms used by Oracle.
E (Correct): Enqueue statistics, which provide information on the waits for locks that manage the concurrency between users.
F (Correct): Segment-level statistics, which provide detailed information on database segments such as tables, indexes, etc., to identify I/O and contention issues.
C (Incorrect): While optimizer execution plans are an essential aspect of performance tuning, detailed execution plan capture is not part of the Statspack report at level 6.
D (Incorrect): Plan usage data refers to how frequently a plan is being used, which is more associated with AWR and not typically captured in Statspack reports.
Which two statements are true about the use and monitoring of Buffer Cache Hit ratios and their value in tuning Database I/O performance?
A high buffer cache hit ratio typically indicates that the database is effectively using the buffer cache and does not often need to read data from disk. However, this metric alone is not a reliable indicator of the I/O performance of the database for several reasons:
Full table scans and fast full index scans (A) can bypass the buffer cache by design if the blocks are not deemed reusable shortly, which can impact the cache hit ratio.
A high cache hit ratio (B) can be misleading if the database performance is poor due to other factors, such as inefficient queries or contention issues.
The buffer cache advisory (C) is a more valuable tool for understanding the potential impact of different cache sizes on the database's I/O performance. It simulates scenarios with different cache sizes and provides a more targeted recommendation.
The RECYCLE and KEEP buffer caches (D) are specialized caches designed for certain scenarios. While high hit ratios can be beneficial, they are not universally required; some workloads might not be significantly impacted by lower hit ratios in these caches.
A lower cache hit ratio (E) does not necessarily mean poor I/O performance. In some cases, a system with a well-designed storage subsystem and efficient queries might perform well even with a lower cache hit ratio.
Reference
Oracle Database 19c Performance Tuning Guide - Buffer Cache Hit Ratio
Oracle Database 19c Performance Tuning Guide - v$db_cache_advice
Database performance degraded between 23:15 and 23:30 for the last three nights. The awr snapshot interval is one hour. The AODM report contains nothing about this performance problem.
With which tool can you further analyze this problem?
The Active Session History (ASH) report is a tool that provides detailed information about active sessions for the time period specified. Since the AWR snapshot interval is one hour and does not capture the granularity needed for this issue, ASH reports are more suitable as they contain more granular data for sessions that were active during the period of interest.
Oracle Database Performance Tuning Guide, 19c
Which procedure gathers statistics that are always used in the generation of any execution plan?
The DBMS_STATS.GATHER_DATABASE_STATS procedure is used to gather statistics for all schema objects in the database that do not have up-to-date statistics. These statistics are essential for the optimizer to make informed decisions about the most efficient way to execute a query. The procedure collects statistics such as table and column statistics, index statistics, and system statistics, which are all used in the execution plan generation.
Oracle Database PL/SQL Packages and Types Reference, 19c
Oracle Database Performance Tuning Guide, 19c
A database instance is suffering poor I/O performance on two frequently accessed large tables.
No Big Table caching occurs in the database.
Examine these parameter settings:
Which are two actions either one of which will allow Big Table caching to occur?
Big Table caching is a feature that allows frequently accessed large tables to be cached in memory to improve I/O performance. From the parameter settings provided, Big Table caching is not occurring because DB_BIG_TABLE_CACHE_PERCENT_TARGET is set to 10, which is the minimum threshold for enabling the feature, but the size of the cache is too small for the big tables to be effectively cached.
To enable Big Table caching, one of the following actions could be taken:
C (Correct): Increasing DB_BIG_TABLE_CACHE_PERCENT_TARGET to at least 25. This action would allocate a larger percentage of the buffer cache for storing big tables, which could allow for caching large tables and thus improve I/O performance.
D (Correct): Increasing DB_CACHE_SIZE to 1G. Since the size of the buffer cache is a determining factor for how much data can be cached, increasing this parameter would provide more memory space for big tables to be cached.
Options A, B, E, and F will not enable Big Table caching because:
A: Increasing DB_BIG_TABLE_CACHE_PERCENT_TARGET to 50 without adjusting the overall size of the cache might still not be sufficient if the DB_CACHE_SIZE is not large enough to hold the big tables.
B: Setting DB_KEEP_CACHE_SIZE to at least 50M only specifies a separate buffer pool for objects with the KEEP cache attribute and does not affect Big Table caching.
E: and F: Changing the PARALLEL_DEGREE_POLICY to ADAPTIVE or AUTO influences the behavior of parallel execution but does not directly enable or influence Big Table caching.
Oracle Database Performance Tuning Guide: Big Table Caching
Oracle Database Reference: DB_BIG_TABLE_CACHE_PERCENT_TARGET
