Master Oracle 1Z0-084 Exam with Reliable Practice Questions
Upgrade to Premium
Examine these commands, which execute successfully:
Which statement is true?
Correct : D
The DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure allows setting attributes related to AWR snapshots. While the interval setting controls the frequency of snapshot generation, purging them is a separate process that can be managed either automatically (with retention settings) or manually. Reference:
Oracle Database PL/SQL Packages and Types Reference, 19c
Oracle Multitenant Administrator's Guide, 19c
Start a Discussions
Which two types of performance problems are reported by ADDM for PDBS?
Correct : A, E
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.
Oracle Multitenant Administrator's Guide, 19c
Oracle Database Performance Tuning Guide, 19c
Start a Discussions
Examine this statement and its corresponding execution plan:
Which phase introduces the CONCATENATION step?
Correct : D
The CONCATENATION step in an execution plan is introduced during the SQL Transformation phase. This phase is part of the optimizer's query transformations which can include various techniques to rewrite the query for more efficient execution. The CONCATENATION operation is used to combine the results of two separate SQL operations, typically when there is an OR condition in the WHERE clause, as seen in the provided query.
Oracle Database SQL Tuning Guide, 19c
Oracle Database Concepts, 19c
Start a Discussions
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?
Correct : A, D
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.
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.
Start a Discussions
A database instance is suffering poor I/O performance on two frequently accessed large tables.
No Big Table caching occurs in the database.
Examine these parameter settings:
Which are two actions either one of which will allow Big Table caching to occur?
Correct : C, D
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 because DB_BIG_TABLE_CACHE_PERCENT_TARGET is 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): Increasing DB_BIG_TABLE_CACHE_PERCENT_TARGET to 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): Increasing DB_CACHE_SIZE to 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: Increasing DB_BIG_TABLE_CACHE_PERCENT_TARGET to 50 without adjusting the overall size of the cache might still not be sufficient if the DB_CACHE_SIZE is not large enough to hold the big tables.
B: Setting DB_KEEP_CACHE_SIZE to at least 50M only specifies a separate buffer pool for objects with the KEEP cache attribute and does not affect Big Table caching.
E: and F: Changing the PARALLEL_DEGREE_POLICY to ADAPTIVE or AUTO influences the behavior of parallel execution but does not directly enable or influence Big Table caching.
Oracle Database Performance Tuning Guide: Big Table Caching
Oracle Database Reference: DB_BIG_TABLE_CACHE_PERCENT_TARGET
Start a Discussions