Oracle Oracle Database 1Z0-064 Questions & Answers

• Question 81:

  In your database, the locally managed tablespace, USERS, has the default space usage alert set to 85% for the warning level and 97% for the critical level.

  Which two statements are true? (Choose two.)

  A. Alerts are recorded in both Oracle Enterprise Manager Cloud Control and DBA_OUTSTANDING_ALERTS only when the critical threshold is exceeded.

  B. Alert settings for the warning and critical levels must be disabled before taking the USERS tablespace offline.

  C. Alerts that are triggered are automatically recorded in DBA_ALERT_HISTORY after they are cleared.

  D. Alerts are triggered when the space usage reaches the warning level, again when it reaches the critical level, and yet again when the space usage falls below the critical level.

  Correct Answer: AB

• Question 82:

  Examine the parameters set for a database instance supporting a mixed workload:

  

  The database instance supports shared server and dedicated server connections simultaneously. Users complain about increased response times of a few DSS queries. During investigation, you execute the queries:

  

  Based on the output, which two courses of action would you recommend to improve query performance? (Choose two.)

  A. Use a parallel hint in the queries.

  B. Increase the number of DBWn processes.

  C. Increase the value of the SORT_AREA_SIZE initialization parameter.

  D. Increase the size of the temporary tablespace or add a new temporary tablespace.

  E. Increase the value of the PGA_AGGREGATE_TARGET initialization parameter.

  F. Increase the size of the large pool.

  Correct Answer: CF

• Question 83:

  Your database supports a mixed workload. In an application, multiple complex queries with functions and expressions are executing. You want to analyze the queries that are currently cached in the library cache to receive recommendations about the usage of indexes and materialized views.

  What should you do to achieve this? (Choose the best answer.)

  A. Create an STS for the queries cached in the library cache and submit it as an input to SQL Tuning Advisor.

  B. Create an STS for the queries cached in the library cache and submit it as an input to SQL Access Advisor.

  C. Capture the workload in an STS and submit to SQL Tuning Advisor for recommendations.

  D. Create an STS for the queries cached in the library cache and submit it as an input to SQL Performance Analyzer.

  Correct Answer: D

• Question 84:

  Examine the parameters set for your database instance: You upgrade your database to Oracle Database 12c. The database supports a mixed workload and works with different workloads at different times. You notice in an ADDM report that the shared pool is inadequately sized. You resize the shared pool by decreasing the sizes of other pools, which results in inadequate sizes for other pools. You want to automate the sizing of SGA components.

  

  Which two actions should you perform? (Choose two.)

  A. Set the SGA_TARGET parameter equal to SGA_MAX_SIZE.

  B. Set the SGA_TARGET parameter to the sum of DB_CACHE_SIZE, SHARED_POOL, and LARGE_POOL_SIZE.

  C. Set the MEMORY_MAX_TARGET parameter to the sum of DB_CACHE_SIZE, SHARED_POOL, and LARGE_POOL_SIZE.

  D. Set DB_CACHE_SIZE, SHARED_POOL, and LARGE_POOL_SIZE to their minimum required values.

  E. Set the PGA_AGGREGATE_TARGET parameter to 0 and the SGA_TARGET parameter to 1.5G.

  Correct Answer: AE

• Question 85:

  Which two statements are true about DB time in V$SYS_TIME_MODEL? (Choose two.)

  A. DB time cannot exceed the total elapsed time (walk clock time) since the database instance started.

  B. DB time cannot exceed the maximum number of concurrent sessions multiplied by the actual elapsed time for each session.

  C. DB time includes the time spent on client processes and background processes.

  D. Reducing DB time allows a database instance to support more user requests by using the same resources.

  E. DB time is always greater than or equal to the DB CPU time.

  Correct Answer: DE

• Question 86:

  Examine the parameters set for your database instance: You notice that for one particular SQL statement, the optimizer generates a new better plan than the plans in the SQL Plan Management Base.

  

  Which action is taken by the optimizer? (Choose the best answer.)

  A. It adds the newly generated plan as an accepted but non-fixed plan.

  B. It adds the newly generated plan as enabled and accepted.

  C. It adds the newly generated plan as enabled but not accepted.

  D. It adds the newly generated plan as a fixed plan, which will be used each time the SQL statement is executed.

  Correct Answer: B

• Question 87:

  Your database supports a DSS workload. In an application, a few complex queries that contain multiple functions and expressions are using materialized views. You notice that some queries are performing poorly because they are not benefiting from query rewrites.

  Which three actions would you take to improve the performance of queries? (Choose three.)

  A. Create a SQL Tuning Set (STS) and submit as input to the SQL Access Advisor to generate recommendations about query rewrite and fast refresh for materialized views.

  B. Use the DBMS_MVIEW.EXPLAIN_REWRITE procedure to analyze why a query failed to rewrite.

  C. Create an STS and submit as input to the SQL Performance Analyzer to get recommendations about improving the performance of queries.

  D. Use the DBMS_ADVISOR.TUNE_MVIEW procedure to get recommendations about rewriting materialized views.

  E. Use the DBMS_ADVISOR.QUICK_TUNE procedure to analyze queries based on the usage of query rewrite with materialized views.

  Correct Answer: ACE

• Question 88:

  Which two situations can lead to sparsely populated index blocks? (Choose two.)

  A. Data is frequently inserted using direct path load into a table with an index.

  B. Indexed columns in a table are frequently updated.

  C. Values in an indexed column are inserted using monotonically incrementing sequences.

  D. Bulk delete operations are performed on a table with indexes.

  E. Online table move operations are performed frequently on a table with indexes.

  Correct Answer: DE

• Question 89:

  For which three problem categories does Automatic Database Diagnostic Monitor (ADDM) provide analysis and recommendations by default? (Choose three.)

  A. for network stack-related bandwidth contention

  B. for concurrency issues because of buffer busy problems

  C. for high-load PL/SQL execution and compilation, and high-load Java usage

  D. for application-level lock contention.

  Correct Answer: BCD

• Question 90:

  Which two statements are true about Compare Period ADDM? (Choose two.)

  A. It is automatically invoked whenever the AWR Compare Period report is invoked.

  B. It is automatically invoked whenever ADDM is run by default.

  C. It verifies if there is any change in the workload or average resource consumption by the SQL executed during the two specified time periods, to ensure 100% accuracy.

  D. It can be used to create a comparison report between the Database Replay workload capture report and the replay report.

  Correct Answer: BD