Oracle Exam 1Z0-084 Topic 1 Question 4 Discussion

Actual exam question for Oracle's 1Z0-084 exam

Question #: 4
Topic #: 1

[All 1Z0-084 Questions]

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?

AGenerate frequency histograms on the CITY__ID column.

BCreate an index on the CITY IP column.

CUse a SQL Profile to enforce the appropriate plan.

DForce the subquery to use dynamic sampling.

EActivate the adaptive plans.

Show Suggested Answer

Suggested Answer: A, B

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

by Loreta at Apr 13, 2024, 08:08 PM

Limited Time Offer

25%

8 months ago

Ah, I see what you guys are saying. This is a classic case of the 'index or histogram' dilemma. But you know what they say, 'when in doubt, index it out!' *laughs*

upvoted 0 times

Erinn

7 months ago

Yeah, that should definitely improve the performance.

upvoted 0 times

...

Kyoko

7 months ago

Let's create an index on the CITY_ID column.

upvoted 0 times

...

Lashaun

8 months ago

I think the first thing we need to do is understand the data distribution. The GROUP BY query shows that the CITY_ID column has a relatively small number of distinct values, so generating a frequency histogram could be a good option.

upvoted 0 times

...

Adria

8 months ago

Hmm, this question seems a bit tricky. We have a 19c database with default optimizer settings, and a subquery that is used extensively in the application. The performance of these queries is often poor, and there is no index on the CITY_ID column.

upvoted 0 times

...