Oracle

1Z0-084
Oracle Database 19c: Performance Management and
Tuning
QUESTION & ANSWERS

https://www.examdumps.in/1Z0-084-pdf-questions.html
QUESTION: 1

Which of the following can be a cause of excessive contention for a particular resource in an Oracle
Database?

Option A : A large number of concurrent transactions attempting to access the resource

Option B : Inadequate system resources such as CPU, memory, or I/O bandwidth

Option C : A high volume of network traffic within the database

Option D : Insufficient database storage space

Correct Answer: A

Explanation/Reference:

Option 1: Correct. If a large number of concurrent transactions are attempting to access the same resource (such as a table or

index), it can cause excessive contention and slow down the overall performance of the database. Contention can occur when

multiple transactions are attempting to modify the same resource simultaneously. Option 2: Incorrect. While inadequate

system resources can certainly have an impact on database performance, it is not a specific cause of contention for a

particular resource in the database. Option 3: Incorrect. Network traffic within the database should not impact the performance

of a particular resource in the database, as this traffic is not directly related to the database activity itself (i.e. queries,

transactions, etc.). Option 4: Incorrect. Insufficient database storage space can certainly impact performance, but it is not a

specific cause of contention for a particular resource in the database.

QUESTION: 2

Which statement is true regarding the sizing and tuning of the shared pool and buffer cache in Oracle
Database 19c?

Option A : The shared pool and buffer cache should always be sized to use as much memory as possible
to improve performance.

Option B : The shared pool and buffer cache should always be sized conservatively to avoid excessive
memory usage.

Option C : The shared pool and buffer cache should be sized based on the estimated workload and
available memory.

Option D : The shared pool and buffer cache should only be tuned after performance issues occur.

Correct Answer: C

https://www.examdumps.in/1Z0-084-pdf-questions.html
Explanation/Reference:

Option 1: Incorrect. Sizing the shared pool and buffer cache to use as much memory as possible can lead to excessive memory

usage and performance problems. Option 2: Incorrect. Sizing the shared pool and buffer cache too conservatively can result in

inadequate memory allocations and performance problems. Option 3: Correct. The shared pool and buffer cache should be

sized based on the estimated workload and available memory to optimize performance. Option 4: Incorrect. Tuning the shared

pool and buffer cache after performance issues occur is reactive and may not fully address the underlying cause of the issues.

QUESTION: 3

Which statement(s) correctly define statistics in Oracle Database 19c?

Option A : Statistics are metadata about the objects in the database and can be used to help optimize
SQL execution plans.

Option B : Statistics are a set of customized queries that can be run to troubleshoot performance issues.

Option C :
Statistics are used exclusively by DBAs to monitor system metrics and resource consumption.

Option D : Statistics are a subset of the various types of indexes that can be created on tables.

Correct Answer: A

Explanation/Reference:

Option 1: This option is correct. Statistics are metadata about the objects in the database that provide critical information

about the storage and access patterns of those objects. They can be used by the query optimizer to help optimize SQL

execution plans. Option 2: This option is incorrect. While customized queries may be used to troubleshoot performance issues,

statistics are not a set of queries themselves. Option 3: This option is incorrect. While DBAs may use statistics to monitor

system metrics and resource consumption, they are not used exclusively by DBAs. Option 4: This option is incorrect. While

indexes may be used to store statistics, statistics are not a subset of the various types of indexes that can be created on

tables.

QUESTION: 4

Which of the following features can be used to manage Oracle Database memory?

Option A : Automatic Memory Management

https://www.examdumps.in/1Z0-084-pdf-questions.html
 Option B : Manual Memory Management

Option C : Automatic Storage Management

Option D : Automatic Undo Management

Correct Answer: A

Explanation/Reference:

Option 1: Correct. Oracle Database provides Automatic Memory Management feature that can dynamically manage the

memory components based on workload and system resources. Option 2: Incorrect. Oracle Database provides Automatic

Memory Management feature for managing memory. Manual Memory Management is not recommended as it requires manual

adjustments and can be error-prone. Option 3: Incorrect. Automatic Storage Management (ASM) is a feature for managing

database storage and is not directly related to memory management. Option 4: Incorrect. Automatic Undo Management is a

feature for managing undo data and is not directly related to memory management.

QUESTION: 5

Which of the following is a benefit of using the Automatic SQL Tuning feature in Oracle Database 19c?

Option A : It automatically identifies and tunes high-load SQL statements

Option B : It enables the creation of SQL profiles for better plan stability

Option C : It provides real-time SQL monitoring for optimizing query execution

Option D : It automatically enables the SQL plan directives feature

Correct Answer: A

Explanation/Reference:

Option 1: Correct answer: The Automatic SQL Tuning feature in Oracle Database 19c automatically identifies and tunes high-

load SQL statements, improving overall performance. Option 2: Incorrect answer: The Automatic SQL Tuning feature in Oracle

Database 19c does not enable the creation of SQL profiles for better plan stability. That is a separate feature. Option 3:

Incorrect answer: The Automatic SQL Tuning feature in Oracle Database 19c does not provide real-time SQL monitoring for

optimizing query execution. That is a separate feature. Option 4: Incorrect answer: The Automatic SQL Tuning feature in Oracle

Database 19c does not automatically enable the SQL plan directives feature. That is a separate feature.

https://www.examdumps.in/1Z0-084-pdf-questions.html
QUESTION: 6

What is the purpose of an index in a database?

Option A :
To store the data in a table

Option B : To provide a way to access data quickly

Option C : To enforce data integrity constraints

Option D : To perform complex calculations on data

Correct Answer: B

Explanation/Reference:

Option 1: Incorrect. An index does not store the data itself. It provides a way to access the data quickly. Option 2: Correct. The

purpose of an index is to provide a way to access data quickly. It improves query performance by reducing the amount of data

that needs to be scanned. Option 3: Incorrect. Enforcing data integrity constraints is the role of primary key and foreign key

constraints, not indexes. Option 4: Incorrect. Indexes do not perform calculations on data. They are used for accessing data

efficiently.

QUESTION: 7

Which statement accurately describes the purpose of the buffer cache in Oracle Database?

Option A : The buffer cache stores copies of data blocks read from disk.

Option B : The buffer cache stores the execution plans of SQL statements.

Option C : The buffer cache stores rollback segments for undo operations.

Option D : The buffer cache stores the control structures used by the database instance.

Correct Answer: A

Explanation/Reference:

Option 1: Correct. The buffer cache in Oracle Database is a portion of the main memory that stores copies of data blocks read

from disk. These data blocks are in memory to improve performance by reducing disk I/O. Option 2: Incorrect. The execution

https://www.examdumps.in/1Z0-084-pdf-questions.html
plans of SQL statements are not stored in the buffer cache. Execution plans are stored in the shared pool. Option 3: Incorrect.

Rollback segments for undo operations are not stored in the buffer cache. Rollback segments are stored in the undo

tablespace. Option 4: Incorrect. The control structures used by the database instance are not stored in the buffer cache.

Control structures such as the System Global Area (SG and Program Global Area (PG are stored in the System Global Area.

QUESTION: 8

You must configure and enable Database Smart Flash Cache for a database.

You configure these flash devices:

Examine these parameter settings:

What must be configured so that the database uses these devices for the Database Smart Flash Cache?

Option A : Set DB_FLASH_CACHE_SIZE to 192G and MEMORY_TARGET to 256G.

Option B : Set DB_FLASH_CACHE_SIZE parameter to 192G.

Option C : Disable Automatic Memory Management and set SGA_TARGET to 256G.

Option D : Set DB_FLASH_CACHE_SIZE to 256G and change device /dev/sdk to 128G.

Option E : Set DB_FLASH_CACHE_SIZE parameter to 128G, 64G.

Correct Answer: E

https://www.examdumps.in/1Z0-084-pdf-questions.html
Explanation/Reference:

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 youintend 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. References Oracle

Database 19c Documentation on Database Smart Flash Cache Oracle Support Articles and Community Discussions on

DB_FLASH_CACHE_SIZE Configuration

QUESTION: 9

Which of the following statements is true about the optimizer index skip scan feature in Oracle Database
19c?

Option A : The optimizer index skip scan feature allows the optimizer to skip over one or more leading
columns of an index in order to improve query performance.

Option B : The optimizer index skip scan feature allows the optimizer to skip over only the first column of
an index in order to improve query performance.

Option C : The optimizer index skip scan feature allows the optimizer to scan an index in reverse order,
starting from the last column instead of the first column.

Option D : The optimizer index skip scan feature is not available in Oracle Database 19c.

Correct Answer: A

Explanation/Reference:

Option 1: This option is correct because the optimizer index skip scan feature allows the optimizer to skip over one or more

leading columns of an index when they do not contribute to the WHERE clause of a query. This can improve query performance

as the number of rows returned by the query is reduced. Option 2: This option is incorrect because the optimizer index skip

scan feature allows the optimizer to skip over one or more leading columns of an index, not just the first column. Option 3: This

option is incorrect because the optimizer index skip scan feature does not allow the optimizer to scan an index in reverse

https://www.examdumps.in/1Z0-084-pdf-questions.html
order. The feature only allows the optimizer to skip over one or more leading columns of an index. Option 4: This option is

incorrect because the optimizer index skip scan feature is available in Oracle Database 19c.

QUESTION: 10

Which feature of Oracle Database 19c provides a mechanism for analyzing the impact on database
performance of real-life workloads, such as batch jobs or online transaction processing (OLTP)?

Option A :
Automatic Workload Repository (AWR)

Option B :
Real Application Testing (RAT)

Option C : Automatic Database Diagnostic Monitor (ADDM)

Option D : SQL Performance Analyzer (SPA)

Correct Answer: B

Explanation/Reference:

Option 1: The Automatic Workload Repository (AWR) is a collection of historical performance data used for performance tuning.

It does not provide a mechanism for analyzing the impact of real-life workloads. Option 2: Real Application Testing (RAT) is the

correct answer. RAT provides a mechanism for analyzing the impact on database performance of real-life workloads by

simulating these workloads in a controlled testing environment, allowing for performance evaluation and tuning before

deploying changes in a production environment. It includes two main components: Database Replay and SQL Performance

Analyzer. Option 3: The Automatic Database Diagnostic Monitor (ADDM) is a component of the Oracle Database that

automatically identifies, analyzes, and provides recommendations for resolving performance problems. While it provides

valuable diagnostic information, it does not specifically focus on analyzing the impact of real-life workloads. Option 4: The SQL

Performance Analyzer (SP is a component of Real Application Testing (RAT). It compares the performance of SQL statements

before and after a database change, such as an upgrade or a system configuration change. While it can be used to evaluate

the impact of changes on performance, it does not specifically focus on analyzing real-life workloads.

QUESTION: 11

Which statement about Undo and Redo Management is correct?

Option A : The UNDO_RETENTION parameter determines the minimum amount of time that undo data will

https://www.examdumps.in/1Z0-084-pdf-questions.html
 be retained in the undo tablespace.

Option B :
The MAXTRANS parameter determines the maximum number of transactions that can be concurrently
active on a given data block.

Option C : The UNDO_TABLESPACE parameter specifies the name of the default tablespace used for
storing undo data.

Option D : The LOG_BUFFER parameter determines the size of the redo log buffer in memory.

Correct Answer: A

Explanation/Reference:

Option 1: Correct: The UNDO_RETENTION parameter determines the minimum amount of time that undo data will be retained

in the undo tablespace. Option 2: Incorrect: The MAXTRANS parameter determines the maximum number of transactions that

can be concurrently active on a given data block. Option 3: Incorrect: The UNDO_TABLESPACE parameter specifies the name of

the default tablespace used for storing undo data. Option 4: Incorrect: The LOG_BUFFER parameter determines the size of the

redo log buffer in memory.

QUESTION: 12

In Oracle Database 19c, which of the following is true about statistics gathering?

Option A : Statistics can be gathered automatically only for tables and indexes.

Option B : Automatic statistics gathering and the use of dynamic sampling can only be performed for
queries that include at least one join.

Option C : If the database is operating in manual statistics mode, statistics can only be gathered by
executing the DBMS_STATS.GATHER_DATABASE_STATS procedure.

Option D : Statistics gathered on partitioned tables only reflect the statistics for the entire table and do
not include statistics for individual partitions.

Correct Answer: B

Explanation/Reference:

Option 1: This option is incorrect as statistics can also be gathered automatically for partitions, subpartitions, materialized

views, and columns used in function-based indexes. Option 2: This option is correct as automatic statistics gathering and the

https://www.examdumps.in/1Z0-084-pdf-questions.html
use of dynamic sampling require the presence of at least one join condition or an IN or EXISTS operator. Queries that only

include single-table access or a cross-product are not eligible for automatic statistics gathering or dynamic sampling. Option 3:

This option is incorrect as manual statistics gathering can also be performed by executing the ANALYZE command. The

DBMS_STATS package is the preferred method for statistics gathering as it provides additional functionality and control. Option

4: This option is incorrect as statistics gathered on partitioned tables include statistics for individual partitions. The

DBMS_STATS package provides the ability to gather statistics at the partition or subpartition level.

QUESTION: 13

Which tool can be used to monitor and troubleshoot performance issues in Oracle Database 19c?

Option A : Oracle Enterprise Manager

Option B : SQL Developer

Option C : Oracle SQL Monitor

Option D : Oracle Exadata

Correct Answer: A

Explanation/Reference:

Option 1: Correct. Oracle Enterprise Manager is a tool provided by Oracle for monitoring and managing Oracle Database. It

allows you to view and analyze performance metrics, identify bottlenecks, and troubleshoot performance issues. Option 2:

Incorrect. While SQL Developer is a popular tool for developing and debugging SQL queries, it does not provide extensive

performance monitoring and troubleshooting capabilities like Oracle Enterprise Manager. Option 3: Incorrect. Oracle SQL

Monitor is a feature of Oracle Database that allows you to monitor the execution of SQL statements and identify performance

issues. However, it is not a standalone tool like Oracle Enterprise Manager. Option 4: Incorrect. Oracle Exadata is a hardware

and software solution that is optimized for running Oracle Database. While it provides performance benefits, it is not a tool

specifically designed for monitoring and troubleshooting performance issues.

QUESTION: 14

Which statement regarding SQL plan baselines is true?

Option A : An accepted baseline becomes the only plan used for that SQL statement.

Option B : A fixed baseline cannot be changed or removed.

Option C : A SQL statement can have multiple plan baselines, only one of which can be accepted.

https://www.examdumps.in/1Z0-084-pdf-questions.html
 Option D : A plan baseline can only be created for a single SQL statement.

Correct Answer: C

Explanation/Reference:

Option 1: This option is incorrect as multiple plan baselines can exist for a single SQL statement. Option 2: This option is

incorrect as a fixed baseline can be removed or changed with the DBMS_SPM.UNPACK_STGTAB_CURSOR_CACHE procedure.

Option 3: This option is correct. Multiple plan baselines can be created for a single SQL statement, but only one can be

accepted as the current plan. Option 4: This option is incorrect as a plan baseline can be created for a group of related SQL

statements with the same execution plan.

QUESTION: 15

Which of the following is a best practice for index management and tuning in Oracle Database 19c?

Option A : Regularly rebuild all indexes to improve performance.

Option B : Use a higher PCTFREE value to reduce the frequency of index block splits.

Option C : Use index-organized tables (IOTs) for small lookup tables with few updates.

Option D : Create bitmap indexes on large tables with low cardinality columns.

Correct Answer: C

Explanation/Reference:

Option 1: Incorrect. Regularly rebuilding all indexes can be an expensive and unnecessary operation. It is not a best practice in

Oracle Database 19c. Option 2: Incorrect. Using a higher PCTFREE value can lead to wasted space and decreased performance.

It is not a best practice in Oracle Database 19c. Option 3: Correct. Index-organized tables (IOTs) are commonly used for small

lookup tables with few update operations because they provide improved performance and storage efficiency. Option 4:

Incorrect. Bitmap indexes are typically used for columns with high cardinality, not low cardinality. Using bitmap indexes on

large tables with low cardinality columns can result in inefficient use of space and reduced performance.

QUESTION: 16

Which statement regarding Automatic Workload Repository (AWR) is correct?

https://www.examdumps.in/1Z0-084-pdf-questions.html
 Option A : AWR data can only be accessed using the Oracle Enterprise Manager.

Option B : AWR automatically collects and stores performance data at regular intervals.

Option C : AWR data is stored in the system tablespace by default.

Option D : AWR data is kept in memory and is not written to disk.

Correct Answer: B

Explanation/Reference:

Option 1: This option is incorrect because AWR data can be accessed using SQL queries in addition to Oracle Enterprise

Manager. Option 2: This option is correct because AWR automatically collects and stores performance data at regular intervals,

typically every hour by default, and maintains a rolling window of data for the past 8 days. Option 3: This option is incorrect

because AWR data is stored in the SYSAUX tablespace by default, not the system tablespace. Option 4: This option is incorrect

because AWR data is written to disk and not kept in memory indefinitely.

QUESTION: 17

Which of the following is considered a best practice for performance management and tuning in Oracle
Database 19c?

Option A : Regularly gather optimizer statistics

Option B : Increase the size of the shared pool size

Option C : Disable Automatic Memory Management

Option D : Use a larger block size for all tablespaces

Correct Answer: A

Explanation/Reference:

Option 1: Correct: Regularly gathering optimizer statistics is a best practice for performance management and tuning in Oracle

Database 19c. It helps the optimizer accurately estimate the number of rows and select the most efficient execution plan for

queries. Option 2: Incorrect: Increasing the size of the shared pool size may not necessarily improve performance and can lead

to inefficient memory usage. It is important to regularly monitor and tune the shared pool, but increasing its size should be

based on a proper analysis of the application's memory requirements. Option 3: Incorrect: Automatic Memory Management is a

recommended feature in Oracle Database 19c that dynamically manages the memory allocations for different components,

such as the buffer cache, shared pool, and the large pool. Disabling it can lead to suboptimal memory configuration and

https://www.examdumps.in/1Z0-084-pdf-questions.html
inefficient memory usage. Option 4: Incorrect: Using a larger block size for all tablespaces is not a recommended practice in

Oracle Database 19c. The block size should be determined based on the type of workload and storage characteristics. Using a

larger block size can result in increased memory consumption and decreased performance for certain workloads.

QUESTION: 18

Which statement regarding SQL Plan Management (SPM) is true?

Option A :
SPM automatically creates and maintains execution plans for SQL statements.

Option B : SPM requires the OPTIMIZER_FEATURES_ENABLE initialization parameter to be set to the

Option C : SPM captures execution plans and uses them to compare with new plans generated for the
same SQL statement.

Option D : SPM automatically updates execution plans based on changes in the database environment.

Correct Answer: C

Explanation/Reference:

Option 1: Incorrect. SPM does not automatically create and maintain execution plans. It captures execution plans and uses

them for comparison. Option 2: Incorrect. The OPTIMIZER_FEATURES_ENABLE initialization parameter does not need to be set

specifically for SPM. Option 3: Correct. SPM captures execution plans and uses them to compare with new plans generated for

the same SQL statement. Option 4: Incorrect. SPM does not automatically update execution plans based on changes in the

database environment.

QUESTION: 19

Which feature of Oracle Database 19c can be used to maximize query performance by automatically creating
and maintaining optimal indexes?

Option A : Table Compression

Option B : Query Rewrite

Option C : Real Application Testing

Option D : Automatic Indexing

https://www.examdumps.in/1Z0-084-pdf-questions.html
 Correct Answer: D

Explanation/Reference:

Option 1: Incorrect. Table Compression is a feature that reduces the storage space required for tables and improves query

performance for certain types of queries. It is not used to create or maintain indexes. Option 2: Incorrect. Query Rewrite is a

feature that allows the database to rewrite SQL queries to equivalent queries that can be executed more efficiently. It does not

create or maintain indexes. Option 3: Incorrect. Real Application Testing is a feature that allows you to test the effect of system

changes and upgrades on the performance of your database. It does not create or maintain indexes. Option 4: Correct.

Automatic Indexing is a feature introduced in Oracle Database 19c that enables the database to automatically create and

maintain indexes to optimize query performance. It uses machine learning algorithms to identify missing indexes and

automatically creates them to improve query execution times.

QUESTION: 20

Which of the following statements about Automatic Database Diagnostic Monitor (ADDM) in Oracle Database
19c is correct?

Option A :
ADDM only analyzes performance issues related to SQL statements.

Option B : ADDM provides recommendations for both hardware and software configurations.

Option C : ADDM requires a separate software installation and license.

Option D : ADDM can only be enabled for Oracle databases running on Oracle Exadata storage.

Correct Answer: B

Explanation/Reference:

Option 1: Incorrect. ADDM analyzes and identifies performance issues in the entire database, not just SQL statements. Option

2: Correct. ADDM provides recommendations for both hardware and software configurations to improve database performance.

Option 3: Incorrect. ADDM is part of the Oracle Database Enterprise Edition and does not require a separate installation or

license. Option 4: Incorrect. ADDM can be enabled on any Oracle database, not just on Oracle Exadata storage.

https://www.examdumps.in/1Z0-084-pdf-questions.html
QUESTION: 21

Which of the following statements accurately describes the Automatic Workload Repository (AWR) feature in
Oracle Database 19c for performance management and tuning?

Option A : AWR provides historical performance data that can be used for diagnosing performance issues
and identifying trends.

Option B : AWR captures and stores all SQL statements executed in the database along with their
execution plans.

Option C : AWR automatically generates recommendations for performance tuning based on the collected
performance data.

Option D : AWR provides real-time monitoring of database performance and generates alerts for potential
issues.

Correct Answer: A

Explanation/Reference:

Option 1: Correct. AWR provides historical performance data that can be used for diagnosing performance issues and

identifying trends. It captures and stores data such as wait events, database time, SQL statistics, and system statistics. Option

2: Incorrect. While AWR captures SQL statements executed in the database, it does not store their execution plans. Execution

plans are stored in the Automatic Database Diagnostic Monitor (ADDM). Option 3: Incorrect. AWR does not automatically

generate recommendations for performance tuning. However, it can be used as a basis for generating recommendations using

other tools or features such as the SQL Tuning Advisor. Option 4: Incorrect. While AWR provides historical data for performance

analysis, it does not provide real-time monitoring or generate alerts for potential issues. Real-time monitoring and alerting are

provided by other features such as Oracle Enterprise Manager or Oracle Database Performance Monitoring.

QUESTION: 22

Which is the default memory management mode in Oracle Database 19c?

Option A : Automatic Shared Memory Management (ASMM)

Option B :
Automatic Memory Management (AMM)

Option C : Manual Memory Management

Option D : Dynamic Memory Management

https://www.examdumps.in/1Z0-084-pdf-questions.html
 Correct Answer: B

Explanation/Reference:

Option 1: Incorrect. Automatic Shared Memory Management (ASMM) is a memory management mode in Oracle Database, but

it is not the default in Oracle Database 19c. Option 2: Correct. The default memory management mode in Oracle Database 19c

is Automatic Memory Management (AMM), which allows Oracle to dynamically manage the memory allocation for the SGA and

PGA. Option 3: Incorrect. Manual Memory Management is a memory management mode in Oracle Database, but it is not the

default in Oracle Database 19c. Option 4: Incorrect. Dynamic Memory Management is not a memory management mode in

Oracle Database.

QUESTION: 23

When using Automatic Memory Management in Oracle Database 19c, what is the purpose of the
MEMOPTIMIZE_POOL_SIZE parameter?

Option A : It specifies the maximum size of the SGA.

Option B : It determines the size of the shared pool.

Option C : It controls the amount of memory reserved for PGA usage.

Option D : It adjusts the amount of memory allocated to the memoptimize pool.

Correct Answer: D

Explanation/Reference:

Option 1: The purpose of the MEMOPTIMIZE_POOL_SIZE parameter is to adjust the amount of memory that is allocated to the

memoptimized pool, a feature introduced in Oracle Database 12c that enables a segment of a table to be populated in memory

thereby enabling fast access. Option 2: While the SGThis is impacted by the automatic memory management, the

MEMOPTIMIZE_POOL_SIZE does not specifically set the maximum size of the SG Option 3: The PGThis is managed separately

from the SGA and the MEMOPTIMIZE_POOL_SIZE does not control the memory reserved for total PGA usage, only for

memoptimized pool usage. Option 4: This is the correct answer. The MEMOPTIMIZE_POOL_SIZE parameter is specifically

designed to allocate memory for the memoptimized pool.

QUESTION: 24

Which of the following is the best practice for improving performance when using partitioning in Oracle
Database 19c?

https://www.examdumps.in/1Z0-084-pdf-questions.html
 Option A : Partitioning the table based on a single column that has even distribution of data

Option B : Partitioning the table based on a single column that has uneven distribution of data

Option C :
Partitioning the table based on multiple columns that have even distribution of data

Option D : Partitioning the table based on multiple columns that have uneven distribution of data

Correct Answer: A

Explanation/Reference:

Option 1: Correct: Partitioning the table based on a single column that has even distribution of data is the best practice for

improving performance in Oracle Database 19c. This ensures that the data is distributed evenly across the partitions, allowing

for efficient query execution and parallel processing. Option 2: Incorrect: Partitioning the table based on a single column that

has uneven distribution of data is not a best practice for improving performance. Uneven distribution can lead to data skew

and imbalance across the partitions, causing performance issues. Option 3: Incorrect: Partitioning the table based on multiple

columns that have even distribution of data is not a best practice. Partitioning based on multiple columns can introduce

complexity and might not provide significant performance benefits compared to partitioning based on a single column. Option

4: Incorrect: Partitioning the table based on multiple columns that have uneven distribution of data is not a best practice.

Uneven distribution can lead to data skew and imbalance across the partitions, causing performance issues.

QUESTION: 25

Which method should be used to diagnose and analyze the performance of an Oracle Database using the
Automatic Database Diagnostic Monitor (ADDM)?

Option A : Query the V$ACTIVE_SESSION_HISTORY view

Option B : Query the V$SQL_MONITOR view

Option C : Use the SQL Tuning Advisor

Option D : Use the SQL Performance Analyzer

Correct Answer: B

Explanation/Reference:

Option 1: Incorrect. Querying the V$ACTIVE_SESSION_HISTORY view provides information about active sessions, but it does not

https://www.examdumps.in/1Z0-084-pdf-questions.html
provide detailed performance analysis or recommendations. Option 2: Correct. The ADDM can be used to diagnose and analyze

the performance of an Oracle Database. It automatically identifies potential performance issues and provides

recommendations for improvement. Option 3: Incorrect. The SQL Tuning Advisor is used to tune individual SQL statements, but

it does not provide holistic performance analysis like the ADDM. Option 4: Incorrect. The SQL Performance Analyzer is used to

compare the performance of different execution plans or system changes, but it does not diagnose and analyze the

performance of the entire database like the ADDM.

QUESTION: 26

Which of the following describes the benefit of using Resource Manager to manage resources in Oracle
Database 19c?

Option A : Resource Manager enables the classification of users, making tasks such as user authentication
and role management more manageable.

Option B : Resource Manager enables the prioritization of database server hardware, ensuring that more
important tasks are given priority access to computing resources while lesser tasks are relegated until the
primary request is done.

Option C : Resource Manager enables dynamic switching between different workloads on the database,
ensuring that the server always has the resources to handle traffic spikes.

Option D : Resource Manager enables the definition of shared pool sizes and enables automatic
distribution of resources between databases based on the workload and prior resource usage.

Correct Answer: B

Explanation/Reference:

Option 1: This option is incorrect because Resource Manager is intended to manage computing resources and does not affect

user management. Option 2: This option is the correct answer because Resource Manager enables the prioritization of

hardware resources, ensuring that the most critical database tasks such as transaction processing or report generation are

given precedence over lower-priority requests for better system performance. Option 3: This option is incorrect because

Resource Manager is intended to manage resource allocation, not workloads. Workloads are affected by other tools such as the

SQL plan management or Automatic Workload Repository features. Option 4: This option is incorrect because Resource

Manager does not enable shared pool sizes definition or automatic resource distribution between databases.

QUESTION: 27

Which of the following is the most efficient method to improve SQL performance?

https://www.examdumps.in/1Z0-084-pdf-questions.html
 Option A : Optimizing the table schema

Option B : Increasing the database buffer cache size

Option C : Enabling query result caching

Option D : Running the SQL Access Advisor

Correct Answer: A

Explanation/Reference:

Option 1: Correct. Optimizing the table schema involves making changes to the table structure, indexes, and constraints to

improve SQL performance. This can include creating appropriate indexes, partitioning tables, and using efficient data types.

Option 2: Incorrect. Increasing the database buffer cache size can help improve overall database performance, but it may not

specifically address SQL performance. Other tuning techniques, such as optimizing the table schema, are more directly related

to SQL performance. Option 3: Incorrect. Enabling query result caching can improve performance by caching the results of

frequently executed queries. However, this is not the most efficient method for improving SQL performance compared to

optimizing the table schema. Option 4: Incorrect. The SQL Access Advisor is used to recommend and implement solutions for

performance problems related to SQL access paths, but it is not the most efficient method for improving SQL performance

compared to optimizing the table schema.

QUESTION: 28

What is the purpose of Automatic Workload Repository (AWR) in Oracle Database 19c performance tuning?

Option A : It provides real-time performance data that is used to proactively identify bottlenecks and
service degradation.

Option B : It creates a baseline of performance statistics that can be used to compare with current
performance data to help identify issues.

Option C : It allows for the automated collection and storage of performance data, which can be accessed
through the Oracle Enterprise Manager.

Option D : It provides a graphical user interface that displays performance data in real-time and allows for
real-time adjustments to system settings.

Correct Answer: B

Explanation/Reference:

Option 1: This option is incorrect because although AWR does provide performance data in near-real time, it is used for analysis

https://www.examdumps.in/1Z0-084-pdf-questions.html
and diagnosis after the fact, not proactively identifying bottlenecks. Option 2: This option is correct because AWR captures

detailed performance statistics at regular intervals and stores them in the Automatic Workload Repository. These statistics can

be used to create a baseline of performance data that can be compared with current data to identify issues. Option 3: This

option is incorrect because although AWR does allow for automated collection and storage of performance data, it is primarily

accessed through the command line rather than Oracle Enterprise Manager. Option 4: This option is incorrect because AWR

does not provide a real-time graphical user interface for adjusting system settings. Those adjustments would need to be made

through other means, such as Oracle Enterprise Manager.

QUESTION: 29

Accessing the SALES tables causes excessive db file sequential read wait events. Examine this AWR except:

Now, examine these attributes displayed by querying dba_tables:

Finally, examine these parameter settings:

Which two must both be used to reduce these excessive waits?

Option A : Partition the SALES table.

Option B : Increase PCTFREE for the SALES table.

https://www.examdumps.in/1Z0-084-pdf-questions.html
 Option C : Re-create the SALES table.

Option D : Compress the SALES table.

Option E : Coalesce all sales table indexes.

Correct Answer: A,D

Explanation/Reference:

The AWR excerpt points to excessive physical reads on the SALES table and index, suggesting the need for optimizing table

storage and access. Partitioning the SALES table (A) can reduce 'db file sequential read' waits by breaking down the large

SALES table into smaller, more manageable pieces. This can localize the data and reduce the I/O necessary for query

operations. Compressing the SALES table (D) can also help reduce I/O by minimizing the amount of data that needs to be read

from disk. This can also improve cache utilization and reduce the 'db file sequential read' waits. References: Oracle Database

VLDB and Partitioning Guide, 19c Oracle Database Administrator’s Guide, 19c These changes are recommended based on

Oracle's best practices for managing large tables and reducing I/O waits, ensuring better performance and efficiency.

QUESTION: 30

Which of the following statements accurately describes the purpose and functionality of Automatic Workload
Repository (AWR) in Oracle Database?

Option A : AWR is used for monitoring and diagnostics, capturing and maintaining performance-related
data for analysis.

Option B : AWR provides automatic memory management for optimizing database performance.

Option C : AWR is a feature that allows you to track and analyze individual SQL statements within the
database.

Option D : AWR is a background process that automates the management of storage space for database
objects.

Correct Answer: A

Explanation/Reference:

Option 1: Correct. Automatic Workload Repository (AWR) is a built-in feature in Oracle Database that collects performance-

https://www.examdumps.in/1Z0-084-pdf-questions.html
related data at regular intervals, storing the information in the database for future analysis and diagnostics. Option 2:

Incorrect. Automatic memory management is actually provided by the Automatic Memory Management (AMM) feature in

Oracle Database, not by the Automatic Workload Repository (AWR). Option 3: Incorrect. Tracking and analyzing individual SQL

statements within the database is the purpose of the Automatic SQL Tuning feature, not the Automatic Workload Repository

(AWR). Option 4: Incorrect. The management of storage space for database objects is handled by separate features such as

Automatic Segment Space Management and Automated Storage Management (ASM), not by the Automatic Workload

Repository (AWR).

QUESTION: 31

Which three statements are true about tuning dimensions and details of v$sys_time_model and DB time?

Option A :

Statspack cannot account for high CPU time when CPU TIME is a Top 10 event in DB time. When CPU time
is high, SQL tuning may improve performance.

Option B :

Systems in which CPU time is dominant need more tuning that those in which WAIT TIME is dominant.

Option C : The proportion of WAIT TIME to CPU TIME always increases with increased system load.

Option D : When WAIT TIME is high, instance tuning may improve performance.

Option E : Parse Time Elapsed accounts for successful soft and hard parse operations only.

Option F : DB Time accounts for all time used by background processes and user sessions.

Correct Answer: A,D,F

Explanation/Reference:

A. Statspack is a performance diagnostic tool that can help identify high CPU usage issues. High CPU time may indicate that

SQL statements need to be tuned for better performance. D. High wait times can often be reduced by instance tuning, such as

https://www.examdumps.in/1Z0-084-pdf-questions.html
adjusting database parameters or improving I/O performance. F. DB Time is a cumulative time metric that includes the time

spent by both user sessions and background processes executing database calls.References: Oracle Database Performance

Tuning Guide, 19c Oracle Database Concepts, 19c

QUESTION: 32

Which feature is used to improve the performance of SQL queries by caching the result set of the query in
memory?

Option A : Query Result Cache

Option B : Database Buffer Cache

Option C :
Shared Pool

Option D : In-Memory Column Store

Correct Answer: A

Explanation/Reference:

Option 1: Correct. The Query Result Cache feature improves performance by caching query result sets in memory. This allows

subsequent executions of the same query to use the cached result set instead of executing the query again. Option 2:

Incorrect. The Database Buffer Cache is used to cache data blocks from disk, not query result sets. Option 3: Incorrect. The

Shared Pool is used to store shared SQL statements, not query result sets. Option 4: Incorrect. The In-Memory Column Store is

used to cache and optimize the access to specific columns of tables, not query result sets.

QUESTION: 33

Which of the following techniques is most effective for improving query performance in an Oracle database?

Option A : Using optimizer hints

Option B : Increasing the SGA size

Option C : Adding more indexes to the tables

Option D : Rewriting the SQL queries

https://www.examdumps.in/1Z0-084-pdf-questions.html
 Correct Answer: B

Explanation/Reference:

Option 1: Incorrect. Using optimizer hints can provide some control over the execution plan chosen by the optimizer, but it can

also lead to suboptimal execution plans if not used correctly. It is generally recommended to rely on the optimizer's automatic

plan generation instead of using hints. Option 2: Correct. Increasing the SGA (System Global Are size can improve query

performance by allowing more data to be cached in memory. This reduces the need for disk I/O and can greatly speed up query

execution. Option 3: Incorrect. While adding more indexes to the tables can improve the performance of specific queries, it can

also slow down data modification operations (such as INSERT, UPDATE, and DELETE) and consume additional storage space.

Adding indexes should be done carefully and only after analyzing the specific query workload. Option 4: Incorrect. While

rewriting SQL queries can sometimes improve query performance, it is not always the most effective technique. It is important

to carefully analyze the query execution plan and consider other optimization techniques before resorting to query rewriting.

QUESTION: 34

Which technique can be used to improve the performance of SQL queries?

Option A : Parallel Execution

Option B : Materialized Views

Option C : Database Replay

Option D : Database Resource Manager

Correct Answer: A

Explanation/Reference:

Option 1: Correct: Parallel Execution is a technique that allows a single SQL statement to be divided into multiple smaller tasks,

which can then be executed simultaneously by multiple parallel executing processes. This can significantly improve the

performance of SQL queries by utilizing multiple CPUs or computer nodes to process the query in parallel. Option 2: Incorrect:

Materialized Views are used to improve query performance by precomputing and storing the results of a query, so that

subsequent executions of the query can retrieve the results from the materialized view rather than performing the expensive

computations again. While materialized views can be an effective technique for improving performance, it is not the technique

being asked about in this question. Option 3: Incorrect: Database Replay is a feature that allows you to capture a workload

from a production system and replay it on a test system. This can be helpful for performance testing and analysis, but it is not

a technique specifically targeted at improving the performance of SQL queries. Option 4: Incorrect: Database Resource

Manager is a feature that allows you to manage and prioritize resources, such as CPU and memory, among different database

https://www.examdumps.in/1Z0-084-pdf-questions.html
users and applications. While this can help with performance management and tuning in general, it is not directly related to

improving the performance of SQL queries.

QUESTION: 35

Which application lifecycle phase could be managed reactively?

Option A : Design and development

Option B : Upgrade or migration

Option C : Testing

Option D : Production

Option E : Deployment

Correct Answer: D

Explanation/Reference:

The production phase of the application lifecycle is often managed reactively. While proactive measures and performance

tuning are essential, unforeseen issues can arise in production that require immediate attention and resolution. Reactive

management involves monitoring performance and responding to issues as they occur, ensuring the application maintains

acceptable performance levels for end-users. References Oracle Database 19c Performance Tuning Guide - Reactive Tuning

QUESTION: 36

Which of the following statements about Oracle Database 19c performance tuning is true?

Option A : Automatic Memory Management (AMM) is a recommended approach for managing memory

Option B : Statistics Gathering should be performed manually on a regular basis

Option C : Setting a fixed SGA size is a best practice for optimal performance

https://www.examdumps.in/1Z0-084-pdf-questions.html
 Option D : Oracle Real Application Testing (RAT) can be used to analyze the impact of system changes

Correct Answer: D

Explanation/Reference:

Option 1: Incorrect. Automatic Memory Management (AMM) is not recommended for managing memory in Oracle Database

19c. The best practice is to use Automatic Shared Memory Management (ASMM) and the Memory Advisor for optimal memory

management. Option 2: Incorrect. Statistics Gathering should be performed automatically using the Automatic Workload

Repository (AWR), Automatic Database Diagnostic Monitor (ADDM), and Automatic Statistics Gathering. Manual gathering of

statistics is not recommended for optimal performance. Option 3: Incorrect. Setting a fixed SGA size is not a best practice for

optimal performance in Oracle Database 19c. The recommended approach is to use Automatic Shared Memory Management

(ASMM) and let the database dynamically manage the SGA size. Option 4: Correct. Oracle Real Application Testing (RAT) is a

feature in Oracle Database 19c that can be used to analyze the impact of system changes, such as database upgrades,

patches, or configuration changes. It allows you to capture and replay real application workloads to test the performance and

stability of the changes in a controlled environment.

QUESTION: 37

What is the purpose of the shared pool in the Oracle Database and what is the recommended value for the
SHARED_POOL_SIZE parameter in the init.ora file? A. The shared pool is used to store parsed SQL
statements, and the recommended value for the SHARED_POOL_SIZE parameter is 256 MB B. The shared
pool is used to store parsed SQL statements, and the recommended value for the SHARED_POOL_SIZE
parameter varies depending on the size of the database C. The shared pool is used to store user session
information, and the recommended value for the SHARED_POOL_SIZE parameter is 1 GB D. The shared pool
is used to store database buffer cache, and the recommended value for the SHARED_POOL_SIZE parameter
is 512 MB

Option A : The shared pool is used to store parsed SQL statements, and the recommended value for the
SHARED_POOL_SIZE parameter is 256 MB

Option B : The shared pool is used to store parsed SQL statements, and the recommended value for the
SHARED_POOL_SIZE parameter varies depending on the size of the database

Option C : The shared pool is used to store user session information, and the recommended value for the
SHARED_POOL_SIZE parameter is 1 GB

Option D : The shared pool is used to store database buffer cache, and the recommended value for the
SHARED_POOL_SIZE parameter is 512 MB

Correct Answer: A

https://www.examdumps.in/1Z0-084-pdf-questions.html
Explanation/Reference:

Option 1: This is correct. The shared pool in the Oracle database is used to store parsed SQL statements. The recommended

value for the SHARED_POOL_SIZE parameter in the init.ora file is 256 M Option 2: This is incorrect. While the shared pool is

used to store parsed SQL statements, the recommended value for the SHARED_POOL_SIZE parameter does not vary depending

on the size of the database. Option 3: This is incorrect. The shared pool is not used to store user session information, and the

recommended value for the SHARED_POOL_SIZE parameter is not 1 G Option 4: This is incorrect. The shared pool is not used to

store database buffer cache and the recommended value for the SHARED_POOL_SIZE parameter is not 512 M

https://www.examdumps.in/1Z0-084-pdf-questions.html