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?
Show Answer
Hide Answer
Correct Answer:
B
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.
Oracle Database SQL Tuning Guide: Managing Indexes
Oracle Database SQL Tuning Guide: Index Compression
