Oracle Oracle Database 1Z0-064 Questions & Answers

• Question 11:

  For which two tasks would you recommend the use of a repeating baseline template?

  A. to capture AWR data during a system test that is scheduled for a weekend

  B. to capture AWR data for a scheduled job that runs every day over a specified period of time

  C. to capture user activity information for a consumer group over a specified period of time

  D. to capture AWR data for a specified contiguous time period on an ongoing basis

  E. to capture statistics performance variation over a specified period of time

  Correct Answer: BD

  Use a repeating baseline template to create and drop baselines based on a repeating time schedule. This is useful if you want Oracle Database to automatically capture a contiguous time period on an ongoing basis. For example, you may want to capture AWR data during every Monday morning for a month. In this case, you can create a repeating baseline template to automatically create baselines on a repeating schedule for every Monday, and automatically remove older baselines after a specified expiration interval, such as one month.

  Reference: https://docs.oracle.com/database/121/TGDBA/gather_stats.htm#TGDBA187

• Question 12:

  You are administrating a database that supports a mixed workload. Applications are running on a middle tier and use connection pooling to connect to the database instance. You want to trace all applications and modules that use the ORCL1 service to connect to the database instance.

  How would you consolidate the SQL statements for sessions that are connected by using the ORCL1 service?

  A. by using the DBMS_MONITOR package to enable tracing, the trcsess utility to consolidate trace files, and the tkprof utility to interpret trace files

  B. by setting TRACE_ENABLED = TRUE and using the tkprof utility to consolidate and interpret trace files

  C. by setting SQL_TRACE = TRUE and using the tkprof utility to consolidate and interpret trace files

  D. by using the DBMS_MONITOR package to enable tracing, the tkprof utility to consolidate trace files, and the trcsess utility to interpret trace files

  E. by using the DBMS_TRACE package to enable tracing and the tkprof utility to consolidate and interpret trace files

  Correct Answer: C

• Question 13:

  You are administrating a database that supports an OLTP workload. A few users complain about the poor performance of some SQL statements. You notice that these SQL statements belong to different applications and conclude that there is no correlation between them. You create a SQL Tuning Set (STS) containing these SQL statements.

  What must you do to analyze and generate recommendations for the SQL statements in the STS?

  A. Submit the STS as input to the SQL Performance Analyzer to generate recommendations for creating indexes and materialized views.

  B. Submit the STS as input to the SQL Tuning Advisor to generate recommendations for indexes or SQL profiles or both.

  C. Submit the STS as input to the SQL Tuning Advisor to generate recommendations for SQL plan baselines.

  D. Submit the STS as input to the SQL Access Advisor to generate recommendations for SQL profiles.

  Correct Answer: C

  Reference: https://docs.oracle.com/cd/E11882_01/server.112/e41573/sql_tune.htm

• Question 14:

  To diagnose the performance degradation of your production database, you execute these queries:

  

  Segment space management is manual for some of the tablespaces.

  What can be the reason for the performance degradation?

  A. PGA is undersized

  B. Contention is caused by freelist management.

  C. There are too many disk sort operations.

  D. The buffer cache is undersized.

  E. The database writer (DBWR) process is processing buffers at a rate that is faster than the rate at which the I/O system can return the buffers.

  Correct Answer: E

• Question 15:

  In which three scenarios can you use ADDM to get recommendations?

  A. to determine the SQL statements that are performing poorly before you launch the SQL Tuning Advisor to tune them

  B. to determine the segments on which waits are occurring when the database has a high number of buffer busy waits

  C. to determine whether SQL profiling needs to be done to tune SQL statements transparently without requiring any change to the application code

  D. to determine if the SGA_TARGET parameter value needs to be changed when your database is configured for Automatic Shared Memory Management and additional physical memory is added to the database server

  E. to analyze problems in an unresponsive database, which requires a database restart

  Correct Answer: ACE

• Question 16:

  Examine the parameters set for your database instance:

  

  You are administrating a database that supports a DSS workload. You make some changes to the sizes of memory components. Consequently, you receive this error:

  

  Which three actions might resolve the issue?

  A. pinning the cursors in the shared pool

  B. setting the PGA_AGGREGATE_TARGET parameter to 0 and the MEMORY_TARGET parameter to the value of the PGA_AGGREGATE_TARGET parameter

  C. enabling Automatic Shared Memory Management for the database instance

  D. setting the value of MEMORY_MAX_TARGET to the value of the SGA_MAX_SIZE parameter

  E. increasing the size of the shared pool

  F. enabling Automatic Memory Management for the database instance

  Correct Answer: CDF

  Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ PGA_AGGREGATE_TARGET.html#GUID-DEBBD3F7-9F6D-4AC8-952C-0E0B2E62312D

• Question 17:

  Your database supports an OLTP workload. You are planning to migrate database storage from file system storage to Automatic Storage Management (ASM). The applications supported by the database primarily perform small, random I/Os.

  Which two statements are true regarding ASM striping in combination with RAID 0?

  A. It allows you to evenly spread data over multiple disks.

  B. It provides higher I/O bandwidth.

  C. It helps in reducing network latency.

  D. It improves availability and reliability of data be ensuring that logical data corruptions are found and repaired.

  E. It improves the performance of only write-intensive workloads.

  Correct Answer: AB

• Question 18:

  Examine the query and its execution plan:

  

  Explained.

  

  Which two interpretations are correct?

  A. Id 3 is the first step executed.

  B. The Id 0 step indicates the cost of the entire plan.

  C. Id 4 is the second step executed.

  D. The SORT UNIQUE operation applies only the CUST_MAIN table.

  Correct Answer: CD

• Question 19:

  You are administrating a database that supports an OLTP workload. A new application module is deployed that is shipped along with the SQL plan baselines for the SQL statements executed by the application. You load the SQL plan baselines to the SQL Management Base.

  Examine the parameters:

  

  Which two statements are true in this scenario?

  A. The SQL plan baselines are used but better execution plans may be found and can be evolved manually for the SQL statements.

  B. The SQL plan baselines are used by the SQL statements and ensure that the best plan is used.

  C. The SQL plan baselines are not used by the SQL statements if the optimizer statistics are stale.

  D. The SQL plan baselines are loaded but are not used until SQL profiles are generated for the SQL statements.

  Correct Answer: CD

  Reference: https://docs.oracle.com/database/121/TGSQL/tgsql_spm.htm#TGSQL94621

• Question 20:

  In your database, supporting an OLTP workload, the SALES table is range-partitioned on the SALES_DATE column. A new partition is created every quarter and is frequently used for updates and queries. You recently noticed degraded performance of queries on a new partition due to stale statistics. A local partitioned index exists on the SALES_DATE column.

  What would you recommend to improve performance?

  A. dropping the local partitioned index and creating a global partitioned index on the SALES_DATE column

  B. setting the INCREMENTAL preference to TRUE and the NO_INVALIDATE preference to TRUE for the partitioned table while gathering statistics

  C. setting the INCREMENTAL preference to TRUE and the CASCADE preference to FALSE for the partitioned table while gathering statistics

  D. setting the INCREMENTAL preference to TRUE and the GRANULARITY preference to AUTO for the partitioned table while gathering statistics

  Correct Answer: D