Free Preparation Discussions
MENU
Oracle 1Z0-084 Exam - Topic 1 Question 4 Discussion
Topic #: 1
You manage a 19c database with default optimizer settings.
This statement is used extensively as subquery in the application queries:
SELECT city_id FROM sh2.sales WHERE city_id=:Bl
You notice the performance of these queries is often poor and, therefore, execute:
SELECT city_id,COUNT(*) FROM sh2.sales GROUP BY city_id;
Examine the results:
There is no index on the CITY_ID column.
Which two options improve the performance?
In this scenario, creating an index and generating frequency histograms are two methods that can potentially improve performance:
A (Correct): Generating frequency histograms on the CITY_ID column can help the optimizer make better decisions regarding the execution plan, especially if the data distribution is skewed. Histograms provide the optimizer with more detailed information about the data distribution in a column, which is particularly useful for columns with non-uniform distributions.
B (Correct): Creating an index on the CITY_ID column would speed up queries that filter on this column, especially if it's used frequently in the WHERE clause as a filter. An index would allow for an index range scan instead of a full table scan, reducing the I/O and time needed to execute such queries.
C (Incorrect): While SQL profiles can be used to improve the performance of specific SQL statements, they are usually not the first choice for such a problem, and creating a profile does not replace the need for proper indexing or statistics.
D (Incorrect): Forcing the subquery to use dynamic sampling might not provide a consistent performance benefit, especially if the table statistics are not representative or are outdated. However, dynamic sampling is not as effective as having accurate statistics and a well-chosen index.
E (Incorrect): Adaptive plans can adjust the execution strategy based on the conditions at runtime. While they can be useful in certain scenarios, in this case, creating an index and ensuring accurate statistics would likely provide a more significant performance improvement.
Oracle Database SQL Tuning Guide: Managing Optimizer Statistics
Oracle Database SQL Tuning Guide: Using Indexes and Clusters
Francoise
4 months agoMeaghan
4 months agoRhea
4 months agoJoni
5 months agoFrankie
5 months agoDortha
5 months agoClare
5 months agoSteffanie
5 months agoEmiko
6 months agoJospeh
6 months agoLoren
6 months agoMariko
6 months agoCarylon
6 months agoFrederica
6 months agoDenna
6 months agoAshley
6 months agoVerlene
6 months agoGlory
6 months agoValda
6 months agoAzalee
2 years agoDominga
2 years agoVeronika
2 years agoGracia
2 years agoAzalee
2 years agoVeronika
2 years agoDomingo
2 years agoLisha
2 years agoSage
2 years agoDomingo
2 years agoDorothea
2 years agoErinn
2 years agoKyoko
2 years agoLashaun
2 years agoAdria
2 years ago