1z0-084 Exam Dumps - Oracle Certification Questions and Answers

SQL Performance Analyzer (SPA) can be used to assess the impact of different types of changes on SQL performance. These changes can include database initialization parameters, which can significantly affect how SQL statements are executed and therefore their performance. SPA allows you to capture a workload before and after the change and compare the performance of each SQL statement.

Database consolidation, including moving to pluggable databases (PDBs), can also affect SQL performance. SPA can analyze the SQL workload to see how consolidation impacts performance, by comparing metrics such as elapsed time and CPU time before and after the consolidation.

References:

• Oracle Database SQL Tuning Guide, 19c
• Oracle Database Performance Tuning Guide, 19c

Database services in Oracle are used to manage how resources are allocated and how workloads are managed within the database. The features that use database services are:

• B (Correct):Database Resource Manager (DBRM) uses services to control resource allocation to different workloads. It ensures that resources are assigned to the most critical tasks first, based on the service associated with the workload.
• E (Correct):Oracle Scheduler can also utilize database services. Jobs in Oracle Scheduler can be assigned to different services to control resource allocation and prioritization.

The other features mentioned are related to SQL performance but do not directly utilize database services in the way Resource Manager and Scheduler do:

• A:Oracle Automatic Reoptimization is a feature that allows the database to automatically improve the execution plan of a SQL statement after it is executed, based on the actual performance metrics, but it does not directly use database services.
• C:Oracle SQL Performance Management involves various components of SQL tuning and monitoring, but it does not use database services to operate.
• D:Oracle SQL Tuning Advisor provides advice on how to tune SQL queries for better performance. While it can be used in conjunction with services for managing and analyzing workloads, it doesn't use services in its core functionality.

References:

• Oracle Database Administrator’s Guide:Administering Services
• Oracle Database Administrator's Guide:Managing Resources with Oracle Database Resource Manager
• Oracle Database Scheduler Developer’s Guide:Using the Scheduler

Given a server with 64 CPUs, if the buffer cache size increase did not alleviate free buffer waits and buffer busy waits, one can look into optimizing I/O and the efficiency of the DB writer processes.

C. Setting theDBWR_IO_SLAVESparameter to a non-zero value, such as the number of CPUs, would initiate I/O slave processes to assist the DB writer process. This can help reduce I/O contention when writing from the buffer cache to disk, particularly for systems without asynchronous I/O capabilities.

D. Increasing the value ofDBWRITERPROCESSESenables multiple DB writer processes to be active simultaneously. In a system with many CPUs, such as 64, increasing this value can improve the write throughput to disk and potentially reduce buffer busy waits.

References:

• Oracle Database Reference, 19c
• Oracle Database Performance Tuning Guide, 19c

The Automatic Database Diagnostic Monitor (ADDM) analyzes and reports on various types of performance problems. For Pluggable Databases (PDBs), it can identify issues such as I/O capacity limits which may hinder the overall performance by causing bottlenecks. Additionally, ADDM can report on user I/O waits, which can indicate performance issues related to the time it takes for user queries to read data from the disk.

References:

• Oracle Multitenant Administrator's Guide, 19c
• Oracle Database Performance Tuning Guide, 19c

When both a SQL Profile and a SQL Plan Baseline are in place, the SQL Profile has a stronger preference and the optimizer is more likely to choose the execution plan from the SQL Profile.

C. A SQL Profile is generally more influential than a SQL Plan Baseline because it contains additional statistics and directives that help the optimizer to generate a more efficient execution plan. If both exist, the optimizer will use the profile's plan unless the baseline's plan is proven to be better through the SQL performance monitoring process.

E. SQL Plan Baselines must be accepted before they can be used by the optimizer. If a SQL Plan Baseline is not accepted, it will not be considered for generating the execution plan. Therefore, the presence of an unaccepted SQL Plan Baseline will not automatically force the optimizer to use its plan.

References:

• Oracle Database SQL Tuning Guide, 19c
• Oracle Database Administrator’s Guide, 19c

The SHRINK SPACE operation compacts the table, which can reduce fragmentation and thus improve performance for sequential reads of the table. This operation could improve full table scans, which are typically associated with db file sequential read wait events.

References:

• Oracle Database Administrator’s Guide, 19c

When you executeDBMS_AUTO_TASK_ADMIN.DISABLE, it disables all automated maintenance tasks related to the Auto Task framework. This includes tasks such as the Automatic SQL Tuning Advisor, Automatic Segment Advisor, and others. However, the Automatic Optimizer Statistics Collection (D) remains enabled as it is not part of the Auto Task framework. The gathering of optimizer statistics is controlled separately and is a critical part of the database's self-tuning mechanism to ensure the optimizer has up-to-date information about the data distribution within tables and indexes.

References

• Oracle Database 19c PL/SQL Packages and Types Reference - DBMS_AUTO_TASK_ADMIN
• Oracle Database 19c Database Administrator’s Guide - Managing Optimizer Statistics

TheDBMS_STATS.GATHER_DATABASE_STATSprocedure is used to gather statistics for all schema objects in the database that do not have up-to-date statistics. These statistics are essential for the optimizer to make informed decisions about the most efficient way to execute a query. The procedure collects statistics such as table and column statistics, index statistics, and system statistics, which are all used in the execution plan generation.

References:

• Oracle Database PL/SQL Packages and Types Reference, 19c
• Oracle Database Performance Tuning Guide, 19c

The execution plan shows a full table access for theSALEStable. To reduce the execution time, creating an index onSALES.TIME_IDwould be beneficial as it would allow the database to quickly sort and retrieve the most recent sales without the need to perform a full table scan, which is I/O intensive and slower. By indexingTIME_ID, which is used in theORDER BYclause, the optimizer can take advantage of the index to efficiently sort and limit the result set to the ten most recent sales.

• B (Incorrect):ReplacingFETCH FIRSTwithROWNUMwould not necessarily improve the performance unless there is an appropriate index that the optimizer can use to avoid sorting the entire result set.
• C (Incorrect):There is no indication that the current statistics are inaccurate; hence, collecting new statistics may not lead to performance improvement.
• D (Incorrect):While adaptive plans can provide performance benefits by allowing the optimizer to adapt the execution strategy, the main issue here is the lack of an index on theORDER BYcolumn.
• E (Incorrect):Creating an index onSALES.CUST_IDcould improve join performance but would not address the performance issue caused by the lack of an index on theORDER BYcolumn.

References:

• Oracle Database SQL Tuning Guide:Managing Indexes
• Oracle Database SQL Tuning Guide:Using Indexes and Clusters

To transport performance data from an Oracle Database Standard Edition, which uses Statspack, to an Enterprise Edition database, which uses AWR, you must consider the compatibility of data structures and repository schemas between these tools. The recommended method is:

• D (Correct):Export the data using theexputility with a parameter file appropriate for Statspack (likespuexp.par) from the Statspack repository and import it into a dedicated Statspack schema on the destination. Since Statspack and AWR use different schemas, it's not recommended to import Statspack data directly into the AWR repository.

The other options are incorrect because:

• A (Incorrect):expdpis not designed to export from Statspack, andawrloadis intended for loading from an AWR export file, not a Statspack export.
• B (Incorrect):Althoughexpdpandimpdpare used for exporting and importing data, the AWR repository schema is different from the Statspack schema, so importing Statspack data directly into the AWR repository is not recommended.
• C (Incorrect):Usingexpdpto export from Statspack and then importing directly into the AWR repository is not the correct approach due to the schema differences between Statspack and AWR.

References:

• Oracle Database Performance Tuning Guide:Migrating from Statspack to AWR