1z1-084 Exam Training Practice Tests (Oracle Database 19c Performance and Tuning Management)]

Question 1

Which two actions can cause invalidation or loss of one or more results in the SQL Query Result Cache?

A. A request was made by the RCBG background process in a physical standby database that is opened read only and whose primary has a result cache. B. Decreasing the value set for RESULT_CACHE_REMOTE_EXPIRATION. C. One or more results were aged out of the result cache. D. A request was made by the RCBG background of a non-RAC database. E. result_cache_max_size is set dynamically to 0.

Correct Answer: C,E

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Question 2

You want to reduce the amount of db file scattered read that is generated in the database. You execute the SQL Tuning Advisor against the relevant workload. Which two can be part of the expected result?

A. recommendations regarding the creation of additional indexes B. recommendations regarding the creation of materialized views C. recommendations regarding rewriting the SQL statements D. recommendations regarding partitioning the tables E. recommendations regarding the creation of SQL Patches

Correct Answer: A,C

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Question 3

Examine this statement and its corresponding execution plan:

Which phase introduces the CONCATENATION step?

A. SQL Semantic Check B. SQL Adaptive Execution C. SQL Transformation D. SQL Execution E. SQL Row Source Generation

Correct Answer: C

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Question 4

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?

A. Use a SQL Profile to enforce the appropriate plan. B. Activate the adaptive plans. C. Force the subquery to use dynamic sampling. D. Generate frequency histograms on the CITY__ID column. E. Create an index on the CITY IP column.

Correct Answer: D,E

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Question 5

Buffer cache access is too frequent when querying the SALES table. Examine this command which executes successfully:
ALTER TABLE SALES SHRINK SPACE;
For which access method does query performance on sales improve?

A. db file sequential read B. db file scattered read C. index full scan D. index range scan

Correct Answer: A

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Question 6

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. The performance of workloads that primarily generate full table scans and fast full index scans are always affected by the cache hit ratio. B. A 99% cache hit ratio can be observed for database instances which have very poor I/O performance. C. Both the RECYCLE and KEEP buffer caches should always have a very high cache hit ratio. D. A 60% cache hit ratio can be observed for database instances which have very good I/O performance. E. The buffer cache advisory view v$db_cache_advice provides advice on cache hit ratios appropriate for the instance workload.

Correct Answer: B,E

Oracle Database 19c Performance and Tuning Management - 1z1-084 Exam Practice Test