Oracle Database 19c Performance and Tuning Management (1z1-084) Free Practice Test

Question 1

You must write a statement that returns the ten most recent sales. Examine this statement:

Users complain that the query executes too slowly. Examine the statement's current execution plan:

What must you do to reduce the execution time and why?

A. Collect a new set of statistics on PRODUCT, CUSTOMERS, and SALES because the current stats are inaccurate.

B. Enable Adaptive Plans so that Oracle can change the Join method as well as the Join order for this query.

C. Create an index on SALES.CUST_ID to force an INDEX RANGE SCAN on this index followed by a NESTED LOOP join between CUSTOMERS and SALES.

D. Create an index on SALES.TIME_ID to force the return of rows in the order specified by the ORDER BY clause.

E. Replace the FETCH FIRST clause with ROWNUM to enable the use of an index on SALES.

Correct Answer: D

Explanation: Only visible for TestSimulate 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 materialized views

B. recommendations regarding the creation of additional indexes

C. recommendations regarding partitioning the tables

D. recommendations regarding rewriting the SQL statements

E. recommendations regarding the creation of SQL Patches

Correct Answer: B,D

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

Question 3

Which statement is true about DB time in V$$YS_TIME_MODEL?

A. DB time can be many times greater than the elapsed time since the database instance started.

B. DB tine excludes the time spent waiting for a CPU in the operating system run queue.

C. DB time includes the time spent executing the RMAN backup and restore command.

D. DB time is organized as a simple list of statistics and any time period is attributable to only one statistic.

Correct Answer: A

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

Question 4

A database supporting a mixed workload is hosted on a server with 64 CPUs.
A large number of free buffer waits and buffer busy waits occur affecting performance.
The buffer cache size was then increased but after a few hours, the same wait events occur more often than before the change.
Examine these parameter settings:

Which two actions can help reduce the number of these waits7

A. increasing the value of DB_FILE_MULTIBLOCK_READ_COUNT to 128

B. increasing the value of DBWRITERPROCESSES to 64,

C. setting dbwr_io_slaves to 64

D. Increasing the size of MEMORYTARGET

E. reducing the values of DB_FILE_MULTILOCK_READ_COUNT to 64

Correct Answer: B,C

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

Question 5

Which three statements are true about using the in Memory (IM) column store?

A. It does not improve performance for queries using user-defined virtual column results.

B. It can improve OLTP workload performance by avoiding the use of indexes.

C. It does not improve performance for queries that use join groups on columns from different tables.

D. It improves performance for queries joining several tables using bloom filter joins.

E. It does not improve performance for queries using cached results of function evaluations on columns from the same table.

F. It does not require all database data to fit in memory to improve query performance.

Correct Answer: B,D,F

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

Question 6

You must produce a consolidated formatted trace file by combining all trace files generated by all clients for a single service.
Which combination of utilities does this?

A. TKPROF and Trace Analyzer

B. Autotrace and TKPROF

C. Trcsess and TKPROF

D. Trace Analyzer and Tracsess

Correct Answer: C

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

Welcome to TestSimulate

Oracle Database 19c Performance and Tuning Management (1z1-084) Free Practice Test