[May-2025] 1z1-084 Exam Dumps - Free Demo & 365 Day Updates
Free Sales Ending Soon - Use Real 1z1-084 PDF Questions
NEW QUESTION # 14
Which two types of performance problems are reported by ADDM for PDBS?
- A. SGA sizing issues
- B. Top SQL statements
- C. User I/O waits
- D. I/O capacity limits
- E. Excessive checkpoint writes
Answer: B,D
Explanation:
ADDM (Automatic Database Diagnostic Monitor) is a key Oracle diagnostic tool that analyzes performance data captured by AWR (Automatic Workload Repository) and provides insights into performance issues. In a PDB (Pluggable Database), ADDM can provide recommendations for various types of performance issues.
Why A. I/O capacity limits is correct:
* ADDM evaluates the performance of a database and identifies I/O bottlenecks that could be impacting query performance.
* I/O capacity limits occur when the database cannot handle the I/O workload effectively, which could be due to disk contention, inadequate I/O throughput, or hardware limitations.
* ADDM reports such issues and suggests remedies like tuning SQL queries, redistributing I/O workloads, or upgrading storage systems.
Why D. Top SQL statements is correct:
* One of the critical capabilities of ADDM is identifying high-resource-consuming SQL statements (Top SQL) that are impacting database performance.
* For PDBs, ADDM provides specific recommendations to optimize these SQL statements, such as improving execution plans, adding indexes, or rewriting queries.
Why Other Options Are Incorrect:
* B. Excessive checkpoint writes:
* While excessive checkpoint writes can occur, ADDM typically focuses on higher-level performance problems like I/O issues or top SQL rather than specific low-level operational events like checkpointing. Excessive checkpoint writes are not commonly reported directly for PDBs by ADDM.
* C. SGA sizing issues:
* ADDM does not directly analyze or provide recommendations for SGA (System Global Area) sizing issues at the PDB level because SGA is managed at the CDB (Container Database) level.
PDBs share the SGA of the CDB.
* E. User I/O waits:
* While user I/O waits are captured in performance metrics, ADDM typically groups them under broader issues like I/O capacity limits. It does not specifically report on "user I/O waits" as a standalone problem.
Key Features of ADDM for PDBs:
* Identifying Top SQL Statements impacting performance.
* Reporting I/O issues, including capacity limits and contention.
* Highlighting inter-PDB resource contention within the same CDB.
References to Oracle Documentation:
* Oracle Database 19c Performance Tuning Guide:
* Section: Using ADDM for Pluggable Databases.
* Details the types of performance issues ADDM reports for PDBs.
* Automatic Database Diagnostic Monitor (ADDM) Concepts:
* Highlights ADDM's ability to analyze I/O, SQL performance, and resource utilization.
NEW QUESTION # 15
You execute the following:
EXECUTE DBMS_AuTO_TASK_ADMIN.DISABLE;
Which advisor remains enabled?
- A. Automatic Optimizer Statistics Collection
- B. Automatic SQL Tuning
- C. Optimizer Statistics Advisor
- D. Automatic Segment Advisor
- E. SQL Plan Management Evolve Advisor
Answer: A
Explanation:
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
NEW QUESTION # 16
Examine this AWRreport excerpt:
You must reduce the impact of database I/O, without increasing buffer cache size and without modifying the SQL statements.
Which compression option satisfies this requirement?
- A. COLUMN STORE COMPRESS FOR QUERY HIGH
- B. MN STORE COMPRESS FOR QUERY LOW
- C. STORE COMPRESS
- D. ROW STORE COMPRESS ADVANCED
Answer: D
Explanation:
To reduce the impact of database I/O without increasing the size of the buffer cache and without modifying SQL statements, you can use table compression. Among the given options,ROW STORE COMPRESS ADVANCEDis the most suitable form of table compression to satisfy this requirement.
Advanced row compression (ROW STORE COMPRESS ADVANCED) is designed to work well with all supported types of data, whether it's OLTP or data warehouse environments. It offers a higher level of compression than basic table compression (ROW STORE COMPRESS BASIC)without significant overhead during DML operations. This feature can help reduce the amount of I/O required to retrieve data by storing it more efficiently on disk.
* A, B, D:WhileCOLUMN STORE COMPRESS FOR QUERY HIGHandROW STORE
COMPRESSare both valid compression types,COLUMN STORE COMPRESS FOR QUERY
* HIGHapplies to the In-Memory column store and is not available in all versions and editions, andROW STORE COMPRESSis less advanced thanROW STORE COMPRESS ADVANCED.
References:
* Oracle Database Concepts Guide:Table Compression
* Oracle Database Performance Tuning Guide:Row Compression
NEW QUESTION # 17
You need to collect and aggregate statistics for the ACCTG service and PAYROLL module, and execute:
Where do you find the output of this command?
- A. By viewing V$SERVICE_STATS
- B. By viewing V$SERV_MOD_ACT_STATS
- C. In the current working directory
- D. In $ORACLE_BASE/diag/rdbms/<db unique name>/<instance name>/trace
Answer: B
Explanation:
When you enable statistics gathering for a specific service and module using DBMS_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
NEW QUESTION # 18
Examine these commands, which execute successfully:
Which statement is true?
- A. AWR snapshots can be purged manually in pluggable databases.
- B. AD DM is enabled for all pluggable databases.
- C. ADDM, AWR, and ASH reports can be purged automatically.
- D. AWR- snapshots in all pluggable databases will be purgedautomatically after every 60 mins.
Answer: A
Explanation:
TheDBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGSprocedure 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.References:
* Oracle Database PL/SQL Packages and Types Reference, 19c
* Oracle Multitenant Administrator's Guide, 19c
NEW QUESTION # 19
Which two statements are true about the use and monitoring of Buffer Cache Hit ratios and their value in tuning Database I/O performance?
- A. Both the RECYCLE and KEEP buffer caches should always have a very high cache hit ratio.
- B. The performance of workloads that primarily generate full table scans and fast full index scans are always affected by the cache hit ratio.
- C. A 60% cache hit ratio can be observed for database instances which have very good I/O performance.
- D. A 99% cache hit ratio can be observed for database instances which have very poor I/O performance.
- E. The buffer cache advisory view v$db_cache_advice provides advice on cache hit ratios appropriate for the instance workload.
Answer: D,E
NEW QUESTION # 20
What are the least elevated values of statistics_level and C0NTR0LJ4ANAGEMENT_PACK_ACCESS that allow the usage of Monitoring of Database Operations?
- A. STATISTICS_LEVEL=TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC
- B. STATISTICS_LEVEL=ALL and
CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC+TUNING - C. STATISTICS_LEVEL=TYPICAL and
CONTROL_MANAGEMENT_PACK_ACCESS-DIAGOSTIC*TUNING - D. STATISTICS_LEVEL=BASIC and CONTROL_MANAGEMENT_PACK ACCESS=DIAGOSTIC
Answer: B
Explanation:
Monitoring of Database Operations requires that theSTATISTICS_LEVELparameter be set toALLand CONTROL_MANAGEMENT_PACK_ACCESSbe set toDIAGNOSTIC+TUNING. These settings enable all the advisory features and automatic tuning features within the Oracle Database, including the Automatic Workload Repository (AWR), Automatic Database Diagnostic Monitor (ADDM), and the full functionality of the SQL Tuning Advisor and SQL Access Advisor, which are components of the Diagnostic and Tuning packs.
* STATISTICS_LEVEL=ALL:This setting enables the collection of all system statistics for problem detection and self-tuning purposes.
* CONTROL_MANAGEMENT_PACK_ACCESS=DIAGNOSTIC+TUNING:This grants access to both the Diagnostic Pack and the Tuning Pack, which are essential for detailed performance monitoring and tuning capabilities.
References:
* Oracle Database Reference:STATISTICS_LEVEL
* Oracle Database Licensing Information User Manual:Oracle Database Management Packs
NEW QUESTION # 21
You need to transport performance data from a Standard Edition to an Enterprise Edition database. What is the recommended method to do this?
- A. Export the data by using expdp from the ftatspack repository and import it by using impdp into the AWR repository.
- B. Export the data by using expdp from Statspack and import it by using
$ORACLE_HOME/rdbms/admin/awrload into the AWRrepository. - C. Export the data by using the expdp utility and parameter file spuexp.par from the Statspack repository and import it by using impdp into Export the data by using expdp from the Statspack repository and import it by using impdp into the AWR repository.
- D. Export the data by using the exp utility and parameter file spuexp.par from the Statspack repository and import it by using imp into a dedicated Statspack schema on the destination.
Answer: D
Explanation:
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 (like spuexp.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
NEW QUESTION # 22
What is the right time to stop tuning an Oracle database?
- A. When the buffer cache and library cache hit ratio is above 95%
- B. When all the concurrency waits are eliminated from the Top 10
- C. When the allocated budget for performance tuning has been exhausted
- D. When the I/O is less than 10% of the DB time
Answer: C
Explanation:
The right time to stop tuning an Oracle database is often determined by the point of diminishing returns - when the cost of further tuning (in terms of time, resources, or money) exceeds the performance benefits gained.
This is often related to the budget allocated for performance tuning.
* A (Correct):When the allocated budget for performance tuning has been exhausted, it may be time to stop tuning unless the benefits of further tuning justify requesting additional budget.
* B (Incorrect):Eliminating all concurrency waits from the Top 10 is an unrealistic goal since some waits are inevitable and can occur due to application design, which might not be possible to eliminate completely.
* C (Incorrect):The buffer cache and library cache hit ratio being above 95% does not necessarily indicate that the database is fully optimized. Hit ratios are not reliable indicators of database performance and should not be used as sole criteria to end tuning efforts.
* D (Incorrect):Having I/O less than 10% of DB time is not a definitive indicator to stop tuning. It is essential to consider the overall performance goals and whether they have been met rather than focusing solely on I/O metrics.
References:
* Oracle Database Performance Tuning Guide:Introduction to Performance Tuning
* Oracle Database 2 Day + Performance Tuning Guide:Understanding the Tuning Process
NEW QUESTION # 23
Examine this statement and its corresponding execution plan:
Which phase introduces the CONCATENATION step?
- A. SQL Execution
- B. SQL Transformation
- C. SQL Adaptive Execution
- D. SQL Row Source Generation
- E. SQL Semantic Check
Answer: B
Explanation:
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.
References:
* Oracle Database SQL Tuning Guide, 19c
* Oracle Database Concepts, 19c
NEW QUESTION # 24
Which two options are part of a Soft Parse operation?
- A. SQL Optimization
- B. Semantic Check
- C. Syntax Check
- D. SQL Row Source Generation
- E. Shared Pool Memory Allocation
Answer: B
NEW QUESTION # 25
Examine this AWR report excerpt:
You must reduce the impact of database I/O, without increasing buffer cache size and without modifying the SQL statements.
Which compression option satisfies this requirement?
- A. COLUMN STORE COMPRESS FOR QUERY HIGH
- B. MN STORE COMPRESS FOR QUERY LOW
- C. ROW STORE COMPRESS ADVANCED
- D. STORE COMPRESS
Answer: A
Explanation:
The question asks to reduce database I/O impact without increasing the buffer cache size or modifying SQL statements. This indicates a need to reduce the physical I/O required to access the data. Let's analyze the scenario and the options.
Analysis of the AWR Report:
* Top Wait Events:
* The top foreground wait event is db file sequential read, which accounts for 40.4% of DB time.
This indicates significant physical I/O operations, primarily single-block reads, which are typically associated with index access.
* Reducing the physical I/O associated with db file sequential read can significantly improve performance.
* SQL Ordered by Reads:
* The SQL consuming the most reads involves high physical I/O. This confirms the need to reduce I
/O overhead by compressing data efficiently to minimize physical reads.
Compression Techniques and Their Suitability:
* A. COLUMN STORE COMPRESS FOR QUERY LOW:
* This option is a columnar compression method that optimizes for query performance but provides less compression compared to the HIGH option. While effective, it is not as suitable as FOR QUERY HIGH for reducing I/O.
* B. STORE COMPRESS:
* This is the basic compression option for tables and does not offer the advanced capabilities required for reducing significant physical I/O for queries.
* C. ROW STORE COMPRESS ADVANCED:
* This is a row-level compression that is suitable for OLTP workloads. While it reduces storage, it does not reduce query-related I/O as effectively as columnar compression.
* D. COLUMN STORE COMPRESS FOR QUERY HIGH (Correct Option):
* This is the most effective option for reducing query-related I/O. It:
* Uses columnar compression to reduce the size of data stored on disk.
* Reduces the number of physical reads by compressing data highly, meaning fewer blocks need to be read.
* Optimizes query performance for analytical workloads, which aligns with the scenario described in the AWR report.
Why COLUMN STORE COMPRESS FOR QUERY HIGH Is the Best Fit:
* It is designed to improve query performance by minimizing the amount of I/O required.
* Suitable for environments with heavy read operations (as indicated by the db file sequential read waits).
* Does not require changes to SQL or buffer cache size, adhering to the constraints in the question.
Reference to Oracle Documentation:
* Oracle Database 19c Performance Tuning Guide:
* Section: Using Compression to Reduce Storage and I/O Requirements.
* Discussion of columnar compression techniques for reducing I/O in query-intensive environments.
* Oracle Advanced Compression Documentation:
* Details on COLUMN STORE COMPRESS FOR QUERY HIGH and its benefits for analytical workloads.
NEW QUESTION # 26
Which Optimizer component helps decide whether to use a nested loop join or a hash join in an adaptive execution plan?
- A. Statistics Feedback
- B. Dynamic Statistics
- C. Automatic Reoptimization
- D. Statistics Collector
- E. SQL Plan Directives
Answer: D
Explanation:
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.
References:
* Oracle Database SQL Tuning Guide, 19c
NEW QUESTION # 27
What are the least elevated values of statistics_level and C0NTR0LJ4ANAGEMENT_PACK_ACCESS that allow the usage of Monitoring of Database Operations?
- A. STATISTICS_LEVEL=TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS-
DIAGOSTIC*TUNING - B. STATISTICS_LEVEL=TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC
- C. STATISTICS_LEVEL=ALL and
CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC+TUNING - D. STATISTICS_LEVEL=BASIC and CONTROL_MANAGEMENT_PACK ACCESS=DIAGOSTIC
Answer: C
Explanation:
Monitoring of Database Operations requires that the STATISTICS_LEVEL parameter be set to ALL and CONTROL_MANAGEMENT_PACK_ACCESS be set to DIAGNOSTIC+TUNING. These settings enable all the advisory features and automatic tuning features within the Oracle Database, including the Automatic Workload Repository (AWR), Automatic Database Diagnostic Monitor (ADDM), and the full functionality of the SQL Tuning Advisor and SQL Access Advisor, which are components of the Diagnostic and Tuning packs.
* STATISTICS_LEVEL=ALL: This setting enables the collection of all system statistics for problem detection and self-tuning purposes.
* CONTROL_MANAGEMENT_PACK_ACCESS=DIAGNOSTIC+TUNING: This grants access to both the Diagnostic Pack and the Tuning Pack, which are essential for detailed performance monitoring and tuning capabilities.
References:
* Oracle Database Reference: STATISTICS_LEVEL
* Oracle Database Licensing Information User Manual: Oracle Database Management Packs
NEW QUESTION # 28
Which two statements are true about session wait information contained in v$session or v$session_wait?
- A. Rows for sessions displaying WAITED UNKNOWN TIME in the STATE column indicate that the session is still waiting.
- B. Rows for sessions that are not waiting might contain the actual wait time for the last event for which they waited.
- C. Rows for sessions that are currently waiting have a wait time of 0.
- D. Rows for sessions that are currently waiting have their wait time incremented every microsecond.
- E. Rows for sessions that are not waiting always contain the total wait time since the session started.
Answer: B,C
Explanation:
In theV$SESSIONview, Oracle provides information about the session waits:
B: When theWAIT_TIMEcolumn has a value of 0, it signifies that the session is currently waiting for a resource. This column represents the duration of the current or last wait.
C: If the session is not actively waiting, theWAIT_TIMEcolumn shows the time the session spent waiting for the last wait event. If theSTATEcolumn is showing "WAITED KNOWN TIME", it means the session is not currently waiting, but it indicates the time for which it had waited.
References:
* Oracle Database Reference, 19c
* Oracle Database Performance Tuning Guide, 19c
NEW QUESTION # 29
An Oracle 19c database uses default values for all optimizer initialization parameters.
After a table undergoes partition maintenance, a large number of wait events occur for:
cursor: pin S wait on X
Which command reduces the number of these wait events?
- A. ALTER SYSTEM SET CURSOR_SPACE_FOR_TIME - TRUE;
- B. ALTER SYSTEM SET CURSOR_INVALIDATION = DEFERRED;
- C. ALTER SYSTEM SET CURSOR_SHARING = FORCE;
- D. ALTER SYSTEM SET SESSION CACHED CURSORS = 500;
Answer: B
Explanation:
The cursor: pin S wait on X wait event suggests contention for a cursor pin, which is associated with mutexes (a type of locking mechanism) that protect the library cache to prevent concurrent modifications.
This issue can often be alleviated by deferring the invalidation of cursors until the end of the call to reduce contention. The correct command to use would be:
* C (Correct): ALTER SYSTEM SET CURSOR_INVALIDATION=DEFERRED; This setting defers the invalidation of dependent cursors until the end of the PL/SQL call, which can reduce the cursor: pin S wait on X wait events.
The other options are incorrect in addressing this issue:
* A (Incorrect): Setting CURSOR_SHARING to FORCE makes the optimizer replace literal values with bind variables. It doesn't address the contention for cursor pins directly.
* B (Incorrect): CURSOR_SPACE_FOR_TIME=TRUE aims to reduce the parsing effort by keeping cursors for prepared statements open. It may increase memory usage but does not directly resolve cursor: pin S wait on X waits.
* D (Incorrect): Increasing SESSION_CACHED_CURSORS caches more session cursors but doesn't necessarily prevent the contention indicated by the cursor: pin S wait on X wait events.
References:
* Oracle Database Reference: CURSOR_INVALIDATION
* Oracle Database Performance Tuning Guide: Reducing Cursor Invalidation
NEW QUESTION # 30
Examine this statement and output:
Which two situations can trigger this error?
- A. The user lacks the required privileges to execute the DBMS WORKLOAD CAPTURE package or the directory.
- B. The capture directory is part of the root file system.
- C. The syntax is incomplete.
- D. There is a file in the capture directory.
- E. The instance is unable to access the capture directory.
Answer: A,E
Explanation:
The ORA-15505 error indicates that the instance encountered errors while trying to access the specified directory. This could be due to:
A: Insufficient privileges: The user attempting to start the workload capture might not have the required permissions to execute the DBMS_WORKLOAD_CAPTURE package or to read/write to the directory specified.
E: Accessibility: The database instance may not be able to access the directory due to issues such as incorrect directory path, directory does not exist, permission issues at the OS level, or the directory being on a file system that's not accessible to the database instance.
References:
* Oracle Database Error Messages, 19c
* Oracle Database Administrator's Guide, 19c
NEW QUESTION # 31
Database performance has degraded recently.
index range scan operations on index ix_sales_time_id are slower due to an increase in buffer gets on sales table blocks.
Examine these attributes displayed by querying DBA_TABLES:
Now, examine these attributes displayed by querying DBA_INDEXES:
Which action will reduce the excessive buffer gets?
- A. Re-create the SALES table using the columns in IX_SALES_TIME_ID as the hash partitioning key.
- B. Re-create index IX_SALES_TIME_ID using ADVANCED COMPRESSION.
- C. Partition index IX_SALES_TIME_ID using hash partitioning.
- D. Re-create the SALES table sorted in order of index IX_SALES_TIME_ID.
Answer: B
Explanation:
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 theSALES table 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
NEW QUESTION # 32
You manage a 19c database with default optimizer settings.
This statement is used extensively as subquery in the application queries:
SELECT city_id FROM sh2.sales WHERE city_id=:Bl
You notice the performance of these queries is often poor and, therefore, execute:
SELECT city_id,COUNT(*) FROM sh2.sales GROUP BY city_id;
Examine the results:
There is no index on the CITY_ID column.
Which two options improve the performance?
- A. Force the subquery to use dynamic sampling.
- B. Generate frequency histograms on the CITY__ID column.
- C. Activate the adaptive plans.
- D. Create an index on the CITY IP column.
- E. Use a SQL Profile to enforce the appropriate plan.
Answer: B,D
Explanation:
In this scenario, creating an index and generating frequency histograms are two methods that can potentially improve performance:
* A (Correct): Generating frequency histograms on the CITY_ID column 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 the CITY_ID column 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
NEW QUESTION # 33
You need to collect and aggregate statistics for the ACCTG service and PAYROLL module, and execute:
Where do you find the output of this command?
- A. By viewing V$SERVICE_STATS
- B. By viewing V$SERV_MOD_ACT_STATS
- C. In the current working directory
- D. In $ORACLE_BASE/diag/rdbms/<db unique name>/<instance name>/trace
Answer: B
Explanation:
When you enable statistics gathering for a specific service and module using DBMS_MONITOR.
SERV_MOD_ACT_STAT_ENABLE, the output is aggregated and can be viewed using the V$SERV_MOD_ACT_STATS dynamic 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): The V$SERVICE_STATS view 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
NEW QUESTION # 34
Which two statements are true about the use and monitoring of Buffer Cache Hit ratios and their value in tuning Database I/O performance?
- A. Both the RECYCLE and KEEP buffer caches should always have a very high cache hit ratio.
- B. The performance of workloads that primarily generate full table scans and fast full index scans are always affected by the cache hit ratio.
- C. A 60% cache hit ratio can be observed for database instances which have very good I/O performance.
- D. A 99% cache hit ratio can be observed for database instances which have very poor I/O performance.
- E. The buffer cache advisory view v$db_cache_advice provides advice on cache hit ratios appropriate for the instance workload.
Answer: D,E
Explanation:
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
NEW QUESTION # 35
Which two statements are true about disabling Automatic Shared Memory Management (ASMM)?
- A. The SGA size remains unaffected after disabling ASMM.
- B. All SGA components excluding fixed SGA and other internal allocations are readjusted immediately after disabling ASMM.
- C. All auto-tuned SGA components are reset to their original user-defined values.
- D. It requires a database instance restart to take effect.
- E. Both SGA_TARGET and SGA_MAX_SIZE must be set to zero.
- F. All SGA components retain their current sizes at the time of disabling.
Answer: A,F
Explanation:
When ASMM is disabled, the sizes of the automatically managed SGA components remain at their current values. ASMM is controlled by the SGA_TARGET parameter. If SGA_TARGET is set to a non-zero value, ASMM is enabled and Oracle will automatically manage the sizes of the various SGA components. When ASMM is disabled, by setting SGA_TARGET to zero, the SGA components that were automatically sized will retain their current sizes rather than being reset to their original user-defined values. The overall size of the SGA remains the same unless manually changed by modifying individual component sizes or SGA_MAX_SIZE.
References:
* Oracle Database Administration Guide, 19c
* Oracle Database Performance Tuning Guide, 19c
NEW QUESTION # 36
Which two statements are true about cursor sharing?
- A. Setting optimizer_capture_sql_plan_baselines to TRUE loads all adaptive plans for the same statement into the cursor cache.
- B. Setting cursor_sharing to EXACT prevents Adaptive Cursor Sharing from being used.
- C. Adaptive Cursor Sharing requires histograms on filtered columns, used in equality predicates, to allow different execution plans to be generated for statements whose bound values would normally generate different plans at hard parse time.
- D. Setting Cursor_sharing to FORCE can result in a plan that is suboptimal for the majority of values bound to a bind variable when executing a cursor with one or more bind variables.
- E. Adaptive Cursor Sharing guarantees that a suboptimal plan will never be used on any execution of a SQL statement.
Answer: B,D
Explanation:
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
NEW QUESTION # 37
......
1z1-084 Dumps - Pass Your Certification Exam: https://vcecollection.trainingdumps.com/1z1-084-valid-vce-dumps.html
