Unlock Oracle Database 19c Mastery: Your 1Z0-084 Success Blueprint

Correct : E

To configure and enable Database Smart Flash Cache, you must set the DB_FLASH_CACHE_SIZE parameter to reflect the combined size of the flash devices you intend to use for the cache. In this scenario, two flash devices are configured: /dev/sdj with 128G and /dev/sdk with 64G.

Determine the combined size of the flash devices intended for the Database Smart Flash Cache. In this case, it's 128G + 64G = 192G.

However, Oracle documentation suggests setting DB_FLASH_CACHE_SIZE to the exact sizes of the individual devices, separated by a comma when multiple devices are used.

Modify the parameter in the database initialization file (init.ora or spfile.ora) or using an ALTER SYSTEM command. Here's the command for altering the system setting:

ALTER SYSTEM SET DB_FLASH_CACHE_SIZE='128G,64G' SCOPE=SPFILE;

Since this is a static parameter, a database restart is required for the changes to take effect.

Upon database startup, it will allocate the Database Smart Flash Cache using the provided sizes for the specified devices.

It is important to note that MEMORY_TARGET and MEMORY_MAX_TARGET parameters should be configured independently of DB_FLASH_CACHE_SIZE. They control the Oracle memory management for the SGA and PGA, and do not directly correlate with the flash cache configuration.

Reference

Oracle Database 19c Documentation on Database Smart Flash Cache

Oracle Support Articles and Community Discussions on DB_FLASH_CACHE_SIZE Configuration

Correct : B

To produce a consolidated formatted trace file from multiple trace files generated by all clients for a single service, the combination of trcsess and TKPROF utilities is used. The trcsess utility consolidates trace files based on specified criteria such as session, client identifier, or service name. This results in a single trace file that combines the desired tracing information. Next, TKPROF is used to format the output of the trace file generated by trcsess, providing a readable summary of the trace, including execution counts, execution times, and SQL statement text along with execution plans.

Steps:

Use trcsess to combine trace files:

Command: trcsess output=consolidated.trc service=your_service_name *.trc

Use TKPROF to format the consolidated trace file:

Command: tkprof consolidated.trc output.txt explain=user/password sys=no sort=prsela,fchela

Oracle Database Performance Tuning Guide, 19c

Oracle Database Utilities, 19c

Correct : A, D

A) When Cursor_sharing is set to FORCE, Oracle tries to avoid hard parses by replacing literals in SQL statements with bind variables, even if the original statement didn't include bind variables. This can lead to the use of a single execution plan for multiple executions of a statement with different literal values, which might not be optimal for all executions.

D) Setting cursor_sharing to EXACT ensures that SQL statements must match exactly for them to share a cursor. This setting prevents the use of Adaptive Cursor Sharing (ACS) since ACS relies on the ability to share cursors among similar statements that differ only in their literal values. With EXACT, there's no cursor sharing for statements with different literals, hence no opportunity for ACS to operate.

Oracle Database SQL Tuning Guide, 19c

Oracle Database Reference, 19c

Correct : C

In an adaptive execution plan, the Optimizer makes runtime decisions between nested loop and hash joins using a statistics collector. The collector is a row source that collects statistics about the rows it processes and can adapt the plan based on the number of rows processed.

Oracle Database SQL Tuning Guide, 19c

Correct : E

The optimizer_adaptive_statistics parameter, when set to TRUE, enables the optimizer to use adaptive statistics, such as SQL Plan Directives, to help improve plans by automatically adjusting them based on the actual execution statistics.

Oracle Database SQL Tuning Guide, 19c