Database performance has degraded recently.
index range scan operations on index ix_sales_time_id are slower due to an increase in buffer gets on sales table blocks.
Examine these attributes displayed by querying DBA_TABLES:
Now, examine these attributes displayed by querying DBA_INDEXES:
Which action will reduce the excessive buffer gets?
Given that index range scan operations on IX_SALES_TIME_ID are slower due to an increase in buffer gets, the aim is to improve the efficiency of the index access. In this scenario:
B (Correct): Re-creating the index using ADVANCED COMPRESSION can reduce the size of the index, which can lead to fewer physical reads (reduced I/O) and buffer gets when the index is accessed, as more of the index can fit into memory.
The other options would not be appropriate because:
A (Incorrect): Re-creating the SALES table sorted in order of the index might not address the issue of excessive buffer gets. Sorting the table would not improve the efficiency of the index itself.
C (Incorrect): Using the columns in IX_SALES_TIME_ID as a hash partitioning key for the SALES table is more relevant to data distribution and does not necessarily improve index scan performance.
D (Incorrect): Hash partitioning the index is generally used to improve the scan performance in a parallel query environment, but it may not reduce the number of buffer gets in a single-threaded query environment.
Accessing the SALES tables causes excessive db file sequential read wait events.
Examine this AWR except:
Now, examine these attributes displayed by querying dba_tables:
Finally, examine these parameter settings:
Which two must both be used to reduce these excessive waits?
The AWR excerpt points to excessive physical reads on the SALES table and index, suggesting the need for optimizing table storage and access.
Partitioning the SALES table (A) can reduce 'db file sequential read' waits by breaking down the large SALES table into smaller, more manageable pieces. This can localize the data and reduce the I/O necessary for query operations.
Compressing the SALES table (D) can also help reduce I/O by minimizing the amount of data that needs to be read from disk. This can also improve cache utilization and reduce the 'db file sequential read' waits.
Oracle Database VLDB and Partitioning Guide, 19c
Oracle Database Administrator's Guide, 19c
These changes are recommended based on Oracle's best practices for managing large tables and reducing I/O waits, ensuring better performance and efficiency.
A Standard Edition production database has performance problems for two hours on the same day each week.
Which tool must you use to diagnose the problem?
For a Standard Edition production database, the Statspack tool is available to diagnose performance problems. The Automatic Workload Repository (AWR) and its related tools like AWR Compare Periods report and SQL Performance Analyzer are features of the Oracle Database Enterprise Edition and are not available in Standard Edition. Database Replay is also a feature of the Enterprise Edition. Statspack is a performance diagnostic tool provided for earlier versions and Standard Editions of the Oracle Database to collect, store, and analyze performance data.
Reference
Oracle Database 19c Administrator's Guide - Using Statspack to Diagnose Database Performance Issues
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 three statements are true about using the in Memory (IM) column store?
The Oracle In-Memory (IM) column store feature enhances the performance of databases by providing a fast columnar storage format for analytical workloads while also potentially benefiting OLTP workloads.
C (True): It can improve OLTP workload performance by providing a faster access path for full table scans and reducing the need for indexes in certain scenarios, as the In-Memory store allows for efficient in-memory scans.
E (True): The In-Memory column store does not require all database data to fit in memory. It can be used selectively for performance-critical tables or partitions, and Oracle Database will manage the population and eviction of data as needed.
F (True): In-Memory column store can significantly improve performance for queries joining several tables, especially when bloom filters are used, as they are highly efficient with the columnar format for large scans and join processing.
The other options provided are not correct in the context of the In-Memory column store:
A (False): While In-Memory column store is designed for analytical queries rather than caching results of function evaluations, it does not specifically avoid improving performance for queries using cached results of function evaluations.
B (False): In-Memory column store can improve the performance of queries that use join groups, which can be used to optimize joins on columns from different tables.
D (False): In-Memory column store can improve the performance of queries using expressions, including user-defined virtual columns, because it supports expression statistics which help in optimizing such queries.
Oracle Database In-Memory Guide: In-Memory Column Store in Oracle Database
