1z0-084 Oracle Database 19c: Performance Management and Tuning Questions and Answers

When importing objects that used the In-Memory (IM) column store in their source database using Oracle Data Pump, the following statements are true:

• D (Correct):TheTRANSFORMclause can be used to alter object creation DDL during import operations. This can include adding theINMEMORYclause to tables that were not originally using the IM column store.
• F (Correct):The import operation can preserve theINMEMORYattributes of tables as they were at the time of export, effectively replicating the IM column store settings from the source database.

The other statements are not accurate in the context of Data Pump import:

• A (Incorrect):Data Pump does not give preference to the IM column store clauses at the tablespace level over table-level definitions unless explicitly specified by theTRANSFORMclause.
• B (Incorrect):While Data Pump can transport existingINMEMORYattributes, it is not mandatory. It is controlled by theINCLUDEorEXCLUDEData Pump parameters or theTRANSFORMclause.
• C (Incorrect):TheINMEMORY_CLAUSEparameter is not part of the Data Pump Export utility. To modify the IM column store clauses, you would use theTRANSFORMparameter during import, not export.
• E (Incorrect):Data Pump does not ignore the IM column store clause unless specifically instructed to do so via theEXCLUDEparameter.

References:

• Oracle Database Utilities:Data Pump Export
• Oracle Database Utilities:Data Pump Import

In this scenario, creating an index and generating frequency histograms are two methods that can potentially improve performance:

• A (Correct):Generating frequency histograms on theCITY_IDcolumn can help the optimizer make better decisions regarding the execution plan, especially if the data distribution is skewed. Histograms provide the optimizer with more detailed information about the data distribution in a column, which is particularly useful for columns with non-uniform distributions.
• B (Correct):Creating an index on theCITY_IDcolumn would speed up queries that filter on this column, especially if it's used frequently in the WHERE clause as a filter. An index would allow for an index range scan instead of a full table scan, reducing the I/O and time needed to execute such queries.
• C (Incorrect):While SQL profiles can be used to improve the performance of specific SQL statements, they are usually not the first choice for such a problem, and creating a profile does not replace the need for proper indexing or statistics.
• D (Incorrect):Forcing the subquery to use dynamic sampling might not provide a consistent performance benefit, especially if the table statistics are not representative or are outdated. However, dynamic sampling is not as effective as having accurate statistics and a well-chosen index.
• E (Incorrect):Adaptive plans can adjust the execution strategy based on the conditions at runtime. While they can be useful in certain scenarios, in this case, creating an index and ensuring accurate statistics would likely provide a more significant performance improvement.

References:

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

For a Standard Edition production database, the Statspack tool is available to diagnose performance problems. The Automatic Workload Repository (AWR) and its related tools like AWR Compare Periods report and SQL Performance Analyzer are features of the Oracle Database Enterprise Edition and are not available in Standard Edition. Database Replay is also a feature of the Enterprise Edition. Statspack is a performance diagnostic tool provided for earlier versions and Standard Editions of the Oracle Database to collect, store, and analyze performance data.

References

• Oracle Database 19c Administrator’s Guide - Using Statspack to Diagnose Database Performance Issues

A high buffer cache hit ratio typically indicates that the database is effectively using the buffer cache and does not often need to read data from disk. However, this metric alone is not a reliable indicator of the I/O performance of the database for several reasons:

• Full table scans and fast full index scans (A) can bypass the buffer cache by design if the blocks are not deemed reusable shortly, which can impact the cache hit ratio.
• A high cache hit ratio (B) can be misleading if the database performance is poor due to other factors, such as inefficient queries or contention issues.
• The buffer cache advisory (C) is a more valuable tool for understanding the potential impact of different cache sizes on the database's I/O performance. It simulates scenarios with different cache sizes and provides a more targeted recommendation.
• The RECYCLE and KEEP buffer caches (D) are specialized caches designed for certain scenarios. While high hit ratios can be beneficial, they are not universally required; some workloads might not be significantly impacted by lower hit ratios in these caches.
• A lower cache hit ratio (E) does not necessarily mean poor I/O performance. In some cases, a system with a well-designed storage subsystem and efficient queries might perform well even with a lower cache hit ratio.

References

• Oracle Database 19c Performance Tuning Guide - Buffer Cache Hit Ratio
• Oracle Database 19c Performance Tuning Guide - v$db_cache_advice

When you enable statistics gathering for a specific service and module usingDBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE, the output is aggregated and can be viewed using theV$SERV_MOD_ACT_STATSdynamic performance view. This view contains the cumulative statistics of database activity broken down by service and module, which is exactly what you collect when executing the provided command.

• B (Incorrect):While many types of trace files are located in the Diagnostic Destination directory ($ORACLE_BASE/diag), the aggregated statistics for services and modules are not written to trace files but are instead viewable through dynamic performance views.
• C (Incorrect):TheV$SERVICE_STATSview provides service-level statistics but does not provide the combined service/module-level breakdown.
• D (Incorrect):The output of the PL/SQL block is not written to a file in the current working directory; it is stored in the data dictionary and accessible via dynamic performance views.

References:

• Oracle Database PL/SQL Packages and Types Reference:DBMS_MONITOR
• Oracle Database Reference:V$SERV_MOD_ACT_STATS

A. WhenCursor_sharingis set toFORCE, 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. Settingcursor_sharingtoEXACTensures 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. WithEXACT, there's no cursor sharing for statements with different literals, hence no opportunity for ACS to operate.

References:

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

Statspack is a performance diagnostic tool provided by Oracle prior to the introduction of the Automatic Workload Repository (AWR). At snap level 6, Statspack captures the following types of statistics:

• A (Correct):Parent and child latches are captured. Latch statistics provide information about contention for latches, which are low-level serialization mechanisms used by Oracle.
• E (Correct):Enqueue statistics, which provide information on the waits for locks that manage the concurrency between users.
• F (Correct):Segment-level statistics, which provide detailed information on database segments such as tables, indexes, etc., to identify I/O and contention issues.
• C (Incorrect):While optimizer execution plans are an essential aspect of performance tuning, detailed execution plan capture is not part of the Statspack report at level 6.
• D (Incorrect):Plan usage data refers to how frequently a plan is being used, which is more associated with AWR and not typically captured in Statspack reports.

References:

• Oracle Database Performance Tuning Guide:Using Statspack

When MEMORY_TARGET is set to a nonzero value, Oracle automatically manages the memory allocation between the System Global Area (SGA) and the Program Global Area(PGA). If MEMORY_MAX_TARGET is not explicitly set, Oracle will behave in the following manner:

• MEMORY_MAX_TARGET will default to the value of MEMORY_TARGET, assuming the platform allows for the value of MEMORY_TARGET to be increased dynamically. This means that MEMORY_TARGET represents both the initial allocation and the maximum limit for the dynamically managed memory unless MEMORY_MAX_TARGET is specified differently.
• If MEMORY_TARGET is set to a value that is less than the sum of the current values of SGA_TARGET and PGA_AGGREGATE_TARGET, Oracle will use the higher sum as the default value for MEMORY_MAX_TARGET to ensure that there is adequate memory for both areas. The database instance will not start if MEMORY_TARGET is not sufficient to accommodate the combined SGA and PGA requirements.

References

• Oracle® Database Administrator's Guide 19c: Automatic Memory Management
• Oracle® Database Performance Tuning Guide 19c: Using Automatic Memory Management

Big Table caching is a feature that allows frequently accessed large tables to be cached in memory to improve I/O performance. From the parameter settings provided, Big Table caching is not occurring becauseDB_BIG_TABLE_CACHE_PERCENT_TARGETis set to 10, which is the minimum threshold for enabling the feature, but the size of the cache is too small for the big tables to be effectively cached.

To enable Big Table caching, one of the following actions could be taken:

• C (Correct):IncreasingDB_BIG_TABLE_CACHE_PERCENT_TARGETto at least 25. This action would allocate a larger percentage of the buffer cache for storing big tables, which could allow for caching large tables and thus improve I/O performance.
• D (Correct):IncreasingDB_CACHE_SIZEto 1G. Since the size of the buffer cache is a determining factor for how much data can be cached, increasing this parameter would provide more memory space for big tables to be cached.

Options A, B, E, and F will not enable Big Table caching because:

• A:IncreasingDB_BIG_TABLE_CACHE_PERCENT_TARGETto 50 without adjusting the overall size of the cache might still not be sufficient if theDB_CACHE_SIZEis not large enough to hold the big tables.
• B:SettingDB_KEEP_CACHE_SIZEto at least 50M only specifies a separate buffer pool for objects with the KEEP cache attribute and does not affect Big Table caching.
• E:andF:Changing thePARALLEL_DEGREE_POLICYtoADAPTIVEorAUTOinfluences the behavior of parallel execution but does not directly enable or influence Big Table caching.

References:

• Oracle Database Performance Tuning Guide:Big Table Caching
• Oracle Database Reference:DB_BIG_TABLE_CACHE_PERCENT_TARGET
• Oracle Database Reference:DB_CACHE_SIZE

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

Given that index range scan operations onIX_SALES_TIME_IDare slower due to an increase in buffer gets, the aim is to improve the efficiency of the index access. In this scenario:

• B (Correct):Re-creating the index usingADVANCED COMPRESSIONcan reduce the size of the index, which can lead to fewer physical reads (reduced I/O) and buffer gets when the index is accessed, as more of the index can fit into memory.

The other options would not be appropriate because:

• A (Incorrect):Re-creating theSALEStable sorted in order of the index might not address the issue of excessive buffer gets. Sorting the table would not improve the efficiency of the index itself.
• C (Incorrect):Using the columns inIX_SALES_TIME_IDas a hash partitioning key for theSALEStable is more relevant to data distribution and does not necessarily improve index scan performance.
• D (Incorrect):Hash partitioning the index is generally used to improve the scan performance in a parallel query environment, but it may not reduce the number of buffer gets in a single-threaded query environment.

References:

• Oracle Database SQL Tuning Guide:Managing Indexes
• Oracle Database SQL Tuning Guide:Index Compression

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 Oracle Optimizer statistics advisor, which is part of the automated tasks framework, can be disabled using the DBMS_AUTO_TASK_ADMIN package. This will prevent it fromrunning and thus reduce space usage in the SYSAUX tablespace.References:

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